Stored Procedures

Stored Procedures & Functions

Table of Contents

• Overview
• When to Use
• Quick Start
• Reference Guides
• Best Practices

Overview

Implement stored procedures, functions, and triggers for business logic, data validation, and performance optimization. Covers procedure design, error handling, and performance considerations.

When to Use

• Business logic encapsulation
• Complex multi-step operations
• Data validation and constraints
• Audit trail maintenance
• Performance optimization
• Code reusability across applications
• Trigger-based automation

Quick Start

PostgreSQL - Scalar Function:

-- Create function returning single value
CREATE OR REPLACE FUNCTION calculate_order_total(
  p_subtotal DECIMAL,
  p_tax_rate DECIMAL,
  p_shipping DECIMAL
)
RETURNS DECIMAL AS $$
BEGIN
  RETURN ROUND((p_subtotal * (1 + p_tax_rate) + p_shipping)::NUMERIC, 2);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Use in queries
SELECT id, subtotal, calculate_order_total(subtotal, 0.08, 10) as total
FROM orders;

-- Or in application code
SELECT * FROM orders
WHERE calculate_order_total(subtotal, 0.08, 10) > 100;

Reference Guides

Detailed implementations in the references/ directory:

Best Practices

✅ DO

• Follow established patterns and conventions
• Write clean, maintainable code
• Add appropriate documentation
• Test thoroughly before deploying

❌ DON'T

• Skip testing or validation
• Ignore error handling
• Hard-code configuration values

Complex Procedures with Error Handling

Complex Procedures with Error Handling

MySQL - Transaction Management:

DELIMITER //

CREATE PROCEDURE create_order(
  IN p_user_id INT,
  IN p_items JSON,
  OUT p_order_id INT,
  OUT p_success BOOLEAN,
  OUT p_error VARCHAR(500)
)
BEGIN
  DECLARE EXIT HANDLER FOR SQLEXCEPTION
  BEGIN
    ROLLBACK;
    SET p_success = FALSE;
    SET p_error = 'Transaction failed';
  END;

  START TRANSACTION;

  -- Create order
  INSERT INTO orders (user_id, status, created_at)
  VALUES (p_user_id, 'pending', NOW());

  SET p_order_id = LAST_INSERT_ID();

  -- Add items to order (assuming items is JSON array)
  -- Would require JSON parsing in MySQL 5.7+
  -- INSERT INTO order_items (order_id, product_id, quantity)
  -- SELECT p_order_id, JSON_EXTRACT(...), ...

  -- Update inventory
  UPDATE inventory
  SET quantity = quantity - 1
  WHERE product_id IN (
    SELECT product_id FROM order_items WHERE order_id = p_order_id
  );

  -- Check inventory
  IF EXISTS (SELECT 1 FROM inventory WHERE quantity < 0) THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Insufficient inventory';
  END IF;

  COMMIT;
  SET p_success = TRUE;
  SET p_error = NULL;
END //

DELIMITER ;

MySQL Triggers

MySQL Triggers

MySQL - Insert Trigger:

DELIMITER //

CREATE TRIGGER create_order_trigger
AFTER INSERT ON orders
FOR EACH ROW
BEGIN
  -- Update user statistics
  UPDATE user_stats
  SET total_orders = total_orders + 1,
      total_spent = total_spent + NEW.total
  WHERE user_id = NEW.user_id;

  -- Create audit log
  INSERT INTO audit_log (table_name, operation, record_id, timestamp)
  VALUES ('orders', 'INSERT', NEW.id, NOW());
END //

DELIMITER ;

MySQL - Update Prevention Trigger:

DELIMITER //

CREATE TRIGGER prevent_old_order_update
BEFORE UPDATE ON orders
FOR EACH ROW
BEGIN
  IF OLD.status = 'completed' THEN
    SIGNAL SQLSTATE '45000'
    SET MESSAGE_TEXT = 'Cannot update completed orders';
  END IF;
END //

DELIMITER ;

PostgreSQL Triggers

PostgreSQL Triggers

Audit Trail Trigger:

-- Audit table
CREATE TABLE user_audit_log (
  id UUID PRIMARY KEY DEFAULT gen_random_uuid(),
  user_id UUID,
  operation VARCHAR(10),
  old_values JSONB,
  new_values JSONB,
  changed_at TIMESTAMP DEFAULT NOW()
);

-- Trigger function
CREATE OR REPLACE FUNCTION audit_user_changes()
RETURNS TRIGGER AS $$
BEGIN
  INSERT INTO user_audit_log (user_id, operation, old_values, new_values)
  VALUES (
    COALESCE(NEW.id, OLD.id),
    TG_OP,
    to_jsonb(OLD),
    to_jsonb(NEW)
  );
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

-- Create trigger
CREATE TRIGGER user_audit_trigger
AFTER INSERT OR UPDATE OR DELETE ON users
FOR EACH ROW
EXECUTE FUNCTION audit_user_changes();

Update Timestamp Trigger:

CREATE OR REPLACE FUNCTION update_timestamp()
RETURNS TRIGGER AS $$
BEGIN
  NEW.updated_at = NOW();
  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER update_users_timestamp
BEFORE UPDATE ON users
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

CREATE TRIGGER update_orders_timestamp
BEFORE UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION update_timestamp();

Validation Trigger:

