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

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