Oracle Plsql catching exceptions and throwing exceptions

While writing Oracle stored procedures, many things put into stored procedures, such as some judgments, are much simpler than in the program logic, but also involve capturing and throwing the same problem.

Catch Exception Syntax:

EXCEPTION when    excepttion_name1 then      ...    . When the excepttion_name2 then     ...    When the Excepttion_name3 then     ... End;

Example:

Declare     a  int:=0;    b int:=1;   Ex_1 exception;   Ex_2 exception;  Begin     If A=0 then    raise ex_1;    End If;        If B=1 then       raise ex_2;    End If;        Exception when      ex_1         then Dbms_output.put_line (' caught error 1 ');      When Ex_2          then Dbms_output.put_line (' caught error 2 '); end;

Output:

Error 1 was caught

Because of the error in Ex_1 place, the following ex_2 is not executed, but instead jumps directly to the error-handling section of the code. An exception is not allowed in Oracle to be handled by multiple exception handling blocks.

Use Ohters to handle all errors

Declare     a  int:=0;    Ex_1 exception; Begin     If A=0 then    raise ex_1;    End If;         Exception when       others and        then Dbms_output.put_line (' Catch a global error ');  End

Output:

Throw Exception Raise_application_error function

The function is to transfer application-specific errors from the server side to the client application (Sqlplus on other machines or the foreground development language)

PROCEDURE raise_application_error (error_number_in in number, error_msg_in in VARCHAR2);    ERROR_NUMBER_IN: Custom error code, allowable from 20000 to 20999, so that there is no conflict with any error code of ORACLE.    error_msg_in: Length cannot exceed 2k, otherwise intercept 2k

Example: let a number not be 0

Declare   a int:=0;begin    if a=0 then       raise_application_error (-20001, ' value cannot be 0 ');    End If; End

Run:

Oracle Plsql catching exceptions and throwing exceptions