Oracle 筆記（八）、PL/SQL 進階應用程式（遊標、預存程序、函數、程式包）

一、遊標
二、預存程序
三、函數
    過程與函數的異同
四、程式包

一、遊標
    遊標是一種 PL/SQL 控制結構，可以對SQL語句的處理進行顯式控制，便於對錶的資料逐條進行處理。
ps.當表中資料量大的時候，不建議使用遊標（效率不高，耗費資源），但是它能逐條取資料方法靈活。
    遊標是記錄的指標，利用遊標對活動集的更新或刪除會反饋到表的記錄上。

遊標屬性：%FOUND、%NOTFOUND、%ROWCOUNT、%ISOPEN

1、顯式遊標
    顯式遊標是由使用者顯式聲明的遊標。根據在遊標中定義的查詢，查詢返回的行集可以包含零或多行，這些行稱為活動集。遊標將指向活動集中的當前行。

顯式遊標操縱過程：聲明、開啟、從遊標中擷取記錄、關閉。

SET SERVEROUTPUT ON;
DECLARE
  CURSOR cur IS
    SELECT * FROM books;
  myrecord books%ROWTYPE;
BEGIN
  OPEN cur;
  LOOP
    FETCH cur INTO books;
    EXIT WHEN cur%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(books.id || ' ' || books.name);
  END LOOP;
  CLOSE cur;
END;

1.1、帶參數的顯式遊標：參數不需指定長度或者精度。
1.2、FOR 迴圈遊標：採用遍曆方式，自動開啟、提取和關閉遊標。（能否利用 %ROWCOUNT 獲得遊標提取的行數？）

DECLARE
  /* 定義帶參數遊標 */
  CURSOR cur_para(id varchar2) IS
  SELECT books_name FROM books WHERE books_id = id;
BGEIN
  /* 調用帶參數遊標，並以 FOR 迴圈方式處理 */
  FOR cur IN cur_para('0001') LOOP
    DBMS_OUTPUT.PUT_LINE(cur.books_id || ' ' || cur.books_id);
  END LOOP;
END;

1.3、使用顯示遊標刪除或更新記錄
定義時：需使用 SELECT ... FOR UPDATE 語句表示事物的鎖定；
執行時：需使用 WHERE CURRENT OF curXXX 子句指定遊標的當前行。

  /* 定義部分 */
  CURSOR cur IS
  SELECT name FROM deptment FOR UPDATE;
....
  /* 執行部分 */
  UPDATE deptment SET name=name || '_tt' WHERE CURRENT OF cur;

2、隱式遊標
不需聲明，開啟和關閉的遊標。PL/SQL 為所有的 SQL 資料操縱語句隱式聲明遊標，它是不能直接命名和控制。

BEGIN
  FROM cur IN (SELECT name FROM deptment) LOOP
    DBMS_OUTPUT.PUT_LINE(cur.books_id || ' ' || cur.books_id);
  END LOOP;
END;

ps.
匿名塊：每次執行時都需要被編譯，並且無法儲存到資料庫中，別的 PL/SQL 塊也無法調用它。
命名塊：儲存在資料庫中，屬於資料庫物件。

排錯：SHOW ERRORS PROCEDURE/FUNCTION/PACKAGE obj_name;

二、預存程序

CREATE OR REPLACE PROCEDURE test (value IN varchar2, value2 OUT NUMBER)
  /* 參數，不需指定長度或精度 */
IS
  /* 局部變數，省略 DECLARE 關鍵字，需有長度 */
  identity NUMBER;
BEGIN
  SELECT ITEMRATE INTO identity
    FROM itemFile
    WHERE itemcode = value;
  IF identity < 200 THEN
    value2 := 200;
  ELSE
    value2 :=50;
  END IF;
END;

● 匿名塊執行過程

DECLARE
  tvalue2 NUMBER;
BEGIN
  test('i202', tvalue2);
  DBMS_OUTPUT.PUT_LINE('value2的值為：' || TO_CHAR(value2));
END;

● 單獨執行
    EXECUTE myproc('0001');

三、函數
    函數的主要特性是它必須返回一個值。建立函數時通過 RETURN 子句指定函數傳回值的資料類型。
函數的一些限制：
  ● 函數只能帶有 IN 參數，不能帶有 IN OUT 或 OUT 參數。
  ● 形式參數必須只使用資料庫類型，不能使用 PL/SQL 類型。
  ● 函數的傳回型別必須是資料庫類型。

CREATE OR REPLACE FUNCTION item_price_rage (price NUMBER)
   /* 參數、指定傳回型別 */
  RETURN varchar2
AS
   /* 定義局部變數 */
  min_price NUMBER;
  max_price NUMBER;
