PL/SQL Introduction

PL/SQL (Procedural Language/SQL) is Oracle’s extension of SQL that adds procedural features like loops, conditions, and
error handling. It allows developers to write powerful programs that combine SQL queries with logic to control how data
is processed. With PL/SQL, complex operations, calculations, and error handling can be performed directly within the
Oracle database, making data manipulation more efficient and flexible.

PL/SQL allows developers to:

 Execute SQL queries and DML commands inside procedural blocks.

 Define variables and perform complex calculations.

 Create reusable program units, such as procedures, functions, and triggers.

 Handle exceptions, ensuring the program runs smoothly even when errors occur.

Key Features of PL/SQL

PL/SQL brings the benefits of procedural programming to the relational database world. Some of the most important
features of PL/SQL include:

 Block Structure: PL/SQL can execute a number of queries in one block using single command.

 Procedural Constructs: One can create a PL/SQL unit such as procedures, functions, packages, triggers, and
types, which are stored in the database for reuse by applications.

 Error Handling: PL/SQL provides a feature to handle the exception which occurs in PL/SQL block known as
exception handling block.

 Reusable Code: Create stored procedures, functions, triggers, and packages, which can be executed repeatedly.

 Performance: Reduces network traffic by executing multiple SQL statements within a single block

Structure of PL/SQL Block

PL/SQL extends SQL by adding constructs found in procedural languages, resulting in a structural language that is more
powerful than SQL. The basic unit in PL/SQL is a block. All PL/SQL programs are made up of blocks, which can be nested
within each other.

Typically, each block performs a logical action in the program. A block has the following structure:

DECLARE
declaration statements;

BEGIN
executable statements;

EXCEPTIONS
exception handling statements;

END;
PL/SQL code is written in blocks, which consist of three main sections:

 Declare section starts with DECLARE keyword in which variables, constants, records as cursors can be declared
which stores data temporarily. It basically consists definition of PL/SQL identifiers. This part of the code is
optional.

 Execution section starts with BEGIN and ends with END keyword. This is a mandatory section and here the
program logic is written to perform any task like loops and conditional statements. It supports all DML
commands, DDL commands and SQL*PLUS built-in functions as well.

 Exception section starts with EXCEPTION keyword. This section is optional which contains statements that are
executed when a run-time error occurs. Any exceptions can be handled in this section.

PL/SQL Identifiers

In PL/SQL, identifiers are names used to represent various program elements like variables, constants, procedures,
cursors, triggers etc. These identifiers allow you to store, manipulate, and access data throughout your PL/SQL code.

1. Variables in PL/SQL

Like several other programming languages, variables in PL/SQL must be declared prior to its use. A variable is like a
container that holds data during program execution. Each variable must have a valid name and a specific data type.

Syntax for declaration of variables:

variable_name datatype [NOT NULL := value ];

 variable_name: The name of the variable.

 datatype: The data type of the variable (e.g., INTEGER, VARCHAR2).

 NOT NULL: This optional constraint means the variable cannot be left empty.

 := value: This optional assignment assigns an initial value to the variable.

Example: Declaring Variables

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE
var1 INTEGER;
var2 REAL;
var3 varchar2(20) ;

BEGIN
null;
END;
/

Output:

PL/SQL procedure successfully completed.

Explanation:

 SET SERVEROUTPUT ON: It is used to display the buffer used by the dbms_output.

 var1 INTEGER : It is the declaration of variable, named var1 which is of integer type. There are many other data
types that can be used like float, int, real, smallint, long etc. It also supports variables used in SQL as well like
NUMBER(prec, scale), varchar, varchar2 etc.

 Slash (/) after END;: The slash (/) tells the SQL*Plus to execute the block.

 Assignment operator (:=) : It is used to assign a value to a variable.

2. Displaying Output in PL/SQL

The outputs are displayed by using DBMS_OUTPUT which is a built-in package that enables the user to display output,
debugging information, and send messages from PL/SQL blocks, subprograms, packages, and triggers. Let us see an
example to see how to display a message using PL/SQL :

