PL/SQL — Chapter 6 Notes

Computer Science, Class XII

Contents
1. Introduction to PL/SQL
2. PL/SQL Basics
3. PL/SQL Block Structure
4. SQL vs PL/SQL
5. PL/SQL Identifiers — Variables
6. Control Structures
7. IF Statements
8. LOOP Statements
9. Triggers
10. Cursor
11. Trigger vs Cursor
12. Summary
13. True/False Self-Check
14. Solved Programs
15. Exercise Questions
6.1 Introduction to PL/SQL
PL/SQL = Procedural Language extensions to
SQL
An extension of SQL used in Oracle databases;
lets the programmer write code in a procedural
format (unlike plain SQL)
PL/SQL is a superset of SQL — it combines SQL's
data manipulation power with the processing
power of a procedural language to build powerful
queries
Architecture of PL/SQL
Three main components:
1. PL/SQL Block
2. PL/SQL Engine
3. Database Server
Definition: PL/SQL is a block-structured language
that enables developers to combine the power of
SQL with procedural statements. All the statements
of a block are passed to the Oracle engine at once,
which increases processing speed and decreases
network traffic.
Features of PL/SQL
1. A procedural language — provides decision-
making, iteration, and other procedural
programming features
2. Can execute a number of queries in one block
using a single command
3. Lets you create reusable PL/SQL units —
procedures, functions, packages, triggers, types
— stored in the database for reuse by applications
4. Handles run-time errors via an exception
handling block
5. Portable — applications work on any computer
hardware/OS where Oracle runs
6. Offers extensive error checking
Advantages of Using PL/SQL
1. Better performance — SQL executed in bulk, not
statement-by-statement
2. High productivity
3. Tight integration with SQL
4. Full portability
5. Tight security
6. Supports Object-Oriented Programming concepts
Disadvantages of SQL (why PL/SQL is
needed)
 No technique for condition checking, looping, or
branching
Statements passed to the Oracle engine one at a
time → increases traffic, decreases speed
No facility for error checking during data
manipulation

6.2 PL/SQL Basics

Like other programming languages, PL/SQL has a
character set, reserved words, punctuation,
datatypes, and fixed syntax rules.
Character Sets and Lexical Units
Letters: A..Z, a..z
Numerals: 0..9
Symbols: ( ) + - * / <> = ! ~ ^ ; : . ' @
% , " # $ & _ ! { } ? [ ]
Also: tabs, spaces, carriage returns

PL/SQL keywords are not case-sensitive —

lower-case is equivalent to upper-case, except
within string and character literals.

6.3 PL/SQL Block Structure

The basic unit in PL/SQL is a block. All PL/SQL
programs are made of blocks, which can be nested
within each other. Each block performs one logical
action.
DECLARE
declaration statements;
BEGIN
executable statements
EXCEPTION
exception handling statements
END;

Section Keyword Mandatory? Purpose

Declares
variables,
constants,
cursors —
Declare DECLARE Optional defines
PL/SQL
identifiers;
stores data
temporarily
Execution BEGIN … Mandatory Program
END logic —
loops,
conditionals;
supports all
DML, DDL,
 Section Keyword Mandatory? Purpose
and
SQL*Plus
built-in
functions
Contains
statements
Exception Optional executed
EXCEPTION
when a run-
time error
occurs
Note: The / after END; tells SQL*Plus to execute the
block.

6.4 SQL vs PL/SQL

SQL PL/SQL
No procedural Procedural
capabilities — no capabilities —
conditional checking, supports conditional
looping, or branching checking, looping,
branching
Time-consuming Reduced network
processing — traffic — an entire
 SQL PL/SQL
statements passed to the block of statements is
engine one at a time, sent at once
adding network traffic
No error-handling Error-handling
procedures — Oracle procedures — PL/SQL
just displays its own error supports error handling
messages routines
Facility sharing —
same subprogram can
No facility sharing be shared by multiple
applications via the
database

6.5 PL/SQL Identifiers — Variables

PL/SQL identifiers include variables, constants,
procedures, cursors, triggers, etc.
Variables must be declared before use, with a valid
name and datatype.
Syntax:
variable_name datatype [NOT NULL := value];

Example:
 SQL> SET SERVEROUTPUT ON;
SQL> DECLARE
var1 INTEGER;
var2 REAL;
var3 VARCHAR2(20);
BEGIN
NULL;
END;
/

Output: PL/SQL procedure successfully

completed.

SET SERVEROUTPUT ON — displays the buffer

