Oracle_pl/sql (9) Exception handling

Exception handling
1. Exception classifications: Pre-defined exceptions, non-predefined exceptions, three types of custom exceptions
Delivery exception: If there is no catch exception in the exception handling section exception, Oracle passes the exception to the calling environment.
Catching and Handling Exceptions: Use the exception handling section to complete
exception
When Exception1 Then
Statement1;
When Exception2 Then
Statement2;
...
When others then-must be the last clause in the Exception handling section
Statement_n;
...

2. Pre-defined exceptions
2.1 System pre-defined exceptions are 21, as follows:
dup_val_on_index:ora-00001 error.
Trigger exception when you type duplicate values on a column that corresponds to a unique index
zero_divide:ora-01476 error.
If you use a numeric value other than 0, the exception is triggered implicitly
invalid_number:ora-01722 error.
When an SQL statement cannot effectively convert a character to a number, the exception is triggered implicitly
no_date_found:ora-01403 error.
When you perform a SELECT into that does not return a row, or if you reference an element that is not initialized by the index table, the exception is triggered implicitly
too_many_rows:ora-01422 error.
When a SELECT INTO statement is executed, an exception is triggered if more than one row is returned
access_into_null:ora-06530 error.
The object must be initialized before referencing the object properties, otherwise triggering an exception
value_error:ora-06502 error.
If the variable length is not sufficient to accommodate the actual data, the departure exception is implied
case_not_found:ora-06592 error.
When you write a case statement, if you do not include the required conditional branch (else) in the When clause,
And does not contain an ELSE clause, it triggers
cursor_already_open:ora-06511 error.
When you reopen a cursor that is already open, the exception that is triggered is implied.
The display cursor has been opened with open and a for loop is executed, which implicitly triggers the exception
invalid_cursor:ora-01001 error.
When an attempt is made to perform an operation on an illegal cursor, an implicit trigger exception is implied.
To extract data from a cursor that has never been opened, or to close an open cursor, the exception is triggered
rowtype_mismatch:ora-06504 error.
The return type of the host cursor variable and the PL/SQL cursor variable is incompatible
collection_is_null:ora-06531 error.
Before assigning a value to a collection element (nested table and Varray type), the collection element must first be initialized or the exception is triggered
subscript_beyond_count:ora-06533 error.
When using nested tables or varray elements, if an element subscript is outside the scope of a nested table or varray element,
The triggering exception that is suppressed back
subscript_outside_limit:ora-06532 error.
When a nested table or varray element is used, an exception is suppressed if the element is labeled negative
login_denied:ora-01017 error.
An incorrect user name and password were provided when connecting to the database
not_logged_on:ora-01012 error.
Not connected to database
program_error:ora-06501 error.
There is a PL/SQL internal issue that may require reinstalling the data dictionary and the PL/SQL System Package
self_is_null:ora-30625 error.
When using an object type, if a member method is called on a null instance, the exception is triggered implicitly
storage_error:ora-06500 error.
If memory is exceeded or memory is corrupted
sys_invalid_rowid:ora-01410 error.
When a string is converted to rowID, a valid string must be used, or the exception is triggered
timeout_on_resource:ora-00051 error.
Oracle has a timeout error while waiting for resources

2.2 Examples
Example 1:case_not_found:
ora-06592. When you write a case statement, if you do not include the required conditional branch (else) in the When clause,
And does not contain an ELSE clause, it triggers
Declare
V_sal Emp.sal%type;
Begin
Select Sal to v_sal from EMP where empno=&no;
Case
When v_sal<1000 Then
Update emp set sal=sal+100 where empno=&no;
When v_sal<2000 Then
Update emp set sal=sal+150 where empno=&no;
When v_sal<3000 Then
Update emp set sal=sal+200 where empno=&no;
End case;
--exception
-When the Case_not_found then
--Dbms_output.put_line (' missing in case statement with ' | | v_sal| | ' The relevant conditions ');
End

