1、7過程、函數和包過程、函數和包Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 2目標目標在完成本章學習后，你將掌握下列技能；在完成本章學習后，你將掌握下列技能；lPL/SQL過程的定義與用法過程的定義與用法lPL/SQL函數的定義與用法函數的定義與用法lPL/SQL包的定義與用法包的定義與用法Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 3本章內容本章內容lPL/SQL過程過程lPL/SQL函數函數lPL/SQL包包Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 4導航導航lPL/SQL過程過程l

2、PL/SQL函數函數lPL/SQL包包Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 5PL/SQL過程過程l過程是指用于執行特定操作的過程是指用于執行特定操作的PL/SQL塊塊l通過使用過程，不僅可以簡化客戶應用的開發和維護，通過使用過程，不僅可以簡化客戶應用的開發和維護，而且可以提高應用程序的運行性能。而且可以提高應用程序的運行性能。l語法：語法：CREATE OR REPLACE PROCEDURE procedure_name(argument1mode1 datatype1,argument2mode2 datatype2,)IS|ASPL/SQL B

3、lock;其中其中procedure_name指定過程名，指定過程名，argument指定過程指定過程參數；參數；IS或或AS用于開始用于開始PL/SQL塊。塊。Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 6PL/SQL過程過程-建立無參數的過程建立無參數的過程l下面以建立用于輸出當前日期和時間的過程下面以建立用于輸出當前日期和時間的過程out_time為例。為例。CREATE OR REPLACE PROCEDURE out_timeISBEGIN DBMS_SESSION.SET_NLS(NLS_DATE_FORMAT,YYYY-MM-DD HH24:M

4、I:SS); DBMS_OUTPUT.PUT_LINE(sysdate);END;/Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 7PL/SQL過程過程-建立帶有輸入參數的過程建立帶有輸入參數的過程l通過使用輸入參數，可以將應用程序數據傳通過使用輸入參數，可以將應用程序數據傳遞到過程。遞到過程。l當定義過程參數時，當定義過程參數時，默認參數模式就是輸入默認參數模式就是輸入參數參數l可以使用可以使用IN關鍵字顯式定義輸入參數。關鍵字顯式定義輸入參數。Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 8CREATE OR REPLAC

5、E PROCEDURE add_empl( empno empl.empno%TYPE,ename empl.ename%TYPE, ebirthday empl.ebirthday%TYPE, egrade empl.egrade%TYPE, ejob empl.ejob%TYPE,emajor empl.emajor%TYPE, indate empl.indate%TYPE,salary empl.salary%TYPE, allow empl.allow%TYPE,deptno empl.deptno%TYPE) IS BEGIN INSERT INTO empl VALUES(emp

6、no,ebirthday,ename,ejob,egrade,emajor,indate,salary,allow,depno);END;Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 9PL/SQL過程過程-建立帶有輸出參數的過程建立帶有輸出參數的過程l通過使用輸入參數，可以將數據或消息傳遞到調用環境和應用程序。通過使用輸入參數，可以將數據或消息傳遞到調用環境和應用程序。l當定義輸出參數時，需要制定參數模式為當定義輸出參數時，需要制定參數模式為OUT。l下面建立用于更新雇員工資并輸出雇員姓名的過程下面建立用于更新雇員工資并輸出雇員姓名的過程update_sal

7、為例。為例。Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 10CREATE OR REPLACE PROCEDURE update_sal( eno varhcar2, sal NUMBER, name OUT VARCHAR2) IS BEGIN UPDATE empl SET salary=sal WHERE empno=eno RETURNING ename INTO name;END;/Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 11PL/SQL過程過程-建立帶有輸入輸出參數的過程建立帶有輸入輸出參數的過程l通過在過

