PL/SQL EXCEPTION-II

PL/SQL Exception:-

Summary: in this tutorial, you will learn how to handle PL/SQL exception appropriately. In addition, you’ll
also learn how to define your own exception and raise it in your code.
Introducing to PL/SQL Exception
In PL/SQL, any kind of errors is treated as exceptions. An exception is defined as a special condition that
changes the program execution flow. The PL/SQL provides you with a flexible and powerful way to handle
such exceptions.
PL/SQL catches and handles exceptions by using exception handler architecture. Whenever an exception
occurs, it is raised. The current PL/SQL block execution halts, control is passed to a separate section called
exception section.
In the exception section, you can check what kind of exception has been occurred and handle it appropriately.
This exception handler architecture enables separating the business logic and exception handling code hence
make the program easier to read and maintain.
PL/SQL Exception
There are two types of exceptions:
System exception: the system exception is raised by PL/SQL run-time when it detects an error. For
example, NO_DATA_FOUND exception is raised if you select a non-existing record from the database.
Programmer-defined exception: the programmer-defined exception is defined by you in a specific
application. You can map exception names with specific Oracle errors using the
EXCEPTION_INIT pragma. You can also assign a number and description to the exception using
RAISE_APPLICATION_ERROR .
Defining PL/SQL Exception
An exception must be defined before it can be raised. Oracle provides many predefined exceptions in the
STANDARD package. To define an exception you use EXCEPTION keyword as below:
To raise an exception that you’ve defined you use the RAISE statement as follows:
1 EXCEPTION_NAME EXCEPTION;
1 RAISE EXCEPTION_NAME;
In the exception handler section, you use can handle the exception as usual. 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 an exception BELOW_SALARY_RANGE . If the salary is above the
range, we raise the exception ABOVE_SALARY_RANGE just make it simple for demonstration.
In this tutorial, you’ve learned how to define your own PL/SQL exception, raise and handle it in exception
handler section of PL/SQL block.
PL/SQL QUICK START
What is PL/SQL
PL/SQL Setting Up
BASIC PL/SQL TUTORIAL
PL/SQL Block Structure
PL/SQL Variables
PL/SQL Function
PL/SQL Procedure
PL/SQL Nested Block
PL/SQL IF
PL/SQL CASE
123456789
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
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;
/
Previous Tutorial:
PL/SQL GOTO « Next Tutorial:
PL/SQL Record »
HOME PROCEDURE FUNCTION CURSOR RECORD PACKAGE