[強(qiáng)烈推薦]ORACLE PL/SQL編程詳解之七：程序包的創(chuàng)建與應(yīng)用(聰明在于學(xué)習(xí)，天才在于積累！)

在這篇中，我講給大家講解PL/SQL最引人注目的特色“ORACLE的程序包”，此篇文章花了我近兩周的時(shí)間，今天終于出文了。有的地方可能還不全面，望大蝦們多多指教！一定要推薦、推薦、推薦、推薦、推薦、推薦、推薦、推薦、推薦、推薦、評(píng)論與支持呀~！本篇主要內(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 包的管理

[強(qiáng)烈推薦]ORACLE PL/SQL編程詳解之七：

程序包的創(chuàng)建與應(yīng)用(聰明在于學(xué)習(xí)，天才在于積累！)

——通過(guò)知識(shí)共享樹(shù)立個(gè)人品牌。

繼上七篇：

[頂]ORACLE PL/SQL編程詳解之二：PL/SQL塊結(jié)構(gòu)和組成元素(為山九仞，豈一日之功)

ORACLE PL/SQL編程之六：把過(guò)程與函數(shù)說(shuō)透(窮追猛打，把根兒都拔起!)

ORACLE PL/SQL編程之八：把觸發(fā)器說(shuō)透

在這篇中，我講給大家講解PL/SQL最引人注目的特色“ORACLE的程序包”，此篇文章花了我近兩周的時(shí)間，今天終于出文了。有的地方可能還不全面，望大蝦們多多指教！

一定要推薦、推薦、推薦、推薦、推薦、推薦、推薦、推薦、推薦、推薦、評(píng)論與支持呀~！

本篇主要內(nèi)容如下：

第七章程序包的創(chuàng)建和應(yīng)用

7.1 程序包簡(jiǎn)介

程序包(PACKAGE，簡(jiǎn)稱(chēng)包)是一組相關(guān)過(guò)程、函數(shù)、變量、常量和游標(biāo)等PL/SQL程序設(shè)計(jì)元素的組合，作為一個(gè)完整的單元存儲(chǔ)在數(shù)據(jù)庫(kù)中，用名稱(chēng)來(lái)標(biāo)識(shí)包。它具有面向?qū)ο蟪绦蛟O(shè)計(jì)語(yǔ)言的特點(diǎn)，是對(duì)這些PL/SQL 程序設(shè)計(jì)元素的封裝。包類(lèi)似于c#和JAVA語(yǔ)言中的類(lèi)，其中變量相當(dāng)于類(lèi)中的成員變量，過(guò)程和函數(shù)相當(dāng)于類(lèi)方法。把相關(guān)的模塊歸類(lèi)成為包，可使開(kāi)發(fā)人員利用面向?qū)ο蟮姆椒ㄟM(jìn)行存儲(chǔ)過(guò)程的開(kāi)發(fā)，從而提高系統(tǒng)性能。

與高級(jí)語(yǔ)言中的類(lèi)相同，包中的程序元素也分為公用元素和私用元素兩種，這兩種元素的區(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)

u 簡(jiǎn)化應(yīng)用程序設(shè)計(jì)：程序包的說(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)行進(jìn)行應(yīng)用程序的原形開(kāi)發(fā)。

u 模塊化：可將邏輯相關(guān)的PL/SQL塊或元素等組織在一起，用名稱(chēng)來(lái)唯一標(biāo)識(shí)程序包。把一個(gè)大的功能模塊劃分人適當(dāng)個(gè)數(shù)小的功能模塊，分別完成各自的功能。這樣組織的程序包都易于編寫(xiě)，易于理解更易于管理。

u 信息隱藏：因?yàn)榘械脑乜梢苑譃楣性睾退接性亍９性乜杀怀绦虬鼉?nèi)的過(guò)程、函數(shù)等的訪問(wèn)，還可以被包外的PL/SQL訪問(wèn)。但對(duì)于私有元素只能被包內(nèi)的過(guò)程、函數(shù)等訪問(wèn)。對(duì)于用戶，只需知道包的說(shuō)明，不用了解包休的具體細(xì)節(jié)。

