1. Procedure에서 Exception 처리

DECLARE
    V_EXCEPTION EXCEPTION;

    V_NUM NUMBER := 0;
BEGIN

    FOR V_ROW IN (SELECT LEVEL AS SEQ FROM DUAL CONNECT BY LEVEL <= 10)
    LOOP
    
        BEGIN
            
            V_NUM := MOD(V_ROW.SEQ, 5);
            
            IF V_NUM = 0 THEN RAISE V_EXCEPTION; END IF;
            
            DBMS_OUTPUT.PUT_LINE('출력 : ' || V_ROW.SEQ || ' : ' || V_NUM);
            
            EXCEPTION
                WHEN V_EXCEPTION THEN
                    DBMS_OUTPUT.PUT_LINE('1. 예외 : ' || V_ROW.SEQ || ' : ' || V_NUM);
                    
                WHEN OTHERS THEN
                    DBMS_OUTPUT.PUT_LINE('2. 예외 : ' || V_ROW.SEQ || ' : ' || V_NUM);
        END;
        
    END LOOP;

    EXCEPTION
        WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE('3. 예외 : ' || SQLERRM);
END;
/

2. Procedure에서 RAISE_APPLICATION_ERROR 처리

BEGIN

    FOR V_IDX IN 1 .. 10
    LOOP
    
        BEGIN
            
            IF V_IDX = 5 THEN
            	RAISE_APPLICATION_ERROR(-20000, '에러!');
            END IF;
            
            DBMS_OUTPUT.PUT_LINE(V_IDX);
            
		    EXCEPTION
		        WHEN OTHERS THEN
		            DBMS_OUTPUT.PUT_LINE(V_IDX || ' : ' || SQLERRM);
		           
        END;
        
    END LOOP;

END;



1
2
3
4
5 : ORA-20000: 에러!
6
7
8
9
10

3. Function에서 RAISE_APPLICATION_ERROR 처리

CREATE OR REPLACE FUNCTION GET_ROW_INFO  ( P_ROW_NO  IN VARCHAR2)  RETURN ROW_INFO
IS
        V_ROW_NO VARCHAR2(100);
        V_ROW_NM VARCHAR2(100);
        V_ROW_INFO   ROW_INFO := ROW_INFO(NULL, NULL, NULL, NULL);
BEGIN

        SELECT *
        INTO V_ROW_NO
        ,    V_ROW_NM
        FROM XXXX
        WHERE...
        ;

        IF V_ROW_NO IS NULL THEN
            RAISE_APPLICATION_ERROR(-20000, 'ROW_NO에 데이터가 없습니다.');
        END IF;

        IF V_ROW_NM IS NULL THEN
            RAISE_APPLICATION_ERROR(-20000, 'ROW_NM에 데이터가 없습니다.');
        END IF;
        
        RETURN V_ROW_INFO;

        EXCEPTION
                WHEN OTHERS THEN
                        DBMS_OUTPUT.PUT_LINE('ROW_NO : ' || P_ROW_NO || ' => ' || SQLERRM);
                        RAISE_APPLICATION_ERROR(-20000, 'ROW_NO : ' || P_ROW_NO || ' => ' || SQLERRM); <-- 이구문이 있으면 그냥 실행으로도(DBMS출력 없이)Exception Throw한다.
                        RETURN NULL;

END;