SQL 85 🛢️ Conditional Flow and Loops in Procedural SQL - KandZ

⏱️ Estimated read: 17 min | |

SQL is a declarative language. A query says what it wants, not how to compute it. Procedural SQL adds the “how” back: variables, conditionals, loops, and exception handling. The procedural extensions differ by database. PostgreSQL has PL/pgSQL. SQL Server has T-SQL. MySQL has its own stored program language. Oracle has PL/SQL. The syntax differs, but the constructs are the same, and the skill transfers. This chapter covers the conditionals and the loops that appear in every procedural routine.

Key point: Procedural SQL is a set-based language’s escape hatch. The set-based query is almost always faster than the loop, and the loop is the right choice only when the operation cannot be expressed as a set. A loop over a million rows one at a time is a million statements; the equivalent UPDATE is one.

Why conditional flow and loops exist

The decision problem. A stored procedure often needs to branch on the input. A transfer that checks the balance, an order that checks the inventory, a batch job that behaves differently on a weekend. The IF statement is the mechanism.

The iteration problem. Some operations are inherently sequential. A cursor over a result set, a batch of files to process, a recursive tree walk. The LOOP and the WHILE are the mechanisms.

The control problem. The loop must be able to exit early, skip an iteration, or raise an error. The EXIT, the CONTINUE, and the RAISE are the mechanisms.

The portability problem. The procedural extensions differ between databases. The IF is similar, the LOOP is similar, but the syntax is not identical. The developer who knows one database can learn another quickly.

The performance problem. A loop is the wrong tool for a set operation. The set-based query is optimized, and the loop is not. The UPDATE ... FROM in PostgreSQL, the UPDATE ... JOIN in MySQL, the MERGE in SQL Server. The set-based form is the first choice, and the loop is the fallback.

The readability problem. A complex CASE expression can be harder to read than an IF chain. The procedural form is sometimes clearer, even when the set-based form is available.

The maintenance problem. The business rule changes. A rule expressed as a procedural IF is easier to modify than a rule expressed as a nested CASE in a query. The procedural form is the right choice when the rule is likely to change.

a. The conditional statements

The IF statement is the primary conditional. It evaluates a boolean expression and runs a block if the expression is true.

The PostgreSQL IF

CREATE OR REPLACE PROCEDURE check_balance(account_id INT, amount NUMERIC)
LANGUAGE plpgsql
AS $$
DECLARE
    balance NUMERIC;
BEGIN
    SELECT a.balance INTO balance FROM accounts a WHERE a.id = account_id;

    IF balance >= amount THEN
        RAISE NOTICE 'Sufficient funds';
    ELSE
        RAISE NOTICE 'Insufficient funds';
    END IF;
END;
$$;

The IF ... THEN ... ELSE ... END IF is the basic form. The THEN block runs when the condition is true, and the ELSE block runs when it is false.

The ELSEIF

IF balance >= amount THEN
    RAISE NOTICE 'Sufficient funds';
ELSIF balance >= amount * 0.5 THEN
    RAISE NOTICE 'Partial funds';
ELSE
    RAISE NOTICE 'Insufficient funds';
END IF;

The ELSIF (or ELSEIF) chains the conditions. The first true condition wins, and the rest are skipped.

The SQL Server IF

CREATE PROCEDURE dbo.CheckBalance
    @AccountId INT,
    @Amount    DECIMAL(12,2)
AS
BEGIN
    DECLARE @Balance DECIMAL(12,2);
    SELECT @Balance = balance FROM accounts WHERE id = @AccountId;

    IF @Balance >= @Amount
    BEGIN
        PRINT 'Sufficient funds';
    END
    ELSE IF @Balance >= @Amount * 0.5
    BEGIN
        PRINT 'Partial funds';
    END
    ELSE
    BEGIN
        PRINT 'Insufficient funds';
    END
END;

The SQL Server form uses BEGIN/END instead of THEN/END IF. The ELSE IF is a nested IF in the ELSE block.

The MySQL IF

DELIMITER //

