pl/sql游标操作的步骤:
1:声明游标
cursor c is
select * from emp;
2:打开游标
open c;
3:迭代抓取游标
fetch c into 变量名
4:关闭游标
close c;
利用for循环遍历游标,会自动打开和关闭游标
declare
cursor c is
select * from emp;
v_emp emp%rowtype;
begin
open c;
loop
fetch c into v_emp;
exit when(c%notfound);
dbms_output.put_line(v_emp.ename);
end loop;
close c;
end;
利用while遍历游标
declare
cursor c is
select * from emp;
v_emp emp%rowtype;
begin
open c;
fetch c into v_emp;
while(c%found) loop
dbms_output.put_line(v_emp.ename);
fetch c into v_emp;
end loop;
close c;
end;
下面我们利用游标实现: 更新emp表,工资小于1200的,加5快,工资小于1800的,加3快,其他工资的加1快
declare
cursor c
is select * from emp for update;
v_emp emp%rowtype;
begin
for v_emp in c loop
if(v_emp.sal < 1200) then
update emp set sal = sal +5 where current of c;
elsif(v_emp.sal <1800) then
update emp set sal = sal + 3 where current of c ;
else
update emp set sal = sal + 1 where current of c ;
end if;
end loop;
end;
可更新的游标
declare cursor c is select * from emp2 for update;当后面加上for update时,我们就可以利用current of c 来获取当前的游标。
游标有带参数的游标
declare
cursor c(v_deptno emp.deptno%type,v_job emp.job%type) is
select ename,sal from emp where deptno=v_deptno and job=v_job;
begin
for v_temp in c(30,'CLERK') loop
dbms_output.put_line(v_temp.ename);
end loop;
end;