Introduction to Pl/sql exception handling

1. Definition

Exception: The identifier produced during the execution of the Pl/sql, and a warning or error in Pl/sql is called an exception.

Errors usually fall into two categories:

Compile-time errors-typically syntax errors, found by the Pl/sql engine and reported to the user, and the program itself cannot handle because it is not yet running.

Run-time Error-An error that the program throws at run time, such as not returning a result and returning multiple results (only one row of results can be returned)

Exception handling---usually refers to handling run-time errors.

An important reason for throwing an exception is that an error occurred while processing the data. Statistics show that SELECT statements, DML statements, and cursor action statements are more likely to throw exceptions.

The main purpose of writing Pl/sql blocks is to process data, and pl/sql blocks are logically separate from data, and programmers simply cannot anticipate changes in data.

For example, to query 10 of employees in the department, programmers have no idea whether there are any employees in the department, or if there are multiple employees.

So when writing a program, programmers should consider all kinds of possible exceptions, write these exception processing code in the program, such programs can withstand all kinds of wrong test.

2. Predefined exceptions

Predefined Exceptions: Oracle defines some common bugs as named exceptions, which are predefined exceptions. "has exception code, exception name, exception message"

Oracle has a number of predefined exceptions that do not need to be defined when processing, and only the appropriate exception handlers need to be written.

When an error occurs in the Pl/sql block execution, the database server automatically throws the corresponding exception and executes the written exception handler.

Oracle Exceptions with predefined descriptions

ORACLE Error Exception Name description

ORA-0001 Dup_val_on_index tries to break a uniqueness limit

ORA-0051 timeout-on-resource Timeout while waiting for resource

ORA-0061 Transaction-backed-out is undone because a deadlock transaction occurred.

ORA-1001 Invalid-cursor attempted to use an invalid cursor

ORA-1012 not-logged-on not connected to Oracle

ORA-1017 login-denied Invalid username/password

ORA-1403 no_data_found No data found

ORA-1422 Too-many-rows SELECT INTO returns multiple rows

ORA-1476 Zero-divide tried to be 0 apart

ORA-1722 Invalid-number Convert a number failed

ORA-6500 Storage-error Internal error caused by insufficient memory

ORA-6501 Program-error Internal Error

ORA-6502 value-error conversion or truncation error

ORA-6511 Cursor-already-open attempted to open an existing cursor

ORA-6530 Access-into-null attempted to assign a value to a property of a null object