CREATE PROCEDURE check_balance(
    IN p_account_id INT,
    IN p_amount DECIMAL(12,2)
)
BEGIN
    DECLARE v_balance DECIMAL(12,2);
    SELECT balance INTO v_balance FROM accounts WHERE id = p_account_id;

    IF v_balance >= p_amount THEN
        SELECT 'Sufficient funds' AS message;
    ELSEIF v_balance >= p_amount * 0.5 THEN
        SELECT 'Partial funds' AS message;
    ELSE
        SELECT 'Insufficient funds' AS message;
    END IF;
END //

DELIMITER ;

The MySQL form uses THEN/ELSEIF/END IF. The syntax is closest to PostgreSQL.

The CASE statement

The CASE statement is the alternative to the IF chain.

CASE status
    WHEN 'pending' THEN
        RAISE NOTICE 'Pending review';
    WHEN 'shipped' THEN
        RAISE NOTICE 'In transit';
    WHEN 'delivered' THEN
        RAISE NOTICE 'Complete';
    ELSE
        RAISE NOTICE 'Unknown';
END CASE;

The CASE statement is the procedural form of the CASE expression. The two are not the same: the expression returns a value, and the statement runs a block.

The IF versus the CASE

Aspect	IF	CASE
Condition	Any boolean	The value matched against the options
Blocks	Yes	Yes
Readability	Good for a few branches	Good for a value match
Fallthrough	No	No
Use case	Complex conditions	A value with several options

The NULL handling

A NULL in the condition evaluates to NULL, which is not true. The IF block does not run, and the ELSE block runs.

IF balance > 100 THEN
    -- Runs when balance > 100.
ELSE
    -- Runs when balance <= 100 OR balance IS NULL.
END IF;

The IS NULL check is required when the NULL must be treated as a distinct case.

IF balance IS NULL THEN
    RAISE NOTICE 'No balance';
ELSIF balance > 100 THEN
    RAISE NOTICE 'High balance';
ELSE
    RAISE NOTICE 'Low balance';
END IF;

The boolean expression

The condition can be any expression that evaluates to a boolean: a comparison, a LIKE, an IN, an EXISTS, a function call.

IF EXISTS (SELECT 1 FROM orders WHERE customer_id = 42) THEN
    RAISE NOTICE 'Customer has orders';
END IF;

The nested IF

The IF blocks can be nested.

IF balance >= amount THEN
    IF status = 'active' THEN
        RAISE NOTICE 'Transfer allowed';
    ELSE
        RAISE NOTICE 'Account not active';
    END IF;
ELSE
    RAISE NOTICE 'Insufficient funds';
END IF;

The nesting is the mechanism for the multi-level decision.

b. The loop statements

A loop repeats a block while a condition holds. The procedural SQL has several loop forms.

The basic LOOP

The LOOP is an infinite loop with an explicit EXIT.

CREATE OR REPLACE PROCEDURE count_to_ten()
LANGUAGE plpgsql
AS $$
DECLARE
    i INT := 0;
BEGIN
    LOOP
        i := i + 1;
        RAISE NOTICE 'i = %', i;
        EXIT WHEN i >= 10;
    END LOOP;
END;
$$;

The EXIT WHEN is the condition. The loop runs until the condition is true.

The WHILE loop

The WHILE loop runs while the condition is true.

CREATE OR REPLACE PROCEDURE count_while()
LANGUAGE plpgsql
AS $$
DECLARE
    i INT := 0;
BEGIN
    WHILE i < 10 LOOP
        i := i + 1;
        RAISE NOTICE 'i = %', i;
    END LOOP;
END;
$$;

The condition is checked before each iteration. The loop does not run if the condition is false on the first check.

The FOR loop over a range

The FOR loop iterates over a range of integers.

CREATE OR REPLACE PROCEDURE count_for()
LANGUAGE plpgsql
AS $$
BEGIN
    FOR i IN 1..10 LOOP
        RAISE NOTICE 'i = %', i;
    END LOOP;
END;
$$;

The 1..10 is the range. The loop runs ten times, with i from 1 to 10.

The FOR loop with a step

The BY clause sets the step.

FOR i IN 1..10 BY 2 LOOP
    RAISE NOTICE 'i = %', i;  -- 1, 3, 5, 7, 9
END LOOP;

The REVERSE keyword counts down.