CREATE OR REPLACE FUNCTION validate_order()
RETURNS TRIGGER AS $$
BEGIN
  -- Validate order total
  IF NEW.total < 0 THEN
    RAISE EXCEPTION 'Order total cannot be negative';
  END IF;

  -- Validate user exists
  IF NOT EXISTS (SELECT 1 FROM users WHERE id = NEW.user_id) THEN
    RAISE EXCEPTION 'User does not exist';
  END IF;

  RETURN NEW;
END;
$$ LANGUAGE plpgsql;

CREATE TRIGGER validate_order_trigger
BEFORE INSERT OR UPDATE ON orders
FOR EACH ROW
EXECUTE FUNCTION validate_order();

Simple Functions

Simple Functions

PostgreSQL - Scalar Function:

-- Create function returning single value
CREATE OR REPLACE FUNCTION calculate_order_total(
  p_subtotal DECIMAL,
  p_tax_rate DECIMAL,
  p_shipping DECIMAL
)
RETURNS DECIMAL AS $$
BEGIN
  RETURN ROUND((p_subtotal * (1 + p_tax_rate) + p_shipping)::NUMERIC, 2);
END;
$$ LANGUAGE plpgsql IMMUTABLE;

-- Use in queries
SELECT id, subtotal, calculate_order_total(subtotal, 0.08, 10) as total
FROM orders;

-- Or in application code
SELECT * FROM orders
WHERE calculate_order_total(subtotal, 0.08, 10) > 100;

PostgreSQL - Table Returning Function:

-- Return set of rows
CREATE OR REPLACE FUNCTION get_user_orders(p_user_id UUID)
RETURNS TABLE (
  order_id UUID,
  order_date TIMESTAMP,
  total DECIMAL,
  status VARCHAR
) AS $$
BEGIN
  RETURN QUERY
  SELECT o.id, o.created_at, o.total, o.status
  FROM orders o
  WHERE o.user_id = p_user_id
  ORDER BY o.created_at DESC;
END;
$$ LANGUAGE plpgsql STABLE;

-- Use function
SELECT * FROM get_user_orders('user-123');

Simple Procedures

Simple Procedures

MySQL - Basic Procedure:

-- Simple procedure
DELIMITER //

CREATE PROCEDURE get_user_by_email(IN p_email VARCHAR(255))
BEGIN
  SELECT id, email, name, created_at
  FROM users
  WHERE email = p_email;
END //

DELIMITER ;

-- Call procedure
CALL get_user_by_email('john@example.com');

MySQL - Procedure with OUT Parameters:

DELIMITER //

CREATE PROCEDURE calculate_user_stats(
  IN p_user_id INT,
  OUT p_total_orders INT,
  OUT p_total_spent DECIMAL
)
BEGIN
  SELECT
    COUNT(*),
    SUM(total)
  INTO p_total_orders, p_total_spent
  FROM orders
  WHERE user_id = p_user_id AND status != 'cancelled';

  IF p_total_orders IS NULL THEN
    SET p_total_orders = 0;
    SET p_total_spent = 0;
  END IF;
END //

DELIMITER ;

-- Call procedure
CALL calculate_user_stats(123, @orders, @spent);
SELECT @orders as total_orders, @spent as total_spent;

Stored Procedures

Stored Procedures

PostgreSQL - Procedure with OUT Parameters:

-- Stored procedure with output parameters
CREATE OR REPLACE PROCEDURE process_order(
  p_order_id UUID,
  OUT p_success BOOLEAN,
  OUT p_message VARCHAR
)
LANGUAGE plpgsql AS $$
BEGIN
  BEGIN
    -- Start transaction
    UPDATE orders SET status = 'processing' WHERE id = p_order_id;

    UPDATE inventory
    SET quantity = quantity - 1
    WHERE product_id IN (
      SELECT product_id FROM order_items WHERE order_id = p_order_id
    );

    -- Check inventory
    IF EXISTS (SELECT 1 FROM inventory WHERE quantity < 0) THEN
      RAISE EXCEPTION 'Insufficient inventory';
    END IF;

    p_success := true;
    p_message := 'Order processed successfully';
  EXCEPTION WHEN OTHERS THEN
    p_success := false;
    p_message := SQLERRM;
    -- Transaction automatically rolled back
  END;
END;
$$;

-- Call procedure
CALL process_order('order-123', success, message);
SELECT success, message;

Complex Procedure with Logic:

CREATE OR REPLACE PROCEDURE transfer_funds(
  p_from_account_id INT,
  p_to_account_id INT,
  p_amount DECIMAL,
  OUT p_success BOOLEAN,
  OUT p_error_message VARCHAR
)
LANGUAGE plpgsql AS $$
DECLARE
  v_from_balance DECIMAL;
BEGIN
  BEGIN
    -- Check balance
    SELECT balance INTO v_from_balance
    FROM accounts
    WHERE id = p_from_account_id
    FOR UPDATE;

    IF v_from_balance < p_amount THEN
      RAISE EXCEPTION 'Insufficient funds';
    END IF;

    -- Debit from account
    UPDATE accounts
    SET balance = balance - p_amount
    WHERE id = p_from_account_id;

    -- Credit to account
    UPDATE accounts
    SET balance = balance + p_amount
    WHERE id = p_to_account_id;

    -- Log transaction
    INSERT INTO transaction_log (from_id, to_id, amount, status)
    VALUES (p_from_account_id, p_to_account_id, p_amount, 'completed');

    p_success := true;
    p_error_message := NULL;
  EXCEPTION WHEN OTHERS THEN
    p_success := false;
    p_error_message := SQLERRM;
  END;
END;
$$;