--1 显示游标
declare cursor v_sc1 is
select sendnum,registedate from user_info t for update of t.userlevel,nicheng nowait; --of 后面可以多个变量
n_level number;
n_sendnum number;
d_registedate date;
begin
n_level:=0;
open v_sc1;
loop
fetch v_sc1 into n_sendnum, d_registedate,n_id;
exit when v_sc1%notfound; --一定要有
if (n_sendnum>0) and (n_sendnum<=100) and (d_registedate
n_level :=1;
else
n_level :=3;
end if;
update user_info set userlevel = n_level where current of v_sc1; --更新数据 只更新当前数据;不用再次执行全表扫描
end loop;
end;
/
--2 带参数的游标
declare
cursor c_test(pid_1 number,pid_2 number) is select * from t1 where t1.emp_no> pid_1 and t1.emp_no
i number:=0;
begin
for i in 100..102 loop --外循环 :在查询数据量大的表时一次性查出风险太大。可以分批执行
for v_test in c_test(i,i+100) loop
update t1 set t1.tel='13838100400' where current of c_test;
end loop;
commit;
end loop;
end;
--3 另一种打开方式
declare
type refcursor is ref cursor;
v_cursor refcursor;
begin
open cur for select t1.tel from t1;
loop
exit when cur%notfound;
fetch cur into v_tel ;
end loop;
end;
4- 判断游标指向最后一条记录
loop
i:=i+1;
if i=t_cursor%rowcount-1 then
最后一条记录
end if;