PL/SQL 包的建立和應用

PL/SQL 包的建立和應用

PL/SQL 包的建立和應用

①簡介
    包是一組相關過程、函數、變數、常量和遊標等PL/SQL程式設計項目的組合，它具有物件導向程式設計語言的特點，是對這些PL/SQL 程式設計項目的封裝。
包類似於C++和JAVA語言中的類，其中變數相當於類中的成員變數，過程和函數相當於類方法。
把相關的模組歸類成為包，可使開發人員利用物件導向的方法進行預存程序的開發，從而提高系統效能。
    與類相同，包中的程式元素也分為公用元素和私用元素兩種，這兩種元素的區別是他們允許訪問的程式範圍不同，即它們的範圍不同。
公用元素不僅可以被包中的函數、過程所調用，也可以被包外的PL/SQL程式訪問，而私人元素只能被包內的函數和過程式所訪問。
    在PL/SQL程式設計中，使用包不僅可以使程式設計模組化，對外隱藏包內所使用的資訊（通過使用私用變數），而且可以提高程式的執行效率。
因為，當程式首次調用包內函數或過程時，Oracle將整個包調入記憶體，當再次訪問包內元素時，ORACLE直接從記憶體中讀取，而不需要進行磁碟I/O操作，
從而使程式執行效率得到提高。

一個包由兩個分開的部分組成：
包定義（PACKAGE）：包定義部分聲明包內資料類型、變數、常量、遊標、子程式和異常錯誤處理等元素，這些元素為包的公有元素。
包主體（PACKAGE BODY）：包主體則是包定義部分的具體實現，它定義了包定義部分所聲明的遊標和子程式，在包主體中還可以聲明包的私人元素。
包定義和包主體分開編譯，並作為兩部分分開的對象存放在資料庫字典中，詳見資料字典user_source, all_source, dba_source.

②包的定義
包定義的文法如下：
1.建立包定義:
CREATE[OR REPLACE] PACKAGE package_name
    [AUTHID {CURRENT_USER | DEFINER}]
{IS| AS}
    [公有資料類型定義[公有資料類型定義]…]
    [公有遊標聲明[公有遊標聲明]…]
    [公有變數、常量聲明[公有變數、常量聲明]…]
    [公有子程式聲明[公有子程式聲明]…]
END[package_name];
其中：AUTHID CURRENT_USER和AUTHID DEFINER選項說明應用程式在調用函數時所使用的許可權模式，
它們與CREATE FUNCTION語句中invoker_right_clause子句的作用相同。
建立包主體:
CREATE[OR REPLACE] PACKAGE BODYpackage_name
{IS| AS}
    [私人資料類型定義[私人資料類型定義]…]
    [私人變數、常量聲明[私人變數、常量聲明]…]
    [私人子程式聲明和定義[私人子程式聲明和定義]…]
    [公有遊標定義[公有遊標定義]…]
    [公有子程式定義[公有子程式定義]…]
BEGIN
    PL/SQL 陳述式
END[package_name];
其中：在包主體定義公有程式時，它們必須與包定義中所聲明子程式的格式完全一致。
對包內共有元素的調用格式為：包名.元素名稱

③子程式重載
PL/SQL 允許對包內子程式和本地子程式進行重載。
所謂重載時指兩個或多個子程式有相同的名稱，但擁有不同的參數變數、參數順序或參數資料類型。

④刪除包
可以使用DROP PACKAGE命令對不需要的包進行刪除，文法如下：
DROP PACKAGE [BODY] [user.]package_name;

⑤包的管理
DBA_SOURCE, USER_SOURCE, USER_ERRORS, DBA-OBJECTS

⑥例子：
建立的包為demo_pack, 該包中包含一個記錄變數DeptRec、兩個函數和一個過程。
CREATE OR REPLACE PACKAGE demo_pack
IS
    DeptRec dept%ROWTYPE;
    FUNCTION add_dept(dept_no NUMBER, dept_name VARCHAR2, location VARCHAR2)
        RETURN NUMBER;
    FUNCTION remove_dept(dept_no NUMBER)
        RETURN NUMBER;
    PROCEDURE query_dept(dept_no IN NUMBER);
END demo_pack

包主體的建立方法，它實現上面所聲明的包定義
CREATE OR REPLACE PACKAGE BODY demo_pack
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 是違反唯一約束條件的錯誤碼*/
    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 remove_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 remove_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('資料庫中沒有編碼為'||dept_no||'的部門');
      WHEN TOO_MANY_ROWS THEN
          DBMS_OUTPUT.PUT_LINE('程式運行錯誤!請使用遊標');
      WHEN OTHERS THEN
          DBMS_OUTPUT.PUT_LINE(SQLCODE||’----‘||SQLERRM);
  END query_dept;
BEGIN
  Null;
END demo_pack;

Oracle--plsql複合資料型別

--------------------------------------分割線 --------------------------------------

rlwrap - 解決Linux下SQLPLUS退格、上翻鍵亂碼問題

SQLPLUS spool 到動態記錄檔名

Oracle SQLPLUS提示符設定

通過設定SQLPLUS ARRAYSIZE(行預取)加快SQL返回速度

PL/SQL Developer實用技巧分享

--------------------------------------分割線 --------------------------------------