Oracle Study Notes 17

It is normal for programs to encounter exceptions during execution, and exceptions during programming are inevitable. However, an exception handling server is required.

To ensure the normal execution of the program. An error occurs during the execution of PL/SQL programs, which is called an exception. A good program should be able to handle it correctly

Errors, and try to recover from the errors. PL/SQL provides an exception handling mechanism.

Concept:

Exception Processing (exception) is used to handle unexpected events during normal execution. The exception Processing of the program block defines errors and custom errors,

Once an exception occurs in the PL/SQL block, the program terminates unexpectedly.

There are three types of errors

Predefined exceptions:

It is one of Oracle system exceptions and is used to handle common exceptions. The Oracle system defines their error numbers and names. When PL/SQL code is run to generate a predefined error, the exception is thrown automatically. After the Exception Processing Mechanism is captured, it is processed. The two most common exceptions are no_date_found and too_mang_rows. The error numbers are 1403 and 1422. a multiline error is returned for no data or result.

Non-predefined exceptions:

This type of exception is specific. Oracle defines their numbers, but there is no name. You need to define their names in the declaration, which is automatically triggered by Oracle.

Custom exception:

When processing user-defined errors, some operations do not produce system errors, but from the business perspective, the rules are not very compliant.

Structure:

EXCEPTION

WHEN exception_name1 THEN code;

WHEN exception_name2 THEN code;

...

When others then code;

END;

Exception Handling Procedure

Definition exception: the declaration part defines the error exception. Exce_name exception; pragma exception_init (exce_name error_number );

Throw exception: When an error is generated, the error corresponding to the exception is thrown. Raise exce_name;

Capture and handle exceptions: The Exception Handling part captures and processes exceptions.

For example, use the predefined no_data_found exception to prevent data not found during running.

Declare
V_sal emp. sal % type;
Begin
Select sal into v_sal from emp where empno = 7938;
Dbms_output.put_line (v_sal );
Exception
When no_data_found then dbms_output.put_line ('no such data ');
End;

When executing the delete from dept where deptno = 10 Statement, the system throws a ORA-02292 that violates the full constraint condition (SCOTT. FK_DEPTNO)-A subrecord found exception. The system defines a number for it, but there is no name. You can customize a name.

For example, a non-defined exception is used to handle integrity violation errors.

Declare
Exce_deptno exception;
Pragma exception_init (exce_deptno,-2292 );
Begin
Delete from dept where deptno = 10;
Exception
When exce_deptno then dbms_output.put_line ('the employee table uses this number ');
End;

For example, if an employee's salary is less than 1000, an exception is thrown and processed.

Declare
E_sal exception;
V_empno emp. empno % type;
Cursor cou_sal is select * from emp;
Begin
For c in cou_sal loop
Dbms_output.put_line (c. sal );
If (c. sal <1000) then
V_empno: = c. empno;
Raise e_sal;
End if;
End loop;
Exception
When e_sal then dbms_output.put_line (v_empno | 'salary increase ');
End;

Abnormal Functions

When an error occurs in a PL/SQL program, you can use the exception function to obtain the error code and related Error Descriptions. You can use SQLCODE to obtain the error code. SQLERRM users can obtain the error description.

Example: Get error code and description

Begin
Delete from dept where deptno = 10;
Exception
When others then dbms_output.put_line (sqlcode | '---' | sqlerrm );
End;