oracle PL/SQL（procedure language/SQL）程式設計之函數+過程+包

標籤：

匿名PL/SQL塊回顧

DECLARE (可選)     定義在PL/SQL塊中要使用的對象 BEGIN (必須)     執行語句 EXCEPTION (可選)     錯誤處理語句 END; (必須) 匿名塊（以DECLARE或BEGIN開始）每次使用時都要進行編譯。 匿名塊不儲存在資料庫中，並且它不能從其它的PL/SQL塊中進行調用。

 

 

過程、函數、包和觸發器：都是帶名塊。（請注重同匿名塊的比較） 過程、函數、包和觸發器：可以儲存在資料庫中，並且可以在需要的時候運行。 過程、函數、包和觸發器：可以從其它PL/SQL塊中進行調用。 子程式：過程、函數

子程式是帶名的PL/SQL塊，能夠接受參數和被環境調用。在PL/SQL中兩類子程式：過程和函數

子程式說明（header）決定 : PL/SQL子程式的類型是過程還是函數。 子程式的名稱 參數列表 當子程式是函數時必須要有傳回值(使用RETURN子句) 關鍵字IS或AS是必須的。 子程式體:是一個擁有聲明、執行和異常處理部分的PL/SQL塊。 聲明部分介於IS|AS和BEGIN之間。在匿名塊中必須使用DECLARE關鍵字顯示的指出聲明部分，而在子程式中沒有DECLARE關鍵字。 執行部分介於BEGIN和END關鍵字之間，這部分必須存在。 異常處理部分介於EXCEPTION和 END之間是可選的部分

建立過程（文法）

Example  建立預存程序，輸出系統的日期和時間 CREATE OR REPLACE PROCEDURE display_time IS BEGIN    dbms_output.put_line(systimestamp); END display_time; 使用三種方式調用上面建立的預存程序 方式一：使用sqlplus命令EXECUTE(簡寫EXEC) 調用     EXECUTE display_time; 方式二：使用sql命令CALL調用     CALL display_time( ); 方式三：在PL/SQL塊中調用     BEGIN         display_time;     END; 參數和模式

形式參數(Formal parameters):在過程中聲明的參數就是形式參數（p_FirstName, p_LastName, p_Major ）。

CREATE OR REPLACE PROCEDURE insert_emp  [形式參數] (no  emp.empno%TYPE,            name  emp.ename%TYPE, Job  emp.job%TYPE,                mgr  emp.mgr%TYPE, hiredate emp.hiredate%TYPE ,    salary emp.sal%TYPE , comm emp.comm%TYPE ,        deptno emp.deptno%TYPE ) IS BEGIN 　　INSERT INTO emp VALUES(no,name,job,mgr,hiredate,salary,comm,deptno); END; 調用上述儲存過程[匿名塊來調用儲存過程（儲存過程是帶名塊。）]

DECLARE v_no  emp.empno%TYPE:=10000, v_name  emp.ename%TYPE :=‘Jones’, v_job  emp.job%TYPE := ‘SALESMAN‘, v_mgr  emp.mgr%TYPE :=7369, v_hiredate emp.hiredate%TYPE :=SYSDATE, v_salary emp.sal%TYPE := 800, v_comm emp.comm%TYPE :=NULL, v_deptno emp.deptno%TYPE :=10 BEGIN insert_emp(v_no, v_name, v_job, v_mgr, v_hiredate, v_salary, v_comm, v_deptno ); END;

參數和模式 過程可以通過參數和調用環境進行資料傳遞。 有三種傳遞模式—IN、OUT和IN OUT。如果沒有為形式參數指定模式，則使用預設模式IN。

對形式參數的約束：在過程聲明中，限制CHAR和VARCHAR2參數的長度以及限制NUMBER參數的精度和/或刻度範圍都是非法的。 CREATE OR REPLACE PROCEDURE ParameterLength (   p_Parameter1 IN OUT VARCHAR2(10),   p_Parameter2 IN OUT NUMBER(3,2)) AS BEGIN   p_Parameter1 := ‘abcdefghijklm‘;   p_Parameter2 := 12.3; END ParameterLength; / 過程聲明非法

參數和模式--參數的預設值

CREATE OR REPLACE PROCEDURE add_dept   (p_name IN dept.dname%TYPE DEFAULT ‘unknown‘,    p_loc IN dept.loc%TYPE  DEFAULT ‘NEW YORK‘) IS BEGIN    INSERT INTO dept (deptno,dname, loc)    VALUES (dept_seq.NEXTVAL, p_name, p_loc); END add_dept; 執行：(用匿名塊來執行 帶名塊)

SQL>BEGIN                add_dept;                add_dept (‘TRAINING‘);                add_dept ( p_loc =>‘BOSTON ‘, p_name => ‘EDUCATION‘);                add_dept ( p_loc => ‘CHICAGO‘) ;                END;          SQL>SELECT * FROM dept; 刪除過程  DROP PROCEDURE raise_salary;

建立函數

CREATE [OR REPLACE] FUNCTION function_name [(argument1 [IN | OUT | IN OUT] data_type , argument2 [IN | OUT | IN OUT] data­_type,…)] RETURN data_type IS |AS    [declaration_section;] BEGIN    executable_section;    RETURN expression; [EXCEPTION    exception_handlers; RETURN expression;] END [function_name]; 相關說明

