PL/SQL Fundamentals

What PL/SQL is#

PL/SQL ("Procedural Language/SQL") is Oracle's procedural extension to SQL — it wraps ordinary SQL statements in real programming constructs (variables, loops, conditionals, exception handling) and runs inside the database engine itself rather than as a separate application layer calling SQL over the wire. This is the single biggest structural difference between Oracle and a database like MySQL or PostgreSQL used purely through application-side SQL: entire pieces of business logic can live as compiled, named units inside the database, callable by any application or user with permission, rather than duplicated across every client that needs them.

Every PL/SQL unit — an anonymous block, a stored procedure, a function, a package, or a trigger (all covered across the next few pages of this track) — is built from the same core structure. This page covers that structure using the simplest form: the anonymous block, which isn't stored anywhere and just runs once.

Anonymous blocks#

DECLARE
    v_employee_name employees.last_name%TYPE;
    v_salary        employees.salary%TYPE;
BEGIN
    SELECT last_name, salary
    INTO v_employee_name, v_salary
    FROM employees
    WHERE employee_id = 201;

    DBMS_OUTPUT.PUT_LINE(v_employee_name || ' earns ' || v_salary);
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No employee with that ID.');
END;
/

Okafor earns 9500

The four sections, in order:

• DECLARE — optional; declares variables, constants, and cursors used in the block. Omit it entirely for a block with no local variables.
• BEGIN — the executable part; ordinary SQL and procedural logic.
• EXCEPTION — optional; handlers for errors raised anywhere in the BEGIN section.
• END; — closes the block, followed by the standalone / that tells SQL*Plus/SQLcl to execute everything just typed (see the introduction page).

Run SET SERVEROUTPUT ON once per session before using DBMS_OUTPUT.PUT_LINE — without it, output is buffered but never actually printed to the client.

Variables, %TYPE, and %ROWTYPE#

A variable declared with an explicit type works exactly as you'd expect:

DECLARE
    v_count       NUMBER := 0;
    v_bonus_rate  CONSTANT NUMBER := 0.10;
    v_last_name   VARCHAR2(30);
BEGIN
    v_count := v_count + 1;
    v_last_name := 'Suzuki';
END;
/

Hardcoding a variable's type and size to match a column, though, is a maintenance trap waiting to happen — if employees.salary is later widened from NUMBER(8,2) to NUMBER(10,2), every PL/SQL variable declared NUMBER(8,2) to match it is now silently out of sync. %TYPE anchors a variable's type to a specific column (or another variable) instead, so it always tracks the schema automatically:

DECLARE
    v_salary employees.salary%TYPE;   -- always matches the real column's type, whatever it is
BEGIN
    SELECT salary INTO v_salary FROM employees WHERE employee_id = 201;
END;
/

%ROWTYPE does the same thing for an entire row — one variable holding a full record shaped exactly like a table's (or a query's) columns, without declaring a field for each one by hand:

DECLARE
    v_employee employees%ROWTYPE;
BEGIN
    SELECT * INTO v_employee FROM employees WHERE employee_id = 201;
    DBMS_OUTPUT.PUT_LINE(v_employee.last_name || ' - ' || v_employee.job_title);
END;
/

%TYPE and %ROWTYPE are used throughout the stored procedures, functions, and packages covered on the next page — it's the idiomatic default for parameter and variable declarations in real Oracle code, not just a beginner convenience.

Control flow#

IF / ELSIF / ELSE#

DECLARE
    v_salary employees.salary%TYPE := 7500;
BEGIN
    IF v_salary >= 9000 THEN
        DBMS_OUTPUT.PUT_LINE('Senior band');
    ELSIF v_salary >= 6000 THEN
        DBMS_OUTPUT.PUT_LINE('Mid band');
    ELSE
        DBMS_OUTPUT.PUT_LINE('Junior band');
    END IF;
END;
/

Basic LOOP with EXIT WHEN#

The plain LOOP has no built-in termination condition at all — it runs forever unless something inside it explicitly breaks out with EXIT or EXIT WHEN:

DECLARE
    v_count NUMBER := 1;
