7. PL/SQL 명시적 커서 사용
A. Explicit Cursor
B. Cursor 선언
CURSOR cursor_name IS
Select_statement;
DECLARE
CURSOR c_emp_cursor IS
SELECT employee_id, last_name FROM employees
WHERE department_id = 30;
…
DECLARE
v_locid NUMBER := 1700;
CURSOR c_dept_cursor IS
SELECT * FROM departments
WHERE location_id = v_locid;
…
C. Opening the Cursor
DECLARE
CURSOR c_emp_cursor IS
SELECT employee_id, last_name FROM employees
WHERE department_id = 30;
…
BEGIN
OPEN c_emp_cursor;
D. Fetching Data from the Cursor
1)
declare
cursor c_emp_cursor is
select employee_id, last_name from employees
where department_id = 30;
v_empno employees.employee_id%type;
v_lname employees.last_name%type;
begin
open c_emp_cursor;
fetch c_emp_cursor into v_empno, v_lname;
dbms_output.put_line( v_empno || ‘ ‘ || v_lname );
end;
/
2)
declare
cursor c_emp_cursor is
select employee_id, last_name from employees
where department_id = 30;
v_empno employees.employee_id%type;
v_lname employees.last_name%type;
begin
open c_emp_cursor;
loop
fetch c_emp_cursor into v_empno, v_lname;
exit when c_emp_cursor%notfound;
dbms_output.put_line( v_empno || ‘ ‘ || v_lname );
end;
/
E. Closing the Cursor
…
LOOP
FETCH c_emp_cursor INTO empno, lname;
EXIT WHEN c_emp_cursor%NOTFOUND;
DBMS_OUTPUT.PUT_LINE( empno || ‘ ‘ || lname
END LOOP;
CLOSE c_emp_cursor;
END;
/
F. Cursors and Records
declare
cursor c_emp_cursor is
select employee_id, last_name from employees
where department_id = 30;
v_emp_record c_emp_cursor%rowtype;
begin
open c_emp_cursor;
loop
fetch c_emp_cursor into v_emp_record;
exit when c_emp_cursor%notfound;
dbms_output.put_line ( v_emp_record.employee_id || ‘ ‘ || v_emp_record.last_name);
end loop;
close c_emp_cursor;
end;
/
G. Cursor FOR Loops
FOR record_name IN cursor_name LOOP
statement1;
statement2;
…
end loop;
ex)
declare
cursor c_emp_cursor is
select employee_id, last_name from employees
where department_id = 30;
begin
for emp_record in c_emp_cursor loop
dbms_output.put_line( emp_record.employee_id || ‘ ‘|| emp_record.last_name);
end loop;
end;
/
H. Explicit Cursor 속성
[%ISOPEN 속성]
if not c_emp_cursor%isopen then
open c_emp_cursor;
end if;
loop
fetch c_emp_cursor …
[%ROWCOUNT 및 %NOTFOUND 예제]
declare
cursor c_emp_cursor is
select employee_id, last_name from employees;
v_emp_record c_emp_cursor%rowtype;
begin
loop
fetch c_emp_cursor into v_emp_record;
exit when c_emp_cursor%rowcount > 10 or c_emp_cursor%notfound;
dbms_output.put_line( v_emp_record.employee_id || ‘ ‘ || v_emp_record.last_name);
end loop;
close c_emp_cursor;
end;
/
[Subquery 를 사용하는 Cursor FOR Loop]
begin
for emp_record in (select employee_id, last_name
from employees where department_id = 30)
loop
dbms_output.put_line(emp_record.employee_id || ‘ ‘|| emp_record.last_name);
end loop;
end;
/
[Parameter 가 포함된 Cursor]
CURSOR cursor_name
[(parameter_name datatype, …)]
IS
select_statement;
OPEN cursor_name(parameter_value, …);
ex)
declare
cursor c_emp_cursor (deptno number) is
select employee_id, last_name
from employees
where department_id = deptno;
begin
for emp_record in c_emp_cursor(10) loop
dbms_output.put_line( emp_record.employee_id || ‘ ‘|| emp_record.last_name);
end loop;
close c_emp_cursor;
end;
/
[WHERE CURRENT OF 절]
WHERE CURRENT OF cursor ;
update employees
set salary = …
where current of c_emp_cursor;