Oracle PL/SQL中EXCEPTION的用法

這篇文章主要講解了“Oracle PL/SQL中EXCEPTION的用法”，文中的講解內(nèi)容簡單清晰，易于學(xué)習(xí)與理解，下面請大家跟著小編的思路慢慢深入，一起來研究和學(xué)習(xí)“Oracle PL/SQL中EXCEPTION的用法”吧！

1.自定義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;

2.使用oracle自帶的error返回

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;

3.Retrieving the Error Code and Error Message: SQLCODE and SQLERRM

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;

4. raise_application_error

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;

5.指定PRAGMA EXCEPTION_INIT(COMPILATION_ERROR,ERROR_CODE)

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;

6.DBMS_UTILITY.format_error_backtrace,DBMS_UTILITY.format_error_stack 返回錯誤行和錯誤

DECLARE
   V_TABLE_NAME   VARCHAR2 (500);
BEGIN
   SELECT TABLE_NAME INTO V_TABLE_NAME FROM DBA_TABLES;
EXCEPTION
   WHEN OTHERS
   THEN
      DBMS_OUTPUT.put_line (DBMS_UTILITY.format_error_backtrace|| '--'|| DBMS_UTILITY.format_error_stack);
      --DBMS_OUTPUT.put_line ('error line:' || DBMS_UTILITY.FORMAT_ERROR_BACKTRACE () || ' SQLCODE:'|| SQLCODE|| '  SQLERRM:'|| SQLERRM);
END;

感謝各位的閱讀，以上就是“Oracle PL/SQL中EXCEPTION的用法”的內(nèi)容了，經(jīng)過本文的學(xué)習(xí)后，相信大家對Oracle PL/SQL中EXCEPTION的用法這一問題有了更深刻的體會，具體使用情況還需要大家實踐驗證。這里是億速云，小編將為大家推送更多相關(guān)知識點的文章，歡迎關(guān)注！