BEGIN
    LOOP
        DBMS_OUTPUT.PUT_LINE('Iteration ' || v_count);
        v_count := v_count + 1;
        EXIT WHEN v_count > 5;
    END LOOP;
END;
/

FOR LOOP#

A numeric FOR loop is the right tool whenever the number of iterations is known up front — it declares its own loop variable automatically, counting inclusively from the lower to the upper bound:

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

FOR i IN REVERSE 1..5 LOOP counts down from 5 to 1 instead — the bounds are still written smallest-to-largest even in reverse.

WHILE LOOP#

Use WHILE when the loop should continue as long as some condition holds, rather than for a fixed count:

DECLARE
    v_total  NUMBER := 0;
    v_next   NUMBER := 1;
BEGIN
    WHILE v_total < 100 LOOP
        v_total := v_total + v_next;
        v_next  := v_next + 1;
    END LOOP;
    DBMS_OUTPUT.PUT_LINE('Total reached: ' || v_total);
END;
/

Cursors#

A cursor is a handle to the result set of a SELECT. Oracle opens one implicitly for every SQL statement, but the interesting case is an explicit cursor, used to walk through multiple rows one at a time inside PL/SQL.

The cursor FOR loop — the idiomatic default#

For the overwhelming majority of cases, a cursor FOR loop is the cleanest way to iterate a query's results — it implicitly opens the cursor, fetches every row, and closes the cursor automatically when done (even if an exception is raised partway through), with no manual bookkeeping at all:

BEGIN
    FOR emp_rec IN (
        SELECT employee_id, last_name, salary
        FROM employees
        WHERE department_id = 20
        ORDER BY salary DESC
    ) LOOP
        DBMS_OUTPUT.PUT_LINE(emp_rec.last_name || ': ' || emp_rec.salary);
    END LOOP;
END;
/

emp_rec doesn't need to be declared anywhere — the loop creates it automatically, shaped exactly like the query's result columns, scoped only to the loop body.

Explicit cursors with OPEN / FETCH / CLOSE#

For cases needing finer control — stopping early based on some computed condition, fetching from two cursors in lockstep, or reusing the same cursor with different bind values — declare and manage the cursor explicitly:

DECLARE
    CURSOR c_high_earners IS
        SELECT employee_id, last_name, salary
        FROM employees
        WHERE salary > 8000;

    v_employee_id employees.employee_id%TYPE;
    v_last_name   employees.last_name%TYPE;
    v_salary      employees.salary%TYPE;
BEGIN
    OPEN c_high_earners;
    LOOP
        FETCH c_high_earners INTO v_employee_id, v_last_name, v_salary;
        EXIT WHEN c_high_earners%NOTFOUND;

        DBMS_OUTPUT.PUT_LINE(v_last_name || ': ' || v_salary);
    END LOOP;
    CLOSE c_high_earners;
END;
/

%NOTFOUND becomes TRUE the moment a FETCH finds no more rows — EXIT WHEN c_high_earners%NOTFOUND right after the FETCH is the standard pattern for ending the loop. %FOUND (its opposite), %ROWCOUNT (how many rows fetched so far), and %ISOPEN are the other cursor attributes available. Forgetting CLOSE at the end leaks the cursor for the rest of the session — one more reason the cursor FOR loop, which closes automatically, is the better default whenever manual control isn't actually needed.

Common mistakes#

• Hardcoding a variable's type and length (v_salary NUMBER(8,2)) instead of anchoring it with %TYPE — it works today, but silently drifts out of sync the moment the underlying column's definition changes.
• Writing a plain LOOP with no EXIT/EXIT WHEN reachable under some condition, producing an infinite loop that has to be killed from outside the session.
• Reaching for an explicit OPEN/FETCH/CLOSE cursor for simple row-by-row iteration when a cursor FOR loop does the same job with less code and no risk of forgetting CLOSE.
• Not handling NO_DATA_FOUND on a SELECT ... INTO that might match zero rows — a bare SELECT INTO expecting exactly one row raises NO_DATA_FOUND if none match (and TOO_MANY_ROWS if more than one matches), and an unhandled exception aborts the whole block.