天天看點
傳回

REF CURSOR 總結

REF 遊标：

REF遊标又稱為動态遊标，在運作時使不同的語句與之關聯，動态關聯結果集的臨時對象，即在運作的時候動态決定執行查詢。REF遊标可以使用遊标變量。

遊标變量：

遊标變量是一種引用REF遊标類型的變量，隻想動态關聯的結果集。

遊标變量的類型：

1.具有限制的遊标變量，具有傳回類型的遊标變量也稱為強遊标。

2.無限制的遊标變量，沒有傳回類型的遊标變量也稱為弱遊标。

REF遊标的作用：

實作程式間傳遞結果集的功能，利用REF cursor 也可以實作bulk sql 進而提高sql性能。

靜态遊标和REF遊标的差別：

1.靜态遊标是靜态定義，REF遊标是動态關聯

2.使用REF遊标需要REF遊标變量

3.REF遊标可以作為參數進行傳遞，而靜态遊标是不能作為參數傳遞的。

REF遊标的文法：

1.強類型REF遊标：指定return datatype,REF遊标變量的類型必須和return datatype一緻。

文法：TYPE TYPE_NAME IS REF CURSOR RETURN DATATYPE;

2.弱類型REF遊标：不指定return datatype 能和任何類型的cursor 變量比對，用于擷取任何結果集。

文法：TYPE TYPE_NAME IS REF CURSOR;

SYS_REFCURSOR：

主要用在過程中傳回結果集，如果僅僅為了傳回值，無需自己在標頭中定義遊标類型，隻需直接使用sys_refcursor 即可輕松傳回結果。

提示：

使用靜态光标--通過靜态SQL（但不用ref光标）--比使用ref光标效率高，而ref光标的使用僅限于以下幾種情況：

把結果集傳回給用戶端；

在多個子例程之間共享光标（實際上與上面提到的一點非常類似）；

沒有其他有效的方法來達到你的目标時，則使用ref光标，正如必須用動态SQL時那樣；

簡言之，首先考慮使用靜态SQL，隻有絕對必須使用ref光标時才使用ref光标，也有人建議盡量使用隐式遊标，避免編寫附加的遊标控制代碼（聲明，打開，擷取，關閉），也不需要聲明變量來儲存從遊标中擷取的資料。

PACKAGE

示例中涉及到包(package)和包體(package body)以下為package 用法的說明：

包結構：

一個包由兩個分開的部分組成：包規範和包體

1.包定義（package）：包定義部分是為應用程式的接口，聲明包内資料類型、變量、常量、遊标、子程式和異常錯誤處理等元素，這些元素為包的公有元素。

文法：

CREATE [OR REPLACE] PACKAGE PACKAGE_NAME

{IS|AS}

[公有資料類型定義]

[公有遊标聲明]

[公有變量、常量聲明]

[公有子程式聲明]

END [PACKAGE_NAME];

2.包主體（package body）：包主體則是包定義部分的具體實作，它定義了包定義部分所聲明的遊标和子程式，在包主體中還可以聲明包的私有元素。如果在包主體中的遊标或子程式并沒有在標頭中定義，那麼這個遊标或子程式是私有的。

CREATE [OR REPLACE] PACKAGE BODY PACKAGE_NAME

{IS |AS}

[私有資料類型定義]

[私有變量、常量]

[私有子程式聲明和定義]

[公有子程式定義]

BEGIN

執行部分（初始化部分）；

與類相同，包中的程式元素也分為公有元素和私有元素兩種，這兩種元素的差別是他們允許通路程式範圍不同，即他們的作用域不同。公有元素不僅可以被包中的函數、過程所調用也可以被包外的PL/SQL程式通路，而私有元素隻能被包内的函數和過程所通路。包定義和包主體分開編譯，并作為兩部分分開的對象存放在資料庫字典中。包定義一定要在包主體前面編譯，包主體可以沒有，但是包定義一定要有，包的名稱和包體的名稱要保持一緻。

示例1：強類型REF遊标

DECLARE
TYPE REF_CURSOR IS REF CURSOR RETURN DJ_DJB%ROWTYPE;
------遊标僅能打開DJ_DJB表的資料

REF_C REF_CURSOR;
----遊标變量

V_DJB DJ_DJB%ROWTYPE;
SELECTION VARCHAR2(1):=('&請輸入：');
BEGIN
  IF SELECTION='1' THEN
    OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2016%' AND ROWNUM<10;
    ELSIF SELECTION='2' THEN
    OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2017%' AND ROWNUM<10;
    END IF;
    LOOP
      FETCH REF_C INTO V_DJB;
      EXIT WHEN REF_C%NOTFOUND;
      DBMS_OUTPUT.ENABLE(BUFFER_SIZE=>NULL);
      DBMS_OUTPUT.PUT_LINE(V_DJB.SLBH||','||V_DJB.BDCZH);
      END LOOP;
      CLOSE REF_C;
      END;

示例2：弱類型REF遊标

DECLARE
TYPE REF_CURSOR IS REF CURSOR;
REF_C REF_CURSOR;