Example 2:collection_is_null:
ora-06531 before assigning values to collection elements (nested tables and Varray types),
The collection element must be initialized first, or the exception is triggered
Declare
Type Ename_table_type is table of Emp.ename%type;
Ename_table Ename_table_type;
Begin
Select Ename to Ename_table (1) from EMP where empno=&no;
Dbms_output.put_line (' Employee Name: ' | | Ename_table (1));
exception
When Collection_is_null Then
Dbms_output.put_line (' must initialize the collection element using the constructor method ');
When Subscript_beyond_count Then
Dbms_output.put_line (' Subscript exceeds the range of the set element ');
End

3. Handling non-pre-defined exceptions
Using non-predefined exceptions includes three steps:
Define the exception name in the definition section,
Then establish an association between exceptions and Oracle errors,
The exception is eventually captured and handled in the Exception handling section.
When defining an association between Oracle errors and exceptions, you need to use pseudo-procedures Exception_init
Update emp set deptno=99 where empno=7788;
ORA-02291

Declare
E_integrity exception;
pragma exception_init (e_integrity,-2291);
Begin
Update emp set Deptno=&dno where empno=&eno;
exception
When E_integrity Then
Dbms_output.put_line (' The sector does not exist ');
End

4. Working with custom exceptions
Custom exceptions are not associated with Oracle errors.
Unlike pre-defined and non-predefined, custom exceptions must display triggers.
Declare
E_no_employee exception;
Begin
Update emp set deptno=20 where empno=&eno;
If Sql%notfound Then
Raise E_no_employee;
End If;
exception
When E_no_employee Then
Dbms_output.put_line (' The employee does not exist ');
End

5. Using the exception function
The function Sqlcode is used to obtain the Oracle error number, while SQLERRM is used to obtain the error message associated with it.
You can use Raise_application_error custom error numbers and error messages in stored procedures, functions, and packages.
5.1 Sqlcode and SQLERRM
Declare
V_ename Emp.ename%type;
--V_code VARCHAR2 (100);
--V_ERRM VARCHAR2 (100);
Begin
Select Ename to V_ename from EMP where empno=&empno;
--exception
-When the others then
--Dbms_output.put_line (' Error number: ' | | Sqlcode);
--Dbms_output.put_line (' Error message: ' | | SQLERRM);
--V_code:=sqlcode;
--V_ERRM:=SQLERRM;
--INSERT INTO error_log (id,code,errm,e_date)
--VALUES (seq_log.nextval,v_code,v_errm,sysdate);
End

5.2 Raise_application_error
Used to customize error messages in a PL/SQL application.
Raise_application_error can only be used in procedures, functions, packages, triggers, and cannot be used in anonymous blocks.
Syntax: Raise_application_error (error_number,message);
Description
Error_number: Must be a negative integer between-20000 and-20999.
Message: Maximum 2048 bytes
Example:
Create or Replace procedure Proc_trans_value (p_acid_out number,p_acid_in number,p_value number)
Is
L_CNT Number (8): = 0;
L_value Account.value%type;
Begin
Select COUNT (1) into l_cnt from account where accountid=p_acid_out;
If L_cnt=1 Then
Select value into L_value from account where accountid=p_acid_out;
If L_value>=p_value Then
Update account set value=value-p_value where accountid=p_acid_out;
Else
Raise_application_error (-20003, ' [insufficient turnover of account] ');
End If;
Else
Raise_application_error (-20001, ' [transfer out of account not present] ');
End If;
Select COUNT (1) into l_cnt from account where accountid=p_acid_in;
If L_cnt=1 Then
Update account set value=value+p_value where accountid=p_acid_in;
Else
Raise_application_error (-20002, ' [transfer to account does not exist] ');
End If;
End Proc_trans_value;

Insert into account VALUES (111, ' a ', 10000);
Insert into account values (A, ' B ', 100);
EXEC Proc_trans_value (111,112,20000)

Oracle_pl/sql (9) Exception handling