数据库存储过程

1. 存储过程概述

1.1 什么是存储过程

存储过程（Stored Procedure）是一组为了完成特定功能的SQL语句集合，经过编译后存储在数据库中。存储过程把经常使用的SQL语句或业务逻辑封装起来，预编译保存在数据库中，当需要时从数据库中直接调用。

核心特性：

预编译：存储过程在创建时编译，执行时无需再编译
封装性：将复杂的业务逻辑封装在数据库中
可重用性：一次创建，多次调用
安全性：可以控制对数据的访问权限

1.2 存储过程的存储位置

存储过程保存在 mysql.proc 表中：

1 2	-- 查看存储过程信息 SELECT * FROM mysql.proc WHERE db = 'database_name';

注意：直接操作 mysql.proc 表不推荐，应使用标准的SQL语句管理存储过程。

1.3 存储过程的优势

1. 提高运行速度

预编译：存储过程在创建时编译，执行时直接运行
减少编译时间：避免每次执行SQL时重新编译
执行计划缓存：MySQL可以缓存存储过程的执行计划

性能对比：

1 2	普通SQL：解析 → 编译 → 优化 → 执行存储过程：执行（已编译）

2. 降低网络数据传输量

减少网络往返：一次调用执行多条SQL语句
批量处理：可以在存储过程中处理大量数据
减少带宽占用：只传输调用参数和结果

示例：

-- 普通方式：需要多次网络往返
INSERT INTO table1 VALUES(...);
INSERT INTO table2 VALUES(...);
INSERT INTO table3 VALUES(...);

-- 存储过程：一次网络调用
CALL batch_insert(...);

3. 提高安全性

权限控制：可以精确控制存储过程的执行权限
SQL注入防护：参数化查询减少SQL注入风险
数据访问控制：隐藏底层表结构

4. 业务逻辑封装

代码复用：一次编写，多处使用
维护集中：业务逻辑集中在数据库，便于维护
版本控制：可以统一管理业务逻辑版本

1.4 存储过程的适用场景

适合使用存储过程的场景

复杂业务逻辑：需要多条SQL语句完成的复杂操作
批量数据处理：需要处理大量数据的操作
数据迁移：定期执行的数据迁移任务
报表生成：复杂的报表计算逻辑
数据验证：复杂的数据验证规则

不适合使用存储过程的场景

简单查询：简单的SELECT查询不需要存储过程
频繁变更的逻辑：业务逻辑经常变化的场景
跨数据库应用：需要跨数据库移植的应用
复杂计算：更适合在应用层实现的复杂计算

1.5 存储过程的缺点

1. 可移植性差

不同数据库的存储过程语法不同
难以在不同数据库间迁移

2. 调试困难

存储过程调试工具有限
错误定位相对困难

3. 维护成本高

业务逻辑分散在数据库和应用层
需要数据库开发人员维护

4. 性能考虑

存储过程可能占用数据库资源
大量存储过程可能影响数据库性能

2. 创建存储过程

2.1 创建存储过程的基本语法

1 2	CREATE PROCEDURE sp_name ([proc_parameter [,proc_parameter ...]]) routine_body

2.2 参数说明

proc_parameter 语法

1	[IN \| OUT \| INOUT] parameter_name type

参数类型：

类型	说明	用途
IN	输入参数	调用存储过程时传入的值，存储过程内部不能修改
OUT	输出参数	存储过程执行后返回的值，调用前不需要赋值
INOUT	输入输出参数	既可以传入值，也可以返回值

参数名称：

遵循标识符命名规则
建议使用有意义的名称

参数类型：

可以是MySQL支持的任何数据类型
如：INT、VARCHAR、DATETIME等

2.3 创建无参存储过程

基本示例

-- 修改语句结束符（避免存储过程中的分号被误解析）
MariaDB [hellodb]> DELIMITER //

-- 创建存储过程
MariaDB [hellodb]> CREATE PROCEDURE showTime()
    -> BEGIN
    ->     SELECT NOW();
    -> END//

-- 恢复语句结束符
MariaDB [hellodb]> DELIMITER ;
Query OK, 0 rows affected (0.02 sec)

说明：

DELIMITER //：将语句结束符改为//，避免存储过程中的;被误解析
BEGIN...END：存储过程体，包含要执行的SQL语句
DELIMITER ;：恢复默认的语句结束符

调用存储过程