u 效率高：程序包在應(yīng)用程序第一次調(diào)用程序包中的某個(gè)元素時(shí)，ORACLE將把整個(gè)程序包加載到內(nèi)存中，當(dāng)?shù)诙卧L問(wèn)程序包中的元素時(shí)，ORACLE將直接從內(nèi)在中讀取，而不需要進(jìn)行磁盤(pán)I/O操作而影響速度，同時(shí)位于內(nèi)在中的程序包可被同一會(huì)話期間的其它應(yīng)用程序共享。因此，程序包增加了重用性并改善了多用戶、多應(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)行磁盤(pán)I/O操作，從而使程序執(zhí)行效率得到提高。

一個(gè)包由兩個(gè)分開(kāi)的部分組成：

包說(shuō)明（PACKAGE）：包說(shuō)明部分聲明包內(nèi)數(shù)據(jù)類(lèi)型、變量、常量、游標(biāo)、子程序和異常錯(cuò)誤處理等元素，這些元素為包的公有元素。

包主體（PACKAGE BODY）：包主體則是包定義部分的具體實(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ù)類(lèi)型、異常、過(guò)程、函數(shù)、游標(biāo)等。包說(shuō)明中定義的公有組件不僅可以在包內(nèi)使用，還可以由包外其他過(guò)程、函數(shù)。但需要說(shuō)明與注意的是，我們?yōu)榱藢?shí)現(xiàn)信息的隱藏，建議不要將所有組件都放在包說(shuō)明處聲明，只應(yīng)把公共組件放在包聲明部分。包的名稱(chēng)是唯一的，但對(duì)于兩個(gè)包中的公有組件的名稱(chēng)可以相同，這種用“包名.公有組件名“加以區(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)需要注意：

u 包體只能在包說(shuō)明被創(chuàng)建或編譯后才能進(jìn)行創(chuàng)建或編譯。

u 在包體中實(shí)現(xiàn)的過(guò)程、函數(shù)、游標(biāo)的名稱(chēng)必須與包說(shuō)明中的過(guò)程、函數(shù)、游標(biāo)一致，包括名稱(chēng)、參數(shù)的名稱(chēng)以及參數(shù)的模式（IN、OUT、IN OUT）。并建設(shè)按包說(shuō)明中的次序定義包體中具體的實(shí)現(xiàn)。

u 在包體中聲明的數(shù)據(jù)類(lèi)型、變量、常量都是私有的，只能在包體中使用而不能被印刷體外的應(yīng)用程序訪問(wèn)與使用。

u 在包體執(zhí)行部分，可對(duì)包說(shuō)明，包體中聲明的公有或私有變量進(jìn)行初始化或其它設(shè)置。

創(chuàng)建程序包說(shuō)明語(yǔ)法格式:

CREATE [OR REPLACE] PACKAGE package_name
  [AUTHID {CURRENT_USER | DEFINER}]
  {IS | AS}
  [公有數(shù)據(jù)類(lèi)型定義[公有數(shù)據(jù)類(lèi)型定義]…]
  [公有游標(biāo)聲明[公有游標(biāo)聲明]…]
  [公有變量、常量聲明[公有變量、常量聲明]…]
  [公有函數(shù)聲明[公有函數(shù)聲明]…]
  [公有過(guò)程聲明[公有過(guò)程聲明]…]
END [package_name];

其中：AUTHIDCURRENT_USER和AUTHIDDEFINER選項(xiàng)說(shuō)明應(yīng)用程序在調(diào)用函數(shù)時(shí)所使用的權(quán)限模式，它們與CREATEFUNCTION語(yǔ)句中invoker_right_clause子句的作用相同。

創(chuàng)建程序包主體語(yǔ)法格式:

CREATE [OR REPLACE] PACKAGE BODY package_name
  {IS | AS}
  [私有數(shù)據(jù)類(lèi)型定義[私有數(shù)據(jù)類(lèi)型定義]…]
  [私有變量、常量聲明[私有變量、常量聲明]…]
  [私有異常錯(cuò)誤聲明[私有異常錯(cuò)誤聲明]…]
  [私有函數(shù)聲明和定義[私有函數(shù)聲明和定義]…]
  [私有函過(guò)程聲明和定義[私有函過(guò)程聲明和定義]…]
  [公有游標(biāo)定義[公有游標(biāo)定義]…]
  [公有函數(shù)定義[公有函數(shù)定義]…]
  [公有過(guò)程定義[公有過(guò)程定義]…]
BEGIN
  執(zhí)行部分(初始化部分)
END package_name;

其中：在包主體定義公有程序時(shí)，它們必須與包定義中所聲明子程序的格式完全一致。

7.3 包的開(kāi)發(fā)步驟

