Faculty of Polytechnic, Akole

CH.4 PL/SQL Programming

Chapter 4 Marks 16
PL/SQL Programming

Introduction

PL/SQL, the Oracle procedural language extension of SQL, is a completely portable, high-
performance transaction-processing language.

Main Features of PL/SQL

PL/SQL combines the data-manipulating power of SQL with the processing power of procedural
languages.

When a problem can be solved using SQL, you can issue SQL statements from your PL/SQL
programs, without learning new APIs.

Like other procedural programming languages, PL/SQL lets you declare constants and variables,
control program flow, define subprograms, and trap run-time errors.

You can break complex problems into easily understandable subprograms, which you can reuse in
multiple applications.

Advantages of PL/SQL:

PL/SQL has following advantages:

High Performance

With PL/SQL, an entire block of statements can be sent to the database at one time. This can drastically
reduce network traffic between the database and an application.

High Productivity

PL/SQL lets you write very compact code for manipulating data. In the same way that scripting
languages such as PERL can read, transform, and write data from files, PL/SQL can query, transform, and
update data in a database. PL/SQL saves time on design and debugging by offering a full range of software-
engineering features, such as exception handling, encapsulation, data hiding, and object-oriented data types.
PL/SQL extends tools such as Oracle Forms. With PL/SQL in these tools, you can use familiar language
constructs to build applications. For example, you can use an entire PL/SQL block in an Oracle Forms trigger,
instead of multiple trigger steps, macros, or user exits. PL/SQL is the same in all environments. After you learn
PL/SQL with one Oracle tool, you can transfer your knowledge to other tools.

Full Portability

Applications written in PL/SQL can run on any operating system and platform where the database runs.
With PL/SQL, you can write portable program libraries and reuse them in different environments.

1 DMS(22319)
 Faculty of Polytechnic, Akole

CH.4 PL/SQL Programming

Tight Security

PL/SQL stored subprograms move application code from the client to the server, where you can protect
it from tampering, hide the internal details, and restrict who has access. For example, you can grant users access
to a subprogram that updates a table, but not grant them access to the table itself or to the text of the UPDATE
statement. Triggers written in PL/SQL can control or record changes to data, making sure that all changes obey
your business rules.

Access to Predefined Packages

Oracle provides product-specific packages that define APIs you can invoke from PL/SQL to perform
many useful tasks. These packages include DBMS_ALERT for using triggers, DBMS_FILE for reading and
writing operating system text files, UTL_HTTP for making hypertext transfer protocol (HTTP) callouts,
DBMS_OUTPUT for display output from PL/SQL blocks and subprograms, and DBMS_PIPE for
communicating over named pipes.

Support for Object-Oriented Programming

Object types are an ideal object-oriented modelling tool, which you can use to reduce the cost and time
required to build complex applications. Besides enabling you to create software components that are modular,
maintainable, and reusable, object types allow different teams of programmers to develop software components
concurrently.
By encapsulating operations with data, object types let you move data-maintenance code out of SQL scripts and
PL/SQL blocks into methods. Also, object types hide implementation details, so that you can change the details
without affecting client programs.

Support for Developing Web Applications and Server Pages

You can use PL/SQL to develop Web applications and Server Pages (PSPs).

PL/SQL Blocks

The basic unit of a PL/SQL source program is the block, which groups related declarations and
statements. A PL/SQL block is defined by the keywords DECLARE, BEGIN, EXCEPTION, and END.
These keywords partition the block into a declarative part, an executable part, and an exception-handling
part. Only the executable part is required. Declarations are local to the block and cease to exist when the block
completes execution, helping to avoid cluttered namespaces for variables and subprograms. Blocks can be
nested: Because a block is an executable statement, it can appear in another block wherever an executable
statement is allowed.

PL/SQL Block Structure

DECLARE -- Declarative part (optional)

-- Declarations of local types, variables, & subprograms
BEGIN -- Executable part (required)
-- Statements (which can use items declared in declarative part)
EXCEPTION -- Exception-handling part (optional)
-- Exception handlers for exceptions raised in executable part
END; -- (required)

2 DMS(22319)
 Faculty of Polytechnic, Akole

CH.4 PL/SQL Programming

A PL/SQL block can be submitted to an interactive tool (such as SQL*Plus or Enterprise Manager) or
embedded in an Oracle Precompiler or OCI program. The interactive tool or program executes the block only
once. The block is not stored in the database.

PL/SQL Engine

The PL/SQL compilation and run-time system is an engine that compiles and executes PL/SQL units.
The engine can be installed in the database or in an application development tool, such as Oracle
Forms. In either environment, the PL/SQL engine accepts as input any valid PL/SQL unit. The engine
executes procedural statements, but sends SQL statements to the SQL engine in the database, as
shown in Figure 5.1

Figure 5.1 PL/SQL Engine

