一、游標
二、存儲過程
三、函數(shù)
    過程與函數(shù)的異同
四、程序包



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

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


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

顯式游標操縱過程：聲明、打開、從游標中獲取記錄、關閉。

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、帶參數(shù)的顯式游標：參數(shù)不需指定長度或者精度。
1.2、FOR 循環(huán)游標：采用遍歷方式，自動打開、提取和關閉游標。（能否利用 %ROWCOUNT 獲得游標提取的行數(shù)？）

DECLARE
  /* 定義帶參數(shù)游標 */
  CURSOR cur_para(id varchar2) IS
  SELECT books_name FROM books WHERE books_id = id;
BGEIN
  /* 調用帶參數(shù)游標，并以 FOR 循環(huán)方式處理 */
  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 語句表示事物的鎖定；
執(zhí)行時：需使用 WHERE CURRENT OF curXXX 子句指定游標的當前行。

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


2、隱式游標
不需聲明，打開和關閉的游標。PL/SQL 為所有的 SQL 數(shù)據(jù)操縱語句隱式聲明游標，它是不能直接命名和控制。

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

ps.
匿名塊：每次執(zhí)行時都需要被編譯，并且無法存儲到數(shù)據(jù)庫中，別的 PL/SQL 塊也無法調用它。
命名塊：存儲在數(shù)據(jù)庫中，屬于數(shù)據(jù)庫對象。

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



二、存儲過程

CREATE OR REPLACE PROCEDURE test (value IN varchar2, value2 OUT NUMBER)
  /* 參數(shù)，不需指定長度或精度 */
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;

● 匿名塊執(zhí)行過程

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

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



三、函數(shù)
    函數(shù)的主要特性是它必須返回一個值。創(chuàng)建函數(shù)時通過 RETURN 子句指定函數(shù)返回值的數(shù)據(jù)類型。
函數(shù)的一些限制：
  ● 函數(shù)只能帶有 IN 參數(shù)，不能帶有 IN OUT 或 OUT 參數(shù)。
  ● 形式參數(shù)必須只使用數(shù)據(jù)庫類型，不能使用 PL/SQL 類型。
  ● 函數(shù)的返回類型必須是數(shù)據(jù)庫類型。

CREATE OR REPLACE FUNCTION item_price_rage (price NUMBER)
   /* 參數(shù)、指定返回類型 */
  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;

● 匿名塊執(zhí)行函數(shù)

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

● SELECT查詢調用（因為函數(shù)必須有返回值）
    SELECT myfunction FROM dual;



    過程與函數(shù)的異同
過程：
    作為 PL/SQL 語句執(zhí)行；
    在規(guī)范中不包含 RETURN 子句；
    不返回任何值（只有輸入/輸出參數(shù)，結果集）；
    可以包含 RETURN 語句，但是與函數(shù)不同，它不能用于返回值。
函數(shù)：
    作為表達式的一部分調用；
    必須在規(guī)范中包含 RETURN 子句；
    必須返回單個值；
    必須包含至少一條 RETURN 語句。



四、程序包
    程序包是一種數(shù)據(jù)庫對象，它是對相關 PL/SQL 類型、子程序、游標、異常、變量和常量的封裝。
程序包規(guī)范：聲明類型、變量、常量、異常、游標和子程序。
程序包主體：用于實現(xiàn)在程序包規(guī)范中定義的游標、子程序。


4.1、程序包規(guī)范
包含應用程序所需的程序包資源，是與應用程序的接口。

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

*創(chuàng)建 pack_me 包，并聲明了子程序 order_proc 和 order_fun，并交由程序包主體實現(xiàn)。


4.2、程序包主體
當程序包規(guī)范中指定了子程序和游標時，必須有程序包主體。

CREATE OR REPLACE PACKAGE BODY pack_me
AS
  /* 實現(xiàn)定義的存儲過程 */
  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;
  /* 實現(xiàn)定義的函數(shù) */
  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;

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

● 要執(zhí)行包中預定義的函數(shù)

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

 
4.3、程序包的優(yōu)點
    程序包將相關的功能在邏輯上組織在一起，模塊化，信息隱藏和更好的性能。
ps.數(shù)據(jù)字典視圖 USER_SOURCE 包含存儲過程的代碼文本。


4.4、內(nèi)置程序包
STANDARD 和 DBMS_STANDARD：定義和擴展 PL/SQL 語言環(huán)境
DBMS_LOB：提供對 Oracle LOB 數(shù)據(jù)類型進行操作的功能
DBMS_LOCK：用戶定義的鎖
DBMS_OUTPUT：處理 PL/SQL 塊和子程序輸出調試信息
DBMS_SESSION：提供 ALTER SESSION 命令的 PL/SQL 等效功能
DBMS_ROWID：獲得 ROWID 的詳細信息
DBMS_RANDOM：提供隨機數(shù)生成器
DBMS_SQL：允許用戶使用動態(tài) SQL，構造和執(zhí)行任意 DML 或 DDL 語句
DBMS_JOB：提交和管理在數(shù)據(jù)庫中執(zhí)行的定時任務
DBMS_XMLDOM：用 DOM 模型讀寫 XML 類型的數(shù)據(jù)
DBMS_XMLPARSER：XML 解析，處理 XML 文檔內(nèi)容和結構
DBMS_XMLGEN：將 SQL 查詢結果轉換為規(guī)范的 XML 格式
DBMS_XMLQUERY：提供將數(shù)據(jù)轉換為 XML 類型的功能
DBMS_XSLPROCESSOR：提供 XSLT 功能，轉換 XML 文檔
UTL_FILE：用 PL/SQL 程序來讀寫操作系統(tǒng)文本文件