與開(kāi)發(fā)存儲(chǔ)過(guò)程類(lèi)似，包的開(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表的增加、刪除與查詢。

CREATE OR REPLACE PACKAGE  DEMO_PKG
IS
  DEPTREC DEPT%ROWTYPE;

  --Add dept...
  FUNCTION add_dept(
           dept_no    NUMBER,
           dept_name VARCHAR2,
           location  VARCHAR2)
  RETURN NUMBER;

  --delete dept...
  FUNCTION delete_dept(dept_no NUMBER)
  RETURN NUMBER;

  --query dept...
  PROCEDURE query_dept(dept_no IN NUMBER);
END DEMO_PKG;

包主體的創(chuàng)建方法，它實(shí)現(xiàn)上面所聲明的包定義，并在包主體中聲明一個(gè)私有變量flag和一個(gè)私有函數(shù)check_dept，由于在add_dept和remove_dept等函數(shù)中需要調(diào)用check_dpet函數(shù)，所以，在定義check_dept 函數(shù)之前首先對(duì)該函數(shù)進(jìn)行聲明，這種聲明方法稱(chēng)作前向聲明。

CREATE OR REPLACE PACKAGE BODY DEMO_PKG
IS
FUNCTION add_dept
(
   dept_no NUMBER,
   dept_name VARCHAR2,
   location VARCHAR2
)
RETURN NUMBER
IS
  empno_remaining EXCEPTION; --自定義異常
  PRAGMA EXCEPTION_INIT(empno_remaining, -1);
   /* -1 是違反唯一約束條件的錯(cuò)誤代碼 */
BEGIN
  INSERT INTO dept VALUES(dept_no, dept_name, location);
  IF SQL%FOUND THEN
     RETURN 1;
  END IF;
EXCEPTION
     WHEN empno_remaining THEN
        RETURN 0;
     WHEN OTHERS THEN
        RETURN -1;
END add_dept;

FUNCTION delete_dept(dept_no NUMBER)
RETURN NUMBER
IS
BEGIN
  DELETE FROM dept WHERE deptno = dept_no;
  IF SQL%FOUND THEN
    RETURN 1;
  ELSE
    RETURN 0;
   END IF;
EXCEPTION
  WHEN OTHERS THEN
    RETURN -1;
END delete_dept;

PROCEDURE query_dept
(dept_no IN NUMBER)
IS
BEGIN
      SELECT * INTO DeptRec FROM dept WHERE deptno=dept_no;
EXCEPTION
       WHEN NO_DATA_FOUND THEN
          DBMS_OUTPUT.PUT_LINE('溫馨提示:數(shù)據(jù)庫(kù)中沒(méi)有編碼為'||dept_no||'的部門(mén)');
       WHEN TOO_MANY_ROWS THEN
          DBMS_OUTPUT.PUT_LINE('程序運(yùn)行錯(cuò)誤,請(qǐng)使用游標(biāo)進(jìn)行操作!');
       WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM);
END query_dept;

BEGIN
    Null;
END DEMO_PKG;

對(duì)包內(nèi)共有元素的調(diào)用格式為：包名.元素名稱(chēng)

調(diào)用DEMO_PKG包內(nèi)函數(shù)對(duì)dept表進(jìn)行插入、查詢和刪除操作，并通過(guò)DEMO_PKG包中的記錄變量DEPTREC顯示所查詢到的數(shù)據(jù)庫(kù)信息：

DECLARE
    Var NUMBER;
BEGIN
    Var := DEMO_PKG.add_dept(90,'HKLORB', 'HAIKOU');
    IF var =-1 THEN
        DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM);
    ELSIF var =0 THEN
        DBMS_OUTPUT.PUT_LINE('溫馨提示:該部門(mén)記錄已經(jīng)存在！');
    ELSE
        DBMS_OUTPUT.PUT_LINE('溫馨提示:添加記錄成功！');
        DEMO_PKG.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 THEN
            DBMS_OUTPUT.PUT_LINE(SQLCODE||'----'||SQLERRM);
        ELSIF var=0 THEN
            DBMS_OUTPUT.PUT_LINE('溫馨提示:該部門(mén)記錄不存在！');
        ELSE
            DBMS_OUTPUT.PUT_LINE('溫馨提示:刪除記錄成功！');
        END IF;
    END IF;
END;

例2: 創(chuàng)建包EMP_PKG，讀取emp表中的數(shù)據(jù)

