MySQL Stored Procedures

50 min

MySQL stored procedures are reusable SQL code blocks stored in the database that can be called by applications. Stored procedures can accept input parameters, return output parameters, and return result sets. They enable centralizing business logic in the database, reducing network traffic, and improving security by controlling database access. Understanding stored procedures enables you to build efficient, maintainable database applications.

Stored procedures are created using CREATE PROCEDURE and can contain variables, control structures (IF/ELSE, loops), error handling, and SQL statements. Procedures can be simple (no parameters) or complex (multiple parameters, conditional logic, transactions). Stored procedures are compiled and cached, providing good performance. Understanding procedure syntax enables you to create effective stored procedures.

Parameters in stored procedures can be IN (input), OUT (output), or INOUT (both). IN parameters pass values into the procedure, OUT parameters return values, and INOUT parameters do both. Procedures can return result sets using SELECT statements, enabling them to function like views with parameters. Understanding parameter types enables flexible procedure design.

Error handling in stored procedures uses DECLARE HANDLER to catch and handle errors. Handlers can catch specific error codes or general SQL exceptions. Error handling enables procedures to gracefully handle errors, roll back transactions, and return meaningful error messages. Understanding error handling enables robust stored procedures.

Stored procedures can use transactions to ensure atomicity across multiple operations. START TRANSACTION, COMMIT, and ROLLBACK enable procedures to group operations that must succeed or fail together. Transaction support enables procedures to maintain data consistency. Understanding transactions in procedures enables reliable database operations.

Best practices for stored procedures include keeping them focused on single responsibilities, using appropriate parameter types, handling errors gracefully, and documenting their purpose and usage. Procedures should be tested thoroughly and maintained as part of the database schema. Understanding best practices enables you to create maintainable, reliable stored procedures.

Key Concepts

Stored procedures are reusable SQL code blocks stored in the database.
Procedures can accept IN, OUT, and INOUT parameters.
Procedures can return result sets and output parameters.
Error handling enables graceful error management.
Procedures can use transactions for atomic operations.

Learning Objectives

Master

Creating stored procedures with parameters
Implementing error handling in procedures
Using transactions within procedures
Calling and managing stored procedures

Develop

Understanding database programming patterns
Designing maintainable stored procedures
Centralizing business logic in the database

Tips

Create procedure: DELIMITER // CREATE PROCEDURE name(IN param INT) BEGIN ... END // DELIMITER ;
Use error handlers: DECLARE EXIT HANDLER FOR SQLEXCEPTION BEGIN ROLLBACK; RESIGNAL; END;
Call procedure: CALL procedure_name(param1, param2);
Use transactions: START TRANSACTION; ... COMMIT; for atomic operations.

Common Pitfalls

Putting too much business logic in procedures, reducing application flexibility.
Not handling errors, causing procedure failures to break applications.
Not using transactions when needed, causing data inconsistency.
Not documenting procedures, making maintenance difficult.

Summary

Stored procedures enable reusable, centralized database logic.
Procedures support parameters, error handling, and transactions.
Understanding procedures enables efficient database programming.
Following best practices ensures maintainable procedures.

Exercise

Create stored procedures for common database operations.

DELIMITER //

-- Stored procedure to get user orders
CREATE PROCEDURE GetUserOrders(IN user_id_param INT)
BEGIN
    SELECT 
        o.id,
        o.total_amount,
        o.order_date,
        o.status,
        COUNT(oi.id) as item_count
    FROM orders o
    LEFT JOIN order_items oi ON o.id = oi.order_id
    WHERE o.user_id = user_id_param
    GROUP BY o.id
    ORDER BY o.order_date DESC;
END //

-- Stored procedure to add new product
CREATE PROCEDURE AddProduct(
    IN p_name VARCHAR(255),
    IN p_description TEXT,
    IN p_price DECIMAL(10,2),
    IN p_stock INT,
    IN p_category ENUM('electronics', 'clothing', 'books')
)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        RESIGNAL;
    END;
    
    START TRANSACTION;
    
    INSERT INTO products (name, description, price, stock, category)
    VALUES (p_name, p_description, p_price, p_stock, p_category);
    
    COMMIT;
    
    SELECT LAST_INSERT_ID() as new_product_id;
END //

DELIMITER ;

-- Use the stored procedures
CALL GetUserOrders(1);
CALL AddProduct('New Product', 'Description', 49.99, 10, 'electronics');

Exercise Tips

Use DELIMITER to change statement delimiter for procedure creation.
Return result sets: use SELECT statements in procedures to return data.
Use LAST_INSERT_ID() to get auto-increment value after INSERT.
Test procedures thoroughly: procedures run in database context with different error handling.

MySQL Stored Procedures