Stored Procedures and Functions: Encapsulating Database Logic

Introduction to Stored Procedures and Functions

Stored procedures and functions are precompiled SQL code blocks stored in the database. They encapsulate business logic, improve performance, and provide reusable database operations.

Benefits of Stored Procedures and Functions

• Performance: Precompiled and cached for faster execution
• Security: Reduce SQL injection risks and control data access
• Maintainability: Centralize business logic in the database
• Reusability: Call the same logic from multiple applications
• Network Traffic: Reduce data transfer between application and database
• Transaction Control: Better control over complex transactions

Stored Procedures vs Functions

Creating Stored Procedures

Basic Syntax

DELIMITER //
CREATE PROCEDURE procedure_name(
    [IN | OUT | INOUT] parameter_name data_type,
    ...
)
BEGIN
    -- SQL statements
END //
DELIMITER ;

Simple Stored Procedure

DELIMITER //
CREATE PROCEDURE GetCustomerCount()
BEGIN
    SELECT COUNT(*) AS total_customers FROM customers;
END //
DELIMITER ;

-- Call the procedure
CALL GetCustomerCount();

Procedure with Parameters

DELIMITER //
CREATE PROCEDURE GetCustomersByCity(
    IN city_name VARCHAR(50)
)
BEGIN
    SELECT 
        customer_id,
        first_name,
        last_name,
        email
    FROM customers
    WHERE city = city_name
    ORDER BY last_name;
END //
DELIMITER ;

-- Call with parameter
CALL GetCustomersByCity('New York');

Procedure with OUT Parameters

DELIMITER //
CREATE PROCEDURE GetCustomerStats(
    OUT total_customers INT,
    OUT avg_orders_per_customer DECIMAL(10,2)
)
BEGIN
    SELECT COUNT(*) INTO total_customers FROM customers;
    
    SELECT AVG(order_count) INTO avg_orders_per_customer
    FROM (
        SELECT COUNT(order_id) as order_count
        FROM orders
        GROUP BY customer_id
    ) customer_orders;
END //
DELIMITER ;

-- Call and get output values
CALL GetCustomerStats(@total, @avg_orders);
SELECT @total AS total_customers, @avg_orders AS avg_orders_per_customer;

Control Flow in Stored Procedures

IF-ELSE Statements

DELIMITER //
CREATE PROCEDURE UpdateCustomerStatus(
    IN customer_id INT,
    IN order_count INT
)
BEGIN
    IF order_count >= 10 THEN
        UPDATE customers 
        SET status = 'VIP' 
        WHERE customer_id = customer_id;
    ELSEIF order_count >= 5 THEN
        UPDATE customers 
        SET status = 'Premium' 
        WHERE customer_id = customer_id;
    ELSE
        UPDATE customers 
        SET status = 'Regular' 
        WHERE customer_id = customer_id;
    END IF;
END //
DELIMITER ;

CASE Statements

DELIMITER //
CREATE PROCEDURE GetShippingCost(
    IN weight DECIMAL(5,2),
    IN distance INT,
    OUT shipping_cost DECIMAL(8,2)
)
BEGIN
    SET shipping_cost = CASE
        WHEN weight <= 1 AND distance <= 100 THEN 5.00
        WHEN weight <= 1 AND distance <= 500 THEN 8.00
        WHEN weight <= 5 AND distance <= 100 THEN 10.00
        WHEN weight <= 5 AND distance <= 500 THEN 15.00
        ELSE 25.00
    END;
END //
DELIMITER ;

WHILE Loops

DELIMITER //
CREATE PROCEDURE GenerateSequence(
    IN max_num INT
)
BEGIN
    DECLARE counter INT DEFAULT 1;
    
    DROP TEMPORARY TABLE IF EXISTS temp_sequence;
    CREATE TEMPORARY TABLE temp_sequence (num INT);
    
    WHILE counter <= max_num DO
        INSERT INTO temp_sequence VALUES (counter);
        SET counter = counter + 1;
    END WHILE;
    
    SELECT * FROM temp_sequence;
END //
DELIMITER ;

Error Handling

DECLARE HANDLER

DELIMITER //
CREATE PROCEDURE SafeInsertCustomer(
    IN p_first_name VARCHAR(50),
    IN p_last_name VARCHAR(50),
    IN p_email VARCHAR(100),
    OUT result_message VARCHAR(255)
)
BEGIN
    DECLARE duplicate_key CONDITION FOR 1062;
    DECLARE EXIT HANDLER FOR duplicate_key
    BEGIN
        SET result_message = 'Error: Email already exists';
    END;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET result_message = 'Error: Database error occurred';
        ROLLBACK;
    END;
    
    START TRANSACTION;
    
    INSERT INTO customers (first_name, last_name, email)
    VALUES (p_first_name, p_last_name, p_email);
    
    SET result_message = 'Customer inserted successfully';
    COMMIT;
END //
DELIMITER ;

Creating Functions

Basic Function Syntax

DELIMITER //
CREATE FUNCTION function_name(parameter_list)
RETURNS return_data_type
[DETERMINISTIC | NOT DETERMINISTIC]
[READS SQL DATA | MODIFIES SQL DATA | NO SQL | CONTAINS SQL]
BEGIN
    -- Function body
    RETURN value;
END //
DELIMITER ;

Simple Function

