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包执行动态游标,这种方法过于烦琐,性能上讲也没发现有什么优势,估计很少有人会选择这种方式: