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
Oracle has a pre-defined exception condition of about 24. The handling of such abnormal conditions. It does not need to be defined in the program and is initiated by Oracle itself.

2. Non-pre-defined (predefined) error
That is, other standard Oracle errors.

The handling of such an exception requires the user to define it in the program, which is then initiated by Oracle itself.

3. User-defined (user_define) error
During the process of running the program, it appears that the programmer feels the abnormal situation. The handling of such abnormal conditions. The user needs to be defined in the program and then explicitly raised 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 a random order, but the others must be placed at the end.

③ pre-defined exception handling
The handling of such anomalies is only required in the exception handling section of the PL/SQL block. directly referencing the corresponding exception case name. and to the completion of the corresponding exception error handling can be.

[Define exceptions in advance]
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.

Procedures such as the following:
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 handled in the corresponding case.

[Non-pre-defined exception]
Declare

V_sal Employees.salary%type;
--Declaring an exception
DELETE_MGR_EXCEP exception;
--Associating your own defined exceptions with Oracle's errors
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. Will implicitly trigger 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 runs the error handling code.

For the handling of this type of exception, the process is 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 in the corresponding case.

[user-defined exceptions]
Declare

V_sal Employees.salary%type;
--Declaring an exception
DELETE_MGR_EXCEP exception;
--Associating your own defined exceptions with Oracle's errors
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 specified employee's salary, such as salary less than 300. The No_data_found is added 100, and the too_many_rows is processed.
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 constraints"

Declare
--1. Defining exceptions
Temp_exception exception;

--2. An exception that defines it well. Connect to standard ORACLE errors using the Exception_init statement
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. Define the exception yourself: Update the specified employee's salary and add 100. Throws a user-defined exception if the employee does not exist: No_result

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

--Use an implicit cursor to throw your own definition exception
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