DBMS Lab Record

PL/SQL Programs
With Programs, Execution Steps & Output

Program 1 — Find the Greatest of Three Numbers

Aim:
Write PL/SQL code to find the greatest of three numbers.
Objective:
Declare three number variables a, b, c. Use IF-ELSIF-ELSE conditional statements with the AND keyword to
compare all three and determine which is greatest.
💡 PL/SQL uses ':=' for assignment and '&' for accepting runtime input from the user.
Program:

declare
a number;
b number;
c number;
begin
a := &a; -- accept value of a from user
b := &b; -- accept value of b from user
c := &c; -- accept value of c from user

if (a > b) and (a > c) then

dbms_output.put_line('a is greatest');
elsif (b > a) and (b > c) then
dbms_output.put_line('b is greatest');
else
dbms_output.put_line('c is greatest');
end if;
end;
/

Execution Steps:
1. Open SQL*Plus or Oracle SQL Developer and connect to your schema.
2. Type the program exactly as shown above.
3. Press Enter after '/' to execute.
4. Oracle prompts: 'Enter value for a:' — type a number (e.g., 5) and press Enter.
5. Repeat for b (e.g., 2) and c (e.g., 3).
6. Oracle substitutes values and runs the IF logic. The greatest value is printed.
Output:

Enter value for a: 5

old 4: a := &a;
new 4: a := 5;
Enter value for b: 2
Enter value for c: 3

a is greatest
PL/SQL procedure successfully completed.
How the Logic Works:
IF (a>b) AND (a>c) → a is greater than both, so a is greatest.
ELSIF (b>a) AND (b>c) → b is greater than both, so b is greatest.
ELSE → neither a nor b is greatest, so c must be greatest.

Program 2 — Generate Multiplication Table

Aim:
Write PL/SQL code to generate the multiplication table of a given number.
Objective:
Declare a variable 'a' to hold the input number. Use a FOR loop with the loop variable 'i' running from 1 to 10.
Inside the loop, multiply a*i and print the result using dbms_output.put_line.
💡 The '||' operator in PL/SQL is used for string concatenation — joining text and numbers together for display.
Program:

declare
a number;
i number;
begin
a := &a; -- accept the number from user

for i in 1..10 loop -- loop i from 1 to 10

dbms_output.put_line(a || ' * ' || i || ' = ' || (a * i));
end loop;
end;
/

Execution Steps:
7. Open SQL*Plus and type the program.
8. Run with '/'. Oracle prompts 'Enter value for a:'.
9. Enter a number — for example 3 — and press Enter.
10. PL/SQL substitutes a=3 and runs the FOR loop from i=1 to i=10.
11. Each iteration prints one line: 3 * 1 = 3, 3 * 2 = 6, and so on up to 3 * 10 = 30.
Output:

Enter value for a: 3

3 * 1 = 3
3 * 2 = 6
3 * 3 = 9
3 * 4 = 12
3 * 5 = 15
3 * 6 = 18
3 * 7 = 21
3 * 8 = 24
3 * 9 = 27
3 * 10 = 30
PL/SQL procedure successfully completed.

How the FOR Loop Works:

'for i in 1..10 loop' — PL/SQL automatically increments i from 1 to 10 each iteration. No need to manually
increment i unlike C/Java.
'end loop;' — marks the end of one iteration. After i=10, the loop exits.

Program 3 — Insert Square and Square Root into a Table

Aim:
Write a PL/SQL code to insert values into a table with columns: no, square, and square root.
Objective:
First create the table SQ using a CREATE statement. Then in PL/SQL, declare variables for the number (p), its
square (q), and square root (r). Use p*p for square and the built-in sqrt() function for square root. Insert all three
into the table using INSERT INTO.
💡 Always run the CREATE TABLE statement first in SQL before running the PL/SQL block.
Step 1 — Create the Table (run in SQL first):

CREATE TABLE sq (no number, square number, squareroot number);

Step 2 — PL/SQL Program:

declare
p number := &p; -- accept input number
q number; -- will store square
r number; -- will store square root
begin
dbms_output.put_line('number is ' || p);

q := p * p; -- calculate square
dbms_output.put_line('square of a number is ' || q);

r := sqrt(p); -- calculate square root using built-in function

dbms_output.put_line('squareroot of a number is ' || r);

insert into sq values(p, q, r); -- insert all three values into table
end;
/

Step 3 — Verify (run after PL/SQL block):

SELECT * FROM sq;

Execution Steps:
12. Run the CREATE TABLE statement first to create the SQ table.
13. Run the PL/SQL block. When prompted, enter a number e.g., 4.
14. PL/SQL calculates: square = 4*4 = 16, squareroot = sqrt(4) = 2.
15. All three values (4, 16, 2) are inserted into the SQ table.
16. Run SELECT * FROM sq to verify the inserted row.
Output:

