MySQL Stored Procedures and Triggers: A Practical Guide to Variables, Flow Control, Cursors, and Functions

This article focuses on the core capabilities of MySQL stored procedures and triggers. It addresses common problems such as scattered business logic, poor SQL reusability, and difficult data validation, while systematically covering variables, flow control, cursors, and trigger mechanics. Keywords: MySQL, stored procedures, triggers.

Technical specification snapshot

Parameter	Description
Language	SQL / MySQL Procedural SQL
License	Marked in the source as CC 4.0 BY-SA
Stars	Not provided
Core dependencies	MySQL 8.x, information_schema, binlog (relevant for function scenarios)

Stored procedures are executable SQL units in MySQL for encapsulating business logic.

A stored procedure is essentially a group of SQL statements organized for a specific task. MySQL compiles and stores it on the database side, and when you call it, you only need to provide the procedure name and parameters.

Stored procedures work well for logic that runs repeatedly, follows clear rules, and belongs close to the data. Typical use cases include batch updates, statistical aggregation, permission isolation, and transaction orchestration.

DELIMITER //

CREATE PROCEDURE p_demo()
BEGIN
    SELECT NOW(); -- Return the current database time
END //

DELIMITER ;

CALL p_demo(); -- Call the stored procedure

This example shows the smallest complete loop of creating and calling a stored procedure.

The value of stored procedures appears in performance, reuse, and security boundaries.

They offer five main benefits: precompilation reduces repeated parsing cost, SQL templates become reusable, direct table access can be restricted, transaction control becomes easier, and applications become less sensitive to schema changes.

The trade-offs are equally clear: weak cross-database portability, limited debugging capabilities, declining maintainability as complex logic accumulates, and in high-concurrency scenarios, more pressure may concentrate on the database layer.

Mastering the syntax for creating, viewing, calling, and dropping procedures is the first step to using them.

In MySQL, multi-statement procedures usually require a temporary change to the statement delimiter. Otherwise, the semicolons inside BEGIN...END will terminate parsing too early.

DELIMITER //

CREATE PROCEDURE p_user_count(IN dept_id INT)
BEGIN
    SELECT COUNT(*) AS total
    FROM user_info
    WHERE department_id = dept_id; -- Count users by department
END //

DELIMITER ;

SHOW CREATE PROCEDURE p_user_count; -- Show the procedure definition
CALL p_user_count(10); -- Execute the procedure
DROP PROCEDURE IF EXISTS p_user_count; -- Drop the procedure

This code covers the full lifecycle management of a procedure object.

The variable system determines how data flows inside a procedure.

System variables use @@ and are divided into GLOBAL and SESSION scopes. The former affects instance-wide behavior, while the latter applies only to the current connection.

User variables start with @, require no declaration, and work well for temporary session-level value passing. Local variables must be declared with DECLARE and are valid only inside a BEGIN...END block.

-- View a system variable
SELECT @@GLOBAL.autocommit; -- Check the global autocommit setting

-- User variable
SET @dept_id := 10; -- Define a session variable
SELECT @dept_id;

-- Local variable example
DELIMITER //
CREATE PROCEDURE p_var()
BEGIN
    DECLARE total INT DEFAULT 0; -- Declare a local variable
    SET total := 100; -- Assign a local value
    SELECT total;
END //
DELIMITER ;

This example illustrates the scope and typical syntax of the three variable types.

Flow control statements give MySQL programming capabilities similar to traditional languages.

When writing business logic on the database side, the most commonly used constructs are IF, CASE, and loop structures. They work well for rule branching, state classification, and batch control.

Parameters are divided into IN, OUT, and INOUT. IN handles input, OUT returns results, and INOUT can serve both purposes.

DELIMITER //

CREATE PROCEDURE p_level(IN score INT, OUT level_name VARCHAR(20))
BEGIN
    IF score >= 90 THEN
        SET level_name := 'A'; -- High-score level
    ELSEIF score >= 60 THEN
        SET level_name := 'B'; -- Passing level
    ELSE
        SET level_name := 'C'; -- Failing level
    END IF;
END //

DELIMITER ;

This example uses IF and an OUT parameter to implement rule-based grading inside a procedure.

Loops work well for accumulation, scanning, and conditional exits.

WHILE evaluates the condition before execution, REPEAT executes first and evaluates later, and LOOP is more flexible. In practice, LOOP often works with LEAVE and ITERATE to implement exit and skip behavior.

DELIMITER //

