Any error is called an exception. When an exception occurs, the execution unit must take control of the delivery. This is a process in which the meat Steamed Stuffed Bun hits the dog.
PL/SQL engines treat system exceptions, user exceptions, or application exceptions equally.
Is the exception only an exception? For some exceptions, such as no_data_found, we prefer to treat it as a branch of logic.
The following describes in detail how to define, throw, and handle exceptions.
(I) Definition exception
Illegal anonymous users are still legal citizens, but they are major contributors to poor readability and high maintenance costs.
Exception!
Syntax:
Must be in the Declaration Section
Exception_name exception;
Usage:
① Raise Statement of Execution Unit
Raise prediction_name;
② When statement of the Exception Handling Unit
When exception_name then;
However, when we have to deal with anonymous exceptions, for example:
① Mysterious superior in the world-an anonymous system exception
② Application exception thrown by raise_application_error
In this case, the "exception_init", called the compiling command, can help you name an anonymous exception.
Syntax:
Must be in the Declaration Section
Exception_name exception; -- the exception has been named in the standard section of the same block, external block, or package.
Pragma exception_init (exception_name, error_number );
Note:
-- Error_number Constraints
●-1403 is not available (it is one of the error numbers of no_data_found)
● Do not use any positive number other than 0 or 100
● The negative value of <-1000000 is unavailable.
-- Best practices
We recommend that you set prediction_init to a package, for example:
Create or replace package pkg_think
Is
Invalid_date exception;
Pragma exception_init (invalid_date,-110 );
End pkg_think;
We can intercept these exceptions in any program, for example:
When pkg_think.invalid_date then...
For the scope of the exception, it is necessary for think to nagging twice
We know that the program is transparent in external code and it is a "black box"
For example, we can throw a B exception in process a, but cannot throw a B exception from program c that calls process.
(Ii) throw an exception
There are two ways to manually throw an exception:
Revoke raise statement
▼ Raise_application_error Process
First look at raise
Syntax:
Raise prediction_name; or
Raise package_name.exception_name; or
Raise;
Note:
Method 1: Can be used to throw a custom exception in the current block or the external block that contains the current block
You can also throw a pre-defined system exception for the standard package.
Method 3: only the when statement in the exception handling unit can be used for Exception Propagation.
Raise_application_error
Compared with raise, this Jun has the advantage of adding annotations to exceptions.
Syntax:
Raise_application_error (error_number, error_comments );
Here the error_number can only be between-20999 ~ -20000 errors 1000
(Iii) Handling exceptions
There are two terminologies:
▲Exception Handling Unit: The exception keyword indicates the start of the exception handling unit.
▲Exception handling handle: each independent when... then... is a handle.
An exception handling unit can have multiple handling handles, for example:
Exception
/* Handle 1 */
When no_data_found
Then...
/* Handle 2 */
When payment_overdue
Then...
/* Handle 3 */
When others
Then...
End;
The when statement can only capture exceptions based on the Exception name, but cannot catch exceptions based on the error number.
The when others statement is optional and must be the last processing handle of the exception handling unit. Without this statement, any unprocessed exceptions are immediately propagated to the outer layer.
Contains the when others statement in the Exception Handling Unit to capture all other unprocessed exceptions.
The combination of when others and sqlcode can replace the prediction_init function.
However, you should carefully use when others because it is easy to "Swallow" exceptions and conceal these exceptions from the outer layer and users.
Of course, we use the OR operator to include multiple exceptions in a Single Handle, regardless of whether the exception comes from the system or application.
However, the and operator cannot be used because only one exception can be thrown at any time.
Before further discussing the exception handling details, let's look at two built-in exception handling functions.
▲Sqlcode: returns the last error code thrown out in the Code.
▲Sqlerrm: returns the error message corresponding to an error code.
There are two actions that affect exceptions:
-- Throw: Execution Unit → Exception Handling Unit (usually at the same layer)
-- Propagation: from inner layer block → outer layer block
The error code for all programmer-defined exceptions is 1, and the error message is "user-defined exception"
Unless you use exception_init to specify different error numbers for this declared exception, and raise_application_error to specify different error messages for it
If you use a local definition exception, you should provide an exception handle for the error name.
Otherwise, unhandled exceptions will be transmitted to the outer blocks, and the outer blocks will be invisible and inactive.
Think mentioned in the beginning that once the control is handed over, it means that there is no chance for the emperor to take turns and arrive at my house next year.
So how to "Roll Back?
The exception thrown by the execution unit is always handled in the current block-if a matched handle exists
You can add a begin to any statement, and then add an exception unit and end statement to build a "virtual block"
In this way, you can control the failure Range Caused by exceptions by building anonymous blocks in the code.
In addition, it is recommended that all codes to be isolated be put in a separate process or function.
In this way, you can hide the begin-exception-end statement from the main program line.
Make the program easier to read, understand, maintain, and reuse in multiple Environments
