[PL/SQL] 다중 로우를 처리하는 동적 SQL문

다중 로우를 처리하는 동적 SQL문

OPEN FOR 문

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

예제

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'));

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;

WHERE 조건에 들어가는 값을 바인드 변수로 사용한다.

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
    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;

위 쿼리는 프로시저 호출이나 익명 블록을 실행하는 동적 SQL 문이 아니므로 SQL문 내의 바인드 변수명은 중요하지 않고, 바인드 변수를 순서와 타입에 맞춰 써주면된다.

다중 로우를 반환하는 SELECT문을 동적 쿼리로 만들어 실행하는 방법

1. 커서 변수를 선언한다.

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

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

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

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

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

성능 향상을 위한 다중 로우 처리

만약, 쿼리 결과로 반환되는 로우 수가 많다면 루프를 돌면서 결과를 패치하는 식의 처리는 시간도 많이 걸리고 성능 면에서 좋지 않다.

일일이 한 로우씩 읽어가지 않고 통채로 처리하는 방법이 있는데 바로 BULK COLLECT INTO절이다.

DECLARE
    -- 레코드 선언
    TYPE REC_PHYSICIST IS RECORD
    (
        IDS         CH13_PHYSICIST.IDS%TYPE,
        NAMES       CH13_PHYSICIST.NAMES%TYPE,
        BIRTH_DT    CH13_PHYSICIST.BIRTH_DT%TYPE
    );
    
    -- 레코드를 항목으로 하는 중첩 테이블 선언
    TYPE NT_PHYSICIST IS TABLE OF REC_PHYSICIST;
    
    -- 중첩테이블 변수 선언
    VR_PHYSICIST    NT_PHYSICIST;
BEGIN
    SELECT  *
    BULK COLLECT INTO VR_PHYSICIST
    FROM    CH13_PHYSICIST;
    
    FOR IDX IN 1..VR_PHYSICIST.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(VR_PHYSICIST(IDX).NAMES);
    END LOOP;
END;

DECLARE
    TYPE REC_PHYSICIST IS RECORD
    (
        IDS         CH13_PHYSICIST.IDS%TYPE,
        NAMES       CH13_PHYSICIST.NAMES%TYPE,
        BIRTH_DT    CH13_PHYSICIST.BIRTH_DT%TYPE
    );
    
    -- 레코드를 항목으로 하는 중첩 테이블 선언
    TYPE NT_PHYSICIST IS TABLE OF REC_PHYSICIST;
    
    -- 중첩테이블 변수 선언
    VR_PHYSICIST    NT_PHYSICIST;
    VS_SQL          VARCHAR2(1000);
    VN_IDS          CH13_PHYSICIST.IDS%TYPE := 1;
BEGIN
    VS_SQL := 'SELECT * FROM CH13_PHYSICIST WHERE IDS > :A';
    
    EXECUTE IMMEDIATE VS_SQL BULK COLLECT INTO VR_PHYSICIST USING VN_IDS;
 
    FOR IDX IN 1..VR_PHYSICIST.COUNT
    LOOP
        DBMS_OUTPUT.PUT_LINE(VR_PHYSICIST(IDX).NAMES);
    END LOOP;
END;