8、程中使用輸入輸出函數，可以調用過程通過在過程中使用輸入輸出函數，可以調用過程時輸入數據到過程，在調用結束后輸出數據到調時輸入數據到過程，在調用結束后輸出數據到調用環境和應用程序。用環境和應用程序。l當定義輸入輸出參數時，需要指定參數模式為當定義輸入輸出參數時，需要指定參數模式為IN OUT。Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 12CREATE OR REPLACE PROCEDURE devide( num1 IN OUT NUMBER，num2 IN OUT NUMBER) IS v1 NUMBER; v2 NUMBER;BEGIN v1:=TRU

9、NC(num1/num2); v2:=MOD(num1,num2); num1:=v1; num2:=v2;END;/Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 13PL/SQL過程過程-調用過程調用過程l在在SQL*PLUS中調用過程時，需要使用中調用過程時，需要使用CALL或者或者EXECUTE命令；而在命令；而在PL/SQL塊中過程可以直接應用。塊中過程可以直接應用。l過程調用帶參數分過程調用帶參數分4種情況：種情況：如果無參數，那么可以直接引用過程名；如果無參數，那么可以直接引用過程名；如果有輸入參數，那么需要為輸入參數提供數值；如果有輸入參數，那么

10、需要為輸入參數提供數值；如果帶有輸出參數，那么需要使用變量接收輸出結果；如果帶有輸出參數，那么需要使用變量接收輸出結果；如果有輸入輸出參數，那么調用時需要使用具有輸入值的變量。如果有輸入輸出參數，那么調用時需要使用具有輸入值的變量。Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 14PL/SQL過程過程-調用無參數的過程調用無參數的過程l直接引用過程名直接引用過程名exec out_timeOracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 15PL/SQL過程過程-調用帶有輸入參數的過程調用帶有輸入參數的過程l需要為輸入參數提供數據

11、值需要為輸入參數提供數據值exec add_empl(1111, 1980-3-4, lei, programmer, 1, 7369, SYSDATE, 800, null, 30)Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 16PL/SQL過程過程-調用帶有輸出參數的過程調用帶有輸出參數的過程l需要使用變量接受輸出參數的數據值需要使用變量接受輸出參數的數據值DECLARE v_name empl.ename%TYPE;BEGIN update_sal(&eno,&salary,v_name); DBMS_OUTPUT.PUT_LINE(n

12、ame:|v_name);END;/輸入輸入 雇員號的值：雇員號的值： 1111輸入工資的值：輸入工資的值： 2500姓名：姓名：leiOracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 17PL/SQL過程過程-調用帶有輸入輸出參數的過程調用帶有輸入輸出參數的過程l調用之前需要定義變量存放輸入值，并在調用結束之后調用之前需要定義變量存放輸入值，并在調用結束之后使用變量輸出數據。使用變量輸出數據。DECLARE n1 NUMBER:=&n1; n2 NUMBER:=&n2;BEGIN divide(n1,n2); DBMS_OUTPUT.PUT_LI

13、NE(商商:|n1|,余數余數:|n2);END; /輸入輸入n1的值：的值：100輸入輸入n2的值：的值：30商：商：3，余數，余數10Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 18為參數傳遞變量或數據為參數傳遞變量或數據當為參數傳遞變量或數據，可以采用以下當為參數傳遞變量或數據，可以采用以下3種方法種方法位置傳遞位置傳遞名稱傳名稱傳組合傳遞組合傳遞Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 19PL/SQL過程過程-使用位置傳遞為參數傳遞變量和數據使用位置傳遞為參數傳遞變量和數據l位置傳遞位置傳遞是指按照參數定義順序

14、依次為每個參數是指按照參數定義順序依次為每個參數提供變量或者數據值提供變量或者數據值l當使用位置傳遞時，當使用位置傳遞時，必須為所有參數提供變量或必須為所有參數提供變量或者輸入數據者輸入數據CALL add_empl(2222,1978-12-2， alex,NULL, 1， NULL,SYSDATE,800,NULL,30);Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 20PL/SQL過程過程-使用名稱傳遞為參數傳遞變量和數據使用名稱傳遞為參數傳遞變量和數據l名稱傳遞名稱傳遞是指通過制定參數名為其提供變量或者數據是指通過制定參數名為其提供變量或者數據l當使

