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

專題七過程、函數(shù)和程序包

回顧游標(biāo)就是指向上下文區(qū)的句柄或指針。游標(biāo)有兩種類型：顯式游標(biāo)、隱式游標(biāo)。四個游標(biāo)屬性：SQL％FOUND、SQL％NOTFOUND、SQL％ROWCOUNT、SQL％ISOPEN顯式游標(biāo)的使用步驟：４個記錄變量和%ROWTYPE帶參數(shù)的游標(biāo)游標(biāo)FOR循環(huán)（循環(huán)游標(biāo)）游標(biāo)中的更新和刪除REF游標(biāo)教學(xué)目標(biāo)掌握過程的用法掌握函數(shù)的用法理解過程與函數(shù)的相同點(diǎn)和不同點(diǎn)理解程序包的概念并能熟練應(yīng)用工作任務(wù)

用無參過程實現(xiàn)“HelloWorld！”程序用帶輸入?yún)?shù)的過程向表中插入記錄用帶輸出參數(shù)的過程查詢表中的記錄數(shù)使用帶輸入輸出參數(shù)的過程查詢記錄是否存在使用函數(shù)查詢部門信息使用程序包封裝過程和函數(shù)相關(guān)實踐知識從開始菜單中打開SQL*Plus工具，以SCOTT用戶的身份登錄到數(shù)據(jù)庫