--創(chuàng)建包說(shuō)明
CREATE OR REPLACE PACKAGE EMP_PKG
IS
  TYPE emp_table_type IS TABLE OF emp%ROWTYPE
  INDEX BY BINARY_INTEGER;

  PROCEDURE read_emp_table (p_emp_table OUT emp_table_type);
END EMP_PKG;

--創(chuàng)建包體
CREATE OR REPLACE PACKAGE BODY EMP_PKG
IS
PROCEDURE read_emp_table (p_emp_table OUT emp_table_type)
IS
I BINARY_INTEGER := 0;
BEGIN
   FOR emp_record IN ( SELECT * FROM emp ) LOOP
      P_emp_table(i) := emp_record;
      I := I + 1;
    END LOOP;
  END read_emp_table;
END EMP_PKG;

--執(zhí)行
DECLARE
  E_table EMP_PKG.emp_table_type;
BEGIN
  EMP_PKG.read_emp_table(e_table);
  FOR I IN e_table.FIRST ..e_table.LAST LOOP
    DBMS_OUTPUT.PUT_LINE(e_table(i).empno||'  '||e_table(i).ename);
  END LOOP;
END;

例3: 創(chuàng)建包MANAGE_EMP_PKG，對(duì)員工進(jìn)行管理（新增員工、新增部門(mén)、刪除指定員工、刪除指定部門(mén)、增加指定員工的工資與獎(jiǎng)金）：

--創(chuàng)建序列從100開(kāi)始,依次增加1
CREATE SEQUENCE empseq
START WITH 100
INCREMENT BY 1
ORDER NOCYCLE;

--創(chuàng)建序列從100開(kāi)始,依次增加10
CREATE SEQUENCE deptseq
START WITH 100
INCREMENT BY 10
ORDER NOCYCLE;

-- *******************************************
  -- 創(chuàng)建包說(shuō)明
  -- 包   名：MANAGE_EMP_PKG
  -- 功能描述：對(duì)員工進(jìn)行管理(新增員工,新增部門(mén)
  --            ,刪除員工,刪除部門(mén),增加工資與獎(jiǎng)金等)
  -- 創(chuàng)建人員：胡勇
  -- 創(chuàng)建日期：2010-05-19
  -- Q     Q: 80368704
  -- E-mail : 80368704@yahoo.com.cn
  -- WebSite: http://www.rzrgm.cn/huyong
-- ******************************************
CREATE OR REPLACE PACKAGE MANAGE_EMP_PKG
AS
  --增加一名員工
  FUNCTION hire_emp
    (ename VARCHAR2, job VARCHAR2
    , mgr NUMBER, sal NUMBER
    , comm NUMBER, deptno NUMBER)
  RETURN NUMBER;

  --新增一個(gè)部門(mén)
  FUNCTION add_dept(dname VARCHAR2, loc VARCHAR2)
  RETURN NUMBER;

  --刪除指定員工
  PROCEDURE remove_emp(empno NUMBER);
  --刪除指定部門(mén)
  PROCEDURE remove_dept(deptno NUMBER);
  --增加指定員工的工資
  PROCEDURE increase_sal(empno NUMBER, sal_incr NUMBER);
  --增加指定員工的獎(jiǎng)金
  PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER);
END MANAGE_EMP_PKG;--創(chuàng)建包說(shuō)明結(jié)束

-- *******************************************
  -- 創(chuàng)建包體
  -- 包   名：MANAGE_EMP_PKG
  -- 功能描述：對(duì)員工進(jìn)行管理(新增員工,新增部門(mén)
  --            ,刪除員工,刪除部門(mén),增加工資與獎(jiǎng)金等)
  -- 創(chuàng)建人員：胡勇
  -- 創(chuàng)建日期：2010-05-19
  -- Q     Q: 80368704
  -- E-mail : 80368704@yahoo.com.cn
  -- WebSite: http://www.rzrgm.cn/huyong
-- ******************************************
CREATE OR REPLACE PACKAGE BODY MANAGE_EMP_PKG
AS
    total_emps  NUMBER; --員工數(shù)
    total_depts NUMBER; --部門(mén)數(shù)
    no_sal    EXCEPTION;
    no_comm   EXCEPTION;
  --增加一名員工
  FUNCTION hire_emp(ename VARCHAR2, job VARCHAR2, mgr NUMBER,
                       sal NUMBER, comm NUMBER, deptno NUMBER)
  RETURN NUMBER  --返回新增加的員工編號(hào)
  IS
    new_empno NUMBER(4);
  BEGIN