15、用名稱傳遞時，需要使用當使用名稱傳遞時，需要使用=為參數提供變量或者為參數提供變量或者數值數值exec add_emp(empno=3333,ebirghday=,1989-2-2，indate=null, egrade=1, ename=JOHN,ejob=null,major=null, salary=null,allow=null,deptno=null)Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 21PL/SQL過程過程-使用組合傳遞為參數傳遞變量和數據使用組合傳遞為參數傳遞變量和數據l組合傳遞組合傳遞是指在調用子程序時結合使用位置傳遞和是指在調用子

16、程序時結合使用位置傳遞和名稱傳遞名稱傳遞exec add_emp(4444, 1985-2-3,AGASI , tester,1, null, indate=SYSDATE, salary=1200, allow=0, deptno=30)Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 22PL/SQL過程過程-過程開發示例過程開發示例l為過程參數指定默認值為過程參數指定默認值CREATE OR REPLACE PROCEDURE add_emp( empno empl,empno%TYPE, ebirthday empl.ebirthday%TYPE, ena

17、me empl.ename%TYPE, ejob empl.ejob%TYPE DEFAULT programmer, egrade empl.egrade%TYPE DEFAULT 1, major empl.mgr%TYPE, indate empl.indate%TYPE DEFAULT SYSDATE, salary empl.salary%TYPE DEFAULT 1000, allow empl.allow%TYPE DEFAULT 0, deptno empl.deptno%TYPE)ISBEGIN INSERT INTO emp VALUES(empno,ebirthday,e

18、name,ejob,egrade major, indate, salary,allow, deptno);END;/Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 23PL/SQL過程過程-使用異常處理使用異常處理CREATE OR REPLACE PROCEDURE update_sal( name empl.ename%TYPE, sal empl.salary%TYPE) IS e_no_rows EXCEPTION;BEGIN UPDATE empl SET salary=sal WHERE LOWER(ename)=LOWER(name); IF S

19、QL%NOTFOUND THEN RAISE e_no_rows; END IF;EXCEPTION WHEN e_no_rows THEN DBMS_OUTPUT.PUT_LINE(這雇員不存在這雇員不存在);END;lExec update_sal(jarbus,1500) 這雇員不存在這雇員不存在lExec update_sal(allen,1500)Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 24PL/SQL過程過程-維護過程維護過程l刪除過程刪除過程 DROP PROCEDURE l顯示編譯錯誤，查詢數據字典顯示編譯錯誤，查詢數據字典USER_ER

20、RORS，或者執行或者執行SHOW ERRORS命令。命令。l確定過程狀態確定過程狀態, 查詢數據字典查詢數據字典USER_OBJECTS。l編譯過程，編譯過程，ALTER PROCEDURE COMPILE;l查看過程查看過程 代碼，通過查詢數據字典代碼，通過查詢數據字典USER_SOURCE。Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 25導航導航lPL/SQL過程過程lPL/SQL函數函數lPL/SQL包包Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 26函數函數l函數是一種命名存儲塊，可以接收輸入并進函數是一種命名存

