ORACLE程序包的創(chuàng)建與應(yīng)用

1、.ORACLE PL/SQL編程詳解之七：程序包的創(chuàng)建與應(yīng)用(聰明在于學(xué)習(xí)，天才在于積累 ！ )本篇主要內(nèi)容如下：第七章程序包的創(chuàng)建和應(yīng)用7.1 程序包簡(jiǎn)介7.2 程序包的定義7.3 包的開(kāi)發(fā)步驟7.4 包定義的說(shuō)明7.5 子程序重載7.6 加密實(shí)用程序7.7 刪除包7.8 包的管理7.1 程序包簡(jiǎn)介程序包 (PACKAGE ，簡(jiǎn)稱包 )是一組相關(guān)過(guò)程、函數(shù) 、變量 、常量和游標(biāo)等PL/SQL 程序設(shè)計(jì)元素的組合 ，作為一個(gè)完整的單元存儲(chǔ)在數(shù)據(jù)庫(kù)中，用名稱來(lái)標(biāo)識(shí)包。它具有面向?qū)ο蟪绦蛟O(shè)計(jì)語(yǔ)言的特點(diǎn) ，是對(duì)這些PL/SQL程序設(shè)計(jì)元素的封裝。包類似于c# 和 JAVA 語(yǔ)言中的類，其中變量相當(dāng)于

2、類中的成員變量，過(guò)程和函數(shù)相當(dāng)于類方法。 把相關(guān)的模塊歸類成為包，可使開(kāi)發(fā)人員利用面向?qū)ο蟮姆椒ㄟM(jìn)行存儲(chǔ)過(guò)程的開(kāi)發(fā)，從而提高系統(tǒng)性能。與高級(jí)語(yǔ)言中的類相同，包中的程序元素也分為 公用元素 和私用元素 兩種 ，這兩種元素的區(qū)別是他們?cè)试S訪問(wèn)的程序范圍不同，即它們的作用域不同。公用元素不僅可以被包中的函數(shù)、.下載可編輯 .過(guò)程所調(diào)用 ，也可以被包外的PL/SQL 程序訪問(wèn) ，而私有元素只能被包內(nèi)的函數(shù)和過(guò)程序所訪問(wèn)。當(dāng)然 ，對(duì)于不包含在程序包中的過(guò)程、函數(shù)是獨(dú)立存在的。一般是先編寫(xiě)?yīng)毩⒌倪^(guò)程與函數(shù)，待其較為完善或經(jīng)過(guò)充分驗(yàn)證無(wú)誤后，再按邏輯相關(guān)性組織為程序包。程序包的優(yōu)點(diǎn)簡(jiǎn)化應(yīng)用程序設(shè)計(jì)：程序包的

3、說(shuō)明部分和包體部分可以分別創(chuàng)建各編譯。主要體現(xiàn)在以下三個(gè)方面：1) 可以在設(shè)計(jì)一個(gè)應(yīng)用程序時(shí)，只創(chuàng)建各編譯程序包的說(shuō)明部分，然后再編寫(xiě)引用該程序包的 PL/SQL 塊 。2) 當(dāng)完成整個(gè)應(yīng)用程序的整體框架后，再回頭來(lái)定義包體部分。只要不改變包的說(shuō)明部分，就可以單獨(dú)調(diào)試 、增加或替換包體的內(nèi)容，這不會(huì)影響其他的應(yīng)用程序。3) 更新包的說(shuō)明后必須重新編譯引用包的應(yīng)用程序，但更新包體 ，則不需重新編譯引用包的應(yīng)用程序 ，以快速進(jìn)行應(yīng)用程序的原形開(kāi)發(fā)。模塊化 ：可將邏輯相關(guān)的PL/SQL 塊或元素等組織在一起，用名稱來(lái)唯一標(biāo)識(shí)程序包。把一個(gè)大的功能模塊劃分人適當(dāng)個(gè)數(shù)小的功能模塊，分別完成各自的功能。這

