PL/SQL Exception error handling and plsql error handling

PL/SQL Exception error handling and plsql error handling
Zookeeper

Exception Handling
A good program should be able to correctly handle all kinds of errors and recover from errors as much as possible. ORACLE provides EXCEPTION and exception handler for error handling.

① There are three types of exception errors:
1. Predefined Error
There are about 24 predefined ORACLE exceptions. To handle this exception, you do not need to define it in the program. ORACLE will automatically throw it.

2. Predefined Error
That is, other standard ORACLE errors. To handle this exception, you need to define it in the program, and then the ORACLE will automatically cause it.

3. user defined (User_define) Error
During program execution, the programmer may think that the program is abnormal. To handle this exception, you need to define it in the program and then explicitly raise it in the program.

② The Exception Handling part is generally placed in the back half of the PL/SQL program body. The structure is:
EXCEPTION
WHEN first_exception THEN <code to handle first exception>
WHEN second_exception THEN <code to handle second exception>
When others then <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 handle this exception, you only need to directly reference the corresponding Exception name in the Exception Handling Section of the PL/SQL block and handle the corresponding Exception error.

[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 Too_many_rows then dbms_output.put_line ('Too many output rows ');
End;

④ Non-predefined Exception Handling
To handle such exceptions, you must first define non-defined ORACLE errors. The procedure is as follows:
1. Define exceptions in the definition section of PL/SQL blocks:
<EXCEPTION> EXCEPTION;

2. Associate the defined exceptions with standard ORACLE errors and use the PRAGMA EXCEPTION_INIT statement:
PRAGMA EXCEPTION_INIT (<exception status>, <error code> );

3. handle exceptions in PL/SQL blocks.

[Non-predefined exception]
Declare

V_sal employees. salary % type;
-- Declare an exception
Delete_mgr_excep exception;
-- Associate custom exceptions with oracle 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 Too_many_rows then dbms_output.put_line ('Too many output rows ');
When delete_mgr_excep then dbms_output.put_line ('manager cannot be deleted directly ');
End;

⑤ Custom exception handling
When an error related to an exception occurs, it is implicitly triggered. A user-defined exception error is triggered by explicitly using the RAISE statement. When an EXCEPTION error is thrown, the control switches to the EXCEPTION part of the EXCEPTION block and runs the error handling code.
To handle such exceptions, follow these steps:
1. Define exceptions in the definition section of PL/SQL blocks:

<EXCEPTION> EXCEPTION;
2. RAISE <exception>;

3. handle exceptions in PL/SQL blocks.

[User-defined exception]
Declare

V_sal employees. salary % type;
-- Declare an exception
Delete_mgr_excep exception;
-- Associate custom exceptions with oracle errors
PRAGMA EXCEPTION_INIT (delete_mgr_excep,-2292 );

-- Declare an exception
Too_high_sal exception;
Begin

Select salary into v_sal
From employees
Where employee_id = 100;

If v_sal> 1000 then
Raise too_high_sal;
End if;

Delete from employees
Where employee_id = 100;

Dbms_output.put_line (v_sal );

Exception
When Too_many_rows then dbms_output.put_line ('Too many output rows ');
When delete_mgr_excep then dbms_output.put_line ('manager cannot be deleted directly ');
-- Handle exceptions
When too_high_sal then dbms_output.put_line ('overpay ');
End;

⑥ Use SQLCODE and SQLERRM in PL/SQL

Error code number returned by SQLCODE
SQLERRM returns an error message.

EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLCODE | '---' | SQLERRM );

7. Abnormal Program:

1. query a person's salary through select... into.... If no data is found, the output is "no data found"
Declare
-- Define a variable
V_sal employees. salary % type;
Begin
-- Use select... into... to assign values to v_sal
Select salary into 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 ('data not found ');
End;

Or
Declare
-- Define a variable
V_sal employees. salary % type;
Begin
-- Use select... into... to assign values to v_sal
Select salary into 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. If the employee's salary is less than 300, an additional 100 will be added. If NO_DATA_FOUND is abnormal, TOO_MANY_ROWS will be processed.
Declare
V_sal employees. salary % type;
Begin
Select salary into v_sal from employees where employee_id = 100;

If (v_sal <300) then
Update employees set salary = salary + 100 where employee_id = 100;
Else
Dbms_output.put_line ('salary greater than 300 ');
End if;
Exception
When no_data_found then
Dbms_output.put_line ('data not found ');
When too_many_rows then
Dbms_output.put_line ('Too many data rows output ');
End;

3. Handle non-predefined Exception Handling: "Violation of complete constraints"

Declare
-- 1. Definition exception
Temp_exception exception;

-- 2. Associate the defined exception with a standard ORACLE error and use 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 ('violation of integrity constraints! ');
End;

4. custom exception: update the salary of a specified employee by 100. If the employee does not exist, a custom exception is thrown: no_result.

Declare
-- Custom exception
No_result exception;
Begin
Update employees set salary = salary + 100 where employee_id = 1001;

-- Use an implicit cursor to throw a custom exception
If SQL % notfound then
Raise no_result;
End if;

Exception

-- Exception thrown by the processing program
When no_result then
Dbms_output.put_line ('Update failed ');
End;