Shulou(Shulou.com)05/31 Report--

This article mainly explains "the usage of EXCEPTION in Oracle PL/SQL". The content of the explanation is simple and clear, and it is easy to learn and understand. Please follow the editor's train of thought to study and learn "the usage of EXCEPTION in Oracle PL/SQL".

1. Custom 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

two。 Return using error customized by oracle

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. Specify 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 (- 2011Magi 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 ('anti-consistency')

WHEN OTHERS THEN

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

END

6. DBMSroomUTILITY.formatting errorworthy backtracedDBMSroomUTILITY.formatting erroroperations stack returns error rows and errors

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

Thank you for your reading, the above is the content of "the usage of EXCEPTION in Oracle PL/SQL". After the study of this article, I believe you have a deeper understanding of the use of EXCEPTION in Oracle PL/SQL, and the specific use needs to be verified in practice. Here is, the editor will push for you more related knowledge points of the article, welcome to follow!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.