CTEs, Stored Procedures, Functions & Triggers

Part 1: Common Table Expressions (CTEs)

What Is a CTE?

A Common Table Expression (CTE) is a named, temporary result set that you define at the beginning of a query using the WITH keyword. You can then reference it by name within that query, just like a table or view.

Think of a CTE as a temporary alias for a subquery — but one that you write once at the top and reference multiple times, making your query far more readable than nesting subqueries.

-- Without CTE: nested subquery is hard to read
SELECT first_name, last_name, salary
FROM employees
WHERE salary > (
    SELECT AVG(salary)
    FROM employees
    WHERE department_id = (
        SELECT department_id
        FROM departments
        WHERE department_name = 'IT'
    )
);

-- With CTE: each step is named and readable
WITH it_dept AS (
    SELECT department_id
    FROM   departments
    WHERE  department_name = 'IT'
),
it_avg_salary AS (
    SELECT AVG(salary) AS avg_sal
    FROM   employees
    WHERE  department_id IN (SELECT department_id FROM it_dept)
)
SELECT first_name, last_name, salary
FROM   employees
WHERE  salary > (SELECT avg_sal FROM it_avg_salary);

Basic CTE Syntax

WITH cte_name AS (
    -- Your SELECT query here
    SELECT column1, column2
    FROM   some_table
    WHERE  some_condition
)
-- Now use the CTE as if it were a table
SELECT *
FROM   cte_name
WHERE  column1 = 'some_value';

Multiple CTEs in One Query

You can chain multiple CTEs by separating them with commas. Each CTE can reference the CTEs defined before it:

-- Step 1: get department IDs and their average salaries
WITH dept_averages AS (
    SELECT department_id,
           AVG(salary)   AS avg_salary,
           COUNT(*)      AS headcount
    FROM   employees
    GROUP BY department_id
),
-- Step 2: label departments as above/below the company average (uses CTE 1)
dept_performance AS (
    SELECT d.department_name,
           da.avg_salary,
           da.headcount,
           CASE
               WHEN da.avg_salary > (SELECT AVG(salary) FROM employees)
               THEN 'Above Average'
               ELSE 'Below Average'
           END AS performance_label
    FROM   dept_averages da
    JOIN   departments d ON da.department_id = d.department_id
)
-- Final query: uses CTE 2
SELECT *
FROM   dept_performance
ORDER BY avg_salary DESC;

CTEs vs Subqueries vs Temp Tables

*Note: In most databases, a CTE is not materialized — it's executed each time it's referenced. For expensive CTEs used multiple times, a temp table may be faster.

Recursive CTEs

A recursive CTE is one that references itself. It's the standard SQL way to work with hierarchical data (org charts, category trees, bill of materials) or to generate series.

Structure of a Recursive CTE

Every recursive CTE has exactly two parts connected by UNION ALL:

WITH RECURSIVE cte_name AS (
    -- 1. ANCHOR MEMBER: the starting point (no self-reference)
    SELECT ...
    FROM   base_table
    WHERE  starting_condition

    UNION ALL

    -- 2. RECURSIVE MEMBER: references the CTE itself
    SELECT ...
    FROM   base_table
    JOIN   cte_name ON join_condition    -- self-reference!
    WHERE  termination_condition         -- MUST eventually become false!
)
SELECT * FROM cte_name;

Example 1: Org Chart Traversal

The employees table has a manager_id column — a self-referential foreign key. Each employee's manager is another employee. This forms a tree (hierarchy).

-- Oracle / PostgreSQL: traverse the employee org chart top-down
WITH org_chart (employee_id, first_name, last_name, manager_id, depth, path) AS (
    -- Anchor: start with the CEO (no manager)
    SELECT employee_id,
           first_name,
           last_name,
           manager_id,
           0 AS depth,
           first_name || ' ' || last_name AS path
    FROM   employees
    WHERE  manager_id IS NULL          -- the top of the hierarchy

    UNION ALL

    -- Recursive: find each employee's direct reports
    SELECT e.employee_id,
           e.first_name,
           e.last_name,
           e.manager_id,
           oc.depth + 1,
           oc.path || ' > ' || e.first_name || ' ' || e.last_name
    FROM   employees e
    JOIN   org_chart oc ON e.manager_id = oc.employee_id  -- join to parent
)
SELECT LPAD(' ', depth * 4) || first_name || ' ' || last_name AS org_tree,
       depth,
       path
