oracle過程、函數(shù)和程序包

1、過程、函數(shù)和包目標在完成本本章學(xué)習習后，你你將掌握握下列技技能；PL/SQL過程的定定義與用用法PL/SQL函數(shù)的定定義與用用法PL/SQL包的定義義與用法法本章內(nèi)容容PL/SQL過程PL/SQL函數(shù)PL/SQL包導(dǎo)航PL/SQL過程PL/SQL函數(shù)PL/SQL包PL/SQL過程過程是指指用于執(zhí)執(zhí)行特定定操作的的PL/SQL塊通過使用用過程，不僅可可以簡化化客戶應(yīng)應(yīng)用的開開發(fā)和維維護，而而且可以以提高應(yīng)應(yīng)用程序序的運行行性能。語法：CREATEORREPLACEPROCEDUREprocedure_name(argument1mode1 datatype1,argument2mode2 dat

2、atype2,)IS|ASPL/SQLBlock;其中procedure_name指定過程程名，argument指定過程程參數(shù)；IS或AS用于開始始PL/SQL塊。PL/SQL過程-建立無參參數(shù)的過過程下面以建建立用于于輸出當當前日期期和時間間的過程程out_time為例。CREATEORREPLACEPROCEDUREout_timeISBEGINDBMS_SESSION.SET_NLS(NLS_DATE_FORMAT,YYYY-MM-DD HH24:MI:SS);DBMS_OUTPUT.PUT_LINE(sysdate);END;/PL/SQL過程-建立帶有有輸入?yún)?shù)的過過程通過使用用輸

3、入?yún)?shù)，可可以將應(yīng)應(yīng)用程序序數(shù)據(jù)傳傳遞到過過程。當定義過過程參數(shù)數(shù)時，默認參數(shù)數(shù)模式就就是輸入入?yún)?shù)可以使用用IN關(guān)鍵字顯顯式定義義輸入?yún)?shù)。CREATEORREPLACEPROCEDUREadd_empl(empnoempl.empno%TYPE,enameempl.ename%TYPE,ebirthdayempl.ebirthday%TYPE,egradeempl.egrade%TYPE,ejob empl.ejob%TYPE,emajorempl.emajor%TYPE,indateempl.indate%TYPE,salaryempl.salary%TYPE,allowempl.a

4、llow%TYPE,deptno empl.deptno%TYPE) ISBEGININSERTINTOempl VALUES(empno,ebirthday,ename,ejob,egrade,emajor,indate,salary,allow,depno);END;PL/SQL過程-建立帶有有輸出參參數(shù)的過過程通過使用用輸入?yún)?shù)，可可以將數(shù)數(shù)據(jù)或消消息傳遞遞到調(diào)用用環(huán)境和和應(yīng)用程程序。當定義輸輸出參數(shù)數(shù)時，需需要制定定參數(shù)模模式為OUT。下面建立立用于更更新雇員員工資并并輸出雇雇員姓名名的過程程update_sal為例。CREATEORREPLACEPROCEDUREupdate_sa

5、l(enovarhcar2, salNUMBER, nameOUT VARCHAR2)ISBEGINUPDATEemplSETsalary=salWHEREempno=enoRETURNINGenameINTOname;END;/PL/SQL過程-建立帶有有輸入輸輸出參數(shù)數(shù)的過程程通過在過過程中使使用輸入入輸出函函數(shù)，可可以調(diào)用用過程時時輸入數(shù)數(shù)據(jù)到過過程，在在調(diào)用結(jié)結(jié)束后輸輸出數(shù)據(jù)據(jù)到調(diào)用用環(huán)境和和應(yīng)用程程序。當定義輸輸入輸出出參數(shù)時時，需要要指定參參數(shù)模式式為INOUT。CREATEORREPLACEPROCEDUREdevide(num1 IN OUTNUMBER，num2 IN OUT

6、NUMBER)ISv1NUMBER;v2NUMBER;BEGINv1:=TRUNC(num1/num2);v2:=MOD(num1,num2);num1:=v1;num2:=v2;END;/PL/SQL過程-調(diào)用過程程在SQL*PLUS中調(diào)用過過程時，需要使使用CALL或者EXECUTE命令；而而在PL/SQL塊中過程程可以直直接應(yīng)用用。過程調(diào)用用帶參數(shù)數(shù)分4種情況：如果無參參數(shù)，那那么可以以直接引引用過程程名；如果有輸輸入?yún)?shù)數(shù)，那么么需要為為輸入?yún)?shù)提供供數(shù)值；如果帶有有輸出參參數(shù)，那那么需要要使用變變量接收收輸出結(jié)結(jié)果；如果有輸輸入輸出出參數(shù)，那么調(diào)調(diào)用時需需要使用用具有輸輸入值的的變

