http://www.blogjava.net/cheneyfree/archive/2008/07/31/219138.html

今天簡單的總結一下PL/SQL中cursor(光標/游標)的用法。

cursor分類：

                                    --顯式cursor             --靜態cursor |

            |                      --隱式cursorcursor |                      |                                                               --強類型(限制)，規定返回類型              --動態cursor   --ref cursor  |                                                               --弱類型(非限制)，不規定返回類型，可以獲取任何結果集

一、顯式cursor

顯式是相對與隱式cursor而言的，就是有一個明確的聲明的cursor。顯式游標的聲明類似如下：

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 invarchar2) is              selectgsmno from gsm_resource where nettype=p_nettype and status='0';            v_gsmno number;        begin           openget_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;                      openget_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;           closeget_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;                begin             updatezrp SET str = 'updateD' where str like '%D%';            ifSQL%ROWCOUNT= 0 then              insert into zrp values ('1111111');             endif;        end;        /                PL/SQL PRocedure successfullycompleted                SQL> select * from zrp;                STR        ----------        updateD        ABCXEFG        ABCYEFG        updateD        ABCZEFG                          begin             updatezrp SET str = 'updateD' where str like '%S%';            ifSQL%ROWCOUNT= 0 THEN              insert into zrp values ('0000000');             endif;         end;         /                PL/SQL procedure successfullycompleted                SQL> select * from zrp;                STR        ----------        updateD        ABCXEFG        ABCYEFG        updateD        ABCZEFG        0000000       6 rows selected                SQL>

