ตอนที่ 12 : การใช้ Try Catch Exception ดักจับข้อผิดลาด (Oracle : Stored Procedure)

BEGIN

EXCEPTION
  WHEN <named_exception> THEN
    -- handle identified exception
  WHEN <named_exception> THEN
    -- handle identified exception
  WHEN OTHERS THEN
    -- handle any exceptions not previously handled
END;

CREATE OR REPLACE PROCEDURE MY_STORED_PROCEDURE
  (pAmount1 IN DECIMAL, pAmount2 IN DECIMAL)
AS
BEGIN
  DECLARE
    dResult DECIMAL(18,2);
    BEGIN
      dResult := pAmount1 / pAmount2;
      DBMS_OUTPUT.PUT_LINE('Result : ' || dResult);    
      EXCEPTION
        WHEN ZERO_DIVIDE  THEN
          DBMS_OUTPUT.PUT_LINE('Error : Division By Zero'); 
        WHEN OTHERS THEN
          BEGIN
            DBMS_OUTPUT.PUT_LINE('Error Something'); 
            -- Other Statement
          END;
    END;   
END;

EXEC MY_STORED_PROCEDURE('100','0');

EXEC MY_STORED_PROCEDURE('100','10');

EXEC MY_STORED_PROCEDURE('10000000000000000000','10');

CREATE OR REPLACE PROCEDURE MY_STORED_PROCEDURE
  (pAmount1 IN DECIMAL, pAmount2 IN DECIMAL)
AS
BEGIN
  DECLARE
    dResult DECIMAL(18,2);
    BEGIN
      dResult := pAmount1 / pAmount2;
      DBMS_OUTPUT.PUT_LINE('Result : ' || dResult);    
      EXCEPTION
        WHEN ZERO_DIVIDE  THEN
          DBMS_OUTPUT.PUT_LINE('Error : Division By Zero'); 
        WHEN OTHERS THEN
          BEGIN
            DBMS_OUTPUT.PUT_LINE('Error Something'); 
            -- Other Statement
          END;
    END;   
END;

CREATE OR REPLACE PROCEDURE MY_STORED_PROCEDURE
AS
BEGIN
    DECLARE
    SQLQuery VARCHAR2(1000);
    BEGIN
      SQLQuery := 'INSERT INTO MEMBER (USERNAME,PASSWORD) VALUES ("A","B")';
      EXECUTE IMMEDIATE SQLQuery; 
      
      EXCEPTION 
        WHEN NO_DATA_FOUND  THEN
          DBMS_OUTPUT.PUT_LINE('Error : No Data!'); 
        WHEN OTHERS THEN
          BEGIN
            DBMS_OUTPUT.PUT_LINE('Error Query : ' || SQLQuery); 
            DBMS_OUTPUT.PUT_LINE('Error Description : ' || SQLERRM); 
            -- Other Statement
          END; 
          
    END;
END;

EXEC MY_STORED_PROCEDURE();