oracle 游标

[size=medium][b]游标类型: [color=blue]隐式游标 显示游标 REF游标[/color][/b][b][/size]
1.隐式游标[/b]（用于处理 insert update delete select into语句）

sql%rowcount  返回多少行被影响了
sql%found
sql%notfound
sql%isopen 游标是否打开 始终为false

[b]2.显示游标[/b]

declare
  cursor c_ename is select ename from emp where rownum <= 5;
  vr_ename c_ename%rowtype;
begin
  open c_ename;
  loop
    fetch c_ename into vr_ename;
    exit when c_ename%notfound;
    dbms_output.put_line('ename: ' || vr_ename.ename);
  end loop;
  close c_ename;
exception
  when others then
    if c_ename%isopen then
      close c_ename;
    end if;
end;

[b]使用游标for循环(不需要打开和关闭游标)[/b]

declare
  cursor c_ename is select ename from emp where rownum <= 5;
  begin
  for vr_ename in c_ename
  loop
    dbms_output.put_line('ename:' || vr_ename.ename);
  end loop;
end;

带参数的显示游标

declare
  cursor c_ename(n number) is select ename from emp where rownum<=n;
begin
 for vr_ename in c_ename(5)   
  loop   
    dbms_output.put_line('ename:' || vr_ename.ename);   
  end loop;  
end;

使用显示游标更新行

declare
  cursor c_sal is select sal from emp for update of sal;
begin
  for r in c_sal loop
    update emp set sal = r.sal * 1.5 where current of c_sal;
  end loop;
end;

declare
  cursor c_dept(n integer) is select * from dept
  where deptno > n for update;
begin
  for r in c_dept(40) loop
    delete from dept where current of c_dept;
  end loop;
end;

[b]3.ref 游标[/b]

declare
  type cursor_type is ref cursor;
  c_emp cursor_type;
  vr_emp emp%rowtype;
begin
  open c_emp for select * from emp where rownum <= 5;
  loop
    fetch c_emp into vr_emp;
    exit when c_emp%notfound;
    dbms_output.put_line('ename:' || vr_emp.ename);
  end loop;
  close c_emp;
end;

[b]使用游标执行动态SQL[/b]

declare
  type c_type is ref cursor;
  c_emp c_type;
  r_emp emp%rowtype;
  v_sal number;
begin
  v_sal := 2000;
  open c_emp for 'select * from emp where sal > :1' using v_sal;
  loop
    fetch c_emp into r_emp;
    exit when c_emp%notfound;
    dbms_output.put_line('ename: ' || r_emp.ename || ' sal: ' || r_emp.sal);
  end loop;
  close c_emp;
end;

[b]使用bulk collect into[/b]

declare
  --声明ref游标
  type c_type is ref cursor;
  c_emp c_type;
  --声明table记录类型
  type id_list is table of emp.empno%type;
  type name_list is table of emp.ename%type;
  ids id_list;
  names name_list;
begin
  open c_emp for select empno, ename from emp;
  fetch c_emp bulk collect into ids, names;
  close c_emp;
  for i in ids.first.. ids.last loop
    dbms_output.put_line('empno: ' || ids(i) || ' ename: ' || names(i));
  end loop;
end;