FROM   org_chart
ORDER BY path;

-- Result (indented by depth):
-- ORG_TREE                    | DEPTH | PATH
-- ----------------------------|-------|---------------------------
-- Steven King                 | 0     | Steven King
--     Neena Kochhar           | 1     | Steven King > Neena Kochhar
--         Lex De Haan         | 2     | Steven King > Neena Kochhar > Lex...
--     Lex De Haan             | 1     | Steven King > Lex De Haan

Example 2: Generate a Number Series

Useful for generating test data, date series, or sequence numbers without a numbers table:

-- PostgreSQL: generate numbers 1 to 10
WITH RECURSIVE numbers AS (
    SELECT 1 AS n           -- Anchor: start at 1
    UNION ALL
    SELECT n + 1            -- Recursive: add 1 each iteration
    FROM   numbers
    WHERE  n < 10           -- Termination: stop at 10
)
SELECT n FROM numbers;
-- Result: 1, 2, 3, 4, 5, 6, 7, 8, 9, 10

-- Oracle: generate a number series using CONNECT BY (simpler alternative)
SELECT LEVEL AS n
FROM   DUAL
CONNECT BY LEVEL <= 10;

Example 3: Generate a Date Series

-- PostgreSQL: generate every date in January 2024
WITH RECURSIVE dates AS (
    SELECT DATE '2024-01-01' AS d    -- Anchor: start date
    UNION ALL
    SELECT d + 1                     -- Recursive: add 1 day
    FROM   dates
    WHERE  d < DATE '2024-01-31'     -- Termination: stop at end of month
)
SELECT d FROM dates;

-- Oracle equivalent using CONNECT BY:
SELECT DATE '2024-01-01' + LEVEL - 1 AS d
FROM   DUAL
CONNECT BY LEVEL <= 31;

-- DANGEROUS: no termination condition — infinite loop!
WITH RECURSIVE bad_cte AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM bad_cte    -- no WHERE clause to stop!
)
SELECT * FROM bad_cte;           -- will run forever

-- SAFE: always include a termination condition
WITH RECURSIVE good_cte AS (
    SELECT 1 AS n
    UNION ALL
    SELECT n + 1 FROM good_cte
    WHERE n < 100                -- stops at 100
)
SELECT * FROM good_cte;

SELECT employee_id, manager_id, LEVEL
FROM   employees
CONNECT BY NOCYCLE PRIOR employee_id = manager_id
START WITH manager_id IS NULL;

Part 2: Stored Procedures

What Is a Stored Procedure?

A stored procedure is a named block of SQL (and procedural logic) that is saved in the database and can be called by name. Think of it as a function in a programming language — you write it once, name it, and call it whenever needed.

Benefits:

• Reusability: write complex logic once, call from multiple places
• Performance: procedure is parsed and compiled once; execution plan is cached
• Security: grant EXECUTE privilege on the procedure instead of direct table access
• Encapsulation: hide complex business logic inside the database

Oracle PL/SQL Stored Procedure

-- Create a procedure that gives a salary raise to all employees in a department
CREATE OR REPLACE PROCEDURE give_raise (
    p_department_id IN  employees.department_id%TYPE,   -- IN: read-only input
    p_pct_increase  IN  NUMBER,                         -- IN: raise percentage
    p_rows_updated  OUT NUMBER                          -- OUT: returns count to caller
)
IS
BEGIN
    UPDATE employees
    SET    salary = salary * (1 + p_pct_increase / 100)
    WHERE  department_id = p_department_id;
    
    p_rows_updated := SQL%ROWCOUNT;   -- number of rows affected by the UPDATE
    
    COMMIT;
    
    DBMS_OUTPUT.PUT_LINE('Updated ' || p_rows_updated || ' employees in dept ' || p_department_id);
    
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
        RAISE;  -- re-raise the exception to the caller
END give_raise;
/

Calling a Stored Procedure (Oracle)