Typically, the database processes PL/SQL units. When an application development tool processes
PL/SQL units, it passes them to its local PL/SQL engine. If a PL/SQL unit contains no SQL
statements, the local engine processes the entire PL/SQL unit. This is useful if the application
development tool can benefit from conditional and iterative control.
For example, Oracle Forms applications frequently use SQL statements to test the values of field
entries and do simple computations. By using PL/SQL instead of SQL, these applications can avoid
calls to the database.

PL/SQL Variables and Constants

PL/SQL lets you declare variables and constants, and then use them in SQL and procedural
statements anywhere an expression can be used. You must declare a variable or constant before
referencing it in any other statements.

3 DMS(22319)
 Faculty of Polytechnic, Akole

CH.4 PL/SQL Programming

Declaring PL/SQL Variables

A PL/SQL variable can have any SQL data type (such as CHAR, DATE, or NUMBER) or a PL/SQL-
only data type (such as BOOLEAN or PLS_INTEGER).

Example

a number;
empname varchar2(10);

Assigning Values to Variables

You can assign a value to a variable in the following ways:
• With the assignment operator (:=).

a:=10

• By selecting (or fetching) database values into it

Select ename into empname

From emp;

Declaring PL/SQL Constants

Declaring a PL/SQL constant is like declaring a PL/SQL variable except that you must add the
keyword CONSTANT and immediately assign a value to the constant.
example:
pi CONSTANT number := 3.14;

5.2 PL/SQL Control Structures:

Procedural computer programs use the basic control structures shown in Figure 5.2

Figure 5.2 Control Structures

4 DMS(22319)
 Faculty of Polytechnic, Akole

CH.4 PL/SQL Programming

The selection structure tests a condition, then executes one sequence of statements instead of
another, depending on whether the condition is true or false. A condition is any variable or expression
that returns a BOOLEAN value.
The iteration structure executes a sequence of statements repeatedly as long as a condition
holds true.
The sequence structure simply executes a sequence of statements in the order in which they
occur.

5.2.1 Conditional Control (IF and CASE Statements):

The IF statement executes a sequence of statements depending on the value of a condition. There
are three forms of IF statements: IF-THEN, IF-THEN-ELSE, and IF-THEN-ELSIF.

Using the IF-THEN Statement

The simplest form of IF statement associates a condition with a sequence of statements enclosed by
the keywords THEN and END IF (not ENDIF) shown in following example

declare
a number :=5;
b number :=10;
begin
if a > b then
dbms_output.put_line('a is greater ' || a);
end if;
end;

The Result is

Statement processed.

The sequence of statements is executed only if the condition is TRUE. If the condition is FALSE or
NULL, the IF statement does nothing. In either case, control passes to the next statement.

Using the IF-THEN-ELSE Statement

The second form of IF statement adds the keyword ELSE followed by an alternative sequence of
statements, as shown in following example

declare
a number :=5;
b number :=10;
begin
if a > b then
dbms_output.put_line('a is greater ' || a);
else
dbms_output.put_line('b is greater ' || b);
end if;
end;

Result is
b is greater 10

Statement processed.
5 DMS(22319)
 Faculty of Polytechnic, Akole

CH.4 PL/SQL Programming

The statements in the ELSE clause are executed only if the condition is FALSE or NULL. The IF-
THEN-ELSE statement ensures that one or the other sequence of statements is executed.

Using the IF-THEN-ELSIF Statement

Sometimes you want to choose between several alternatives. You can use the keyword ELSIF (not
ELSEIF or ELSE IF) to introduce additional conditions, as shown in following example

declare
a number :=5;
b number :=10;
c number :=15;
begin
if a > b and a > c then
dbms_output.put_line('a is greater ' || a);
elsif b > a and b > c then
dbms_output.put_line('b is greater ' || b);
else
dbms_output.put_line('c is greater ' || c);
end if;
end;
The result is

c is greater 15

Statement processed.

If the first condition is FALSE or NULL, the ELSIF clause tests another condition. An IF statement can
have any number of ELSIF clauses; the final ELSE clause is optional. Conditions are evaluated one
by one from top to bottom. If any condition is TRUE, its associated sequence of statements is
executed and control passes to the next statement. If all conditions are false or NULL, the sequence
in the ELSE clause is executed as shown in above example.

Using the Simple CASE Statement

Like the IF statement, the CASE statement selects one sequence of statements to execute. However,
to select the sequence, the CASE statement uses a selector rather than multiple Boolean
expressions. A selector is an expression whose value is used to select one of several alternatives.

DECLARE
grade CHAR(1);
BEGIN
grade := 'B';

CASE grade
WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Very Good');
WHEN 'C' THEN DBMS_OUTPUT.PUT_LINE('Good');
WHEN 'D' THEN DBMS_OUTPUT.PUT_LINE('Fair');
WHEN 'F' THEN DBMS_OUTPUT.PUT_LINE('Poor');
ELSE DBMS_OUTPUT.PUT_LINE('No such grade');
END CASE;
END;

The result is
Very Good

6 DMS(22319)
 Faculty of Polytechnic, Akole