4、樣組織的程序包都易于編寫(xiě) ，易于理解更易于管理。信息隱藏 ：因?yàn)榘械脑乜梢苑譃楣性睾退接性亍９性乜杀怀绦虬鼉?nèi)的過(guò)程、函數(shù)等的訪問(wèn) ，還可以被包外的PL/SQL 訪問(wèn) 。 但對(duì)于私有元素只能被包內(nèi)的過(guò)程、函數(shù)等訪問(wèn) 。 對(duì)于用戶 ，只需知道包的說(shuō)明，不用了解包休的具體細(xì)節(jié)。效率高 ：程序包在應(yīng)用程序第一次調(diào)用程序包中的某個(gè)元素時(shí)， ORACLE 將把整個(gè)程序包加載到內(nèi)存中 ，當(dāng)?shù)诙卧L問(wèn)程序包中的元素時(shí)， ORACLE 將直接從內(nèi)在中讀取 ，而不.下載可編輯 .需要進(jìn)行磁盤I/O 操作而影響速度，同時(shí)位于內(nèi)在中的程序包可被同一會(huì)話期間的其它應(yīng)用程序共享 。 因此 ，程序包增加了重用

5、性并改善了多用戶、多應(yīng)用程序環(huán)境的效率。對(duì)程序包的優(yōu)點(diǎn)可總結(jié)如下：在PL/SQL程序設(shè)計(jì)中 ，使用包不僅可以使程序設(shè)計(jì)模塊化，對(duì)外隱藏包內(nèi)所使用的信息（通過(guò)使用私用變量）， 而寫(xiě)可以提高程序的執(zhí)行效率。因?yàn)?，?dāng)程序首次調(diào)用包內(nèi)函數(shù)或過(guò)程時(shí)， ORACLE 將整個(gè)包調(diào)入內(nèi)存，當(dāng)再次訪問(wèn)包內(nèi)元素時(shí)，ORACLE 直接從內(nèi)存中讀取，而不需要進(jìn)行磁盤I/O 操作 ，從而使程序執(zhí)行效率得到提高。一個(gè)包由兩個(gè)分開(kāi)的部分組成：包說(shuō)明 （ PACKAGE）： 包說(shuō)明部分聲明包內(nèi)數(shù)據(jù)類型、變量 、常量 、游標(biāo) 、子程序和異常錯(cuò)誤處理等元素 ，這些元素為包的公有元素。包主體 （ PACKAGE BODY ）： 包

6、主體則是包定義部分的具體實(shí)現(xiàn)，它定義了包定義部分所聲明的游標(biāo)和子程序，在包主體中還可以聲明包的私有元素。包說(shuō)明和包主體分開(kāi)編譯，并作為兩部分分開(kāi)的對(duì)象存放在數(shù)據(jù)庫(kù)字典中，可查看數(shù)據(jù)字典user_source, all_source, dba_source，分別了解包說(shuō)明與包主體的詳細(xì)信息。7.2 程序包的定義程序包的定義分為程序包說(shuō)明定義和程序包主體定義兩部分組成 。程序包說(shuō)明用于聲明包的公用組件，如變量 、常量、自定義數(shù)據(jù)類型、異常、過(guò)程、函數(shù)、游標(biāo)等 。 包說(shuō)明中定義的公有組件不僅可以在包內(nèi)使用，還可以由包外其他過(guò)程、函數(shù) 。但需要說(shuō)明與注意的是，我們?yōu)榱藢?shí)現(xiàn)信息的隱藏，建議不要將所有組件

7、都放在包說(shuō)明處聲明，只應(yīng)把公共組件放在包聲明部分。包的名稱是唯一的，但對(duì)于兩個(gè)包中的公有組件的名稱可以相同，這種用 “包名 .公有組件名“加以區(qū)分 。.下載可編輯 .包體是包的具體實(shí)現(xiàn)細(xì)節(jié)，其實(shí)現(xiàn)在包說(shuō)明中聲明的所有公有過(guò)程、函數(shù) 、游標(biāo)等 。 當(dāng)然也可以在包體中聲明僅屬于自己的私有過(guò)程、函數(shù) 、游標(biāo)等 。 創(chuàng)建包體時(shí) ，有以下幾點(diǎn)需要注意：包體只能在包說(shuō)明被創(chuàng)建或編譯后才能進(jìn)行創(chuàng)建或編譯。在包體中實(shí)現(xiàn)的過(guò)程、函數(shù) 、游標(biāo)的名稱必須與包說(shuō)明中的過(guò)程、函數(shù) 、游標(biāo)一致 ，包括名稱、參數(shù)的名稱以及參數(shù)的模式（ IN 、OUT 、 IN OUT ）。 并建設(shè)按包說(shuō)明中的次序定義包體中具體的實(shí)現(xiàn)。在包

