컬렉션 할당 기능 개선

개요

Oracle 21c부터 Iterator Choice로 컬렉션에 값을 할당할 수 있습니다.

 

테스트를 위해 아래와 같이 테이블을 생성하겠습니다.

-- 1
DROP TABLE t1 PURGE;
CREATE TABLE t1 (c1 NUMBER, c2 VARCHAR2(1));

INSERT INTO t1 VALUES (2, 'A');
INSERT INTO t1 VALUES (4, 'B');
INSERT INTO t1 VALUES (6, 'C');
COMMIT;

 

Basic Iterator Choice

Basic Iterator Choice는 컬렉션에 값을 할당할 수 없습니다.

 

아래 예제는 에러가 발생합니다.

-- 2
DECLARE
    TYPE taa IS TABLE OF VARCHAR2(1) INDEX BY PLS_INTEGER;
    v1 taa;
BEGIN
    v1 := taa (FOR v IN (SELECT * FROM t1) => v.c2);

    FOR i, v IN PAIRS OF v1 LOOP
        DBMS_OUTPUT.PUT_LINE (i || ',' || v);
    END LOOP;
END;
/
PLS-00868: 반복 제어의 iterand 유형이 모음 인덱스 유형과 호환되지 않습니다.
           기본 이터레이터 연관 대신 SEQUENCE 또는 INDEX 이터레이터 연관을 사용하십시오.

 

Sequence Iterator Choice

Sequence Iterator Choice는 항상 순번으로 컬렉션을 인덱싱합니다.

 

아래 예제는 Sequence Iterator Choice로 연관 배열에 값을 할당합니다.

-- 3
DECLARE
    TYPE taa IS TABLE OF t1%ROWTYPE INDEX BY PLS_INTEGER;
    v1 taa;
BEGIN
    v1 := taa (FOR v IN (SELECT * FROM t1) SEQUENCE => v);

    FOR i, v IN PAIRS OF v1 LOOP
        DBMS_OUTPUT.PUT_LINE (i || ',' || v.c1 || ',' || v.c2);
    END LOOP;
END;
/
1,2,A
2,4,B
3,6,C

PL/SQL 처리가 정상적으로 완료되었습니다.

 

앞선 예제는 아래 예제와 동일하게 동작합니다.

-- 4
DECLARE
    TYPE taa IS TABLE OF t1%ROWTYPE INDEX BY PLS_INTEGER;
    v1 taa;
BEGIN
    FOR i, v IN PAIRS OF (SELECT * FROM t1) LOOP
        v1(i) := v;
    END LOOP;

    FOR i, v IN PAIRS OF v1 LOOP
        DBMS_OUTPUT.PUT_LINE (i || ',' || v.c1 || ',' || v.c2);
    END LOOP;
END;
/
1,2,A
2,4,B
3,6,C

PL/SQL 처리가 정상적으로 완료되었습니다.

 

아래 예제처럼 BULK COLLECT INTO 절을 사용하는 편이 성능 측면에서 유리합니다.

-- 4
DECLARE
    TYPE taa IS TABLE OF t1%ROWTYPE INDEX BY PLS_INTEGER;
    v1 taa;
BEGIN
    SELECT * BULK COLLECT INTO v1 FROM t1;

    FOR i, v IN PAIRS OF v1 LOOP
        DBMS_OUTPUT.PUT_LINE (i || ',' || v.c1 || ',' || v.c2);
    END LOOP;
END;
/
1,2,A
2,4,B
3,6,C

PL/SQL 처리가 정상적으로 완료되었습니다.

 

Index Iterator Choice

Index Iterator Choice를 사용하면 테이블 값으로 컬렉션을 인덱싱할 수 있습니다.

 

아래 예제는 SQL 문을 사용한 Index Iterator Choice로 연관 배열에 값을 할당합니다.

-- 5: sql statement
DECLARE
    TYPE taa IS TABLE OF VARCHAR2(1) INDEX BY PLS_INTEGER;
    v1 taa;
BEGIN
    v1 := taa (FOR v IN (SELECT * FROM t1) INDEX v.c1 => v.c2);

    FOR i, v IN PAIRS OF v1 LOOP
        DBMS_OUTPUT.PUT_LINE (i || ',' || v);
    END LOOP;
END;
/
2,A
4,B
6,C

PL/SQL 처리가 정상적으로 완료되었습니다.

 

아래 예제는 커서 오브젝트를 사용한 Index Iterator Choice로 연관 배열에 값을 할당합니다.

-- 6: cursor object
DECLARE
    CURSOR cur1 IS SELECT * FROM t1;
    TYPE taa IS TABLE OF VARCHAR2(1) INDEX BY PLS_INTEGER;
    v1 taa;
BEGIN
    v1 := taa (FOR v IN cur1 INDEX v.c1 => v.c2);

    FOR i, v IN PAIRS OF v1 LOOP
        DBMS_OUTPUT.PUT_LINE (i || ',' || v);
    END LOOP;
END;
/
2,A
4,B
6,C

PL/SQL 처리가 정상적으로 완료되었습니다.

 

아래 예제는 커서 변수를 사용한 Index Iterator Choice로 연관 배열에 값을 할당합니다.

-- 7: cursor variable
DECLARE
    TYPE taa IS TABLE OF VARCHAR2(1) INDEX BY PLS_INTEGER;
    v1 SYS_REFCURSOR;
    v2 taa;
BEGIN
    OPEN v1 FOR SELECT * FROM t1;

    v2 := taa (FOR v t1%ROWTYPE IN v1 INDEX v.c1 => v.c2);

    CLOSE v1;

    FOR i, v IN PAIRS OF v2 LOOP
        DBMS_OUTPUT.PUT_LINE (i || ',' || v);
    END LOOP;
END;
/
2,A
4,B
6,C

PL/SQL 처리가 정상적으로 완료되었습니다.

 

아래 예제는 동적 SQL 문을 사용한 Index Iterator Choice로 연관 배열에 값을 할당합니다.

-- 8: dynamic sql
DECLARE
    v_sql_text VARCHAR2(4000) := 'SELECT * FROM T1';
    TYPE taa IS TABLE OF VARCHAR2(1) INDEX BY PLS_INTEGER;
    v1 taa;
BEGIN
    v1 := taa (FOR v t1%ROWTYPE IN (EXECUTE IMMEDIATE v_sql_text) INDEX v.c1 => v.c2);

    FOR i, v IN PAIRS OF v1 LOOP
        DBMS_OUTPUT.PUT_LINE (i || ',' || v);
    END LOOP;
END;
/
2,A
4,B
6,C

PL/SQL 처리가 정상적으로 완료되었습니다.