21、儲塊，可以接收輸入并進行定義的邏輯處理以后把結果返回給調用者。行定義的邏輯處理以后把結果返回給調用者。l與過程相似，只是函數必須有返回值。與過程相似，只是函數必須有返回值。Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 27函數函數-創建創建l基本語法基本語法CREATE OR REPLACE FUNCTION function_name (parameterIN|IN OUT date_type1,parameterIN|IN OUT date_type2,RETURN return_type;IS|AS declaration sectionBEGIN ex

22、ecutable statementsEXCEPTION exception handlers;ENDfunction_name; Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 28函數函數-例子例子 l下列是求從下列是求從1到某個數累加的例子到某個數累加的例子CREATE OR REPLACE FUNCTION sum(n IN number) RETURN numberAS result number:=0;BEGIN FOR i in 1n LOOP result:=result+i; END LOOP; return(result);END sum;O

23、racle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 29函數函數-調用調用l調用函數的時候需要保存返回值調用函數的時候需要保存返回值l例子例子DECLARE num1 number:=10; sum1 number:=0;BEGIN sum1:=sum(num1); DBMS_OUTPUT.PUT_LINE(“result=“|sum1);Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 30導航導航lPL/SQL過程過程lPL/SQL函數函數lPL/SQL包包Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學

24、 31PL/SQL包包l包用于邏輯組合相關的自定義類型、常量、包用于邏輯組合相關的自定義類型、常量、變量、游標、過程和函數。變量、游標、過程和函數。l簡化了客戶應用開發，提高性能，實現信息簡化了客戶應用開發，提高性能，實現信息隱藏、子程序重載等功能。隱藏、子程序重載等功能。Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 32PL/SQL包包-規范規范包規范用于定義包的公用組件，包括常量、變包規范用于定義包的公用組件，包括常量、變量、游標、自定義類型、過程和函數等。量、游標、自定義類型、過程和函數等。建立包規范語法建立包規范語法CREATE OR REPLACE

25、PACKAGE package_nameIS|AS - 定義定義公用常量、變量等公用常量、變量等 -定義定義公用過程和函數等。公用過程和函數等。END package_name;Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 33PL/SQL包包-例子例子CREATE OR REPLACE PACKAGE emp_package ISg_deptno empl.deptno%TYPE:=30;PROCEDURE add_employee(empno empl.empno%TYPE, ebirthday empl.ebirthday%TYPE, ename emp

26、l.ename%TYPE, ejob empl.ejob%TYPE DEFAULT programmer, egrade empl.egrade%TYPE,major empl.mgr%TYPE, indate empl.indate%TYPE DEFAULT SYSDATE, salary empl.salary%TYPE DEFAULT 1000, allow empl.allow%TYPE, deptno empl.deptno%TYPE DEFAULT g_deptno);PROCEDURE fire_employee(eno NUMBER);FUNCTION get_sal(eno

27、NUMBER) RETURN NUMBER;END emp_package;Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 34PL/SQL包包-建立包體建立包體l包體用于實現包規范所定義的公用過程和函數，包包體用于實現包規范所定義的公用過程和函數，包體不僅可用與實現公用過程和函數，而且可以定義體不僅可用與實現公用過程和函數，而且可以定義包的私有組件（變量、常量等）包的私有組件（變量、常量等）l建立包體語法建立包體語法CREATE OR REPLACE PACKAGE BODY package_name IS|AS -定義私有變量、常量等定義私有變量、常量等 -

28、實現實現公用過程和函數公用過程和函數END package_name;Oracle 數據庫應用與設計數據庫應用與設計杭州電子科技大學杭州電子科技大學 35PL/SQL包包-例子例子CREATE OR REPLACE PACKAGE BODY emp_package ISFUNCTION validate_deptno(dno empl.deptno%TYPE) RETURN BOOLEANIS temp INT;BEGIN SELECT 1 INTO temp FROM dept WHERE deptno=dno; RETURN TRUE;EXCEPTION WHEN NO_DATA_FOUN

29、D THEN RETURN FALSE; END;PROZCEDURE add_employee(empno empl.empno%TYPE, ebirthday empl.ebirthday%TYPE, ename empl.ename%TYPE, ejob empl.ejob%TYPE DEFAULT programmer, egrade empl.egrade%TYPE,major empl.mgr%TYPE, indate empl.indate%TYPE DEFAULT SYSDATE, salary empl.salary%TYPE DEFAULT 1000, allow empl.allow%TYPE, deptno empl.dep