A stored procedure is a named block of procedural SQL code stored in the database itself. You call it like a function: pass inputs, it executes multiple statements (possibly with logic and transactions), and optionally returns results. The code runs on the database server — no network round trips for each statement.

Why use stored procedures?

PL/pgSQL (PostgreSQL)

-- Create a procedure to place an order
CREATE OR REPLACE PROCEDURE place_order(
  p_user_id   INTEGER,
  p_product_id INTEGER,
  p_quantity   INTEGER
)
LANGUAGE plpgsql
AS $$
DECLARE
  v_order_id   INTEGER;
  v_unit_price NUMERIC(10,2);
BEGIN
  -- Look up current price
  SELECT price INTO v_unit_price
  FROM   products
  WHERE  id = p_product_id;

  IF v_unit_price IS NULL THEN
    RAISE EXCEPTION 'Product % not found', p_product_id;
  END IF;

  -- Create the order
  INSERT INTO orders (user_id, status, placed_at)
  VALUES (p_user_id, 'pending', NOW())
  RETURNING id INTO v_order_id;

  -- Add the line item
  INSERT INTO order_items (order_id, product_id, quantity, unit_price)
  VALUES (v_order_id, p_product_id, p_quantity, v_unit_price);

  RAISE NOTICE 'Order % created, total: %', v_order_id, v_unit_price * p_quantity;
END;
$$;

-- Call the procedure
CALL place_order(1, 101, 2);

PL/SQL (Oracle)

-- Oracle PL/SQL syntax
CREATE OR REPLACE PROCEDURE place_order(
  p_user_id   IN NUMBER,
  p_product_id IN NUMBER,
  p_quantity   IN NUMBER
) AS
  v_order_id   NUMBER;
  v_unit_price NUMBER(10,2);
BEGIN
  SELECT price INTO v_unit_price
  FROM   products
  WHERE  id = p_product_id;

  INSERT INTO orders (id, user_id, status, placed_at)
  VALUES (order_seq.NEXTVAL, p_user_id, 'pending', SYSDATE)
  RETURNING id INTO v_order_id;

  INSERT INTO order_items (order_id, product_id, quantity, unit_price)
  VALUES (v_order_id, p_product_id, p_quantity, v_unit_price);

  COMMIT;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    RAISE_APPLICATION_ERROR(-20001, 'Product not found: ' || p_product_id);
END place_order;
/

-- Call
EXEC place_order(1, 101, 2);

MySQL

DELIMITER //
CREATE PROCEDURE place_order(
  IN p_user_id   INT,
  IN p_product_id INT,
  IN p_quantity   INT
)
BEGIN
  DECLARE v_order_id   INT;
  DECLARE v_unit_price DECIMAL(10,2);

  SELECT price INTO v_unit_price
  FROM   products
  WHERE  id = p_product_id;

  IF v_unit_price IS NULL THEN
    SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Product not found';
  END IF;

  INSERT INTO orders (user_id, status, placed_at)
  VALUES (p_user_id, 'pending', NOW());
  SET v_order_id = LAST_INSERT_ID();

  INSERT INTO order_items (order_id, product_id, quantity, unit_price)
  VALUES (v_order_id, p_product_id, p_quantity, v_unit_price);
END //
DELIMITER ;

-- Call
CALL place_order(1, 101, 2);

A function is like a procedure but it returns a value and can be called inside a SELECT. Procedures perform actions; functions compute results.

Scalar function (PL/pgSQL)

-- Calculate order total
CREATE OR REPLACE FUNCTION order_total(p_order_id INTEGER)
RETURNS NUMERIC
LANGUAGE plpgsql
AS $$
DECLARE
  v_total NUMERIC(10,2);
BEGIN
  SELECT SUM(quantity * unit_price)
  INTO   v_total
  FROM   order_items
  WHERE  order_id = p_order_id;

  RETURN COALESCE(v_total, 0);
END;
$$;

-- Use in a query
SELECT id, placed_at, order_total(id) AS total
FROM   orders
WHERE  user_id = 1;

Table-returning function (PL/pgSQL)

