使用游标将emp表中的ename全取出来:
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
loop --由于是一条一条fetch,所以使用do-while循环
fetch c into v_emp;
exit when(c%notfound);--这里是结束循环的条件
dbms_output.put_line(v_emp.ename);
end loop;
close c;
end;
/
declare
cursor c is
select * from emp;
v_emp c%rowtype;
begin
open c;
fetch c into v_emp;
while(c%found) loop--使用while-do循环
dbms_output.put_line(v_emp.ename);
fetch c into v_emp;
end loop;
end;
for循环的特点:(最不容易出错的循环,使用最多)
1. for里面变量不用声明
2. for里面游标不需open,不用fetch,不用close。它会帮你打开游标,从开头提取游标,关闭游标。
declare
cursor c is
select * from emp;
begin
for v_emp in c loop
dbms_output.put_line(v_emp.ename);
end loop;
end;
给游标传递形参:
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_emp in c(30,’clerk’) loop --传递给形参两个参数值
dbms_output.put_line(v_emp.ename);
end loop;
end;
定义用于更新的游标:
declare
cursor c is
select * from emp2 for update;
begin
for v_temp in c loop
if(v_temp.sal <2000) then
update emp2 set sal = sal*2 where current of c;--游标走哪更新到哪
elsif (v_temp.sal = 5000) then
delete fom emp2 where current of c;
end if;
end loop;
commit;
end;
[@more@]来自 “ ITPUB博客 ” ,链接:http://blog.itpub.net/21548840/viewspace-1035687/,如需转载,请注明出处,否则将追究法律责任。
转载于:http://blog.itpub.net/21548840/viewspace-1035687/