CREATE PROCEDURE p_even_sum(IN n INT)
BEGIN
    DECLARE total INT DEFAULT 0; -- Store the accumulated result

    sum_label: LOOP
        IF n <= 0 THEN
            LEAVE sum_label; -- End the loop
        END IF;

        IF n % 2 = 1 THEN
            SET n := n - 1; -- Decrement odd numbers first
            ITERATE sum_label; -- Skip this iteration
        END IF;

        SET total := total + n; -- Accumulate even numbers
        SET n := n - 1;
    END LOOP;

    SELECT total;
END //

DELIMITER ;

This procedure implements the logic for summing even numbers from 1 to n.

Cursors and condition handlers are used for row-by-row reads and exception branches.

When a single SQL statement cannot clearly express row-by-row processing logic, a cursor can iterate through a result set. MySQL cursors are read-only and are commonly used inside procedures and functions.

Condition handlers capture NOT FOUND, SQLWARNING, or SQLEXCEPTION. They prevent a procedure from terminating immediately when a cursor is exhausted or an exception occurs.

DELIMITER //

CREATE PROCEDURE p_cursor_demo()
BEGIN
    DECLARE done INT DEFAULT 0; -- Mark whether reading is complete
    DECLARE uid INT;
    DECLARE cur CURSOR FOR SELECT id FROM user_info;
    DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -- Handle cursor exhaustion

    OPEN cur;

    read_loop: LOOP
        FETCH cur INTO uid; -- Read id row by row
        IF done = 1 THEN
            LEAVE read_loop; -- Result set has been fully read
        END IF;
        SELECT uid;
    END LOOP;

    CLOSE cur;
END //

DELIMITER ;

This example demonstrates the coordination pattern among a cursor, an end-of-data flag, and a condition handler.

Stored functions and triggers handle value computation and automatic event responses respectively.

A stored function must return a value, and its parameters can only be IN. In MySQL 8.0 with binary logging enabled, defining a function usually requires explicit characteristics such as DETERMINISTIC or READS SQL DATA.

DELIMITER //

CREATE FUNCTION f_user_count(dept_id INT)
RETURNS INT
READS SQL DATA
BEGIN
    DECLARE total INT DEFAULT 0;
    SELECT COUNT(*) INTO total
    FROM user_info
    WHERE department_id = dept_id; -- Query the department user count
    RETURN total; -- Return the aggregated result
END //

DELIMITER ;

This example packages counting logic into a reusable scalar function.

Triggers execute automatically before or after data changes and work well for auditing and validation.

Triggers are bound to a table and fire on INSERT, UPDATE, or DELETE events. BEFORE is useful for interception and validation, while AFTER is better suited to auditing and linked record writing.

MySQL supports only row-level triggers and does not support statement-level triggers. NEW represents the new value, and OLD represents the old value. The data you can access depends on the event type.

DELIMITER //

CREATE TRIGGER trg_user_log
AFTER UPDATE ON user_info
FOR EACH ROW
BEGIN
    INSERT INTO user_log(user_id, old_name, new_name)
    VALUES (OLD.id, OLD.name, NEW.name); -- Record values before and after the update
END //

DELIMITER ;

This trigger automatically writes an audit log after user information is updated.

The image assets mainly come from platform decoration and do not function as technical diagrams.

This image is a page button icon. It does not represent database structure, execution flow, or code semantics, so it is not interpreted as technical content.

AI Visual Insight: This image is a screenshot of an advertisement slot on the page. It does not show a SQL execution plan, ER relationships, trigger chains, or a procedure call stack, so it does not add direct technical insight into MySQL stored procedures or triggers.

FAQ

Q1: When should you prioritize stored procedures?

Prioritize stored procedures when logic depends heavily on the database, when you need to reduce network round trips, when transaction boundaries must be unified, or when access to sensitive tables should be encapsulated.

Q2: What is the biggest difference between triggers and stored procedures?

Stored procedures require an explicit CALL to run. Triggers are bound to table events and fire automatically when data changes, which makes them suitable for auditing, validation, and linked writes.

Q3: Why are cursors often considered something to use with caution?

Cursors process rows one at a time. They are expressive, but their performance is usually worse than set-based operations. If a task can be completed with a single SQL statement, avoid using a cursor whenever possible.

Core summary

This article systematically reconstructs the core knowledge of MySQL stored procedures and triggers. It covers creation and invocation, variable scope, IF/CASE/loops, cursors, condition handlers, stored functions, and trigger mechanisms, helping developers quickly build practical database-side programming skills.