FOR i IN REVERSE 10..1 LOOP
    RAISE NOTICE 'i = %', i;  -- 10, 9, ..., 1
END LOOP;

The FOR loop over a query

The FOR ... IN SELECT loop iterates over the rows of a query.

CREATE OR REPLACE PROCEDURE list_orders()
LANGUAGE plpgsql
AS $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT id, total FROM orders WHERE customer_id = 42 LOOP
        RAISE NOTICE 'Order %: %', r.id, r.total;
    END LOOP;
END;
$$;

The r is a record that holds the current row. The loop runs once per row.

The SQL Server WHILE

CREATE PROCEDURE dbo.CountWhile
AS
BEGIN
    DECLARE @i INT = 0;

    WHILE @i < 10
    BEGIN
        SET @i = @i + 1;
        PRINT 'i = ' + CAST(@i AS NVARCHAR(10));
    END
END;

SQL Server has the WHILE and the CURSOR, but not the FOR range loop. The WHILE is the general form.

The MySQL WHILE

DELIMITER //

CREATE PROCEDURE count_while()
BEGIN
    DECLARE i INT DEFAULT 0;

    WHILE i < 10 DO
        SET i = i + 1;
        SELECT i AS value;
    END WHILE;
END //

DELIMITER ;

The MySQL WHILE uses DO/END WHILE.

The MySQL REPEAT

DELIMITER //

CREATE PROCEDURE count_repeat()
BEGIN
    DECLARE i INT DEFAULT 0;

    REPEAT
        SET i = i + 1;
        SELECT i AS value;
    UNTIL i >= 10
    END REPEAT;
END //

DELIMITER ;

The REPEAT checks the condition at the end of the iteration, so the loop runs at least once.

The EXIT statement

The EXIT exits the loop immediately.

LOOP
    i := i + 1;
    EXIT WHEN i >= 10;
    RAISE NOTICE 'i = %', i;
END LOOP;

The EXIT WHEN is the conditional form. The EXIT without a condition is the unconditional form.

The CONTINUE statement

The CONTINUE skips the rest of the current iteration and starts the next.

FOR i IN 1..10 LOOP
    CONTINUE WHEN i % 2 = 0;
    RAISE NOTICE 'Odd: %', i;  -- 1, 3, 5, 7, 9
END LOOP;

The CONTINUE WHEN is the conditional form.

The loop labels

A loop can be labeled, and the EXIT and CONTINUE can reference the label.

<<outer_loop>>
FOR i IN 1..10 LOOP
    <<inner_loop>>
    FOR j IN 1..10 LOOP
        EXIT outer_loop WHEN i * j > 50;
        CONTINUE inner_loop WHEN j % 2 = 0;
        RAISE NOTICE 'i = %, j = %', i, j;
    END LOOP inner_loop;
END LOOP outer_loop;

The label disambiguates the loop in a nested structure.

The FOR loop over a cursor

A cursor is an explicit pointer over a result set.

CREATE OR REPLACE PROCEDURE process_orders()
LANGUAGE plpgsql
AS $$
DECLARE
    cur CURSOR FOR SELECT id, total FROM orders WHERE status = 'pending';
    r RECORD;
BEGIN
    OPEN cur;
    LOOP
        FETCH cur INTO r;
        EXIT WHEN NOT FOUND;
        RAISE NOTICE 'Processing order %', r.id;
    END LOOP;
    CLOSE cur;
END;
$$;

The OPEN, the FETCH, and the CLOSE are the cursor operations.

The FOR loop over a query versus a cursor

The FOR ... IN SELECT is the implicit cursor. The CURSOR is the explicit form. The implicit form is simpler, and the explicit form gives more control.

Aspect	FOR … IN SELECT	CURSOR
Declaration	Inline	Separate
Open/close	Automatic	Manual
Control	Limited	Full
Performance	Same	Same
Use case	The common case	The rare case

The loop performance

A loop over a large result set is slow. The set-based query is fast. The rule is to use the loop only when the operation cannot be expressed as a set.

-- ❌ The loop updates one row at a time.
FOR r IN SELECT id FROM orders WHERE status = 'pending' LOOP
    UPDATE orders SET status = 'processed' WHERE id = r.id;