8、體中聲明的數(shù)據(jù)類型、變量 、常量都是私有的，只能在包體中使用而不能被印刷體外的應(yīng)用程序訪問(wèn)與使用。在包體執(zhí)行部分，可對(duì)包說(shuō)明 ，包體中聲明的公有或私有變量進(jìn)行初始化或其它設(shè)置。創(chuàng)建程序包說(shuō)明語(yǔ)法格式:CREATE OR REPLACE PACKAGE package_nameAUTHIDCURRENT_USER | DEFINERIS | AS 公有數(shù)據(jù)類型定義 公有數(shù)據(jù)類型定義 公有游標(biāo)聲明 公有游標(biāo)聲明 公有變量 、常量聲明 公有變量 、常量聲明 公有函數(shù)聲明 公有函數(shù)聲明 公有過(guò)程聲明 公有過(guò)程聲明 END package_name;其中 ： AUTHID CURRENT_USER和 A

9、UTHID DEFINER 選項(xiàng)說(shuō)明應(yīng)用程序在調(diào)用函數(shù)時(shí)所使用的權(quán)限模式，它們與 CREATE FUNCTION 語(yǔ)句中invoker_right_clause子句的作用相同 。創(chuàng)建程序包主體語(yǔ)法格式:CREATE OR REPLACE PACKAGE BODY package_nameIS | AS 私有數(shù)據(jù)類型定義 私有數(shù)據(jù)類型定義 私有變量 、常量聲明 私有變量 、常量聲明 私有異常錯(cuò)誤聲明 私有異常錯(cuò)誤聲明 .下載可編輯 . 私有函數(shù)聲明和定義 私有函數(shù)聲明和定義 私有函過(guò)程聲明和定義 私有函過(guò)程聲明和定義 公有游標(biāo)定義 公有游標(biāo)定義 公有函數(shù)定義 公有函數(shù)定義 公有過(guò)程定義 公有過(guò)程

10、定義 BEGIN執(zhí)行部分 (初始化部分 )END package_name;其中：在包主體定義公有程序時(shí)，它們必須與包定義中所聲明子程序的格式完全一致。7.3 包的開(kāi)發(fā)步驟與開(kāi)發(fā)存儲(chǔ)過(guò)程類似，包的開(kāi)發(fā)需要幾個(gè)步驟：1 將每個(gè)存儲(chǔ)過(guò)程調(diào)式正確；2 用文本編輯軟件將各個(gè)存儲(chǔ)過(guò)程和函數(shù)集成在一起；3 按照包的定義要求將集成的文本的前面加上包定義；4 按照包的定義要求將集成的文本的前面加上包主體；5 使用 SQLPLUS 或開(kāi)發(fā)工具進(jìn)行調(diào)式 。7.4 包定義的說(shuō)明例 1: 創(chuàng)建的包為DEMO_PKG,該包中包含一個(gè)記錄變量DEPTREC、兩個(gè)函數(shù)和一個(gè)過(guò)程。實(shí)現(xiàn)對(duì) dept 表的增加 、刪除與查詢 。

11、CREATE OR REPLACE PACKAGE DEMO_PKGISDEPTREC DEPT%ROWTYPE;-Adddept.FUNCTIONadd_dept(dept_noNUMBER ,dept_nameVARCHAR2 ,locationVARCHAR2 )RETURN NUMBER ;.下載可編輯 .-deletedept.FUNCTIONdelete_dept(dept_noNUMBER )RETURN NUMBER ;-querydept.PROCEDURE query_dept(dept_noIN NUMBER );END DEMO_PKG;包主體的創(chuàng)建方法 ，它實(shí)現(xiàn)上面所

