熱點推薦:
您现在的位置: 電腦知識網 >> 編程 >> Oracle >> 正文

PL/SQL-嵌套游標cursor

2013-11-13 16:12:06  來源: Oracle 

        cursor() 函數可以將一個查詢結果集封裝成一個類似 REF CURSOR 的游標變量可以 FETCH 記錄也可以作為 REF CURSOR 類型的參數進行傳遞它被稱為嵌套游標(nested cursor)
        FETCH 記錄
        我們先看一下測試表 test 和 test 的數據

 

SQL> select * from test;
 
         A

        
        
        
        
 
SQL> select * from test;
 
        ID NAME

         yuechaotian
         yuechaotian
         yuechaotian
         yuechaotian
         yuechaotian
 
  我們可能會發出這樣一個查詢
 


SQL> select id name (select a from test where a = testid)
      from test;
select id name (select a from test where a = testid)
                  *
ERROR 位於第 行:
ORA: 單行子查詢返回多個行
 
  因為表 test 中有兩條 a= 的記錄所以這個查詢執行失敗了但有時候我們確實需要這樣的查詢怎麼辦呢?你可以試試 cursor() 函數
 

SQL> set serveroutput on
SQL> declare
      cursor cur_test is
        select id name cursor(select a from test where a = testid)
          from test;
      rec_test test%rowtype;
 
      cur_test sys_refcursor;
      rec_test test%rowtype;
    begin
     open cur_test;
     loop
       fetch cur_test into rec_testid rec_testname cur_test;
       exit when cur_test%notfound;
       dbms_outputput_line(rec_testid: || rec_testid || | rec_testname: || rec_testname);
       這裡不需要再顯式 OPEN 游標 cur_test也不需要顯式關閉
       loop
         fetch cur_test into rec_test;
         exit when cur_test%notfound;
         dbms_outputput_line( rec_testa: || rec_testa );
       end loop;
     end loop;
     close cur_test;
   end;
   /
rec_testid: | rec_testname: yuechaotian
rec_testa:
rec_testa:
rec_testid: | rec_testname: yuechaotian
rec_testa:
rec_testid: | rec_testname: yuechaotian
rec_testa:
rec_testid: | rec_testname: yuechaotian
rec_testid: | rec_testname: yuechaotian
 
PL/SQL 過程已成功完成
 


 
  怎麼樣?達到你的目的了吧我們再看一個嵌套了兩個 cursor() 函數的例子
 

SQL> declare
      嵌套定義游標
      cursor cur_test is
        select id name cursor(select a cursor(select * from dual)
                                            from test
                                          where testa = testid)
          from test;
      cur_test sys_refcursor;
      cur_dual  sys_refcursor;
     rec_test test%rowtype;
     rec_test test%rowtype;
     rec_dual varchar();
   begin
     open cur_test;
     loop
       fetch cur_test into rec_testid rec_testname cur_test;
       exit when cur_test%notfound;
       dbms_outputput_line( rec_testid: || rec_testid ||    rec_testname: || rec_testname);
       這裡不需要再顯式 OPEN 游標 cur_test也不需要顯式關閉
       loop
         fetch cur_test into rec_testa cur_dual;
         exit when cur_test%notfound;
         dbms_outputput_line( rec_testa: || rec_testa );
         這裡不需要再顯式 OPEN 游標 cur_dual也不需要顯式關閉
         loop
           fetch cur_dual into rec_dual;
           exit when cur_dual%notfound;
           dbms_outputput_line( rec_dual: || rec_dual );
         end loop;
       end loop;
     end loop;
     close cur_test;
   end;
   /
rec_testid:    rec_testname: yuechaotian
rec_testa:
rec_dual: X
rec_testa:
rec_dual: X
rec_testid:    rec_testname: yuechaotian
rec_testa:
rec_dual: X
rec_testid:    rec_testname: yuechaotian
rec_testa:
rec_dual: X
rec_testid:    rec_testname: yuechaotian
rec_testid:    rec_testname: yuechaotian
 
PL/SQL 過程已成功完成
 
        由以上例子可以看出嵌套游標是隱式打開的它在以下情況下被關閉
        顯式關閉
          父游標再次執行時(比如下一次循環前會先關閉嵌套游標再根據新數據重新打開)
          父游標關閉時
          父游標退出時
        fetch 父游標出錯時
        傳遞參數
        我們先看看測試表中的數據

 

SQL> select * from test;
 
        ID NAME

         yuechaotian
         yuechaotian
         yuechaotian
         yuechaotian
         yuechaotian
         yuechaotian
         yuechaotian
         yuechaotian
         yuechaotian
        yuechaotian
 
已選擇
 
SQL> select * from test order by a;
 
         A

        
        
        
        
        
        
        
        
        
        
 
已選擇
 


 
  下面我要查詢 test 中的數據查詢條件是 testid 在 testa 中對應的記錄數比如我要查詢表 testid 在 testa 中不存在的記錄查詢表 testid 在testa 中存在條的記錄存在條的記錄存在條的記錄……我可以使用嵌套游標實現
 

SQL> create function f_count(cur_names in sys_refcursor) return number
    is
      v_name testname%type;
      n_count number() := ;
    begin
      loop
        fetch cur_names into v_name;
        exit when cur_names%notfound;
        n_count := n_count + ;
     end loop;
     return n_count;
   end f_count;
   /
 
函數已創建
 
SQL> select id name
      from test
     where f_count( cursor( select a from test where a = testid ) ) = ;
 
        ID NAME

         yuechaotian
         yuechaotian
         yuechaotian
 
SQL> select id name
      from test
     where f_count( cursor( select a from test where a = testid ) ) = ;
 
        ID NAME

         yuechaotian
         yuechaotian
         yuechaotian
        yuechaotian
 
SQL> select id name
      from test
     where f_count( cursor( select a from test where a = testid ) ) = ;
 
        ID NAME

         yuechaotian
         yuechaotian
 
SQL> select id name
      from test
     where f_count( cursor( select a from test where a = testid ) ) = ;
 
        ID NAME

         yuechaotian


From:http://tw.wingwit.com/Article/program/Oracle/201311/17957.html
    推薦文章
    Copyright © 2005-2013 電腦知識網 Computer Knowledge   All rights reserved.