END LOOP;

-- ✅ The set-based query updates all rows at once.
UPDATE orders SET status = 'processed' WHERE status = 'pending';

The difference is the number of statements. The loop is one statement per row, and the set-based query is one statement.

The loop anti-patterns

Anti-pattern	Problem
A loop over a large result set	Row-by-row overhead
A cursor for a single-row query	Unnecessary complexity
A loop that runs a query per iteration	N+1 queries
A `WHILE` loop for a set operation	The set-based form is faster
An unlabeled nested loop	The `EXIT` is ambiguous

Complete Example Session

-- ============================================
-- PART 1: THE IF STATEMENT
-- ============================================

CREATE OR REPLACE PROCEDURE check_balance(account_id INT, amount NUMERIC)
LANGUAGE plpgsql
AS $$
DECLARE
    balance NUMERIC;
BEGIN
    SELECT a.balance INTO balance FROM accounts a WHERE a.id = account_id;

    IF balance >= amount THEN
        RAISE NOTICE 'Sufficient funds';
    ELSE
        RAISE NOTICE 'Insufficient funds';
    END IF;
END;
$$;

-- ============================================
-- PART 2: THE ELSEIF
-- ============================================

IF balance >= amount THEN
    RAISE NOTICE 'Sufficient';
ELSIF balance >= amount * 0.5 THEN
    RAISE NOTICE 'Partial';
ELSE
    RAISE NOTICE 'Insufficient';
END IF;

-- ============================================
-- PART 3: THE CASE STATEMENT
-- ============================================

CASE status
    WHEN 'pending' THEN RAISE NOTICE 'Pending';
    WHEN 'shipped' THEN RAISE NOTICE 'Shipped';
    WHEN 'delivered' THEN RAISE NOTICE 'Delivered';
    ELSE RAISE NOTICE 'Unknown';
END CASE;

-- ============================================
-- PART 4: THE BASIC LOOP
-- ============================================

CREATE OR REPLACE PROCEDURE count_to_ten()
LANGUAGE plpgsql
AS $$
DECLARE
    i INT := 0;
BEGIN
    LOOP
        i := i + 1;
        EXIT WHEN i >= 10;
    END LOOP;
    RAISE NOTICE 'Done at i = %', i;
END;
$$;

-- ============================================
-- PART 5: THE WHILE LOOP
-- ============================================

CREATE OR REPLACE PROCEDURE count_while()
LANGUAGE plpgsql
AS $$
DECLARE
    i INT := 0;
BEGIN
    WHILE i < 10 LOOP
        i := i + 1;
    END LOOP;
END;
$$;

-- ============================================
-- PART 6: THE FOR LOOP
-- ============================================

DO $$
BEGIN
    FOR i IN 1..10 LOOP
        RAISE NOTICE 'i = %', i;
    END LOOP;

    FOR i IN REVERSE 10..1 BY 2 LOOP
        RAISE NOTICE 'i = %', i;
    END LOOP;
END $$;

-- ============================================
-- PART 7: THE FOR LOOP OVER A QUERY
-- ============================================

DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT id, total FROM orders WHERE customer_id = 42 LOOP
        RAISE NOTICE 'Order %: %', r.id, r.total;
    END LOOP;
END $$;

-- ============================================
-- PART 8: THE EXIT AND CONTINUE
-- ============================================

DO $$
BEGIN
    FOR i IN 1..10 LOOP
        CONTINUE WHEN i % 2 = 0;
        EXIT WHEN i > 7;
        RAISE NOTICE 'Odd: %', i;
    END LOOP;
END $$;

-- ============================================
-- PART 9: THE LABELED LOOP
-- ============================================

DO $$
BEGIN
    <<outer_loop>>
    FOR i IN 1..10 LOOP
        FOR j IN 1..10 LOOP
            EXIT outer_loop WHEN i * j > 50;
        END LOOP;
    END LOOP outer_loop;
END $$;

-- ============================================
-- PART 10: THE LOOP VERSUS THE SET
-- ============================================

-- ❌ The loop updates one row at a time.
DO $$
DECLARE
    r RECORD;