隱式cursor示例二：

 begin        for rec in (select gsmno,status fromgsm_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 withdynamic sql           open l_cursor for'select * from emp';      elsif (to_char(sysdate,'dd') = 29) then           -- ref cursor withstatic sql           open l_cursor forselect * from dept;      else           -- with ref cursorwith static sql           open l_cursor forselect * from dual;      end if;          -- the "normal"static cursor           open c;    end;    /

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

在上面的代碼中，聲明了一個弱類型的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_resourcevalues('13905310001','0',200.00,'SD.JN.01');     insert into gsm_resource values('13905312002','0',800.00,'SD.JN.02');    insert into gsm_resourcevalues('13905315005','1',500.00,'SD.JN.01');    insert into gsm_resourcevalues('13905316006','0',900.00,'SD.JN.03');    commit;        set serveroutput on     declare           type gsm_rec isrecord(               gsmno varchar2(11),               status varchar2(1),               price number(8,2));           my_recgsm_rec;                type app_ref_cur_type is ref cursor /*return gsm_rec可加可不加，不影響執行結果*/;           my_curapp_ref_cur_type;            begin          open my_cur for selectgsmno,status,price from gsm_resource where store_id='SD.JN.01';          fetch my_cur intomy_rec;          while my_cur%found loop               dbms_output.put_line(my_rec.gsmno||'#'||my_rec.status||'#'||my_rec.price);          fetch my_cur intomy_rec;          end loop;          close my_cur;      end;      /         13905310001#0#200     13905315005#1#500    PL/SQL procedure successfully completed    static cursor與ref cursor還存在下面一些區別：

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

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

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

4）使用靜態光標--通過靜態SQL（但不用ref光標）--比使用ref光標效率高，而ref光標的使用僅限于這幾種情況：把結果集返回給客戶端；在多個子例程之間共享光標；沒有其他有效的方法來達到你的目標時，則使用ref光標，正如必須用動態SQL時那樣；

注：首先考慮使用靜態SQL，只有絕對必須使用ref光標時才使用ref光標，也有人建議盡量使用隱式游標，避免編寫附加的游標控制代碼（聲明，打開，獲取，關閉），也不需要聲明變量來保存從游標中獲取的數據。

四、游標屬性

%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 SQLstatement

注：NO_DATA_FOUND和%NOTFOUND的用法是有區別的，小結如下：1）SELECT . . . INTO 語句觸發NO_DATA_FOUND；2）當一個顯式光標的 where 子句未找到時觸發%NOTFOUND；3）當UPDATE或DELETE語句的where子句未找到時觸發 SQL%NOTFOUND；4）在光標的提取(Fetch)循環中要用 %NOTFOUND 或%FOUND 來確定循環的退出條件。********************************轉自：http://hi.baidu.com/edeed **********************************

Oracle動態游標中,游標變量在定義時不指定固定的SQL語句,在Open時才指定SQL語句。下面是自己的一些實踐筆記：【1】動態游標的2中不同寫法create or replace procedure pro_set_loop(    i_id varchar2,    o_result_code out number,    o_result_msg out varchar2  )as    v_bookname    varchar2(100);   v_id            number;    type ref_cursor_type is REF CURSOR;    cursor_select   ref_cursor_type;    select_cname  varchar2(1000);  begin    select_cname:='select bookname from book where id =:1';--1                Open  cursor_select For select_cnameusing i_id; --2        loop         Fetch cursor_select intov_bookname;         exit whencursor_select%notfound;              update book set price = '25' where bookname = v_bookname;        end loop;       Close cursor_select;end;備注：上面1,2兩句也可以寫成：select_cname:='select bookname from book where id ='||i_id;          Open  cursor_select For select_cname；

 【2】動態游標返回結果集給客戶端

  返回結果集給客戶端，可以通過2中方式來實現，一是oracle存儲過程，另外一個是oracle函數。由于oracle存儲過程沒有返回值，它的所有返回值都是通過out參數來替代的，列表同樣也不例外，對于集合的返回，能用一般的參數，必須要用pagkage來實現，oracle函數也是這樣。  建包：

create or replace package typesas    type ref_cursor is ref cursor;end;

oracle存儲過程：

create or replace procedure get_book_pro(  i_id number,  o_bookname out types.ref_cursor) asbegin    open o_bookname for select * from book where id = i_id;end get_book_pro;

oracle 函數:

create or replace function get_book_func( i_id in number) return types.ref_cursoras    o_bookname types.ref_cursor;begin    open o_bookname for select * from book where id = i_id;    return o_bookname;end get_book_func;

 測試SQL：

create table book(  id       number,  bookname varchar2(100),  price    varchar2(100));insert into book(1,'dephi','100');insert into book(2,'c','200');insert into book(3,'c++','300');insert into book(4,'java','400');insert into book(5,'c#','500');insert into book(6,'shell','600');insert into book(7,'vb','700');insert into book(8,'plsql','800');PL/SQL procedure successfully completedSQL>select * from book;1 1 dephi 100 2 2 c 200 3 3 c++ 300 4 4 java 400 5 5 c# 500 6 6 shell 600 7 7 vb 700 8 8 vj 800 9 9 plsql 900 

下面代碼就是調用oracle存儲過程或函數并返回結果集：

package J4;import java.sql.CallableStatement;import java.sql.Connection;import java.sql.DriverManager;import java.sql.ResultSet;import java.sql.SQLException;import java.sql.Types;public class TestOracle { public static final String URL = "jdbc:oracle:thin:@10.40.152.186:1521:zxin"; public static final String DRIVER = "oracle.jdbc.driver.OracleDriver"; public static final String USER_NAME = "wap"; public static final String PASSWord = "wap"; private boolean uSEOracleQuery = false;  private String oracleQuery_func = "{?= call get_book_func(?)}";--oracle函數聲明，一個入參一個出參。   private String oracleQuery_pro = "{call get_book_pro(?,?) }";--oracle存儲過程聲明，一個入參一個出參。 public void findStored_ref(int id) {  try {   Class.forName(DRIVER);   Connection conn = DriverManager.getConnection(URL, USER_NAME,PASSWORD);   String query = useOracleQuery ? oracleQuery_pro : oracleQuery_func;   if(useOracleQuery){    //oracle動態游標在存儲過程中的運用    System.out.println("--------ref cursor in proc--------");    CallableStatement stmt = conn.prepareCall(query);    stmt.setInt(1,id);--存儲過程入參    stmt.registerOutParameter(2,oracle.jdbc.OracleTypes.CURSOR);--存儲過程出參    stmt.execute();    ResultSet rs = (ResultSet) stmt.getObject(2);--注意是getObject(2)      while (rs.next()) {     System.out.print(rs.getString(1)+" ");     System.out.print(rs.getString(2)+" ");     System.out.println(rs.getString(3)+" ");    }    stmt.close();    conn.close();   }   else{    //oracle動態游標在函數中的運用    System.out.println("--------ref cursor in func--------");    CallableStatement stmt = conn.prepareCall(query);    stmt.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);--函數出參    stmt.setInt(2,id);--函數入參    stmt.execute();    ResultSet rs = (ResultSet) stmt.getObject(1); --注意是getObject(1)      while (rs.next()) {     System.out.print(rs.getString(1)+" ");     System.out.print(rs.getString(2)+" ");     System.out.println(rs.getString(3));    }    stmt.close();    conn.close();   }  } catch (ClassNotFoundException e) {   e.printStackTrace();  } catch (SQLException e) {   e.printStackTrace();  } }  public static void main(String[] args) {  TestOracle test = new TestOracle();  test.findStored_ref(1);--實參為id = 1 }}結果：當private boolean useOracleQuery = true;時，程序打印：--------ref cursor in proc--------1 dephi 100 當private boolean useOracleQuery = false;時，程序打印：------ref cursor in func--------1 dephi 100