SELECT empseq.NEXTVAL INTO new_empno FROM dual;
SELECT COUNT(*) INTO total_emps FROM emp;--當(dāng)前記錄總數(shù)

    INSERT INTO emp
    VALUES (new_empno, ename, job, mgr, sysdate, sal, comm, deptno);
    total_emps:=total_emps+1;
  RETURN(new_empno);
  EXCEPTION
     WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('溫馨提示:發(fā)生系統(tǒng)錯(cuò)誤!');
  END hire_emp;

  --新增一個(gè)部門(mén)
  FUNCTION add_dept(dname VARCHAR2, loc VARCHAR2)
  RETURN NUMBER
  IS
    new_deptno NUMBER(4); --部門(mén)編號(hào)
  BEGIN
    --得到一個(gè)新的自增的員工編號(hào)
    SELECT deptseq.NEXTVAL INTO new_deptno FROM dual;
    SELECT COUNT(*) INTO total_depts FROM dept;--當(dāng)前部門(mén)總數(shù)
    INSERT INTO dept VALUES (new_deptno, dname, loc);
    total_depts:=total_depts;
  RETURN(new_deptno);
  EXCEPTION
     WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('溫馨提示:發(fā)生系統(tǒng)錯(cuò)誤!');
  END add_dept;

  --刪除指定員工
  PROCEDURE remove_emp(empno NUMBER)
  IS
    no_result EXCEPTION; --自定義異常
  BEGIN
    DELETE FROM emp WHERE emp.empno=remove_emp.empno;
    IF SQL%NOTFOUND THEN
        RAISE no_result;
    END IF;
    total_emps:=total_emps - 1; --總的員工數(shù)減1
  EXCEPTION
     WHEN no_result THEN
        DBMS_OUTPUT.PUT_LINE('溫馨提示:你需要的數(shù)據(jù)不存在!');
     WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('溫馨提示:發(fā)生系統(tǒng)錯(cuò)誤!');
  END remove_emp;

  --刪除指定部門(mén)
  PROCEDURE remove_dept(deptno NUMBER)
  IS
     no_result EXCEPTION; --自定義異常
     exception_deptno_remaining EXCEPTION; --自定義異常
     /*-2292 是違反一致性約束的錯(cuò)誤代碼*/
     PRAGMA EXCEPTION_INIT(exception_deptno_remaining, -2292);
  BEGIN
    DELETE FROM dept WHERE dept.deptno=remove_dept.deptno;

    IF SQL%NOTFOUND THEN
        RAISE no_result;
    END IF;
    total_depts:=total_depts-1; --總的部門(mén)數(shù)減1
  EXCEPTION
     WHEN no_result THEN
        DBMS_OUTPUT.PUT_LINE('溫馨提示:你需要的數(shù)據(jù)不存在!');
     WHEN exception_deptno_remaining THEN
        DBMS_OUTPUT.PUT_LINE('溫馨提示:違反數(shù)據(jù)完整性約束!');
     WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('溫馨提示:發(fā)生系統(tǒng)錯(cuò)誤!');
  END remove_dept;

  --給指定員工增加指定數(shù)量的工資
  PROCEDURE increase_sal(empno NUMBER, sal_incr NUMBER)
  IS
    curr_sal NUMBER(7, 2); --當(dāng)前工資
  BEGIN
    --得到當(dāng)前工資
    SELECT sal INTO curr_sal FROM emp WHERE emp.empno=increase_sal.empno;

    IF curr_sal IS NULL THEN
       RAISE no_sal;
    ELSE
       UPDATE emp SET sal = sal + increase_sal.sal_incr --當(dāng)前工資加新增的工資
       WHERE emp.empno = increase_sal.empno;
    END IF;
    EXCEPTION
       WHEN NO_DATA_FOUND THEN
          DBMS_OUTPUT.PUT_LINE('溫馨提示:你需要的數(shù)據(jù)不存在!');
       WHEN no_sal THEN
          DBMS_OUTPUT.PUT_LINE('溫馨提示:此員工的工資不存在!');
       WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE('溫馨提示:發(fā)生系統(tǒng)錯(cuò)誤!');
  END increase_sal;

  --給指定員工增加指定數(shù)量的獎(jiǎng)金
  PROCEDURE increase_comm(empno NUMBER, comm_incr NUMBER)
  IS
    curr_comm NUMBER(7,2);
  BEGIN
    --得到指定員工的當(dāng)前資金
    SELECT comm INTO curr_comm
    FROM emp WHERE emp.empno = increase_comm.empno;

    IF curr_comm IS NULL THEN
       RAISE no_comm;
    ELSE
      UPDATE emp SET comm = comm + increase_comm.comm_incr
      WHERE emp.empno=increase_comm.empno;
    END IF;
  EXCEPTION
     WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('溫馨提示:你需要的數(shù)據(jù)不存在!');
     WHEN no_comm THEN
        DBMS_OUTPUT.PUT_LINE('溫馨提示:此員工的獎(jiǎng)金不存在!');
     WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('溫馨提示:發(fā)生系統(tǒng)錯(cuò)誤!');
  END increase_comm;