CH.4 PL/SQL Programming

Statement processed.

The CASE statement is more readable and more efficient. When possible, rewrite lengthy IF-
THEN-ELSIF statements as CASE statements. The CASE statement begins with the keyword CASE.
The keyword is followed by a selector, which is the variable grade in the last example. The selector
expression can be arbitrarily complex. For example, it can contain function calls. Usually, however, it
consists of a single variable. The selector expression is evaluated only once. The value it yields can
have any PL/SQL data type other than BLOB, BFILE, an object type, a PL/SQL record, an index-by-
table, a array, or a nested table.

5.2.2 Iterative Control (LOOP, EXIT, and CONTINUE Statements):

A LOOP statement executes a sequence of statements multiple times. PL/SQL provides the following
loop statements:
• Basic LOOP
• WHILE LOOP
• FOR LOOP

To exit a loop, PL/SQL provides the following statements:

• EXIT
• EXIT-WHEN

You can put EXIT and CONTINUE statements anywhere inside a loop, but not outside a loop. To
complete a PL/SQL block before it reaches its normal end, use the RETURN statement.

Using the Basic LOOP Statement

The simplest LOOP statement is the basic loop, which encloses a sequence of statements between
the keywords LOOP and END LOOP, as follows:

LOOP
sequence_of_statements;

END LOOP;

With each iteration of the loop, the sequence of statements is executed, then control resumes at the
top of the loop. You can use CONTINUE and CONTINUE-WHEN statements in a basic loop, but to
prevent an infinite loop, you must use an EXIT or EXIT-WHEN statement.

Using the EXIT-WHEN Statement

When an EXIT-WHEN statement is encountered, the condition in the WHEN clause is evaluated. If the
condition is true, the loop completes and control passes to the statement immediately after END LOOP. Until
the condition is true, the EXIT-WHEN statement acts like a NULL statement (except for the evaluation of its
condition) and does not terminate the loop. A statement inside the loop must change the value of the condition,
as in shown in following example
DECLARE
a NUMBER;
BEGIN
a:=1;
LOOP
DBMS_OUTPUT.PUT_LINE(a);
7 DMS(22319)
 Faculty of Polytechnic, Akole

CH.4 PL/SQL Programming

a:=a+1;
EXIT WHEN a>3;
END LOOP;
END;

1
2
3

Statement processed.

Using the FOR-LOOP Statement

Simple FOR loops iterate over a specified range of integers (lower_bound .. upper_bound). The
number of iterations is known before the loop is entered. The range is evaluated when the FOR loop
is first entered and is never re-evaluated. If lower_ bound equals upper_bound, the loop body is
executed once.
DECLARE
a NUMBER;
BEGIN
a:=1;
FOR a IN 1..3 LOOP
DBMS_OUTPUT.PUT_LINE(a);
END LOOP;
END;

1
2
3

Statement processed.

By default, iteration proceeds upward from the lower bound to the higher bound. If you use the
keyword REVERSE, iteration proceeds downward from the higher bound to the lower bound. After
each iteration, the loop counter is decremented. You still write the range bounds in ascending (not
descending) order.
DECLARE
a NUMBER;
BEGIN
a:=1;
FOR a IN REVERSE 1..3 LOOP
DBMS_OUTPUT.PUT_LINE(a);
END LOOP;
END;
3
2
1

Statement processed.

============Print factorial of 1 to 5 numbers using Nested For Loop======

DECLARE
i NUMBER;
j NUMBER;
fact NUMBER;
BEGIN
FOR i IN 1..5 LOOP
8 DMS(22319)
 Faculty of Polytechnic, Akole

CH.4 PL/SQL Programming

fact :=1;
FOR j IN 1..i LOOP
fact := fact * j;
END LOOP;
DBMS_OUTPUT.PUT_LINE ('fact of ' || i || ' is ' || fact);
END LOOP;
END;

fact of 1 is 1
fact of 2 is 2
fact of 3 is 6
fact of 4 is 24
fact of 5 is 120

Statement processed.
OR
==================== Print factorial of 1 to 5 numbers using For Loop=============
DECLARE
i NUMBER;
j NUMBER;
fact NUMBER;
BEGIN
fact := 1;
i := 1;
FOR i IN 1..5 LOOP
fact := fact * i;
DBMS_OUTPUT.PUT_LINE ('fact of ' || i || ' is ' || fact);
END LOOP;
END;

fact of 1 is 1
fact of 2 is 2
fact of 3 is 6
fact of 4 is 24
fact of 5 is 120

Statement processed.

Sequential Control (GOTO and NULL Statements)

Unlike the IF and LOOP statements, the GOTO and NULL statements are not crucial to PL/SQL
programming. The GOTO statement is seldom needed. Occasionally, it can simplify logic enough to
warrant its use. The NULL statement can improve readability by making the meaning and action of
conditional statements clear.

Overuse of GOTO statements can result in code that is hard to understand and maintain. Use GOTO
statements sparingly. For example, to branch from a deeply nested structure to an error-handling
routine, raise an exception rather than use a GOTO statement.