DELIMITER //
CREATE FUNCTION CalculateTax(amount DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE tax_rate DECIMAL(4,3) DEFAULT 0.085;
    RETURN amount * tax_rate;
END //
DELIMITER ;

-- Use the function
SELECT 
    product_name,
    price,
    CalculateTax(price) AS tax_amount,
    price + CalculateTax(price) AS total_price
FROM products;

Function with Database Lookup

DELIMITER //
CREATE FUNCTION GetCustomerOrderCount(customer_id INT)
RETURNS INT
READS SQL DATA
DETERMINISTIC
BEGIN
    DECLARE order_count INT DEFAULT 0;
    
    SELECT COUNT(*) INTO order_count
    FROM orders
    WHERE customer_id = customer_id;
    
    RETURN order_count;
END //
DELIMITER ;

-- Use the function
SELECT 
    customer_id,
    first_name,
    last_name,
    GetCustomerOrderCount(customer_id) AS total_orders
FROM customers;

Advanced Examples

Complex Order Processing Procedure

DELIMITER //
CREATE PROCEDURE ProcessOrder(
    IN p_customer_id INT,
    IN p_product_id INT,
    IN p_quantity INT,
    OUT p_order_id INT,
    OUT p_result_message VARCHAR(255)
)
BEGIN
    DECLARE v_stock_quantity INT DEFAULT 0;
    DECLARE v_unit_price DECIMAL(10,2) DEFAULT 0;
    DECLARE v_total_amount DECIMAL(10,2) DEFAULT 0;
    
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        SET p_result_message = 'Error: Transaction failed';
        ROLLBACK;
    END;
    
    START TRANSACTION;
    
    -- Check stock availability
    SELECT stock_quantity, price 
    INTO v_stock_quantity, v_unit_price
    FROM products 
    WHERE product_id = p_product_id;
    
    IF v_stock_quantity < p_quantity THEN
        SET p_result_message = 'Error: Insufficient stock';
        ROLLBACK;
    ELSE
        -- Calculate total
        SET v_total_amount = v_unit_price * p_quantity;
        
        -- Create order
        INSERT INTO orders (customer_id, order_date, total_amount, status)
        VALUES (p_customer_id, NOW(), v_total_amount, 'pending');
        
        SET p_order_id = LAST_INSERT_ID();
        
        -- Add order item
        INSERT INTO order_items (order_id, product_id, quantity, unit_price)
        VALUES (p_order_id, p_product_id, p_quantity, v_unit_price);
        
        -- Update stock
        UPDATE products 
        SET stock_quantity = stock_quantity - p_quantity
        WHERE product_id = p_product_id;
        
        SET p_result_message = 'Order processed successfully';
        COMMIT;
    END IF;
END //
DELIMITER ;

Recursive Function (MySQL 8.0+)

DELIMITER //
CREATE FUNCTION Factorial(n INT)
RETURNS BIGINT
DETERMINISTIC
NO SQL
BEGIN
    IF n <= 1 THEN
        RETURN 1;
    ELSE
        RETURN n * Factorial(n - 1);
    END IF;
END //
DELIMITER ;

-- Use the function
SELECT Factorial(5) AS result;  -- Returns 120

Managing Stored Procedures and Functions

Viewing Procedures and Functions

-- Show all procedures in current database
SHOW PROCEDURE STATUS WHERE Db = DATABASE();

-- Show all functions in current database
SHOW FUNCTION STATUS WHERE Db = DATABASE();

-- Show procedure/function definition
SHOW CREATE PROCEDURE GetCustomersByCity;
SHOW CREATE FUNCTION CalculateTax;

Dropping Procedures and Functions

DROP PROCEDURE IF EXISTS GetCustomersByCity;
DROP FUNCTION IF EXISTS CalculateTax;

Modifying Procedures and Functions

-- You cannot modify existing procedures/functions
-- You must drop and recreate them
DROP PROCEDURE IF EXISTS GetCustomersByCity;

DELIMITER //
CREATE PROCEDURE GetCustomersByCity(
    IN city_name VARCHAR(50),
    IN limit_count INT DEFAULT 10
)
BEGIN
    SELECT 
        customer_id,
        first_name,
        last_name,
        email
    FROM customers
    WHERE city = city_name
    ORDER BY last_name
    LIMIT limit_count;
END //
DELIMITER ;

Best Practices

• Use meaningful names: Clear, descriptive procedure/function names
• Handle errors properly: Always include error handling
• Use transactions: For procedures that modify data
• Document parameters: Comment parameter purposes and types
• Keep functions pure: Functions should not modify data
• Use appropriate data types: Match parameter types to column types
• Test thoroughly: Test with various input scenarios

Security Considerations

• SQL Injection: Stored procedures help prevent SQL injection
• Access Control: Grant EXECUTE permissions selectively
• Data Validation: Validate input parameters
• Audit Trail: Log important procedure executions

Performance Tips

• Use indexes: Ensure queries in procedures use appropriate indexes
• Minimize network calls: Combine multiple operations
• Cache results: Use temporary tables for complex calculations
• Avoid cursors: Use set-based operations when possible

Practice Exercises

1. Create a procedure to calculate customer lifetime value
2. Write a function to format phone numbers consistently
3. Build a procedure to archive old orders and clean up data
4. Create a function to calculate shipping costs based on weight and distance

Next Steps

In the final lesson, we'll cover database security, backup strategies, and maintenance best practices.