Posted on 2010-07-24 16:14
斷點 閱讀(246)
評論(0) 編輯 收藏 所屬分類:
Oracle DBA
--游標
declare
cursor c is
select * from emp;
v_emp c%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;
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;
--fetch c into v_emp; 導致第一條沒有打印，最后一條打印2遍。
--dbms_output.put_line(v_emp.ename);
end loop;
close c;
end;
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;
--v_temp c%rowtype;
begin
for v_temp in c(30,'CLERK') loop --for自動打開游標。
dbms_output.put_line(v_temp.ename);
end loop;
end;
--可更新的游標
declare
cursor c
is
select * from emp2 for update;
--v_temp c%rowtype;
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 from emp2 where current of c;
end if;
end loop;
commit;
end;