-- Return a set of rows
CREATE OR REPLACE FUNCTION user_order_summary(p_user_id INTEGER)
RETURNS TABLE(order_id INTEGER, placed_at TIMESTAMP, total NUMERIC)
LANGUAGE plpgsql
AS $$
BEGIN
  RETURN QUERY
    SELECT o.id, o.placed_at, SUM(oi.quantity * oi.unit_price)
    FROM   orders o
    JOIN   order_items oi ON oi.order_id = o.id
    WHERE  o.user_id = p_user_id
    GROUP BY o.id, o.placed_at
    ORDER BY o.placed_at DESC;
END;
$$;

-- Call like a table
SELECT * FROM user_order_summary(1);

Oracle PL/SQL function

CREATE OR REPLACE FUNCTION order_total(p_order_id NUMBER)
RETURN NUMBER
AS
  v_total NUMBER(10,2);
BEGIN
  SELECT SUM(quantity * unit_price)
  INTO   v_total
  FROM   order_items
  WHERE  order_id = p_order_id;

  RETURN NVL(v_total, 0);
END order_total;
/

-- Use
SELECT id, order_total(id) FROM orders WHERE user_id = 1;

MySQL function

DELIMITER //
CREATE FUNCTION order_total(p_order_id INT)
RETURNS DECIMAL(10,2)
DETERMINISTIC
READS SQL DATA
BEGIN
  DECLARE v_total DECIMAL(10,2);

  SELECT SUM(quantity * unit_price) INTO v_total
  FROM   order_items
  WHERE  order_id = p_order_id;

  RETURN COALESCE(v_total, 0);
END //
DELIMITER ;

SELECT id, order_total(id) FROM orders WHERE user_id = 1;

A trigger is procedural code that fires automatically in response to an INSERT, UPDATE, or DELETE on a table. Use them for audit logging, computed columns, validation that’s too complex for CHECK constraints, or cascading side-effects.

Trigger anatomy

• Timing: BEFORE or AFTER the event.
• Event: INSERT, UPDATE, DELETE (or combinations).
• Granularity: FOR EACH ROW (fires per row) or FOR EACH STATEMENT (fires once per statement).
• Access to data: NEW (the row being inserted/updated) and OLD (the row before update/delete).

Audit trail trigger (PL/pgSQL)

-- Audit table
CREATE TABLE orders_audit (
  audit_id    SERIAL PRIMARY KEY,
  order_id    INTEGER NOT NULL,
  old_status  TEXT,
  new_status  TEXT,
  changed_at  TIMESTAMP NOT NULL DEFAULT NOW(),
  changed_by  TEXT
);

-- Trigger function
CREATE OR REPLACE FUNCTION log_order_status_change()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  IF OLD.status IS DISTINCT FROM NEW.status THEN
    INSERT INTO orders_audit (order_id, old_status, new_status, changed_by)
    VALUES (NEW.id, OLD.status, NEW.status, current_user);
  END IF;
  RETURN NEW;
END;
$$;

-- Attach trigger
CREATE TRIGGER trg_orders_status_audit
  AFTER UPDATE ON orders
  FOR EACH ROW
  EXECUTE FUNCTION log_order_status_change();

-- Now any status change is automatically logged:
UPDATE orders SET status = 'shipped' WHERE id = 1001;
SELECT * FROM orders_audit;

BEFORE trigger — auto-set updated_at

-- PostgreSQL: auto-update timestamp on any row change
CREATE OR REPLACE FUNCTION set_updated_at()
RETURNS TRIGGER
LANGUAGE plpgsql
AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$;

CREATE TRIGGER trg_users_updated_at
  BEFORE UPDATE ON users
  FOR EACH ROW
  EXECUTE FUNCTION set_updated_at();

MySQL trigger

DELIMITER //
CREATE TRIGGER trg_orders_status_audit
AFTER UPDATE ON orders
FOR EACH ROW
BEGIN
  IF OLD.status != NEW.status THEN
    INSERT INTO orders_audit (order_id, old_status, new_status, changed_at)
    VALUES (NEW.id, OLD.status, NEW.status, NOW());
  END IF;
END //
DELIMITER ;

Oracle PL/SQL trigger

CREATE OR REPLACE TRIGGER trg_orders_status_audit
AFTER UPDATE OF status ON orders
FOR EACH ROW
BEGIN
  IF :OLD.status != :NEW.status THEN
    INSERT INTO orders_audit (order_id, old_status, new_status, changed_at)
    VALUES (:NEW.id, :OLD.status, :NEW.status, SYSDATE);
  END IF;
END;
/