-- Method 1: EXEC shorthand (SQL*Plus / SQL Developer)
EXEC give_raise(60, 10, :rows_updated);

-- Method 2: Anonymous PL/SQL block
DECLARE
    v_count NUMBER;
BEGIN
    give_raise(
        p_department_id => 60,
        p_pct_increase  => 10,
        p_rows_updated  => v_count
    );
    DBMS_OUTPUT.PUT_LINE('Rows updated: ' || v_count);
END;
/

-- Method 3: CALL syntax (SQL standard)
CALL give_raise(60, 10, NULL);

PostgreSQL Stored Procedure

-- PostgreSQL: CREATE PROCEDURE (PostgreSQL 11+)
CREATE OR REPLACE PROCEDURE give_raise(
    p_dept_id    INT,
    p_pct        NUMERIC,
    INOUT p_rows INT DEFAULT 0
)
LANGUAGE plpgsql
AS $$
BEGIN
    UPDATE employees
    SET    salary = salary * (1 + p_pct / 100)
    WHERE  department_id = p_dept_id;
    
    GET DIAGNOSTICS p_rows = ROW_COUNT;
    
    COMMIT;
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE;
END;
$$;

-- Call in PostgreSQL:
CALL give_raise(60, 10, 0);

Parameters: IN, OUT, IN OUT

-- Procedure demonstrating all three parameter types
CREATE OR REPLACE PROCEDURE calculate_bonus (
    p_employee_id  IN     NUMBER,         -- input: which employee
    p_bonus_amount OUT    NUMBER,         -- output: computed bonus amount
    p_salary       IN OUT NUMBER          -- in-out: pass salary in, get adjusted salary out
)
IS
    v_dept_id    employees.department_id%TYPE;
BEGIN
    -- Look up the employee's department
    SELECT department_id INTO v_dept_id
    FROM   employees
    WHERE  employee_id = p_employee_id;
    
    -- Calculate bonus based on salary (IN OUT parameter p_salary is readable)
    IF p_salary > 10000 THEN
        p_bonus_amount := p_salary * 0.20;  -- 20% bonus for high earners
    ELSE
        p_bonus_amount := p_salary * 0.10;  -- 10% bonus for others
    END IF;
    
    -- Adjust the salary (IN OUT: we write a new value back)
    p_salary := p_salary + p_bonus_amount;
END calculate_bonus;
/

Exception Handling

CREATE OR REPLACE PROCEDURE hire_employee (
    p_emp_id   IN NUMBER,
    p_name     IN VARCHAR2,
    p_salary   IN NUMBER,
    p_dept_id  IN NUMBER
)
IS
BEGIN
    INSERT INTO employees (employee_id, first_name, salary, department_id)
    VALUES (p_emp_id, p_name, p_salary, p_dept_id);
    
    COMMIT;
    
EXCEPTION
    WHEN DUP_VAL_ON_INDEX THEN
        -- Specific Oracle exception: duplicate primary key
        DBMS_OUTPUT.PUT_LINE('Employee ID ' || p_emp_id || ' already exists.');
        ROLLBACK;
        
    WHEN VALUE_ERROR THEN
        -- Data type or value issue
        DBMS_OUTPUT.PUT_LINE('Invalid data provided.');
        ROLLBACK;
        
    WHEN OTHERS THEN
        -- Catch-all for any other exception
        DBMS_OUTPUT.PUT_LINE('Unexpected error: ' || SQLERRM);
        ROLLBACK;
        RAISE;  -- re-raise so the caller knows something went wrong
END hire_employee;
/

Part 3: User-Defined Functions (UDFs)

Procedure vs Function — The Key Difference

Oracle Function Syntax

-- Function: returns an employee's annual salary including bonus
CREATE OR REPLACE FUNCTION get_annual_compensation (
    p_employee_id IN employees.employee_id%TYPE
)
RETURN NUMBER
IS
    v_salary         employees.salary%TYPE;
    v_commission_pct employees.commission_pct%TYPE;
    v_annual         NUMBER;
