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 獲得遊标提取的行數？）

  /* 定義帶參數遊标 */   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);

1.3、使用顯示遊标删除或更新記錄

定義時：需使用 SELECT ... FOR UPDATE 語句表示事物的鎖定；

執行時：需使用 WHERE CURRENT OF curXXX 子句指定遊标的目前行。

  /* 定義部分 */

  SELECT name FROM deptment FOR UPDATE;

....

  /* 執行部分 */

  UPDATE deptment SET name=name || '_tt' WHERE CURRENT OF cur;

2、隐式遊标

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

  FROM cur IN (SELECT name FROM deptment) LOOP

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;   SELECT ITEMRATE INTO identity     FROM itemFile     WHERE itemcode = value;   IF identity < 200 THEN     value2 := 200;   ELSE     value2 :=50;   END IF;

● 匿名塊執行過程

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

● 單獨執行

    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;   SELECT MAX(ITEMRATE), MIN(ITEMRATE) INTO max_price, min_price     FROM itemfile;   IF price >= min_price AND price <= max_price THEN     RETURN '輸入的單價介于最低價與最高價之間';     RETURN '超出範圍';

● 匿名塊執行函數

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

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

    SELECT myfunction FROM dual;

過程：

    作為 PL/SQL 語句執行；

    在規範中不包含 RETURN 子句；

    不傳回任何值（隻有輸入/輸出參數，結果集）；

    可以包含 RETURN 語句，但是與函數不同，它不能用于傳回值。

函數：

    作為表達式的一部分調用；

    必須在規範中包含 RETURN 子句；

    必須傳回單個值；

    必須包含至少一條 RETURN 語句。

    程式包是一種資料庫對象，它是對相關 PL/SQL 類型、子程式、遊标、異常、變量和常量的封裝。

程式包規範：聲明類型、變量、常量、異常、遊标和子程式。

程式包主體：用于實作在程式包規範中定義的遊标、子程式。

4.1、程式包規範

包含應用程式所需的程式包資源，是與應用程式的接口。

CREATE OR REPLACE PACKAGE pack_me   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   /* 實作定義的存儲過程 */   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     icode varchar2(5);     ocode varchar2(5);     qtyord NUMBER;     qtydeld NUMBER;     SELECT qty_ord, qty_deld, itemcode, ordernc INTO qtyord, qtydeld, icode, ocode       FROM order_detail       WHERE orderno = ornos;     IF qtyord < qtydeld THEN       RETURN ocode;       RETURN icode;   END order_fun;

● 要執行 pack_me包中的 order_proc過程，輸入

    EXECUTE pack_me.order_proc('o002');

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

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

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 程式來讀寫作業系統文本檔案

本文轉自 qvodnet 51CTO部落格，原文連結:http://blog.51cto.com/bks2015/1982991