BEGIN
    FOR r IN SELECT id FROM orders WHERE status = 'pending' LOOP
        UPDATE orders SET status = 'processed' WHERE id = r.id;
    END LOOP;
END $$;

-- ✅ The set-based query updates all rows at once.
UPDATE orders SET status = 'processed' WHERE status = 'pending';

The ten parts showed the IF, the ELSEIF, the CASE, the basic LOOP, the WHILE, the FOR, the FOR over a query, the EXIT and CONTINUE, the labeled loop, and the loop versus the set.

Quick Reference

The Conditional Syntax

Database	If	Else If	Else
PostgreSQL	`IF cond THEN`	`ELSIF cond THEN`	`ELSE`
SQL Server	`IF cond`	`ELSE IF cond`	`ELSE`
MySQL	`IF cond THEN`	`ELSEIF cond THEN`	`ELSE`

The Loop Syntax

Database	Basic	While	For Range
PostgreSQL	`LOOP ... END LOOP`	`WHILE cond LOOP`	`FOR i IN 1..10 LOOP`
SQL Server	`WHILE cond BEGIN ... END`	`WHILE cond BEGIN ... END`	Not supported
MySQL	`LOOP ... END LOOP`	`WHILE cond DO ... END WHILE`	Not supported

The Loop Control

Statement	Purpose
`EXIT`	Exit the loop
`EXIT WHEN cond`	Exit when the condition is true
`CONTINUE`	Skip to the next iteration
`CONTINUE WHEN cond`	Skip when the condition is true
`RETURN`	Exit the procedure

The Cursor Operations

Operation	Purpose
`DECLARE cur CURSOR FOR ...`	Declare the cursor
`OPEN cur`	Open the cursor
`FETCH cur INTO r`	Fetch the next row
`EXIT WHEN NOT FOUND`	Exit at the end
`CLOSE cur`	Close the cursor

The FOR Loop Forms

Form	Purpose
`FOR i IN 1..10`	Iterate a range
`FOR i IN REVERSE 10..1`	Iterate in reverse
`FOR i IN 1..10 BY 2`	Iterate with a step
`FOR r IN SELECT ...`	Iterate a query

Best Practices

✅ Do This:

-- Use IF for the conditional.
IF balance >= amount THEN ... END IF;  -- ✅

-- Use CASE for a value with several options.
CASE status WHEN 'pending' THEN ... END CASE;  -- ✅

-- Use the FOR loop over a query.
FOR r IN SELECT id FROM orders LOOP ... END LOOP;  -- ✅

-- Use EXIT WHEN to end the loop.
LOOP ... EXIT WHEN i >= 10; END LOOP;  -- ✅

-- Use CONTINUE to skip an iteration.
CONTINUE WHEN i % 2 = 0;  -- ✅

-- Use a label for a nested loop.
<<outer>> FOR ... LOOP EXIT outer WHEN ...; END LOOP outer;  -- ✅

-- Use the set-based query when possible.
UPDATE orders SET status = 'processed' WHERE status = 'pending';  -- ✅

❌ Don’t Do This:

-- Don't loop over a large result set.
FOR r IN SELECT * FROM orders LOOP UPDATE ...; END LOOP;  -- ❌

-- Don't run a query per iteration.
FOR r IN SELECT id FROM orders LOOP
    SELECT * FROM customers WHERE id = r.customer_id;  -- ❌ N+1
END LOOP;  -- ❌

-- Don't use an unlabeled nested loop.
FOR i IN 1..10 LOOP FOR j IN 1..10 LOOP EXIT; END LOOP; END LOOP;  -- ❌

-- Don't use a cursor for a single-row query.
DECLARE cur CURSOR FOR SELECT ... LIMIT 1;  -- ❌

-- Don't ignore the NULL in a condition.
IF balance > 100 THEN ... END IF;  -- ⚠️ NULL is not true

Common Pitfalls

Pitfall	Why It Happens	Fix
Loop is slow	Row-by-row overhead	Use the set-based query
Infinite loop	No exit condition	Add `EXIT WHEN`
Loop does not run	The condition is false on the first check	Use `REPEAT` or `LOOP`
NULL in the condition	Not handled	Add `IS NULL`
N+1 queries	A query per iteration	Use a join
Unlabeled nested loop	Ambiguous `EXIT`	Add a label
Cursor not closed	The `CLOSE` is missing	Add the `CLOSE`