不帶參數(shù)的過程2-1輸入以下代碼，創(chuàng)建一個最簡單的過程功能:顯示”HelloWorld!”CREATEORREPLACEPROCEDUREsp_helloWorldASBEGIN DBMS_OUTPUT.PUT_LINE('HelloWorld!');ENDsp_helloWorld;不帶參數(shù)的過程2-2執(zhí)行過程EXECUTEsp_helloWorld;執(zhí)行結(jié)果:刪除過程DROPPROCEDUREsp_helloWorld;帶輸入?yún)?shù)的過程3-1實現(xiàn)的功能：向表dept中插入一條記錄創(chuàng)建帶輸入?yún)?shù)的過程，代碼為：帶輸入?yún)?shù)的過程3-2CREATEORREPLACEPROCEDUREsp_dept_insert(i_deptnoNUMBER,i_dnameVARCHAR2,i_locVARCHAR2)ASBEGININSERTINTOdeptVALUES(i_deptno,i_dname,i_loc);COMMIT;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('添加失敗!原因為:'||SQLERRM);ROLLBACK;ENDsp_dept_insert;帶輸入?yún)?shù)的過程3-3分別輸入兩組數(shù)據(jù)來執(zhí)行過程，結(jié)果如下：帶輸出出參數(shù)數(shù)的過過程3-1實現(xiàn)功功能：：通過過輸出出參數(shù)數(shù)count來來得到到dept表中中的記記錄數(shù)數(shù)創(chuàng)建帶帶輸出參參數(shù)的過程程CREATEORREPLACEPROCEDUREsp_getcount(o_countOUTNUMBER)ASBEGINSELECTCOUNT(*)INTOo_countFROMdept;ENDsp_getcount;帶輸出出參數(shù)數(shù)的過過程3-2編寫一一段匿匿名的的PL/SQL塊來來執(zhí)行行過程程DECLAREcntNUMBER;BEGINsp_getcount(cnt);DBMS_OUTPUT.PUT_LINE('dept表表中的的記錄錄數(shù)為為:'||cnt);END;帶輸出出參數(shù)數(shù)的過過程3-3輸出結(jié)結(jié)果：：帶INOUT參數(shù)數(shù)的過過程2-1實現(xiàn)功功能:查詢詢某個個部門門名稱稱在表表dept中是是否已已經(jīng)存存在創(chuàng)建帶帶INOUT參參數(shù)的過程程，代代碼為為：CREATEORREPLACEPROCEDUREsp_dept_dname_exist(io_valueINOUTVARCHAR2)ISl_countNUMBER;BEGINSELECTCOUNT(*)INTOl_countFROMdeptWHEREdname=io_value;IF(l_count>0)THENio_value:='存在在';ELSEio_value:='不存存在';ENDIF;ENDsp_dept_dname_exist;帶INOUT參數(shù)數(shù)的過過程2-2執(zhí)行過過程：：輸出結(jié)結(jié)果：：部門名名稱ACCOUNTING存存在!DECLAREl_iotestvarchar2(20):='ACCOUNTING';BEGINsp_dept_dname_exist(l_iotest);DBMS_OUTPUT.PUT_LINE('部部門名名稱ACCOUNTING'||l_iotest||'!');END;函數(shù)4-1實現(xiàn)功功能：：按部門門編號號查詢詢出表表dept中的的部門門名稱稱創(chuàng)建一一個函數(shù)，代碼碼為：：函數(shù)4-2CREATEORREPLACEFUNCTIONf_dept_getname_byno(i_deptnoNUMBER)RETURNVARCHAR2ASl_dnameVARCHAR2(14);BEGINSELECTdnameINTOl_dnameFROMdeptWHEREdeptno=i_deptno;RETURNl_dname;EXCEPTIONWHENNO_DATA_FOUNDTHENRETURN'錯錯誤!該編編號的的部門門不存存在!';ENDf_dept_getname_byno;函數(shù)4-3函數(shù)的的調(diào)用用及其輸輸出結(jié)結(jié)果：：函數(shù)4-4刪除函函數(shù)DROPFUNCTIONf_dept_getname_byno過程與與函數(shù)數(shù)小結(jié)結(jié)2-1共同點(diǎn)點(diǎn)：兩兩者的的實質(zhì)質(zhì)都是是已命名的的PL/SQL程序序塊，即子程序序，它們們是子子程序序的兩兩種類類型，，存儲儲在數(shù)數(shù)據(jù)庫庫中，，可以以從任任何數(shù)數(shù)據(jù)庫庫客戶戶端和和前臺臺應(yīng)用用程序序中調(diào)調(diào)用它它們。。過程與與函數(shù)數(shù)小結(jié)結(jié)2-2不同點(diǎn)點(diǎn)：過程函數(shù)參數(shù)模式可以是IN、OUT或INOUT參數(shù)模式只能是IN模式在語法規(guī)范中不包含RETURN子句在語法規(guī)范中必須包含RETURN子句在可執(zhí)行語句部分可以有RETURN語句，但其后不能加任何表達(dá)式在可執(zhí)行語句部分至少應(yīng)該包含一條RETURNexpression語句可以用EXECUTE語句來執(zhí)行不能用EXECUTE語句來執(zhí)行程序包包5-1利用程序包包封裝過過程sp_dept_insert和和函數(shù)數(shù)f_dept_getname_byno程序包包規(guī)范范部分分的代代碼：：CREATEORREPLACEPACKAGEpkg_deptASPROCEDUREsp_dept_insert(i_deptnoNUMBER,i_dnameVARCHAR2,i_locVARCHAR2);FUNCTIONf_dept_getname_byno(i_deptnoNUMBER)RETURNVARCHAR2;ENDpkg_dept;程序包包5-2程序包包主體體部分分的代代碼CREATEORREPLACEPACKAGEBODYpkg_deptAS--過過程sp_dept_insertPROCEDUREsp_dept_insert(i_deptnoNUMBER,i_dnameVARCHAR2,i_locVARCHAR2)ASBEGININSERTINTOdeptVALUES(i_deptno,i_dname,i_loc);COMMIT;EXCEPTIONWHENOTHERSTHENDBMS_OUTPUT.PUT_LINE('添添加失失敗!原因因為:'||SQLERRM);ROLLBACK;ENDsp_dept_insert;程序包包5-3--函函數(shù)f_num_rangeFUNCTIONf_dept_getname_byno(i_deptnoNUMBER)RETURNVARCHAR2ASl_dnameVARCHAR2(14);BEGINSELECTdnameINTOl_dnameFROMdeptWHEREdeptno=i_deptno;RETURNl_dname;EXCEPTIONWHENNO_DATA_FOUNDTHENRETURN'錯錯誤!該編編號的的部門門不存存在!';ENDf_dept_getname_byno;ENDpkg_dept;程序包包5-4執(zhí)行程程序包包中的的過程程和函函數(shù)：程序包包5-5刪除程程序包包：只刪除除程序序包主主體：：DROPPACKAGEBODYpkg_dept;刪除整整個程程序包包（規(guī)規(guī)范+主體體）：：DROPPACKAGEpkg_dept;小結(jié)：：程序序包的的優(yōu)點(diǎn)點(diǎn)使用程程序包包的優(yōu)優(yōu)點(diǎn)：：信息隱隱藏模塊化化對多態(tài)態(tài)的支支持性能更更佳過程返返回結(jié)結(jié)果集集2-1在Oracle中的的過程程不能能象SQLSERVER那那樣直直接返返回結(jié)結(jié)果集集，而而必須須借助助于REF游標(biāo)標(biāo)程序包包規(guī)范范中的的代碼碼:CREATEORREPLACEPACKAGEpkg_deptASTYPEdeptcursorISREFCURSOR;PROCEDUREsp_dept_getall(dept_curOUTdeptcursor);ENDpkg_dept;過程返返回結(jié)結(jié)果集集2-2程序包包主體體中的的代碼碼:執(zhí)行過過程:CREATEORREPLACEPACKAGEBODYpkg_deptASPROCEDUREsp_dept_getall(dept_curOUTdeptcursor)ISBEGINOPENdept_curFORSELECT*FROMdept;ENDsp_dept_getall;ENDpkg_dept;VARIABLEtest_curREFCURSOR;EXECUTEpkg_dept.sp_dept_getall(:test_cur);PRINTtest_cur;OEM中管管理過過程、、函數(shù)數(shù)、程程序包包請老師師用瀏瀏覽器器打開開OEM，，演示示在OEM中管理過過程、、函數(shù)數(shù)、程程序包包總結(jié)過程和和函數(shù)數(shù)都是是命名名的PL/SQL程程序塊塊，合合稱子子程序序過程有有三種種參數(shù)數(shù)模式式：IN、、OUT和和INOUT過程用用戶執(zhí)執(zhí)行特特定的的任務(wù)務(wù)，函函數(shù)用用于執(zhí)執(zhí)行任任務(wù)并并返回回值過程與與函數(shù)數(shù)的相相同點(diǎn)點(diǎn)和不不同點(diǎn)點(diǎn)程序包包是對對過程程、函函數(shù)、、變量量、常常量、、游標(biāo)標(biāo)、異異常及及PL/SQL數(shù)據(jù)據(jù)類型型等的的封裝裝程序包包由兩兩部分分組成成，即即包規(guī)規(guī)范和和包主主體使用程程序包包的優(yōu)優(yōu)點(diǎn)過程的的定義義定義：：過程是是一組組為了了完成特特定功功能的、符符合數(shù)數(shù)據(jù)庫庫程序序腳本本規(guī)范范的程程序，，經(jīng)編譯后后存儲在在數(shù)據(jù)據(jù)庫中中，然然后由由一個個應(yīng)用用程序序或其其他的的PL/SQL程序序調(diào)用用。從從根本本上講講，過過程就就是命名的的PL/SQL程序序塊。創(chuàng)建過過程的的語法法CREATE[ORREPLACE]PROCEDUREprocedure_name[(parameter_list)]{AS|IS}[local_declarations]BEGINexecutable_statements[EXCEPTIONexception_handlers]END[procedure_name];執(zhí)行過過程在SQL提提示符符下：：EXEC[UTE]procedure_name(parameters_list)在其它它的過過程、、函數(shù)數(shù)或匿匿名的的PL/SQL塊中中的可可執(zhí)行行語句句部分分執(zhí)行行過程程，直直接寫寫過程程的名名稱（（如有有參數(shù)數(shù)需帶帶上））即可可刪除過過程刪除過過程的的語法法:DROPPROCEDUREprocedure_name;過程的的參數(shù)數(shù)模式式Oracle中中過過程的的參數(shù)數(shù)模式式有三三種：：IN、OUT和INOUT，，即即輸入入、輸輸出和和輸入入輸出出定義參參數(shù)的的語法法：parameter_name[IN|OUT|INOUT]DATATYPE[{:=|DEFAULT}expression]IN模模式參參數(shù)IN模模式是是輸入入模式式，可可以傳傳遞輸輸入?yún)?shù)IN模模式是是默認(rèn)認(rèn)模式式，如如果未未指定定參數(shù)數(shù)的模模式，，則該該參數(shù)數(shù)是IN模模式的的可以在在參數(shù)數(shù)列表表中為為IN參數(shù)數(shù)賦予予一個個默認(rèn)認(rèn)值OUT模式式參數(shù)數(shù)OUT模式式是輸輸出模模式，，可以以傳遞遞輸出出參數(shù)數(shù)OUT模式式的參參數(shù)，，則必必須明明確指指定，，即即OUT必必須注注明在返回回到調(diào)調(diào)用環(huán)環(huán)境之之前，，應(yīng)該該先給給OUT模模式的的參數(shù)數(shù)賦值值不能為為OUT模模式的的參數(shù)數(shù)賦默默認(rèn)值值INOUT模模式參參數(shù)INOUT模模式是是一種種比較較特殊殊的模模式，，它兼兼有IN模模式和和OUT模模式的的特點(diǎn)點(diǎn)INOUT模模式的的參數(shù)數(shù)，則則必須須明確確指定定不能為為INOUT模式式的參參數(shù)賦賦默認(rèn)認(rèn)值函數(shù)的的定義義定義函數(shù)的的實質(zhì)質(zhì)也是是數(shù)據(jù)據(jù)庫中中已命名的的PL/SQL程序序塊。它的的主要要特性性是函函數(shù)能且只只能返返回一一個值值。創(chuàng)建函函數(shù)的的語法法CREATE[ORREPLACE]FUNCTIONfunction_name[(parameter_list)]RETURNDATATYPE{AS|IS}[local_declarations]BEGINexecutable_statements[EXCEPTIONexception_handlers]END[function_name];函數(shù)定定義的的要點(diǎn)點(diǎn)創(chuàng)建函函數(shù)時時通過過RETURN子句句來定定義函函數(shù)的的返回回類型型在函數(shù)數(shù)體的的任何何地方方用戶戶都可可以通通過RETURNexpression語語句從從函數(shù)數(shù)返回回，但但表達(dá)達(dá)式的的類型型一定定要與與RETURN子句句中定定義的的數(shù)據(jù)據(jù)類型型一致致。函數(shù)的的參數(shù)數(shù)模式式只能能是IN模模式的的，而而不能能是OUT或INOUT模模式的的。調(diào)用函函數(shù)在SQL提提示符符下：：SELECTfunction_namefromdual;在其它它的過過程、、函數(shù)數(shù)或匿匿名的的PL/SQL塊中中的可可以用用一個個與函函數(shù)的的返回回類型型相同同的變變量來來接收收該函函數(shù)不能用用EXECUTE語語句來來調(diào)用用函數(shù)數(shù)刪除函函數(shù)刪除函函數(shù)的的語法法：DROPFUNCTIONfunction_name;程序包包程序包包是對對過程程、函函數(shù)、、變量量、常常量、、游標(biāo)標(biāo)、異異常及及PL/SQL數(shù)據(jù)據(jù)類型型等的的封裝裝，是是一種種數(shù)據(jù)據(jù)庫對對象。。它由兩