농부지기

[ Oracle - Procedure 문법_EXCEPTION_020 ]

Error Handling

1. Predefined & Nonpredefined

Define

 Error번호

 exception_name

Predefined

 ORA_01403

ORA_01422

 NO_DATA_FOUND

TOO_MANY_ROWS

Nonpredefined

 ORA_02292

 e_products_remaining

2. Predefined Exception

  ex) EXCEPTION

        WHEN NO_DATA FOUND THEN

          v_message := TO_CHAR (v_product_id)||' Is invalid.';

        WHEN TOO_MANY_ROWS THEN

          v_message := 'Data corruption in S_PRODUCT';

        WHEN OTHERS THEN

          v_message := 'Other error occurred.';

        END;

3. NonPredefined Error

 exception EXCEPTION;

 ...

PRAGMA EXCEPTION_INIT (exception, error_number);

  ex) [DECLARE}

         e_products_remaining EXCEPTION;

       PRAGMA EXCEPTION_INIT (

         e_products_remaining, -2292);

         ...

       BEGIN

         ...

       EXCEPTION

       WHEN e_products_remaining THEN

          v_message := 'Referential integrity constraint violated.';

          ...

       END;

4. User_Defined Exception

exception EXCEPTION;

...

RAISE exception;

  ex) [DECLARE]

          e_amount-remaining EXCEPTION;

          ...

       BEGIN

          ...

       RAISE e_amount_remaining;

          ...

       EXCEPTION

       WHEN e_products_remaining THEN

         v_message := 'There is still an amount in stock.';

         ...

       END;

발주처:http://myhome.netsgo.com/mino70/main_03_oracle02.htm

[ Oracle - Procedure 문법_EXCEPTION_010 ]

시스템 EXCEPTION

1. 변수정의 :

   문법 : SQL_ERROR_RTN   EXCEPTION;

   설명 : 모름???

2. SOURCE에서 사용법

    문법 : BEGIN

                         SELECT A.item_mon

                         INTO      nItem_mon

                         FROM   py_change A

                         WHERE  A.input_ymd = 'value' ;

              EXCEPTION      WHEN OTHERS THEN

                         nItem_mon  := 0;

              END;

     설명 : - select, insert, delete, update문이 실행 중 오류발생가능성 때문에 begin ~ exception~end; 문장을 기술한다.

               - select시 : others 는 주로 notfound 일 경우에 발생하므로  INTO 변수에 0 또는 space 값을 setting 한다.

               - insert, delete, update시 : others는 주로 오류가 발생한다.

                 이때, sqlcode 와 sqlerrm 시스템변수값을 활용하여 오류의 원인을 파악한다.

3.   EXCEPTION

      EXCEPTION

        WHEN NO_DATA_FOUND THEN

            i_errmsg := SQLCODE;

        WHEN OTHERS THEN

            i_errmsg := SQLCODE;

--------------------------------------------------------------------------

* 사용자 EXCEPTION

--------------------------------------------------------------------------

사용자 정의 예외처리는 오라클 서버에 의해 발생될 수 없는 예외 상황에 대해서 사용자가 임의로 정의하여 강제로 발생되게 하는 방법이다.

예외명은 DECLARE절에서 선언하고, 예외가 발생해야할 조건은 실행부분에 정의하면 된다. 예외 상황 발생시 처리해야할 루틴은 미리 정의된 Oracle Server 예외에서와 같이 명시해주면 된다. 단지 사용자가 정의한 예외명을 적어주면 된다.

[ 구문법 ]

    DECLARE

           Exception_name EXCEPTION;

           ......

     BEGIN

           ......

      RAISE Exception_name;

             ......

      EXCEPTION

      WHEN Exception_name THEN

               Statement-1;

               ......

[ 예제]

         DECLARE

                row_cnt int :=0;

               output_line varchar2(80);

               exception_row_over_5 EXCEPTION;

         BEGIN

               SELECT COUNT(*) INTO row_cnt FROM test_table;

               IF row_cnt > 5 THEN

                     RAISE exception_row_over_5;

               END IF;

               EXCEPTION

                     WHEN exception_row_over_5 THEN

                               output_line := 'Row_Count= ' || row_cnt;

                               dbms_output.put_line(output_line);

                END;

                /

[ Oracle - Procedure Package_DBMS_SQL_사용법2 ]

▣ DBMS_SQL package를 이용한 프로 시져 생성의 예

SQL>CREATE OR REPLACE PROCEDURE test(txt varchar2)  AS

                c integer;

                rows integer;

        BEGIN

                c := DBMS_SQL.OPEN_CURSOR;

                DBMS_SQL.PARSE(c,'create or replace '|| txt ,DBMS_SQL.NATIVE);

                rows := DBMS_SQL.EXECUTE(c);

                DBMS_SQL.CLOSE_CURSOR(c);

        END;

SQL>exec test('procedure ttum(aa  number) AS  kk number; BEGIN kk:= 0 ; kk := kk + aa ; END ; ');

SQL> exec test ('procedure ttt1(aa number) as kk varchar2(10); begin select 1 into kk from dual; end;');