PL/SQL Exception error handling



Exception error Handling
A good program should be able to properly handle all kinds of error situations and recover from them as much as possible. ORACLE provides exception conditions (EXCEPTION) and exception handling (EXCEPTION HANDLER) for error handling

① has three types of exception errors:
1. Pre-defined (predefined) errors
There are approximately 24 pre-defined exception cases for Oracle. The handling of this exception does not have to be defined in the program and is automatically raised by Oracle.

2. Non-pre-defined (predefined) error
That is, other standard Oracle errors. Handling this exception requires the user to define it in the program, which is then automatically raised by Oracle.

3. User-defined (user_define) error
During the execution of the program, it appears that the programmer considers the abnormal situation. Handling this exception requires the user to define it in the program and then explicitly raise it in the program.

The ② exception handling section is generally placed in the second half of the PL/SQL program body, with the following structure:
EXCEPTION
When First_exception Then<code to handle first exception >
When Second_exception Then<code to handle second exception >
When OTHERS and <code to handle OTHERS exception >
END;
Exception handling can be arranged in any order, but others must be placed at the end.

③ pre-defined exception handling
To deal with this anomaly, we simply refer to the exception-handling section of the PL/SQL block, directly reference the corresponding exception case name and complete the corresponding exception error handling.

[pre-defined exception]
Declare

V_sal Employees.salary%type;
Begin
Select Salary into V_sal
From Employees
where employee_id >100;

Dbms_output.put_line (v_sal);

exception
When the too_many_rows then Dbms_output.put_line (' The number of rows in the output is too many ');
End

④ non-pre-defined exception handling
For handling this type of exception, you must first define a non-defined Oracle error. The steps are as follows:
1. Define the exception condition in the definition section of the PL/SQL block:
< abnormal situation > EXCEPTION;

2. Use the pragma exception_init statement to associate its defined anomalies with standard Oracle errors:
PRAGMA Exception_init (< exception;, < error code >);

3. In the Exception handling section of the PL/SQL block, the exception is dealt with accordingly.

[Non-pre-defined exception]
Declare

V_sal Employees.salary%type;
--Declaring an exception
DELETE_MGR_EXCEP exception;
--Associating a custom exception with an Oracle error
PRAGMA Exception_init (delete_mgr_excep,-2292);
Begin
Delete FROM Employees
where employee_id = 100;

Select Salary into V_sal
From Employees
where employee_id >100;

Dbms_output.put_line (v_sal);

exception
When the too_many_rows then Dbms_output.put_line (' The number of rows in the output is too many ');
When Delete_mgr_excep then Dbms_output.put_line (' manager cannot be deleted directly ');
End

⑤ user-defined exception handling
When an error related to an exception error occurs, it is implicitly triggered by the exception error. User-defined exception errors are triggered by explicitly using the Raise statement. When an exception error is thrown, the control moves to the exception block exception error section and executes the error-handling code.
For handling this type of exception, the steps are as follows:
1. Define exception conditions in the definition section of the PL/SQL block:

< abnormal situation > EXCEPTION;
2. RAISE < abnormal conditions >;

3. In the exception handling section of the PL/SQL block, the exception is handled accordingly.

[User Custom Exception]
Declare

V_sal Employees.salary%type;
--Declaring an exception
DELETE_MGR_EXCEP exception;
--Associating a custom exception with an Oracle error
PRAGMA Exception_init (delete_mgr_excep,-2292);

--Declaring an exception
Too_high_sal exception;
Begin

Select Salary into V_sal
From Employees
where employee_id = 100;

If V_sal >
Raise Too_high_sal;
End If;

Delete FROM Employees
where employee_id = 100;

Dbms_output.put_line (v_sal);

exception
When the too_many_rows then Dbms_output.put_line (' The number of rows in the output is too many ');
When Delete_mgr_excep then Dbms_output.put_line (' manager cannot be deleted directly ');
--Handling exceptions
When Too_high_sal and then Dbms_output.put_line (' pay too much ');
End

⑥ using sqlcode in PL/SQL, SQLERRM

SQLCODE return error code number
SQLERRM returns an error message.

EXCEPTION
When OTHERS Then
Dbms_output. Put_Line (sqlcode| | ' ---' | | SQLERRM);

⑦ Exception Program:

1. By select ... into ... Query a person's salary, if not, output "no data found"
Declare
--Define a variable
V_sal Employees.salary%type;
Begin
--Use Select ... into ... Assigning Values to V_sal
Select salary to V_sal from employees where employee_id = 1000;
Dbms_output.put_line (' Salary: ' | | v_sal);
exception
When No_data_found Then
Dbms_output.put_line (' No data found ');
End

or
Declare
 --Define a variable
  v_sal employees.salary%type;
Begin
 --use Select ... into ... Assigns a value of V_sal
  Select salary to v_sal from employees;
  Dbms_output.put_line (' Salary: ' | | v_sal);
Exception
  When No_data_found then
       dbms_output.put_line (' No data found! ');
  When Too_many_rows then
       dbms_output.put_line (' Too much data! ');     
End;

2. Update the salary of the specified employee, if the salary is less than 300, add 100; No_data_found exception, too_many_rows processing.
Declare
V_sal Employees.salary%type;
Begin
Select salary to V_sal from employees where employee_id = 100;

if (V_sal <) Then
Update employees Set salary = salary + where employee_id = 100;
Else
Dbms_output.put_line (' Wages greater than 300 ');
End If;
exception
When No_data_found Then
Dbms_output.put_line (' No data found ');
When Too_many_rows Then
Dbms_output.put_line (' Too many rows of data output ');
End

3. Handling non-pre-defined exception handling: "Violation of full constraint condition"

Declare
--1. Defining exceptions
Temp_exception exception;

--2. Use the Exception_init statement to associate a defined exception with a standard ORACLE error.
PRAGMA Exception_init (temp_exception,-2292);
Begin
Delete FROM employees where employee_id = 100;

exception
--3. Handling Exceptions
When Temp_exception Then
Dbms_output.put_line (' Breach of integrity constraint! ');
End

4. Custom exception: Update the specified employee's salary, increase 100; throws a user-defined exception if the employee does not exist: No_result

Declare
--Custom exceptions
No_result exception;
Begin
Update employees Set salary = salary + where employee_id = 1001;

--Throwing a custom exception using an implicit cursor
If Sql%notfound Then
Raise No_result;
End If;

exception

--Exceptions thrown by handlers
When No_result Then
Dbms_output.put_line (' update failed ');
End

PL/SQL Exception error handling