Using the GOTO Statement

The GOTO statement branches to a label unconditionally. The label must be unique within its scope
and must precede an executable statement or a PL/SQL block. When executed, the GOTO statement
transfers control to the labeled statement or block.
DECLARE
i NUMBER;
9 DMS(22319)
 Faculty of Polytechnic, Akole

CH.4 PL/SQL Programming

BEGIN
i:=1;
<<get_no>>
DBMS_OUTPUT.PUT_LINE(i);
i := i+1;
IF i <= 3 THEN
GOTO get_no;
END IF;
END;

1
2
3

Statement processed.
GOTO Statement Restrictions
• A GOTO statement cannot branch into an IF statement, CASE statement, LOOP statement, or
sub-block.
• A GOTO statement cannot branch from one IF statement clause to another, or from one CASE
statement WHEN clause to another.
• A GOTO statement cannot branch from an outer block into a sub-block (that is, an inner
BEGIN-END block).
• A GOTO statement cannot branch out of a subprogram. To end a subprogram early, either use
the RETURN statement or have GOTO branch to a place right before the end of the
subprogram.
• A GOTO statement cannot branch from an exception handler back into the current BEGIN-
END block. However, a GOTO statement can branch from an exception handler into an
enclosing block

Using the NULL Statement

The NULL statement does nothing except pass control to the next statement. Some languages refer to such an
instruction as a no-op (no operation).
== Program to display the given number if it is divisible by 10 else no action should take place==
DECLARE
no NUMBER := 20;
BEGIN
IF (mod (no,10) = 0 ) THEN
DBMS_OUTPUT.PUT_LINE (no || ' is divisible by 10');
ELSE
NULL;
END IF;
END;

20 is divisible by 10

Statement processed.

You can use the NULL statement to indicate that you are aware of a possibility, but that no action is necessary.

Managing Cursors in PL/SQL

PL/SQL uses implicit and explicit cursors. PL/SQL declares a cursor implicitly for all SQL data
manipulation statements, including queries that return only one row.

10 DMS(22319)
 Faculty of Polytechnic, Akole

CH.4 PL/SQL Programming

Implicit cursors are called SQL cursors. If you want precise control over query processing,
you can declare an explicit cursor in the declarative part of any PL/SQL block, subprogram, or
package. You must declare explicit cursors for queries that return more than one row.

SQL Cursors (Implicit)

SQL cursors are managed automatically by PL/SQL. You need not write code to handle these
cursors. However, you can track information about the execution of an SQL cursor through its
attributes.

• %FOUND Attribute: Has a DML Statement Changed Rows?

• %ISOPEN Attribute: Always FALSE for SQL Cursors
• %NOTFOUND Attribute: Has a DML Statement Failed to Change Rows?
• %ROWCOUNT Attribute: How Many Rows Affected So Far?

%FOUND Attribute: Until a SQL data manipulation statement is executed, %FOUND yields NULL.
Thereafter, %FOUND yields TRUE if an INSERT, UPDATE, or DELETE statement affected one or
more rows, or a SELECT INTO statement returned one or more rows. Otherwise, %FOUND yields
FALSE.

%ISOPEN Attribute: The database closes the SQL cursor automatically after executing its
associated SQL statement. As a result, %ISOPEN always yields FALSE.

%NOTFOUND Attribute: %NOTFOUND is the logical opposite of %FOUND. %NOTFOUND yields

TRUE if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO
statement returned no rows. Otherwise, %NOTFOUND yields FALSE.

%ROWCOUNT Attribute: %ROWCOUNT yields the number of rows affected by an INSERT,

UPDATE, or DELETE statement, or returned by a SELECT INTO statement. %ROWCOUNT yields 0
if an INSERT, UPDATE, or DELETE statement affected no rows, or a SELECT INTO statement
returned no rows.

create table emp_temp as select * from emp

Table created.
========Print number of rows deleted from emp_temp table=================
begin
delete from emp_temp;
dbms_output.put_line('No Of rows deleted are ' || sql%rowcount);
end;

No Of rows deleted are 7

Statement processed.

Explicit Cursors

When you need precise control over query processing, you can explicitly DECLARE a cursor in the
declarative part of any PL/SQL block, subprogram, or package.
11 DMS(22319)
 Faculty of Polytechnic, Akole

CH.4 PL/SQL Programming

You use three statements to control a cursor: OPEN, FETCH, and CLOSE. First, you initialize the
cursor with the OPEN statement, which identifies the result set. Then, you can execute FETCH
repeatedly until all rows have been retrieved, or you can use the BULK COLLECT clause to fetch all
rows at once. When the last row has been processed, you release the cursor with the CLOSE
statement.

Attributes of Explicit Cursors

