Oracle exception Handling and functions

There are three types of exception errors:
Pre-defined (predefined) errors
There are approximately 24 pre-defined exception cases for Oracle. The handling of this exception does not have to be defined in the program and is automatically raised by Oracle.
Non-pre-defined (predefined) error
That is, other standard Oracle errors. Handling this exception requires the user to define it in the program, which is then automatically raised by Oracle.
User-defined (user_define) error
During the execution of the program, it appears that the programmer considers the abnormal situation. Handling this exception requires the user to define it in the program and then explicitly raise it in the program.

Exception Handling Section structure:
The exception handling section is generally placed in the second half of the PL/SQL program body
EXCEPTION
When First_exception then <code to handle first exception >
When Second_exception and <code to handle second exception >
When OTHERS and <code to handle OTHERS exception >
END;
Exception handling can be arranged in any order, but OTHERS must be placed in the last
Pre-defined exception handling

For the processing of pre-defined exception cases, simply reference the corresponding exception case name directly in the exception handling section of the PL/SQL block and complete the corresponding exception error handling.
Example: DECLARE
V_empno Emp.empno%type:=&empno;
V_sal Emp.sal%type;
BEGIN
SELECT sal into v_sal from emp WHERE empno=v_empno;
IF v_sal<=1500 Then
UPDATE emp SET sal=sal+100 WHERE empno=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 Then
Dbms_output. Put_Line (' No Code in database ' | | v_empno| | ' Employees ');
When Too_many_rows Then
Dbms_output. Put_Line (' program run Error! Use cursor ');
When OTHERS Then
Dbms_output. Put_Line (sqlcode| | ' ---' | | SQLERRM);
END;
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:
The exception condition is defined in the Declarations section of the PL/SQL block:
< abnormal situation > EXCEPTION;
Use the Exception_init statement to associate a defined exception with a standard Oracle error.
PRAGMA Exception_init (< exception;, < error code >);
In the exception handling section of the PL/SQL block, the exception is handled accordingly.
Deptno_remaining EXCEPTION;
PRAGMA Exception_init (deptno_remaining,-2292);
/*-2292 is an error code that violates the consistency constraint */
User-defined exception handling
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:
The exception condition is defined in the Declarations section of the PL/SQL block:
< abnormal situation > EXCEPTION;
RAISE < Abnormal conditions >
Handling exception cases in the exception handling section of PL/SQL blocks
DECLARE
V_empno Emp.empno%type:=&empno;
No_result EXCEPTION;
BEGIN
UPDATE emp SET sal=sal+100 WHERE empno=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;

Raise_application_error function
Example: Declare
V_deptid Departments.department_id%type: = &no;
V_dname Departments.department_name%type;
Begin
Select Department_name to V_dname from departments
where department_id = V_deptid;
Dbms_output.put_line (V_dname);
exception
When others then
Raise_application_error ( -20001, ' Department ' | | v_deptid| | ' does not exists ');
End

Oracle exception Handling and functions