BEGIN
    SELECT salary, NVL(commission_pct, 0)
    INTO   v_salary, v_commission_pct
    FROM   employees
    WHERE  employee_id = p_employee_id;
    
    -- Annual = 12 months + commission percentage of annual
    v_annual := (v_salary * 12) * (1 + v_commission_pct);
    
    RETURN v_annual;
    
EXCEPTION
    WHEN NO_DATA_FOUND THEN
        RETURN NULL;   -- employee not found
END get_annual_compensation;
/

Using Functions in Queries

-- Use the function directly in a SELECT statement
SELECT employee_id,
       first_name,
       last_name,
       salary,
       get_annual_compensation(employee_id) AS annual_comp
FROM   employees
WHERE  department_id = 80
ORDER BY annual_comp DESC;

Scalar Function — Simple Utility

-- Function: format a name as "Last, First"
CREATE OR REPLACE FUNCTION format_name (
    p_first VARCHAR2,
    p_last  VARCHAR2
)
RETURN VARCHAR2
IS
BEGIN
    RETURN TRIM(p_last) || ', ' || TRIM(p_first);
END format_name;
/

-- Use in queries:
SELECT format_name(first_name, last_name) AS display_name, salary
FROM   employees
ORDER BY last_name;

-- Result:
-- DISPLAY_NAME     | SALARY
-- -----------------|-------
-- Abel, Ellen      | 11000
-- Ande, Sundar     | 6400
-- Atkinson, Mozhe  | 2800

Part 4: Triggers

What Is a Trigger?

A trigger is a stored procedure that automatically executes ("fires") in response to a specific event on a table — an INSERT, UPDATE, or DELETE. You don't call it manually; the database calls it automatically.

Think of a trigger as a motion-sensor light — you don't turn it on; it turns on by itself when something happens.

Why Use Triggers?

• Audit logging: automatically record who changed what and when
• Enforce complex business rules that can't be expressed as constraints
• Derived columns: automatically calculate a value when a row changes
• Cascade custom logic: automatically update related tables

Trigger Timing: BEFORE vs AFTER

FOR EACH ROW vs Statement-Level

BEFORE INSERT Trigger — Auto-set Audit Columns

-- Automatically set created_at and created_by before every INSERT
CREATE OR REPLACE TRIGGER trg_employees_before_insert
BEFORE INSERT ON employees
FOR EACH ROW
BEGIN
    :NEW.created_at := SYSTIMESTAMP;
    :NEW.created_by := USER;
END;
/

-- Now this INSERT automatically gets audit columns set:
INSERT INTO employees (employee_id, first_name, last_name, salary)
VALUES (213, 'Test', 'Employee', 60000);
-- created_at and created_by are set automatically by the trigger

AFTER UPDATE Trigger — Salary Change Audit Log

A classic trigger use case: record every salary change in an audit table.

-- First: create the audit log table
CREATE TABLE salary_audit_log (
    log_id          NUMBER GENERATED ALWAYS AS IDENTITY,
    employee_id     NUMBER,
    old_salary      NUMBER,
    new_salary      NUMBER,
    changed_by      VARCHAR2(100),
    changed_at      TIMESTAMP,
    change_reason   VARCHAR2(500)
);

-- Create the trigger
CREATE OR REPLACE TRIGGER trg_salary_audit
AFTER UPDATE OF salary ON employees    -- fires only when salary column is updated
FOR EACH ROW
WHEN (NEW.salary != OLD.salary)        -- only if salary actually changed
BEGIN
    INSERT INTO salary_audit_log (
        employee_id, old_salary, new_salary, changed_by, changed_at
    )
    VALUES (
        :NEW.employee_id,
        :OLD.salary,         -- :OLD = values BEFORE the update
        :NEW.salary,         -- :NEW = values AFTER the update
        USER,
        SYSTIMESTAMP
    );
END;
/

-- Test it:
UPDATE employees SET salary = 99000 WHERE employee_id = 103;
-- The trigger fires automatically and inserts into salary_audit_log

SELECT * FROM salary_audit_log WHERE employee_id = 103;
-- EMPLOYEE_ID | OLD_SALARY | NEW_SALARY | CHANGED_BY | CHANGED_AT
-- ------------|------------|------------|------------|------------------
-- 103         | 90000      | 99000      | HR_USER    | 2024-03-15 10:23:44

