MySQL Stored Procedures, Functions, Variables, Control Flow, Cursors & Handlers – Example Usage

Scenario

Stored procedures are a set of SQL statements that implement a specific function. They are compiled and optimized, stored on the database server, and invoked with CALL procedure_name to reuse complex logic.

Stored Procedure

Create Stored Procedure

The basic syntax is:

CREATE PROCEDURE sp_name ( [proc_parameter] ) [characteristic ...] routine_body

Key elements: sp_name: name of the procedure. proc_parameter: list of parameters, each defined as [IN|OUT|INOUT] param_name type. The type can be any MySQL data type. characteristic: optional attributes such as LANGUAGE SQL, DETERMINISTIC / NOT DETERMINISTIC, data‑access modifiers ( CONTAINS SQL, NO SQL, READS SQL DATA, MODIFIES SQL DATA), and security context ( SQL SECURITY DEFINER|INVOKER). routine_body is the SQL code delimited by BEGIN … END.

Example table and data:

CREATE TABLE test_student (
  sid INT PRIMARY KEY,
  sname VARCHAR(20),
  ssex CHAR(2),
  sage INT,
  did INT
);

INSERT INTO test_student(sid,sname,sage,did) VALUES
  (1,'Zhang San',13,101),
  (2,'Li Si',14,101),
  (3,'Wang Wu',15,102),
  (4,'Zhao Liu',16,101);

Creating a simple procedure to list all students:

CREATE PROCEDURE Proc_student() BEGIN
  SELECT * FROM test_student;
END;

Calling the procedure: CALL Proc_student(); Creating a procedure that returns the average age of a class:

CREATE PROCEDURE avg_student(
  IN dep INT,
  OUT avg FLOAT
) BEGIN
  SELECT AVG(sage) INTO avg FROM test_student WHERE did = dep;
END;

Invocation and result retrieval:

CALL avg_student(101,@aa);
SELECT @aa;

View Stored Procedure

Three ways to inspect a procedure after creation:

Show status: SHOW PROCEDURE STATUS LIKE 'avg_%'; – lists metadata but not the definition.

Show definition: SHOW CREATE PROCEDURE avg_student; – returns the exact CREATE statement.

Query information_schema.ROUTINES:

SELECT * FROM information_schema.ROUTINES WHERE ROUTINE_NAME = 'avg_student';

Modify Stored Procedure

Use ALTER PROCEDURE to change attributes, e.g.:

ALTER PROCEDURE avg_student
  MODIFIES SQL DATA
  SQL SECURITY INVOKER;

Delete Stored Procedure

Remove a procedure with:

DROP PROCEDURE avg_student;

Stored Function

Create Stored Function

Syntax:

CREATE FUNCTION func_name ( [func_parameter] ) RETURNS type [characteristic ...] routine_body

Parameter definition follows the same IN|OUT|INOUT pattern.

Example function that returns a student's name given a class id:

CREATE FUNCTION name_student(aa INT) RETURNS CHAR(50) BEGIN
  RETURN (SELECT sname FROM test_student WHERE did = aa);
END;

Calling the function: SELECT name_student(102); If the server reports missing deterministic or data‑access attributes, set the global variable temporarily:

SET GLOBAL log_bin_trust_function_creators = TRUE;

View, Modify, Delete Functions

Use SHOW FUNCTION STATUS, SHOW CREATE FUNCTION, or query information_schema.ROUTINES similarly to procedures. Delete with

DROP FUNCTION name_student;

Custom Functions (Variables)

Declare Variables

Use DECLARE inside a routine: DECLARE var_name type [DEFAULT value]; Example:

DECLARE studentid CHAR(10) DEFAULT '一年级';

Assign Values

Assign with SET or SELECT … INTO:

DECLARE v1 INT;
SET v1 = 66;

DECLARE student_name CHAR(50);
SELECT sname INTO student_name FROM test_student WHERE sid = 2;

Control‑Flow Statements

IF

IF price >= 30 THEN
  SELECT 'Price too high';
ELSE
  SELECT 'Price moderate';
END IF;

CASE

CASE did
  WHEN 101 THEN SELECT 'First grade';
  WHEN 102 THEN SELECT 'Second grade';
END CASE;

LOOP

Basic loop without condition (requires LEAVE to exit):

DECLARE aa INT DEFAULT 0;
Add_sum: LOOP
  SET aa = aa + 1;
END LOOP Add_sum; -- dead loop

LEAVE

DECLARE aa INT DEFAULT 0;
Add_sum: LOOP
  SET aa = aa + 1;
  IF aa > 50 THEN LEAVE Add_sum;
  END IF;
END LOOP Add_sum;

ITERATE

Skip to the next iteration inside a loop:

CREATE PROCEDURE pp(a INT) BEGIN
  La: LOOP
    SET a = a + 1;
    IF a < 10 THEN ITERATE La;
    END IF;
    LEAVE La;
  END LOOP La;
  SET @x = a;
END;

REPEAT

DECLARE ss INT DEFAULT 0;
REPEAT
  SET ss = ss + 1;
UNTIL ss >= 10;
END REPEAT;

WHILE

DECLARE ss INT DEFAULT 0;
WHILE ss <= 10 DO
  SET ss = ss + 1;
END WHILE;

Cursor Usage

Declare, open, fetch, and close a cursor to process result sets row by row:

DECLARE cursor_student CURSOR FOR SELECT sid, sname FROM test_student;
OPEN cursor_student;
FETCH cursor_student INTO e_no, e_name;
CLOSE cursor_student;

Condition and Handler Definitions

Define reusable error conditions:

DECLARE command_not_find CONDITION FOR SQLSTATE '44000';
DECLARE NO_TABLE CONDITION FOR 1150;

Define handlers to specify what to do when a condition occurs:

DECLARE CONTINUE HANDLER FOR SQLSTATE '23S00' SET @x = 20;
DECLARE CONTINUE HANDLER FOR 1146 SET @x = 20;
DECLARE CONTINUE HANDLER FOR NO_TABLE SET @info = 'NO_TABLE';
DECLARE EXIT HANDLER FOR SQLWARNING SET @info = 'ERROR';

These mechanisms allow a routine to continue, exit, or undo actions when warnings or errors are encountered, improving robustness.