MySQL & Database Design

Stored Procedures

13 min Lesson 21 of 40

Stored Procedures

Stored procedures are named collections of SQL statements stored in the database that can be executed as a single unit. They encapsulate complex business logic, improve performance, and enhance security. In this lesson, we'll learn how to create and use stored procedures effectively.

What is a Stored Procedure?

A stored procedure is a prepared SQL code that you can save and reuse. Instead of writing the same SQL statements repeatedly, you can encapsulate them in a procedure and call it whenever needed.

Key Benefit: Stored procedures reduce network traffic by executing multiple statements on the server with a single call, and they centralize business logic in one place.

Creating Basic Stored Procedures

Use the CREATE PROCEDURE statement. Note the delimiter change to allow semicolons inside the procedure:

-- Change delimiter temporarily
DELIMITER //

-- Simple procedure without parameters
CREATE PROCEDURE GetAllCustomers()
BEGIN
    SELECT customer_id, customer_name, email
    FROM customers
    ORDER BY customer_name;
END //

DELIMITER ;

-- Call the procedure
CALL GetAllCustomers();

Tip: Always use DELIMITER to change the statement delimiter when creating procedures. This allows semicolons inside the procedure body.

Parameters: IN, OUT, INOUT

Procedures can accept parameters in three modes:

DELIMITER //

-- IN parameter: pass value to procedure
CREATE PROCEDURE GetCustomerByID(IN customer_id_param INT)
BEGIN
    SELECT customer_id, customer_name, email, phone
    FROM customers
    WHERE customer_id = customer_id_param;
END //

-- OUT parameter: return value from procedure
CREATE PROCEDURE GetCustomerCount(OUT total_customers INT)
BEGIN
    SELECT COUNT(*) INTO total_customers
    FROM customers;
END //

-- INOUT parameter: both input and output
CREATE PROCEDURE DoubleValue(INOUT value INT)
BEGIN
    SET value = value * 2;
END //

DELIMITER ;

-- Calling procedures with parameters
CALL GetCustomerByID(5);

-- OUT parameter usage
SET @count = 0;
CALL GetCustomerCount(@count);
SELECT @count AS total_customers;

-- INOUT parameter usage
SET @num = 10;
CALL DoubleValue(@num);
SELECT @num AS doubled_value; -- Returns 20

Variables and Flow Control