12、聲明的包定義，并在包主體中聲明一個(gè)私有變量flag 和一個(gè)私有函數(shù)check_dept ，由于在 add_dept 和 remove_dept等函數(shù)中需要調(diào)用 check_dpet 函數(shù)，所以，在定義 check_dept函數(shù)之前首先對(duì)該函數(shù)進(jìn)行聲明，這種聲明方法稱作前向聲明。CREATE OR REPLACE PACKAGE BODY DEMO_PKGISFUNCTIONadd_dept(dept_noNUMBER ,dept_nameVARCHAR2 ,locationVARCHAR2)RETURN NUMBERISempno_remainingEXCEPTION; - 自定義異常PRAG

13、MAEXCEPTION_INIT(empno_remaining,- 1);/*-1 是違反唯一約束條件的錯(cuò)誤代碼*/BEGININSERT INTOdeptVALUES(dept_no,dept_name,location);IF SQL%FOUNDTHENRETURN 1 ;END IF;EXCEPTIONWHENempno_remainingTHENRETURN 0 ;WHENOTHERS THENRETURN - 1;END add_dept;FUNCTIONdelete_dept(dept_noNUMBER )RETURN NUMBERISBEGIN.下載可編輯 .DELETE FR

14、OM deptWHERE deptno=dept_no;IF SQL%FOUNDTHENRETURN 1 ;ELSERETURN 0 ;END IF;EXCEPTIONWHENOTHERS THENRETURN - 1;END delete_dept;PROCEDURE query_dept(dept_noIN NUMBER )ISBEGINSELECT * INTODeptRecFROM deptWHERE deptno =dept_no;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE( '溫馨提示 :數(shù)據(jù)庫(kù)中沒(méi)有編碼為'

15、|dept_no |'的部門 ');WHENTOO_MANY_ROWSTHENDBMS_OUTPUT.PUT_LINE( '程序運(yùn)行錯(cuò)誤 ,請(qǐng)使用游標(biāo)進(jìn)行操作!' );WHENOTHERS THENDBMS_OUTPUT.PUT_LINE(SQLCODE |'-' |SQLERRM);END query_dept;BEGINNull ;END DEMO_PKG;對(duì)包內(nèi)共有元素的調(diào)用格式為：包名 .元素名稱調(diào)用 DEMO_PKG 包內(nèi)函數(shù)對(duì) dept 表進(jìn)行插入 、查詢和刪除操作，并通過(guò)DEMO_PKG 包中的記錄變量DEPTREC顯示所查詢到的數(shù)

16、據(jù)庫(kù)信息：DECLAREVar NUMBER ;BEGINVar := DEMO_PKG.add_dept( 90 ,'HKLORB' , 'HAIKOU' );IF var=- 1 THENDBMS_OUTPUT.PUT_LINE(SQLCODE |'-' |SQLERRM);ELSIF var= 0 THENDBMS_OUTPUT.PUT_LINE( '溫馨提示 :該部門記錄已經(jīng)存在！ ');ELSEDBMS_OUTPUT.PUT_LINE( '溫馨提示 :添加記錄成功 ！');.下載可編輯 .DEMO_PKG

17、.query_dept(90);DBMS_OUTPUT.PUT_LINE(DEMO_PKG.DeptRec.deptno |'-' |DEMO_PKG.DeptRec.dname |'-' |DEMO_ PKG.DeptRec.loc);var :=DEMO_PKG.delete_dept( 90 );IF var=- 1 THENDBMS_OUTPUT.PUT_LINE(SQLCODE |'-' |SQLERRM);ELSIF var = 0 THENDBMS_OUTPUT.PUT_LINE( '溫馨提示 :該部門記錄不存在！'

18、);ELSEDBMS_OUTPUT.PUT_LINE( '溫馨提示 :刪除記錄成功 ！');END IF;END IF;END;例 2: 創(chuàng)建包 EMP_PKG，讀取 emp 表中的數(shù)據(jù)- 創(chuàng)建包說(shuō)明CREATE OR REPLACE PACKAGEEMP_PKGISTYPE emp_table_typeIS TABLE OF emp %ROWTYPEINDEXBY BINARY_INTEGER;PROCEDUREread_emp_table(p_emp_tableOUTemp_table_type);END EMP_PKG;- 創(chuàng)建包體CREATE OR REPLACE PA

