游标的基本操作

有时候希望能够对瞒住特定条件的记录逐一进行更新处理，可以使用 for update字句的游标
declare
cursor c_cur is
select usernaem,jop from aspnet_user where sal<3000 for update of sal;
begin
open c_cur
wherne c_cur%found loop
update asp_users set sal=3000 where current or c_cur;
end loop;
close c_cur;
commit;


循环


loop
fetch c1 into my_record
exit when c1%notfound
end loop;


批量取得游标中的数据(可以用bulk collect字句批量绑定数据)
这样就能一次性从结果中取得所有的记录
declare type numtab is table of aspnet_user.userNo%type;
type nametabb is table of aspnet_user.user%type;
nums numtab;
names  nametab;
cusor is select userno,usernames from aspnet_user where jop='仓库员';
begin
open c1;
fetch c1;
bulk collect into nums,names;
...
close c1;
end;




例子


declaae v_userno varchar2(20);
v_usernmae nvarchar2(50);
cursor c_user is
select userno,username from apsnet_users;
begin
open c_users;
loop
fet c_users into v_userno，v_username
wxit thwn c_uses%notfound;
dbms_output.put_line(v_userno||'对应'||v_username);
end loop;
close c_users;
end;


游标属性if c1%isopen then
..
else ;
open c1;
end if;
%not found 属性


fetcth c1 into my_username,my_salary;
exit when c1%notfound;
..
end loop;


%rowcount 属性
loop
fetch c1
into my_username ,my_salary
if c1%rowcount>10 then
..
end if;
..
end loop;


每一个显示的游标和游标变量都有4个属性  %found ，%found,%isopen ,%notfound 和rowcount;


%found 属性
loop
fetch  c1
into my_username ,my_salary;
if c1%found then
..
else
exit;
end ifl
end loop;
%isopen属性




为游标FOR循环传递参数


REF cursor 类型可以是强类型也可以是若类型
delcare type empcurtype is ref cursor return employees%rowtype;
type genericcurtyp is ref cursor;