Procedures can use variables and control structures:

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_message VARCHAR(255)
)
BEGIN
    DECLARE v_stock INT;
    DECLARE v_price DECIMAL(10,2);

    -- Check stock availability
    SELECT stock_quantity, price INTO v_stock, v_price
    FROM products
    WHERE product_id = p_product_id;

    IF v_stock < p_quantity THEN
        SET p_message = 'Insufficient stock';
        SET p_order_id = -1;
    ELSE
        -- Create order
        INSERT INTO orders (customer_id, order_date, total_amount, status)
        VALUES (p_customer_id, NOW(), v_price * p_quantity, '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_price);

        -- Update stock
        UPDATE products
        SET stock_quantity = stock_quantity - p_quantity
        WHERE product_id = p_product_id;

        SET p_message = 'Order created successfully';
    END IF;
END //

DELIMITER ;

-- Use the procedure
SET @order_id = 0;
SET @message = '';
CALL ProcessOrder(1, 5, 2, @order_id, @message);
SELECT @order_id, @message;

Conditional Logic

Use IF, CASE, and other control structures:

DELIMITER //

CREATE PROCEDURE GetCustomerDiscount(
    IN p_customer_id INT,
    OUT p_discount_rate DECIMAL(5,2)
)
BEGIN
    DECLARE v_total_spent DECIMAL(10,2);
    DECLARE v_order_count INT;

    -- Get customer statistics
    SELECT COALESCE(SUM(total_amount), 0), COUNT(*)
    INTO v_total_spent, v_order_count
    FROM orders
    WHERE customer_id = p_customer_id;

    -- Determine discount using CASE
    SET p_discount_rate = CASE
        WHEN v_total_spent > 10000 THEN 20.00
        WHEN v_total_spent > 5000 THEN 15.00
        WHEN v_total_spent > 1000 THEN 10.00
        WHEN v_order_count > 5 THEN 5.00
        ELSE 0.00
    END;
END //

DELIMITER ;

Loops in Procedures

MySQL supports WHILE, REPEAT, and LOOP constructs:

DELIMITER //

-- WHILE loop example
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 //

-- REPEAT loop example
CREATE PROCEDURE CalculateFactorial(
    IN n INT,
    OUT factorial BIGINT
)
BEGIN
    DECLARE i INT DEFAULT 1;
    SET factorial = 1;

    REPEAT
        SET factorial = factorial * i;
        SET i = i + 1;
    UNTIL i > n
    END REPEAT;
END //

-- LOOP with LEAVE (break) and ITERATE (continue)
CREATE PROCEDURE ProcessBatch(IN batch_size INT)
BEGIN
    DECLARE counter INT DEFAULT 0;
    DECLARE v_id INT;

    process_loop: LOOP
        SET counter = counter + 1;

        IF counter > batch_size THEN
            LEAVE process_loop; -- Exit loop
        END IF;

        -- Process logic here
        -- Use ITERATE process_loop to skip to next iteration

    END LOOP process_loop;
END //

DELIMITER ;

Error Handling

Use DECLARE handlers to catch and handle errors:

DELIMITER //

CREATE PROCEDURE SafeTransfer(
    IN p_from_account INT,
    IN p_to_account INT,
    IN p_amount DECIMAL(10,2),
    OUT p_success BOOLEAN,
    OUT p_message VARCHAR(255)
)
BEGIN
    DECLARE exit handler FOR SQLEXCEPTION
    BEGIN
        -- Rollback on any error
        ROLLBACK;
        SET p_success = FALSE;
        SET p_message = 'Transfer failed: Database error';
    END;

    DECLARE exit handler FOR SQLWARNING
    BEGIN
        ROLLBACK;
        SET p_success = FALSE;
        SET p_message = 'Transfer failed: Warning occurred';
    END;

    -- Start transaction
    START TRANSACTION;

    -- Deduct from source account
    UPDATE accounts
    SET balance = balance - p_amount
    WHERE account_id = p_from_account
    AND balance >= p_amount;

    -- Check if deduction was successful
    IF ROW_COUNT() = 0 THEN
        ROLLBACK;
        SET p_success = FALSE;
        SET p_message = 'Insufficient balance';
    ELSE
        -- Add to destination account
        UPDATE accounts
        SET balance = balance + p_amount
        WHERE account_id = p_to_account;

        COMMIT;
        SET p_success = TRUE;
        SET p_message = 'Transfer successful';
    END IF;
END //

DELIMITER ;

-- Use the procedure
SET @success = FALSE;
SET @msg = '';
CALL SafeTransfer(1, 2, 100.00, @success, @msg);
SELECT @success, @msg;

Important: Always handle errors in procedures that modify data. Use transactions with proper error handlers to maintain data integrity.

Calling Procedures from PHP

Here's how to call stored procedures from PHP using MySQLi and PDO:

// MySQLi approach
$mysqli = new mysqli('localhost', 'user', 'password', 'database');

// Call procedure with OUT parameter
$mysqli->query("SET @count = 0");
$mysqli->query("CALL GetCustomerCount(@count)");
$result = $mysqli->query("SELECT @count AS total");
$row = $result->fetch_assoc();
echo "Total customers: " . $row['total'];

// PDO approach
$pdo = new PDO('mysql:host=localhost;dbname=database', 'user', 'password');

// Call procedure with IN parameter
$stmt = $pdo->prepare("CALL GetCustomerByID(?)");
$stmt->execute([5]);
$customers = $stmt->fetchAll(PDO::FETCH_ASSOC);

// Call procedure with OUT parameters
$stmt = $pdo->prepare("CALL ProcessOrder(?, ?, ?, @order_id, @message)");
$stmt->execute([1, 5, 2]);

$result = $pdo->query("SELECT @order_id AS order_id, @message AS message");
$row = $result->fetch(PDO::FETCH_ASSOC);
echo "Order ID: " . $row['order_id'];
echo "Message: " . $row['message'];

Managing Stored Procedures

-- List all procedures in current database
SHOW PROCEDURE STATUS WHERE Db = 'your_database';

-- Show procedure definition
SHOW CREATE PROCEDURE GetAllCustomers;

-- Drop a procedure
DROP PROCEDURE IF EXISTS GetAllCustomers;

-- Check if procedure exists before creating
DROP PROCEDURE IF EXISTS ProcessOrder;
DELIMITER //
CREATE PROCEDURE ProcessOrder(...)
BEGIN
    -- procedure body
END //
DELIMITER ;

Real-World Procedure Examples

DELIMITER //

-- Complex order processing procedure
CREATE PROCEDURE CompleteOrderProcessing(
    IN p_order_id INT,
    OUT p_status VARCHAR(50),
    OUT p_tracking_number VARCHAR(100)
)
BEGIN
    DECLARE v_customer_id INT;
    DECLARE v_total_amount DECIMAL(10,2);

    DECLARE exit handler FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SET p_status = 'ERROR';
        SET p_tracking_number = NULL;
    END;

    START TRANSACTION;

    -- Get order details
    SELECT customer_id, total_amount INTO v_customer_id, v_total_amount
    FROM orders
    WHERE order_id = p_order_id;

    -- Update order status
    UPDATE orders
    SET status = 'processing',
        processed_at = NOW()
    WHERE order_id = p_order_id;

    -- Generate tracking number
    SET p_tracking_number = CONCAT('TRK', LPAD(p_order_id, 10, '0'));

    -- Update shipping info
    INSERT INTO shipments (order_id, tracking_number, status, created_at)
    VALUES (p_order_id, p_tracking_number, 'pending', NOW());

    -- Update customer statistics
    UPDATE customers
    SET total_orders = total_orders + 1,
        total_spent = total_spent + v_total_amount,
        last_order_date = NOW()
    WHERE customer_id = v_customer_id;

    COMMIT;
    SET p_status = 'SUCCESS';
END //

-- Data cleanup procedure
CREATE PROCEDURE CleanupOldData(IN days_old INT)
BEGIN
    DECLARE deleted_orders INT DEFAULT 0;
    DECLARE deleted_logs INT DEFAULT 0;

    -- Delete old cancelled orders
    DELETE FROM orders
    WHERE status = 'cancelled'
    AND created_at < DATE_SUB(NOW(), INTERVAL days_old DAY);

    SET deleted_orders = ROW_COUNT();

    -- Delete old log entries
    DELETE FROM activity_logs
    WHERE created_at < DATE_SUB(NOW(), INTERVAL days_old DAY);

    SET deleted_logs = ROW_COUNT();

    -- Log cleanup action
    INSERT INTO maintenance_log (action, details, created_at)
    VALUES (
        'cleanup',
        CONCAT('Deleted ', deleted_orders, ' orders and ', deleted_logs, ' logs'),
        NOW()
    );
END //

DELIMITER ;

Advantages and Disadvantages

Advantages:
✓ Reduced network traffic (one call vs many queries)
✓ Centralized business logic
✓ Improved security (hide table structure)
✓ Better performance (pre-compiled)
✓ Code reusability
✓ Transaction management

Disadvantages:
✗ Vendor lock-in (MySQL-specific syntax)
✗ Harder to version control
✗ More difficult to debug
✗ Can increase database server load
✗ Not suitable for all applications

Practice Exercise:

Create an Inventory Management Procedure:

Create a procedure 'RestockProduct' that:
- Takes product_id and quantity as IN parameters
- Returns success status and message as OUT parameters
- Checks if product exists
- Updates stock quantity
- Logs the restock action with timestamp
- Handles errors with appropriate messages
Test the procedure with valid and invalid product IDs

Best Practices

✓ Use descriptive procedure names (verb + noun)
✓ Add comments to explain complex logic
✓ Always use error handlers for data modifications
✓ Use transactions for operations that modify multiple tables
✓ Validate input parameters
✓ Return meaningful status messages
✓ Keep procedures focused (single responsibility)
✓ Document parameter types and purposes
✗ Don't put too much logic in procedures
✗ Avoid overly complex procedures
✗ Don't ignore error handling

Summary

In this lesson, you learned:

Stored procedures encapsulate SQL logic in the database
Parameters can be IN, OUT, or INOUT
Variables and control structures enable complex logic
Error handlers ensure data integrity
Procedures reduce network traffic and centralize logic
DELIMITER is required when creating procedures
Procedures can be called from PHP using MySQLi or PDO

Next Up: In the next lesson, we'll explore User-Defined Functions and Triggers to further automate database operations!