Every explicit cursor and cursor variable has four attributes: %FOUND, %ISOPEN %NOTFOUND,
and %ROWCOUNT.
• %FOUND Attribute: Has a Row Been Fetched?
• %ISOPEN Attribute: Is the Cursor Open?
• %NOTFOUND Attribute: Has a Fetch Failed?
• %ROWCOUNT Attribute: How Many Rows Fetched So Far?

%FOUND Attribute: After a cursor or cursor variable is opened but before the first fetch, %FOUND
returns NULL. After any fetches, it returns TRUE if the last fetch returned a row, or FALSE if the last
fetch did not return a row.

%ISOPEN Attribute: %ISOPEN returns TRUE if its cursor or cursor variable is open; otherwise,
%ISOPEN returns FALSE.

%NOTFOUND Attribute: %NOTFOUND is the logical opposite of %FOUND. %NOTFOUND yields

FALSE if the last fetch returned a row, or TRUE if the last fetch failed to return a row.

%ROWCOUNT Attribute: When its cursor or cursor variable is opened, %ROWCOUNT is zeroed.
Before the first fetch, %ROWCOUNT yields zero. Thereafter, it yields the number of rows fetched so
far. The number is incremented if the last fetch returned a row.

=========Display information from a table===========

declare
cursor c1 is select empno,ename from emp;
emp_rec c1%rowtype;
begin
open c1;
loop
fetch c1 into emp_rec;
exit when c1%notfound;
dbms_output.put_line(emp_rec.empno ||' ' || emp_rec.ename);
end loop;
close c1;
end;

12 DMS(22319)
 Faculty of Polytechnic, Akole

CH.4 PL/SQL Programming

===========Display names of top 5 highest paid salary employees=============

declare
cursor c_hisal is select ename,salary from emp order by salary desc;
sal_temp c_hisal%rowtype;
begin
open c_hisal;
loop
fetch c_hisal into sal_temp;
dbms_output.put_line(sal_temp.ename || ' ' || sal_temp.salary);
exit when c_hisal%rowcount = 5;
end loop;
close c_hisal;
end;

SQL Cursor FOR LOOP

With PL/SQL, it is very simple to issue a query, retrieve each row of the result into a %ROWTYPE
record, and process each row in a loop:
• You include the text of the query directly in the FOR loop.
• PL/SQL creates a record variable with fields corresponding to the columns of the result set.
• You refer to the fields of this record variable inside the loop. You can perform tests and
calculations, display output, or store the results somewhere else.

========Display the name and job of employees with manager Ids greater than 120.========

BEGIN
FOR item IN
( SELECT ename, job FROM emp
WHERE job LIKE '%CLERK%' AND mgr > 120 )
LOOP
DBMS_OUTPUT.PUT_LINE
('Name = ' || item.ename || ', Job = ' || item.job);
END LOOP;
END;

Before, each iteration of the FOR loop, PL/SQL fetches into the implicitly declared record. The
sequence of statements inside the loop is executed once for each row that satisfies the query. When
you leave the loop, the cursor is closed automatically. The cursor is closed even if you use an EXIT or
GOTO statement to leave the loop before all rows are fetched, or an exception is raised inside the
loop.

Explicit Cursor FOR LOOP

If you must reference the same query from different parts of the same subprogram, you can declare a
cursor that specifies the query, and process the results using a FOR loop.
DECLARE
CURSOR c1 IS SELECT ename, job FROM emp;
BEGIN
FOR item IN c1 LOOP
DBMS_OUTPUT.PUT_LINE ('Name = ' || item.ename || ', Job = ' || item.job);
END LOOP;
END;

13 DMS(22319)
 Faculty of Polytechnic, Akole

CH.4 PL/SQL Programming

Handling PL/SQL Errors

PL/SQL run-time errors can arise from design faults, coding mistakes, hardware failures, and many
other sources. You cannot anticipate all possible errors, but you can code exception handlers that
allow your program to continue to operate in the presence of errors.

In PL/SQL, an error condition is called an exception. An exception can be either internally defined
(by the run-time system) or user-defined.

Internal exceptions are raised implicitly (automatically) by the run-time system. User-defined
exceptions must be raised explicitly by RAISE statements or invocations of the procedure
RAISE_APPLICATION_ERROR.

To handle raised exceptions, you write separate routines called exception handlers. After an
exception handler runs, the current block stops executing and the enclosing block resumes with the
next statement. If there is no enclosing block, control returns to the host environment.

Predefined PL/SQL Exceptions

An internal exception is raised automatically if your PL/SQL program violates a database rule or
exceeds a system-dependent limit. PL/SQL predefines some common ORA-n errors as exceptions.
For example, PL/SQL raises the predefined exception NO_DATA_FOUND if a SELECT INTO
statement returns no rows.

Exception Name ORA SQLCODE Raised When ...

Error
NO_DATA_FOUND -01403 +100 A SELECT INTO statement returns no rows, or
your program references a deleted element in a
nested table or an uninitialized element in an
index-by table.

TOO_MANY_ROWS -01422 -1422 A SELECT INTO statement returns more than one
row TOO_MANY_ROWS.

