Organize class notes PL/SQL Orcale exceptions

1>>>>> exception error Handling

1 > Pre-defined exception handling

Partial ORACLE exception errors for predefined descriptions

The handling of such exceptions is simply to refer to the exception-handling section of the PL/SQL block, directly referencing the corresponding exception case name, and completing the corresponding exception error handling.
Example 1: Update the specified employee's salary, if the salary is less than 1500, then add;

DECLARE
v_empno employees.employee_id%type: = &empno;
V_sal Employees.salary%type;
BEGIN
SELECT salary into V_sal from employees WHERE employee_id = v_empno;
IF v_sal<=1500 then
UPDATE employees SET Salary = salary + WHERE employee_id=v_empno;
Dbms_output. Put_Line (' encoded as ' | | v_empno| | ' Employee pay has been updated! ');
ELSE
Dbms_output. Put_Line (' encoded as ' | | v_empno| | ' Employee wages have exceeded the specified value! ');
END IF;
EXCEPTION
When No_data_found and then
Dbms_output. Put_Line (' No Code in database ' | | v_empno| | ' Employees ');
when Too_many_rows and then
Dbms_output. Put_Line (' program run Error! Use cursor ');
when OTHERS and then
Dbms_output. Put_Line (sqlcode| | ' ---' | | SQLERRM);
END;

2> non-pre-defined exception handling
For handling this type of exception, you must first define a non-defined Oracle error. The steps are as follows:
1. Define the exception condition in the definition section of the PL/SQL block:
< abnormal situation > EXCEPTION;
2. Use the Exception_init statement to associate its defined anomalies with standard Oracle errors:
PRAGMA Exception_init (< exception;, < error code >);
3. In the Exception handling section of the PL/SQL block, the exception is dealt with accordingly.
Example 2: Delete the record information for the specified department to ensure that the department has no employees.
INSERT into Departments VALUES (' FINANCE ', ' CHICAGO ');

DECLARE
V_deptno Departments.department_id%type: = &deptno;
Deptno_remaining EXCEPTION;
PRAGMA Exception_init (deptno_remaining,-2292);

BEGIN
DELETE from departments WHERE department_id = V_deptno;
EXCEPTION
When Deptno_remaining Then
Dbms_output. Put_Line (' Violating data integrity constraints! ');
When OTHERS Then
Dbms_output. Put_Line (sqlcode| | ' ---' | | SQLERRM);
END;

3> user-defined exception handling
When an error related to an exception error occurs, it is implicitly triggered by the exception error. User-defined exception errors are triggered by explicitly using the RAISE statement. When an exception error is thrown, the control moves to the exception block exception error section and executes the error-handling code.
For handling this type of exception, the steps are as follows:
1. Define exception conditions in the definition section of the PL/SQL block:
< abnormal situation > EXCEPTION;
2. RAISE < abnormal conditions >;
3. In the exception handling section of the PL/SQL block, the exception is handled accordingly.
Example 3: Update the specified employee's salary, increase 100;
DECLARE
V_empno Employees.employee_id%type:=&empno;
No_result EXCEPTION;
BEGIN
UPDATE Employees SET salary = salary+100 WHERE employee_id = v_empno;
IF Sql%notfound Then
RAISE No_result;
END IF;
EXCEPTION
When No_result Then
Dbms_output. Put_Line (' Your data UPDATE statement failed! ');
When OTHERS Then
Dbms_output. Put_Line (sqlcode| | ' ---' | | SQLERRM);
END;

5.1.4 user-defined exception handling
Invoking the Raise_application_error procedure defined by the Dbms_standard (Oracle-provided package) package, you can redefine the exception error message, which provides a way for the application to interact with Oracle.
The syntax for Raise_application_error is as follows:
Raise_application_error (Error_number,error_message,[keep_errors]);
The error_number here are the parameters from –20,000 to –20,999,
Error_message is the appropriate message (< 2048 bytes),
Keep_errors is optional, if Keep_errors =true, the new error is added to the list of errors that have been raised. If Keep_errors=false (default), the new error replaces the current list of errors.
Example 4: Create a function, get_salary, that retrieves the sum of the wages for the specified department, which defines the-20991 and-No. 20992 errors, dealing with both empty and illegal department codes, respectively:
CREATE TABLE ErrLog (
Errcode number,
Errtext CHAR (40));

CREATE OR REPLACE FUNCTION get_salary (p_deptno number)
RETURN number
As
V_sal number;
BEGIN
IF P_deptno is NULL then
Raise_application_error (-20991, ' Department code is empty ');
Elsif P_deptno<0 Then
Raise_application_error (-20992, ' invalid department code ');
ELSE
SELECT SUM (employees.salary) to v_sal from employees
WHERE Employees.department_id=p_deptno;
RETURN v_sal;
END IF;
END;