used by dbms_output
var1 INTEGER — declares variable var1 of
integer type. Other datatypes: FLOAT , INT ,
REAL , SMALLINT , LONG , NUMBER(prec, scale) ,
VARCHAR , VARCHAR2 , etc.

"PL/SQL procedure successfully completed" is

displayed when code compiles and executes
successfully

6.6 Control Structures in PL/SQL

A condition is any variable/expression that returns a
BOOLEAN value (TRUE/FALSE).
PL/SQL groups execution control statements into:
 IF Statements — conditionally execute a block of
statements (Selection)
LOOP Statements — repeatedly execute a block
of statements (Iteration)

6.7 IF Statements
The IF statement executes a sequence of statements
depending on the value of a condition. Three forms:
Form Characteristics
Simplest form. The condition
IF-THEN- determines whether the statements
END IF between THEN and END IF run. If
FALSE, the code is skipped.
IF-THEN- Either/or logic: executes the block
ELSE- between THEN/ELSE or the block
END IF between ELSE/END IF. Exactly one
always runs.
Most complex form — selects the one
IF-THEN- TRUE condition from a series of
ELSIF- mutually exclusive conditions and runs
ELSE- its statements. (From Oracle 9i onward,
END IF consider a searched CASE statement
instead.)
6.7.1 The IF-THEN Statement
Executes a sequence of statements only if the
condition is TRUE. If FALSE or NULL, the IF statement
does nothing and control passes to the next
statement.
Syntax:
IF <condition> THEN
<action>
END IF;

Example — check if a value is greater than 50:

