Oracle Database Learning: Part 5 – PL/SQL Essentials: Blocks, Cursors & Procedures

What is PL/SQL?

PL/SQL (Procedural Language/SQL) is Oracle's procedural extension to SQL. It adds variables, loops, conditions, cursors, and exception handling — making it possible to write business logic inside the database.

PL/SQL Block Structure

DECLARE
    -- Variable declarations (optional)
    v_name   VARCHAR2(50);
    v_salary NUMBER(10,2) := 0;
    c_tax    CONSTANT NUMBER := 0.3;
BEGIN
    -- Executable statements (required)
    SELECT first_name, salary
    INTO v_name, v_salary
    FROM employees
    WHERE emp_id = 101;

    DBMS_OUTPUT.PUT_LINE('Employee: ' || v_name);
    DBMS_OUTPUT.PUT_LINE('Net Salary: ' || ROUND(v_salary * (1 - c_tax), 2));
EXCEPTION
    -- Error handling (optional)
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Employee not found.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/

-- Enable output before running blocks
SET SERVEROUTPUT ON;

Variables and Data Types

DECLARE
    -- Scalar types
    v_name     VARCHAR2(100);
    v_count    PLS_INTEGER := 0;  -- faster than NUMBER for integers
    v_flag     BOOLEAN := FALSE;
    v_today    DATE := SYSDATE;

    -- %TYPE: inherit column data type (recommended)
    v_salary   employees.salary%TYPE;
    v_emp_name employees.first_name%TYPE;

    -- %ROWTYPE: inherit entire row structure
    v_emp_rec  employees%ROWTYPE;
BEGIN
    SELECT * INTO v_emp_rec FROM employees WHERE emp_id = 100;
    DBMS_OUTPUT.PUT_LINE(v_emp_rec.first_name || ' earns ' || v_emp_rec.salary);
END;
/

Control Flow

DECLARE
    v_salary NUMBER := 75000;
    v_grade  VARCHAR2(10);
BEGIN
    -- IF / ELSIF / ELSE
    IF v_salary >= 100000 THEN
        v_grade := 'A';
    ELSIF v_salary >= 70000 THEN
        v_grade := 'B';
    ELSIF v_salary >= 40000 THEN
        v_grade := 'C';
    ELSE
        v_grade := 'D';
    END IF;

    -- CASE expression
    v_grade := CASE
        WHEN v_salary >= 100000 THEN 'A'
        WHEN v_salary >= 70000  THEN 'B'
        WHEN v_salary >= 40000  THEN 'C'
        ELSE 'D'
    END;
    DBMS_OUTPUT.PUT_LINE('Grade: ' || v_grade);
END;
/

Loops

BEGIN
    -- Basic LOOP with EXIT
    DECLARE v_i NUMBER := 1;
    BEGIN
        LOOP
            EXIT WHEN v_i > 5;
            DBMS_OUTPUT.PUT_LINE('Iteration: ' || v_i);
            v_i := v_i + 1;
        END LOOP;
    END;

    -- WHILE LOOP
    DECLARE v_j NUMBER := 10;
    BEGIN
        WHILE v_j > 0 LOOP
            v_j := v_j - 2;
        END LOOP;
    END;

    -- FOR LOOP (implicit counter)
    FOR i IN 1..5 LOOP
        DBMS_OUTPUT.PUT_LINE('i = ' || i);
    END LOOP;
END;
/

Cursors

Implicit Cursor

BEGIN
    UPDATE employees SET salary = salary * 1.05
    WHERE dept_id = 10;

    -- SQL%ROWCOUNT: rows affected by last DML
    DBMS_OUTPUT.PUT_LINE(SQL%ROWCOUNT || ' rows updated.');
    COMMIT;
END;
/

Explicit Cursor

DECLARE
    CURSOR c_emp IS
        SELECT emp_id, first_name, salary
        FROM employees
        WHERE dept_id = 10
        ORDER BY salary DESC;

    v_emp c_emp%ROWTYPE;
BEGIN
    OPEN c_emp;
    LOOP
        FETCH c_emp INTO v_emp;
        EXIT WHEN c_emp%NOTFOUND;
        DBMS_OUTPUT.PUT_LINE(v_emp.first_name || ': ' || v_emp.salary);
    END LOOP;
    CLOSE c_emp;
END;
/

