《精通Oracle10gPLSQL编程》9、使用游标

1.显示游标

cursor cursor_name is select_statement;
open cursor_name;
fetch cursor_name into variable1,variable2,...;
--fetch cursor_name into collect1,collect2,...;
close cursor_name;

显示游标属性：%ISOPEN,%FOUND,%NOTFOUNT,%ROWCOUNT

2.参数游标：

declare
cursor emp_cursor(no number) is
select ename from emp where deptno=no;
v_ename emp.ename%type;
begin
open emp_cursor(10);
loop
fetch emp_cursor into v_ename;
exit when emp_cursor%notfound;
dbms_output.put_line(v_name);
end loop;
close emp_cursor;
end;

3.使用游标更新或删除数据

declare
cursor emp_cursor is 
select ename,sal from emp for update;
v_ename emp.ename%type;
v_sal emp.sal%type;
begin
open emp_cursor;
loop
fetch emp_cursor into v_ename,v_oldsal;
exit when emp_cursor%notfound;
if v_oldsal < 2000 then
--update
 update emp set sal=sal+100 where current of emp_cursor;
--delete
delete from emp where current of emp_cursor;
 end if;
end loop;
close emp_cursor;
end;

4.游标for循环（简化了对游标的处理，oracle隐含的打开游标、提取数据、关闭游标）

declare
cursor emp_cursor is select ename,sal from emp;
begin
for emp_cursor in emp_cursor loop
... ...
end loop;
end;

5、使用游标变量

declare
type emp_cursor_type is ref cursor;
emp_cursor emp_cursor_type;
emp_record emp%rowtype;
begin
open emp_cursor for select * from emp where deptno = 10;
loop
fetch emp_cursor into emp_record;
exit when emp_cursor%notfound;
... ...
end loop;
close emp_cursor;
end;

6、使用CURSOR表达式

语法：CURSOR(subquery)