BEGIN
  SELECT MAX(ITEMRATE), MIN(ITEMRATE) INTO max_price, min_price
    FROM itemfile;
  IF price >= min_price AND price <= max_price THEN
    RETURN '輸入的單價介於最低價與最高價之間';
  ELSE
    RETURN '超出範圍';
  END IF;
END;

● 匿名塊執行函數

DECLARE
  p NUMBER := 300;
  MSG varchar2(200);
BEGIN
  MSG := item_price_range(p);
  DBMS_OUTPUT.PUT_LINE(MSG);
END;

● SELECT查詢調用（因為函數必須有傳回值）
    SELECT myfunction FROM dual;

    過程與函數的異同
過程：
    作為 PL/SQL 陳述式執行；
    在規範中不包含 RETURN 子句；
    不返回任何值（只有輸入/輸出參數，結果集）；
    可以包含 RETURN 語句，但是與函數不同，它不能用於傳回值。
函數：
    作為運算式的一部分調用；
    必須在規範中包含 RETURN 子句；
    必須返回單個值；
    必須包含至少一條 RETURN 語句。

四、程式包
    程式包是一種資料庫物件，它是對相關 PL/SQL 類型、子程式、遊標、異常、變數和常量的封裝。
程式包規範：宣告類型、變數、常量、異常、遊標和子程式。
程式包主體：用於實現在程式包規範中定義的遊標、子程式。

4.1、程式包規範
包含應用程式所需的程式包資源，是與應用程式的介面。

CREATE OR REPLACE PACKAGE pack_me
IS
  PROCEDURE order_proc (orno varchar2);
  FUNCTION order_fun (ornos varchar2) RETURN varchar2;
END pack_me;

*建立 pack_me 包，並聲明了子程式 order_proc 和 order_fun，並交由程式包主體實現。

4.2、程式包主體
當程式包規範中指定了子程式和遊標時，必須有程式包主體。

CREATE OR REPLACE PACKAGE BODY pack_me
AS
  /* 實現定義的預存程序 */
  PROCEDURE order_proc (orno varchar2)
  IS
    stst CHAR(1);
  BEGIN
    SELECT ostatus INTO stat FROM order_master
      WHERE orderno = orno;
    IF stat = 'p' THEN
      DBMS_OUTPUT.PUT_LINE('暫掛的訂單');
    ELSE
      DBMS_OUTPUT.PUT_LINE('已完成的訂單');
    END IF;
  END order_proc;
  /* 實現定義的函數 */
  FUNCTION order_fun(ornos varchar2) RETURN varchar2
  IS
    icode varchar2(5);
    ocode varchar2(5);
    qtyord NUMBER;
    qtydeld NUMBER;
  BEGIN
    SELECT qty_ord, qty_deld, itemcode, ordernc INTO qtyord, qtydeld, icode, ocode
      FROM order_detail
      WHERE orderno = ornos;
    IF qtyord < qtydeld THEN
      RETURN ocode;
    ELSE
      RETURN icode;
    END IF;
  END order_fun;
END pack_me;

● 要執行 pack_me包中的 order_proc過程，輸入
    EXECUTE pack_me.order_proc('o002');

● 要執行包中預定義的函數

DECLARE
  msg varchar2(10);
BEGIN
  msg := pack_me.order_fun('o002');
  DBMS_OUTPUT.PUT_LINE('值是 ' || msg);
END;

 
4.3、程式包的優點
    程式包將相關的功能在邏輯上組織在一起，模組化，資訊隱藏和更好的效能。
ps.資料字典視圖 USER_SOURCE 包含預存程序的代碼文本。

4.4、內建程式包
STANDARD 和 DBMS_STANDARD：定義和擴充 PL/SQL 語言環境
DBMS_LOB：提供對 Oracle LOB 資料類型進行操作的功能
DBMS_LOCK：使用者定義的鎖
DBMS_OUTPUT：處理 PL/SQL 塊和子程式輸出調試資訊
DBMS_SESSION：提供 ALTER SESSION 命令的 PL/SQL 等效功能
DBMS_ROWID：獲得 ROWID 的詳細資料
DBMS_RANDOM：提供隨機數產生器
DBMS_SQL：允許使用者使用動態 SQL，構造和執行任意 DML 或 DDL 語句
DBMS_JOB：提交和管理在資料庫中執行的定時任務
DBMS_XMLDOM：用 DOM 模型讀寫 XML 類型的資料
DBMS_XMLPARSER：XML 解析，處理 XML 文檔內容和結構
DBMS_XMLGEN：將 SQL 查詢結果轉換為規範的 XML 格式
DBMS_XMLQUERY：提供將資料轉換為 XML 類型的功能
DBMS_XSLPROCESSOR：提供 XSLT 功能，轉換 XML 文檔
UTL_FILE：用 PL/SQL 程式來讀寫作業系統文字檔