Real-World Examples

1. IF

IF balance >= amount THEN RAISE NOTICE 'OK'; END IF;

2. ELSEIF

IF x > 10 THEN ... ELSIF x > 5 THEN ... ELSE ... END IF;

3. CASE

CASE status WHEN 'pending' THEN ... ELSE ... END CASE;

4. Basic Loop

LOOP i := i + 1; EXIT WHEN i >= 10; END LOOP;

5. WHILE

WHILE i < 10 LOOP i := i + 1; END LOOP;

6. FOR Range

FOR i IN 1..10 LOOP RAISE NOTICE '%', i; END LOOP;

7. FOR Query

FOR r IN SELECT id FROM orders LOOP ... END LOOP;

8. EXIT and CONTINUE

CONTINUE WHEN i % 2 = 0; EXIT WHEN i > 7;

9. Labeled Loop

<<outer>> FOR ... LOOP EXIT outer WHEN ...; END LOOP outer;

10. Set-Based

UPDATE orders SET status = 'processed' WHERE status = 'pending';

Visual

The IF Statement

┌────────────────────────────────────────────────────────────┐
│  THE IF STATEMENT                                          │
│                                                            │
│  IF balance >= amount THEN                                 │
│    RAISE NOTICE 'Sufficient';                              │
│  ELSIF balance >= amount * 0.5 THEN                        │
│    RAISE NOTICE 'Partial';                                 │
│  ELSE                                                      │
│    RAISE NOTICE 'Insufficient';                            │
│  END IF;                                                   │
│                                                            │
│  ┌──────────────────────────────────────────────────┐     │
│  │  balance >= amount?  → YES → 'Sufficient'         │     │
│  │  balance >= 50%?     → YES → 'Partial'            │     │
│  │  otherwise           →      'Insufficient'        │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  The first true condition wins.                            │
└────────────────────────────────────────────────────────────┘

The Loop Forms

┌────────────────────────────────────────────────────────────┐
│  THE LOOP FORMS                                            │
│                                                            │
│  LOOP (infinite with EXIT)                                 │
│  ┌──────────────────────────────────────────────────┐     │
│  │  LOOP                                             │     │
│  │    i := i + 1;                                    │     │
│  │    EXIT WHEN i >= 10;                             │     │
│  │  END LOOP;                                        │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  WHILE (condition first)                                   │
│  ┌──────────────────────────────────────────────────┐     │
│  │  WHILE i < 10 LOOP                                │     │
│  │    i := i + 1;                                    │     │
│  │  END LOOP;                                        │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  FOR (range)                                               │
│  ┌──────────────────────────────────────────────────┐     │
│  │  FOR i IN 1..10 LOOP                              │     │
│  │    RAISE NOTICE '%', i;                           │     │
│  │  END LOOP;                                        │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  FOR (query)                                               │
│  ┌──────────────────────────────────────────────────┐     │
│  │  FOR r IN SELECT id FROM orders LOOP              │     │
│  │    RAISE NOTICE '%', r.id;                        │     │
│  │  END LOOP;                                        │     │
│  └──────────────────────────────────────────────────┘     │
└────────────────────────────────────────────────────────────┘

The Loop Control

┌────────────────────────────────────────────────────────────┐
│  THE LOOP CONTROL                                          │
│                                                            │
│  EXIT                                                      │
│  ┌──────────────────────────────────────────────────┐     │
│  │  FOR i IN 1..10 LOOP                              │     │
│  │    EXIT WHEN i > 5;   ← exit the loop             │     │
│  │  END LOOP;                                        │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  CONTINUE                                                  │
│  ┌──────────────────────────────────────────────────┐     │
│  │  FOR i IN 1..10 LOOP                              │     │
│  │    CONTINUE WHEN i % 2 = 0;  ← skip the rest      │     │
│  │    RAISE NOTICE 'Odd: %', i;                      │     │
│  │  END LOOP;                                        │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  LABEL                                                     │
│  ┌──────────────────────────────────────────────────┐     │
│  │  <<outer>>                                        │     │
│  │  FOR i IN 1..10 LOOP                              │     │
│  │    FOR j IN 1..10 LOOP                            │     │
│  │      EXIT outer WHEN i * j > 50;                  │     │
│  │    END LOOP;                                      │     │
│  │  END LOOP outer;                                  │     │
│  └──────────────────────────────────────────────────┘     │
└────────────────────────────────────────────────────────────┘

