Oracle PL/SQL 프로시저 [트랜잭션]

트랜잭션은 '거래' 라는 뜻으로 우리가 은행에서 입금과 출금을 하는 그 거래를 말한다. 일반 적인 프로그래밍 언어나 오라클에서 말하는 트랜잭션도 이 개념에서 차용한것이다.

A란 은행에서 출금을 하여 B란 은행계죄에서는 돈이 빠져 나갔는데, 중간에 오류가 생겨서 B은행 계좌에 입금되지 않았다면 그렇다면 이 문제를 어덯게 처리 하면될까?

 

해결책은 거래 가 성공적으로 모두 끝난 후에야 이를 완전한 거래로 승인하고, 거래 도중 뭔가 오류가 발생햇을때는 이 거래를 아에 처음부터 없었던 거래로 되돌리는 것이다. 이렇게 하면 거래 도중에 발생하 오류에 대해 즉시 처리하지 못할지언정 최소한 금액손실은 발생하지 않는다. 거래 당사자 입장에서도 좀 귀찮기는 하지만 금액 손실이 발생하느니 다시 거래하는 것이 낫다. 이렇게 거래의 안정성을 확보하는 방법이 트랜잭션이다. 

 

데이터 베이스도 마찬가지다. 특정 테이블에서 데이터를 읽어 조작후 다른 테이블에 데이터를 입력하거나 갱신,삭제하는데 처리 도중 오류가 발생하면 모든 작업을 원상태로 되돌리고, 처리 과정이 모두 성공했을 때만 최종적으로 데이터베이스에 반영하는 것이 바로 트랜잭션 처리다. 

 

COMMIT 과 ROLLBACK

 

거래가 모두 성고적으로 마쳤을때, " 이 거래는 성공했으니 반영해도 된다," 라고 확인하는 역할을 하는것이 바로 COMMIT문이다. 따라서  

COMMIT문을 실행하지 않으면   INSERT,UPDATE,DELETE,MERGE한 결과가 최종적으로 테이블에 반영되지 않는다.

 

COMMIT[WORK];

WOR는 가독성을 향상시키는 목적으로 사용하는데 보통은 생략할 때가 많다. 예제를 통해 트랜잭션 처리를 해볼 텐데, 먼저 다음과 같이 샘플 테이블을 만들어 보자.

 

CREATE TABLE SALES(
    SALES_MONTH VARCHAR2(8),
    COUNTRY_NAME VARCHAR2(40),
    PROD_CATEGORY VARCHAR2(50),
    CHANNEL_DESC VARCHAR2(20),
    SALES_AMT NUMBER);
    

이제 SALES,PRODUCTS,CUSTOMERS,CHANNELS,COUNTRIES 테이블을 조인해서 입력월을 매개변수로 받아 해당 월에 대한 국가, 제품 카테고리, 채널 유형별 매출금액을 구해 SALES 테이블에 입력하는 프로시저를 만들어보자.

 

CREATE OR REPLACE PROCEDURE CH10_SALES (
    P_SALES_MONTH IN SALES.SALES_MONTH %TYPE)

IS
BEGIN
    INSERT INTO SALES (SALES_MONTH, COUNTRY_NAME,PROD_CATEGORY,CHANNEL_DESC,SALES_AMT)

    SELECT A.SALES_MONTH, B.COUNTRY_NAME,C.PROD_CATEGORY,E.CHANNEL_DESC,SUM(S.AMOINT_SOLD)
    FROM SALES A,CUSTOMER B,COUNTRIES C,PRODUCTS D,CHANNELS E
    WHERE A.SALES_MONTH = P_SALES_MONTH
    AND A.CUST_ID = B.CUST_ID
    AND B.COUNTRY_ID = C.COUNTRY_ID
    AND A.PROD_ID = D.PROD_ID
    AND A.CHANNEL_ID = E.CHANNEL_ID
    GROUP BY A.SALES_MONTH,
            C.COUNTRY_ID,
            D.PROD_CATEGORY,
            E.CHANNEL_DESC;
            
