oracle游标的替代方法,Oracle使用游标的四种方法

Oracle使用游标的方法共有四种，分别为静态游标、隐式动态游标、显示动态游标及DBMS_SQL包执行动态游标。以下为您展示每一种调用的方法：

1、静态游标，此法适用于某确定的sql语句，使用方法非常简单：

-- Created on 2013/10/28 by M083370

declare

v_UserId        qs_user_list.user_id%type;

v_UserName      qs_user_list.user_name%type;

v_RoleId        qs_user_list.role_id%type;

cursor curUser is select user_id,user_name,role_id from qs_user_list where rownum<=100;

begin

-- Test statements here

open curUser;

loop

fetch curUser into v_UserId,v_UserName,v_RoleId;

exit when curUser%notfound;

dbms_output.put_line(v_UserId||'-'||v_UserName||'-'||v_RoleId);

end loop;

close curUser;

end;

2、隐式动态游标，此法对于确定或不确的sql语句都适用，用法也很简单：

-- Created on 2013/10/28 by M083370

declare

-- Local variables here

v_Flag          integer:=0;

v_UserId        qs_user_list.user_id%type;

v_UserName      qs_user_list.user_name%type;

v_RoleId        qs_user_list.role_id%type;

type ref_cur_type is ref cursor;

curUser         ref_cur_type;

begin

-- Test statements here

if v_Flag =0 then

open curUser for select user_id,user_name,role_id from qs_user_list where rownum<=100 and role_id='R004';

else

open curUser for select user_id,user_name,role_id from qs_user_list where rownum<=100;

end if;

loop

fetch curUser into v_UserId,v_UserName,v_RoleId;

exit when curUser%notfound;

dbms_output.put_line(v_UserId||'-'||v_UserName||'-'||v_RoleId);

end loop;

close curUser;

end;

3、显示动态游标，这种方法与第2种的区别在于它需要先定义游标的结构，稍微麻烦一些，不过写法也更严紧一些：

-- Created on 2013/10/28 by M083370

declare

-- Local variables here

v_Flag          integer:=0;

type userinfo is record(

userid        qs_user_list.user_id%type,

username      qs_user_list.user_name%type,

roleid        qs_user_list.role_id%type

);

type ref_cur_type is ref cursor return userinfo;

curUser     ref_cur_type;

userRec     userinfo;

begin

-- Test statements here

if v_Flag =0 then

open curUser for select user_id,user_name,role_id from qs_user_list where rownum<=100 and role_id='R004';

else

open curUser for select user_id,user_name,role_id from qs_user_list where rownum<=100;

end if;

loop

fetch curUser into userRec;

exit when curUser%notfound;

dbms_output.put_line(userRec.userid||'-'||userRec.username||'-'||userRec.roleid);

end loop;

close curUser;

end;

4、DBMS_SQL包执行动态游标，这种方法过于烦琐，性能上讲也没发现有什么优势，估计很少有人会选择这种方式：