Exception Handling in PL/SQL

Index - PL/SQL Tutorial

Introduction to PL/SQL
Chap 1 : Block structure and Advantages of PL/SQL
Chap 2 : PL/SQL Variables and Constants
Chap 3 : PL/SQL Records
Chap 4 : PL/SQL IF ELSE statement / Conditional statements
Chap 5 : PL/SQL FOR and WHILE LOOP / Iterative Statements
Chap 6 : Cursors in PL/SQL
Chap 7 : PL/SQL Procedures
Chap 8 : PL/SQL Functions
Chap 9 : Parameters-Procedure and Function in PL/SQL
Chap 10 : What are PL/SQL Triggers
Chap 11 : Exception Handling in PL/SQL

In PL/SQL, any kind of errors are treated as exceptions. An exception is defined as a special condition that change the program execution flow.

In PL/SQL there are two types of exceptions:
1)Internal exceptions that occur when a PL/SQL block does not comply with a rule of the server
2)External user-defined exceptions, which are declared in section declarativa block, subroutine or package and which are activated explicitly in the executabila block, PL/SQL.

Structure of Exception Handling.
The General Syntax for coding the exception section

DECLARE
Declaration section
BEGIN
Exception section
EXCEPTION
WHEN ex_name1 THEN
-Error handling statements
WHEN ex_name2 THEN
-Error handling statements
WHEN Others THEN
-Error handling statements
END;

Example :
The following example illustrates the programmer-defined exceptions. We get the salary of an employee and check it with the job’s salary range. If the salary is below the range, we raise exception BELOW_SALARY_RANGE. If the salary is above the range, we raise exception ABOVE_SALARY_RANGE.

SET SERVEROUTPUT ON SIZE 100000;
DECLARE
-- define exceptions
BELOW_SALARY_RANGE EXCEPTION;
ABOVE_SALARY_RANGE EXCEPTION;
-- salary variables
n_salary employees.salary%TYPE;
n_min_salary employees.salary%TYPE;
n_max_salary employees.salary%TYPE;
-- input employee id
n_emp_id employees.employee_id%TYPE := &emp_id;
BEGIN
SELECT salary,
min_salary,
max_salary
INTO n_salary,
n_min_salary,
n_max_salary
FROM employees
INNER JOIN jobs ON jobs.job_id = employees.job_id
WHERE employee_id = n_emp_id;

IF n_salary < n_min_salary THEN
RAISE BELOW_SALARY_RANGE;
ELSIF n_salary > n_max_salary THEN
RAISE ABOVE_SALARY_RANGE;
END IF;

DBMS_OUTPUT.put_line('Employee ' || n_emp_id ||
' has salary $' || n_salary );

EXCEPTION
WHEN BELOW_SALARY_RANGE THEN
DBMS_OUTPUT.put_line('Employee ' || n_emp_id ||
' has salary below the salary range');
WHEN ABOVE_SALARY_RANGE THEN
DBMS_OUTPUT.put_line('Employee ' || n_emp_id ||
' has salary above the salary range');
WHEN NO_DATA_FOUND THEN
DBMS_OUTPUT.PUT_LINE('Employee ' || n_emp_id || ' not found');
END;
/