더북(TheBook)

SAVEPOINT

보통 ROLLBACK을 명시하면 INSERT, DELETE, UPDATE, MERGE 작업 전체가 취소되는데 전체가 아닌 특정 부분에서 트랜잭션을 취소시킬 수 있다. 이렇게 하려면 취소하려는 지점을 명시한 뒤, 그 지점까지 작업을 취소하는 식으로 사용하는데 이 지점을 SAVEPOINT라고 한다. SAVEPOINT를 명시하는 방법은 다음과 같다.

    SAVEPOINT 세이브포인트명;

그리고 나서, ROLLBACK TO 세이브포인트명;을 실행하면 해당 지점까지 처리한 작업이 취소된다. 예제를 통해 사용법을 알아 보자. 먼저 월별, 국가별 매출총액을 가진 ch10_country_month_sales 테이블을 만들자.

입력

    CREATE TABLE ch10_country_month_sales (
                   sales_month   VARCHAR2(8),
                   country_name  VARCHAR2(40),
                   sales_amt     NUMBER,
                   PRIMARY KEY (sales_month, country_name) );

결과

    table CH10_COUNTRY_MONTH_SALES이(가) 생성되었습니다.

iud_ch10_sales_proc 프로시저를 수정하는데, 이번에는 매개변수로 국가명을 하나 더 받아서 ch10_sales 테이블에 넣고, 다시 ch10_country_month_sales 테이블에 매출월과 국가, 그리고 총 매출액을 넣도록 수정해 보자.

입력

    CREATE OR REPLACE PROCEDURE iud_ch10_sales_proc
      ( p_sales_monthch10_sales.sales_month%TYPE,
        p_country_namech10_sales.country_name%TYPE )
    IS

    BEGIN

      --기존 데이터 삭제
      DELETE ch10_sales
      WHERE sales_month  = p_sales_month
        AND country_name = p_country_name;

      -- 신규로 월, 국가를 매개변수로 받아 INSERT
      -- DELETE를 수행하므로 PRIMARY KEY 중복이 발생치 않음
      INSERT INTO ch10_sales (sales_month, country_name, prod_category, channel_desc, sales_amt)
      SELECT A.SALES_MONTH,
             C.COUNTRY_NAME,
             D.PROD_CATEGORY,
             E.CHANNEL_DESC,
             SUM(A.AMOUNT_SOLD)
       FROM SALES A, CUSTOMERS B, COUNTRIES C, PRODUCTS D, CHANNELS E
      WHERE A.SALES_MONTH  = p_sales_month
        AND C.COUNTRY_NAME = p_country_name
        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_NAME,
               D.PROD_CATEGORY,
               E.CHANNEL_DESC;

      -- SAVEPOINT 확인을 위한 UPDATE
      -- 현재시간에서 초를 가져와 숫자로 변환한 후 * 10 (매번 초는 달라지므로 성공적으로 실행 시 이 값은 매번 달라짐)
      UPDATE ch10_sales
         SET sales_amt = 10 * to_number(to_char(sysdate, 'ss'))
       WHERE sales_month  = p_sales_month
         AND country_name = p_country_name;


     -- SAVEPOINT 지정
     SAVEPOINT mysavepoint;

      -- ch10_country_month_sales 테이블에 INSERT
      -- 중복 입력 시 PRIMARY KEY 중복됨
      INSERT INTO ch10_country_month_sales
           SELECT sales_month, country_name, SUM(sales_amt)
             FROM ch10_sales
            WHERE sales_month  = p_sales_month
              AND country_name = p_country_name
            GROUP BY sales_month, country_name;

    EXCEPTION WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE(SQLERRM);
      ROLLBACK TO mysavepoint; -- SAVEPOINT 까지만 ROLLBACK

      COMMIT;   -- SAVEPOINT 이전까지는 COMMIT
    END;

결과

    PROCEDURE IUD_CH10_SALES_PROC이(가) 컴파일되었습니다.
신간 소식 구독하기
뉴스레터에 가입하시고 이메일로 신간 소식을 받아 보세요.