Oracle REF Cursor 用法（轉帖）

 

Oracle 系列：REF Cursor

 

在上文  Oracle 系列：Cursor  （參見：http://blog.csdn.net/qfs_v/archive/2008/05/06/2404794.aspx）中
 提到個思考：怎樣讓遊標作為參數傳遞？ 解決這個問題就需要用到 REF Cursor 。

 

1，什麼是 REF遊標 ？
 動態關連接果集的臨時對象。即在啟動並執行時候動態決定執行查詢。
 
2，REF 遊標 有什麼作用？
 實現在程式間傳遞結果集的功能，利用REF CURSOR也可以實現BULK SQL，從而提高SQL效能。

 

3，靜態資料指標和REF 遊標的區別是什嗎？
 ①靜態資料指標是靜態定義，REF 遊標是動態關聯；
 ②使用REF 遊標需REF 遊標變數。
 ③REF 遊標能做為參數進行傳遞，而靜態資料指標是不可能的。
 
4，什麼是REF 遊標變數？
 REF遊標變數是一種 引用 REF遊標類型  的變數，指向動態關聯的結果集。

 

5，怎麼使用  REF遊標 ？
 ①聲明REF 遊標類型，確定REF 遊標類型；
  ⑴強型別REF遊標：指定retrun type，REF 遊標變數的類型必須和return type一致。
   文法：Type   REF遊標名   IS   Ref Cursor Return  結果集返回記錄類型；
  ⑵弱類型REF遊標：不指定return type，能和任何類型的CURSOR變數匹配，用於擷取任何結果集。
   文法：Type   REF遊標名   IS   Ref Cursor；

 

 ②聲明Ref 遊標類型變數；
  文法：變數名  已聲明Ref 遊標類型；
  
 ③開啟REF遊標，關連接果集 ；
  文法：Open   Ref 遊標類型變數   For   查詢語句返回結果集；
  
 ④擷取記錄，操作記錄；
  文法：Fatch    REF遊標名 InTo   臨時記錄類型變數或屬性類型變數列表；
  
 ⑤關閉遊標，完全釋放資源；
  文法：Close   REF遊標名；
 
 例子：強型別REF遊標
 /*conn scott/tiger*/
 Declare
  Type MyRefCurA IS  REF CURSOR RETURN emp%RowType;
  Type MyRefCurB IS  REF CURSOR RETURN emp.ename%Type;
  vRefCurA  MyRefCurA;
  vRefCurB  MyRefCurB;
  vTempA  vRefCurA%RowType;
  vTempB  vRefCurB.ename%Type;
  
 Begin
  Open  vRefCurA  For Select  *  from   emp   Where  SAL > 2000;
  Loop
   Fatch  vRefCurA InTo  vTempA;
   Exit  When  vRefCurA%NotFound;
   DBMS_OUTPUT.PUT_LINE(vRefCurA%RowCount||'  '|| vTempA.eno||'  '||vTempA.ename ||'  '||vTempA.sal)
  End Loop;
  Close vRefCurA;
  
  DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------');
  
  Open  vRefCurB  For Select  ename  from   emp   Where  SAL > 2000;
  Loop
   Fatch  vRefCurB InTo  vTempB;
   Exit  When  vRefCurB%NotFound;
   DBMS_OUTPUT.PUT_LINE(vRefCurB%RowCount||'  '||vTempB)
  End Loop;
  Close vRefCurB; 
  
  DBMS_OUTPUT.PUT_LINE('-------------------------------------------------------------------------------------------------------');   
  
  Open  vRefCurA  For Select  *  from   emp   Where  JOB = 'CLERK';
  Loop
   Fatch  vRefCurA InTo  vTempA;
   Exit  When  vRefCurA%NotFound;
   DBMS_OUTPUT.PUT_LINE(vRefCurA%RowCount||'  '|| vTempA.eno||'  '||vTempA.ename ||'  '||vTempA.sal)
  End Loop;
  Close vRefCurA;
 End;
 
 例子：弱類型REF遊標
 /*conn scott/tiger*/
 Declare
  Type MyRefCur  IS  Ref  Cursor;
  vRefCur MyRefCur;
  vtemp  vRefCur%RowType;
 Begin
  Case(&n)
   When  1 Then Open vRefCur  For Select   *   from emp;
   When  2 Then Open vRefCur  For Select   *   from dept;
   Else
    Open vRefCur  For Select   eno,  ename  from emp Where JOB = 'CLERK';
  End Case;
  Close  vRefCur;
 End;

 

6，怎樣讓REF遊標作為參數傳遞？

 

--作為函數傳回值
create or replace function returnacursor return sys_refcursor
is
   v_csr sys_refcursor;
begin
    open v_csr for select a1 from test3;
    return v_csr;
end;
/

declare
c sys_refcursor;
a1 char(2);
begin
  c:=returnacursor;
  loop
    fetch c into a1;
    exit when c%notfound;
    dbms_output.put_line(a1);
  end loop;
  close c;
end;
/

 

--作為參數
create or replace procedure proc_ref_cursor (rc in sys_refcursor) as
  v_a number;
  v_b varchar2(10);
 
begin
  loop
    fetch rc into v_a, v_b;
    exit when rc%notfound;
    dbms_output.put_line(v_a || ' ' || v_b);
  end loop;
end;
/

declare
v_rc sys_refcursor;
begin
  open v_rc for
  select a1,a2 from test3;
  proc_ref_cursor(v_rc);
  close v_rc;
end;
/