oracle 中游标和存储过程的使用

创建一个表

create table test4 (id int,name varchar2(20),cnum number);

在sqlplus下写一个游标的使用

declare

cursor curr is select cnum from test4 where cnum<5;
cur curr%rowtype;
begin
open curr;
LOOP
fetch curr into cur;
exit when curr%NOTFOUND;
dbms_output.put_line(cur.cnum);
end loop;
close curr;
end;

创建一个带游标 的存储过程

create or replace procedure p_test
is 
cursor curr is select cnum from test4 where cnum<5;
cur curr%rowtype;
begin
open curr;
LOOP
fetch curr into cur;
exit when curr%NOTFOUND;
dbms_output.put_line(cur.cnum);
end loop;
close curr;
end;

修改这个存储过程，使其能更新操作

create or replace procedure p_test
is
cursor curr is select cnum from test4 for update of cnum;
cur curr%rowtype;
sal test4.cnum%type;
begin
open curr;
loop
fetch curr into cur;
exit when curr%notfound;
sal:=cur.cnum*2;
update test4 set cnum=sal where current of curr;
dbms_output.put_line(sal);
end loop;

commit;(如果不显示提交，关闭则回滚，还有如果数据量比较大可以将commit放到循环内，也可以在sqlplus下设置set autocommit on,默认是off)
close curr;
end;