Oracle Stored Procedure Exception Handling

1. To improve the robustness of the stored procedure and avoid running errors, An Exception Handling Section should be included when a stored procedure is created.
2. Exceptions are PL/SQL identifiers, including predefined exceptions, non-predefined exceptions, and custom exceptions;
3. a predefined exception is a system exception provided by PL/SQL. A non-predefined exception is used to handle Oracle errors unrelated to a predefined exception (such as integrity constraints ); custom exceptions are used to handle other exceptions related to Oracle errors.
4. RAISE_APPLICATION_ERROR is used to customize error messages, and the message number must be in-20000 ~ -20999

Cause of naming system exception
Access_into_null: Undefined object
If no corresponding when exists in case_not_found case
Collection_is_null collection element not initialized
Curser_already_open cursor opened
The column corresponding to the unique index of dup_val_on_index has duplicate values.
Invalid_cursor operates on an invalid cursor
Invalid_number embedded SQL statements cannot convert characters into numbers
No_data_found: If select into is used, no rows are returned, or the application index table is not initialized.
When too_many_rows executes select into, the result set exceeds one row.
The zero_divide divisor is 0.
Subscript_beyond_count element subscript exceeds the maximum value of nested tables or varray
When subscript_outside_limit uses a nested table or varray, the subscript is specified as a negative number.
When value_error is assigned a value, the variable length is insufficient to accommodate actual data.
When the login_denied pl/SQL application connects to the oracle database, an incorrect user name or password is provided.
The not_logged_on pl/SQL application accesses data when it is not connected to the oralce database.
Program_error pl/SQL internal problems, you may need to reinstall the data dictionary & pl./SQL System package
The rowtype_mismatch primary cursor variable is incompatible with the pl/SQL cursor variable return type
When self_is_null uses the object type, the object method is called on the null object.
When storage_error runs pl/SQL, the memory space is exceeded.
Invalid sys_invalid_id rowid string
Timeout_on_resource oracle timeout while waiting for resources

-- Custom exception handling
Create or replace procedure stu_proc
(
-- Multiple IDs are separated by commas (,).
V_id IN NUMBER
) IS
-- Separated by semicolons
V_max_id NUMBER;
V_name VARCHAR2 (20 );
V_raise EXCEPTION;
BEGIN
Select max (a. id) INTO v_max_id FROM student;
IF v_id> v_max_id THEN
RAISE v_raise;
End if;
SELECT o. sname INTO v_name FROM student o WHERE o. id = v_id;
Dbms_output.put_line ('student name: '| v_name );
EXCEPTION
WHEN v_raise THEN
RAISE_APPLICATION_ERROR (-20010, 'v _ id not exists! ');
WHEN NO_DATA_FOUND THEN
RAISE_APPLICATION_ERROR (-20011, 'error: nonexistent! ');
END stu_proc;