OR REPLACE關鍵字指出如果過程存在，首先刪除它，然後用新版本建立。 Parameter、mode和datatype的含義與過程相同,但是應該盡量避免使用OUT和IN OUT參數模式。 return_datatype是函數返回的數值的類型，不能對資料類型的尺寸進行限定。 PL/SQL塊以BEGIN開始或以局部變數聲明開始，以END或END function_name結束。在PL/SQL塊中，可以使用多個RETURN語句，但是必須保證至少有一條RETURN語句存在。函數每一次運行只有一個RETURN語句被執行。

舉例：。。。。。。。。。。。。。。。。。。。。。。 CREATE OR REPLACE FUNCTION get_sal       (p_id IN emp.empno%TYPE) RETURN NUMBER IS       v_salary emp.sal%TYPE :=0; BEGIN      SELECT sal     INTO v_salary     FROM emp     WHERE empno = p_id;      RETURN v_salary; END get_sal;

執行 函數 注意：因為函數具有傳回值，所以調用函數是作為一個運算式的一部分，而不能像調用過程那樣作為一個獨立的語句使用。 方式一：使用變數接收傳回值 VAR salary NUMBER; EXEC :salary:=get_sal(7369); PRINT salary;方式二：在SQL語句中直接調用函數 SELECT get_sal(7369) FROM DUAL; 方式三：使用DBMS_OUTPUT調用函數 SET SERVEROUTPUT ON EXEC dbms_output.put_line(‘工資是:‘|| get_sal(7369));
刪除函數 DROP FUNCTION get_sal;

建立包

包是一個可以將相關Object Storage Service在一起的PL/SQL結構，是Oracle資料庫的一種模式對象。包有兩個獨立的部分—包的規範和主體，這兩部分獨立的儲存在資料字典中。 包所包含的程式對象是過程、函數、變數、常數、遊標和異常。

包比獨立的過程和函數有更多的優點： 包使組織應用開發更有效。 包使授權更有效。 包允許修改包的對象而不需要重新編譯從屬的對象。 包允許ORACLE一次讀入多個包對象到記憶體。 包可包含全域變數和遊標，可為包中的全部過程和函數使用。 包允許重載過程和函數。

建立包

建立一個包，要執行兩個步驟： （1）用CREATE PACKAGE命令建立包規範。在包規範中說明程式對象，這些對象稱為公用對象。公用對象可以在包外引用，也可以在包中的其它對象引用。 （2）用CREATE PACKAGE BODY命令建立包體，在包體中說明和定義程式對象： 定義在包規範中說明的公用對象； 定義附加的包對象（私用對象），由於私用對象是在包體中說明，它不能在包外引用。 CREATE [OR REPLACE] PACKAGE package_name IS|AS     public type and item declarations     subprogram specifications END package_name; specification

OR REPLACE：如果包規範存在則刪除並重新建立包的規範。 package_name：包的名字 public type and item declarations ：聲明公用的變數、常量、遊標、異常和資料類型。 subprogram specifications：聲明PL/SQL子程式。 在包規範中聲明的變數，預設的初始化值是NULL。

建立包的規範
CREATE OR REPLACE PACKAGE comm_package IS      g_comm NUMBER := 0; --initialized to 0      PROCEDURE reset_comm  (p_comm IN NUMBER); END comm_package; G_COMM 是一個全域變數，初始化值是0.10。 RESET_COMM 是一個公用的過程，它在包體中定義其執行代碼。 建立包體 CREATE [OR REPLACE] PACKAGE BODY package_name IS|AS        private type and item declarations        subprogram bodies END package_name; OR REPLACE：如果包體存在則刪除並重新建立包體。 package_name：包的名字。 private type and item declarations：聲明私人的變數、常量、遊標、異常和資料類型。 subprogram bodies ：定義公有和私人的PL/SQL子程式。 建立包體

CREATE OR REPLACE PACKAGE BODY comm_package  IS    FUNCTION validate_comm (p_comm IN NUMBER)  RETURN BOOLEAN  （包中建立函數）    IS        v_max_comm NUMBER;    BEGIN        SELECT MAX(comm)  INTO v_max_comm  FROM emp;        IF p_comm > v_max_comm THEN RETURN(FALSE);        ELSE RETURN(TRUE);        END IF;    END validate_comm;    PROCEDURE reset_comm (p_comm IN NUMBER)   IS （包中建立儲存過程）    BEGIN         IF validate_comm(p_comm)  THEN  g_comm:=p_comm; --reset global variable         ELSE  RAISE_APPLICATION_ERROR(-20210,‘Invalid commission‘);         END IF;    END reset_comm; END comm_package; 執行

建立一個沒有包體包 CREATE OR REPLACE PACKAGE global_consts IS      mile_2_kilo CONSTANT NUMBER := 1.6093;      kilo_2_mile CONSTANT NUMBER := 0.6214;      yard_2_meter CONSTANT NUMBER := 0.9144;      meter_2_yard CONSTANT NUMBER := 1.0936; END global_consts; 執行 SQL>EXECUTE DBMS_OUTPUT.PUT_LINE(‘20 miles = ‘||20*global_consts.mile_2_kilo|| ‘ km‘)

http://www.cnblogs.com/pacoson/p/3523467.html

oracle PL/SQL（procedure language/SQL）程式設計之函數+過程+包（轉）