Example: Displaying Output

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE
var varchar2(40) := 'I love Anurag' ;

BEGIN
dbms_output.put_line(var);

END;
/

Output:

I love Anurag
PL/SQL procedure successfully completed.

Explanation: dbms_output.put_line : This command is used to direct the PL/SQL output to a screen.

Storing the program permanently

CREATE OR REPLACE PROCEDURE print_message

AS

var VARCHAR2(40) := 'I love Anurag';

BEGIN

DBMS_OUTPUT.PUT_LINE(var);

END print_message;

OUTPUT

SQL > exec print_message;

I love Anurag

3. Comments in PL/SQL

Like in many other programming languages, in PL/SQL also, comments can be put within the code which has no effect in
the code. There are two syntaxes to create comments in PL/SQL :

 Single Line Comment: To create a single line comment , the symbol - - is used.

 Multi Line Comment: To create comments that span over several lines, the symbol /* and */ is used.

Example: Adding Comments

-- This is a single-line comment

/*
This is a multi-line comment
that spans over multiple lines.
*/
4. Taking input from users

In PL/SQL we can take input from the user and store it in a variable using substitution variables. These variables are
preceded by an & symbol. Let us see an example to show how to take input from users in PL/SQL:

Example: Taking Input from Users

SQL> SET SERVEROUTPUT ON;

SQL> DECLARE

-- taking input for variable a

a NUMBER := &a;

-- taking input for variable b

b VARCHAR2(30) := '&b';

BEGIN

NULL;

END;

Adding Two numbers Program

SET SERVEROUTPUT ON
DECLARE
a NUMBER(2) := &a;
b NUMBER(2) := &b;
s NUMBER(3);
BEGIN
s:= a + b;
DBMS_OUTPUT.PUT_LINE(‘Sum’ || s);
END;
/

Greatest of 3 Numbers
SET SERVEROUTPUT ON

DECLARE
a NUMBER := &a;
b NUMBER := &b;
c NUMBER := &c;
greatest NUMBER;
BEGIN
IF a >= b AND a >= c THEN
greatest := a;
ELSIF b >= a AND b >= c THEN
greatest := b;
ELSE
greatest := c;
END IF;
DBMS_OUTPUT.PUT_LINE('Greatest number is: ' || greatest);
END;
/
Alternative method
SET SERVEROUTPUT ON

DECLARE
a NUMBER := &a;
b NUMBER := &b;
c NUMBER := &c;
BEGIN
DBMS_OUTPUT.PUT_LINE(
'Greatest number is: ' || GREATEST(a, b, c)
);
END;
/

Another Alternative

SET SERVEROUTPUT ON

DECLARE
a NUMBER;
b NUMBER;
c NUMBER;
BEGIN
DBMS_OUTPUT.PUT_LINE(
'Greatest number is: ' || GREATEST(&a, &b, &c)
);
END;
/

Simple programs using loop, while and for iterative control statement.
1. Simple LOOP (print 1 to 5)
SET SERVEROUTPUT ON

DECLARE
i NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE(i);
i := i + 1;

EXIT WHEN i > 5;

END LOOP;
END;
/
2. WHILE LOOP (print 1 to 5)
SET SERVEROUTPUT ON

DECLARE
i NUMBER := 1;
BEGIN
WHILE i <= 5 LOOP
DBMS_OUTPUT.PUT_LINE(i);
i := i + 1;
END LOOP;
END;
/

3. FOR LOOP (print 1 to 5)

SET SERVEROUTPUT ON

BEGIN
FOR i IN 1..10 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;
/
Reverse loop (FOR)
BEGIN
FOR i IN REVERSE 1..5 LOOP
DBMS_OUTPUT.PUT_LINE(i);
END LOOP;
END;
/

Write a program to check whether the given number is Armstrong or not

SET SERVEROUTPUT ON

