What Procedural SQL Adds Over Plain SQL

Every module before this one was declarative: you describe the result you want (a SELECT), and the engine figures out how to get it, with no branching or looping in the query itself. Procedural SQL adds the missing pieces of a general-purpose programming language, still inside the database: variables (DECLARE) hold an intermediate value across several statements; control flow (IF/ELSE, and loops — plain SQL has no loop construct at all) lets logic branch on a condition; stored procedures/functions package a routine under a name so it can be saved once and called repeatedly, with parameters, instead of being retyped; triggers are routines the database fires automatically whenever a row is inserted/updated/deleted, with no application code having to remember to call them; cursors step through a result set one row at a time when a problem genuinely can't be expressed as a single set-based query (rare — reach for this last); and exception handling lets a routine catch and react to an error itself, instead of the error propagating straight back to the caller.

Runnable, via Docker Postgres

Neither SQLite nor DuckDB implement real stored procedures/triggers — this module's one runnable demo uses the optional Docker Postgres container from Part 10. A stored PL/pgSQL function restocks a product automatically when stock drops below a threshold; a trigger logs every price change to an audit table without any application code having to remember to call it.

PL/pgSQL · a stored function with control flow

CREATE OR REPLACE FUNCTION restock_if_low(p_id INTEGER, p_threshold INTEGER, p_restock_qty INTEGER)
RETURNS TEXT AS $$
DECLARE
    current_stock INTEGER;
BEGIN
    SELECT stock_quantity INTO current_stock FROM products WHERE id = p_id;

    IF current_stock IS NULL THEN
        RETURN 'Product not found';
    ELSIF current_stock < p_threshold THEN
        UPDATE products SET stock_quantity = stock_quantity + p_restock_qty WHERE id = p_id;
        RETURN 'Restocked: ' || current_stock || ' -> ' || (current_stock + p_restock_qty);
    ELSE
        RETURN 'Stock sufficient, no action taken';
    END IF;
END;
$$ LANGUAGE plpgsql;

SELECT restock_if_low(2, 10, 50);   -- stock=3, below threshold=10 -> restocks

PL/pgSQL · a trigger that fires automatically

CREATE OR REPLACE FUNCTION log_price_change() RETURNS TRIGGER AS $$
BEGIN
    IF NEW.price != OLD.price THEN
        INSERT INTO price_audit VALUES (OLD.id, OLD.price, NEW.price, now());
    END IF;
    RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER trg_price_change
BEFORE UPDATE ON products
FOR EACH ROW EXECUTE FUNCTION log_price_change();

UPDATE products SET price = 139.99 WHERE id = 1;   -- fires the trigger automatically

Oracle PL/SQL (reference)

PL/SQL wraps SQL in BEGIN/END blocks with the same DECLARE/control-flow shape as PL/pgSQL — the same underlying ideas from the section above, Oracle's specific spelling of them. Two features are distinctive to PL/SQL: %ROWTYPE declares a variable that automatically mirrors an entire table row's structure, without having to redeclare every column by hand (and %TYPE does the same for a single column); packages (PACKAGE/PACKAGE BODY) bundle a set of related procedures and functions together under one versioned name, closer to a code module than a single stored routine. Capability description only — verify exact syntax against current Oracle docs.

PL/SQL · reference, not executed here

CREATE OR REPLACE PROCEDURE restock_if_low(p_id IN products.id%TYPE, p_threshold IN NUMBER) IS
    v_product products%ROWTYPE;
BEGIN
    SELECT * INTO v_product FROM products WHERE id = p_id;
    IF v_product.stock_quantity < p_threshold THEN
        UPDATE products SET stock_quantity = stock_quantity + 50 WHERE id = p_id;
    END IF;
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('Product not found');
END;
/

SQL Server / Fabric T-SQL (reference)

T-SQL structures error handling as BEGIN TRY/BEGIN CATCH blocks, which reads closer to exception handling in a mainstream general-purpose language (Python's try/except) than PL/SQL's EXCEPTION WHEN clause does. Its other distinctive feature is table variables (DECLARE @t TABLE (...)) — a variable that holds an entire result set in memory for the duration of a batch, useful for staging an intermediate result inside a procedure without creating a real temp table. Microsoft Fabric's Warehouse item supports a T-SQL subset for querying and procedures; verify current feature coverage against Fabric docs, since it doesn't yet match on-premises SQL Server one-to-one. Capability description only.

T-SQL · reference, not executed here

CREATE OR ALTER PROCEDURE RestockIfLow @ProductId INT, @Threshold INT AS
BEGIN
    BEGIN TRY
        DECLARE @CurrentStock INT = (SELECT stock_quantity FROM products WHERE id = @ProductId);
        IF @CurrentStock < @Threshold
            UPDATE products SET stock_quantity = stock_quantity + 50 WHERE id = @ProductId;
    END TRY
    BEGIN CATCH
        PRINT ERROR_MESSAGE();
    END CATCH
END;

Do Cloud Warehouses Still Need This?

Snowflake and BigQuery both answer with their own scripting extensions rather than adopting PL/SQL or T-SQL directly. The dbt/ELT culture this series otherwise follows pushes logic into SQL models + Python, not stored procedures — reach for procedural SQL when logic genuinely needs to live inside the database (a trigger reacting to a write), not as a default way to write transformations. Part 13 picks this back up from the platform-choice angle: an organization already running Oracle or SQL Server for OLTP is exactly where PL/SQL/T-SQL fluency keeps paying off, regardless of which warehouse sits alongside it.