V_V1 VARCHAR2(100);

SELECTION VARCHAR2(1):=('&請輸入：');

BEGIN
  IF SELECTION='1' THEN
    OPEN REF_C FOR SELECT TO_CHAR(BDCZH) FROM DJ_DJB WHERE SLBH LIKE '2016%' AND ROWNUM<10;
        -------弱類型遊标對目标表沒有限制，資料可以使來自任何表

    ELSIF SELECTION='2' THEN
    OPEN REF_C FOR SELECT TO_CHAR(QLRMC) FROM DJ_QLRGL WHERE SLBH LIKE '2017%' AND ROWNUM<10 AND QLRLX='權利人';
            -------弱類型遊标對目标表沒有限制，資料可以使來自任何表
    END IF;

    LOOP
      FETCH REF_C INTO V_V1;
      EXIT WHEN REF_C%NOTFOUND;
      DBMS_OUTPUT.ENABLE(BUFFER_SIZE=>NULL);
      DBMS_OUTPUT.PUT_LINE('輸出結果值：'||V_V1);
      END LOOP;
      CLOSE REF_C;
      END;

示例3：SYS_REFCURSOR

DECLARE

V_DJB DJ_DJB%ROWTYPE;

REF_C SYS_REFCURSOR; ---利用SYS_REFCUSOR 來聲明遊标變量

SELECTION VARCHAR2(1):=('&請輸入：');
BEGIN
  IF SELECTION='1' THEN
    OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2016%' AND ROWNUM<10;
    ELSIF SELECTION='2' THEN
    OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2017%' AND ROWNUM<11;
    END IF;

 LOOP
   FETCH REF_C INTO V_DJB;
   EXIT WHEN REF_C%NOTFOUND;
   DBMS_OUTPUT.ENABLE(BUFFER_SIZE=>NULL);
   DBMS_OUTPUT.PUT_LINE(V_DJB.SLBH||','||V_DJB.BDCZH);
   END LOOP;
   CLOSE REF_C;
   END;           
CREATE OR REPLACE PACKAGE EMP_T
IS
TYPE REF_CURSOR IS REF CURSOR RETURN DJ_DJB%ROWTYPE;
PROCEDURE GET_BDCZH(REF_C IN OUT REF_CURSOR,SELECTION VARCHAR2);
PROCEDURE RETURN_C(SELECTION VARCHAR2);

END;

CREATE OR REPLACE PACKAGE BODY EMP_T 
IS
PROCEDURE GET_BDCZH (REF_C IN OUT REF_CURSOR,SELECTION VARCHAR2)
  IS
  BEGIN
    IF SELECTION =1 THEN
      OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2016%' AND ROWNUM<10;
      ELSIF SELECTION=2 THEN
      OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2017%' AND ROWNUM<10;
      END IF;
      END;

PROCEDURE RETURN_C (SELECTION VARCHAR2)
  IS
  V_1 REF_CURSOR;
  V_DJB dj_djb%ROWTYPE;

  BEGIN
    GET_BDCZH(V_1,SELECTION);
    LOOP
      FETCH V_1 INTO V_DJB;
      EXIT WHEN V_1%NOTFOUND;
      DBMS_OUTPUT.ENABLE(BUFFER_SIZE=>NULL);
      DBMS_OUTPUT.PUT_LINE(V_DJB.SLBH||','||V_DJB.BDCZH);
      END LOOP;
      CLOSE V_1;
      END;
      END;
---調用
begin
  EMP_T.RETURN_C(1);
  END;           
DECLARE
TYPE REF_CURSOR IS REF CURSOR;
REF_C REF_CURSOR;

TYPE R_DJB IS TABLE OF DJ_DJB%ROWTYPE INDEX BY BINARY_INTEGER;
V_DJB R_DJB;
SELECTION VARCHAR2(1):=('&請輸入：');

BEGIN
  IF SELECTION='1' THEN
  OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2016%' AND ROWNUM<100;
  ELSIF SELECTION='2' THEN
  OPEN REF_C FOR SELECT * FROM DJ_DJB WHERE SLBH LIKE '2017%' AND ROWNUM<100;
  END IF;
  LOOP
    FETCH REF_C BULK COLLECT INTO V_DJB LIMIT 10;
    ---EXIT WHEN REF_C%NOTFOUND;
     DBMS_OUTPUT.ENABLE(BUFFER_SIZE=>NULL);
    ---FOR I IN 1..V_DJB.COUNT LOOP
    FOR I IN V_DJB.FIRST..V_DJB.LAST LOOP
      DBMS_OUTPUT.PUT_LINE(V_DJB(I).SLBH||','||V_DJB(I).BDCZH||'I 的值為：'||I);
      END LOOP;
      DBMS_OUTPUT.PUT_LINE('ROWCOUNT的值：'||REF_C%ROWCOUNT);
      EXIT WHEN REF_C%NOTFOUND;
      END LOOP;
      END;
sys_refcursor ref cursor 動态遊标
上一篇: Oracle：select 或 inactive 會話語句産生鎖？
下一篇: imp

繼續閱讀