19、CKAGEBODYEMP_PKGISPROCEDUREread_emp_table(p_emp_tableOUTemp_table_type)ISI BINARY_INTEGER:=0 ;BEGINFOR emp_recordIN( SELECT * FROMemp) LOOPP_emp_table(i):= emp_record;I:=I+1;END LOOP;END read_emp_table;END EMP_PKG;- 執(zhí)行DECLAREE_tableEMP_PKG.emp_table_type;BEGINEMP_PKG.read_emp_table(e_table);FOR I IN

20、e_table.FIRST .e_table.LASTLOOP.下載可編輯 .DBMS_OUTPUT.PUT_LINE(e_table(i).empno| ' '| e_table(i).ename);END LOOP;END ;例 3: 創(chuàng)建包 MANAGE_EMP_PKG ，對(duì)員工進(jìn)行管理（新增員工 、新增部門 、刪除指定員工、刪除指定部門 、增加指定員工的工資與獎(jiǎng)金）：- 創(chuàng)建序列從 100 開(kāi)始 ,依次增加 1 CREATE SEQUENCE empseq START WITH 100 INCREMENT BY 1ORDER NOCYCLE;- 創(chuàng)建序列從 100 開(kāi)始

21、 ,依次增加 10 CREATE SEQUENCE deptseq START WITH 100INCREMENT BY 10 ORDER NOCYCLE;- *- 創(chuàng)建包說(shuō)明- 包 名： MANAGE_EMP_PKG- 功能描述 ：對(duì)員工進(jìn)行管理 (新增員工 ,新增部門- ,刪除員工 ,刪除部門 ,增加工資與獎(jiǎng)金等 )- 創(chuàng)建人員 ：胡勇- 創(chuàng)建日期 ： 2010-05-19- Q Q: 80368704- E-mail : 80368704- WebSite: - *CREATE OR REPLACE PACKAGEMANAGE_EMP_PKGAS- 增加一名員工FUNCTIONhire_

22、emp(enameVARCHAR2 , jobVARCHAR2, mgr NUMBER , sal NUMBER, comm NUMBER , deptno NUMBER )RETURN NUMBER ;- 新增一個(gè)部門FUNCTIONadd_dept(dnameVARCHAR2 , locVARCHAR2 )RETURNNUMBER ;- 刪除指定員工.下載可編輯 .PROCEDUREremove_emp(empnoNUMBER );- 刪除指定部門PROCEDUREremove_dept(deptnoNUMBER );- 增加指定員工的工資PROCEDUREincrease_sal(emp

23、noNUMBER , sal_incrNUMBER );- 增加指定員工的獎(jiǎng)金PROCEDUREincrease_comm(empnoNUMBER , comm_incrNUMBER );END MANAGE_EMP_PKG; - 創(chuàng)建包說(shuō)明結(jié)束- *- 創(chuàng)建包體- 包 名： MANAGE_EMP_PKG- 功能描述 ：對(duì)員工進(jìn)行管理 (新增員工 ,新增部門- ,刪除員工 ,刪除部門 ,增加工資與獎(jiǎng)金等 )- 創(chuàng)建人員 ：胡勇- 創(chuàng)建日期 ： 2010-05-19- Q Q: 80368704- E-mail : 80368704- WebSite: - *CREATE OR REPLACE

24、PACKAGEBODYMANAGE_EMP_PKGAStotal_empsNUMBER ; - 員工數(shù)total_deptsNUMBER ; - 部門數(shù)no_salEXCEPTION;no_commEXCEPTION;- 增加一名員工FUNCTION hire_emp(ename VARCHAR2 , job VARCHAR2 , mgr NUMBER , sal NUMBER , comm NUMBER , deptno NUMBER )RETURNNUMBER- 返回新增加的員工編號(hào)ISnew_empnoNUMBER (4 );BEGINSELECT empseq.NEXTVALINTOn

