Plsql Exception Handling

The cursor has been browsed once, in order to improve the robustness of the Plsql program, first to write Plsql exception handling, that is, if the PLSQL code block error, the normal does not go to capture processing, will definitely stop execution, catch the exception and the corresponding processing, you can let the program continue to execute, An unknown error that occurs is called an exception.

Goal

1) Use pre-defined exceptions

2) Use of non-pre-defined exceptions

3) Use custom exceptions

Defining three exceptions is funny, predefined is Oracle's common error, non-predefined is to handle Oracle errors that the predefined cannot handle, and customization is to handle errors that are not related to Oracle.

Capturing and Handling exceptions

EXCEPTION

When Exception1 Then

Statement1

、、、、

When OTHERS Then

Statementend

Remember when others is always in the last condition of exception handling, representing the above when you write all the times are not satisfied with the time to meet this others

"Pre-defined error"

A system exception provided by Plsql, which violates Oracle rules or system limitations, triggers an internal exception, presumably providing more than 20, with each of the predefined exceptions corresponding to an Oracle system error, such as No_data_found corresponding to a ORA-01403 error

This error is implicitly triggered when no data is obtained, too_many_rows, etc.

"Non-pre-defined error"

Non-predefined errors are used to handle Oracle errors unrelated to a predefined exception, and here is a case where the constraint is violated.

There are three steps to use:

1) Define the partial definition exception name, declare e_integrity exception;

2) Establish an association between exceptions and Oracle errors pragma exception_init (e_integrity,-2291);

3) Exception handling section captures and handles exceptions

Declare

E_integrity exception;

pragma exception_init (e_integrity,-2291);

Begin

Update emp set Deptno=%dno where empno=&eno;

Commit

exception

When E_integrity Then

Dbms_output.put_line (' Not exists deptno ');

When others then

Dbms_output.put_line (Sqlcode);

End

"Custom Exceptions"

The biggest difference between custom exceptions is the trigger that needs to be displayed ,

Three steps to use:

1) Define part of the definition E_no_employee exception;

2) Perform a partial trigger if Sql%notfoud then raise E_no_employee;

3) Exception handling section captures and processes

Declare

E_integrity exception;

pragma exception_init (e_integrity,-2291);

E_no_employee exception;

Begin

Update emp set Deptno=&dno where empno=&eno;

If Sql%notfound Then

Raise E_no_employee;

End If;

exception

When E_integrity Then

Dbms_output.put_line (' Deptno not exists ');

When E_no_employee Then

Dbms_output.put_line (' Empno not exists ');

End

The other is the exception function, which is often used when writing C to be sqlcode and SQLERRM, to get error numbers and error messages,

Dbms_output.put_line (Sqlcode);

Dbms_output.put_line (SQLERRM);

There are three types of warnings that are often used when compiling in a different way:

1) Server: If the alias of the parameter is the problem,

2) Performance: Performance issues, such as performance issues with type transformations

3) All: This keyword is used to check all warnings

Control warning message

Alter system set Plsql warnings= ' Enable:all ';

Alter session set plsql_warnings= ' Enable:performance ';

Alter procedure Hello Complie; it's like recompiling the hello process.

The key is to understand the three types of warning information, such as what the dead code needs to be checked, do not execute, but there should be code block execution performance problems, so when the check made performance,

The other point is that attributes and exceptions, such as Sql%notfound and No_data_found, are different from who produced them, and can be used at any time before you confuse

This article from "Yun Weibang" blog, declined reprint!

Plsql Exception Handling