Pl/sql Exception Handling

When designing a Pl/sql program, this or that error occurs frequently, and exception handling is the segment of the program that handles the error, and the exception handling in Oracle 9i is divided into system predefined exception handling and custom exception handling.

System predefined exception handling

System predefined exception handling is a procedure to deal with the problems that occur during the compilation and execution of Pl/sql programs. The following code is the correct code and can be executed successfully in "Sqlplus Worksheet".
―――――――――――――――――――――――――――――――――――――
Set Serveroutput on
Declare
Tempno integer:=90;
Begin
tempno:=tempno+1;
End
―――――――――――――――――――――――――――――――――――――
"Matching program Location": 9th Chapter \ Correctplsql.sql.
The following code is an error code, and the results of the execution in "Sqlplus worksheet" are shown in Figure 9.56.

"Matching program Location": 9th Chapter \ Wrongplsql.sql.
Because of the error in the code, the system's predefined exception handling is activated and the following message is drawn.

Oracle 9i provides a lot of exception handling, and readers can try to modify a program that works correctly and execute a modified program to discover which exception handlers are invoked, and here's how to customize exception handling.

Custom Exception Handling

1. Define Exception handling
The syntax for defining exception handling is as follows:
Declare
Exception name exception;
2. Trigger Exception Handling
The syntax for triggering exception handling is as follows:
Raise exception name;
3. Handling Exceptions
After the exception handling is triggered, you can define the exception handling section, which is the following syntax:
Exception
When exception name 1 then
Exception handling statement segment 1;
When exception name 2 Then
Exception handling statement segment 2;
4. Examples
The following Pl/sql program contains the complete process of defining, triggering, and handling exception handling. Define an exception named Salaryerror, find the empno=7566 record in the Scott.emp datasheet, place its value in the variable tempsal, and determine if the Tempsal value is not between 900 and 2600, indicating that the employee's salary is problematic and will activate exception handling. Hint information.
Execute the following pl/sql code in "Sqlplus Worksheet", as shown in Figure 9.57.

"Matching program Location": 9th Chapter \ Exceptiondefine.sql.