-- 1. Procedure가 또 다른 Procedure를 중첩해서 호출할때.
------------------------------------------------------------------------------

CREATE TABLE TEST1
( ID NUMBER NOT NULL
, NAME VARCHAR2(10));

DELETE FROM TEST1;
COMMIT;

CREATE OR REPLACE PROCEDURE PROC01(P_NUM IN NUMBER)
IS
    V_ID TEST1.ID%TYPE;
BEGIN
    SELECT NVL(MAX(ID), 0) + 1 INTO V_ID FROM TEST1;
    INSERT INTO TEST1 VALUES (V_ID, 'PROC01 : ' || V_ID);

    DBMS_OUTPUT.PUT_LINE('PROC01 BEFORE 1');
    PROC02(P_NUM);
    DBMS_OUTPUT.PUT_LINE('PROC01 AFTER  2');
    COMMIT;                                         -- 1. 이 구문이 PROC02 호출 이전에 있다면 PROC02에서 ROLLBACK이 발생해도 PROC01는 영향받지 않고 COMMIT된 정보 유지된다.
--  ROLLBACK;                                       -- 2. PROC02 COMMIT되고 여기에 PROC01에서 ROLLBACK처리해도 두번의 Insert 구문이 모두 COMMIT되어 있다. 
                                                    -- 3. 중첩호출되는 PROCEDURE에서 COMMIT이 되면 MAIN PROCEDURE에서 ROLLBACK해도 중첩호출된 PROCEDURE에서 처리된 값은 COMMIT유지된다.
                                                    -- 4. 중첩호출되는 PROCEDURE에서 COMMIT이나 ROLLBACK이 명시되지 않으면 MAIN PROCEDURE에서의 명시한 COMMIT/ROLLBACK으로 일괄 적용된다. 

                                                    -- 결국, PROCEDURE를 Application에서 호출하고 그 Transcation 결과가 All Or Nothing 되도록 하기위해서는 
                                                    -- 호출되는 개별 PROCEDURE에서 COMMIT/ROLLBACK 명시하지 않고 Application에서 COMMIT/ROLLBACK 명시하면 된다.
    DBMS_OUTPUT.PUT_LINE('PROC01 COMMIT 3');
     
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            DBMS_OUTPUT.PUT_LINE('PROC01 ERROR MESSAGE : ' || SQLERRM);
            RAISE_APPLICATION_ERROR(-20000, 'PROC01 ERROR MESSAGE : ' || SQLERRM);
END;
/

CREATE OR REPLACE PROCEDURE PROC02(P_NUM IN NUMBER)
IS
    V_ID TEST1.ID%TYPE;
    V_NUM NUMBER;
BEGIN
    SELECT NVL(MAX(ID), 0) + 1 INTO V_ID FROM TEST1;
    INSERT INTO TEST1 VALUES (V_ID, 'PROC02 : ' || V_ID);

    DBMS_OUTPUT.PUT_LINE(' PROC02 BEFORE 1');
    V_NUM := 1 / P_NUM;
    DBMS_OUTPUT.PUT_LINE(' PROC02 AFTER  2');
    COMMIT;
    DBMS_OUTPUT.PUT_LINE(' PROC02 COMMIT 3');
     
    EXCEPTION
        WHEN OTHERS THEN
            ROLLBACK;
            DBMS_OUTPUT.PUT_LINE(' PROC02 ERROR MESSAGE : ' || SQLERRM);
            RAISE_APPLICATION_ERROR(-20000, ' PROC02 ERROR MESSAGE : ' || SQLERRM);
END;
/


EXEC PROC01(1); -- 정상입력

EXEC PROC01(0); -- 오류발생하여 모두 ROLLBACK 됨

오라클의 프로시져(Procedure) 트랜젝션 특성1 : https://blog.daonelab.com/post/24/135/