25、ew_empnoFROMdual;SELECT COUNT (*) INTOtotal_empsFROMemp; - 當(dāng)前記錄總數(shù)INSERT INTOempVALUES (new_empno,ename,job,mgr,sysdate,sal, comm,deptno);total_emps:= total_emps+ 1 ;RETURN (new_empno);EXCEPTIONWHENOTHERS THENDBMS_OUTPUT.PUT_LINE(' 溫馨提示 :發(fā)生系統(tǒng)錯(cuò)誤 !' );END hire_emp;.下載可編輯 .- 新增一個(gè)部門FUNCTIONadd_de

26、pt(dnameVARCHAR2 , locVARCHAR2 )RETURNNUMBERISnew_deptnoNUMBER (4); - 部門編號(hào)BEGIN- 得到一個(gè)新的自增的員工編號(hào)SELECT deptseq.NEXTVALINTOnew_deptnoFROMdual;SELECT COUNT (*) INTOtotal_deptsFROMdept; - 當(dāng)前部門總數(shù)INSERT INTOdeptVALUES (new_deptno,dname,loc);total_depts:=total_depts;RETURN (new_deptno);EXCEPTIONWHENOTHERS T

27、HENDBMS_OUTPUT.PUT_LINE(' 溫馨提示 :發(fā)生系統(tǒng)錯(cuò)誤 !' );END add_dept;- 刪除指定員工PROCEDUREremove_emp(empnoNUMBER )ISno_resultEXCEPTION;- 自定義異常BEGINDELETE FROMempWHERE emp.empno= remove_emp.empno;IF SQL%NOTFOUNDTHENRAISE no_result;END IF;total_emps:= total_emps- 1; - 總的員工數(shù)減1EXCEPTIONWHENno_resultTHENDBMS_OUT

