Encapsulating Business Logic with Stored Procedures and Functions

Stored procedures and stored functions bundle multiple SQL statements into reusable server-side objects, reducing network overhead and improving security. A stored procedure accepts input, output, or both types of parameters, while a stored function always returns a single value.

Building a Procedure

Define the parameter direction (IN, OUT, INOUT) and use DELIMITER to avoid semicolon conflicts.

DELIMITER $$
CREATE PROCEDURE get_employee_email(
    IN  employee_name VARCHAR(50),
    OUT employee_email VARCHAR(100)
)
BEGIN
    SELECT email INTO employee_email
    FROM staff_members
    WHERE name = employee_name;
END $$
DELIMITER ;

Call the procedure and inspect the output parameter.

CALL get_employee_email('Alice Chen', @email_addr);
SELECT @email_addr;

Creating a Function

A function must declare the return data type and include a RETURN clause.

DELIMITER $$
CREATE FUNCTION calculate_bonus(base_salary DECIMAL(10,2))
RETURNS DECIMAL(10,2)
DETERMINISTIC
READS SQL DATA
BEGIN
    DECLARE bonus_amount DECIMAL(10,2);
    SET bonus_amount = base_salary * 0.1;
    RETURN bonus_amount;
END $$
DELIMITER ;

Invoke the function inside a SELECT statement.

SELECT name, salary, calculate_bonus(salary) AS bonus
FROM staff_members;

Routine Management Shortcuts

View metadata through SHOW CREATE PROCEDURE, SHOW FUNCTION STATUS, or the information_schema.ROUTINES table. Modify characteristics with ALTER and drop unused routines with DROP.

Working with Variables, Error Handlers, and Cursors

MySQL supports system-defined global and session variables, as well as user-defined local and session variables.

Session and Local Variables

Sesion variables are prefixed with @ and persist for the current connection. Local variables exist only inside BEGIN...END blocks and must be declared with DECLARE.

SET @min_limit = 5000;

DELIMITER $$
CREATE PROCEDURE show_high_earners()
BEGIN
    DECLARE threshold INT DEFAULT 7000;
    SELECT name, salary
    FROM staff_members
    WHERE salary > threshold;
END $$
DELIMITER ;

Defining Conditions and Handlers

Map error codes to meaningful names and decide how to react when they occur.

DELIMITER $$
CREATE PROCEDURE safe_insert_department(
    IN dept_name VARCHAR(30)
)
BEGIN
    DECLARE duplicate_key CONDITION FOR 1062;
    DECLARE CONTINUE HANDLER FOR duplicate_key
        SET @insert_status = 'DUPLICATE_IGNORED';
    
    INSERT INTO departments(department_name) VALUES (dept_name);
END $$
DELIMITER ;

Iterating with Cursors

Cursors allow row-by-row processing of a result set. The typical workflow requires DECLARE, OPEN, FETCH, and CLOSE.

DELIMITER $$
CREATE PROCEDURE accumulate_salaries(OUT total_count INT)
BEGIN
    DECLARE current_salary DECIMAL(10,2);
    DECLARE running_total DECIMAL(10,2) DEFAULT 0;
    DECLARE emp_cursor CURSOR FOR
        SELECT salary FROM staff_members ORDER BY salary DESC;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET total_count = 0;
    
    OPEN emp_cursor;
    
    read_loop: LOOP
        FETCH emp_cursor INTO current_salary;
        IF running_total >= 50000 THEN
            LEAVE read_loop;
        END IF;
        SET running_total = running_total + current_salary;
    END LOOP;
    
    CLOSE emp_cursor;
    SET total_count = running_total;
END $$
DELIMITER ;

Automating Actions with Triggers

Triggers execute automaitcally before or after INSERT, UPDATE, or DELETE events, enforcing business rules or maintaining audit logs.

DELIMITER $$
CREATE TRIGGER salary_guard
BEFORE INSERT ON staff_members
FOR EACH ROW
BEGIN
    IF NEW.salary < 2000 THEN
        SIGNAL SQLSTATE '45000'
        SET MESSAGE_TEXT = 'Salary must be at least 2000';
    END IF;
END $$
DELIMITER ;

Track changes with an audit table:

CREATE TABLE salary_changes (
    emp_id INT,
    old_value DECIMAL(10,2),
    new_value DECIMAL(10,2),
    changed_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

DELIMITER $$
CREATE TRIGGER log_salary_update
AFTER UPDATE ON staff_members
FOR EACH ROW
BEGIN
    IF OLD.salary <> NEW.salary THEN
        INSERT INTO salary_changes(emp_id, old_value, new_value)
        VALUES (NEW.employee_id, OLD.salary, NEW.salary);
    END IF;
END $$
DELIMITER ;

Triggers should be used carefully because they can obscure business logic and complicate debugging. Always document their purpose.

Leveraging MySQL 8 Enhancements

Persisting Global Variables

The SET PERSIST command writes global variable changes to mysqld-auto.cnf, surviving server restarts.

SET PERSIST max_connections = 800;
SELECT @@global.max_connections;

Analyzing Data with Window Functions

Window functions compute values across row groups without collapsing the result set.

SELECT
    department_id,
    name,
    salary,
    RANK() OVER (PARTITION BY department_id ORDER BY salary DESC) AS salary_rank,
    SUM(salary) OVER (PARTITION BY department_id) AS dept_total
FROM staff_members;

Key ranking functions include ROW_NUMBER(), RANK(), and DENSE_RANK(). Offset functions like LAG() and LEAD() compare values between rows.

SELECT
    name,
    salary,
    LAG(salary, 1) OVER (ORDER BY hire_date) AS previous_salary
FROM staff_members;

Simplifying Queries with Common Table Exprestions (CTEs)

CTEs create reusable temporary result sets within a single statement.

WITH department_stats AS (
    SELECT department_id, AVG(salary) AS avg_sal
    FROM staff_members
    GROUP BY department_id
)
SELECT s.name, s.salary, d.avg_sal
FROM staff_members s
JOIN department_stats d ON s.department_id = d.department_id
WHERE s.salary > d.avg_sal;

Recursive CTEs traverse hierarchical data, such as organizational charts.

WITH RECURSIVE hierarchy AS (
    SELECT employee_id, name, manager_id, 1 AS level
    FROM staff_members
    WHERE manager_id IS NULL
    UNION ALL
    SELECT s.employee_id, s.name, s.manager_id, h.level + 1
    FROM staff_members s
    JOIN hierarchy h ON s.manager_id = h.employee_id
)
SELECT * FROM hierarchy ORDER BY level, name;

Recursive CTEs must follow a seed-plus-recursive-member structure connected with UNION ALL.