Skip to content
Tutorials Center
My learning

Topics

Lesson 17 of 38 Β· PL/SQL Basics

PL/SQL Control Flow

Control PL/SQL programs with IF, ELSIF and CASE statements, then repeat work with LOOP, WHILE and FOR loops, EXIT, CONTINUE, labels and cursor FOR loops.

A program that only runs statements top to bottom is a script. A program that decides and repeats is software. PL/SQL gives you the usual tools: IF and CASE to choose between paths, and three kinds of loops to repeat statements. They look different from C or Java (there are no braces, and ELSIF is spelled with one E), but the ideas are the same.

All code on this page is PL/SQL, so it is read-only, with the output when we are certain of it. Two runnable SQL stand-ins at the end give the same logic with CASE and a recursive query. Prerequisite: blocks and variables.

IF statements

PLSQL
DECLARE
  v_salary NUMBER := 72000;
  v_band   VARCHAR2(10);
BEGIN
  IF v_salary >= 100000 THEN
    v_band := 'high';
  ELSIF v_salary >= 60000 THEN
    v_band := 'mid';
  ELSE
    v_band := 'entry';
  END IF;
  DBMS_OUTPUT.PUT_LINE('Band: ' || v_band);
END;
/

This prints Band: mid. Details that catch newcomers:

  • The keyword is ELSIF, not ELSE IF and not ELSEIF. (ELSE IF opens a new nested IF that needs its own END IF.)
  • Every IF ends with END IF;, with a semicolon.
  • Conditions may be TRUE, FALSE or NULL. NULL behaves like false in an IF (the ELSE branch runs). So IF v_x = v_y THEN ... ELSE ... takes the ELSE branch when either side is NULL. Test with IS NULL explicitly.

The same facts apply to BOOLEAN variables: IF v_is_paid THEN is enough, and IF NOT v_is_paid is false when v_is_paid is NULL.

CASE statements and expressions

A CASE statement executes one branch. The simple form compares one value; the searched form uses conditions:

PLSQL
CASE v_grade
  WHEN 'A' THEN DBMS_OUTPUT.PUT_LINE('Excellent');
  WHEN 'B' THEN DBMS_OUTPUT.PUT_LINE('Good');
  ELSE          DBMS_OUTPUT.PUT_LINE('Review needed');
END CASE;
PLSQL
CASE
  WHEN v_total >= 1000 THEN v_discount := 0.10;
  WHEN v_total >= 500  THEN v_discount := 0.05;
  ELSE                      v_discount := 0;
END CASE;

A CASE expression returns a value and ends with END (not END CASE), so it can be used in an assignment:

PLSQL
v_label := CASE v_status WHEN 'P' THEN 'Paid' WHEN 'S' THEN 'Shipped' ELSE 'Unknown' END;

Unlike an IF, a CASE statement without an ELSE raises CASE_NOT_FOUND (ORA-06592) if no branch matches. Add an ELSE branch, even if it only does NULL;.

The basic LOOP and EXIT

The simplest loop runs until you leave it explicitly:

PLSQL
DECLARE
  v_n PLS_INTEGER := 1;
BEGIN
  LOOP
    DBMS_OUTPUT.PUT_LINE('n = ' || v_n);
    v_n := v_n + 1;
    EXIT WHEN v_n > 3;
  END LOOP;
END;
/

The output is n = 1, n = 2 and n = 3. EXIT WHEN condition; is shorthand for IF condition THEN EXIT; END IF;. Without an exit, the loop never ends, so always make sure something inside changes the condition. This form always runs the body at least once.

WHILE loops

WHILE tests the condition before each pass, so the body may run zero times:

PLSQL
DECLARE
  v_balance NUMBER := 1000;
  v_years   PLS_INTEGER := 0;
BEGIN
  WHILE v_balance < 1500 LOOP
    v_balance := v_balance * 1.05;
    v_years   := v_years + 1;
  END LOOP;
  DBMS_OUTPUT.PUT_LINE('Years: ' || v_years);
END;
/

This prints Years: 9, because 1000 at 5 percent a year first passes 1500 in the ninth year.

FOR loops

The numeric FOR loop declares its own counter, which exists only inside the loop and cannot be assigned to:

PLSQL
BEGIN
  FOR i IN 1..5 LOOP
    DBMS_OUTPUT.PUT_LINE(i || ' squared is ' || i * i);
  END LOOP;

  FOR i IN REVERSE 1..3 LOOP
    DBMS_OUTPUT.PUT_LINE('Countdown ' || i);
  END LOOP;