28、PUT.PUT_LINE(' 溫馨提示 :你需要的數(shù)據(jù)不存在!' );WHENOTHERS THENDBMS_OUTPUT.PUT_LINE(' 溫馨提示 :發(fā)生系統(tǒng)錯(cuò)誤 !' );END remove_emp;- 刪除指定部門PROCEDUREremove_dept(deptnoNUMBER )ISno_resultEXCEPTION;- 自定義異常exception_deptno_remainingEXCEPTION;- 自定義異常/*-2292是違反一致性約束的錯(cuò)誤代碼*/PRAGMAEXCEPTION_INIT(exception_deptno_rema

29、ining,- 2292 );BEGINDELETE FROMdeptWHERE dept.deptno= remove_dept.deptno;.下載可編輯 .IF SQL%NOTFOUNDTHENRAISE no_result;END IF;total_depts:=total_depts-1 ; - 總的部門數(shù)減1EXCEPTIONWHENno_resultTHENDBMS_OUTPUT.PUT_LINE(' 溫馨提示 :你需要的數(shù)據(jù)不存在!' );WHENexception_deptno_remainingTHENDBMS_OUTPUT.PUT_LINE(' 溫

30、馨提示 :違反數(shù)據(jù)完整性約束!' );WHENOTHERS THENDBMS_OUTPUT.PUT_LINE(' 溫馨提示 :發(fā)生系統(tǒng)錯(cuò)誤 !' );END remove_dept;- 給指定員工增加指定數(shù)量的工資PROCEDUREincrease_sal(empnoNUMBER , sal_incrNUMBER )IScurr_salNUMBER (7 , 2 ); - 當(dāng)前工資BEGIN- 得到當(dāng)前工資SELECT sal INTOcurr_salFROMempWHERE emp.empno= increase_sal.empno;IF curr_salIS NUL

31、LTHENRAISE no_sal;ELSEUPDATEempSET sal = sal +increase_sal.sal_incr- 當(dāng)前工資加新增的工資WHERE emp.empno= increase_sal.empno;END IF;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(' 溫馨提示 :你需要的數(shù)據(jù)不存在!' );WHENno_salTHENDBMS_OUTPUT.PUT_LINE(' 溫馨提示 :此員工的工資不存在!' );WHENOTHERS THENDBMS_OUTPUT.PUT_L

32、INE(' 溫馨提示 :發(fā)生系統(tǒng)錯(cuò)誤 !' );END increase_sal;- 給指定員工增加指定數(shù)量的獎(jiǎng)金PROCEDUREincrease_comm(empnoNUMBER , comm_incrNUMBER )IScurr_commNUMBER (7,2 );BEGIN- 得到指定員工的當(dāng)前資金SELECT commINTOcurr_comm.下載可編輯 .FROMempWHERE emp.empno=increase_comm.empno;IF curr_commIS NULLTHENRAISE no_comm;ELSEUPDATEempSET comm=comm

33、+increase_m_incrWHERE emp.empno= increase_comm.empno;END IF;EXCEPTIONWHENNO_DATA_FOUNDTHENDBMS_OUTPUT.PUT_LINE(' 溫馨提示 :你需要的數(shù)據(jù)不存在!' );WHENno_commTHENDBMS_OUTPUT.PUT_LINE(' 溫馨提示 :此員工的獎(jiǎng)金不存在!' );WHENOTHERS THENDBMS_OUTPUT.PUT_LINE(' 溫馨提示 :發(fā)生系統(tǒng)錯(cuò)誤 !' );END increase_comm;END MANAGE_

34、EMP_PKG; - 創(chuàng)建包體結(jié)束- 調(diào)用SQL>variableempnonumberSQL> execute:empno: =manage_emp_pkg.hire_emp('HUYONG',PM, 1455 ,5500 ,14,10)PL/ SQL proceduresuccessfullycompletedempno-105例 4：利用游標(biāo)變量創(chuàng)建包CURROR_VARIBAL_PKG。 由于游標(biāo)變量指是一個(gè)指針，其狀態(tài)是不確定的 ，因此它不能隨同包存儲(chǔ)在數(shù)據(jù)庫(kù)中，既不能在PL/SQL 包中聲明游標(biāo)變量。但在包中可以創(chuàng)建游標(biāo)變量參照類型，并可向包中的子程序傳

35、遞游標(biāo)變量參數(shù)。- *- 創(chuàng)建包體- 包 名： CURROR_VARIBAL_PKG- 功能描述 ：在包中引用游標(biāo)變量- 創(chuàng)建人員 ：胡勇- 創(chuàng)建日期 ： 2010-05-19- Q Q: 80368704- E-mail : 80368704- WebSite: - *CREATE OR REPLACE PACKAGECURROR_VARIBAL_PKGAS.下載可編輯 .TYPE DeptCurTypeIS REF CURSORRETURNdept %ROWTYPE;- 強(qiáng)類型定義TYPE CurTypeIS REF CURSOR ;-弱類型定義PROCEDUREOpenDeptVar(C

36、v INOUT DeptCurType,ChoiceINTEGER DEFAULT0 ,Dept_noNUMBERDEFAULT50 ,Dept_nameVARCHARDEFAULT'%' );END ;- *- 創(chuàng)建包體- 包 名： CURROR_VARIBAL_PKG- 功能描述 ：在包中引用游標(biāo)變量- 創(chuàng)建人員 ：胡勇- 創(chuàng)建日期 ： 2010-05-19- Q Q: 80368704- E-mail : 80368704- WebSite: - *CREATE OR REPLACE PACKAGE BODY CURROR_VARIBAL_PKG ASPROCEDUREO

37、penDeptvar(Cv INOUT DeptCurType,ChoiceINTEGER DEFAULT0 ,Dept_noNUMBERDEFAULT50 ,Dept_nameVARCHARDEFAULT% )ISBEGINIF choice= 1 THENOPEN cv FOR SELECT * FROM dept WHERE deptno <= dept_no; ELSIF choice = 2 THENOPEN cvFOR SELECT * FROMdeptWHERE dnameLIKE dept_name;ELSEOPEN cvFOR SELECT * FROMdept;END

38、 IF;END OpenDeptvar;END CURROR_VARIBAL_PKG;- 定義一個(gè)過(guò)程CREATE OR REPLACE PROCEDURE UP_OpenCurType(Cv INOUT CURROR_VARIBAL_PKG.CurType,.下載可編輯 .FirstCapInTableNameCHAR )ASBEGIN-CURROR_VARIBAL_PKG.CurType采用弱類型定義- 所以可以使用它定義的游標(biāo)變量打開(kāi)不同類型的查詢語(yǔ)句IF FirstCapInTableName= 'D'THENOPENcvFOR SELECT *FROMdept;ELS

39、EOPEN cvFOR SELECT * FROMemp;ENDIF;ENDUP_OpenCurType;- 定義一個(gè)應(yīng)用DECLAREDeptRecDept %ROWTYPE;EmpRecEmp %ROWTYPE;Cv1CURROR_VARIBAL_PKG.deptcurtype;Cv2CURROR_VARIBAL_PKG.curtype;BEGINDBMS_OUTPUT.PUT_LINE(' 游標(biāo)變量強(qiáng)類型定義應(yīng)用');CURROR_VARIBAL_PKG.OpenDeptVar(cv1,1, 30);FETCH cv1INTODeptRec;WHILEcv1 %FOUND

40、LOOPDBMS_OUTPUT.PUT_LINE(DeptRec.deptno| ':' | DeptRec.dname);FETCH cv1INTODeptRec;END LOOP;CLOSE cv1;DBMS_OUTPUT.PUT_LINE(' 游標(biāo)變量弱類型定義應(yīng)用');CURROR_VARIBAL_PKG.OpenDeptvar(cv2,2, dept_name=>'A%' );FETCH cv2INTODeptRec;WHILEcv2 %FOUNDLOOPDBMS_OUTPUT.PUT_LINE(DeptRec.deptno| &

41、#39;:' | DeptRec.dname);FETCH cv2INTODeptRec;END LOOP;DBMS_OUTPUT.PUT_LINE(' 游標(biāo)變量弱類型定義應(yīng)用dept 表 ');UP_OpenCurType(cv2,'D' );FETCH cv2INTODeptRec;WHILEcv2 %FOUNDLOOPDBMS_OUTPUT.PUT_LINE(deptrec.deptno|':' | deptrec.dname);FETCH cv2INTOdeptrec;END LOOP;.下載可編輯 .DBMS_OUTPUT.PU

42、T_LINE(' 游標(biāo)變量弱類型定義應(yīng)用emp 表 ');UP_OpenCurType(cv2,'E' );FETCH cv2INTOEmpRec;WHILEcv2 %FOUNDLOOPDBMS_OUTPUT.PUT_LINE(emprec.empno| ':' | emprec.ename);FETCH cv2INTOemprec;END LOOP;CLOSE cv2;END ;-運(yùn)行結(jié)果 -游標(biāo)變量強(qiáng)類型定義應(yīng)用10:ACCOUNTING20:RESEARCH30:SALES游標(biāo)變量弱類型定義應(yīng)用10:ACCOUNTING游標(biāo)變量弱類型定義應(yīng)

43、用 dept表10:ACCOUNTING20:RESEARCH30:SALES40:OPERATIONS50:50abc60:Developer游標(biāo)變量弱類型定義應(yīng)用 emp 表7369 :SMITH7499 :ALLEN7521 :WARD7566 :JONES7654 :MARTIN7698 :BLAKE7782 :CLARK7788 :SCOTT7839 :KING7844 :TURNER7876 :ADAMS7900 :JAMES7902 :FORD7934 :MILLERPL/ SQL proceduresuccessfullycompleted7.5 子程序重載.下載可編輯 .PL/SQL允許對(duì)包內(nèi)子程序和本地子程序進(jìn)行重載。所謂重載時(shí)指兩個(gè)或多個(gè)子程序有相同的名稱 ，但擁有不同的參數(shù)變量、參數(shù)順序或參數(shù)數(shù)據(jù)類型。例 5：- *- 創(chuàng)建包說(shuō)明- 包 名： DEMO_PKG1- 功能描述 ：創(chuàng)建包對(duì)子程序重載進(jìn)行測(cè)試- 創(chuàng)建人員 ：胡勇- 創(chuàng)建日期 ： 2010-05-22- Q Q: 80368704- E-mail : 80368704- WebSite: - *CREATE OR REPLACE PACKAGEDEMO_PKG1ISDeptRecdept %ROWTYPE;V_sqlcodeNUMBER ;V_sqle