-- 调用存储过程（无参时可以省略括号）
MariaDB [hellodb]> CALL showTime;
+---------------------+
| now()               |
+---------------------+
| 2018-10-09 19:38:46 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

注意：

无参存储过程可以省略括号：CALL showTime; 或 CALL showTime();
有参存储过程必须使用括号：CALL procedure_name(param);

2.4 创建含参存储过程

IN参数示例

-- 创建带IN参数的存储过程
MariaDB [hellodb]> DELIMITER //
MariaDB [hellodb]> CREATE PROCEDURE selectById(IN uid SMALLINT UNSIGNED)
    -> BEGIN
    ->     SELECT * FROM students WHERE stuid = uid;
    -> END//
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> DELIMITER ;

-- 调用存储过程
MariaDB [hellodb]> CALL selectById(2);
+-------+------------+-----+--------+---------+-----------+
| StuID | Name       | Age | Gender | ClassID | TeacherID |
+-------+------------+-----+--------+---------+-----------+
|     2 | Shi Potian |  22 | M      |       1 |         7 |
+-------+------------+-----+--------+---------+-----------+
1 row in set (0.02 sec)

Query OK, 0 rows affected (0.02 sec)

IN参数特点：

只能传入值，不能返回值
存储过程内部可以读取，但不能修改
调用时必须提供值

OUT参数示例

-- 创建带OUT参数的存储过程
DELIMITER //
CREATE PROCEDURE getStudentCount(OUT total_count INT)
BEGIN
    SELECT COUNT(*) INTO total_count FROM students;
END//
DELIMITER ;

-- 调用存储过程
CALL getStudentCount(@count);

-- 查看输出参数的值
SELECT @count;
+--------+
| @count |
+--------+
|     25 |
+--------+

OUT参数特点：

只能返回值，不能传入值
调用前不需要赋值
使用用户变量接收返回值

INOUT参数示例

-- 创建带INOUT参数的存储过程
DELIMITER //
CREATE PROCEDURE incrementValue(INOUT value INT)
BEGIN
    SET value = value + 1;
END//
DELIMITER ;

-- 调用存储过程
SET @num = 10;
CALL incrementValue(@num);
SELECT @num;
+------+
| @num |
+------+
|   11 |
+------+

INOUT参数特点：

既可以传入值，也可以返回值
调用时需要提供初始值
执行后值会被修改

2.5 存储过程体（routine_body）

简单语句

CREATE PROCEDURE simple_proc()
BEGIN
    SELECT 'Hello World';
END;

多条语句

CREATE PROCEDURE multi_statement()
BEGIN
    SELECT 'First Statement';
    SELECT 'Second Statement';
    SELECT 'Third Statement';
END;

变量声明

CREATE PROCEDURE variable_example()
BEGIN
    DECLARE var1 INT DEFAULT 0;
    DECLARE var2 VARCHAR(50);
    SET var1 = 100;
    SET var2 = 'Hello';
    SELECT var1, var2;
END;

变量说明：

DECLARE：声明局部变量
SET：给变量赋值
@variable：用户变量（会话级）
DECLARE variable：局部变量（存储过程内）

3. 查看存储过程

3.1 查看存储过程列表

使用SHOW PROCEDURE STATUS

-- 查看所有存储过程
MariaDB [hellodb]> SHOW PROCEDURE STATUS\G
*************************** 1. row ***************************
                  Db: hellodb
                Name: showTime
                Type: PROCEDURE
             Definer: root@localhost
            Modified: 2018-10-09 19:38:45
             Created: 2018-10-09 19:38:45
       Security_type: DEFINER
             Comment: 
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 rows in set (0.00 sec)

输出字段说明：

字段	说明
`Db`	数据库名
`Name`	存储过程名称
`Type`	类型（PROCEDURE）
`Definer`	定义者
`Modified`	修改时间
`Created`	创建时间
`Security_type`	安全类型（DEFINER/INVOKER）

查看指定数据库的存储过程

-- 查看指定数据库的存储过程
SHOW PROCEDURE STATUS WHERE Db = 'hellodb';

-- 查看指定名称的存储过程
SHOW PROCEDURE STATUS WHERE Name LIKE 'show%';

3.2 查看存储过程定义

使用SHOW CREATE PROCEDURE

-- 查看存储过程的创建语句
MariaDB [hellodb]> SHOW CREATE PROCEDURE showTime\G
*************************** 1. row ***************************
           Procedure: showTime
            sql_mode: STRICT_TRANS_TABLES,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
    Create Procedure: CREATE DEFINER=`root`@`localhost` PROCEDURE `showTime`()
