First, the concept
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:
In the declaration section of the PL/SQL block, define the exception condition:< > EXCEPTION;
RAISE < Abnormal conditions >
In the exception handling section of the PL/SQL block, the exception is handled accordingly.
Second, the procedure
--Example 1: Using custom exception variables
DECLARE
V_empno Emp.empno%type:=&empno;
No_result exception;--Defining exception variables
BEGIN
UPDATE emp SET sal=sal+100 WHERE empno=v_empno;
IF Sql%notfound Then
RAISE No_result;
END IF;
EXCEPTION
When No_result Then
Dbms_output. Put_Line (' Your data UPDATE statement failed! ');
When OTHERS Then
Dbms_output. Put_Line (SQLCODE | | ' ---' | | SQLERRM);
END;
--Example 2: Throwing an exception using the Raise_application_error function
DECLARE
v_deptid departments.department_id%type: = &no;
v_dname Departments.department_name%type;
BEGIN
SELECT department_name
into V_dname
from Departments
WHERE department_id = V_deptid;
Dbms_output.put_line (v_dname);
EXCEPTION
when OTHERS then
raise_ Application_Error ( -20001,
' Department ' | | V_deptid | |
' does not exists ');
END;
Attention:
User-defined exception, exception number starting from -20001 .
A given exception is handled by at most one processor in the Exception handling section. If there are multiple exception handlers, the PL/SQL compiler produces a PLS-00483 error.
Qlcode returns the current error code, SQLERRM returns the message text for the current error. For user-defined exceptions, the return value of Sqlcode is ' 1 ' and the SQLERRM return value is ' user-defined Exception '.
The maximum length of the Oracle error message is 512 bytes.
The values of Sqlcode and SQLERRM are first assigned to local variables and cannot be used directly in SQL statements.
Third, abnormal execution mechanism
If the current block sets the processing for the exception error, it executes and completes the execution of the block successfully, and then controls the transfer to the containing block.
If the processor is not defined on the current block exception error setting, the exception is propagated by throwing it in the containing block. Then perform step 1 on the containing block.
Iv. How to output the line number and exception code for exception occurrences in PL/SQL
simply add the following code to the exception block.
EXCEPTION
When OTHERS Then
Dbms_output.put_line (SQLERRM | | '-' | | Dbms_utility.format_error_backtrace);
This article is from the "Eight Days Dragon" blog, please make sure to keep this source http://1224517743.blog.51cto.com/2643474/1610216
Custom exception trivia in PL/SQL