ZERO_DIVIDE -01476 -1476 A program attempts to divide a number by zero.

Defining Your Own PL/SQL Exceptions

PL/SQL lets you define exceptions of your own. Unlike a predefined exception, a user-defined
exception must be declared and then raised explicitly, using either a RAISE statement or the
procedure RAISE_APPLICATION_ERROR.

Declaring PL/SQL Exceptions

Exceptions can be declared only in the declarative part of a PL/SQL block, subprogram, or package.
You declare an exception by introducing its name, followed by the keyword EXCEPTION. In the
following example, you declare an exception named past_due:
DECLARE
14 DMS(22319)
 Faculty of Polytechnic, Akole

past_due EXCEPTION; CH.4 PL/SQL Programming

Exception and variable declarations are similar. But remember, an exception is an error condition, not
a data item. Unlike variables, exceptions cannot appear in assignment statements or SQL
statements. However, the same scope rules apply to variables and exceptions.
declare
negative_sal exception;
t_sal emp.salary%type;
begin
select salary into t_sal from emp where ename = 'A1';
if t_sal < 0 then
raise negative_sal;
end if;
exception
when no_data_found then
dbms_output.put_line('Employee Name does not exist' );
when negative_sal then
dbms_output.put_line('Negative salary exist' );
end;

Negative salary exist

Statement processed.

Defining Your Own Error Messages (RAISE_APPLICATION_ERROR Procedure)

The RAISE_APPLICATION_ERROR procedure lets you issue user-defined ORA-n error messages from
stored subprograms. That way, you can report errors to your application and avoid returning
unhandled exceptions.

To invoke RAISE_APPLICATION_ERROR, use the following syntax:

raise_application_error(error_number, message);

where
error_number is a negative integer in the range -20000 to -20999
and
message is a character string up to 2048 bytes long.

declare
t_sal emp.salary%type;
begin
select salary into t_sal from emp where ename = 'A1';
if t_sal < 0 then
raise_application_error (-20999,'Negative Salary');
end if;

15 DMS(22319)
 Faculty of Polytechnic, Akole

CH.4 PL/SQL Programming

exception
when no_data_found then
dbms_output.put_line('Employee Name does not exist' );
end;

ORA-20999: Negative Salary

Procedure

Procedure is a subprogram which consists of a set of SQL statement. Procedures are not very
different from functions. A procedure or function is a logically grouped set of SQL and PL/SQL
statements that perform a specific task.

To make a procedure or function dynamic either of them can be passed parameters before execution.
A procedure or function can then change the way it works depending upon the parameters passed
prior to its execution.

Procedure and function are made op of a declarative part, an executable part and an optional
execution handling part.

• Declarative part: It consists of cursors, constants, variables, exception, and subprograms.

These objects are local execution and to the procedure.
• An executable part: This contains PL/SQL block consisting of statements that assign values,
control execution and manipulate ORACLE data. Action to be performed and values to be
returned from here. Variables declared are used in this block
• Optional exception handling part-This part contains code that performs an action to deal with
exception raised during the execution of the executable part. The exceptions could be oracle
own exception or declared in declarative part.

The CREATE PROCEDURE statement creates or replaces a standalone stored procedure or a call
specification. A standalone stored procedure is a procedure (a subprogram that performs a specific
action) that is stored in the database.

Syntax for stored procedure:

CREATE or REPLACE PROCEDURE [schema] procedure_name (argument {IN, OUT, IN OUT}

data type) {IS|AS}
Variable declarations; constant declaration;
BEGIN
PL/SQL subprogram body;
EXCEPTION
PL /SQL EXCEPTION BLOCK;
END;

OR REPLACE
16 DMS(22319)
 Faculty of Polytechnic, Akole

CH.4 PL/SQL Programming

Specify OR REPLACE to re-create the procedure if it already exists. Use this clause to change the
definition of an existing procedure without dropping, re-creating, and regranting object privileges
previously granted on it. If you redefine a procedure, then the database recompiles it.

SCHEMA
Specify the schema to contain the procedure. If you omit schema, then the database creates the
procedure in your current schema.

PROCEDURE_NAME
Specify the name of the procedure to be created.

ARGUMENT
Is the name of an argument to the procedure or function, parentheses can be omitted if no arguments
are present

IN
Specifies that a value for the argument must be specified when calling the procedure or function

OUT
Specifies that, after execution, the procedure passes a value for this argument back to its calling
environment

IN OUT
Specifies that a value for the argument must be specified when calling the procedure and that the
procedure passes a value for this argument back to its calling environment after execution. By default
it takes IN.

DATA TYPE: is the data type of an argument.

DECLARE_SECTION
The optional declarative part of the procedure. Declarations are local to the procedure can be
referenced in body, and cease to exist when the procedure completes execution.

BODY
The required executable part of the procedure and, optionally, the exception-handling part of the
procedure.

EXAMPLE

=========== Write a procedure to find square of a given number ===========

CREATE or REPLACE PROCEDURE getsquare (x in number ) IS