BEGIN
SELECT now();
END
character_set_client: utf8
collation_connection: utf8_general_ci
  Database Collation: utf8_general_ci
1 row in set (0.00 sec)

输出说明：

Procedure：存储过程名称
sql_mode：SQL模式
Create Procedure：完整的创建语句
character_set_client：客户端字符集

3.3 查询系统表

查询information_schema.routines

-- 查询存储过程信息
SELECT 
    ROUTINE_NAME,
    ROUTINE_TYPE,
    DEFINER,
    CREATED,
    LAST_ALTERED
FROM information_schema.ROUTINES
WHERE ROUTINE_SCHEMA = 'hellodb'
  AND ROUTINE_TYPE = 'PROCEDURE';

查询mysql.proc表

-- 查询存储过程详细信息
SELECT 
    db,
    name,
    type,
    definer,
    created,
    modified
FROM mysql.proc
WHERE db = 'hellodb'
  AND type = 'PROCEDURE';

4. 调用存储过程

4.1 调用存储过程的基本语法

1 2	CALL sp_name ([proc_parameter [,proc_parameter ...]]); CALL sp_name;

语法说明：

CALL：调用存储过程的关键字
sp_name：存储过程名称
proc_parameter：参数值
无参时可以省略括号

4.2 调用无参存储过程

-- 方式一：省略括号
CALL showTime;

-- 方式二：使用括号
CALL showTime();

4.3 调用有参存储过程

调用IN参数

-- 直接传入值
CALL selectById(2);

-- 使用变量
SET @student_id = 2;
CALL selectById(@student_id);

调用OUT参数

1
2
3

-- 使用用户变量接收返回值
CALL getStudentCount(@total);
SELECT @total;

调用INOUT参数

-- 需要提供初始值
SET @value = 10;
CALL incrementValue(@value);
SELECT @value;

4.4 跨数据库调用存储过程

在同一数据库内调用

1
2
3

-- 当前数据库
USE hellodb;
CALL showTime;

跨数据库调用

-- 切换到其他数据库
MariaDB [hellodb]> USE db1;
Database changed

-- 直接调用会报错（在当前数据库查找）
MariaDB [db1]> CALL showTime;
ERROR 1305 (42000): PROCEDURE db1.showTime does not exist

-- 使用数据库名.存储过程名调用
MariaDB [db1]> CALL hellodb.showTime;
+---------------------+
| now()               |
+---------------------+
| 2018-10-09 19:43:15 |
+---------------------+
1 row in set (0.00 sec)

Query OK, 0 rows affected (0.00 sec)

跨数据库调用语法：

1	CALL database_name.procedure_name([parameters]);

5. 存储过程示例实战

5.1 简单计算存储过程

创建计算存储过程

-- 创建计算1到n的累加和的存储过程
MariaDB [hellodb]> DELIMITER //
MariaDB [hellodb]> CREATE PROCEDURE dorepeat(n INT)
    -> BEGIN
    ->     SET @i = 0;
    ->     SET @sum = 0;
    ->     REPEAT 
    ->         SET @sum = @sum + @i; 
    ->         SET @i = @i + 1;
    ->     UNTIL @i > n END REPEAT;
    -> END//
Query OK, 0 rows affected (0.02 sec)

MariaDB [hellodb]> DELIMITER ;

代码说明：

@i：用户变量，循环计数器
@sum：用户变量，累加和
REPEAT...UNTIL：循环结构
SET @i = @i + 1：相当于 i++

调用存储过程

-- 调用存储过程计算1到100的累加和
MariaDB [hellodb]> CALL dorepeat(100);
Query OK, 0 rows affected (0.00 sec)

-- 查看计算结果
MariaDB [hellodb]> SELECT @sum;
+------+
| @sum |
+------+
| 5050 |
+------+
1 row in set (0.00 sec)

验证：1+2+3+…+100 = 5050 ✓

5.2 改进版本（使用OUT参数）

-- 使用OUT参数返回结果
DELIMITER //
CREATE PROCEDURE dorepeat_improved(IN n INT, OUT result INT)
BEGIN
    DECLARE i INT DEFAULT 0;
    DECLARE sum_val INT DEFAULT 0;
    
    REPEAT 
        SET sum_val = sum_val + i;
        SET i = i + 1;
    UNTIL i > n END REPEAT;
    
    SET result = sum_val;