Enter value for p: 4

number is 4
square of a number is 16
squareroot of a number is 2
 PL/SQL procedure successfully completed.

SQL> SELECT * FROM sq;

NO SQUARE SQUAREROOT
---------- ---------- ----------
4 16 2

Program 4 — Cursor to Retrieve Employees where Salary > 2000

Aim:
Write PL/SQL code using an explicit cursor to retrieve information from the emp table where salary > 2000.
Objective:
A cursor is a pointer to a memory area (context area) that stores the result of a SELECT query. An EXPLICIT
cursor is one that the programmer declares, opens, fetches from, and closes manually. Here we declare cursor 'c'
to select ename, empno, sal from emp where sal > 2000.
💡 Explicit Cursor steps: DECLARE → OPEN → FETCH (in loop) → CLOSE. Always close the cursor after use.
Program:

declare
name [Link]%type; -- variable same type as [Link] column
no [Link]%type; -- variable same type as [Link] column
s [Link]%type; -- variable same type as [Link] column
i number;
cursor c is -- declare the cursor with the query
select ename, empno, sal from emp where sal > 2000;
begin
open c; -- open: execute the SELECT and populate cursor

select count(*) into i -- count how many rows satisfy the condition
from emp where sal > 2000;

for j in 1..i loop -- loop once per matching row

fetch c into name, no, s; -- fetch one row into variables
dbms_output.put_line('salary of ' || name || ' is ' || s);
end loop;

close c; -- always close the cursor

end;
/

Execution Steps:
17. Make sure the emp table exists with columns ename, empno, sal (standard Oracle sample table).
18. Type and run the PL/SQL block.
19. OPEN C executes the SELECT and loads matching rows into cursor memory.
20. COUNT(*) finds how many rows match so we know how many times to loop.
21. Each FETCH retrieves one row into (name, no, s). dbms_output prints the salary.
22. CLOSE C releases the cursor memory after all rows are fetched.
Output:

salary of JONES is 2975

salary of BLAKE is 2850
salary of CLARK is 2450
 salary of SCOTT is 3000
salary of KING is 5000
PL/SQL procedure successfully completed.

Key Concepts:
[Link]%TYPE — Borrows the data type of the ename column from the emp table. This is safer than
hardcoding VARCHAR2(10) because if the column type changes, the variable adjusts automatically.
FETCH c INTO name, no, s — Retrieves the next row from the cursor result set. Each FETCH moves the cursor
pointer forward by one row.

Program 5 — Function to Find Factorial of a Number

Aim:
Write PL/SQL code to create a function that finds the factorial of a given number.
Objective:
A FUNCTION is a named PL/SQL block that performs actions AND returns a value. Unlike a procedure, a
function must have a RETURN statement. Here we create a stored function 'fact' that takes a number n as input
and returns n! (n factorial) using a FOR loop.
💡 Factorial: 4! = 4 × 3 × 2 × 1 = 24. The function is stored in the database and can be called from SQL queries
directly using SELECT.
Step 1 — Create the Function:

create or replace function fact(n in number)

return number is
f number := 1; -- initialize f to 1 (since 0! = 1)
begin
for i in 1..n loop -- multiply f by each number from 1 to n
f := f * i;
end loop;
return f; -- return the final factorial value
end fact;
/

Step 2 — Call the Function:

-- Call using SELECT from dual (dual is a dummy 1-row table in Oracle)
SELECT fact(4) FROM dual;

-- Or call from an anonymous PL/SQL block:

begin
dbms_output.put_line('Factorial = ' || fact(5));
end;
/

Execution Steps:
23. Run the CREATE OR REPLACE FUNCTION block. Oracle responds: 'Function created.'
24. Run SELECT fact(4) FROM dual; to call the function with input 4.
25. Function executes: f starts at 1. Loop: f=1*1=1, f=1*2=2, f=2*3=6, f=6*4=24.
26. Returns 24. Oracle displays the result.
Output:

Function created.
 SQL> SELECT fact(4) FROM dual;

FACT(4)
----------
24

How the FOR Loop Calculates Factorial:

f starts at 1. Each iteration multiplies f by the loop counter i.
For n=4: i=1 → f=1, i=2 → f=2, i=3 → f=6, i=4 → f=24. Returns 24.
Difference: Function vs Procedure:
Function → MUST return a value using RETURN. Called inside expressions or SELECT.
Procedure → Does NOT return a value. Called using EXECUTE.

Program 6 — Procedure to Check Palindrome

Aim:
Write PL/SQL code to check whether a given number/string is a palindrome or not using a procedure.
Objective:
A PROCEDURE is a named PL/SQL block that performs actions but does NOT return a value. Here, procedure
'pal2' takes a string input s1, reverses it character by character using a REVERSE FOR loop and substr(), and
compares the reversed string s3 with the original s1.
💡 Palindrome: A word or number that reads the same forwards and backwards. Example: '121', 'madam',
'racecar'.

