Oracle Exception Handling

1. pre-defined exception handling

Example 1: update the specified employee's salary. If the employee's salary is smaller than 1500, the employee's salary is increased by 100;
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 + 100 WHERE employee_id = v_empno;
DBMS_OUTPUT.PUT_LINE ('Code: '| v_empno |' employee salary updated! ');
ELSE
DBMS_OUTPUT.PUT_LINE ('Code: '| v_empno |' the employee's salary has exceeded the specified value! ');
End if;
EXCEPTION
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE ('the database is not encoded as '| v_empno |' employee ');
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE ('program running error! Use the cursor ');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLCODE | '---' | SQLERRM );
END;

2. perform the following steps to handle non-predefined exceptions (to handle such exceptions, you must first define non-defined ORACLE errors:

1. Define exceptions in the definition section of PL/SQL blocks (<EXCEPTION situation> EXCEPTION ;)
2. Associate the defined exception with a standard ORACLE error and use the EXCEPTION_INIT statement:
PRAGMA EXCEPTION_INIT (<exception status>, <error code> );
3. handle exceptions in PL/SQL blocks.

Example 2: Delete the records of a specified department to ensure that the Department has no employees.
Insert into attributes VALUES (50, 'Finance ', 'Chicago ');
DECLARE
V_deptno parameters. department_id % TYPE: = & deptno;
Deptno_remaining EXCEPTION;
PRAGMA EXCEPTION_INIT (deptno_remaining,-2292 );
/*-2292 is an error code that violates consistency constraints */
BEGIN
Delete from orders ments WHERE department_id = v_deptno;
EXCEPTION
WHEN deptno_remaining THEN
DBMS_OUTPUT.PUT_LINE ('violation of data integrity constraints! ');
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE (SQLCODE | '---' | SQLERRM );
END;

3. Custom Exception Handling

1. Define exceptions in the definition section of PL/SQL blocks:
<EXCEPTION situation> EXCEPTION; 2. RAISE <EXCEPTION situation>; 3. handle exceptions in PL/SQL blocks.

Example 3: update the salary of a specified employee, increasing by 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;

4. User-defined exception handling

Example 4: Create a get_salary function to retrieve the total salary of a specified department. The-20991 and-20992 errors are defined, handle two types of errors: Empty parameter and invalid department code:
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 null ');
ELSIF p_deptno <0 THEN
RAISE_APPLICATION_ERROR (-20992, 'invalid Department Code ');
ELSE
Select sum (employees. salary) INTO 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 ('10 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: '| 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;

)