END MANAGE_EMP_PKG;--創(chuàng)建包體結(jié)束

--調(diào)用
SQL> variable empno number
SQL>execute  :empno:= manage_emp_pkg.hire_emp('HUYONG',PM,1455,5500,14,10)

PL/SQL procedure successfully completed
empno
---------
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)變量參照類(lèi)型，并可向包中的子程序傳遞游標(biāo)變量參數(shù)。

-- *******************************************
  -- 創(chuàng)建包體
  -- 包   名：CURROR_VARIBAL_PKG
  -- 功能描述：在包中引用游標(biāo)變量
  -- 創(chuàng)建人員：胡勇
  -- 創(chuàng)建日期：2010-05-19
  -- Q     Q: 80368704
  -- E-mail : 80368704@yahoo.com.cn
  -- WebSite: http://www.rzrgm.cn/huyong
-- ******************************************
CREATE OR REPLACE PACKAGE CURROR_VARIBAL_PKG AS
  TYPE DeptCurType IS REF CURSOR
  RETURN dept%ROWTYPE; --強(qiáng)類(lèi)型定義

  TYPE CurType IS REF CURSOR;-- 弱類(lèi)型定義

  PROCEDURE OpenDeptVar(
    Cv IN OUT DeptCurType,
    Choice INTEGER DEFAULT 0,
    Dept_no NUMBER DEFAULT 50,
    Dept_name VARCHAR DEFAULT '%');
END;

-- *******************************************
  -- 創(chuàng)建包體
  -- 包   名：CURROR_VARIBAL_PKG
  -- 功能描述：在包中引用游標(biāo)變量
  -- 創(chuàng)建人員：胡勇
  -- 創(chuàng)建日期：2010-05-19
  -- Q     Q: 80368704
  -- E-mail : 80368704@yahoo.com.cn
  -- WebSite: http://www.rzrgm.cn/huyong
-- ******************************************
CREATE OR REPLACE PACKAGE BODY CURROR_VARIBAL_PKG
AS
  PROCEDURE OpenDeptvar(
    Cv IN OUT DeptCurType,
    Choice INTEGER DEFAULT 0,
    Dept_no NUMBER DEFAULT 50,
    Dept_name VARCHAR DEFAULT ‘%’)
  IS
  BEGIN
    IF choice =1 THEN
      OPEN cv FOR SELECT * FROM dept WHERE deptno <= dept_no;
    ELSIF choice = 2 THEN
      OPEN cv FOR SELECT * FROM dept WHERE dname LIKE dept_name;
    ELSE
      OPEN cv FOR SELECT * FROM dept;
    END IF;
  END OpenDeptvar;
END CURROR_VARIBAL_PKG;

--定義一個(gè)過(guò)程
CREATE OR REPLACE PROCEDURE UP_OpenCurType(
  Cv IN OUT CURROR_VARIBAL_PKG.CurType,
  FirstCapInTableName CHAR)
AS
BEGIN
  --CURROR_VARIBAL_PKG.CurType采用弱類(lèi)型定義
  --所以可以使用它定義的游標(biāo)變量打開(kāi)不同類(lèi)型的查詢語(yǔ)句
  IF FirstCapInTableName = 'D' THEN
    OPEN cv FOR SELECT * FROM dept;
  ELSE
    OPEN cv FOR SELECT * FROM emp;
  END IF;
END UP_OpenCurType;

--定義一個(gè)應(yīng)用
DECLARE
  DeptRec Dept%ROWTYPE;
  EmpRec Emp%ROWTYPE;
  Cv1 CURROR_VARIBAL_PKG.deptcurtype;
  Cv2 CURROR_VARIBAL_PKG.curtype;
