这篇文章主要讲解了“Oracle PL/SQL中EXCEPTION的用法”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“Oracle PL/SQL中EXCEPTION的用法”吧！

1.自定義EXCEPTION

DECLARE

past_due EXCEPTION;

acct_num NUMBER := 2;

BEGIN

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

past_dueEXCEPTION; --this declaration prevails

acct_numNUMBER :=3;

due_dateDATE := 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('HandlingPAST_DUE exception.'||acct_num);

WHEN OTHERS THEN

DBMS_OUTPUT.PUT_LINE('Could notrecognize 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('HandlingPAST_DUE exception.'||acct_num);

WHEN OTHERS THEN

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

END;

DECLARE

out_of_stock EXCEPTION;

number_on_hand NUMBER := 0;

BEGIN

IFnumber_on_hand < 1 THEN

RAISEout_of_stock; -- raisean exception that we defined

END IF;

EXCEPTION

WHENout_of_stock THEN

-- handle the error

DBMS_OUTPUT.PUT_LINE('Encounteredout-of-stock error.');

END;

2.使用oracle自帶的error返回

DECLARE

acct_type INTEGER := 7;

BEGIN

IF acct_typeNOT IN (1, 2, 3) THEN

RAISE INVALID_NUMBER; --raise predefined exception

END IF;

EXCEPTION

WHEN INVALID_NUMBER THEN

DBMS_OUTPUT.PUT_LINE('HANDLINGINVALID INPUT BY ROLLING BACK.');

ROLLBACK;

END;

3.Retrieving the Error Code andError 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('Errorcode ' || v_code || ': ' || v_errm);

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

-- AUTONOMOUS_TRANSACTION, to insert informationabout errors.

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

commit;

END;

4. raise_application_error

DECLARE

num_tablesNUMBER;

BEGIN

SELECT COUNT(*) INTOnum_tables FROM USER_TABLES;

IFnum_tables < 1000 THEN

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

Notethat you do not need to qualify raise_application_error with

DBMS_STANDARD */

raise_application_error(-20101, 'Expectingat least 1000 tables');

ELSE

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

END IF;

END;

5.指定PRAGMA EXCEPTION_INIT(COMPILATION_ERROR,ERROR_CODE)

CREATE OR REPLACE PROCEDURESFIS1.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 thesesometimes happen due to

-- dependencies between views AND procedures

--

EXCEPTION

WHENCOMPILATION_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

WHENEMP_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的用法”的内容了，经过本文的学习后，相信大家对Oracle PL/SQL中EXCEPTION的用法这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！