Oracle 游标

游标逐行处理表中数据。
分类: 显式、隐式
属性: %FOUND,%ISOPEN,%NOTFOUND,%ROWCOUNT

使用游标：
--loop
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;
/

--while
declare
cursor c is
select * from dept;
v_emp c%rowtype;
begin
open c;
fetch c into v_emp;
while(c%found) loop
dbms_output.put_line(v_emp.deptno||','||v_emp.dname||','||v_emp.loc);
fetch c into v_emp;
end loop;
close c;
end;
/

--for：不需要打开、关闭游标
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 cur_para(no varchar2) is --定义的参数不需要宽度
select dname from dept where deptno >= no; --使用参数
t_name dept.dname%TYPE; --指定一列类型
begin
open cur_para('10'); --指定参数
loop
fetch cur_para into t_name;
exit when cur_para%NOTFOUND;
dbms_output.put_line(t_name);
end loop;
close cur_para;
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_temp in c(30, 'CLERK') loop
dbms_output.put_line(v_temp.ename);
end loop;
end;
/

游标的%ISOPEN属性：
declare
cursor t_cur(no number) is --参数不给精度，长度
select dname from dept where deptno=no;
t_name dept.dname%TYPE;
begin
if t_cur%ISOPEN then
dbms_output.put_line('游标已被打开');
else
open t_cur(20);
end if;
fetch t_cur into t_name;
close t_cur;
dbms_output.put_line(t_name);
end;
/

游标的%ROWCOUNT属性：
declare
cursor mycur is
select dname from dept;
t_name varchar2(10);
begin
open mycur;
loop
fetch mycur into t_name;
--未做fetch时%NOTFOUND为空。
exit when mycur%NOTFOUND or mycur%NOTFOUND is null;
dbms_output.put_line('游标mycur的ROWCOUNT是：'||mycur%ROWCOUNT);
end loop;
close mycur;
end;
/

使用游标修改数据：
--创建emp2表，然后使用游标。
create table emp2 as select * from emp;
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 from emp2 where current of c;
end if;
end loop;
commit;
end;
/
--比较：
select emp.sal, emp2.sal as sal2 from emp left join emp2 on emp.empno=emp2.empno;


隐式游标：
begin
for cur in (select dname from dept) loop
dbms_output.put_line(cur.dname);
end loop;
end;
/