BEGIN
  DBMS_OUTPUT.PUT_LINE('游標(biāo)變量強(qiáng)類(lèi)型定義應(yīng)用');
  CURROR_VARIBAL_PKG.OpenDeptVar(cv1, 1, 30);
  FETCH cv1 INTO DeptRec;
  WHILE cv1%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(DeptRec.deptno||':'||DeptRec.dname);
    FETCH cv1 INTO DeptRec;
  END LOOP;
  CLOSE cv1;

  DBMS_OUTPUT.PUT_LINE('游標(biāo)變量弱類(lèi)型定義應(yīng)用');
  CURROR_VARIBAL_PKG.OpenDeptvar(cv2, 2, dept_name => 'A%');
  FETCH cv2 INTO DeptRec;
  WHILE cv2%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(DeptRec.deptno||':'||DeptRec.dname);
    FETCH cv2 INTO DeptRec;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('游標(biāo)變量弱類(lèi)型定義應(yīng)用—dept表');
  UP_OpenCurType(cv2, 'D');
  FETCH cv2 INTO DeptRec;
  WHILE cv2%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(deptrec.deptno||':'||deptrec.dname);
    FETCH cv2 INTO deptrec;
  END LOOP;

  DBMS_OUTPUT.PUT_LINE('游標(biāo)變量弱類(lèi)型定義應(yīng)用—emp表');
  UP_OpenCurType(cv2, 'E');
  FETCH cv2 INTO EmpRec;
  WHILE cv2%FOUND LOOP
    DBMS_OUTPUT.PUT_LINE(emprec.empno||':'||emprec.ename);
    FETCH cv2 INTO emprec;
  END LOOP;
  CLOSE cv2;
END;
----------運(yùn)行結(jié)果-------------------
游標(biāo)變量強(qiáng)類(lèi)型定義應(yīng)用
10:ACCOUNTING
20:RESEARCH
30:SALES
游標(biāo)變量弱類(lèi)型定義應(yīng)用
10:ACCOUNTING
游標(biāo)變量弱類(lèi)型定義應(yīng)用—dept表
10:ACCOUNTING
20:RESEARCH
30:SALES
40:OPERATIONS
50:50abc
60:Developer
游標(biāo)變量弱類(lèi)型定義應(yīng)用—emp表
7369:SMITH
7499:ALLEN
7521:WARD
7566:JONES
7654:MARTIN
7698:BLAKE
7782:CLARK
7788:SCOTT
7839:KING
7844:TURNER
7876:ADAMS
7900:JAMES
7902:FORD
7934:MILLER

PL/SQL procedure successfully completed

7.5 子程序重載

PL/SQL 允許對(duì)包內(nèi)子程序和本地子程序進(jìn)行重載。所謂重載時(shí)指兩個(gè)或多個(gè)子程序有相同的名稱(chēng)，但擁有不同的參數(shù)變量、參數(shù)順序或參數(shù)數(shù)據(jù)類(lèi)型。

例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@yahoo.com.cn
  -- WebSite: http://www.rzrgm.cn/huyong
-- ******************************************
CREATE OR REPLACE PACKAGE DEMO_PKG1
IS
    DeptRec dept%ROWTYPE;
    V_sqlcode NUMBER;
    V_sqlerr VARCHAR2(2048);

  --兩個(gè)子程序名字相同,但參數(shù)類(lèi)型不同
    FUNCTION query_dept(dept_no IN NUMBER)
    RETURN INTEGER;

    FUNCTION query_dept(dept_no IN VARCHAR2)
    RETURN INTEGER;
END DEMO_PKG1;

-- *******************************************
  -- 創(chuàng)建包體
  -- 包   名：DEMO_PKG1
  -- 功能描述：創(chuàng)建包對(duì)子程序重載進(jìn)行測(cè)試
  -- 創(chuàng)建人員：胡勇
  -- 創(chuàng)建日期：2010-05-22
  -- Q     Q: 80368704
  -- E-mail : 80368704@yahoo.com.cn
  -- WebSite: http://www.rzrgm.cn/huyong
