cursor 與refcursor及sys_refcursor的區(qū)別 - Phoenix_...

jacklopy 2011-04-13

展開全文

cursor 與refcursor及sys_refcursor的區(qū)別收藏
今天簡單的總結(jié)一下PL/SQL中cursor(光標(biāo)/游標(biāo))的用法。

相信不少做開發(fā)或維護(hù)的DBA在找工作的時候，遇到過類似的面視問題：請簡單的描述一下光標(biāo)的類型，說一下普通光標(biāo)和REF光標(biāo)之間的區(qū)別，以及什么時候該正確應(yīng)用哪一個?　

這個題目，我著實難住了不少人，其實他們在具體開發(fā)的時候，也還是比較能夠把握正確的光標(biāo)的使用的，但就是說不出來，當(dāng)然了，這與大家自身的溝流交通能力是有關(guān)系的。有的人不善于說，但做的卻很好。扯的扯就走遠(yuǎn)了，最后嘮叨一下：做技術(shù)這條路，能干不能說，或者說會干不會包裝，路是走不"遠(yuǎn)"的。

一、顯式cursor

顯式是相對與隱式cursor而言的，就是有一個明確的聲明的cursor。顯式游標(biāo)的聲明類似如下（詳細(xì)的語法參加plsql ref doc ）：

cursor cursor_name (parameter list) is select ...

游標(biāo)從declare、open、fetch、close是一個完整的生命旅程。當(dāng)然了一個這樣的游標(biāo)是可以被多次open進(jìn)行使用的，顯式cursor是靜態(tài)cursor，她的作用域是全局的，但也必須明白，靜態(tài)cursor也只有pl/sql代碼才可以使用她。下面看一個簡單的靜態(tài)顯式cursor的示例：

declare
          cursor get_gsmno_cur (p_nettype in varchar2) is
             select gsmno
                from gsm_resource
                where nettype=p_nettype and status='0';
           v_gsmno gsm_resource.gsmno%type;
        begin
           open get_gsmno_cur('139');
           loop
                fetch get_gsmno_cur into v_gsmno;
                exit when get_gsmno_cur%notfound;
                dbms_output.put_line(v_gsmno);
           end loop;
           close emp_cur;

           open get_gsmno_cur('138');
           loop
                fetch get_gsmno_cur into v_gsmno;
                exit when get_gsmno_cur%notfound;
                dbms_output.put_line(v_gsmno);
           end loop;
           close get_gsmno_cur;
        end;
        /

上面這段匿名塊用來實現(xiàn)選號的功能，我們顯式的定義了一個get_gsmno_cur，然后根據(jù)不同的號段輸出當(dāng)前系統(tǒng)中該號短對應(yīng)的可用手機(jī)號碼。當(dāng)然了，實際應(yīng)用中沒人這么用的，我只是用來說應(yīng)一個顯式cursor的用法。

二、隱式cursor

隱式cursor當(dāng)然是相對于顯式而言的，就是沒有明確的cursor的declare。在Oracle的PL/SQL中，所有的DML操作都被Oracle內(nèi)部解析為一個cursor名為SQL的隱式游標(biāo)，只是對我們透明罷了。

另外，我們前面提到的一些循環(huán)操作中的指針for 循環(huán)，都是隱式cursor。　

隱式cursor示例一：

CREATE TABLE zrp (str VARCHAR2(10));
        insert into zrp values ('ABCDEFG');
        insert into zrp values ('ABCXEFG');
        insert into zrp values ('ABCYEFG');
        insert into zrp values ('ABCDEFG');
        insert into zrp values ('ABCZEFG');
        COMMIT;

        SQL> begin
          2    update zrp SET str = 'updateD' where str like '%D%';
          3    ifSQL%ROWCOUNT= 0 then
          4      insert into zrp values ('1111111');
          5    end if;
          6 end;
          7 /

        PL/SQL procedure successfully completed

        SQL> select * from zrp;

        STR
        ----------
        updateD
        ABCXEFG
        ABCYEFG
        updateD
        ABCZEFG

        SQL>
        SQL> begin
          2    update zrp SET str = 'updateD' where str like '%S%';
          3    ifSQL%ROWCOUNT= 0 THEN
          4      insert into zrp values ('0000000');
          5    end if;
          6 end;
          7 /

        PL/SQL procedure successfully completed

        SQL> select * from zrp;

        STR
        ----------
        updateD
        ABCXEFG
        ABCYEFG
        updateD
        ABCZEFG
        0000000
       6 rows selected

        SQL>

