Posted Updated database / oracle12 minutes read (About 1870 words)

PL/SQL - Cursor

Cursor

  • 특정 SQL 문장을 처리한 결과를 담고 있는 영역 (PRIVATE SQL이라는 메모리 영역) 을 가르키는 일종의 포인터이다.

  • 커서를 통해 처리된 SQL 문장의 결과 집합에 접근할 수 있다. 개별 로우에 순차적으로 접근하는 형태이다.

  • 커서의 종류에는 묵시적 커서와 명시적 커서가 있는데, 묵시적 커서는 oracle 내부적으로 자동으로 생성되어 사용하는 커서이다. PL/SQL 블록에서 실행하는 SQL 문장이 실행될 때마다 자동으로 만들어져 사용된다. 명시적 커서는 개발자가 직접 정의해서 사용하는 커서를 뜻한다.

  • 커서의 종류와 무관하게 Cursor의 라이프사이클은 open -> fetch -> close 3단계로 나누어 진행된다.

묵시적 Cursor

  • 개발자가 Cursor의 동작에 관여할 수는 없지만, Cursor의 정보를 참조할 수는 있다.
1
2
3
4
5
6
7
8
9
10
11
SET SERVEROUTPUT ON;
DECLARE 
    vn_department_id employees.department_id%TYPE := 80;
BEGIN 
    UPDATE employees 
    SET emp_name = emp_name
    WHERE department_id = vn_department_id;
    
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT); --SQL 커서의 ROWCOUNT 속성
    COMMIT;
END;

위 예제에서 보면 SQL 커서를 어디에도 선언하진 않았지만, 커서의 정보를 참조할 수는 있다.
기타 커서의 속성들은 다음과 같다.

  • SQL%FOUND : 결과 집합의 행수가 1개 이상이면 TRUE 아니면 FALSE를 반환
  • SQL%NOTFOUND : 결과 집합의 행수가 0개이면 TRUE 아니면 FALSE를 반환
  • SQL%ROWCOUNT : 영향 받은 결과 집합의 행수를 반환 , 없으면 0을 반환

명시적 Cursor

  1. Cursor 선언 
1
2
3
CURSOR 커서명 (매개변수)
IS
SELECT 문장;

명시적 커서 선언은 위와 같이 가능하다. 이때 매개변수는 생략이 가능한데, 매개변수의 주 사용 목적은 SELECT Query의 WHERE 절에 들어갈 조건으로 사용된다고 한다.

  1. Cursor Open 
1
OPEN 커서명 (매개변수)

커서를 사용하려면 먼저 커서를 열어야 한다.

  1. Cursor close
1
CLOSE 커서명;

커서를 닫는 다는 것은 말그대로 커서가 가르키는 결과 집합을 메모리에서 내린다는 뜻이다.

명시적 Cursor 사용예시

1
FETCH 커서명 INTO 변수1,변수2....
  • 위와 같이 실행하면 커서에서 반환되는 각 컬럼 값을 변수에 할당할 수 있다. 이때 당연히 타입은 일치해주어야 한다. 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
DECLARE 
    vs_emp_name employees.emp_name%TYPE;
    
    -- Cursor 선언
    CURSOR cur_emp_dep ( cp_department_id employees.department_id%TYPE )
    IS
    SELECT emp_name
    FROM   employees 
    WHERE department_id = cp_department_id;
BEGIN 
    -- Cursor 오픈 
    OPEN cur_emp_dep(90);
    LOOP
        -- Cursor 사용 (FETCH 커서명 INTO 변수)
        FETCH cur_emp_dep INTO vs_emp_name; -- cur_emp_dep 커서가 가르키는 로우를 vs_emp_name변수에 할당
        EXIT WHEN cur_emp_dep%NOTFOUND; -- cursor의 NOTFOUND속성 접근. 만약 로우가 없으면 반복문 종료 
        DBMS_OUTPUT.PUT_LINE(vs_emp_name);
    END LOOP;


    --Cursor close
    CLOSE cur_emp_dep;
END;

실수로 CLOSE 커서명을 코딩하지 않아도, 프로시저는 정상적으로 compile되고 동작한다. 또한 프로시저가 종료될때, oracle은 cursor를 자동으로 닫아준다고 한다. 하지만 커서 닫는 작업이 오버헤드를 발생시키므로 되도록 명시적으로 닫아주는 것을 권고하고 있다.

명시적 Cursor 사용예시(For Loop)

1
2
3
4
FOR '커서가 가르키는 레코드' IN 커서명(매개변수)
LOOP
 처리문;
END LOOP;
  • Cursor는 FOR문과 같이 사용될 수도 있다. FOR문과 같이 사용하게 될 경우에 Cursor가 가르키는 레코드가 없으면 (패치가 끝나면) 자동으로 반복문이 종료된다. 또한 Cursor를 명시적으로 닫거나, 열필요가 없어지므로 코드가 휠씬 간결해진다. 