DECLARE
V_salary number (7,2);
V_sqlcode number;
V_sqlerr VARCHAR2 (512);
Null_deptno EXCEPTION;
Invalid_deptno EXCEPTION;
PRAGMA Exception_init (null_deptno,-20991);
PRAGMA Exception_init (Invalid_deptno,-20992);
BEGIN
V_salary: =get_salary (10);
Dbms_output. Put_Line (' No. 10th Department Salary: ' | | To_char (v_salary));

BEGIN
V_salary: =get_salary (-10);
EXCEPTION
When Invalid_deptno Then
V_sqlcode: =sqlcode;
V_sqlerr: =SQLERRM;
INSERT into ErrLog (Errcode, Errtext)
VALUES (V_sqlcode, V_sqlerr);
COMMIT;
END Inner1;

V_salary: =get_salary (20);
Dbms_output. Put_Line (' salary of department number 20 is: ' | | To_char (v_salary));

BEGIN
V_salary: =get_salary (NULL);
END Inner2;

V_salary: = Get_salary (30);
Dbms_output. Put_Line (' salary of department number 30 is: ' | | To_char (v_salary));

EXCEPTION
When Null_deptno Then
V_sqlcode: =sqlcode;
V_sqlerr: =SQLERRM;
INSERT into ErrLog (Errcode, Errtext) VALUES (V_sqlcode, V_sqlerr);
COMMIT;
When OTHERS Then
Dbms_output. Put_Line (sqlcode| | ' ---' | | SQLERRM);
END outer;

Example 5: Defining a trigger, using RAISE_APPLICATION_ERROR to block the insertion of a new staff record without an employee's name:
CREATE OR REPLACE TRIGGER tr_insert_emp
Before INSERT on employees
For each ROW
BEGIN
IF:new.first_name is null OR:new.last_name was null then
Raise_application_error ( -20000, ' Employee must has a name. ');
END IF;
END;

2 >>>>>> Abnormal error propagation
Because exception errors can occur in the Declarations section and in the execution section, and in the Exception Errors section, the exception errors that are thrown in different parts are not the same.
1. Throwing exception errors in the execution section
When an exception error is raised in the execution section, the following occurs:
A. If the current block has handled the exception error, execute it and successfully complete the execution of the block, and then control the transfer to the containing block.
B. If the processor is not defined for the current block exception error setting, the exception is propagated by throwing it in the containing block. Then perform step 1 on the containing block).
2. Throw an exception error in the Declaration section
If an exception is raised in the declaration section, where an error occurs in the Declaration section, then the error can affect other blocks. For example, in the case of a PL/SQL program:
DECLARE
Name varchar2: = ' Erichu ';
Other statements
BEGIN
Other statements
EXCEPTION
When OTHERS Then
Other statements
END;

example, because ABC number (3) = ' abc '; An error occurred, although when the OTHERS then statement was described in exception, the When OTHERS and then was not executed. However, if there is an exception error outside the block of the error statement, the error can be caught, such as:
BEGIN
DECLARE
Name varchar2: = ' Erichu ';
Other statements
BEGIN
Other statements
EXCEPTION
When OTHERS Then
Other statements
END;
EXCEPTION
When OTHERS Then
Other statements
END;

3. Exception error Handling programming
In general application processing, it is recommended that the program personnel use exception handling, because if no exception handling is declared in the program, the program is terminated when the program runs in error, and no information is prompted. The following are examples of programming using system-provided exceptions.
4. Using SQLCODE in PL/SQL, SQLERRM exception handling functions
Because the maximum length of the Oracle error message is 512 bytes, we can get the error message with the SQLERRM and SUBSTR functions together to get the complete wrong message, especially if it is more convenient when the when others exception handler.
SQLCODE returns the Oracle error number encountered,
SQLERRM returns the Oracle error message encountered.
such as: sqlcode=-100èsqlerrm= ' No_data_found '
Sqlcode=0èsqlerrm= ' Normal, successfual completion '
Example 6. Storing the Oracle error code and its information in the Error code table
CREATE TABLE Errors (errnum number (4), ErrMsg VARCHAR2 (100));

DECLARE
Err_msg VARCHAR2 (100);
BEGIN

For Err_num IN-100. 0 LOOP
Err_msg: = SQLERRM (Err_num);
INSERT into Errors VALUES (Err_num, err_msg);
END LOOP;
END;
DROP TABLE errors;

Example 7. Querying the Oracle error code;
BEGIN
INSERT into employees (employee_id, first_name,last_name,hire_date,department_id)
VALUES (2222, ' Eric ', ' Hu ', sysdate, 20);
Dbms_output. Put_Line (' Insert data record successfully! ');