The Loop vs the Set

┌────────────────────────────────────────────────────────────┐
│  THE LOOP vs THE SET                                       │
│                                                            │
│  LOOP                                                      │
│  ┌──────────────────────────────────────────────────┐     │
│  │  FOR r IN SELECT id FROM orders LOOP              │     │
│  │    UPDATE orders SET status = 'processed'         │     │
│  │    WHERE id = r.id;                               │     │
│  │  END LOOP;                                        │     │
│  │                                                   │     │
│  │  One UPDATE per row.                              │     │
│  │  For 1,000,000 rows: 1,000,000 statements.        │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  SET                                                       │
│  ┌──────────────────────────────────────────────────┐     │
│  │  UPDATE orders                                    │     │
│  │  SET status = 'processed'                         │     │
│  │  WHERE status = 'pending';                        │     │
│  │                                                   │     │
│  │  One UPDATE for all rows.                         │     │
│  │  For 1,000,000 rows: 1 statement.                 │     │
│  └──────────────────────────────────────────────────┘     │
│                                                            │
│  The set-based form is almost always faster.               │
└────────────────────────────────────────────────────────────┘

Summary

Item	Value
`IF`	The conditional statement
`ELSIF`	The chained condition
`CASE`	The value-match statement
`LOOP`	The infinite loop with `EXIT`
`WHILE`	The condition-first loop
`FOR`	The range or query loop
`EXIT`	Exit the loop
`CONTINUE`	Skip the iteration
`LABEL`	The named loop
`CURSOR`	The explicit pointer

Key takeaways:

The IF statement branches on a boolean expression. The THEN block runs when the condition is true, the ELSE block runs when it is false, and the ELSIF chains the conditions. The first true condition wins.
The CASE statement matches a value. It is the procedural form of the CASE expression, and it is the right tool when the branches are the options of a single value.
The LOOP is the infinite loop with an explicit EXIT. The WHILE checks the condition before each iteration. The FOR iterates a range or a query.
The EXIT and the CONTINUE control the loop. The EXIT WHEN ends the loop, the CONTINUE WHEN skips the iteration, and the label disambiguates the nested loops.
The FOR ... IN SELECT is the implicit cursor. The explicit CURSOR is the alternative when the loop needs more control. The implicit form is simpler, and the explicit form is more flexible.
The loop over a large result set is slow. The set-based query is the first choice, and the loop is the fallback. The N+1 query is the anti-pattern.
The NULL in the condition is not true. The IF block does not run when the condition is NULL, and the IS NULL check is required to treat the NULL as a distinct case.
The procedural extensions differ by database. The IF and the LOOP are similar, but the syntax is not identical. The FOR range loop is in PostgreSQL and MySQL, and the WHILE is the general form in SQL Server.

Remember: The procedural SQL adds the conditional flow and the loops to the declarative query language. The IF and the CASE branch on the condition or the value. The LOOP, the WHILE, and the FOR repeat the block. The EXIT and the CONTINUE control the iteration, and the label disambiguates the nested loops. The cursor is the explicit pointer over the result set. The result is a procedural routine that can express the logic that the set-based query cannot, and a developer who knows when to use the loop and when to use the set.

Stop using slow, ad-bloated tool sites! 🤮

🔎 Search “KandZ Tools” on Google to use many professional utilities for free.

KandZ.me is the ultimate minimalist hub for:
✅ Finance (Mortgage, Interest, Inflation)
✅ Tech (Base64, JSON, Dev Suite, IP)
✅ Health (BMI, BMR, TDEE)
✅ Productivity (Timer, Workspace, QR)

⚡️ Fast & Private
🔒 No data leaves your device
💎 100% Free

🔗 Use it now: https://tools.kandz.me
🔖 Bookmark it—you’ll need it later!