1
2
3
4
5
6
7
8
9
10
11
12
13
14
DECLARE 
    vs_emp_name employees.emp_name%TYPE;
    -- Cursor 선언
    CURSOR cur_emp_dep ( cp_department_id employees.department_id%TYPE )
    IS
    SELECT emp_name
    FROM   employees 
    WHERE department_id = cp_department_id;
BEGIN 
    FOR emp_rec in cur_emp_dep(90)
    LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.emp_name);
    END LOOP;
END;
  • 아예 커서 선언부 부분을 FOR 문의 커서명 자리에 위치시키는 방법도 있다. 
1
2
3
4
5
6
FOR emp_rec in  (
                SELECT emp_name
                FROM employees 
                WHERE department_id = 90 
                )
//

Cursor 변수

  • 한개 이상의 쿼리를 연결해 사용할 수 있는 Cursor 변수라는 개념이 있다. 커서 변수를 한번 선언한 뒤에 같은 커서 변수를 또 다른 결과 집합 쿼리에 연결해서 사용하는 것이다.

  • 변수처럼 Cursor 변수를 함수나 procedure의 매개변수로 전달 가능하다.

  • Cursor 상수와 마찬가지로 Cursor 속성을 사용할 수 있다. (ex) 커서명%커서속성)

  • Cursor 변수를 선언하는 방법은 두 단계로 나뉜다. (1. 커서 타입 선언 2. 커서 타입으로 커서 변수 선언)

1
2
3
TYPE 커서타입명 IS REF CURSOR 반환타입; -- 반환타입은 생략가능하며 반환타입을 생략할때는 
                                      -- 결과 집합이 유동적임으로 약한 커서라고도 부른다. 
커서변수명 커서타입명;

구체적인 사용예시를 살펴보면 다음과 같다. 

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Cursor 변수 사용예제
DECLARE 
    vs_emp_name employees.emp_name%TYPE;
    -- 약한 Cursor 타입 (반환타입 명시하지않음) 
    TYPE emp_dep_curtype IS REF CURSOR;
    -- Cursor 변수 선언 
    emp_dep_curvar emp_dep_curtype;

BEGIN 
    
    -- 커서 변수를 사용한 커서 정의 및 오픈 
    OPEN emp_dep_curvar FOR SELECT emp_name
                              FROM employees
                             WHERE department_id = 90;
    LOOP 
        FETCH emp_dep_curvar INTO vs_emp_name;
        EXIT WHEN emp_dep_curvar%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(vs_emp_name);
    END LOOP;
END;

커서변수는 특징으로 결과 집합이 고정될 필요가 없다는 특징을 가진다. 예를 들면 커서변수에 다른 결과집합을 한 프로시저안에서 할당가능하다는 소리이다. 추가로 커서변수는 자동으로 닫혀짐으로, 개발자가 별도로 메모리에서 결과집합을 내릴필요는 없다. 

1
2
3
OPEN test_curvar FOR SELECT * FROM A;
OPEN test_curvar FOR SELECT * FROM B;
OPEN test_curvar FOR SELECT * FROM C;

Built-in Cursor 타입 - SYS_REFCURSOR

  • Oracle 에서는 SYS_REFCURSOR라는 자체적으로 약한 커서 타입을 제공한다.
  • SYS_REFCURSOR를 사용하면 별도로 커서타입을 명시할 필요가 없다. 
1
2
3
4
5
DECLARE 
    -- Cursor 타입 선언 생략 
    test_curvar SYS_REFCURSOR;
BEGIN 
...

Cursor 변수의 활용 - 매개변수로 전달

  • 앞서 정리한것처럼 Cursor 변수는 procedure나 함수의 매개변수로 전달이 가능하다.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
DECLARE 
    emp_dep_curvar SYS_REFCURSOR;
    vs_emp_name employees.emp_name%TYPE;
    -- cursor 변수를 IN OUT 매개변수로 전달 
    PROCEDURE test_cursor_argu (p_curvar IN OUT SYS_REFCURSOR)
    IS 
    BEGIN 
        OPEN p_curvar FOR SELECT emp_name
                               FROM employees
                               WHERE department_id = 90;
    END;
BEGIN 
    test_cursor_argu(emp_dep_curvar);
    LOOP 
        FETCH emp_dep_curvar INTO vs_emp_name;
        EXIT WHEN emp_dep_curvar%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(vs_emp_name);
    END LOOP;
END;

Cursor 표현식

  • select 문에서 칼럼 형태로 커서를 사용하는 표현식으로 하나의 cursor가 반환되지만 실제로 cursor는 내부적으로 여러개의 값을 가지고 있다. 
1
CURSOR(서브 쿼리)
1
2
3
4
5
6
SELECT 
    d.department_name,
    CURSOR( SELECT e.emp_name
            FROM employees e 
            WHERE e.department_id = d.department_id ) AS emp_name
FROM departments d;

실제로 sqldeveloper 에서 반환결과를 보면 하나의 부서명에 하나의 커서 (여러개의 record) 가 반환되는 것을 볼 수 있다.

Comments

Categories

Recents

Archives

×