INSERT into employees (employee_id, first_name,last_name,hire_date,department_id)
VALUES (2222, ' Hu ', ' Yong ', sysdate, 20);
Dbms_output. Put_Line (' Insert data record successfully! ');
EXCEPTION
When OTHERS Then
Dbms_output. Put_Line (sqlcode| | ' ---' | | SQLERRM);
END;

Example 8. Using Oracle error code, write exception error handling code;
DECLARE
Empno_remaining EXCEPTION;
PRAGMA Exception_init (empno_remaining,-1);

BEGIN
INSERT into employees (employee_id, first_name,last_name,hire_date,department_id)
VALUES (3333, ' Eric ', ' Hu ', sysdate, 20);
Dbms_output. Put_Line (' Insert data record successfully! ');

INSERT into employees (employee_id, first_name,last_name,hire_date,department_id)
VALUES (3333, ' Hu ', ' Yong ', sysdate, 20);
Dbms_output. Put_Line (' Insert data record successfully! ');
EXCEPTION
When Empno_remaining Then
Dbms_output. Put_Line (' Violating data integrity constraints! ');
When OTHERS Then
Dbms_output. Put_Line (sqlcode| | ' ---' | | SQLERRM);
END;


1, the advantages of the exception
　　
If there is no exception, in the program, you should check the success or failure of each command, such as
BEGIN
SELECT ...
--Check for ' No data found ' error
SELECT ...
--Check for ' No data found ' error
SELECT ...
--Check for ' No data found ' error
The disadvantage of this approach is that error handling is not separated from normal processing, is poorly readable, uses exceptions, can facilitate error handling, and that exception handlers are separated from normal transaction logic to improve readability, such as
BEGIN
SELECT ...
SELECT ...
SELECT ...
...
EXCEPTION
When No_data_found then – catches all ' NO DATA FOUND ' errors
2. Classification of anomalies
There are two kinds of exceptions,one for the inner exception, one for the user to customize the exception, an inner exception is an error that is caused by an oracle error that is returned to a PL/SQL block during execution or by an operation of the PL/SQL code, such as a divisor of zero or a memory overflow condition. User-defined exceptions are defined by the developer display, passing information in a PL/SQL block to control error handling for the application.
　　
An internal exception is implicitly generated whenever PL/SQL violates the principles of Oracle or the principle of system dependencies. Because each Oracle error has a number and exception is handled by name in PL/SQL, Oracle provides a predefined inner exception. Oracle exception No_data_found generated when the SELECT INTO statement does not return rows. For pre-defined exceptions, the most common exceptions are listed below:
Exception Oracle Error Sqlcode value condition
No_data_found ora-01403 +100 SELECT INTO statement no qualifying records returned
Too_many_rows ora-01422-1422 SELECT INTO statement matches a condition record has multiple returns
Dup_val_on_index ora-00001-1 for a column in a database table, the column has been limited to a unique index, and the program tries to store two duplicate values
Value_error ora-06502-6502 When a character type is converted, truncated, or limited in length, the exception is thrown, such as when a character is assigned to a variable and the length of the variable is shorter than the character.
Storage_Error ora-06500-6500 Memory Overflow
Zero_divide ora-01476-1476 Divisor is zero
Case_not_found ora-06592-6530 for selecting case statements, there are no conditions to match, and no else statement captures other conditions
Cursor_already_open ora-06511-6511 program tries to open a cursor that is already open
Timeout_on_resource ora-00051-51 system is waiting for a resource, time expires
　　
If you want to handle an unnamed inner exception, you must use the others exception handler or the pragma exception_init. Pragma is controlled by the compiler, or is a comment for the compiler. pragma is processed at compile time, not at run time. Exception_init tells the compiler to combine the exception name with the Oracle error code so that any inner exception can be referenced by name, and an appropriate exception handler can be written by name for the exception.
　　
The syntax for using Exception_init in subroutines is as follows:
PRAGMA Exception_init (Exception_name,-oracle_error_number);
　　
In this syntax, the exception name is the declared exception, and the following example is its usage:
DECLARE
Deadlock_detected EXCEPTION;
PRAGMA Exception_init (deadlock_detected,-60);
BEGIN
...--Some operation that causes an ORA-00060 error
EXCEPTION
When deadlock_detected Then
--Handle the error
END;
　　
For user-defined exceptions, exceptions can only be declared in the Declarations section of the PL/SQL block, and the name of the exception is introduced by the exception keyword:
reserved_loaned Exception
　　
After an exception is generated, the control passes to the exception part of the subroutine, turning the exception to the respective exception control block, and the following structure must be used in the code to handle the error:
Exception
When Exception1 Then
Sequence of statements;
When Exception2 Then
Sequence of statements;
When others then
　　
3, the exception throws
　　
Throws an exception in three different ways
　　
1. Through the PL/SQL run-time engine
　　
2. Using the Raise statement
　　
3. Call the raise_application_error stored procedure
　　
When a database or PL/SQL error occurs at run time, an exception is automatically thrown by the PL/SQL runtime engine. Exceptions can also be thrown by raise statements
RAISE Exception_name;
　　
An explicit throw exception is a custom usage of the exception that the programmer handles the declaration, but raise is not limited to the exception that is declared, and it can throw any exception. For example, if you want to use the Timeout_on_resource error to detect a new runtime exception handler, you simply use the following statement in your program:
RAISE Timeout_on_resouce;
　　
Example of an order entry below, if the order is less than the inventory quantity, throws an exception, catches the exception, handles the exception
DECLARE
Inventory_too_low EXCEPTION;
　　
---Other declaration statements
BEGIN
IF Order_rec.qty>inventory_rec.qty Then
RAISE Inventory_too_low;
END IF
EXCEPTION
When Inventory_too_low Then
order_rec.staus:= ' backordered ';
END;
　　
The raise_application_error built-in function is used to throw an exception and give the exception an error number along with an error message. The default error number for a custom exception is +1, and the default information is user_defined_exception.　 The Raise_application_error function can be called in the execution and exception portions of the PL/SQL program block, explicitly throwing a named exception with a special error number. Raise_application_error (Error_number,message[,true,false]))
　　
The range of error numbers is 20,000 to-20,999. The error message is a text string, up to 2048 bytes. True and false indicate whether to add (TRUE) the heap (error stack) or overwrite (overwrite) error heaps (FALSE). False by default.
　　
As shown in the following code:
IF Product_not_found Then
Raise_application_error ( -20123, ' Invald product Code ' TRUE);
END IF;
　　
4. Exception Handling
　　
The exception portion of the PL/SQL program block contains code that the program handles the error, and an exception trap occurs automatically when the exception is thrown, and the control leaves the execution part into the exception section, and once the program enters the exception part it cannot go back to the execution part of the same block. The following is the general syntax for the exception section:
EXCEPTION
When Exception_name Then
Code for handing Exception_name
[When Another_exception Then
Code for handing Another_exception]
[When others then
Code for handing any other exception.]
　　
The user must design exception handling code for each exception in a separate when substring, when the others substring must be placed on the last surface as the default processor to handle exceptions that are not explicitly handled. When an exception occurs, the control goes to the exception section, and Oracle finds the appropriate when for the current exception: Then statement, catch exception, then code is executed, if the error trap code just exits the corresponding nested block, then the program will continue to execute the statement after the inner block end. If the corresponding exception trap is not found, then the when OTHERS will be executed. In the exception section when the substring has no number limit.
EXCEPTION
When Inventory_too_low Then
order_rec.staus:= ' backordered ';
Replenish_inventory (inventory_nbr=>
Inventory_rec.sku,min_amount=>order_rec.qty-inventory_rec.qty);
When Discontinued_item Then
--code for Discontinued_item processing
When Zero_divide Then
--code for Zero_divide
When OTHERS Then
--code for any other exception
END;
　　
When the exception is thrown, the control unconditionally goes to the exception section, which means that control cannot go back to where the exception occurred, and when the exception is processed and resolved, control returns to the next statement in the previous layer of execution.
BEGIN
DECLARE
Bad_credit exception;
BEGIN
RAISE Bad_credit;
-abnormal, control steering;
EXCEPTION
When Bad_credit Then
Dbms_output.put_line (' Bad_credit ');
END;
--bad_credit after exception handling, control goes here
EXCEPTION
When OTHERS Then
　　
--control does not transfer from Bad_credit to here
　　
--because Bad_credit has been processed
　　
END;
　　
When an exception occurs where the exception handler is not inside the block, the control goes to or propagates to the exception handling portion of the previous block.
　　
BEGIN
DECLARE---inner block start
　　
Bad_credit exception;
BEGIN
RAISE Bad_credit;
　　
-abnormal, control steering;
EXCEPTION
When Zero_divide then--Cannot handle Bad_credite exception
Dbms_output.put_line (' Divide by zero error ');
　　
End--ends inner block
　　
--control cannot get here because the exception is not resolved;
　　
--Exception section
　　
EXCEPTION
When OTHERS Then
--because Bad_credit is not resolved, control will be transferred here
END;
　　
5. Abnormal propagation
　　
An exception that is not handled will propagate along the detection exception invoker to the outside, when the exception is handled and resolved or the outermost propagation of the program arrives stopped. Exceptions thrown in the declaration section will control the exception portion of the previous layer.
　　
BEGIN
Executable statements
BEGIN
Today date:= ' syadate '; --errror
　　
Begin--The inner block begins
Dbms_output.put_line (' This line would not execute ');
EXCEPTION
When OTHERS Then
　　
--exceptions are not handled here
　　
end;--Inner block End
EXCEPTION
When OTHERS Then
　　
Handling Exceptions
　　
END

Organize class notes PL/SQL Orcale exceptions