隱式cursor示例二：

begin
          for rec in (select gsmno,status from gsm_resource) loop
              dbms_output.put_line(rec.gsmno||'--'||rec.status);
          end loop;
        end;
        /

三、REFcursor

Ref cursor屬于動態(tài)cursor(直到運行時才知道這條查詢)。

從技術(shù)上講，在最基本的層次靜態(tài)cursor和ref cursor是相同的。一個典型的PL/SQL光標(biāo)按定義是靜態(tài)的。Ref光標(biāo)正好相反，可以動態(tài)地打開，或者利用一組SQL靜態(tài)語句來打開，選擇哪種方法由邏輯確定（一個IF/THEN/ELSE代碼塊將打開一個或其它的查詢）。例如，下面的代碼塊顯示一個典型的靜態(tài)SQL光標(biāo)，光標(biāo)C。此外，還顯示了如何通過使用動態(tài)SQL或靜態(tài)SQL來用ref光標(biāo)（在本例中為L_CURSOR）來打開一個查詢：

Declare
      type rc is ref cursor;
      cursor c is select * from dual;

      l_cursor rc;
    begin
      if (to_char(sysdate,'dd') = 30) then
          -- ref cursor with dynamic sql
          open l_cursor for 'select * from emp';
      elsif (to_char(sysdate,'dd') = 29) then
          -- ref cursor with static sql
          open l_cursor for select * from dept;
      else
           -- with ref cursor with static sql
           open l_cursor for select * from dual;
      end if;
      -- the "normal" static cursor
      open c;
    end;
    /

在這段代碼塊中，可以看到了最顯而易見的區(qū)別：無論運行多少次該代碼塊，光標(biāo)C總是select * from dual。相反，ref光標(biāo)可以是任何結(jié)果集，因為"select * from emp"字符串可以用實際上包含任何查詢的變量來代替。

在上面的代碼中，聲明了一個弱類型的REF cursor，下面再看一個強(qiáng)類型（受限）的REF cursor，這種類型的REF cursor在實際的應(yīng)用系統(tǒng)中用的也是比較多的。

create table gsm_resource
    (
      gsmno varchar2(11),
      status varchar2(1),
      price number(8,2),
      store_id varchar2(32)
    );
    insert into gsm_resource values('13905310001','0',200.00,'SD.JN.01');
    insert into gsm_resource values('13905312002','0',800.00,'SD.JN.02');
    insert into gsm_resource values('13905315005','1',500.00,'SD.JN.01');
    insert into gsm_resource values('13905316006','0',900.00,'SD.JN.03');
    commit;

    SQL> declare
      2     type gsm_rec is record(
      3          gsmno varchar2(11),
      4          status varchar2(1),
      5          price number(8,2));
      6
      7     type app_ref_cur_type is ref cursor return gsm_rec;
      8     my_cur app_ref_cur_type;
      9     my_rec gsm_rec;
     10
     11 begin
     12     open my_cur for select gsmno,status,price
     13          from gsm_resource
     14          where store_id='SD.JN.01';
     15     fetch my_cur into my_rec;
     16     while my_cur%found loop
     17           dbms_output.put_line(my_rec.gsmno||'#'||my_rec.status||'#'||my_rec.price);
     18           fetch my_cur into my_rec;
     19     end loop;
     20     close my_cur;
     21 end;
     22 /

    13905310001#0#200
    13905315005#1#500

    PL/SQL procedure successfully completed

    SQL>