-- Simpler: Cursor FOR LOOP (auto open/fetch/close)
BEGIN
    FOR rec IN (SELECT first_name, salary FROM employees WHERE dept_id = 10) LOOP
        DBMS_OUTPUT.PUT_LINE(rec.first_name || ': ' || rec.salary);
    END LOOP;
END;
/

Stored Procedures

CREATE OR REPLACE PROCEDURE give_raise (
    p_dept_id  IN  employees.dept_id%TYPE,
    p_pct      IN  NUMBER,
    p_updated  OUT NUMBER
) AS
BEGIN
    UPDATE employees
    SET    salary = salary * (1 + p_pct/100)
    WHERE  dept_id = p_dept_id;

    p_updated := SQL%ROWCOUNT;
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END give_raise;
/

-- Execute procedure
DECLARE
    v_rows NUMBER;
BEGIN
    give_raise(p_dept_id => 20, p_pct => 10, p_updated => v_rows);
    DBMS_OUTPUT.PUT_LINE(v_rows || ' employees updated.');
END;
/

Stored Functions

CREATE OR REPLACE FUNCTION get_annual_salary (
    p_emp_id IN employees.emp_id%TYPE
) RETURN NUMBER AS
    v_salary employees.salary%TYPE;
BEGIN
    SELECT salary INTO v_salary FROM employees WHERE emp_id = p_emp_id;
    RETURN v_salary * 12;
EXCEPTION
    WHEN NO_DATA_FOUND THEN RETURN NULL;
END get_annual_salary;
/

-- Use in SQL
SELECT first_name, get_annual_salary(emp_id) AS annual_sal
FROM employees WHERE dept_id = 10;

Exception Handling

DECLARE
    v_salary employees.salary%TYPE;
BEGIN
    SELECT salary INTO v_salary FROM employees WHERE emp_id = 999999;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee found.');
    WHEN TOO_MANY_ROWS THEN
        DBMS_OUTPUT.PUT_LINE('Multiple rows returned.');
    WHEN OTHERS THEN
        DBMS_OUTPUT.PUT_LINE('Error code: ' || SQLCODE);
        DBMS_OUTPUT.PUT_LINE('Error msg:  ' || SQLERRM);
        -- Re-raise to propagate
        RAISE;
END;
/

-- User-defined exception
DECLARE
    e_low_salary EXCEPTION;
    PRAGMA EXCEPTION_INIT(e_low_salary, -20001);
    v_sal NUMBER := 500;
BEGIN
    IF v_sal < 1000 THEN
        RAISE_APPLICATION_ERROR(-20001, 'Salary below minimum threshold.');
    END IF;
EXCEPTION
    WHEN e_low_salary THEN
        DBMS_OUTPUT.PUT_LINE(SQLERRM);
END;
/

Lab 5: Employee Bonus Calculator

CREATE OR REPLACE PROCEDURE calc_bonus (
    p_year IN NUMBER DEFAULT EXTRACT(YEAR FROM SYSDATE)
) AS
    v_bonus NUMBER;
BEGIN
    FOR emp IN (SELECT emp_id, first_name, salary, dept_id FROM employees) LOOP
        v_bonus := CASE
            WHEN emp.salary > 100000 THEN emp.salary * 0.10
            WHEN emp.salary > 60000  THEN emp.salary * 0.15
            ELSE                          emp.salary * 0.20
        END;

        INSERT INTO employee_bonuses (emp_id, bonus_year, bonus_amount)
        VALUES (emp.emp_id, p_year, v_bonus);

        DBMS_OUTPUT.PUT_LINE(emp.first_name || ' → Bonus: ' || v_bonus);
    END LOOP;
    COMMIT;
END calc_bonus;
/

Common Errors

ORA-01403: no data found – SELECT INTO returned zero rows. Handle with WHEN NO_DATA_FOUND.

ORA-06502: value error – Variable too small for assigned value (check VARCHAR2 size).

PLS-00103: encountered symbol... – Syntax error in PL/SQL block, check spelling and END; placement.

Summary

Every PL/SQL block: DECLARE → BEGIN → EXCEPTION → END
Use %TYPE and %ROWTYPE for maintainable, type-safe code
Cursor FOR LOOP is the cleanest way to iterate result sets
Always handle NO_DATA_FOUND and TOO_MANY_ROWS for SELECT INTO

Next: Part 6 – PL/SQL Advanced: Packages, Triggers & Bulk Processing