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

cursor分類：

                                     --顯式cursor
             --靜態(tài)cursor |

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

一、顯式cursor

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

cursor cursor_name (parameter list) is select ...

游標(biāo)從declare、open、fetch、close是一個完整的生命旅程。當(dāng)然了一個這樣的游標(biāo)是可以被多次open進行使用的，顯式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 number;
        begin
           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;
          
           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 get_gsmno_cur;
        end;
        /

上面這段匿名塊用來實現(xiàn)選號的功能，我們顯式的定義了一個get_gsmno_cur，然后根據(jù)不同的號段輸出當(dāng)前系統(tǒng)中該號短對應(yīng)的可用手機號碼。當(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;
       
        begin
             update zrp SET str = 'updateD' where str like '%D%';
             ifSQL%ROWCOUNT= 0 then
               insert into zrp values ('1111111');
             end if;
        end;
        /
       
        PL/SQL procedure successfully completed
       
        SQL> select * from zrp;
       
        STR
        ----------
        updateD
        ABCXEFG
        ABCYEFG
        updateD
        ABCZEFG
       
        
         begin
             update zrp SET str = 'updateD' where str like '%S%';
             ifSQL%ROWCOUNT= 0 THEN
               insert into zrp values ('0000000');
             end if;
         end;
         /
       
        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，下面再看一個強類型（受限）的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;
    
    set serveroutput on 
    declare
           type gsm_rec is record(
                gsmno varchar2(11),
                status varchar2(1),
                price number(8,2));
           my_rec gsm_rec; 
          
    type app_ref_cur_type is ref cursor /*return gsm_rec可加可不加，不影響執(zhí)行結(jié)果*/;
           my_cur app_ref_cur_type;
     
      begin
          open my_cur for select gsmno,status,price from gsm_resource where store_id='SD.JN.01';
          fetch my_cur into my_rec;
          while my_cur%found loop
                dbms_output.put_line(my_rec.gsmno||'#'||my_rec.status||'#'||my_rec.price);
          fetch my_cur into my_rec;
          end loop;
          close my_cur;
      end;
      /
    
     13905310001#0#200
     13905315005#1#500

    PL/SQL procedure successfully completed
   
static cursor與ref cursor還存在下面一些區(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)，無需混合使用全局變量。

4）使用靜態(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ù)。

四、游標(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)的退出條件。

********************************轉(zhuǎn)自：http://hi.baidu.com/edeed **********************************

Oracle動態(tài)游標(biāo)中,游標(biāo)變量在定義時不指定固定的SQL語句,在Open時才指定SQL語句。下面是自己的一些實踐筆記：
【1】動態(tài)游標(biāo)的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_cname using i_id; --2
        loop
         Fetch cursor_select into v_bookname;
         exit when cursor_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】動態(tài)游標(biāo)返回結(jié)果集給客戶端

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

create or replace package types
as
    type ref_cursor is ref cursor;
end;

oracle存儲過程：

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

oracle 函數(shù):

create or replace function get_book_func
(
 i_id in number
) 
return types.ref_cursor
as
    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 completed

SQL>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 

下面代碼就是調(diào)用oracle存儲過程或函數(shù)并返回結(jié)果集：

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函數(shù)聲明，一個入?yún)⒁粋€出參。
  
 private String oracleQuery_pro = "{call get_book_pro(?,?) }";--oracle存儲過程聲明，一個入?yún)⒁粋€出參。

 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動態(tài)游標(biāo)在存儲過程中的運用
    System.out.println("--------ref cursor in proc--------");
    CallableStatement stmt = conn.prepareCall(query);
    stmt.setInt(1,id);--存儲過程入?yún)?br />     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動態(tài)游標(biāo)在函數(shù)中的運用
    System.out.println("--------ref cursor in func--------");
    CallableStatement stmt = conn.prepareCall(query);
    stmt.registerOutParameter(1,oracle.jdbc.OracleTypes.CURSOR);--函數(shù)出參
    stmt.setInt(2,id);--函數(shù)入?yún)?br />     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
 }

}

結(jié)果：
當(dāng)private boolean useOracleQuery = true;時，程序打印：

--------ref cursor in proc--------
1 dephi 100 

當(dāng)private boolean useOracleQuery = false;時，程序打印：
------ref cursor in func--------
1 dephi 100