Chapter 7: Stored Procedures and Functions

Stored Procedures

Stored procedures are SQL code saved in the database that can be called repeatedly.

Basic Syntax

DELIMITER //

CREATE PROCEDURE GetCustomerOrders(IN customer_id INT)
BEGIN
    SELECT * FROM orders
    WHERE customer_id = customer_id
    ORDER BY order_date DESC;
END //

DELIMITER ;

-- Call procedure
CALL GetCustomerOrders(123);

Parameters

DELIMITER //

CREATE PROCEDURE UpdateProductPrice(
    IN product_id INT,
    IN new_price DECIMAL(10,2),
    OUT old_price DECIMAL(10,2)
)
BEGIN
    SELECT price INTO old_price
    FROM products WHERE id = product_id;
    
    UPDATE products
    SET price = new_price
    WHERE id = product_id;
END //

DELIMITER ;

-- Call with OUT parameter
CALL UpdateProductPrice(1, 19.99, @old);
SELECT @old;

Control Flow

DELIMITER //

CREATE PROCEDURE ProcessOrder(
    IN order_id INT,
    OUT status_message VARCHAR(100)
)
BEGIN
    DECLARE order_total DECIMAL(10,2);
    DECLARE customer_credit DECIMAL(10,2);
    
    SELECT total INTO order_total
    FROM orders WHERE id = order_id;
    
    IF order_total IS NULL THEN
        SET status_message = 'Order not found';
    ELSEIF order_total > 1000 THEN
        SET status_message = 'Large order - requires approval';
    ELSE
        SET status_message = 'Order processed';
        UPDATE orders SET status = 'confirmed'
        WHERE id = order_id;
    END IF;
END //

DELIMITER ;

Loops

DELIMITER //

CREATE PROCEDURE GenerateReport()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE prod_id INT;
    DECLARE prod_name VARCHAR(100);
    
    DECLARE cur CURSOR FOR
        SELECT id, name FROM products;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND
        SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO prod_id, prod_name;
        
        IF done THEN
            LEAVE read_loop;
        END IF;
        
        -- Process each product
        INSERT INTO report_temp (product_id, product_name)
        VALUES (prod_id, prod_name);
    END LOOP;
    
    CLOSE cur;
END //

DELIMITER ;

Stored Functions

Functions return a single value and can be used in SQL statements.

DELIMITER //

CREATE FUNCTION CalculateDiscount(
    total DECIMAL(10,2),
    customer_type VARCHAR(20)
)
RETURNS DECIMAL(10,2)
DETERMINISTIC
BEGIN
    DECLARE discount DECIMAL(10,2);
    
    IF customer_type = 'gold' THEN
        SET discount = total * 0.20;
    ELSEIF customer_type = 'silver' THEN
        SET discount = total * 0.10;
    ELSE
        SET discount = 0;
    END IF;
    
    RETURN discount;
END //

DELIMITER ;

-- Use in query
SELECT
    order_id,
    total,
    CalculateDiscount(total, customer_type) AS discount
FROM orders;

Triggers

Triggers automatically execute in response to table events.

DELIMITER //

CREATE TRIGGER update_inventory
AFTER INSERT ON order_items
FOR EACH ROW
BEGIN
    UPDATE products
    SET stock_quantity = stock_quantity - NEW.quantity
    WHERE id = NEW.product_id;
END //

CREATE TRIGGER audit_price_changes
BEFORE UPDATE ON products
FOR EACH ROW
BEGIN
    IF OLD.price != NEW.price THEN
        INSERT INTO price_audit (product_id, old_price, new_price, changed_at)
        VALUES (NEW.id, OLD.price, NEW.price, NOW());
    END IF;
END //

DELIMITER ;

Error Handling

DELIMITER //

CREATE PROCEDURE SafeTransfer(
    IN from_account INT,
    IN to_account INT,
    IN amount DECIMAL(10,2)
)
BEGIN
    DECLARE exit handler for SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT 'Transfer failed' AS error_message;
    END;
    
    START TRANSACTION;
    
    UPDATE accounts
    SET balance = balance - amount
    WHERE id = from_account;
    
    UPDATE accounts
    SET balance = balance + amount
    WHERE id = to_account;
    
    COMMIT;
    SELECT 'Transfer successful' AS message;
END //

DELIMITER ;

Managing Procedures and Functions

-- List procedures
SHOW PROCEDURE STATUS;

-- View procedure code
SHOW CREATE PROCEDURE procedure_name;

-- Drop procedure
DROP PROCEDURE IF EXISTS procedure_name;

-- List functions
SHOW FUNCTION STATUS;

-- Drop function
DROP FUNCTION IF EXISTS function_name;

Best Practices

1. Use meaningful names for procedures and parameters
2. Add comments to complex logic
3. Handle errors appropriately
4. Keep procedures focused on single tasks
5. Test thoroughly before production
6. Consider performance - procedures aren't always faster

---

Next: Chapter 8: Security and User Management