Master MySQL Stored Procedures: Concepts, Syntax, and Real-World Examples

1. Overview of Stored Procedures

1.1 Understanding

Meaning: A Stored Procedure (Stored Procedure) is a group of pre‑compiled SQL statements.
Execution: The procedure is stored on the MySQL server; the client calls it with a single command and the server runs all encapsulated SQL statements.

Benefits:

Simplifies operations and improves SQL reuse, reducing developer workload.

Decreases mistakes and boosts efficiency.

Reduces network traffic because the client does not send all SQL statements.

Limits exposure of SQL on the network, enhancing query security.

Comparison with views and functions:

Like views, stored procedures are clear, safe, and reduce network traffic.

Unlike views (virtual tables that do not directly modify underlying tables), stored procedures are programmable SQL that can directly manipulate base tables and handle more complex data processing.

Procedures have no return value, unlike functions.

1.2 Classification

Parameter modes: IN, OUT, INOUT.
Categories:
1. No parameters (no input, no output)
2. Only IN parameters (input only)
3. Only OUT parameters (output only)
4. Both IN and OUT (input and output)
5. INOUT (both in a single parameter)
Note: Multiple IN, OUT, or INOUT parameters can appear in one procedure.

2. Creating Stored Procedures

2.1 Syntax Analysis

CREATE PROCEDURE procedure_name(IN|OUT|INOUT param_name param_type, ...)
[characteristics ...]
BEGIN
    -- procedure body
END

Parameter symbols: IN: Input parameter; the procedure only reads its value. Default if omitted. OUT: Output parameter; after execution the caller can read the returned value. INOUT: Can be both input and output.

Characteristics (optional constraints):

LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'

Explanation of each characteristic: LANGUAGE SQL: Procedure body consists of SQL statements. DETERMINISTIC / NOT DETERMINISTIC: Whether the same inputs always produce the same outputs. CONTAINS SQL: Contains SQL but not data‑modifying statements. NO SQL: Contains no SQL statements. READS SQL DATA: Reads data but does not modify it. MODIFIES SQL DATA: Modifies data. SQL SECURITY DEFINER: Only the creator can execute. SQL SECURITY INVOKER: Any user with execute privilege can run.

Procedure body can contain multiple SQL statements; if only one statement, BEGIN and END may be omitted.

Common statements inside the body: DECLARE: Declare variables (must appear before other statements). SET: Assign values to variables. SELECT ... INTO: Store query results into variables.

Delimiter handling:

DELIMITER new_delimiter   -- change from default ';' to avoid conflict
... CREATE PROCEDURE ... END new_delimiter
DELIMITER ;               -- restore default

Example:

DELIMITER $
CREATE PROCEDURE proc_name(IN param1 INT)
BEGIN
    SELECT * FROM table1;
END $
DELIMITER ;

2.2 Code Examples

-- Example 1: Show all rows in emps
DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
    SELECT * FROM emps;
END $
DELIMITER ;

-- Example 2: Return average salary
DELIMITER //
CREATE PROCEDURE avg_employee_salary()
BEGIN
    SELECT AVG(salary) AS avg_salary FROM emps;
END //
DELIMITER ;

-- Example 3: Show maximum salary with attributes
DELIMITER //
CREATE PROCEDURE show_max_salary()
LANGUAGE SQL
NOT DETERMINISTIC
CONTAINS SQL
SQL SECURITY DEFINER
COMMENT 'Show highest salary'
BEGIN
    SELECT MAX(salary) FROM emps;
END //
DELIMITER ;

-- Example 4: Show minimum salary via OUT parameter
DELIMITER //
CREATE PROCEDURE show_min_salary(OUT ms DOUBLE)
BEGIN
    SELECT MIN(salary) INTO ms FROM emps;
END //
DELIMITER ;

-- Example 5: Query a specific employee's salary using IN parameter
DELIMITER //
CREATE PROCEDURE show_someone_salary(IN empname VARCHAR(20))
BEGIN
    SELECT salary FROM emps WHERE ename = empname;
END //
DELIMITER ;

-- Example 6: Same as above but also return via OUT parameter
DELIMITER //
CREATE PROCEDURE show_someone_salary2(IN empname VARCHAR(20), OUT empsalary DOUBLE)
BEGIN
    SELECT salary INTO empsalary FROM emps WHERE ename = empname;
END //
DELIMITER ;

-- Example 7: Use INOUT to get and set manager name
DELIMITER //
CREATE PROCEDURE show_mgr_name(INOUT empname VARCHAR(20))
BEGIN
    SELECT ename INTO empname FROM emps WHERE eid = (SELECT MID FROM emps WHERE ename = empname);
END //
DELIMITER ;

3. Calling Stored Procedures

3.1 Call Syntax

CALL procedure_name(arg_list);
-- Example with database qualifier: CALL dbname.procname;

