開發(fā)子程序和包(修改后).ppt

ORACLE數(shù)據(jù)庫查詢和管理,第10章開發(fā)子程序和包,程序塊的語法結(jié)構(gòu)：數(shù)據(jù)類型條件控制語句循環(huán)結(jié)構(gòu)異常處理,內(nèi)容回顧,%type%rowtype%record%table,DECLARE定義部分BEGIN執(zhí)行部分EXCEPTION異常處理部分END;,IF條件表達(dá)式1THEN語句段1ELSIF條件表達(dá)式2THEN語句段2ELSIF條件表達(dá)式3THEN語句段3.ELSIF條件表達(dá)式n語句段nENDIF;,CASE表達(dá)式WHEN條件表達(dá)式結(jié)果1THEN語句段1;WHEN條件表達(dá)式結(jié)果2THEN語句段2;.ELSE語句段n;ENDCASE;,LOOP語句段;EXITWHEN條件表達(dá)式ENDLOOP;,WHILE條件表達(dá)式LOOP語句段;ENDLOOP;,EXCEPTIONWHEN異常錯誤名稱1THEN語句段1;WHEN異常錯誤名稱2THEN語句段2;.WHENOTHERSTHEN語句段3;,FOR循環(huán)變量in初值表達(dá)式.終值表達(dá)式LOOP語句段;ENDLOOP;,本章內(nèi)容,過程函數(shù)程序包,本章目標(biāo),掌握過程的創(chuàng)建與使用掌握函數(shù)的創(chuàng)建與使用掌握包的創(chuàng)建與使用,1子程序簡介,子程序是指被命名的PL/SQL塊，這種塊可以帶有參數(shù)，可以在不同應(yīng)用中多次調(diào)用,類似于java中的函數(shù)PL/SQL有兩種類型的子程序：過程和函數(shù)過程用于執(zhí)行特定操作，不返回數(shù)據(jù)函數(shù)則用于返回特定數(shù)據(jù),2過程,過程一般用于執(zhí)行一個指定的操作，可以將常用的特定操作封裝成過程創(chuàng)建過程：,CREATEORREPLACEPROCEDUREprocedure_name(argument1mode1datatype1,argument2mode2datatype2,.)ISAS聲明部分BEGIN執(zhí)行部分EXCEPTION異常處理部分END;,創(chuàng)建過程：無參數(shù)創(chuàng)建一個無參過程，完成修改獎金的操作執(zhí)行過程的三種方式,createtabletb_test(Avarchar2(10),Bvarchar2(10);insertintotb_testvalues(aa,bb);insertintotb_testvalues(aa,cc);insertintotb_testvalues(bb,cc);insertintotb_testvalues(aa,bb);insertintotb_testvalues(aa,cc);insertintotb_testvalues(bb,cc);select*fromtb_test,CREATEORREPLACEPROCEDUREproc_del_dup_recASBEGINDELETETB_TESTaWHEREa.rowid=(SELECTMAX(ROWID)FROMTB_TESTbWHEREa.a=b.aANDa.b=b.b);END;/,callproc_del_dup_rec();-execproc_del_dup_rec;-beginproc_del_dup_rec;end;,創(chuàng)建過程：帶有IN參數(shù)當(dāng)為過程定義參數(shù)時，如果不指定參數(shù)模式，則默認(rèn)為輸入?yún)?shù)根據(jù)輸入的員工編號輸出該員工的工資,創(chuàng)建過程時，無需為參數(shù)指定長度，因?yàn)檫^程中的參數(shù)長度最終是由傳遞給參數(shù)的外部數(shù)據(jù)長度來決定的,createorreplaceprocedurepro_query_emp(v_noinemp.empno%type)asv_salemp.sal%type;beginselectsalintov_salfromempwhereempno=v_no;dbms_output.put_line(該員工薪水為：|v_sal);exceptionwhenno_data_foundthendbms_output.put_line(找不到該員工！);end;,聲明輸入?yún)?shù)v_no,使用輸入?yún)?shù)v_no,beginpro_query_emp(7369);end;,創(chuàng)建過程：帶有OUT參數(shù)過程不僅可以用于執(zhí)行特定操作，還可以用于輸出數(shù)據(jù)在過程中輸出數(shù)據(jù)時，需要使用OUT或INOUT參數(shù)來完成修改剛才的過程讓員工工資作為輸出參數(shù),createorreplaceprocedurepro_query_emp(v_noinemp.empno%type,out_saloutnumber)asbeginselectsalintoout_salfromempwhereempno=v_no;exceptionwhenno_data_foundthendbms_output.put_line(找不到該員工！);end;,聲明輸出參數(shù)out_sal,給輸出參數(shù)out_sal賦值,調(diào)用帶有OUT參數(shù)的過程必須定義變量接收輸出參數(shù)的數(shù)據(jù),declarev_noemp.empno%type;v_salemp.sal%type;beginv_no:=,定義輸入和輸出參數(shù),創(chuàng)建過程：帶有INOUT參數(shù)INOUT參數(shù)也稱為輸入輸出參數(shù)，當(dāng)使用這種參數(shù)時，在調(diào)用過程之前需要通過變量給該種參數(shù)傳遞數(shù)據(jù)，調(diào)用結(jié)束后，Oracle會通過該變量將過程結(jié)果傳遞給應(yīng)用,createorreplaceprocedurepro_testinout(param_numinoutnumber)asbeginselectsalintoparam_numfromempwhereempno=param_num;end;,定義輸入、輸出參數(shù),調(diào)用帶INOUT參數(shù)的過程：,declareinout_numnumber;begininout_num:=,輸入時帶入編號、輸出時是工資的值。,過程調(diào)用時的多參數(shù)傳遞,使用過程時多參傳遞為形參傳遞變量和數(shù)據(jù)可以采用位置傳遞名稱傳遞組合傳遞,createorreplaceprocedurepro_add_dept(v_deptnonumber,v_dnamevarchar2,v_locvarchar2)asbegininsertintodeptvalues(v_deptno,v_dname,v_loc);commit;end;,按位置傳遞按位置傳遞是指在調(diào)用時按參數(shù)的排列順序依次寫出實(shí)參的名稱，將形參與實(shí)參關(guān)聯(lián)起來進(jìn)行傳遞在這種方法中，形參與實(shí)參的名稱是相互獨(dú)立、沒有關(guān)系的，次序才重要,execpro_add_dept(70,研發(fā)部,北京);,按照參數(shù)定義的順序傳入實(shí)參的值。,注意在oraclesqldeveloper中報“無效SQL語句異常”。只能用call調(diào)用，而在命令窗口正常。,按名稱傳遞按名稱傳遞是指在調(diào)用時按照形參與實(shí)參的名稱寫出實(shí)參所對應(yīng)的形參，將形參與實(shí)參關(guān)聯(lián)起來進(jìn)行傳遞在這種方法中，形參與實(shí)參的名稱是相互獨(dú)立、沒有關(guān)系的，名稱的對應(yīng)關(guān)系很重要，但次序不重要名稱傳遞在調(diào)用子程序時指定參數(shù)名，并使用關(guān)聯(lián)符號“=”為其提供相應(yīng)的數(shù)值或變量,callpro_add_emp(v_deptno=90,v_loc=南京,v_dname=軟件部);,按照形參的名稱賦值。,組合傳遞可以將按位置傳遞、按名稱傳遞兩種方法在同一調(diào)用中混合使用但前面的實(shí)參必須使用按位置傳遞方法，而后面其余的實(shí)參則可以使用按名稱傳遞的方法,callpro_add_emp(90,v_loc=南京,v_dname=軟件部);,第一個按形參位置傳遞，后面的按照形參的名稱賦值。,3函數(shù),函數(shù)用于返回特定數(shù)據(jù)，如果在應(yīng)用程序中經(jīng)常需要通過執(zhí)行SQL語句來返回特定數(shù)據(jù)，則可以基于這些操作創(chuàng)建特定的函數(shù),CREATEORREPLACEFUNCTIONfunction_name(argument1modeldatatype1,argument2mode2datatype2,.)RETURNdatatypeIS|AS聲明部分BEGIN執(zhí)行部分EXCEPTION異常處理部分END;,創(chuàng)建函數(shù),創(chuàng)建函數(shù)：當(dāng)創(chuàng)建函數(shù)時，通過使用輸入?yún)?shù)，可以將應(yīng)用的數(shù)據(jù)傳遞到函數(shù)中，最終通過執(zhí)行函數(shù)可以將結(jié)果返回到應(yīng)用程序中當(dāng)定義參數(shù)時，如果不指定參數(shù)模式，則默認(rèn)為輸入?yún)?shù),createorreplacefunctionfun_getrandomreturnnumberasv_numnumber;-存儲返回值beginv_num:=floor(dbms_random.value(1,10);-產(chǎn)生隨機(jī)數(shù)returnv_num;-返回隨機(jī)數(shù)end;,declarenumnumber;beginnum:=fun_getrandom();dbms_output.put_line(num);end;,創(chuàng)建帶輸入?yún)?shù)的函數(shù)：通過輸入員工編號獲得員工所在的部門名稱,createfunctiongetDept(enonumber)returnVARCHAR2asdeptNamedept.dname%type;beginselectdNameintodeptNamefromdept,empwheredept.deptno=emp.deptnoandempno=eno;returndeptname;end;,declaredeptNameVARCHAR2(30);begindeptName:=getDept(7654);dbms_output.put_line(deptname);end;,selectgetDept(7788)fromdual,過程與函數(shù)的比較,過程與函數(shù)有許多相同的功能及特性都使用IN模式的參數(shù)傳入數(shù)據(jù)、OUT模式的參數(shù)返回數(shù)據(jù)輸入?yún)?shù)都可以接收默認(rèn)值，都可以傳值調(diào)用時的實(shí)參都可以使用位置表示法或名稱表示法都有聲明部分、執(zhí)行部分和異常處理部分一般而言，如果需要返回多個值或不返回值，就使用過程如果只需要返回一個值，就使用函數(shù)雖然函數(shù)帶OUT模式的參數(shù)也能返回多個值，但是一般都認(rèn)為這種方法屬于不好的編程習(xí)慣或風(fēng)格過程一般用于執(zhí)行一個指定的動作，函數(shù)一般用于計算和返回一個值,4包,創(chuàng)建包包（Package）用于組合邏輯相關(guān)的PL/SQL類型、PL/SQL項(xiàng)和PL/SQL子程序通過使用PL/SQL包，不僅可以簡化應(yīng)用設(shè)計，提高應(yīng)用性能，還可以實(shí)現(xiàn)信息隱藏、子程序重載等功能包由包規(guī)范和包體兩部分組成當(dāng)創(chuàng)建包時，需要首先創(chuàng)建包規(guī)范，然后再創(chuàng)建包體,創(chuàng)建包,創(chuàng)建包規(guī)范包規(guī)范是包與應(yīng)用程序之間的接口，用于定義包的公用組件，包括常量、變量、游標(biāo)、過程和函數(shù)在包規(guī)范中所定義的公用組件不僅可以在包內(nèi)引用，而且還可以由其他的子程序引用創(chuàng)建包規(guī)范時需要注意的是：為了實(shí)現(xiàn)信息隱藏，不應(yīng)該將所有組件全部放在包規(guī)范處定義，而應(yīng)該只定義公用組件,CREATEORREPLACEPACKAGEpackage_nameIS|ASpublictypeanditemdeclarationssubprogramspecificationsENDpackage_name;,createorreplacepackagedbutil_packageispiconstantnumber(10,7):=3.1415926;functiongetarea(radiusnumber)returnnumber;procedureprint_area;enddbutil_package;,創(chuàng)建包體為了實(shí)現(xiàn)包規(guī)范中所定義的公用過程和函數(shù)，必須創(chuàng)建包體包體用于實(shí)現(xiàn)包規(guī)范所定義的過程和函數(shù)在創(chuàng)建包時，為了實(shí)現(xiàn)信息隱藏，應(yīng)該在包體內(nèi)定義私有組件,CREATEORREPLACEPACKAGEBODYpackage_nameIS|ASprivatetypeanditemdeclarationssubprogrambodiesENDpackage_name;,createorreplacepackagebodydbutil_packageasareanumber(10);functiongetarea(radiusnumber)returnnumberisbeginarea:=pi*radius*radius;returnarea;end;procedureprint_areaisbegindbms_output.put_line(圓的面積是：|area);end;enddbutil_package;,注意：在oraclesqldeveloper中運(yùn)行編譯后，會出現(xiàn)錯誤，導(dǎo)致不能正常執(zhí)行。是因?yàn)槠鋌ug，將最后的分號丟掉了，再編輯一下，加上即可。,調(diào)用包的組件對于包的私有組件，只能在包內(nèi)調(diào)用，并且可以直接調(diào)用對于包的公用組件，既可以在包內(nèi)調(diào)用，又可以在其他應(yīng)用中調(diào)用在調(diào)用同一包內(nèi)其他組件，可直接調(diào)用，不需要加包名作為前綴調(diào)用包的公用變量、過程、函數(shù)當(dāng)在其他應(yīng)用中調(diào)用包的公用變量時，必須在公用變量、過程、函數(shù)名前添加包名作為前綴,declareareanumber(10,7);beginarea:=dbutil_package.getarea(3);dbms_output.put_line(由function返回的面積：|area);dbutil_package.print_area;end;,查看子程序的源碼通過查詢數(shù)據(jù)字典USER_SOURCE，可顯示當(dāng)前用戶的所有子程序及其源代碼名稱必須大寫selecttextfromuser_sourcewherename=DBUTIL_PACKAGE;刪除子程序dropprocedureproc_name;,子程序和包的管理,子程序源代碼,查看包源代碼通過查詢數(shù)據(jù)字典USER_SOURCE，可以顯示當(dāng)前用戶的包及其源代碼selecttextfro