REF CURSOR 總結(jié)

REF 游標：
REF游標又稱為動態(tài)游標，在運行時使不同的語句與之關(guān)聯(lián)，動態(tài)關(guān)聯(lián)結(jié)果集的臨時對象，即在運行的時候動態(tài)決定執(zhí)行查詢。REF游標可以使用游標變量。
游標變量：
游標變量是一種引用REF游標類型的變量，只想動態(tài)關(guān)聯(lián)的結(jié)果集。
游標變量的類型：
1.具有約束的游標變量，具有返回類型的游標變量也稱為強游標。
2.無約束的游標變量，沒有返回類型的游標變量也稱為弱游標。
REF游標的作用：
實現(xiàn)程序間傳遞結(jié)果集的功能，利用REF cursor 也可以實現(xiàn)bulk sql 從而提高sql性能。
靜態(tài)游標和REF游標的區(qū)別：
1.靜態(tài)游標是靜態(tài)定義，REF游標是動態(tài)關(guān)聯(lián)
2.使用REF游標需要REF游標變量
3.REF游標可以作為參數(shù)進行傳遞，而靜態(tài)游標是不能作為參數(shù)傳遞的。
REF游標的語法：
1.強類型REF游標：指定return datatype,REF游標變量的類型必須和return datatype一致。
語法：TYPE TYPE_NAME IS REF CURSOR RETURN DATATYPE;
2.弱類型REF游標：不指定return datatype 能和任何類型的cursor 變量匹配，用于獲取任何結(jié)果集。
語法：TYPE TYPE_NAME IS REF CURSOR;
SYS_REFCURSOR：
主要用在過程中返回結(jié)果集，如果僅僅為了返回值，無需自己在包頭中定義游標類型，只需直接使用sys_refcursor 即可輕松返回結(jié)果。
提示：
使用靜態(tài)光標--通過靜態(tài)SQL（但不用ref光標）--比使用ref光標效率高，而ref光標的使用僅限于以下幾種情況：
把結(jié)果集返回給客戶端；
在多個子例程之間共享光標（實際上與上面提到的一點非常類似）；
沒有其他有效的方法來達到你的目標時，則使用ref光標，正如必須用動態(tài)SQL時那樣；
簡言之，首先考慮使用靜態(tài)SQL，只有絕對必須使用ref光標時才使用ref光標，也有人建議盡量使用隱式游標，避免編寫附加的游標控制代碼（聲明，打開，獲取，關(guān)閉），也不需要聲明變量來保存從游標中獲取的數(shù)據(jù)。
PACKAGE
示例中涉及到包(package)和包體(package body)以下為package 用法的說明：
包結(jié)構(gòu)：
一個包由兩個分開的部分組成：包規(guī)范和包體
1.包定義（package）：包定義部分是為應(yīng)用程序的接口，聲明包內(nèi)數(shù)據(jù)類型、變量、常量、游標、子程序和異常錯誤處理等元素，這些元素為包的公有元素。
語法：
CREATE [OR REPLACE] PACKAGE PACKAGE_NAME
{IS|AS}
[公有數(shù)據(jù)類型定義]
[公有游標聲明]
[公有變量、常量聲明]
[公有子程序聲明]
END [PACKAGE_NAME];
2.包主體（package body）：包主體則是包定義部分的具體實現(xiàn)，它定義了包定義部分所聲明的游標和子程序，在包主體中還可以聲明包的私有元素。如果在包主體中的游標或子程序并沒有在包頭中定義，那么這個游標或子程序是私有的。
語法：
CREATE [OR REPLACE] PACKAGE BODY PACKAGE_NAME
{IS |AS}
[私有數(shù)據(jù)類型定義]
[私有變量、常量]
[私有子程序聲明和定義]
[公有子程序定義]
BEGIN
執(zhí)行部分（初始化部分）；
END [PACKAGE_NAME];
與類相同，包中的程序元素也分為公有元素和私有元素兩種，這兩種元素的區(qū)別是他們允許訪問程序范圍不同，即他們的作用域不同。公有元素不僅可以被包中的函數(shù)、過程所調(diào)用也可以被包外的PL/SQL程序訪問，而私有元素只能被包內(nèi)的函數(shù)和過程所訪問。包定義和包主體分開編譯，并作為兩部分分開的對象存放在數(shù)據(jù)庫字典中。包定義一定要在包主體前面編譯，包主體可以沒有，但是包定義一定要有，包的名稱和包體的名稱要保持一致。

示例1：強類型REF游標

DECLARE
TYPE REF_CURSOR IS REF CURSOR RETURN DJ_DJB%ROWTYPE;
------游標僅能打開DJ_DJB表的數(shù)據(jù)

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;
        -------弱類型游標對目標表沒有限制，數(shù)據(jù)可以使來自任何表

    ELSIF SELECTION='2' THEN
    OPEN REF_C FOR SELECT TO_CHAR(QLRMC) FROM DJ_QLRGL WHERE SLBH LIKE '2017%' AND ROWNUM<10 AND QLRLX='權(quán)利人';
            -------弱類型游標對目標表沒有限制，數(shù)據(jù)可以使來自任何表
    END IF;

    LOOP
      FETCH REF_C INTO V_V1;
      EXIT WHEN REF_C%NOTFOUND;
      DBMS_OUTPUT.ENABLE(BUFFER_SIZE=>NULL);
      DBMS_OUTPUT.PUT_LINE('輸出結(jié)果值：'||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;

示例4：REF 游標作為參數(shù)傳遞

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;
---調(diào)用
begin
  EMP_T.RETURN_C(1);
  END;

示例5：使用BULK COLLECT INTO.......批量提取數(shù)據(jù)

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;

BY WOLIAHAITO 2018.04.03