7、量。PL/SQL過程-調(diào)用無參參數(shù)的過過程直接引用用過程名名exec out_timePL/SQL過程-調(diào)用帶有有輸入?yún)?shù)的過過程需要為輸輸入?yún)?shù)數(shù)提供數(shù)數(shù)據(jù)值execadd_empl(1111,1980-3-4,lei,programmer,1,7369,SYSDATE,800, null, 30)PL/SQL過程-調(diào)用帶有有輸出參參數(shù)的過過程需要使用用變量接接受輸出出參數(shù)的的數(shù)據(jù)值值DECLAREv_nameempl.ename%TYPE;BEGINupdate_sal(&eno,&salary,v_name);DBMS_OUTPUT.PUT_LINE(name:|v_name);END

8、;/輸入雇雇員號的的值：1111輸入工資資的值：2500姓名：leiPL/SQL過程-調(diào)用帶有有輸入輸輸出參數(shù)數(shù)的過程程調(diào)用之前前需要定定義變量量存放輸輸入值，并在調(diào)調(diào)用結(jié)束束之后使使用變量量輸出數(shù)數(shù)據(jù)。DECLAREn1NUMBER:=&n1;n2NUMBER:=&n2;BEGINdivide(n1,n2);DBMS_OUTPUT.PUT_LINE(商:|n1|,余數(shù):|n2);END; /輸入n1的值：100輸入n2的值：30商：3，余數(shù)10為參數(shù)傳傳遞變量量或數(shù)據(jù)據(jù)當為參數(shù)數(shù)傳遞變變量或數(shù)數(shù)據(jù)，可可以采用用以下3種方法位置傳遞遞名稱傳組合傳遞遞PL/SQL過程-使用位置置傳遞為為參數(shù)傳傳

9、遞變量量和數(shù)據(jù)據(jù)位置傳遞遞是指按照照參數(shù)定定義順序序依次為為每個參參數(shù)提供供變量或或者數(shù)據(jù)據(jù)值當使用位位置傳遞遞時，必須為所所有參數(shù)數(shù)提供變變量或者者輸入數(shù)數(shù)據(jù)CALL add_empl(2222,1978-12-2， alex,NULL,1，NULL,SYSDATE,800,NULL,30);PL/SQL過程-使用名稱稱傳遞為為參數(shù)傳傳遞變量量和數(shù)據(jù)據(jù)名稱傳遞遞是指通過過制定參參數(shù)名為為其提供供變量或或者數(shù)據(jù)據(jù)當使用名名稱傳遞遞時，需需要使用用=為參數(shù)提提供變量量或者數(shù)數(shù)值exec add_emp(empno=3333,ebirghday=,1989-2-2，indate=null, egr

10、ade=1,ename=JOHN,ejob=null,major=null,salary=null,allow=null,deptno=null)PL/SQL過程-使用組合合傳遞為為參數(shù)傳傳遞變量量和數(shù)據(jù)據(jù)組合傳遞遞是指在調(diào)調(diào)用子程程序時結(jié)結(jié)合使用用位置傳傳遞和名名稱傳遞遞exec add_emp(4444,1985-2-3,AGASI ,tester,1,null,indate=SYSDATE,salary=1200, allow=0,deptno=30)PL/SQL過程-過程開發(fā)發(fā)示例為過程參參數(shù)指定定默認值值CREATEORREPLACEPROCEDUREadd_emp(empnoemp

11、l,empno%TYPE, ebirthdayempl.ebirthday%TYPE,enameempl.ename%TYPE, ejobempl.ejob%TYPEDEFAULTprogrammer,egradeempl.egrade%TYPEDEFAULT 1,majorempl.mgr%TYPE,indateempl.indate%TYPEDEFAULT SYSDATE, salaryempl.salary%TYPE DEFAULT1000,allowempl.allow%TYPE DEFAULT0,deptno empl.deptno%TYPE)ISBEGININSERTINTOemp

12、VALUES(empno,ebirthday,ename,ejob,egrademajor,indate,salary,allow, deptno);END;/PL/SQL過程-使用異常常處理CREATEORREPLACEPROCEDUREupdate_sal(name empl.ename%TYPE,sal empl.salary%TYPE) IS e_no_rowsEXCEPTION;BEGINUPDATEemplSET salary=salWHERELOWER(ename)=LOWER(name);IFSQL%NOTFOUNDTHENRAISEe_no_rows;ENDIF;EXCEPT