-- IN parameter call
CALL sp1('value');

-- OUT parameter call
SET @name = NULL;
CALL sp1(@name);
SELECT @name;

-- INOUT parameter call
SET @name = 'value';
CALL sp1(@name);
SELECT @name;

3.2 Call Examples

-- Example: Count fruits for a given sid
DELIMITER //
CREATE PROCEDURE CountProc(IN sid INT, OUT num INT)
BEGIN
    SELECT COUNT(*) INTO num FROM fruits WHERE s_id = sid;
END //
DELIMITER ;
CALL CountProc(101, @num);
SELECT @num;  -- returns 3

-- Example: Sum 1+2+...+n
DELIMITER //
CREATE PROCEDURE add_num(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE sum INT DEFAULT 0;
    WHILE i <= n DO
        SET sum = sum + i;
        SET i = i + 1;
    END WHILE;
    SELECT sum;
END //
DELIMITER ;
CALL add_num(50);  -- returns the sum of 1..50

3.3 Debugging

Because MySQL lacks a dedicated IDE for procedures, you can insert SELECT statements inside the body to output intermediate results.
After confirming a step works, move the SELECT to the next statement and continue.
Alternatively, copy individual SQL statements out of the procedure and test them separately.

4. Practice Exercises

#0. Prepare environment
CREATE DATABASE test15_pro_func;
USE test15_pro_func;

#1. Procedure insert_user(username, pwd) inserts into admin table
CREATE TABLE admin(
    id INT PRIMARY KEY AUTO_INCREMENT,
    user_name VARCHAR(15) NOT NULL,
    pwd VARCHAR(25) NOT NULL
);
DELIMITER //
CREATE PROCEDURE insert_user(IN username VARCHAR(20), IN loginPwd VARCHAR(20))
BEGIN
    INSERT INTO admin(user_name, pwd) VALUES(username, loginPwd);
END //
DELIMITER ;

#2. Procedure get_phone(id, OUT name, OUT phone) returns a beauty's name and phone
CREATE TABLE beauty(
    id INT PRIMARY KEY AUTO_INCREMENT,
    NAME VARCHAR(15) NOT NULL,
    phone VARCHAR(15) UNIQUE,
    birth DATE
);
INSERT INTO beauty(NAME, phone, birth) VALUES
('朱茵','13201233453','1982-02-12'),
('孙燕姿','13501233653','1980-12-09'),
('田馥甄','13651238755','1983-08-21'),
('邓紫棋','17843283452','1991-11-12'),
('刘若英','18635575464','1989-05-18'),
('杨超越','13761238755','1994-05-11');
DELIMITER //
CREATE PROCEDURE get_phone(IN id INT, OUT NAME VARCHAR(20), OUT phone VARCHAR(20))
BEGIN
    SELECT b.NAME, b.phone INTO NAME, phone FROM beauty b WHERE b.id = id;
END //
DELIMITER ;
CALL get_phone(1, @name, @phone);
SELECT @name, @phone;

#3. Procedure date_diff(birth1, birth2, OUT result) returns date interval
DELIMITER //
CREATE PROCEDURE date_diff(IN birth1 DATETIME, IN birth2 DATETIME, OUT result INT)
BEGIN
    SELECT DATEDIFF(birth1, birth2) INTO result;
END //
DELIMITER ;
SET @b1='1992-09-08';
SET @b2='1989-01-03';
CALL date_diff(@b1, @b2, @result);
SELECT @result;

#4. Procedure format_date(mydate, OUT strdate) formats date as "yy年mm月dd日"
DELIMITER //
CREATE PROCEDURE format_date(IN mydate DATETIME, OUT strdate VARCHAR(50))
BEGIN
    SELECT DATE_FORMAT(mydate, '%y年%m月%d日') INTO strdate;
END //
DELIMITER ;
SET @mydate='1992-09-08';
CALL format_date(@mydate, @strdate);
SELECT @strdate;

#5. Procedure beauty_limit(startIndex, size) paginates beauty table
DELIMITER //
CREATE PROCEDURE beauty_limit(IN startIndex INT, IN size INT)
BEGIN
    SELECT * FROM beauty LIMIT startIndex, size;
END //
DELIMITER ;
CALL beauty_limit(1,3);

#6. Procedure add_double(INOUT a INT, INOUT b INT) doubles both values
DELIMITER //
CREATE PROCEDURE add_double(INOUT a INT, INOUT b INT)
BEGIN
    SET a = a * 2;
    SET b = b * 2;
END //
DELIMITER ;
SET @a=3, @b=5;
CALL add_double(@a, @b);
SELECT @a, @b;

#7. Drop procedure beauty_limit
DROP PROCEDURE beauty_limit;

#8. Show procedure information for add_double
SHOW CREATE PROCEDURE add_double;
SHOW PROCEDURE STATUS LIKE 'add_double';