DECLARE
n NUMBER := &n;
temp NUMBER;
digit NUMBER;
sum_val NUMBER := 0;
digits NUMBER := 0;
BEGIN
temp := n;
-- Calculate Armstrong sum
WHILE temp > 0 LOOP
digit := MOD(temp, 10);
sum_val := sum_val + POWER(digit, 3);
DBMS_OUTPUT.PUT_LINE(sum_val);
temp := TRUNC(temp / 10);
END LOOP;
-- Check Armstrong condition
IF sum_val = n THEN
DBMS_OUTPUT.PUT_LINE(n || ' is an Armstrong number');
ELSE
DBMS_OUTPUT.PUT_LINE(n || ' is NOT an Armstrong number');
END IF;
END;
/

Write a program to generate all prime numbers below 100.

SET SERVEROUTPUT ON

DECLARE
i NUMBER; j NUMBER; is_prime BOOLEAN;
BEGIN
DBMS_OUTPUT.PUT_LINE('Prime numbers below 100:');
FOR i IN 2..99 LOOP
is_prime := TRUE;
FOR j IN 2..(i-1) LOOP
IF MOD(i, j) = 0 THEN
is_prime := FALSE;
EXIT;
END IF;
END LOOP;
IF is_prime THEN
DBMS_OUTPUT.PUT_LINE(i);
END IF;
END LOOP;
END;
/

Write a program to demonstrate the GOTO statement.

SET SERVEROUTPUT ON

DECLARE
n NUMBER := 1;
BEGIN
<<start_loop>>
DBMS_OUTPUT.PUT_LINE('Value of n = ' || n);
n := n + 1;

IF n <= 5 THEN
GOTO start_loop;
END IF;

DBMS_OUTPUT.PUT_LINE('GOTO demonstration completed');

END;
/

%TYPE in Oracle PL/SQL

%TYPE is a PL/SQL attribute used to declare a variable with the same data type as a table column, view column, or
another variable. It allows the variable to automatically inherit the data type, size, and constraints of the referenced
item.

Example
v_name employees.emp_name%TYPE;
Here, v_name gets the same data type as the emp_name column of the employees table.
Advantages of %TYPE
 Prevents data type mismatch errors
 Automatically adapts to changes in table structure
 Reduces maintenance and improves code reliability
 Makes programs more readable and consistent

Usage
%TYPE can be used with:
 Table or view columns
 PL/SQL variables
 Cursor fields

%TYPE ensures strong coupling between PL/SQL variables and database column data types, making programs safer and
easier to maintain.

set serveroutput on
DECLARE
ino iss_rec.iss_no%TYPE;
issdate iss_rec.iss_date%TYPE;
BEGIN
SELECT iss_no, iss_date
INTO ino, issdate
FROM iss_rec
WHERE mem_no = 101;

DBMS_OUTPUT.PUT_LINE('Issue Number : ' || ino);

DBMS_OUTPUT.PUT_LINE('Issue date : ' || issdate);
ENd;
/

Also without %Type

set serveroutput on
DECLARE
ino number;
issdate date;
BEGIN
SELECT iss_no, iss_date FROM iss_rec WHERE mem_no = 101;

DBMS_OUTPUT.PUT_LINE('Issue Number : ' || ino);

DBMS_OUTPUT.PUT_LINE('Issue date : ' || issdate);
ENd;
/

Demonstrating %ROWTYPE
%ROWTYPE allows a record variable to represent an entire row of a table.
Example
DECLARE
v_emp_record iss_rec%ROWTYPE;
BEGIN
SELECT *
INTO v_emp_record
FROM iss_rec
WHERE mem_no = 101;

DBMS_OUTPUT.PUT_LINE('Issue Number : ' || v_emp_record.iss_no);

DBMS_OUTPUT.PUT_LINE('Issue date : ' || v_emp_record.iss_date);
DBMS_OUTPUT.PUT_LINE('Salary : ' || v_emp_record.mem_no);
DBMS_OUTPUT.PUT_LINE('Salary : ' || v_emp_record.book_no);
END;
/

Why %ROWTYPE is useful

 Holds all columns of a row at once
 Easy access using dot notation
 Automatically adapts to table structure changes