END;
/

The first loop prints 1 squared is 1 up to 5 squared is 25; the second prints Countdown 3, Countdown 2, Countdown 1. Note that you write the range low to high even with REVERSE. The bounds are evaluated once, before the loop starts. There is no step option in older versions: to count by 2, test MOD(i, 2) = 0 inside the loop.

Cursor FOR loops

The most useful loop reads rows. It runs the query and gives you each row as a record, with no OPEN, FETCH or CLOSE code:

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

Oracle fetches rows in batches of 100 behind the scenes (bulk fetching is automatic in this form), so it is both short and fast. More about cursors comes in chapter 6.

EXIT, CONTINUE, labels and GOTO

CONTINUE skips to the next pass of the loop, and CONTINUE WHEN does it conditionally:

PLSQL
FOR i IN 1..10 LOOP
  CONTINUE WHEN MOD(i, 2) = 0;       -- skip even numbers
  DBMS_OUTPUT.PUT_LINE(i);           -- 1 3 5 7 9
END LOOP;

Labels name loops, so an inner loop can leave an outer one:

PLSQL
<<rows>>
FOR r IN 1..3 LOOP
  FOR c IN 1..3 LOOP
    EXIT rows WHEN r * c = 4;        -- leaves both loops
    DBMS_OUTPUT.PUT_LINE(r || 'x' || c);
  END LOOP;
END LOOP rows;

PL/SQL also has GOTO label;, which jumps to a <<label>> in the same block. You almost never need it, and it makes code hard to follow. The NULL; statement does nothing and serves as a placeholder in an otherwise empty branch.

Choosing a loop

Which loop fits?

FOR Known range or query rows
WHILE Test first, maybe zero passes
LOOP Runs once, then EXIT WHEN
Cursor FOR One pass over query results

Prefer FOR; use LOOP with EXIT WHEN for fetch-style code.

1 / 2

Stand-ins you can run

ELSIF chains are CASE in SQL:

SQL Stand-in for IF / ELSIF / ELSE Β· database: company

Press Run (or Ctrl + Enter) to see the result here.

A loop with a counter is a recursive query. This reproduces the WHILE loop that counts the years until 1000 grows to 1500:

SQL Stand-in for the WHILE loop

Press Run (or Ctrl + Enter) to see the result here.

And the classic FizzBuzz exercise, as FOR i IN 1..15 plus IF:

SQL Stand-in for a FOR loop with ELSIF (FizzBuzz)

Press Run (or Ctrl + Enter) to see the result here.

In Oracle, loops that issue one SQL statement per iteration are slow. If a task can be a single INSERT ... SELECT or UPDATE, write it in SQL: set-based statements beat row-by-row loops by a large factor.

Check your understanding

Quick check Β· 5 questions

of 5 answered You scored / 5 Β· perfect!

  1. 1. What is the correct PL/SQL keyword for an 'else if' branch?

    PL/SQL spells it ELSIF. ELSE IF would start a nested IF that needs its own END IF.
  2. 2. IF v_a = v_b THEN ... ELSE ... END IF; runs the ELSE branch when v_a is NULL because:

    A condition that evaluates to NULL is not TRUE, so the ELSE branch is taken. Use IS NULL to test for NULL.
  3. 3. What does FOR i IN REVERSE 1..3 do?

    With REVERSE the range is still written low..high; the counter simply goes downwards.
  4. 4. Which loop always runs its body at least once?

    In a basic LOOP the exit test comes after the first pass. WHILE and FOR test before the body.
  5. 5. A CASE statement without ELSE finds no matching branch. What happens?

    Unlike a CASE expression in SQL, which returns NULL, a PL/SQL CASE statement raises CASE_NOT_FOUND (ORA-06592).

Summary

  • IF ... ELSIF ... ELSE ... END IF; chooses a branch; a NULL condition counts as not true.
  • CASE statements run a branch (and need an ELSE); CASE expressions return a value.
  • LOOP ... EXIT WHEN, WHILE and FOR repeat code; FOR i IN REVERSE 1..n counts down.
  • Cursor FOR loops read query rows with no cursor management.
  • CONTINUE, labels and EXIT label control nested loops; prefer set-based SQL to row-by-row loops.

Next: SELECT INTO, %TYPE and Records