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包执行动态游标,这种方法过于烦琐,性能上讲也没发现有什么优势,估计很少有人会选择这种方式:
-- Created on 2013/10/28 by M083370
declare
-- Local variables here
v_UserId qs_user_list.user_id%type;
v_UserName qs_user_list.user_name%type;
v_RoleId qs_user_list.role_id%type;
rs Integer;
v_tmp Integer;
strSQL Varchar2(1000):='select user_id,user_name,role_id from qs_user_list where rownum<=100';
begin
-- Test statements here
rs :=DBMS_SQL.open_cursor;
Dbms_SQL.Parse(rs,strSQL,Dbms_SQL.V7);
Dbms_SQL.Define_Column(rs,1,v_UserId,50);
Dbms_SQL.Define_Column(rs,2,v_UserName,50);
Dbms_SQL.Define_Column(rs,3,v_RoleId,50);
v_tmp:=Dbms_SQL.Execute(rs);
Loop
If DBMS_SQL.Fetch_Rows(rs)=0 Then
exit;
End If;
Dbms_SQL.Column_Value(rs,1,v_UserId);
Dbms_SQL.Column_Value(rs,2,v_UserName);
Dbms_SQL.Column_Value(rs,3,v_RoleId);
dbms_output.put_line(v_UserId||'-'||v_UserName||'-'||v_RoleId);
End loop;
DBMS_SQL.close_cursor(rs);
end;