PLSQL中顯示Cursor、隱示Cursor、動態Ref Cursor區別，plsqlref

PLSQL中顯示Cursor、隱示Cursor、動態Ref Cursor區別，plsqlref
一、顯式cursor

顯式是相對與隱式cursor而言的，就是有一個明確的聲明的cursor。顯式遊標的聲明類似如下（詳細的文法參加plsql ref doc ）：

cursor cursor_name (parameter list) is select ...

遊標從declare、open、fetch、close是一個完整的生命旅程。當然了一個這樣的遊標是可以被多次open進行使用的，顯式cursor是靜態cursor，她的範圍是全域的，但也必須明白，靜態cursor也只有pl/sql代碼才可以使用她。下面看一個簡單的靜態顯式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;
        /

上面這段匿名塊用來實現選號的功能，我們顯式的定義了一個get_gsmno_cur，然後根據不同的號段輸出當前系統中該號短對應的可用手機號碼。當然了，實際應用中沒人這麼用的，我只是用來說應一個顯式cursor的用法。

二、隱式cursor

隱式cursor當然是相對於顯式而言的，就是沒有明確的cursor的declare。在Oracle的PL/SQL中，所有的DML操作都被Oracle內部解析為一個cursor名為SQL的隱式遊標，只是對我們透明罷了。

另外，我們前面提到的一些迴圈操作中的指標for 迴圈，都是隱式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屬於動態cursor(直到運行時才知道這條查詢)。

從技術上講，在最基本的層次靜態cursor和ref cursor是相同的。一個典型的PL/SQL游標按定義是靜態。Ref游標正好相反，可以動態地開啟，或者利用一組SQL靜態語句來開啟，選擇哪種方法由邏輯確定（一個IF/THEN/ELSE代碼塊將開啟一個或其它的查詢）。例如，下面的代碼塊顯示一個典型的靜態SQL游標，游標C。此外，還顯示了如何通過使用動態SQL或靜態SQL來用ref游標（在本例中為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;
    /

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

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

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還有一些大家應該都熟悉的區別，我再浪費點唾沫。

1）PL/SQL靜態游標不能返回到用戶端，只有PL/SQL才能利用它。ref游標能夠被返回到用戶端，這就是從Oracle的預存程序返回結果集的方式。

2）PL/SQL靜態游標可以是全域的，而ref游標則不是。 也就是說，不能在包說明或包體中的過程或函數之外定義ref游標。 只能在定義ref游標的過程中處理它，或返回到用戶端應用程式。

3）ref游標可以從子常式傳遞到子常式，而游標則不能。 為了共用靜態游標，必須在包說明或包體中把它定義為全域游標。 因為使用全域變數通常不是一種很好的編碼習慣，因此可以用ref游標來共用PL/SQL中的游標，無需混合使用全域變數。

最後，使用靜態游標--通過靜態SQL（但不用ref游標）--比使用ref游標效率高，而ref游標的使用僅限於以下幾種情況：

把結果集返回給用戶端；
在多個子常式之間共用游標（實際上與上面提到的一點非常類似）；
沒有其他有效方法來達到你的目標時，則使用ref游標，正如必須用動態SQL時那樣；

簡言之，首先考慮使用靜態SQL，只有絕對必須使用ref游標時才使用ref游標，也有人建議盡量使用隱式遊標，避免編寫附加的遊標控制碼（聲明，開啟，擷取，關閉），也不需要聲明變數來儲存從遊標中擷取的資料。這個就因人因具體的case大家去酌定吧。

四、遊標屬性

%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的用法是有區別的，小結如下：
1）SELECT . . . INTO 語句觸發 NO_DATA_FOUND；
2）當一個顯式游標的 where 子句未找到時觸發 %NOTFOUND；
3）當UPDATE或DELETE 語句的where 子句未找到時觸發 SQL%NOTFOUND；
4）在游標的提取(Fetch)迴圈中要用 %NOTFOUND 或%FOUND 來確定迴圈的允出準則，不要用NO_DATA_FOUND。


oracle中的ref cursor與record的不同

cursor是一個遊標（有些地方叫指標吧），它裡面應該放的是指向記憶體中存放資料的首地址吧
record是把資料直接放進去的吧
 
怎使用REF CURSOR處理Oracle的結果集

Type -------------------- -------- ------------ EMPNO NOT NULL NUMBER(4) ENAME VARCHAR2(10) JOB VARCHAR2(9) MGR NUMBER(4) HIREDATE DATE SAL NUMBER(7,2) COMM NUMBER(7,2) DEPTNO NUMBER(2)最後使用ref cursor獲得結果集輸出：SQL> set serveroutput on SQL> DECLARE 2 TYPE mytable IS TABLE OF emp%ROWTYPE; 3 l_data mytable; 4 l_refc sys_refcursor; 5 BEGIN 6 OPEN l_refc FOR 7 SELECT empno, ename, job, mgr, hiredate, sal, comm, deptno FROM emp; 8 9 FETCH l_refc BULK COLLECT INTO l_data; 10 11 CLOSE l_refc; 12 13 FOR i IN 1 .. l_data.COUNT 14 LOOP 15 DBMS_OUTPUT.put_line ( l_data (i).ename 16 || ' was hired since ' 17 || l_data (i).hiredate 18 ); 19 END LOOP; 20 END; 21 / SMITH was hired since 17-DEC-80 ALLEN was hired since 20-FEB-81 WARD was hired since 22-FEB-81 JONES was hired since 02-APR-81 MARTIN was hired since 28-SEP-81 BLAKE was hired since 01-MAY-81 CLARK was hired since 09-JUN-81 SCOTT was hired since 19-APR-87 KING was hired since 17-NOV-81 TURNER was hired since 08-SEP-81 ADAMS was hired since 23-MAY-87 JAMES was hired since 03-DEC-81 FORD was hired since 03-DEC-81 MILLER was hired since 23-JAN-82 PL/SQL procedure successfully completed.（責任編輯：盧兆林）