7. PL/SQL 명시적 커서 사용

7. PL/SQL 명시적 커서 사용

 

A. Explicit Cursor

 

 

 

B. Cursor 선언

§Syntax

  CURSOR cursor_name IS

  Select_statement;

 

§Examples

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 loop 사용시 명시적 커서를 간단히 처리 가능

§open, fetch, close 작업이 묵시적으로 일어남

§Record 가 묵시적으로 선언

§Syntax

  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 속성]

§Fetch 를 수행하기 전에 %ISOPEN 속성을 사용하여 Cursor 가 open 되어있는지를 테스트

§Example

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]

§Syntax

CURSOR cursor_name

[(parameter_name datatype, …)]

IS

  select_statement;

OPEN cursor_name(parameter_value, …);

§Cursor 가 open 되고 query 가 실행되면서 parameter 값이 전달

§매번 다른 결과행 집합으로 여러 번 명시적 Cursor 를 open 한다.

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 절]

 

§Syntax

WHERE CURRENT OF cursor ;

§Cursor 를 사용하여 현재 행을 갱신 또는 삭제

§먼저 row에 lock 을 걸 수 있도록 FOR UPDATE 절을 포함

§WHERE CURRENT OF 절을 사용하여 명시적 CURSOR 에서 현재 행을 참조합니다.

update employees

set salary = …

where current of c_emp_cursor;