PL/SQL - 커서

- 암시적 커서

- 명시적 커서

create or replace procedure emp_process

is

v_empno emp.empno%TYPE;

v_ename emp.ename%TYPE;

v_sal NUMBER(7,2);

cursor emp_cursor(v_deptno number) is

select empno, ename, sal from emp where deptno = v_deptno;

begin

open emp_cursor(10); 

loop

fetch emp_cursor into v_empno, v_ename, v_sal;

exit when emp_cursor%ROWCOUNT > 5 or emp_cursor%NOTFOUND;

...

end loop;

close emp_cursor;

end emp_process;

/

or

create or replace procedure emp_process

is

cursor emp_cursor(v_deptno number) is

select empno, ename, sal from emp where deptno = v_deptno;

begin

for v_emp in emp_cursor(10) loop

... v_emp.empno ... v_emp.ename ... v_emp.sal ...

end loop;

end emp_process;

/

-

decalre

cursor emp_cursor is

select empno, ename, sal from emp where deptno = 30 for update;

begin

for v_emp in emp_cursor loop

update emp set sal = sal * 1.1 where current of emp_cursor;

...

end loop;

commit;

end;

/