13、IONWHEN e_no_rowsTHENDBMS_OUTPUT.PUT_LINE(這雇員不不存在);END;Execupdate_sal(jarbus,1500)這雇員不不存在Execupdate_sal(allen,1500)PL/SQL過程-維護過程程刪除過程程DROPPROCEDURE顯示編譯譯錯誤，查詢數(shù)數(shù)據(jù)字典典USER_ERRORS，或者執(zhí)執(zhí)行SHOW ERRORS命令。確定過程程狀態(tài),查詢數(shù)據(jù)據(jù)字典USER_OBJECTS。編譯過程程，ALTERPROCEDURECOMPILE;查看過程程 代碼碼，通過過查詢數(shù)數(shù)據(jù)字典典USER_SOURCE。導(dǎo)航PL/SQL過程PL/SQL函

14、數(shù)PL/SQL包函數(shù)函數(shù)是一一種命名名存儲塊塊，可以以接收輸輸入并進進行定義義的邏輯輯處理以以后把結(jié)結(jié)果返回回給調(diào)用用者。與過程相相似，只只是函數(shù)數(shù)必須有有返回值值。函數(shù)-創(chuàng)建基本語法法CREATEOR REPLACE FUNCTIONfunction_name (parameterIN|INOUTdate_type1,parameterIN|INOUTdate_type2,RETURNreturn_type;IS|ASdeclarationsectionBEGINexecutable statementsEXCEPTIONexceptionhandlers;ENDfunction_name;

15、函數(shù)-例子 下列是求求從1到某個數(shù)數(shù)累加的的例子CREATEOR REPLACEFUNCTIONsum(nINnumber)RETURNnumberASresultnumber:=0;BEGINFORiin1nLOOPresult:=result+i;ENDLOOP;return(result);ENDsum;函數(shù)-調(diào)用調(diào)用函數(shù)數(shù)的時候候需要保保存返回回值例子DECLAREnum1 number:=10;sum1 number:=0;BEGINsum1:=sum(num1);DBMS_OUTPUT.PUT_LINE(“result=“|sum1);導(dǎo)航PL/SQL過程PL/SQL函數(shù)PL/SQ

16、L包PL/SQL包包用于邏邏輯組合合相關(guān)的的自定義義類型、常量、變量、游標、過程和和函數(shù)。簡化了客客戶應(yīng)用用開發(fā)，提高性性能，實實現(xiàn)信息息隱藏、子程序序重載等等功能。PL/SQL包-規(guī)范包規(guī)范用用于定義義包的公公用組件件，包括括常量、變量、游標、自定義義類型、過程和和函數(shù)等等。建立包規(guī)規(guī)范語法法CREATEOR REPLACE PACKAGEpackage_nameIS|AS-定義公用常量量、變量量等-定義公用過程程和函數(shù)數(shù)等。ENDpackage_name;PL/SQL包-例子CREATEORREPLACEPACKAGE emp_package ISg_deptnoempl.deptno%T

17、YPE:=30;PROCEDUREadd_employee(empnoempl.empno%TYPE, ebirthdayempl.ebirthday%TYPE,enameempl.ename%TYPE, ejobempl.ejob%TYPEDEFAULTprogrammer, egradeempl.egrade%TYPE,major empl.mgr%TYPE, indateempl.indate%TYPE DEFAULTSYSDATE,salary empl.salary%TYPEDEFAULT1000,allowempl.allow%TYPE, deptnoempl.deptno%TYP

18、E DEFAULTg_deptno);PROCEDUREfire_employee(enoNUMBER);FUNCTIONget_sal(enoNUMBER) RETURNNUMBER;ENDemp_package;PL/SQL包-建立包體體包體用于于實現(xiàn)包包規(guī)范所所定義的的公用過過程和函函數(shù)，包包體不僅僅可用與與實現(xiàn)公公用過程程和函數(shù)數(shù)，而且且可以定定義包的的私有組組件（變變量、常常量等）建立包體體語法CREATEOR REPLACE PACKAGEBODYpackage_nameIS|AS-定義私有有變量、常量等等-實現(xiàn)公用過程程和函數(shù)數(shù)ENDpackage_name;PL/SQL包-例子

19、CREATEORREPLACEPACKAGE BODYemp_packageISFUNCTIONvalidate_deptno(dno empl.deptno%TYPE)RETURNBOOLEANIStemp INT;BEGINSELECT1INTO tempFROMdept WHERE deptno=dno;RETURNTRUE;EXCEPTIONWHEN NO_DATA_FOUNDTHENRETURNFALSE;END;PROZCEDURE add_employee(empnoempl.empno%TYPE, ebirthdayempl.ebirthday%TYPE,enameempl.ename%TYPE, ejobempl.ejob%TYPEDEFAULTprogrammer, egradeempl.egrade%TYPE,major empl.mgr%TYPE, indateempl.indate%TYPE DEFAULTSYSDATE,salary empl.salary%TYPEDEFAULT1000,allowempl.allow%TYPE, deptnoempl.deptno%TYPE DEFAULTg_deptno);ISBEGINI