Key Difference
Feature %TYPE %ROWTYPE
Used for Single column Entire row
Variable One value Record (multiple fields)
size
Flexibility High Very high

Exception Handling
An exception is an error which disrupts the normal flow of program instructions. PL/SQL provides us the exception block
which raises the exception thus helping the programmer to find out the fault and resolve it.

Types of Exception Handling

There are two types of exceptions defined in PL/SQL :

1. System-Defined Exceptions

These are predefined exceptions that occur when Oracle rules or constraints are violated. They include
NO_DATA_FOUND, ZERO_DIVIDE, TOO_MANY_ROWS, etc.

Let's understand this with the help of example:

DECLARE
a NUMBER:=10;
b NUMBER:=0;
c NUMBER;
BEGIN
c:=a / b;
-- Division by zero

DBMS_OUTPUT.PUT_LINE('Result: ' || c);

EXCEPTION
WHEN ZERO_DIVIDE THEN
 DBMS_OUTPUT.PUT_LINE('Error: Division by zero is not allowed.');
END;

In this example:

 Variable Declaration: a = 10, b = 0.

 Program tries to divide a / b, which causes an error.
 The EXCEPTION block catches the ZERO_DIVIDE error.
 It prints: "Error: Division by zero is not allowed.

2. User Define Exception

User-defined exceptions are custom exceptions created by the programmer to handle specific business logic errors that
are not covered by Oracle's predefined exceptions. These exceptions must be declared explicitly and are raised using
the RAISE keyword.

Syntax:

DECLARE
exception_name EXCEPTION; -- Declaration of user-defined exception
BEGIN
-- Logic
IF condition THEN
RAISE exception_name; -- Raising the exception explicitly
END IF;
EXCEPTION
WHEN exception_name THEN -- Handling code
DBMS_OUTPUT.PUT_LINE('Exception handled');
END;

Example :

DECLARE
x INT := &x;
y INT := &y;
div_r FLOAT;
exp1 EXCEPTION; -- For division by zero
exp2 EXCEPTION; -- For y > x
BEGIN
IF y = 0 THEN
RAISE exp1;
ELSIF y > x THEN
RAISE exp2;
ELSE
div_r := x / y;
DBMS_OUTPUT.PUT_LINE('The result is ' || div_r);
END IF;
EXCEPTION
WHEN exp1 THEN
DBMS_OUTPUT.PUT_LINE('Error: Division by zero is not allowed');
WHEN exp2 THEN
DBMS_OUTPUT.PUT_LINE('Error: y is greater than x, please check the input');
END;
Cursors in PL/SQL
A Cursor in PL/SQL is a pointer to a context area that stores the result set of a query.

PL/SQL Cursors

The cursor is used to retrieve data one row at a time from the results set, unlike other SQL commands that operate on
all rows at once.

Cursors update table records in a singleton or row-by-row manner.

The Data that is stored in the Cursor is called the Active Data Set. Oracle DBMS has another predefined area in the main
memory Set, within which the cursors are opened. Hence the size of the cursor is limited by the size of this pre-defined
area.

Cursor Actions

Key actions involved in working with cursors in PL/SQL are:

1. Declare Cursor: A cursor is declared by defining the SQL statement that returns a result set.
2. Open: A Cursor is opened and populated by executing the SQL statement defined by the cursor.
3. Fetch: When the cursor is opened, rows can be fetched from the cursor one by one or in a block to perform data
manipulation.
4. Close: After data manipulation, close the cursor explicitly.
5. Deallocate: Finally, delete the cursor definition and release all the system resources associated with the cursor.

Types of Cursors in PL/SQL

Cursors are classified depending on the circumstances in which they are opened.

 Implicit Cursor: If the Oracle engine opened a cursor for its internal processing it is known as an Implicit Cursor.
It is created "automatically" for the user by Oracle when a query is executed and is simpler to code.

 Explicit Cursor: A Cursor can also be opened for processing data through a PL/SQL block, on demand. Such a
user-defined cursor is known as an Explicit Cursor.

Explicit cursor