Step 1 — Create the Procedure:

create or replace procedure pal2(s1 in varchar2) is

s2 varchar2(20); -- holds one character at a time
s3 varchar2(20); -- builds the reversed string
begin
-- Loop through s1 from last character to first (REVERSE)
for i in reverse 1..length(s1) loop
s2 := substr(s1, i, 1); -- extract character at position i
s3 := s3 || s2; -- append it to s3 (building reverse)
end loop;

-- Compare original with reversed

if s1 = s3 then
dbms_output.put_line('Palindrome');
else
dbms_output.put_line('Not palindrome');
end if;
end pal2;
/

Step 2 — Execute the Procedure:

EXECUTE pal2('121');
EXECUTE pal2('123');
EXECUTE pal2('madam');

Execution Steps:
27. Run the CREATE OR REPLACE PROCEDURE block. Oracle responds: 'Procedure created.'
 28. Run EXECUTE pal2('121');
29. Procedure runs: length('121')=3. Loop in REVERSE: i=3 → s2='1', s3='1'. i=2 → s2='2', s3='12'. i=1 →
s2='1', s3='121'.
30. Compare: s1='121' = s3='121' → prints 'Palindrome'.
31. Run EXECUTE pal2('123'). Reverse of '123' = '321'. '123' ≠ '321' → prints 'Not palindrome'.
Output:

Procedure created.

SQL> EXECUTE pal2('121');

Palindrome
PL/SQL procedure successfully completed.

SQL> EXECUTE pal2('123');

Not palindrome
PL/SQL procedure successfully completed.

Key Functions Used:

length(s1) — Returns the number of characters in the string. For '121', returns 3.
substr(s1, i, 1) — Extracts 1 character from s1 starting at position i.
'for i in reverse 1..n' — Loop counts DOWN from n to 1, enabling easy string reversal.

Program 7 — Procedure to Display Department Salary Statistics

Aim:
Write a PL/SQL procedure to accept deptno as a parameter and display total salary, number of employees,
maximum salary, and average salary of that department.
Objective:
Create a stored procedure 'sample1' that takes department number as IN parameter. Inside the procedure, use
aggregate functions SUM, COUNT, MAX, and AVG in a single SELECT INTO statement to compute all statistics
for that department. Display results using dbms_output.
💡 Aggregate functions (SUM, COUNT, MAX, AVG) work on groups of rows and return a single result each.
SELECT INTO is used when exactly one row is returned.
Step 1 — Create the Procedure:

create or replace procedure sample1(deptno in number) is

a number; -- total salary (SUM)
b number; -- number of employees (COUNT)
c number; -- maximum salary (MAX)
d number; -- average salary (AVG)
begin
-- Fetch all 4 aggregate values in one query
select sum(sal), count(*), max(sal), avg(sal)
into a, b, c, d
from emp
where deptno = 10; -- filter by department 10

dbms_output.put_line('total salary = ' || a);

dbms_output.put_line('no of employees = ' || b);
dbms_output.put_line('max salary = ' || c);
dbms_output.put_line('average salary = ' || d);
end sample1;
/
Step 2 — Execute the Procedure:

EXECUTE sample1(10);

Execution Steps:
32. Run the CREATE OR REPLACE PROCEDURE block. Oracle responds: 'Procedure created.'
33. Run: EXECUTE sample1(10);
34. Procedure receives deptno=10 as the parameter.
35. The SELECT INTO runs all four aggregate functions at once for dept 10.
36. Results are stored into a, b, c, d respectively and printed.
Output:

Procedure created.

SQL> EXECUTE sample1(10);

total salary = 8750

no of employees = 3
max salary = 5000
average salary = 2916.6666666666666667
PL/SQL procedure successfully completed.

Aggregate Functions Used:

SUM(sal) — Adds all salaries in department 10 → 2450 + 1300 + 5000 = 8750
COUNT(*) — Counts all rows (employees) in department 10 → 3
MAX(sal) — Finds the highest salary in department 10 → 5000 (KING)
AVG(sal) — Calculates average salary → 8750 / 3 = 2916.67

Program 8 — Package with Overloading of Procedure

Aim:
Write a PL/SQL program to implement a package with overloading of a procedure.
Objective:
OVERLOADING means having multiple procedures/functions with the SAME name but different parameters. A
PACKAGE groups related procedures and functions together. It has two parts: (1) Package Specification —
declares what is inside. (2) Package Body — defines the actual implementation.
💡 Here, procedure 'sum' is overloaded: one version adds 2 numbers, another adds 3 numbers. Oracle decides
which version to call based on the number of arguments you pass.
Step 1 — Create Package Specification (declares the interface):

