Shulou(Shulou.com)05/31 Report--

This article introduces the relevant knowledge of "Procedure Analysis of Oracle stored procedures". Many people will encounter this dilemma in the operation of actual cases, so let the editor lead you to learn how to deal with these situations. I hope you can read it carefully and be able to achieve something!

We all know that even the best-written PL/SQL programs encounter errors or unexpected events. A good program should be able to handle all kinds of errors correctly and recover from them as much as possible.

Any ORACLE errors (reported as Oracle error numbers in the form of ORA-xxxxx), PL/SQL run errors, or user-defined conditions, PL/SQL compilation errors cannot be handled by PL/SQL exception handling because these errors occur before the PL/SQL program executes.

ORACLE provides exception case (EXCEPTION) and exception handling (EXCEPTION HANDLER) for error handling.

Exception handling (EXCEPTION) is used to handle unexpected events during normal execution, and the exception handling of the program block handles predefined errors and custom errors. Because once the PL/SQL block produces an exception and does not indicate how to handle it, the program will automatically terminate the whole program.

There are three types of exception errors:

1. Predefined (Predefined) error

There are about 24 predefined exceptions in ORACLE. The handling of this exception does not need to be defined in the program and is automatically thrown by ORACLE.

2. Unpredefined (Predefined) error

That is, other standard ORACLE errors. The handling of this exception requires the user to define it in the program, and then ORACLE automatically throws it.

3. User defined (User_define) error

During the execution of the program, the programmer thinks that the abnormal situation occurs. Handling of this exception requires the user to define it in the program and then explicitly throw it in the program.

The exception handling part is usually placed in the second half of the PL/SQL program body, which is structured as follows:

EXCEPTION

WHEN first_exception THEN

WHEN second_exception THEN

WHEN OTHERS THEN

END

Exception handling can be arranged in any order, but OTHERS must be placed last.

For instance

Create or replace procedure emp_exce_p (empno number) is

V_sal emp.sal%type

Begin

Select sal into v_sal from emp where empno = empno

If v_sal

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.