BEFORE UPDATE Trigger — Validate Business Rules

-- Prevent salary decreases (business rule: we don't cut salaries)
CREATE OR REPLACE TRIGGER trg_no_salary_cut
BEFORE UPDATE OF salary ON employees
FOR EACH ROW
BEGIN
    IF :NEW.salary < :OLD.salary THEN
        RAISE_APPLICATION_ERROR(
            -20001,
            'Salary decrease not allowed. Employee ' || :OLD.employee_id ||
            ' current salary: ' || :OLD.salary ||
            ', attempted new salary: ' || :NEW.salary
        );
    END IF;
END;
/

-- This UPDATE will raise an error:
UPDATE employees SET salary = 50000 WHERE employee_id = 100;
-- ORA-20001: Salary decrease not allowed. Employee 100 current salary: 24000...
-- (assuming employee 100 earns 24000 — which is higher than 50000 is not, but
--  if they earn 100000, trying to set 50000 would fail)

Trigger Timing Summary Table

:OLD and :NEW Pseudorecords

Inside a row-level trigger, :OLD and :NEW let you access the row values before and after the DML operation:

-- In an UPDATE trigger:
-- :OLD.salary → what salary was BEFORE the UPDATE
-- :NEW.salary → what salary will be AFTER the UPDATE (can be modified in BEFORE trigger)

Risks and Downsides of Triggers

Putting It All Together — A Complete Example

Here's a system that uses CTEs, a procedure, and a trigger together:

-- 1. CTE to find underperforming departments (used in the procedure)
-- 2. Procedure to apply targeted raises
-- 3. Trigger to audit every salary change

-- The audit trigger (already shown above) fires automatically on every UPDATE.

-- Procedure: give raises to departments below the company average
CREATE OR REPLACE PROCEDURE raise_low_salary_depts (
    p_raise_pct     IN  NUMBER DEFAULT 5,
    p_rows_affected OUT NUMBER
)
IS
BEGIN
    -- Use a CTE-style inline view to find below-average departments
    UPDATE employees e
    SET    salary = salary * (1 + p_raise_pct / 100)
    WHERE  department_id IN (
        -- Find departments whose average salary < company average
        WITH dept_avgs AS (
            SELECT department_id,
                   AVG(salary) AS dept_avg
            FROM   employees
            GROUP BY department_id
        )
        SELECT department_id
        FROM   dept_avgs
        WHERE  dept_avg < (SELECT AVG(salary) FROM employees)
    );
    
    p_rows_affected := SQL%ROWCOUNT;
    COMMIT;  -- The salary audit trigger fires for each row updated
END raise_low_salary_depts;
/

-- Call the procedure:
DECLARE
    v_count NUMBER;
BEGIN
    raise_low_salary_depts(p_raise_pct => 7, p_rows_affected => v_count);
    DBMS_OUTPUT.PUT_LINE('Raised salaries for ' || v_count || ' employees.');
END;
/
-- The trg_salary_audit trigger fires automatically for every salary change,
-- logging each change to salary_audit_log without any extra code.

Common Errors

Interview Corner

-- Function: used in SELECT
CREATE OR REPLACE FUNCTION get_annual_salary(p_id NUMBER) RETURN NUMBER IS
    v_sal NUMBER;
BEGIN
    SELECT salary * 12 INTO v_sal FROM employees WHERE employee_id = p_id;
    RETURN v_sal;
END;

SELECT last_name, get_annual_salary(employee_id) AS annual FROM employees;

-- Procedure: used for an action
CREATE OR REPLACE PROCEDURE give_raise(p_id NUMBER, p_pct NUMBER) IS
BEGIN
    UPDATE employees SET salary = salary * (1 + p_pct/100)
    WHERE employee_id = p_id;
    COMMIT;
END;

Related Topics

• Subqueries — CTEs and subqueries are closely related; understand the trade-offs
• Window Functions — wrap window function logic in CTEs for multi-step calculations
• Views — named views are similar to CTEs but persist in the schema
• Performance — use the MATERIALIZE hint and EXPLAIN PLAN to understand CTE execution