WebsiteLogo

Advanced MySQL Features: Stored Procedures and Triggers

Explore advanced MySQL features like stored procedures (precompiled SQL code) and triggers (automatic actions triggered by database events).

⬅ Previous Next ➡

Mastering MySQL: Stored Procedures - Practical Examples

What are Stored Procedures?

Stored procedures are precompiled SQL statements stored within the database. They are like functions in programming languages, allowing you to encapsulate a set of SQL commands into a single unit. This unit can then be executed by calling the procedure's name. They offer several advantages:

  • Improved Performance: Stored procedures are compiled and stored, reducing network traffic and parsing overhead.
  • Enhanced Security: Grant execute permissions on stored procedures instead of direct access to tables.
  • Code Reusability: Write the logic once and reuse it multiple times.
  • Data Integrity: Enforce business rules and data validation within the procedure, ensuring consistency.

Practical Examples

1. Data Validation

This example demonstrates a stored procedure to validate user input before inserting data into a table. Let's assume we have a users table with columns: id, username, email, and age. We want to ensure the age is within a reasonable range (e.g., 18-100) and the email is a valid format. 

 DELIMITER //

CREATE PROCEDURE ValidateAndInsertUser (
    IN p_username VARCHAR(255),
    IN p_email VARCHAR(255),
    IN p_age INT
)
BEGIN
    -- Validate Age
    IF p_age < 18 OR p_age > 100 THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid age. Age must be between 18 and 100.';
    END IF;

    -- Validate Email (Simple Example - more robust validation is recommended)
    IF p_email NOT LIKE '%@%.%' THEN
        SIGNAL SQLSTATE '45000' SET MESSAGE_TEXT = 'Invalid email format.';
    END IF;

    -- Insert the user if validation passes
    INSERT INTO users (username, email, age) VALUES (p_username, p_email, p_age);

END //

DELIMITER ;

-- Example Usage:
-- CALL ValidateAndInsertUser('JohnDoe', 'john.doe@example.com', 30);  -- Success
-- CALL ValidateAndInsertUser('JaneDoe', 'jane.doeexample.com', 25); -- Error: Invalid email format
-- CALL ValidateAndInsertUser('YoungOne', 'young.one@example.com', 15); -- Error: Invalid age

Explanation:

  • The DELIMITER // statement changes the delimiter to // so that the entire procedure definition can be treated as a single statement. This is necessary because the procedure body itself contains semicolons.
  • The procedure ValidateAndInsertUser takes three input parameters: p_username, p_email, and p_age.
  • The IF statements check the validity of the age and email.
  • If either validation fails, the SIGNAL SQLSTATE '45000' statement raises an error, preventing the insertion. 45000 is a general-purpose SQLSTATE for user-defined exceptions. The MESSAGE_TEXT provides a descriptive error message.
  • If both validations pass, the INSERT statement adds the user to the users table.
  • The DELIMITER ; statement resets the delimiter back to the default semicolon.

2. Auditing Changes

This example shows how to create a stored procedure to log changes made to a table. We'll create an audit_log table to store the audit information and a trigger that calls the stored procedure upon updates to a table. 

 -- Create Audit Log Table
CREATE TABLE audit_log (
    id INT AUTO_INCREMENT PRIMARY KEY,
    table_name VARCHAR(255),
    record_id INT,
    column_name VARCHAR(255),
    old_value TEXT,
    new_value TEXT,
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER //

CREATE PROCEDURE LogUserChanges (
    IN p_table_name VARCHAR(255),
    IN p_record_id INT,
    IN p_column_name VARCHAR(255),
    IN p_old_value TEXT,
    IN p_new_value TEXT
)
BEGIN
    INSERT INTO audit_log (table_name, record_id, column_name, old_value, new_value)
    VALUES (p_table_name, p_record_id, p_column_name, p_old_value, p_new_value);
END //

DELIMITER ;

-- Trigger Example:  Log changes to the 'users' table when a user's email is updated
DELIMITER //
CREATE TRIGGER users_email_update_audit
AFTER UPDATE
ON users
FOR EACH ROW
BEGIN
    IF OLD.email <> NEW.email THEN
        CALL LogUserChanges('users', OLD.id, 'email', OLD.email, NEW.email);
    END IF;
END //
DELIMITER ;

-- Example Usage:
-- UPDATE users SET email = 'new.email@example.com' WHERE id = 1;

-- Query the audit_log table to see the change:
-- SELECT * FROM audit_log;

Explanation:

  • The audit_log table stores the details of each change, including the table name, record ID, column name, old value, and new value.
  • The LogUserChanges stored procedure inserts a new record into the audit_log table with the provided information.
  • The users_email_update_audit trigger is executed *after* each update on the users table.
  • The trigger checks if the email column has changed using OLD.email <> NEW.email. OLD refers to the row *before* the update, and NEW refers to the row *after* the update.
  • If the email has changed, the trigger calls the LogUserChanges procedure to record the change.

3. Performing Complex Calculations

Stored procedures can be used to perform complex calculations based on database data. For example, calculating the total order value for a customer based on their order history. 

 -- Assuming you have tables: 'customers' and 'orders'

DELIMITER //

CREATE PROCEDURE CalculateTotalOrderValue (
    IN p_customer_id INT,
    OUT p_total_value DECIMAL(10, 2)
)
BEGIN
    SELECT SUM(order_total) INTO p_total_value
    FROM orders
    WHERE customer_id = p_customer_id;

    -- Handle the case where the customer has no orders. Set to 0
    IF p_total_value IS NULL THEN
        SET p_total_value = 0;
    END IF;

END //

DELIMITER ;

-- Example Usage:
-- SET @total = 0;  -- Initialize the output variable
-- CALL CalculateTotalOrderValue(1, @total); -- Calculate total order value for customer ID 1
-- SELECT @total; -- Display the result

Explanation:

  • The CalculateTotalOrderValue procedure takes a customer_id as input and calculates the total order value for that customer.
  • The OUT p_total_value DECIMAL(10,2) defines an output parameter. This is how the stored procedure returns a value back to the caller.
  • The SELECT SUM(order_total) INTO p_total_value statement calculates the sum of the order_total column from the orders table for the specified customer and assigns the result to the output parameter p_total_value.
  • The IF p_total_value IS NULL THEN SET p_total_value = 0; END IF; handles the case where a customer has no orders, preventing a NULL value from being returned.
  • In the example usage, @total is a user-defined variable used to receive the output value from the stored procedure.

Conclusion

Stored procedures are a powerful tool in MySQL for improving performance, enhancing security, and enforcing data integrity. These practical examples demonstrate how stored procedures can be used for common database tasks. Understanding and utilizing stored procedures effectively is essential for mastering MySQL database development.

⬅ Previous Next ➡