oracle游标

--游标的属性：found、not found、rowcount、isopen
--隐式游标：
--1、sql游标
--2、cursor for游标：无需显示声明，打开等操作

begin
  for te in (select * from test1 where tage>20 and tage<30) loop
    dbms_output.put_line('编号：'||te.tid||',名称：'||te.tname||',年龄：'||te.tage ||'备注：'||te.remark);  
    end loop;
end;

 --显示游标

declare cursor cu_test1 is  
select tid,tname,tage,remark from test1;  
tid test1.tid%type;  
tname test1.tname%type;  
tage test1.tage%type;  
remark test1.remark%type;
begin  
      open cu_test1;  
      fetch cu_test1 into tid,tname,tage,remark;  
         while cu_test1%found loop  
                dbms_output.put_line('编号：'||tid||',名称：'||tname||',年龄：'||tage ||'备注：'||remark);  
                fetch cu_test1 into tid,tname,tage,remark;   
          end loop;  
      close cu_test1;  
end;


declare cursor cu_test2 is
select tid,tname,tage from test1;
tid test1.tid%type;  
tname test1.tname%type;  
tage test1.tage%type; 
begin  
  open cu_test2;
  loop 
    fetch cu_test2 into tid,tname,tage;
          exit when cu_test2%notfound;
          dbms_output.put_line('编号：'||tid||',名称：'||tname||',年龄：'||tage);  
  end loop;
  close cu_test2;
end; 

 --使用行变量

declare cursor cu_test3 is
select * from test1;
te test1%rowtype;
begin
   open cu_test3;
        loop
              fetch cu_test3 into te;
              exit when cu_test3%notfound;
              dbms_output.put_line('编号：'||te.tid||',名称：'||te.tname||',年龄：'||te.tage);  
        end loop;
   close cu_test3;
end;

 --为游标传递参数

declare cursor cu_test4(minAge number,maxAge number) is
select * from test1 where tage>minAge and tage<maxAge;
te test1%rowtype;
begin
  open cu_test4(20,30);
  fetch cu_test4  into te;
  while cu_test4%found loop
    dbms_output.put_line('编号：'||te.tid||',名称：'||te.tname||',年龄：'||te.tage);  
  fetch cu_test4 into te;
  end loop;
  close cu_test4;
end;

 --动态游标：强类型动态游标和弱类型动态游标

--强类型动态游标：游标记录数类型固定

begin
  declare
  type test1_type is ref cursor
       return test1%rowtype;
   t_count number;
   te test1%rowtype;
   cu_test1 test1_type;
   begin
     select count(*) into t_count from test1 where tage>30;
     if t_count=0 then
       open cu_test1 for select * from test1;
     else
       open cu_test1 for select * from test1 where tage>30;
     end if;
     fetch cu_test1 into te;
     while cu_test1%found loop
       dbms_output.put_line('编号：'||te.tid||',名称：'||te.tname||',年龄：'||te.tage);
       fetch cu_test1 into te;
       end loop;
    end;
end; 

 --弱类型动态游标：游标的记录类型灵活多变，这使得利用单个游标来获取不同表中的记录数

begin
  declare
  type comm_type is ref cursor;
  c_count number;
  te test1%rowtype;
  ma T_manager%rowtype;
  cu_comm comm_type;
  begin
    select count(*) into c_count from test1 where tage>40;
    if c_count=0 then
      open cu_comm for select * from T_manager;
      fetch cu_comm into ma;
      while cu_comm%found loop
         dbms_output.put_line('编号：'||ma.mid||',职位：'||ma.mname);
         fetch cu_comm into ma;
         end loop;
    else
      open cu_comm for select * from test1;
      fetch cu_comm into te;
      while cu_comm%found loop
        dbms_output.put_line('编号：'||te.tid||',名称：'||te.tname||',年龄：'||te.tage);
        fetch cu_comm into te;
        end loop;
    end if;
   end;
end;

 

 创建见附件