Assignment 9 PL/SQL

Nov 20, 2025

Updated 11 hours ago

5 min read

PL/SQL Basics: Loops, Cursors and Procedural Logic in Oracle

If you've been following this SQL series, everything so far has been declarative — you tell the database what you want and it figures out how. PL/SQL changes that. It's Oracle's procedural extension to SQL, and it lets you write logic: loops, conditions, variables, exception handling, and cursors.

This is where SQL starts feeling more like actual programming. Before diving in, make sure you're comfortable with SQL constraints and single row functions — PL/SQL builds on top of all of that.

What is PL/SQL?

PL/SQL stands for Procedural Language extensions to SQL. Every PL/SQL program is organized into blocks with three sections:

sql

DECLARE
    -- variables, cursors, types declared here
BEGIN
    -- actual logic goes here
EXCEPTION
    -- error handling goes here
END;
/

The DECLARE and EXCEPTION sections are optional. The BEGIN...END block is mandatory. The / at the end tells Oracle to execute the block.

Assignment Queries

1. Print squares of numbers 1 to 10

Two ways to do the same thing — a FOR loop and a WHILE loop. Both print the square of each number from 1 to 10.

Using FOR loop:

sql

DECLARE
    num NUMBER;
BEGIN
    FOR num IN 1..10 LOOP
        DBMS_OUTPUT.PUT_LINE('Square of ' || num || ' = ' || num*num);
    END LOOP;
END;
/

The FOR loop in PL/SQL automatically increments num from 1 to 10 — you don't need to manually increment it. Clean and concise.

Using WHILE loop:

sql

DECLARE
    num NUMBER := 1;
BEGIN
    WHILE num <= 10 LOOP
        DBMS_OUTPUT.PUT_LINE('Square of ' || num || ' = ' || num*num);
        num := num + 1;
    END LOOP;
END;
/

With WHILE, you control the increment yourself — num := num + 1 at the end of each iteration. Miss that line and you've got an infinite loop.

Expected output (both produce the same):

plaintext

Square of 1 = 1
Square of 2 = 4
Square of 3 = 9
...
Square of 10 = 100

2. Find the reverse of a number

sql

DECLARE
    num NUMBER := 12345;    -- replace with &num to take input at runtime
    rev NUMBER := 0;
    rem NUMBER;
BEGIN
    WHILE num > 0 LOOP
        rem := MOD(num, 10);
        rev := rev * 10 + rem;
        num := TRUNC(num / 10);
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Reversed Number = ' || rev);
END;
/

The logic here is the classic digit-extraction approach. MOD(num, 10) pulls the last digit. rev * 10 + rem shifts the existing reversed number left and appends the new digit. TRUNC(num / 10) removes the last digit from the original. This repeats until num becomes 0.

If you want to take the number as input at runtime instead of hardcoding it, replace 12345 with &num — Oracle will prompt you to enter a value when the block runs.

Expected output:

plaintext

Reversed Number = 54321

3. Fetch employee info by ID (with exception handling)

sql

DECLARE
    v_id   EMPLOYEES.employee_id%TYPE := &emp_id;
    v_name EMPLOYEES.first_name%TYPE;
    v_sal  EMPLOYEES.salary%TYPE;
    v_dept EMPLOYEES.department_id%TYPE;
BEGIN
    SELECT first_name, salary, department_id
    INTO   v_name, v_sal, v_dept
    FROM   employees
    WHERE  employee_id = v_id;

    DBMS_OUTPUT.PUT_LINE('Employee Name : ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Salary        : ' || v_sal);
    DBMS_OUTPUT.PUT_LINE('Department No : ' || v_dept);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found with ID = ' || v_id);
END;
/

A couple of things to notice here. The %TYPE attribute means the variable automatically inherits the datatype of the referenced column — if the column type ever changes, the variable adapts too. The SELECT INTO statement fetches exactly one row into variables. If no row matches, Oracle raises NO_DATA_FOUND which the EXCEPTION block catches and handles gracefully instead of crashing.

Expected output (if ID exists):

plaintext

Employee Name : Amit
Salary        : 12000
Department No : 10

Expected output (if ID not found):

plaintext

No employee found with ID = 999

4. Display all employees in department 10 using a cursor

sql

DECLARE
    CURSOR c_emp IS
        SELECT employee_id, first_name, salary
        FROM employees
        WHERE department_id = 10;

    v_id   employees.employee_id%TYPE;
    v_name employees.first_name%TYPE;
    v_sal  employees.salary%TYPE;
BEGIN
    OPEN c_emp;
    LOOP
        FETCH c_emp INTO v_id, v_name, v_sal;
        EXIT WHEN c_emp%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE('ID: ' || v_id ||
                             ' | Name: ' || v_name ||
                             ' | Salary: ' || v_sal);
    END LOOP;
    CLOSE c_emp;
END;
/

When a SELECT returns multiple rows, you can't use SELECT INTO — that only works for a single row. This is where cursors come in. A cursor is essentially a pointer that steps through a result set one row at a time.

The pattern here is always: OPEN → FETCH in a loop → EXIT WHEN %NOTFOUND → CLOSE. The %NOTFOUND attribute becomes true when there are no more rows to fetch, which is your signal to exit the loop. Always close your cursor after you're done — leaving it open wastes memory.

Expected output:

plaintext

ID: 101 | Name: Amit | Salary: 12000

5. Update salary and check if it actually worked

sql

BEGIN
    UPDATE employees
    SET salary = salary + 1000
    WHERE employee_id = 101;

    IF SQL%ROWCOUNT = 0 THEN
        DBMS_OUTPUT.PUT_LINE('No employee found with ID 101');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Salary updated successfully for employee ID 101');
    END IF;
END;
/

SQL%ROWCOUNT is an implicit cursor attribute that tells you how many rows were affected by the last DML statement. If it's 0, the WHERE clause matched nothing — either the employee doesn't exist or the ID was wrong. This is a good pattern to always include with UPDATE and DELETE so you know whether your operation actually did anything.

Expected output (if employee exists):

plaintext

Salary updated successfully for employee ID 101

Expected output (if not found):

plaintext

No employee found with ID 101

Also Explore These Topics

Introduction to SQL — create tables and insert data from scratch
SQL Assignment 2 — UPDATE, DELETE, WHERE and ORDER BY
SQL Assignment 3 — PRIMARY KEY, FOREIGN KEY, CHECK and other constraints
SQL Assignment 4 — single row functions: character, number, date and conversion
Triggers in SQL — automate PL/SQL logic when data changes
Views in SQL — virtual tables built from queries
SQL Quick Recap — fast revision of all core SQL concepts

For the complete PL/SQL language reference, the Oracle PL/SQL documentation covers everything from basic blocks to advanced packages and triggers. If you want a quick readable intro before going into the official docs, GeeksforGeeks PL/SQL tutorial is a solid starting point.