oracle pl sql exception,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;