DECLARE
a NUMBER := 60;
BEGIN
dbms_output.put_line('Program
started.');
IF (a > 50) THEN
dbms_output.put_line('a is greater
than 50');
END IF;
dbms_output.put_line('Program
completed.');
END;
/

Output: a is greater than 50

6.7.2 The IF-THEN-ELSE Statement
Use this when choosing between two mutually
exclusive actions.
Syntax:
IF condition THEN
-- TRUE sequence of statements
ELSE
-- FALSE/NULL sequence of statements
END IF;

One of the two sequences always executes — it's an

either/or construct. Note: ELSE has no THEN
attached to it.
Example — check if a value is greater or less than
50:
DECLARE
a NUMBER := 30;
BEGIN
dbms_output.put_line('Program
started.');
IF (a > 50) THEN
dbms_output.put_line('a is greater
than 50');
ELSE
dbms_output.put_line('a is less
than 50');
END IF;
dbms_output.put_line('Program
completed.');
 END;
/

Output: a is less than 50

6.7.3 The IF-THEN-ELSIF Statement
Handy for logic with several alternatives — not just
either/or. Provides a way to handle multiple mutually
exclusive conditions in one IF statement.
Syntax:
IF condition-1 THEN
statements-1
ELSIF condition-N THEN
statements-N
[ELSE
else_statements]
END IF;

Every ELSIF needs its own THEN ; only ELSE

doesn't
ELSE is optional — it's the "otherwise" case; if
omitted and no condition is TRUE, nothing inside
the block runs
Conditions are evaluated in order — if two are
TRUE, only the statements for the first one
execute
Example — display value of a variable:
 DECLARE
a NUMBER(3) := 60;
BEGIN
IF (a = 10) THEN
dbms_output.put_line('Value of a is
10');
ELSIF (a = 20) THEN
dbms_output.put_line('Value of a is
20');
ELSIF (a = 30) THEN
dbms_output.put_line('Value of a is
30');
ELSE
dbms_output.put_line('None of the
values is matching');
END IF;
dbms_output.put_line('Exact value of a
is: '||a);
END;
/

Output:
None of the values is matching
Exact value of a is: 60

6.8 LOOP Statements / Iterative

Control
A loop repeats a sequence of statements. LOOP
marks the start, END LOOP marks the end. Once a
loop starts, it runs forever unless a conditional
statement controls how many times it executes.
Three types of loops in PL/SQL:
Basic loop statement
FOR loop statement
WHILE loop statement
6.8.1 Basic LOOP Statement
The simplest (or infinite) loop.
Basic syntax:
LOOP
sequence_of_statements
END LOOP;

With an EXIT statement (EXIT can appear anywhere

inside a loop, but not outside one):
LOOP
<execution block starts>
<EXIT condition based on developer
criteria>
<execution block ends>
END LOOP;
 ⚠️ A basic loop with no EXIT becomes an infinite
loop that never stops.
Example — print 1 to 5:
DECLARE
a NUMBER := 1;
BEGIN
dbms_output.put_line('Program
started.');
LOOP
dbms_output.put_line(a);
a := a + 1;
EXIT WHEN (a > 5);
END LOOP;
dbms_output.put_line('Program
completed.');
END;
/

Output: Program started. → 1 2 3 4 5 →

Program completed.

6.8.2 FOR Loop Statement

Iterates over a specified range of integers; the number
of iterations is known before entry. .. is the range
operator, evaluated once on entry (never re-
evaluated).
Syntax:
 FOR <loop_variable> IN <lower_limit>..
<higher_limit>
LOOP
<execution block starts>
<execution block ends>
END LOOP;

The loop variable is declared implicitly — self-

incrementing, no manual increment needed
Its scope is only inside the loop
Add the keyword REVERSE before the lower limit
to count down
Example — print 1 to 5:
BEGIN
dbms_output.put_line('Program
started.');
FOR a IN 1..5
LOOP
dbms_output.put_line(a);
END LOOP;
dbms_output.put_line('Program
completed.');
END;
/

6.8.3 WHILE LOOP Statement

Similar to the basic loop, but the exit condition sits at
the very beginning — an entry-check loop, so the
body may not execute even once if the condition
already fails. No explicit EXIT keyword is needed.
Syntax:
WHILE <condition>
LOOP
<execution block starts>
<execution block ends>
END LOOP;

Example — print 1 to 5:
DECLARE
a NUMBER := 1;
BEGIN
dbms_output.put_line('Program
started.');
WHILE (a <= 5)
LOOP
dbms_output.put_line(a);
a := a + 1;
END LOOP;
dbms_output.put_line('Program
completed.');
END;
/

At a glance: Basic loop → condition checked inside

(runs ≥1 time); WHILE → condition checked before
(may run 0 times); FOR → runs a pre-known
 number of times with an auto-incrementing
counter.

6.9 Trigger
A trigger is a PL/SQL block fired automatically when a
DML statement (INSERT, DELETE, UPDATE) executes
on a database table. Triggers are stored PL/SQL
procedures associated with tables, called whenever a
certain modification (event) occurs.
Triggers fire in response to:
A DML statement — DELETE, INSERT, or UPDATE
A DDL statement — CREATE, ALTER, or DROP
A database operation — SERVERERROR, LOGON,
LOGOFF, STARTUP, or SHUTDOWN
Triggers can be defined on a table, view, schema, or
database.
Benefits of Triggers
Generating derived column values automatically
Enforcing referential integrity
Event logging and storing information on table
access
Auditing
Synchronous replication of tables
 Imposing security authorizations
Preventing invalid transactions
Types of Triggers
1. Row-level vs Statement-level — a row trigger
fires once for each row affected; a statement
trigger fires once per triggering statement,
regardless of how many rows it affects
2. BEFORE vs AFTER — BEFORE triggers run before
the triggering DML statement executes; AFTER
triggers run after it executes
3. INSTEAD OF — used on views too complex for
native insert/update/delete; lets the view act as
the sole interface for all SQL operations (insert,
update, delete, select)
Triggers on system/user events: System events
include database startup/shutdown and Data Guard
role transitions; user events include logon/logoff and
DDL statements (CREATE, ALTER, DROP).

6.10 Cursor
A cursor names a SELECT statement so its returned
rows can be processed one at a time. It's the private
work area Oracle reserves for internal SQL processing.
The rows a cursor holds are called the active set.
Two types: Implicit Cursors and Explicit Cursors
Implicit Cursors
Automatically created by PL/SQL whenever an
SQL statement executes
The programmer has no control over them
For INSERT: the cursor holds the data to be
inserted. For UPDATE/DELETE: it identifies the
affected rows.
Implicit cursor attributes:
Attribute Description
TRUE if an INSERT/UPDATE/DELETE
affected one or more rows, or a
%FOUND
SELECT INTO returned one or more
rows; otherwise FALSE
Logical opposite of %FOUND —
%NOTFOUND TRUE if no rows were
affected/returned
Always FALSE for implicit cursors —
%ISOPEN Oracle auto-closes the SQL cursor
right after its statement executes
Number of rows affected by an
%ROWCOUNT INSERT/UPDATE/DELETE, or
returned by a SELECT INTO
Explicit Cursors
Defined by the programmer to gain more control
over the context area
Declared in the declaration section of the PL/SQL
block
Created on a SELECT statement that returns
more than one row
Syntax:
CURSOR cursor_name IS select_statement;

Steps to work with an explicit cursor:

1. Declare — initialize it in memory; name it and
associate the SELECT statement
2. Open — allocate memory and fetch the rows
returned by the SQL statement into it
3. Fetch — retrieve data one row at a time (a record-
by-record activity)
4. Close — release the allocated memory once every
record has been fetched

6.11 Trigger vs Cursor

TRIGGER CURSOR
Named PL/SQL blocks Temporary work areas
Stored in the database Not stored independently
in the database
Invoked automatically Can be created both
implicitly and explicitly
Cannot take Can take parameters
parameters
Used to enforce data- Used to process the result
integrity rules of a query

Summary
PL/SQL stands for Procedural Language /
Structured Query Language, and is a superset of
SQL
PL/SQL is a block-structured language combining
SQL's power with procedural statements
Applications written in PL/SQL are portable to any
hardware/OS where Oracle runs
A PL/SQL block has four sections: Declare, Begin,
Exception, End
 The Declare section starts with DECLARE —
memory variables are initialized here (optional)
The Begin section starts with BEGIN — holds the
SQL statements that manipulate data (mandatory)
The Exception section starts with EXCEPTION —
handles errors that occur during execution
(optional)
END marks the end of a PL/SQL block

A trigger defines an action the database should

take when certain database-related events occur,
and fires automatically
A row-level trigger fires each time an affected row
changes; a statement trigger fires once per
triggering statement, regardless of row count
A BEFORE trigger runs before the triggering
statement; an AFTER trigger runs after it
A cursor is a handle/pointer to the context area —
the memory area allocated for processing an SQL
statement
Cursors are of two types: Implicit (automatic) and
Explicit (programmer-defined, for more control)

True/False Self-Check
1. A cursor is a handle or pointer to the context area.
—T
 2. A trigger is a handle or pointer to the context area.
—F
3. The two kinds of cursor used in Oracle are implicit
and explicit. — T
4. A variable is a name associated with a value. — T
5. PL/SQL stands for Procedural
Language/Structured Query Language. — T
6. PL/SQL is not a procedural language. — F
7. PL/SQL is a superset of SQL. — T
8. A cursor can take parameters. — T
9. A trigger cannot take parameters. — T
10. Error handling in PL/SQL is called Exception. — T
11. PL/SQL supports error-handling routines. — T
12. SQL does not have procedural capabilities. — T

Solved Programs
1. Area of a circle:
DECLARE
radius FLOAT;
area FLOAT;
BEGIN
radius := 3;
area := 3.14 * radius * radius;
dbms_output.put_line('The area of
circle is ' || area);
 END;
/

Output: The area of circle is 28.26

2. Area of a rectangle:
DECLARE
LENGTH NUMBER(6,2);
BREADTH NUMBER(6,2);
AREA NUMBER(10,2);
BEGIN
LENGTH := 9;
BREADTH := 10;
AREA := LENGTH * BREADTH;
DBMS_OUTPUT.PUT_LINE(AREA);
END;
/

Output: 90
3. Area of a triangle:
DECLARE
base NUMBER(6,2);
height NUMBER(6,2);
area NUMBER(16,2);
BEGIN
base := 8;
height := 10;
area := base * height / 2;
DBMS_OUTPUT.PUT_LINE(area);
 END;
/

Output: 40
4. Print the series 1 to 50 (FOR loop):
BEGIN
FOR num IN 1..50
LOOP
DBMS_OUTPUT.PUT_LINE(num || ' ');
END LOOP;
END;
/

5. Print the series 1, 4, 7, 10 … 40 (WHILE loop, step

+3):
DECLARE
i NUMBER(2);
BEGIN
i := 1;
DBMS_OUTPUT.ENABLE;
WHILE (i <= 40)
LOOP
DBMS_OUTPUT.PUT(i || ' ');
i := i + 3;
END LOOP;
DBMS_OUTPUT.PUT_LINE(' ');
END;
/
Exercise Questions (for practice)
1. What is PL/SQL?
2. What are the advantages and disadvantages of
PL/SQL?
3. What is the difference between SQL and PL/SQL?
4. Explain the PL/SQL block structure.
5. What is a variable? Explain it.
6. What do you mean by control structure? Explain it.
7. What do you mean by IF statement? Explain its
types with examples.
8. What do you mean by loop statements / iterative
control?
9. Explain the FOR loop and WHILE loop with an
example.
10. What do you mean by a trigger? Write its
advantages.
11. Explain the types of triggers.
12. What do you mean by a cursor? What are the
different types of cursors?
13. Define implicit cursor and explicit cursor.
14. Differentiate between trigger and cursor.
15. Explain the following cursor attributes: %FOUND,
%NOTFOUND, %ISOPEN, %ROWCOUNT