27. Oracle exception

I. Classification of exceptions
Oracle divides exceptions into three predefined exceptions, non-pre-defined exceptions, and custom exceptions.
1), predefined exceptions are used to handle common Oracle errors.
2), non-predefined exceptions are used to handle exceptions that cannot be handled by predefined exceptions.
3), custom exceptions are used to handle other conditions unrelated to Oracle errors.

Below is a small case demonstration of what happens if you don't deal with an exception?
Write a stored procedure to receive the employee's number and display the employee's name.
The SQL code is as follows:

SET serveroutput on;
DECLARE
  V_ename EMP. Ename%type;
BEGIN
  SELECT ename to V_ename from EMP WHERE EMPNO = &GNO;
  Dbms_output. Put_Line (' Name: ' | | V_ename);
END;
/

Enter the non-existent number, enter, will throw the following exception:
ORA-01403: No data found
ORA-06512: On line 6

The exception captures the SQL code as follows:

SET serveroutput on;
DECLARE
  V_ename EMP. Ename%type;
BEGIN
  SELECT ename to V_ename from EMP WHERE EMPNO = &GNO;
  Dbms_output. Put_Line (' Name: ' | | V_ename);

  
    Dbms_output. Put_Line (' Number not found! ‘);
END;
/

Enter the non-existent number, enter, will be friendly tip: number not found!

Ii. handling of pre-defined exceptions
The pre-defined exception is a system exception provided by PL/SQL. An internal exception is implicitly triggered when a PL/SQL application violates an Oracle-imposed limit. PL/SQL provides developers with more than 20 pre-defined exceptions. Let's introduce the common exceptions.
1), Case_not_found Pre-defined exceptions
When you write a case statement in the development PL/SQL block, the case_not_found exception is triggered if you do not include the required conditional branch in the When clause:

SET serveroutput on;
CREATE OR REPLACE PROCEDURE Sp_pro6 (spno number) is
  V_sal EMP. Sal%type;
BEGIN
  SELECT SAL into V_sal from EMP WHERE EMPNO = spno;
  Case
    When V_sal <
      UPDATE EMP SET sal = sal + WHERE EMPNO = spno;
    When V_sal <
      UPDATE EMP SET sal = sal + EMPNO = spno;
  END case;
EXCEPTION
  When Case_not_found Then
    Dbms_output. Put_Line (' case statement not associated with ' | | V_sal | | ' Match the conditions ');
END;
/

--Call the stored procedure
Sql> EXEC Sp_pro6 (7369);
The case statement does not match the 4444 condition

2), Cursor_already_open Pre-defined exceptions
When you reopen a cursor that is already open, the Cursor_already_open exception is implicitly triggered

DECLARE
  CURSOR Emp_cursor is
    SELECT ename, SAL from EMP;
BEGIN
  OPEN Emp_cursor; --the cursor is open when declared, so there is no need to open it again
  For Emp_record1 in Emp_cursor LOOP
    Dbms_output. Put_Line (Emp_record1. ENAME);
  END LOOP;
EXCEPTION
  When Cursor_already_open Then
    Dbms_output. Put_Line (' cursor already open ');
END;
/

3), Dup_val_on_index Pre-defined exceptions
The triggering exception is implied when a duplicate value is inserted on the column corresponding to the unique index

BEGIN
  INSERT into DEPT VALUES (10, ' PR ', ' Beijing ');
EXCEPTION
  When Dup_val_on_index Then
    Dbms_output. Put_Line (' Duplicate values cannot appear on the Deptno column ');
END;
/

4), Invalid_cursorn Pre-defined exceptions
This exception is triggered when an attempt is made to perform an operation on an illegal cursor
For example, attempting to extract data from a cursor that is not open, or to close a cursor that is not open. The exception is triggered

DECLARE
  CURSOR Emp_cursor is
    SELECT ename, SAL from EMP;
  Emp_record Emp_cursor%rowtype;