An explicit cursor is defined in the declaration section of the PL/SQL Block. It is created on a SELECT Statement which
returns more than one row.

Syntax for creating cursor

CURSOR cursor_name IS select_statement;

Where,

 cursor_name: A suitable name for the cursor.

  select_statement: A select query which returns multiple rows

How to use Explicit Cursor?

There are four steps in using an Explicit Cursor.

1. DECLARE the cursor in the Declaration section.

2. OPEN the cursor in the Execution Section.
3. FETCH the data from the cursor into PL/SQL variables or records in the Execution Section.
4. CLOSE the cursor in the Execution Section before you end the PL/SQL Block.

Syntax

General Syntax of using an explicit cursor in PL/SQL is:

DECLARE
variables;
records;
CURSOR cursor_name IS select_statement;
BEGIN
OPEN cursor_name;
LOOP
FETCH cursor_name INTO variables OR records;
EXIT WHEN cursor_name%NOTFOUND;

process the records;

END LOOP;
CLOSE cursor_name;
END;

Create a cursor, which displays all employee numbers and names from the EMP table

Create table emp

(
empno number(10),
ename varchar2(10)
);
DECLARE
-- Declare cursor
CURSOR emp_cursor IS
SELECT empno, ename
FROM emp;
-- Declare variables to hold fetched values
v_empno [Link]%TYPE;
v_ename [Link]%TYPE;
BEGIN
-- Open the cursor
OPEN emp_cursor;
LOOP
-- Fetch each row into variables
FETCH emp_cursor INTO v_empno, v_ename;
-- Exit when no more rows
EXIT WHEN emp_cursor%NOTFOUND;
-- Display the result
DBMS_OUTPUT.PUT_LINE('Employee No: ' || v_empno || ' Name: ' || v_ename);
END LOOP;
-- Close the cursor
 CLOSE emp_cursor;
END;
/

PL/SQL Triggers
PL/SQL triggers are block structures and predefined programs invoked automatically when some event occurs. They are
stored in the database and invoked repeatedly in a particular scenario. There are two states of the triggers, they
are enabled and disabled. When the trigger is created it is enabled. CREATE TRIGGER statement creates a trigger. A
triggering event is specified on a table, a view, a schema, or a database. BEFORE and AFTER are the trigger Timing
points. DML triggers are created on a table or view, and triggers. Cross edition triggers are created on Edition-based
redefinition. System Triggers are created on schema or database using DDL or database operation statements. It is
applied on new data only ,it don't affect existing data.

PL/SQL Trigger Structure

Triggers are fired on the tables or views which are in the database. Either table, view ,schema, or a database are the
basic requirement to execute a trigger. The trigger is specified first and then the action statement are specified later.
Syntax:
CREATE OR REPLACE TRIGGER trigger_name
BEFORE or AFTER or INSTEAD OF //trigger timings
INSERT or UPDATE or DELETE // Operation to be performed
of column_name
on Table_name
FOR EACH ROW
DECLARE
Declaration section
BEGIN
Execution section
EXCEPTION
Exception section
END;
/
Query operation to be performed i.e INSERT,DELETE,UPDATE.

Types of PL/SQL Triggers

Trigger timing and operations forms different combinations such as BEFORE INSERT OR BEFORE DELETE OR BEFORE
UPDATE .BEFORE and AFTER are known as conditional triggers.
Conditional Trigger: Before
Trigger is activated before the operation on the table or view is performed.
Query:
-- Create player table
CREATE TABLE player (
Id INT,
Name VARCHAR2(20),
Score INT
);
-- Insert into player Table
INSERT INTO player (Id, Name, Score) VALUES (1, 'Sam', 800);
INSERT INTO player (Id, Name, Score) VALUES (2, 'Ram', 699);
INSERT INTO player (Id, Name, Score) VALUES (3, 'Tom', 250);
INSERT INTO player (Id, Name, Score) VALUES (4, 'Om', 350);
INSERT INTO player (Id, Name, Score) VALUES (5, 'Jay', 750);
-- insert statement should be written for each entry in Oracle Sql Developer
CREATE TABLE Affect (
Id INT,
Name VARCHAR2(20),
Score INT
);
-- BEFORE INSERT trigger
CREATE OR REPLACE TRIGGER BEFORE_INSERT
BEFORE INSERT ON player
FOR EACH ROW
BEGIN
INSERT INTO Affect (Id, Name, Score)
VALUES (:[Link], :[Link], :[Link]);
END;
/
INSERT INTO player (Id, Name, Score) VALUES (6, 'Arjun', 500);