END//
DELIMITER ;

-- 调用
CALL dorepeat_improved(100, @result);
SELECT @result;

5.3 复杂业务逻辑示例

学生信息管理存储过程

-- 创建学生信息管理存储过程
DELIMITER //
CREATE PROCEDURE manageStudent(
    IN action VARCHAR(10),
    IN student_id INT,
    IN student_name VARCHAR(50),
    OUT result_message VARCHAR(100)
)
BEGIN
    DECLARE student_count INT;
    
    IF action = 'INSERT' THEN
        INSERT INTO students(name) VALUES(student_name);
        SET result_message = CONCAT('学生 ', student_name, ' 添加成功');
        
    ELSEIF action = 'UPDATE' THEN
        SELECT COUNT(*) INTO student_count FROM students WHERE id = student_id;
        IF student_count > 0 THEN
            UPDATE students SET name = student_name WHERE id = student_id;
            SET result_message = '学生信息更新成功';
        ELSE
            SET result_message = '学生不存在';
        END IF;
        
    ELSEIF action = 'DELETE' THEN
        SELECT COUNT(*) INTO student_count FROM students WHERE id = student_id;
        IF student_count > 0 THEN
            DELETE FROM students WHERE id = student_id;
            SET result_message = '学生删除成功';
        ELSE
            SET result_message = '学生不存在';
        END IF;
        
    ELSE
        SET result_message = '无效的操作';
    END IF;
END//
DELIMITER ;

6. 存储过程修改

6.1 修改存储过程的限制

重要说明：

ALTER语句修改存储过程只能修改存储过程的注释等无关紧要的东西，不能修改存储过程体。所以要修改存储过程，方法就是删除重建。

6.2 使用ALTER修改注释

1
2
3

-- 修改存储过程的注释
ALTER PROCEDURE showTime
COMMENT '显示当前时间';

ALTER PROCEDURE可以修改的内容：

注释（COMMENT）
特性（CHARACTERISTIC）
SQL安全类型（SQL SECURITY）

ALTER PROCEDURE不能修改的内容：

存储过程体
参数列表
存储过程名称

6.3 删除重建方式修改

标准流程

-- 1. 查看当前存储过程定义
SHOW CREATE PROCEDURE procedure_name\G

-- 2. 删除旧存储过程
DROP PROCEDURE IF EXISTS procedure_name;

-- 3. 创建新存储过程
CREATE PROCEDURE procedure_name(...)
BEGIN
    -- 修改后的逻辑
END;

示例：修改存储过程

-- 原始存储过程
CREATE PROCEDURE showTime()
BEGIN
    SELECT NOW();
END;

-- 修改后的存储过程（添加格式化）
DROP PROCEDURE IF EXISTS showTime;

CREATE PROCEDURE showTime()
BEGIN
    SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS current_time;
END;

6.4 版本控制建议

-- 建议：在存储过程名称中包含版本号
CREATE PROCEDURE showTime_v1()
BEGIN
    SELECT NOW();
END;

-- 新版本
CREATE PROCEDURE showTime_v2()
BEGIN
    SELECT DATE_FORMAT(NOW(), '%Y-%m-%d %H:%i:%s') AS current_time;
END;

7. 删除存储过程

7.1 删除存储过程的基本语法

1	DROP PROCEDURE [IF EXISTS] sp_name;

语法说明：

DROP PROCEDURE：删除存储过程的关键字
IF EXISTS：可选，如果存储过程不存在也不报错
sp_name：要删除的存储过程名称

7.2 删除存储过程示例

删除单个存储过程

-- 删除存储过程
DROP PROCEDURE showTime;

-- 使用IF EXISTS避免错误
DROP PROCEDURE IF EXISTS showTime;

删除多个存储过程

-- 需要分别删除
DROP PROCEDURE IF EXISTS showTime;
DROP PROCEDURE IF EXISTS selectById;
DROP PROCEDURE IF EXISTS dorepeat;

7.3 删除存储过程的注意事项

1. 检查依赖关系

-- 检查是否有其他对象依赖该存储过程
SELECT 
    ROUTINE_NAME,
    ROUTINE_DEFINITION
FROM information_schema.ROUTINES
WHERE ROUTINE_DEFINITION LIKE '%procedure_name%';

2. 备份存储过程定义