create or replace package overload is

procedure sum(a number, b number); -- version 1: 2 parameters
procedure sum(a number, b number, c number); -- version 2: 3 parameters
end;
/

Step 2 — Create Package Body (implements both versions):

create or replace package body overload as

 -- Implementation of sum with 3 parameters
procedure sum(a number, b number, c number) is
d number;
begin
d := a + b + c;
dbms_output.put_line('Sum of 3 nos = ' || d);
end sum;

-- Implementation of sum with 2 parameters

procedure sum(a number, b number) is
c number;
begin
c := a + b;
dbms_output.put_line('Sum of 2 numbers is ' || c);
end sum;

end;
/

Step 3 — Execute Both Overloaded Versions:

-- Calls the 2-parameter version

EXECUTE [Link](2, 3);

-- Calls the 3-parameter version

EXECUTE [Link](2, 3, 4);

Execution Steps:
37. Run the Package Specification first. Oracle responds: 'Package created.'
38. Run the Package Body. Oracle responds: 'Package body created.'
39. Run EXECUTE [Link](2,3); — Oracle sees 2 arguments, calls the 2-parameter version. Prints: Sum
of 2 numbers is 5.
40. Run EXECUTE [Link](2,3,4); — Oracle sees 3 arguments, calls the 3-parameter version. Prints:
Sum of 3 nos = 9.
Output:

Package created.
Package body created.

SQL> EXECUTE [Link](2, 3);

Sum of 2 numbers is 5
PL/SQL procedure successfully completed.

SQL> EXECUTE [Link](2, 3, 4);

Sum of 3 nos = 9
PL/SQL procedure successfully completed.

Key Concepts:
Package Specification — The PUBLIC interface. Lists all procedures and functions available. Like a header file.
Package Body — The PRIVATE implementation. Contains actual code. Must match the specification.
Overloading — Same procedure name, different parameter lists. Oracle resolves which version to call at compile
time based on the number/type of arguments.
Calling syntax: [Link](args) — e.g., [Link](2,3)
Program 9 — Trigger to Convert ename to Uppercase Before Insert
Aim:
Write a PL/SQL trigger to automatically convert the ename column to uppercase before inserting a new row into
the emp table.
Objective:
A TRIGGER is a stored PL/SQL block that automatically executes (fires) in response to a specific event on a table
— such as INSERT, UPDATE, or DELETE. Here, a BEFORE INSERT trigger intercepts every INSERT on the
emp table and converts the ename value to uppercase before it is saved.
💡 :NEW refers to the new row being inserted. :[Link] is the ename value being inserted. We overwrite it
with UPPER(:[Link]) to force uppercase.
Step 1 — Create the Trigger:

create or replace trigger case_before_insert

before insert -- fires BEFORE an INSERT happens
on emp -- on the emp table
for each row -- fires once for EACH inserted row
begin
-- Convert ename to uppercase before the row is saved
:[Link] := upper(:[Link]);
end;
/

Step 2 — Test the Trigger (Insert with lowercase name):

-- Insert a row with lowercase ename 'abc'

INSERT INTO emp (empno, ename, job, mgr, hiredate, sal, comm, deptno)
VALUES (10, 'abc', 'clerk', 10, '10-nov-89', 45, 56, 10);

-- Verify: ename should be stored as 'ABC' (uppercase)

SELECT empno, ename, job FROM emp WHERE empno = 10;

Execution Steps:
41. Run the CREATE OR REPLACE TRIGGER block. Oracle responds: 'Trigger created.'
42. The trigger is now active on the emp table.
43. Run the INSERT statement with ename = 'abc' (all lowercase).
44. Before saving, the trigger fires and executes: :[Link] := upper('abc') → 'ABC'.
45. The row is saved with ename = 'ABC' in the database.
46. Run SELECT to verify — the ename is stored as 'ABC' not 'abc'.
Output:

Trigger created.

SQL> INSERT INTO emp VALUES(10,'abc','clerk',10,'10-nov-89',45,56,10);

1 row created.

SQL> SELECT empno, ename, job FROM emp WHERE empno = 10;

EMPNO ENAME JOB

---------- ---------- ---------
10 ABC clerk

-- Notice: 'abc' was inserted but saved as 'ABC' by the trigger

Trigger Anatomy:
BEFORE INSERT — Timing: fires before the row is written. Other options: AFTER INSERT, BEFORE UPDATE,
etc.
ON emp — The table this trigger watches.
FOR EACH ROW — Row-level trigger: fires once per inserted row. Without this, it fires once per statement.
:[Link] — The value of ename in the row being inserted. We can read and modify it in a BEFORE trigger.
upper() — Built-in SQL/PL/SQL function that converts a string to all uppercase.

— End of PL/SQL Lab Record —