BEGIN
  --open Emp_cursor; --Open cursor
  FETCH emp_cursor into Emp_record;
  Dbms_output. Put_Line (Emp_record. ENAME);
  CLOSE Emp_cursor;
EXCEPTION
  When Invalid_cursor Then
    Dbms_output. Put_Line (' Please detect if the cursor is open ');
END;
/

5), Invalid_number Pre-defined exceptions
This exception is triggered when the input data is incorrect
For example: The number 100 is written as loo will trigger the exception

SET serveroutput on;
BEGIN
  UPDATE EMP SET sal = sal + ' AAA ';
EXCEPTION
  When Invalid_number Then
    Dbms_output. Put_Line (' The number entered is incorrect ');
END;
/

6), No_data_found Pre-defined exceptions
The following is a PL/SQL block, which is triggered when you execute a SELECT into without returning a row.

SET serveroutput on;
DECLARE
  V_sal EMP. Sal%type;
BEGIN
  SELECT SAL into V_sal from EMP WHERE ename = ' LJQ ';
EXCEPTION
  When No_data_found Then
    Dbms_output. Put_Line (' There is no such employee ');
END;
/

7), too_many_rows Pre-defined exceptions
When a SELECT INTO statement is executed, the exception is triggered if more than one row is returned.

DECLARE
  V_ename EMP. Ename%type;
BEGIN
  SELECT ename to V_ename from EMP;
EXCEPTION
  When Too_many_rows Then
    Dbms_output. Put_Line (' returned multiple lines ');
END;
/

8), zero_divide Pre-defined exceptions
This exception is triggered when a 2/0 statement is executed
9), Value_error Pre-defined exceptions
This exception is triggered when an assignment operation is performed, if the length of the variable is not sufficient to accommodate the actual data value_error

Other pre-defined exceptions (these exceptions are not triggered in PL/SQL, but are triggered when using Oracle, so they are called other predefined exceptions)
1, login_denied
This exception is triggered when a user logs on illegally
2, not_logged_on
This exception is triggered if the user performs a DML operation without logging on
3, Storage_Error
This exception is triggered if the memory space is exceeded or if the memory is corrupted
4, Timeout_on_resource
If Oracle waits for a resource, the exception is triggered when a timeout occurs

Iii. non-pre-defined exceptions
Non-predefined exceptions are used to handle Oracle errors unrelated to the predefined exceptions. Only 21 Oracle errors can be handled with a predefined exception, and some other Oracle errors may be encountered when developing an application using PL/SQL. For example, when executing DML statements in a PL/SQL block, the constraint rules are violated, and so on. In this case, you can also deal with the various exceptions to Oracle, because there are not many non-predefined exceptions, and I don't have an example here.

Iv. Handling of custom exceptions
Pre-defined exceptions and custom exceptions are related to Oracle errors, and Oracle errors that occur implicitly trigger the appropriate exceptions, and custom exceptions are not associated with Oracle errors, which are exceptions defined by the developer for a specific situation.
Issue: Write a PL/SQL block, receive an employee's number, and increase the employee's salary by $1000, if the employee does not exist, please prompt.

CREATE OR REPLACE PROCEDURE ex_test (spno number) is
BEGIN
  UPDATE EMP SET sal = sal + EMPNO = spno;
END;
/

--Call the stored procedure,
EXEC Ex_test (56);

Here, the number 56 is not there, just the report is abnormal, why now do not report abnormal it?
Because it's just a SELECT statement.
How to solve this problem? Modify the code as follows:

--Custom exceptions
CREATE OR REPLACE PROCEDURE ex_test (spno number) is
--Define an exception
MYEX EXCEPTION;
BEGIN
--Update User Sal
UPDATE EMP SET sal = sal + EMPNO = spno;
--sql%notfound This means that there is no update
--raise Myex; Trigger Myex
IF Sql%notfound then RAISE MYEX;
END IF;
EXCEPTION
When MYEX and then Dbms_output. Put_Line (' No user update ');
END;
/

Now test again:
Sql> exec ex_test (56);
No users are updated

27. Oracle exception