PLSQL中显式Cursor、隐式Cursor、动态Ref Cursor

今天简单的总结一下PL/SQL中cursor(光标/游标)的用法。

cursor分类：

--显式cursor
--静态cursor |

| --隐式cursor
cursor | |
--强类型(限制)，规定返回类型
--动态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 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;

上面这段匿名块用来实现选号的功能，我们显式的定义了一个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             update zrp SET str = 'updateD' where str like '%D%';             ifSQL%ROWCOUNT= 0 then               insert into zrp values ('1111111');             end if;        end;

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;

SQL> select * from zrp;

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

隐式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;        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可加可不加，不影响执行结果*/;           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;      /

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 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 来确定循环的退出条件。

注: 此文仅作为我的参考

********************************转自：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_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】动态游标返回结果集给客户端

返回结果集给客户端，可以通过2中方式来实现，一是oracle存储过程，另外一个是oracle函数。由于oracle存储过程没有返回值，它的所有返回值都是通过out参数来替代的，列表同样也不例外，对于集合的返回，能用一般的参数，必须要用pagkage来实现，oracle函数也是这样。

建包：

createor replace package types
as
type ref_cursoris ref cursor;
end;

oracle存储过程：

createor replaceprocedure get_book_pro
(
i_idnumber,
o_bookname out types.ref_cursor
)
as
begin
open o_booknamefor select* from bookwhere id = i_id;
end get_book_pro;

oracle 函数:

createor replacefunction get_book_func
(
i_idin number
)
return types.ref_cursor
as
o_bookname types.ref_cursor;
begin
open o_booknamefor select* from bookwhere id = i_id;
return o_bookname;
end get_book_func;

测试SQL：

createtable book
(
idnumber,
booknamevarchar2(100),
pricevarchar2(100)
);
insertinto book(1,'dephi','100');
insertinto book(2,'c','200');
insertinto book(3,'c++','300');
insertinto book(4,'java','400');
insertinto book(5,'c#','500');
insertinto book(6,'shell','600');
insertinto book(7,'vb','700');
insertinto book(8,'plsql','800');

PL/SQLprocedure successfully completed

SQL>select* from book;
11 dephi100
22 c200
33 c++300
44 java400
55 c#500
66 shell600
77 vb700
88 vj800
99 plsql900

下面代码就是调用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;

publicclass 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
}

}

结果：
当privateboolean useOracleQuery= true;时，程序打印：

--------ref cursor in proc--------
1 dephi100

当privateboolean useOracleQuery= false;时，程序打印：
------ref cursor in func--------
1 dephi100

注:

以上原文转自 http://www.blogjava.net/cheneyfree/archive/2008/07/31/219138.html