ตอนที่ 13 : การใช้ Transaction บน Oracle Stored Procedure (Oracle : Stored Procedure)

BEGIN
  
 // Statement 1

 // Statement 2

// Statement 3

  -- Commit
  COMMIT;

  -- Exception  
  EXCEPTION
    WHEN OTHERS THEN
      BEGIN
        ROLLBACK;
      END;
END;

CREATE OR REPLACE PROCEDURE INSERT_CUSTOMER
(
  pCustomerID IN VARCHAR2,
  pName IN VARCHAR2,
  pEmail IN VARCHAR2,
  pCountryCode IN VARCHAR2,
  pBudget IN DECIMAL,
  pUsed IN DECIMAL
)
AS
BEGIN
  
  -- Create save point
  SAVEPOINT save_point;
  
  -- Delete Customer
  DELETE FROM CUSTOMER WHERE COUNTRY_CODE = 'US';
  
  -- Insert Statement 1
  INSERT INTO CUSTOMER (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED)
    VALUES (pCustomerID, pName, pEmail, pCountryCode, pBudget, pUsed);
    
  -- Insert Statement 2
  INSERT INTO CUSTOMER (CUSTOMER_ID, NAME, EMAIL, COUNTRY_CODE, BUDGET, USED)
    VALUES (pCustomerID, pName, pEmail, pCountryCode, pBudget, pUsed);  
    
  -- Commit
  COMMIT;
    
  -- Exception  
  EXCEPTION
    WHEN OTHERS THEN
      BEGIN
        DBMS_OUTPUT.PUT_LINE('Error : ' || SQLERRM); 
        ROLLBACK TO save_point;
      END;
END;

EXEC INSERT_CUSTOMER('C005','Fun Wipa','[email protected]','TH','100000','0');