Procedural SQL provides standard imperative constructs: variables, IF/ELSE, loops (WHILE, FOR, LOOP), and cursors for row-by-row iteration over a result set. Cursors are powerful but almost always slower than set-based SQL — use them only when row-by-row logic is genuinely needed.

Variables & assignment

-- PL/pgSQL
DECLARE
  v_count   INTEGER := 0;
  v_name    TEXT;
  v_total   NUMERIC(10,2) DEFAULT 0.00;
BEGIN
  SELECT full_name INTO v_name FROM users WHERE id = 1;
  v_count := v_count + 1;
END;

-- Oracle PL/SQL
DECLARE
  v_count   NUMBER := 0;
  v_name    VARCHAR2(100);
BEGIN
  SELECT full_name INTO v_name FROM users WHERE id = 1;
  v_count := v_count + 1;
END;
/

-- MySQL
DECLARE v_count INT DEFAULT 0;
DECLARE v_name VARCHAR(100);
SELECT full_name INTO v_name FROM users WHERE id = 1;
SET v_count = v_count + 1;

IF / ELSIF / ELSE

-- PL/pgSQL / Oracle (same syntax)
IF v_total > 100 THEN
  v_discount := 0.10;
ELSIF v_total > 50 THEN
  v_discount := 0.05;
ELSE
  v_discount := 0;
END IF;

-- MySQL uses ELSEIF (no space)
IF v_total > 100 THEN
  SET v_discount = 0.10;
ELSEIF v_total > 50 THEN
  SET v_discount = 0.05;
ELSE
  SET v_discount = 0;
END IF;

Loops

-- PL/pgSQL: simple loop with EXIT
LOOP
  v_count := v_count + 1;
  EXIT WHEN v_count >= 10;
END LOOP;

-- FOR loop over a range
FOR i IN 1..10 LOOP
  RAISE NOTICE 'Iteration %', i;
END LOOP;

-- FOR loop over a query (implicit cursor)
FOR rec IN SELECT id, email FROM users LOOP
  RAISE NOTICE 'User %: %', rec.id, rec.email;
END LOOP;

-- WHILE loop
WHILE v_count < 10 LOOP
  v_count := v_count + 1;
END LOOP;

Explicit cursors

A cursor lets you process a result set one row at a time. Declare it, open it, fetch rows in a loop, close it.

-- PL/pgSQL explicit cursor
CREATE OR REPLACE PROCEDURE process_pending_orders()
LANGUAGE plpgsql
AS $$
DECLARE
  cur_orders CURSOR FOR
    SELECT id, user_id FROM orders WHERE status = 'pending';
  v_order_id  INTEGER;
  v_user_id   INTEGER;
BEGIN
  OPEN cur_orders;
  LOOP
    FETCH cur_orders INTO v_order_id, v_user_id;
    EXIT WHEN NOT FOUND;

    -- Process each pending order
    RAISE NOTICE 'Processing order % for user %', v_order_id, v_user_id;
    UPDATE orders SET status = 'processing' WHERE id = v_order_id;
  END LOOP;
  CLOSE cur_orders;
END;
$$;

Oracle PL/SQL cursor

DECLARE
  CURSOR cur_orders IS
    SELECT id, user_id FROM orders WHERE status = 'pending';
  v_order_id  NUMBER;
  v_user_id   NUMBER;
BEGIN
  OPEN cur_orders;
  LOOP
    FETCH cur_orders INTO v_order_id, v_user_id;
    EXIT WHEN cur_orders%NOTFOUND;

    UPDATE orders SET status = 'processing' WHERE id = v_order_id;
  END LOOP;
  CLOSE cur_orders;
END;
/

Exception handling

-- PL/pgSQL
BEGIN
  INSERT INTO users (id, email, full_name)
  VALUES (1, 'duplicate@example.com', 'Test');
EXCEPTION
  WHEN unique_violation THEN
    RAISE NOTICE 'Email already exists, skipping.';
  WHEN OTHERS THEN
    RAISE NOTICE 'Unexpected error: %', SQLERRM;
END;

-- Oracle PL/SQL
BEGIN
  INSERT INTO users (id, email, full_name)
  VALUES (1, 'duplicate@example.com', 'Test');
EXCEPTION
  WHEN DUP_VAL_ON_INDEX THEN
    DBMS_OUTPUT.PUT_LINE('Email already exists');
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE('Error: ' || SQLERRM);
END;
/