-- 删除前备份
SHOW CREATE PROCEDURE procedure_name\G

-- 或导出到文件
SELECT ROUTINE_DEFINITION 
FROM information_schema.ROUTINES
WHERE ROUTINE_NAME = 'procedure_name'
INTO OUTFILE '/tmp/procedure_backup.sql';

3. 权限要求

1 2	-- 需要ALTER ROUTINE或DROP权限 GRANT DROP ON PROCEDURE database_name.procedure_name TO 'user'@'host';

8. 存储过程高级特性

8.1 变量使用

用户变量（@variable）

CREATE PROCEDURE user_variable_example()
BEGIN
    SET @global_var = 'Hello';
    SET @global_var = @global_var + ' World';
    SELECT @global_var;
END;

特点：

会话级变量，在整个会话中有效
使用@前缀
不需要声明，直接使用

局部变量（DECLARE）

CREATE PROCEDURE local_variable_example()
BEGIN
    DECLARE local_var INT DEFAULT 0;
    DECLARE var_name VARCHAR(50);
    SET local_var = 100;
    SET var_name = 'Test';
    SELECT local_var, var_name;
END;

特点：

存储过程内有效
使用DECLARE声明
必须指定数据类型
可以有默认值

8.2 控制结构

IF语句

CREATE PROCEDURE if_example(IN score INT)
BEGIN
    DECLARE grade VARCHAR(10);
    
    IF score >= 90 THEN
        SET grade = 'A';
    ELSEIF score >= 80 THEN
        SET grade = 'B';
    ELSEIF score >= 70 THEN
        SET grade = 'C';
    ELSE
        SET grade = 'F';
    END IF;
    
    SELECT grade;
END;

CASE语句

CREATE PROCEDURE case_example(IN day_num INT)
BEGIN
    DECLARE day_name VARCHAR(10);
    
    CASE day_num
        WHEN 1 THEN SET day_name = 'Monday';
        WHEN 2 THEN SET day_name = 'Tuesday';
        WHEN 3 THEN SET day_name = 'Wednesday';
        ELSE SET day_name = 'Other';
    END CASE;
    
    SELECT day_name;
END;

循环结构

WHILE循环：

