OPEN FOR문

기본적인 SQL 구문을 동적 SQL로 변환하는 방법을 살펴봤는데, 지금까지 설명한 내용은 단일 로우를 처리하는 SQL문에 한정됐다. 이제 다중 로우를 처리하는 SQL문을 동적 SQL로 작성하는 방법을 알아 보자.

SELECT문은 단일 로우를 반환할 때는 INTO를 사용했고 EXECUTE IMMEDIATE문에서도 INTO를 사용해 결과를 받아왔다. 그러면 한 개 이상의 로우를 결과 집합으로 반환하는 SELECT문은 어떻게 처리해야 할까? 일반 PL/SQL 블록에서는 이때 커서로 결과 값을 받아 왔는데, 동적 SQL에서는 OPEN FOR문과 커서 변수를 사용해 결과 값을 받아올 수 있다.

    OPEN 커서변수 FOR 동적_SQL문장
    USING 바인드변수1, 바인드변수2, ... ;

즉 커서 변수를 선언하고 동적 SQL 문장을 OPEN FOR문으로 커서 변수와 연결하면, 해당 쿼리 결과가 커서 변수에 담긴다. 그럼 간단한 예제를 통해 동작 원리를 알아 보자. 이전 예제에서 사용했던 ch13_physicist 테이블을 비우고 다음과 같이 새로운 데이터를 넣어 보자.

입력

    TRUNCATE TABLE ch13_physicist ;

    INSERT INTO ch13_physicist VALUES (1, 'Galileo Galilei', TO_DATE('1564-02-15','YYYY-MM-DD'));

    INSERT INTO ch13_physicist VALUES (2, 'Isaac Newton', TO_DATE('1643-01-04','YYYY-MM-DD'));

    INSERT INTO ch13_physicist VALUES (3, 'Max Plank', TO_DATE('1858-04-23','YYYY-MM-DD'));

    INSERT INTO ch13_physicist VALUES (4, 'Albert Einstein', TO_DATE('1879-03-14','YYYY-MM-DD'));

    COMMIT;

결과

    table CH13_PHYSICIST이(가) 잘렸습니다.
    1개 행 이(가) 삽입되었습니다.
    1개 행 이(가) 삽입되었습니다.
    1개 행 이(가) 삽입되었습니다.
    1개 행 이(가) 삽입되었습니다.
    커밋되었습니다.

이제 ch13_physicist 테이블을 조회하는 쿼리를 동적 SQL로 구현해 보자.

입력

    DECLARE
      -- 커서 타입 선언
      TYPE query_physicist IS REF CURSOR;
      -- 커서 변수 선언
      myPhysicist query_physicist;
      -- 반환 값을 받을 레코드 선언
      empPhysicist ch13_physicist%ROWTYPE;

      vs_sql VARCHAR2(1000);
    BEGIN
      vs_sql := 'SELECT * FROM ch13_physicist';
      -- OPEN FOR문을 사용한 동적 SQL
      OPEN myPhysicist FOR vs_sql;
      --루프를 돌며 커서 변수에 담긴 값을 출력한다.
      LOOP
        FETCH myPhysicist INTO empPhysicist;
        EXIT WHEN myPhysicist%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(empPhysicist.names);
      END LOOP;
      --커서를 닫는다
      CLOSE myPhysicist;
    END;

결과

    Galileo Galilei
    Isaac Newton
    Max Plank
    Albert Einstein

OPEN FOR문을 사용한 점만 제외한다면 커서를 사용해서 결과를 담는 방법은 정적 SQL과 크게 다르지 않다. 이번에는 바인드 변수를 사용해 보자. WHERE 조건에 들어가는 값을 바인드 변수로 사용해야 하는데, 역시 OPEN FOR문에서도 USING 다음에 바인드 변수를 기술하면 된다.

입력

    DECLARE
      -- 커서 변수 선언
      myPhysicist SYS_REFCURSOR;
      -- 반환 값을 받을 레코드 선언
      empPhysicist ch13_physicist%ROWTYPE;

      vs_sql VARCHAR2(1000);
      vn_id    ch13_physicist.ids%TYPE    := 1;
      vs_names ch13_physicist.names%TYPE  := 'Albert%';

    BEGIN
      -- 바인드 변수 사용을 위해 WHERE조건 추가
      vs_sql := 'SELECT * FROM ch13_physicist WHERE IDS > :a AND NAMES LIKE :a ';
      -- OPEN FOR문을 사용한 동적 SQL
      OPEN myPhysicist FOR vs_sql USING vn_id, vs_names;

      --루프를 돌며 커서 변수에 담긴 값을 출력
      LOOP
        FETCH myPhysicist INTO empPhysicist;
        EXIT WHEN myPhysicist%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(empPhysicist.names);
      END LOOP;

      CLOSE myPhysicist;
    END;

결과

    Albert Einstein

이번 예제에서는 SYS_REFCURSOR 커서 변수를 선언해서 사용했고 두 가지 조건을 추가했다. 위 예제코드를 보면 프로시저 호출이나 익명 블록을 실행하는 동적 SQL문이 아니므로 SQL문 내의 바인드 변수명은 중요치 않음을 알 수 있다. OPEN FOR문에서도 EXECUTE IMMEDIATE문처럼 USING 다음에 바인드 변수를 순서와 타입에 맞춰 기술해 주면 이상없이 해당 SQL문이 실행되고 원하던 결과를 반환한다. 다중 로우를 반환하는 SELECT문을 동적 쿼리로 만들어 실행하는 방법을 정리해보면 다음과 같다.

ⓐ 커서 변수를 선언한다

REF CURSOR 타입을 선언한 뒤 해당 타입의 커서 변수를 선언하거나 시스템에서 제공하는 SYS_REFCURSOR 타입의 커서 변수를 선언한다.

ⓑ 반환 받을 레코드 변수를 선언한다

ⓒ 동적 SQL문을 문자열 형태로 작성한 뒤, OPEN FOR문을 이용해 선언한 커서와 연결한다

바인드 변수 사용 시 OPEN FOR문의 USING 다음에 해당 변수를 명시한다.

ⓓ 루프를 돌리면서 커서를 패치해 값을 받아온다

ⓔ 루프가 종료되면 해당 커서를 닫는다