BEFORE DELETE Trigger

-- BEFORE DELETE trigger
CREATE OR REPLACE TRIGGER BEFORE_DELETE
BEFORE DELETE ON player
FOR EACH ROW
BEGIN
INSERT INTO Affect (Id, Name, Score)
VALUES (:[Link], :[Link], :[Link]);
END;
/
DELETE FROM player WHERE Id = 3;

BEFORE UPDATE Trigger

-- BEFORE UPDATE trigger
CREATE OR REPLACE TRIGGER BEFORE_UPDATE
BEFORE UPDATE ON player
FOR EACH ROW
BEGIN
INSERT INTO Affect (Id, Name, Score)
VALUES (:[Link], :[Link], :[Link]);
END;
/
UPDATE player SET Score = 900 WHERE Id = 5;
SELECT * FROM Affect;
SELECT * FROM player;

:NEW is a record that holds the new values of a row that is being:
 INSERTED
 UPDATED
It represents the data after the change.
When Is :NEW Used? : It is available in row-level triggers, such as:

In an INSERT trigger → only :NEW exists

In a DELETE trigger → only :OLD exists
In an UPDATE trigger → both :OLD and :NEW exist
:OLD is used inside a row-level trigger (commonly in Oracle PL/SQL).
What :OLD Means
:OLD is a record that holds the previous values of a row before it was changed or deleted.
It represents the data before the operation.

When Is :OLD Available?

:OLD can be used in:
 Yes in DELETE triggers
 Yes in UPDATE triggers
 No NOT available in INSERT triggers

Important Notes
 The colon (:) is required in Oracle triggers.
 :NEW.column_name refers to a specific column value of the row being processed.
 It only works in FOR EACH ROW triggers (not statement-level trigger

CURSOR Problems
1 Create a cursor to:
 Fetch employees from department 10.
 Increase their salary by 10%.
 Display updated salary.
2. Cursor with Exception Handling
Write a PL/SQL block that:
 Fetches employee details.
 Handles NO_DATA_FOUND and TOO_MANY_ROWS exceptions.
3. Cursor in Stored Procedure
Create a stored procedure that:
 Uses a cursor to calculate total salary of each department.
 Displays department number and total salary.
4. Real-Time Scenario Problem
Banking system:
 Fetch customers with balance less than minimum balance.
 Deduct penalty using cursor.
 Display updated balance.

5. BEFORE INSERT Trigger

Write a trigger that:
 Automatically sets created_date to SYSDATE
 When a new record is inserted into the EMPLOYEE table.

6. Audit Table Trigger

Create:
 EMPLOYEE_AUDIT table.
 A trigger that records employee ID, old salary, new salary, and update date whenever salary is updated.

7. Trigger to Maintain Log Table

Write a trigger to:
 Log all INSERT, UPDATE, DELETE operations on STUDENT table.
8. Trigger to Restrict Working Hours
Write a trigger that:
 Prevents INSERT/UPDATE operations on EMPLOYEE
  Outside office hours (9 AM to 6 PM).

9. Salary Increment Procedure

Write a stored procedure that:
 Accepts employee_id and percentage as input.
 Increases the employee’s salary by the given percentage.
 Displays updated salary.

10. Department-wise Salary Update

Create a procedure that:
 Accepts department_id.
 Increases salary of all employees in that department by 10%.
 Displays number of rows updated.

11. Employee Details Fetch

Write a procedure that:
 Accepts employee_id.
 Returns employee name, salary, and department using OUT parameters.