CREATE PROCEDURE while_example(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;

REPEAT循环：

CREATE PROCEDURE repeat_example(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE sum INT DEFAULT 0;
    
    REPEAT
        SET sum = sum + i;
        SET i = i + 1;
    UNTIL i > n END REPEAT;
    
    SELECT sum;
END;

LOOP循环：

CREATE PROCEDURE loop_example(IN n INT)
BEGIN
    DECLARE i INT DEFAULT 1;
    DECLARE sum INT DEFAULT 0;
    
    my_loop: LOOP
        SET sum = sum + i;
        SET i = i + 1;
        
        IF i > n THEN
            LEAVE my_loop;
        END IF;
    END LOOP;
    
    SELECT sum;
END;

8.3 错误处理

使用SIGNAL抛出错误

CREATE PROCEDURE error_example(IN value INT)
BEGIN
    IF value < 0 THEN
        SIGNAL SQLSTATE '45000' 
        SET MESSAGE_TEXT = '值不能为负数';
    END IF;
    
    SELECT value;
END;

使用HANDLER处理错误

CREATE PROCEDURE handler_example()
BEGIN
    DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
    BEGIN
        SELECT '发生错误' AS error_message;
    END;
    
    -- 可能出错的SQL语句
    INSERT INTO table_name VALUES(...);
END;

8.4 游标使用

CREATE PROCEDURE cursor_example()
BEGIN
    DECLARE done INT DEFAULT FALSE;
    DECLARE student_name VARCHAR(50);
    
    DECLARE cur CURSOR FOR 
        SELECT name FROM students;
    
    DECLARE CONTINUE HANDLER FOR NOT FOUND 
        SET done = TRUE;
    
    OPEN cur;
    
    read_loop: LOOP
        FETCH cur INTO student_name;
        IF done THEN
            LEAVE read_loop;
        END IF;
        -- 处理数据
        SELECT student_name;
    END LOOP;
    
    CLOSE cur;
END;

9. 存储过程与自定义函数的区别

9.1 功能复杂度

特性	存储过程	自定义函数
功能复杂度	可以实现复杂的过程逻辑	针对性较强，功能相对简单
业务逻辑	适合复杂业务逻辑	适合简单计算和转换

9.2 返回值

特性	存储过程	自定义函数
返回值数量	可以有多个返回值（通过OUT参数）	只有一个返回值
返回值方式	OUT参数、结果集	RETURN语句
返回值类型	可以是结果集	必须是标量值

示例对比：

-- 存储过程：多个OUT参数
CREATE PROCEDURE getInfo(
    OUT name VARCHAR(50),
    OUT age INT,
    OUT email VARCHAR(100)
)
BEGIN
    SELECT 'John', 25, 'john@example.com' INTO name, age, email;
END;

-- 函数：单个返回值
CREATE FUNCTION getAge(birth_date DATE) 
RETURNS INT
BEGIN
    RETURN YEAR(CURDATE()) - YEAR(birth_date);
END;

9.3 调用方式

特性	存储过程	自定义函数
调用方式	`CALL procedure_name()`	`SELECT function_name()`
使用场景	独立执行	作为SQL语句的一部分
结果处理	可以返回结果集	返回标量值

示例对比：

-- 存储过程：独立调用
CALL getStudentInfo(1);

-- 函数：作为SQL的一部分
SELECT name, getAge(birth_date) AS age FROM students;
SELECT * FROM students WHERE getAge(birth_date) > 18;

9.4 使用限制

特性	存储过程	自定义函数
SQL语句中使用	不能直接在SELECT等语句中使用	可以在SELECT、WHERE等语句中使用
事务控制	可以包含事务控制语句	不能包含事务控制语句
结果集	可以返回结果集	不能返回结果集

9.5 选择建议

使用存储过程的情况

需要执行多条SQL语句
需要返回多个值
需要返回结果集
需要事务控制
复杂的业务逻辑处理

使用函数的情况

简单的计算和转换
需要在SQL语句中使用
只需要返回一个值
数据验证和格式化

10. 存储过程最佳实践

10.1 命名规范

-- 推荐命名方式
sp_表名_操作
-- 示例
sp_students_insert
sp_orders_update
sp_products_delete
sp_users_select

10.2 参数设计

1. 参数数量控制

-- 推荐：参数数量适中（3-5个）
CREATE PROCEDURE sp_example(
    IN param1 INT,
    IN param2 VARCHAR(50),
    OUT result INT
)

-- 不推荐：参数过多
-- 考虑使用结构体或JSON参数

2. 参数类型选择

-- 根据用途选择参数类型
-- IN：输入数据
-- OUT：返回单个值
-- INOUT：需要修改的输入参数

10.3 错误处理

CREATE PROCEDURE robust_example(IN value INT)
BEGIN
    DECLARE EXIT HANDLER FOR SQLEXCEPTION
    BEGIN
        ROLLBACK;
        SELECT '操作失败' AS error;
    END;
    
    START TRANSACTION;
    -- 业务逻辑
    COMMIT;
END;

10.4 性能优化

1. 避免在循环中执行查询

-- 不推荐
WHILE i < n DO
    SELECT * FROM table WHERE id = i;  -- 每次循环都查询
    SET i = i + 1;
END WHILE;

-- 推荐：批量查询
SELECT * FROM table WHERE id IN (1,2,3,...);

2. 使用临时表

CREATE PROCEDURE temp_table_example()
BEGIN
    CREATE TEMPORARY TABLE temp_results AS
    SELECT * FROM large_table WHERE condition;
    
    -- 使用临时表处理
    SELECT * FROM temp_results;
    
    DROP TEMPORARY TABLE temp_results;
END;

10.5 文档和注释

CREATE PROCEDURE documented_example(
    IN student_id INT,
    OUT student_name VARCHAR(50)
)
COMMENT '根据学生ID获取学生姓名'
BEGIN
    -- 功能：根据学生ID查询学生姓名
    -- 参数：student_id - 学生ID
    -- 返回：student_name - 学生姓名
    -- 作者：架构师
    -- 日期：2019-03-08
    
    SELECT name INTO student_name 
    FROM students 
    WHERE id = student_id;
END;

11. 存储过程调试技巧

11.1 使用SELECT输出调试信息

CREATE PROCEDURE debug_example(IN value INT)
BEGIN
    SELECT CONCAT('Input value: ', value) AS debug;
    SET @debug_var = value * 2;
    SELECT CONCAT('Calculated: ', @debug_var) AS debug;
END;

11.2 使用临时表记录调试信息

CREATE PROCEDURE debug_with_table(IN value INT)
BEGIN
    CREATE TEMPORARY TABLE debug_log (
        step INT,
        message VARCHAR(200),
        debug_time DATETIME DEFAULT NOW()
    );
    
    INSERT INTO debug_log VALUES(1, CONCAT('Start: ', value));
    -- 业务逻辑
    INSERT INTO debug_log VALUES(2, 'Processing...');
    -- 更多逻辑
    INSERT INTO debug_log VALUES(3, 'Complete');
    
    SELECT * FROM debug_log;
    DROP TEMPORARY TABLE debug_log;
END;

11.3 使用日志表

-- 创建日志表
CREATE TABLE procedure_log (
    log_id INT AUTO_INCREMENT PRIMARY KEY,
    procedure_name VARCHAR(50),
    log_message TEXT,
    log_time DATETIME DEFAULT NOW()
);

-- 在存储过程中记录日志
CREATE PROCEDURE logged_example()
BEGIN
    INSERT INTO procedure_log(procedure_name, log_message)
    VALUES('logged_example', 'Procedure started');
    
    -- 业务逻辑
    
    INSERT INTO procedure_log(procedure_name, log_message)
    VALUES('logged_example', 'Procedure completed');
END;

12. 总结

12.1 存储过程的核心价值

性能提升：预编译，执行速度快
网络优化：减少网络传输量
代码复用：一次编写，多处使用
安全性：权限控制和SQL注入防护
业务封装：复杂逻辑集中管理

12.2 存储过程使用原则

适度使用：不要过度依赖存储过程
简单优先：简单逻辑优先使用SQL
性能考虑：注意存储过程的性能影响
维护性：保持代码清晰，添加注释
测试验证：充分测试各种场景

12.3 架构师建议

业务逻辑分层：复杂业务逻辑在应用层实现
存储过程用于数据操作：主要用于数据操作和简单业务逻辑
版本控制：将存储过程定义纳入版本控制
文档维护：记录存储过程的用途和参数说明
性能监控：定期检查存储过程的执行性能

12.4 存储过程检查清单

存储过程命名是否规范
参数设计是否合理
是否有适当的错误处理
是否有完整的注释
性能是否可接受
是否进行了充分测试
是否有备份和恢复方案

相关文章：

第430集数据库存储过程

数据库存储过程

1. 存储过程概述

1.1 什么是存储过程

1.2 存储过程的存储位置

1.3 存储过程的优势

1. 提高运行速度

2. 降低网络数据传输量

3. 提高安全性

4. 业务逻辑封装

1.4 存储过程的适用场景

适合使用存储过程的场景

不适合使用存储过程的场景

1.5 存储过程的缺点

1. 可移植性差

2. 调试困难

3. 维护成本高

4. 性能考虑

2. 创建存储过程

2.1 创建存储过程的基本语法

2.2 参数说明

proc_parameter 语法

2.3 创建无参存储过程

基本示例

调用存储过程

2.4 创建含参存储过程

IN参数示例

OUT参数示例

INOUT参数示例

2.5 存储过程体（routine_body）

简单语句

多条语句

变量声明

3. 查看存储过程

3.1 查看存储过程列表

使用SHOW PROCEDURE STATUS

查看指定数据库的存储过程

3.2 查看存储过程定义

使用SHOW CREATE PROCEDURE

3.3 查询系统表

查询information_schema.routines

查询mysql.proc表

4. 调用存储过程

4.1 调用存储过程的基本语法

4.2 调用无参存储过程

4.3 调用有参存储过程

调用IN参数

调用OUT参数

调用INOUT参数

4.4 跨数据库调用存储过程

在同一数据库内调用

跨数据库调用

5. 存储过程示例实战

5.1 简单计算存储过程

创建计算存储过程

调用存储过程

5.2 改进版本（使用OUT参数）

5.3 复杂业务逻辑示例

学生信息管理存储过程

6. 存储过程修改

6.1 修改存储过程的限制

6.2 使用ALTER修改注释

6.3 删除重建方式修改

标准流程

示例：修改存储过程

6.4 版本控制建议

7. 删除存储过程

7.1 删除存储过程的基本语法

7.2 删除存储过程示例

删除单个存储过程

删除多个存储过程

7.3 删除存储过程的注意事项

1. 检查依赖关系

2. 备份存储过程定义

3. 权限要求

8. 存储过程高级特性

8.1 变量使用

用户变量（@variable）

局部变量（DECLARE）

8.2 控制结构