BULK SQL

BULK 로 수행하는 SQL 에 대해 설명합니다.

개요

BULK SQL 은 연속된 여러 번의 SQL 을 BATCH 단위로 한 번에 처리하는 것을 말합니다. PSM 에서 SQL처리 엔진으로 메시지를 BATCH 단위로 처리하기 때문에 일반적으로 연속된 SQL 처리보다 훨씬 빠른 성능상의 이점이 있습니다.

FORALL 문

FORALL 문은 FOR-LOOP 문과 비슷하나 하나의 DML 만을 사용할 수 있으며 batch 단위로 처리하여 실행이 빠른 반복문입니다. 단, index를 DML 에 직접적으로 명시할 수는 없으며 반드시 다른 collection의 index로 사용해야 합니다. Index로 사용하므로 FOR-LOOP의 REVERSE는 사용할 수 없고, collection 자체의 순서를 바꿔야 합니다.

기본적인 사용법은 다음과 같습니다.

FORALL index IN low_bound..high_bound 
    DML 실행문;

항목	설명
index	일반적으로 변수를 사용 시스템이 내부적으로 선언하기 때문에 명시적으로 선언할 필요가 없음 다른 collection 의 참조 index 로 사용해야 함 index를 볼 수 있는 범위는 FORALL 문의 내부
low_bound, high_bound	LOOP 문의 범위를 지정 주로 숫자 상수를 사용 그러나 반드시 숫자 상수일 필요는 없으며, 숫자 상수로 변환될 수 있는 임의의 식을 사용할 수 있음

다음은 FORALL 문의 예입니다.

DROP TABLE T1;
CREATE TABLE T1 (C1 NUMBER, C2 VARCHAR2(20)); 
DECLARE
    TYPE NumTab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    TYPE NameTab IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
    nums NumTab; 
    names NameTab;
BEGIN

FOR i IN 1..10 LOOP
    nums(i) := i;
    names(i) := 'Name' || TO_CHAR(i); 
END LOOP;

FORALL i IN 1..10
    INSERT INTO T1 VALUES(nums(i), names(i)); 
END;

위 예에서 보듯이 index는 i 라는 변수로 정의하고, low_bound와 high_bound는 1부터 시작하여 10에 도달 할 때까지 값을 1씩 증가시키며 LOOP 문을 반복하며 미리 만들어 놓은 nums 와 names 의 index 로 i 를 사용합니다. FOR-LOOP 문과 마찬가지로 low_bound와 high_bound는 반드시 숫자 상수일 필요는 없습니다.

예를 들어 다음과 같이 쓸 수 있습니다.

DECLARE
    TYPE NumArr IS VARRAY(20) OF NUMBER;
    nums NumArr := NumArr(2, 5, 7); 
BEGIN
    FORALL i IN nums.FIRST..nums.LAST 
        DELETE FROM T1 WHERE C1 = nums(i);
END;

SAVE EXCEPTIONS 예약어의 사용

FORALL 문 안에서 SAVE EXCEPTIONS 예약어를 사용하게 되면 해당 DML 이 실패해도 계속 진행하게 됩니다. 이 때 해당 EXCEPTION 을 저장해 놓고 나머지 DML들을 실행한 뒤 FORALL 문이 종료되고 EXCEPTION이 처리됩니다. SAVE EXCEPTIONS를 명시하지 않으면 DML이 실패하는 즉시 EXCEPTION이 발생하고, FORALL 문이 종료합니다.

예시는 다음과 같습니다.

DROP TABLE T1;
CREATE TABLE T1 (C1 NUMBER(1), C2 VARCHAR2(20)); 
DECLARE
    TYPE NumTab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    TYPE NameTab IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
    nums NumTab; 
    names NameTab;
BEGIN

FOR i IN 1..10 LOOP
    nums(i) := 11-i;
    names(i) := 'Name' || TO_CHAR(11-i); 
END LOOP;

FORALL i IN 1..10 SAVE EXCEPTIONS
    INSERT INTO T1 VALUES(nums(i), names(i)); 
EXCEPTION
    WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('DML Error Occurred.'); 
END;