普通cursor與REF cursor還有一些大家應(yīng)該都熟悉的區(qū)別，我再浪費點唾沫。

1）PL/SQL靜態(tài)光標(biāo)不能返回到客戶端，只有PL/SQL才能利用它。ref光標(biāo)能夠被返回到客戶端，這就是從Oracle的存儲過程返回結(jié)果集的方式。

2）PL/SQL靜態(tài)光標(biāo)可以是全局的，而ref光標(biāo)則不是。也就是說，不能在包說明或包體中的過程或函數(shù)之外定義ref光標(biāo)。只能在定義ref光標(biāo)的過程中處理它，或返回到客戶端應(yīng)用程序。

3）ref光標(biāo)可以從子例程傳遞到子例程，而光標(biāo)則不能。為了共享靜態(tài)光標(biāo)，必須在包說明或包體中把它定義為全局光標(biāo)。因為使用全局變量通常不是一種很好的編碼習(xí)慣，因此可以用ref光標(biāo)來共享PL/SQL中的光標(biāo)，無需混合使用全局變量。

最后，使用靜態(tài)光標(biāo)--通過靜態(tài)SQL（但不用ref光標(biāo)）--比使用ref光標(biāo)效率高，而ref光標(biāo)的使用僅限于以下幾種情況：

把結(jié)果集返回給客戶端；
在多個子例程之間共享光標(biāo)（實際上與上面提到的一點非常類似）；
沒有其他有效的方法來達(dá)到你的目標(biāo)時，則使用ref光標(biāo)，正如必須用動態(tài)SQL時那樣；

簡言之，首先考慮使用靜態(tài)SQL，只有絕對必須使用ref光標(biāo)時才使用ref光標(biāo)，也有人建議盡量使用隱式游標(biāo)，避免編寫附加的游標(biāo)控制代碼（聲明，打開，獲取，關(guān)閉），也不需要聲明變量來保存從游標(biāo)中獲取的數(shù)據(jù)。這個就因人因具體的case大家去酌定吧。

四、游標(biāo)屬性

%FOUND： bool - TRUE if >1 row returned
%NOTFOUND：bool - TRUE if 0 rows returned
%ISOPEN： bool - TRUE if cursor still open
%ROWCOUNT：int - number of rows affected by last SQL statement

注：NO_DATA_FOUND和%NOTFOUND的用法是有區(qū)別的，小結(jié)如下：
1）SELECT . . . INTO 語句觸發(fā) NO_DATA_FOUND；
2）當(dāng)一個顯式光標(biāo)的 where 子句未找到時觸發(fā) %NOTFOUND；
3）當(dāng)UPDATE或DELETE 語句的where 子句未找到時觸發(fā) SQL%NOTFOUND；
4）在光標(biāo)的提取(Fetch)循環(huán)中要用 %NOTFOUND 或%FOUND 來確定循環(huán)的退出條件，不要用NO_DATA_FOUND。

五、sys_refcursor

sys_refcursor是oracle9i以后系統(tǒng)定義的一個refcursor,主要用在過程中返回結(jié)果集。

例：

SQL> conn scott/tiger@vongates
connected.
SQL> create or replace procedure getEmpByDept(in_deptNo in emp.deptno%type,
2 out_curEmp out SYS_REFCURSOR) as
3
4 begin
5 open out_curEmp for
6 SELECT * FROM emp WHERE deptno = in_deptNo ;
7 EXCEPTION
8 WHEN OTHERS THEN
9 RAISE_APPLICATION_ERROR(-20101,
10 'Error in getEmpByDept' || SQLCODE );
12 end getEmpByDept;
13 /

已建立程序.

SQL> var rset refcursor;
SQL> exec getEmpByDept(10,:rset);

PL/SQL 程序順利完成.

SQL> print rset;

本文來自CSDN博客，轉(zhuǎn)載請標(biāo)明出處：http://blog.csdn.net/Phoenix_99/archive/2010/08/20/5825926.aspx