-- ******************************************
CREATE OR REPLACE PACKAGE BODY DEMO_PKG1
IS
  FUNCTION check_dept(dept_no NUMBER)
  RETURN INTEGER
  IS
    deptCnt INTEGER; --指定部門(mén)號(hào)的部門(mén)數(shù)量
  BEGIN
    SELECT COUNT(*) INTO deptCnt FROM dept WHERE deptno = dept_no;
    IF deptCnt > 0 THEN
      RETURN 1;
    ELSE
      RETURN 0;
    END IF;
  END check_dept;

  FUNCTION check_dept(dept_no VARCHAR2)
  RETURN INTEGER
  IS
    deptCnt INTEGER;
  BEGIN
    SELECT COUNT(*) INTO deptCnt FROM dept WHERE deptno=dept_no;
    IF deptCnt > 0 THEN
      RETURN 1;
    ELSE
      RETURN 0;
    END IF;
  END check_dept;

  FUNCTION query_dept(dept_no IN NUMBER)
  RETURN INTEGER
  IS
  BEGIN
    IF check_dept(dept_no) =1 THEN
      SELECT * INTO DeptRec FROM dept WHERE deptno=dept_no;
      RETURN 1;
    ELSE
      RETURN 0;
    END IF;
  END query_dept;

  FUNCTION query_dept(dept_no IN VARCHAR2)
    RETURN INTEGER
  IS
  BEGIN
    IF check_dept(dept_no) =1 THEN
      SELECT * INTO DeptRec FROM dept WHERE deptno = dept_no;
      RETURN 1;
    ELSE
      RETURN 0;
    END IF;
  END query_dept;

END DEMO_PKG1;

7.6 加密實(shí)用程序

ORACLE 提供了一個(gè)實(shí)用工具來(lái)加密或者包裝用戶的PL/SQL，它會(huì)將用戶的PL/SQL改變?yōu)橹挥?/span>ORACLE能夠解釋的代碼版本．

WRAP 實(shí)用工具位于$ORACLE_HOME/BIN.

格式為：

WRAP INAME=<input_file_name> [ONAME=<output_file_name>]

wrap iname=e:\sample.txt

注意：在加密前，請(qǐng)將PL/SQL程序先保存一份，以備后用。

7.7 刪除包

可以使用 DROP PACKAGE 命令對(duì)不需要的包進(jìn)行刪除，語(yǔ)法如下：

DROP PACKAGE [BODY] [user.]package_name;

DROP PROCEDURE OpenCurType; --刪除存儲(chǔ)過(guò)程
--刪除我們實(shí)例中創(chuàng)建的各個(gè)包
DROP PACKAGE demo_pack;
DROP PACKAGE demo_pack1;
DROP PACKAGE emp_mgmt;
DROP PACKAGE emp_package;

7.8 包的管理

包與過(guò)程、函數(shù)一樣，也是存儲(chǔ)在數(shù)據(jù)庫(kù)中的，可以隨時(shí)查看其源碼。若有需要，在創(chuàng)建包時(shí)可以隨時(shí)查看更詳細(xì)的編譯錯(cuò)誤。不需要的包也可以刪除。

同樣，為了避免調(diào)用的失敗，在更新表的結(jié)構(gòu)后，一定要記得重新編譯依賴于它的程序包。在更新了包說(shuō)明或包體后，也應(yīng)該重新編譯包說(shuō)明與包體。語(yǔ)法如下：

ALTER PACKAGE package_name COMPILE [PACKAGE|BODY|SPECIFICATION];

也可以通過(guò)以下數(shù)據(jù)字典視圖查看包的相關(guān)。

DBA_SOURCE, USER_SOURCE, USER_ERRORS,DBA-OBJECTS

如，我們可以用：select text from user_source wherename='DEMO_PKG1';來(lái)查看我們創(chuàng)建的包的源碼。

posted @ 2011-05-26 11:38 .NET快速開(kāi)發(fā)框架閱讀(30100) 評(píng)論(65) 收藏舉報(bào)

刷新頁(yè)面返回頂部

.NET快速開(kāi)發(fā)框架

低代碼開(kāi)發(fā)框架、快速開(kāi)發(fā)框架、WinForm開(kāi)發(fā)框架，給用戶和開(kāi)發(fā)者最佳的.Net框架平臺(tái)方案。了解更多：http://www.guosisoft.com/

[強(qiáng)烈推薦]ORACLE PL/SQL編程詳解之七：程序包的創(chuàng)建與應(yīng)用(聰明在于學(xué)習(xí)，天才在于積累！)

7.2 程序包的定義

7.3 包的開(kāi)發(fā)步驟

7.4 包定義的說(shuō)明

7.5 子程序重載

7.6 加密實(shí)用程序

7.7 刪除包

7.8 包的管理

公告