위 예시에서는 precesion 이 1 로 설정된 column 에 10 부터 1 까지를 INSERT 하려하고 있습니다. SAVE EX CEPTIONS를 명시하지 않으면 INSERT가 실패하는 즉시 FORALL문이 종료되지만 SAVE EXCEPTIONS를 명시하였으므로 10 에서 에러를 발생다음 9 부터 1 까지는 실행됩니다.

SAVE EXCEPTIONS 를 명시하고 FORALL 문이 끝나면 -15231 ERROR_PSM_PATCH_DML_FAILED 단 하나의 EXCEPTION 이 발생하지만 묵시적 커서 속성 SQL%BULK_EXCEPTIONS를 통해 FORALL문 수행 도중 발생한 여러 EXCEPTION 들에 대한 정보를 받아올 수 있습니다. SQL%BULK_EXCEPTIONS 는 BULK SQL 수행시 발생하는 여러 EXCEPTION 에 대한 ASSOCIATIVE ARRAY 로, SQL%BULK_EXCEPTIONS.COUNT 가 EXCEPTION 개수를 나타냅니다. 각 에러 i번째에 대하여 SQL%BULK_EXCEPTIONS(i).ER ROR_CODE 는 해당 SQL 의 TIBERO 에러코드를, SQL%BULK_EXCEPTIONS(i).ERROR_INDEX는 FORALL 문 내에서 몇 번째 실행의 에러인지 나타냅니다.

다음은 그 예시입니다.

DROP TABLE T1;
CREATE TABLE T1 (C1 NUMBER(1), C2 VARCHAR2(20)); 
DECLARE
    TYPE NumTab IS TABLE OF NUMBER INDEX BY PLS_INTEGER;
    TYPE NameTab IS TABLE OF VARCHAR2(20) INDEX BY PLS_INTEGER;
    nums NumTab; 
    names NameTab;

    error_message VARCHAR2(100); 
    bad_stmt_no	PLS_INTEGER; 
    dml_errors EXCEPTION;
    PRAGMA EXCEPTION_INIT(dml_errors, -15231); 
    
BEGIN

FOR i IN 1..10 LOOP
    nums(i) := 12-i;
    names(i) := 'Name' || TO_CHAR(12-i); 
END LOOP;

FORALL i IN 1..11 SAVE EXCEPTIONS
    INSERT INTO T1 VALUES(nums(i), names(i)); 
EXCEPTION
    WHEN dml_errors THEN
        FOR i IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
            error_message := SQLERRM(-(SQL%BULK_EXCEPTIONS(i).ERROR_CODE));
        DBMS_OUTPUT.PUT_LINE (error_message);
        bad_stmt_no := SQL%BULK_EXCEPTIONS(i).ERROR_INDEX; 
    END LOOP;
END;

INDICES OF 예약어의 사용

IN lowbound..highbound 대신 INDICES OF collection을 사용하여 해당 collection의 인덱스를 사용할 수 있습니다. 이 경우, 뒤에 BETWEEN lower_bound AND upper_bound 를 명시하여 사용할 인덱스를 제한할 수 있습니다.

예시는 다음과 같습니다.

DROP TABLE T1;
CREATE TABLE T1 (C1 NUMBER, C2 VARCHAR2(20)); 
DECLARE
    TYPE NumArr IS VARRAY(20) OF NUMBER;
    nums NumArr := NumArr(2, 5, 7, 9); 
BEGIN
    FORALL i IN INDICES OF nums BETWEEN 2 and 3
        INSERT INTO T1 VALUES (nums(i), TO_CHAR(nums(i))); 
END;

주의할 점은 위 예시의 VARRAY 내에서 2 와 3 사이의 값을 사용하는게 아니고 두 번째, 세 번째 값 까지 사용합니다는 점입니다. COLLECTION 내의 인덱스는 1 부터 시작합니다.

VALUES OF 예약어의 사용

IN lowbound..highbound 대신 VALUES OF collection을 사용하여 해당 collection의 값을 사용할 수 있습니다. 단, collection의 원소들은 PLS_INTEGER이거나 BINARY_INTEGER여야 합니다.

예시는 다음과 같습니다.