declare cursor my_cur is select empno,deptno from emp; employee_id emp.empno%type; dept_id emp.deptno%type; begin open my_cur; fetch my_cur into employee_id,dept_id; while my_cur%found loop dbms_output.put_line(employee_id||' shit '||dept_id); fetch my_cur into employee_id,dept_id; end loop; end;
使用行变量来获取游标信息
declare cursor mycur is select * from emp; var_emp emp%rowtype; begin open mycur; fetch mycur into var_emp; while mycur%found loop dbms_output.put_line(var_emp.ename||':'||+to_char(var_emp.hiredate,'yyyy-MM-dd')); fetch mycur into var_emp; end loop; end;
隐式游标
declare var_count integer; begin update A set ename='B'; var_count:=sql%rowcount; dbms_output.put_line(to_char(var_count)); end;
动态游标
定义
declare type emp_cur_type is ref cursor return emp%rowtype;
new_emp_cur_type emp_cur_type;
open new_emp_cur_type for select * from emp;