END CH10_SALES;

프로시저 실행후 첫 번째 쿼리에서 확인했을 때는 151건이었으나 다른 창을 띄우고 확인하니 0건이다. 그이유는 COMMIT 문을 실행하지 않았기 때문인데, 같은 세션에서 확인한 첫 번째 쿼리에서는 결과 값이 151건으로 나왔지만 DB에 최종적으로 반영이 안 된 상태여서 다른 편집기나 창에서 확인하면 0건이 나오는 것이다. 

 

 

 

ROLLBACK

 

ROLLBACK 은  COMMIT과는 정반대로 DB에 가해진 변경사항을 취소시키는 기능을 하는데, 사용 법은 다음과 같다.

ROLLBACK [WORK] [TO[SAVEPOINT] 세이브포인트명];

그럼 COMMIT 대신 ROLLBACK을 입력하고 프로시저를 실행해보자.

CREATE OR REPLACE PROCEDURE CH10_SALES (
    P_SALES_MONTH IN SALES.SALES_MONTH %TYPE)

IS
BEGIN
    INSERT INTO SALES (SALES_MONTH, COUNTRY_NAME,PROD_CATEGORY,CHANNEL_DESC,SALES_AMT)

    SELECT A.SALES_MONTH, B.COUNTRY_NAME,C.PROD_CATEGORY,E.CHANNEL_DESC,SUM(S.AMOINT_SOLD)
    FROM SALES A,CUSTOMER B,COUNTRIES C,PRODUCTS D,CHANNELS E
    WHERE A.SALES_MONTH = P_SALES_MONTH
    AND A.CUST_ID = B.CUST_ID
    AND B.COUNTRY_ID = C.COUNTRY_ID
    AND A.PROD_ID = D.PROD_ID
    AND A.CHANNEL_ID = E.CHANNEL_ID
    GROUP BY A.SALES_MONTH,
            C.COUNTRY_ID,
            D.PROD_CATEGORY,
            E.CHANNEL_DESC;
           -- COMMIT;
           ROLLBACK;
END CH10_SALES;

작업한 내역을 모두 취소시켰기 때문에 같은 세션에서 조회하든 다른 세션에서 조회하든 테이블에는 전혀 반영되지 않는다.

COMMIT은 작업이 성공했을때, ROLLBACK 은 작업이 실패했을 때 처리하므로 일반적으로 PL/SQL 코드를 작성할때 디폴트로 실행부 맨 하단에 COMMI을 명시한다. ROLLBACK은 앞절에서 학습했던 예외처리와 함께 사용되어 특정 예외가 발생했을 때 예외처리 구문에 ROLLBACK 을 명시하는 식으로 트랜잭션을 처리한다. 

 

CREATE OR REPLACE PROCEDURE CH10_SALES (
    P_SALES_MONTH IN SALES.SALES_MONTH %TYPE)

IS
BEGIN
    INSERT INTO SALES (SALES_MONTH, COUNTRY_NAME,PROD_CATEGORY,CHANNEL_DESC,SALES_AMT)

    SELECT A.SALES_MONTH, B.COUNTRY_NAME,C.PROD_CATEGORY,E.CHANNEL_DESC,SUM(S.AMOINT_SOLD)
    FROM SALES A,CUSTOMER B,COUNTRIES C,PRODUCTS D,CHANNELS E
    WHERE A.SALES_MONTH = P_SALES_MONTH
    AND A.CUST_ID = B.CUST_ID
    AND B.COUNTRY_ID = C.COUNTRY_ID
    AND A.PROD_ID = D.PROD_ID
    AND A.CHANNEL_ID = E.CHANNEL_ID
    GROUP BY A.SALES_MONTH,
            C.COUNTRY_ID,
            D.PROD_CATEGORY,
            E.CHANNEL_DESC;
        EXCEPTION
            WHEN OTHERS THEN
            DBMS_OUTPUT.PUT_LINE(SQLERRM);
           ROLLBACK;
END CH10_SALES;