Department of Computer Science
SAQIB MAHMOOD
460430
CS236: Advance Database Systems
Class: BSCS-13
Lab 06: Loops in PL/SQL
Date: 11-03-2025
Time: 9:45 to 11:55 and 12:00 to 2:25
Instructor: Dr. Ayesha Hakim
Lab Engineer: Syed Muhammad Ali Musa
CS236: Advance Database Systems Page 1
Lab 06: Loops in PL/SQL
Introduction
PL/SQL (Procedural Language/Structured Query Language) is Oracle’s extension to SQL that
allows for procedural programming within databases. Loops are one of the essential constructs in
PL/SQL that enable executing a block of statements multiple times, either for a fixed number of
iterations or until a condition is satisfied.
Objectives
The objective of this Lab is to understand the basics of Looping statements in PL/SQL.
Tools/Software Requirement
SQL Developer
VSCode
or you can directly run your commands in
https://livesql.oracle.com/next/
Description
PL/SQL provides different types of loops to repeatedly execute a block of statements. Loops are
fundamental in automating repetitive tasks, processing data sets, and performing iterative
calculations.
Types of Loops in PL/SQL
1. Basic Loop
2. WHILE Loop
3. FOR Loop
4. Nested Loops
Basic Loop Structure
A basic loop repeats a sequence of statements until an exit condition is met using the exit or exit
when statement.
Example
DECLARE
counter NUMBER := 1;
BEGIN
LOOP
DBMS_OUTPUT.PUT_LINE('Counter: ' || counter);
counter := counter + 1;
EXIT WHEN counter > 5;
END LOOP;
END;
CS236: Advance Database Systems Page 2
While loop
The WHILE loop evaluates a condition before executing the loop’s body. If the condition is
TRUE, the loop executes; if it’s FALSE, the loop terminates.
Example
DECLARE
counter NUMBER := 1;
BEGIN
WHILE counter <= 5 LOOP
DBMS_OUTPUT.PUT_LINE('Counter: ' || counter);
counter := counter + 1;
END LOOP;
END;
For Loop
The FOR loop is used when the number of iterations is known in advance. It automatically
increments the loop counter.
Example
BEGIN
FOR counter IN 1..5 LOOP
DBMS_OUTPUT.PUT_LINE('Counter: ' || counter);
END LOOP;
END;
Nested Loops
Loops can be nested inside one another to perform complex iterations over multi-dimensional data.
Example
BEGIN
FOR i IN 1..3 LOOP
FOR j IN 1..2 LOOP
DBMS_OUTPUT.PUT_LINE('i = ' || i || ', j = ' || j);
END LOOP;
END LOOP;
END;
CS236: Advance Database Systems Page 3
Lab Task
1) Display All Employee Names and Their Salaries. (Use Only For Loop)
CODE:
SET SERVEROUTPUT ON;
DECLARE
-- Declare the cursor
CURSOR emp_cursor IS
SELECT FIRST_NAME, LAST_NAME , SALARY FROM EMPLOYEES;
-- Declare variables to hold cursor values
fname EMPLOYEES.FIRST_NAME%TYPE;
sname EMPLOYEES.LAST_NAME%TYPE;
e_salary EMPLOYEES.SALARY%TYPE;
BEGIN
-- Open the cursor
OPEN emp_cursor;
-- Loop through each record in the cursor
LOOP
FETCH emp_cursor INTO fname, sname, e_salary;
EXIT WHEN emp_cursor%NOTFOUND; -- Exit loop when no more records
-- Display employee details
DBMS_OUTPUT.PUT_LINE('First Name: ' || fname ||
' | Last Name: ' || sname ||
' | Salary: ' || e_salary);
END LOOP;
-- Close the cursor
CLOSE emp_cursor;
END;
/
CS236: Advance Database Systems Page 4
OUTPUT:
2) Find Highest Paid Job Title Based on MAX_SALARY
Hint:
Loop through all JOBS and
• Find the Job Title with the highest MAX_SALARY
• Display the Job Title and its MAX_SALARY
CODE:
SET SERVEROUTPUT ON;
DECLARE
-- Declare the cursor
CURSOR job_cursor IS
SELECT JOB_TITLE, MAX_SALARY FROM JOBS;
-- Declare variables to hold cursor values
job_title JOBS.JOB_TITLE%TYPE;
max_salary JOBS.MAX_SALARY%TYPE;
highest_salary JOBS.MAX_SALARY%TYPE := 0;
highest_salary_job JOBS.JOB_TITLE%TYPE;
CS236: Advance Database Systems Page 5
BEGIN
-- Open the cursor
OPEN job_cursor;
-- Loop through each record in the cursor
LOOP
FETCH job_cursor INTO job_title, max_salary;
EXIT WHEN job_cursor%NOTFOUND; -- Exit loop when no more records
-- Check if the current salary is higher than the highest salary
IF max_salary > highest_salary THEN
highest_salary := max_salary;
highest_salary_job := job_title;
END IF;
END LOOP;
-- Close the cursor
CLOSE job_cursor;
-- Display the job with the highest salary
DBMS_OUTPUT.PUT_LINE('Highest Paid Job: ' || highest_salary_job ||
' | Max Salary: ' || highest_salary);
END;
/
OUTPUT:
CS236: Advance Database Systems Page 6
3) Link Employee table with the Job history table and Loop through each record in job
history table and display Employee ID, Job ID, and Duration (in months) along with
employee name.
CODE:
SET SERVEROUTPUT ON;
DECLARE
-- Declare the cursor
CURSOR emp_cursor IS
SELECT EMPLOYEES.EMPLOYEE_ID,
EMPLOYEES.FIRST_NAME,
EMPLOYEES.LAST_NAME,
JOB_HISTORY.JOB_ID,
TRUNC(MONTHS_BETWEEN(JOB_HISTORY.END_DATE, JOB_HISTORY.START_DATE))
AS JOB_DURATION
FROM EMPLOYEES
JOIN JOB_HISTORY ON EMPLOYEES.EMPLOYEE_ID = JOB_HISTORY.EMPLOYEE_ID;
-- Declare variables to hold cursor values
fname EMPLOYEES.FIRST_NAME%TYPE;
sname EMPLOYEES.LAST_NAME%TYPE;
Emp_ID EMPLOYEES.EMPLOYEE_ID%TYPE;
Job_ID JOB_HISTORY.JOB_ID%TYPE;
duration NUMBER;
BEGIN
-- Open the cursor
OPEN emp_cursor;
-- Loop through each record in the cursor
LOOP
FETCH emp_cursor INTO Emp_ID, fname, sname, Job_ID, duration;
EXIT WHEN emp_cursor%NOTFOUND; -- Exit loop when no more records
-- Display employee details
DBMS_OUTPUT.PUT_LINE('Employee_ID: ' || Emp_ID ||
' | First Name: ' || fname ||
' | Last Name: ' || sname ||
' | Job_ID: ' || Job_ID ||
' | Job Duration: ' || duration || ' months');
CS236: Advance Database Systems Page 7
END LOOP;
-- Close the cursor
CLOSE emp_cursor;
END;
/
OUTPUT:
Deliverables:
Submit a PDF document including the SQL queries to answer above-mentioned information needs
as well as snapshot of their outcome when executed over MySQL using the Workbench.
CS236: Advance Database Systems Page 8
