Oracle Exception Handling

1. What is an exception?

A: Exceptions are errors that occur when the program is running! Short run-time error. After an exception occurs, the statement stops executing and control shifts to the exception handling section.

Some exceptions are predictable, such as a divisor cannot be zero, but some exceptions are unforeseeable, such as a null pointer.

A good program should have exception handling, which is to capture and process errors that occur while the program is running.

2. Classification of anomalies

Exceptions are classified as pre-defined exceptions, non-predefined exceptions, and user-defined exceptions.

Predefined exceptions: implicitly thrown when a PL/SQL program violates an Oracle rule or exceeds a system limit, there is a predefined name that PL/SQL declares. Eg:no_data_found

such as: Select ... into ... Only one record can be returned, which causes an exception when multiple entries are returned

Non-predefined exception: implicitly thrown when a PL/SQL program violates an Oracle rule or exceeds a system limit, no predefined name is declared by PL/SQL for it.

User-defined exception: requires the user to define an exception variable in the program, explicitly thrown in the program. (The user stipulates that the program cannot do anything, such as age is not greater than 1000 years old)

3. Pre-defined exception syntax and usage

Begin

procedures and SQL statements;

exception

When exception name then

procedures and SQL statements;

When others then

procedures and SQL statements;

End

Test user Scott's EMP table test using the Oracle database:

DECLARE
V_ename Emp.ename%type;
BEGIN
SELECT ename to V_ename from EMP
WHERE ename = ' 1111 ';
Dbms_output.put_line (' Employee Name ' | | V_ename);
EXCEPTION
When No_data_found Then
Dbms_output.put_line (' Employee name does not exist ');
When OTHERS and then--other exceptions occur
Dbms_output.put_line (' Error number ' | | sqlcode| | ' Error name ' | | SQLERRM);
END;

4. Non-predefined exception syntax and usage

A non-pre-defined exception has one more declaration part than a predefined exception, declaring an exception variable:

Declare

< abnormal situation > exception;

pragma exception_init (< exception >,< error code >);

Begin

procedures and SQL statements;

exception

When exception name then

procedures and SQL statements;

When others then

procedures and SQL statements;

End

The predefined exception v_identity violates the primary KEY constraint, such as the department number of the employee table refers to the department table number, if the department table has no department number 11 this department,

At this time the employee table will not be able to update any records for the department number 11.

DECLARE
V_identity EXCEPTION;
PRAGMA Exception_init (v_identity,-2291);
BEGIN
UPDATE emp SET deptno= ' 11 ';
EXCEPTION
When V_identity Then
Dbms_output.put_line (' department does not exist ');
END;

5. User-defined exception syntax and usage

Declare

TEMP_EX exception;

Begin

Raise TEMP_EX;

exception

When TEMP_EX Then

......

End

If an employee with an employee number of 7369 does not have a subsidy, a null value will be returned when the subsidy is queried:

DECLARE
V_comm Emp.comm%type;
Comm_is_null EXCEPTION;
BEGIN
SELECT comm to V_comm from EMP WHERE empno=7369;
IF V_comm is NULL then
RAISE Comm_is_null;
END IF;
Dbms_output.put_line (' Employee Benefits: ' | | V_COMM);
EXCEPTION
When No_data_found Then
Dbms_output.put_line (' No data found ');
When Comm_is_null Then
Dbms_output.put_line (' The employee has no subsidy ');
When OTHERS Then
Dbms_output.put_line (' Other anomalies occurred ');
END;
6. Raise_application_error (display throws exception)

--Create sub-programs
CREATE OR REPLACE PROCEDURE status (pre_date date,act_date date)
As
BEGIN
If Pre_date<act_date then--if the scheduled time is less than the actual time
Raise_application_error (-20001, ' expired ');
END IF;
END;

--calling subroutines
DECLARE
Time_is_out EXCEPTION;
PRAGMA Exception_init (time_is_out,-20001);
BEGIN
Status (To_date (' 2014-9-5 ', ' yyyy-mm-dd '), to_date (' 2014-9-23 ', ' yyyy-mm-dd '));
EXCEPTION
When Time_is_out Then
Dbms_output.put_line (To_char (SQLERRM (-20001)));
END;

Oracle Exception Handling