Oracle PL/SQL中EXCEPTION用法

DECLARE

   past_due EXCEPTION;

   acct_num NUMBER := 2;

BEGIN

   DECLARE  ---------- sub-block begins

     past_due EXCEPTION;  -- this declaration prevails

     acct_num NUMBER :=3;

     due_date DATE := SYSDATE - 1;

     todays_date DATE := SYSDATE;

   BEGIN

      IF due_date < todays_date THEN

         RAISE past_due;  -- this is not handled

      END IF;

   EXCEPTION

      WHEN past_due THEN  -- does not handle raised EXCEPTION

        DBMS_OUTPUT.PUT_LINE('Handling PAST_DUE exception.'||acct_num);

      WHEN OTHERS THEN

        DBMS_OUTPUT.PUT_LINE('Could not recognize PAST_DUE_EXCEPTION in this scope.'||acct_num);  

   END;  ------------- sub-block ends      

EXCEPTION

  WHEN past_due THEN  -- does not handle raised exception

    DBMS_OUTPUT.PUT_LINE('Handling PAST_DUE exception.'||acct_num);

  WHEN OTHERS THEN

    DBMS_OUTPUT.PUT_LINE('Could not recognize PAST_DUE_EXCEPTION in this scope.'||acct_num);

END;

DECLARE

   out_of_stock   EXCEPTION;

   number_on_hand NUMBER := 0;

BEGIN

   IF number_on_hand < 1 THEN

      RAISE out_of_stock; -- raise an exception that we defined

   END IF;

EXCEPTION

   WHEN out_of_stock THEN

      -- handle the error

      DBMS_OUTPUT.PUT_LINE('Encountered out-of-stock error.');

END;

DECLARE

   acct_type INTEGER := 7;

BEGIN

   IF acct_type NOT IN (1, 2, 3) THEN

      RAISE INVALID_NUMBER;  -- raise predefined exception

   END IF;

EXCEPTION

   WHEN INVALID_NUMBER THEN

      DBMS_OUTPUT.PUT_LINE('HANDLING INVALID INPUT BY ROLLING BACK.');

      ROLLBACK;

END;

CREATE TABLE errors (code NUMBER, message VARCHAR2(64), happened TIMESTAMP);

DECLARE

   names employee.name%TYPE;

   v_code NUMBER;

   v_errm VARCHAR2(64);

BEGIN

   SELECT name INTO names FROM employee WHERE id = -1;

   EXCEPTION

      WHEN OTHERS THEN

         v_code := SQLCODE;

         v_errm := SUBSTR(SQLERRM, 1 , 64);

         DBMS_OUTPUT.PUT_LINE('Error code ' || v_code || ': ' || v_errm);

-- Normally we would call another procedure, declared with PRAGMA

-- AUTONOMOUS_TRANSACTION, to insert information about errors.

         INSERT INTO errors VALUES (v_code, v_errm, SYSTIMESTAMP);

         commit;

END;

DECLARE

   num_tables NUMBER;

BEGIN

   SELECT COUNT(*) INTO num_tables FROM USER_TABLES;

   IF num_tables < 1000 THEN

      /* Issue your own error code (ORA-20101) with your own error message.

         Note that you do not need to qualify raise_application_error with

         DBMS_STANDARD */

      raise_application_error(-20101, 'Expecting at least 1000 tables');

   ELSE

      NULL; -- Do the rest of the processing (for the non-error case).

   END IF;

END;

CREATE OR REPLACE PROCEDURE SFIS1.execute_immediate( p_sql_text VARCHAR2 ) IS

   COMPILATION_ERROR EXCEPTION;

   PRAGMA EXCEPTION_INIT(COMPILATION_ERROR,-24344);

   l_cursor INTEGER DEFAULT 0;

   rc       INTEGER DEFAULT 0;

   stmt     VARCHAR2(1000);

BEGIN

   l_cursor := DBMS_SQL.OPEN_CURSOR;

   DBMS_SQL.PARSE(l_cursor, p_sql_text, DBMS_SQL.NATIVE);

   rc := DBMS_SQL.EXECUTE(l_cursor);

   DBMS_SQL.CLOSE_CURSOR(l_cursor);

--

-- Ignore compilation errors because these sometimes happen due to

-- dependencies between views AND procedures

--

EXCEPTION

WHEN COMPILATION_ERROR THEN

    DBMS_SQL.CLOSE_CURSOR(l_cursor);

WHEN OTHERS THEN

    BEGIN

      DBMS_SQL.CLOSE_CURSOR(l_cursor);

      raise_application_error(-20101,sqlerrm || '  when executing ''' || p_sql_text || '''   ');

    END;

END;

CREATE UNIQUE INDEX GC.EMP_NO_ ON GC.EMP(EMP_NAME)

 

DECLARE

   EMP_NAME_UNIQUE EXCEPTION;

   PRAGMA EXCEPTION_INIT(EMP_NAME_UNIQUE, -00001);

BEGIN

    INSERT INTO GC.EMP SELECT * FROM GC.EMP;

EXCEPTION

   WHEN EMP_NAME_UNIQUE THEN

      DBMS_OUTPUT.PUT_LINE('違反一致性');

   WHEN OTHERS THEN

      DBMS_OUTPUT.PUT_LINE(SQLCODE||'---'||SQLERRM);

END;