Oracle - Procedure 문법_EXCEPTION_Bulk_Bind

[ Oracle - Procedure 문법_EXCEPTION_Bulk_Bind ]

 

Bulk Bind

- Oracle9i에서는 Bulk bind가 보다 효율적이고 사용하기에 편리하도록 개선되었습니다.

   . bulk bind 연산의 실패에 대한 에러 핸들링 기능이 제공됩니다.

   . 동적 SQL에서 Bulk 연산을 수행할 수 있습니다.

 

Bulk Bind를 위한 예외 처리)

- 이전 릴리즈에서는 FORALL 연산이 수행되는 도중에 오류가 발생하면, 프로그램의 실행은 즉시 중단되고 예외가 발생했습니다.

- 새롭게 제공되는 예외 처리 메커니즘은 오류에 관한 정보를 기억하고 수행을 계속할 수 있도록 합니다.

- 프로그램이 수행을 마친 후에 그 동안 발생되었던 모든 오류가 한꺼번에 반환됩니다.

 

예: 예외 핸들링)

    DECLARE

    TYPE numtab IS table of number;

    v_discount numtab:= numtab(10,0,15,20,25,0,60,80,34,0);

    total_error NUMBER;

 

    BEGIN

        FORALL i IN v_discount.first..v_discount.last

            SAVE EXCEPTIONS

            UPDATE products

            set    min_price = list_price - list_price/v_numtab(i);

 

        Total_error := SQL%BULK_EXCEPTIONS.COUNT;   

 

예: 예외 핸들링)

      dbms_output.put_line('Total number of errors is ' || total_error);

 

      For i in 1 .. Total_error LOOP

           dbms_output.put_line('Error '||i||'occurred at iteration' ||SQL%BULK_EXCEPTIONS(i) );

          dbms_output.put_line('SQL Error Code is '||

                   SQL%BULK_EXCEPTIONS(i).error|| '[Error:'|| SQLERRM

                   (SQL%BULK_EXCEPTIONS(i).error)||']');

       END LOOP;

 

      END;

 

동적 SQL에서의 Bulk 연산)

    Oracle8i에서 소개된 동적 SQL은 Oracle9i부터는 다음과 같은 Bulk 연산을  지원합니다.

- BULK FETCH

- BULK EXECUTE IMMEDIATE

- FORALL과 함께 COLLECT INTO 및 RETURNING INTO

 

예: 동적 SQL과 Bulk Bind)

   DECLARE

        TYPE num_tab IS TABLE OF NUMBER;

        ids num_tab;

   BEGIN

     EXECUTE IMMEDIATE 'SELECT employee_id FROM employees'

        BULK COLLECT INTO ids;

   END;

 

예: 입력 변수를 위한 Bulk Bind)

    DECLARE

         TYPE num_tab IS TABLE OF NUMBER;

         TYPE char_tab is TABLE OF VARCHAR2(30);

 

       ids num_tab := num_tab (12,13,14,15);

       names char_tab := char_tab('R/D','IT','GL','PR');

 

    BEGIN

        FORALL iter IN 1..4 EXECUTE IMMEDIATE

                                 'INSERT INTO departments(department_id,

                                  department_name)VALUES(:1, :2)'

                               USING ids(iter), names(iter);

       END;

 

예: 출력 변수를 위한 Bulk Bind)

   DECLARE

        TYPE num_tab IS TABLE OF NUMBER;

        sql_str VARCHAR2(200);

        v_sal NUMBER := 10000;

        saltab num_tab;

   BEGIN

        sql_str := 'UPDATE employees SET salary = :1                                                      WHERE department_id = 10

                  RETURNING salary INTO :2';

        EXECUTE IMMEDIATE sql_str

                USING v_sal RETURNING BULK COLLECT INTO saltab;

   END;