BEGIN
DBMS_OUTPUT.PUT_LINE (x*x);
END;

Procedure created.

============= To call procedure getsquare =============

17 DMS(22319)
 Faculty of Polytechnic, Akole

BEGIN

18 DMS(22319)
 Faculty of Polytechnic, Akole
CH.4 PL/SQL Programming
getsquare(2);
END;

Statement processed.

============= Write a procedure to find salary of a given employee =============

create or replace procedure getsal (name in varchar2, sal out number ) is
begin
select salary into sal from emp where ename = name;
end;

============= Call getsal procedure =============

declare
name emp.ename%type;
sal emp.salary%type;
begin
name := 'A1';
getsal(name,sal);
dbms_output.put_line( sal);
end;

10000

Statement processed.

Advantages of procedure

• Security: It enforces data security. For example you can grant access to a procedure rather
that can query a table rather than granting access to table itself
• Performance: Precompiled code hence no compilation is required to execute a code. The
procedure is present in shared pool of SGA (system global area) hence no disk I/O required.
Network traffic is reduced.
• Memory allocation: As procedure is loaded in SGA only one copy is required to access
multiple users
• Productivity: Single procedure can avoid redundant coding and increases productivity.
• Integrity and accuracy: As procedure is needed to be tested only once hence guarantee of
accurate result.

19 DMS(22319)
 Faculty of Polytechnic, Akole

CH.4 PL/SQL Programming

Removing a procedure

A procedure can be deleted using the command

DROP PROCEDURE <PROCEDURE_NAME>;

EXAMPLE

Drop procedure getsal;

Function

Functions are named PL/SQL block that can take parameter, perform an action and return a value to
host environment, function can return only one value.

CREATE or REPLACE FUNCTION [schema] FUNCTION_NAME (argument IN data type)

RETURN data type {IS|AS} argument data type
Variable declarations; constant declaration;
BEGIN
PL/SQL subprogram body;
Return argument;
EXCEPTION
PL /SQL EXCEPTION BLOCK;
END;
===== Write a function to find area of circle =======

create or replace function circle_area ( r in number) return number

is area number;
begin
area := 3.14 * r * r;
return area;
end;
======= Call circle_area function =======

select circle_area (2) from dual;

CIRCLE_AREA(2)
12.56

Removing a Function

Drop function < function name>;

EXAMPLE

Drop function circle_area;

20 DMS(22319)
 Faculty of Polytechnic, Akole
CH.4 PL/SQL Programming
Function V/s Procedures

Conceptually function and procedures are very similar in syntax, execution and advantages with only
difference that function can return a value to host environment but procedures cannot. A function
must return a value back to the caller. By defining multiple out parameters in a procedure, multiple
values can be passed to the caller. But, a function can return only one value to the calling PL/SQL
block. The out variable being globle by nature, its value is accessible by any PL/SQL code block
including the calling PL/SQL block

Triggers

A trigger is a named program unit that is stored in the database and fired (executed) in response to a
specified event. The specified event is associated with a table, a view, a schema, or the database,
and it is one of the following:
• A database manipulation (DML) statement (DELETE, INSERT, or UPDATE)
• A database definition (DDL) statement (CREATE, ALTER, or DROP)
• A database operation (SERVERERROR, LOGON, LOGOFF, STARTUP, or SHUTDOWN)

The trigger is said to be defined on the table, view, schema, or database. Triggers are similar to
stored procedure but stored procedures are called explicitly and triggers are called implicitly by oracle
when the concerned event occurs.

Trigger Types

A DML trigger is fired by a DML statement, a DDL trigger is fired by a DDL statement, a DELETE
trigger is fired by a DELETE statement, and so on.
A system trigger is defined on a schema or the database. A trigger defined on a schema fires for each
event associated with the owner of the schema (the current user).
A trigger defined on a database fires for each event associated with all users. A simple trigger can fire
at exactly one of the following timing points:
• Before the triggering statement executes
• After the triggering statement executes
• Before each row that the triggering statement affects
• After each row that the triggering statement affects

A compound trigger can fire at more than one timing point. Compound triggers make it easier to
program an approach where you want the actions you implement for the various timing points to
share common data.

Types of triggers:
• Statement level trigger: A statement trigger is fired only for once for a DML statement
irrespective of the number of rows affected by the statement. Statement level trigger is the
default type of trigger.
• Row level trigger: A row trigger is fired once for each row that is affected by DML command if
an update command updates 100 rows then row level trigger is fired 100 times whereas a
statement level trigger is fired only for once.
• Before triggers: While defining a trigger you can specify whether the trigger is to be fired
before the command (insert, delete, and update) is executed.
• AFTER Trigger: After triggers are fired after the triggering action is completed for example if
after trigger is associated with INSERT command then it is fired after the row is inserted into
the table

21 DMS(22319)
 Faculty of Polytechnic, Akole

CH.4 PL/SQL Programming

