Custom exception trivia in PL/SQL

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:

1. User-defined exception, exception number starting from -20001 .

2. 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.

3. 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 '.

4. The maximum length of the Oracle error message is 512 bytes.

5. The values of Sqlcode and SQLERRM are first assigned to local variables and cannot be used directly in SQL statements.

Third, abnormal execution mechanism

1. 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.

2. 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