When combining the different types of triggers. There are twelve possible trigger configurations that
can be defined for a table:

Event Trigger time point Trigger type

Before After Statement Row
Insert ✓ ✓ ✓ ✓
Update ✓ ✓ ✓ ✓
Delete ✓ ✓ ✓ ✓

Trigger States

A trigger can be in either of two states:

Enabled: An enabled trigger executes its trigger body if a triggering statement is entered and the
trigger restriction (if any) evaluates to TRUE.
Disabled: A disabled trigger does not execute its trigger body, even if a triggering statement is
entered and the trigger restriction (if any) evaluates to TRUE.

By default, a trigger is created in enabled state.

Uses of Triggers

Triggers supplement the standard capabilities of your database to provide a highly customized
database management system. For example, you can use triggers to:
• Automatically generate derived column values
• Enforce referential integrity across nodes in a distributed database
• Enforce complex business rules
• Provide transparent event logging
• Provide auditing
• Maintain synchronous table replicates
• Gather statistics on table access
• Modify table data when DML statements are issued against views
• Publish information about database events, user events, and SQL statements to subscribing
applications
• Restrict DML operations against a table to those issued during regular business hours
• Enforce security authorizations
• Prevent invalid transactions

Creating Triggers

To create a trigger, use the CREATE TRIGGER statement. By default, a trigger is created in enabled
state. To create a trigger in disabled state, use the DISABLE clause of the CREATE TRIGGER
statement.

CREATE [or REPLACE] TRIGGER [schema] TRIGGER_NAME

{BEFORE | AFTER}
{INSERT | DELETE | UPDATE [OF columns…]}
ON TABLE_NAME
[FOR EACH ROW [WHEN condition]]

22 DMS(22319)
 Faculty of Polytechnic, Akole

CH.4 PL/SQL Programming

PL/SQL BLOCK;
OR REPLACE
Specify OR REPLACE to re-create the trigger if it already exists. Use this clause to change the
definition of an existing trigger without first dropping it.

SCHEMA
Specify the schema to contain the trigger. If you omit schema, then the database creates the trigger
in your own schema.

TRIGGER
Specify the name of the trigger to be created.

BEFORE
Specify BEFORE to cause the database to fire the trigger before executing the triggering event. For
row triggers, the trigger is fired before each affected row is changed.
Restrictions on BEFORE Triggers BEFORE triggers are subject to the following restrictions:
• You cannot specify a BEFORE trigger on a view.
• In a BEFORE statement trigger, or in BEFORE statement section of a compound trigger, you
cannot specify either :NEW or :OLD. A BEFORE row trigger or a BEFORE row section of a
compound trigger can read and write into the :OLD or :NEW fields.

AFTER
Specify AFTER to cause the database to fire the trigger after executing the triggering event. For row
triggers, the trigger is fired after each affected row is changed.
Restrictions on AFTER Triggers AFTER triggers are subject to the following restrictions:
• You cannot specify an AFTER trigger on a view.
• In an AFTER statement trigger or in AFTER statement section of a compound trigger, you
cannot specify either :NEW or :OLD. An AFTER row trigger or AFTER row section of a
compound trigger can only read but not write into the :OLD or :NEW fields.

DELETE Specify DELETE if you want the database to fire the trigger whenever a DELETE statement
removes a row from the table or removes an element from a nested table.

INSERT Specify INSERT if you want the database to fire the trigger whenever an INSERT statement
adds a row to a table or adds an element to a nested table.

UPDATE Specify UPDATE if you want the database to fire the trigger whenever an UPDATE
statement changes a value in one of the columns specified after OF. If you omit OF, then the
database fires the trigger whenever an UPDATE statement changes a value in any column of the
table or nested table.

ON table | view The ON clause lets you determine the database object on which the trigger is to be
created. Specify the schema and table or view name of one of the following on which the trigger is to
be created:
• Table or view
• Object table or object view
• A column of nested-table type

If you omit schema, then the database assumes the table is in your own schema.

23 DMS(22319)
 Faculty of Polytechnic, Akole

CH.4 PL/SQL Programming

FOR EACH ROW
Specify FOR EACH ROW to designate the trigger as a row trigger. The database fires a row trigger
once for each row that is affected by the triggering statement and meets the optional trigger constraint
defined in the WHEN condition.

EXAMPLE

create or replace trigger trig_salary

before insert on emp
for each row
begin
if :new.salary <= 0 then
raise_application_error(-20100,'salary should not be negative');
end if;
end;

Trigger created.

insert into emp (ename,salary)

values ('Parag',0)

ORA-20100: salary should not be negative

ORA-06512: at "SYSTEM.TRIG_SALARY", line 3
ORA-04088: error during execution of trigger 'SYSTEM.TRIG_SALARY'
1. insert into emp (ename,salary)
2. values ('Parag',0)

DROP TRIGGER:
A trigger can be deleted by using following syntax
DROP TRIGGER trigger_name;

EXAMPLE

Drop trigger trig_salary;

24 DMS(22319)