创建语法
CREATE PROCEDURE sp_name ([proc_parameter[,...]])
[characteristic ...] routine_body
proc_parameter: [ IN | OUT | INOUT ] param_name type
characteristic:
LANGUAGE SQL
| [NOT] DETERMINISTIC
| { CONTAINS SQL | NO SQL | READS SQL DATA | MODIFIES SQL DATA }
| SQL SECURITY { DEFINER | INVOKER }
| COMMENT 'string'
参数模式详解
| 模式 | 方向 | 说明 |
IN | 输入 | 调用者传入值,过程内可读不可写回 |
OUT | 输出 | 过程内赋值,调用者接收返回值 |
INOUT | 双向 | 调用者传入,过程内修改,调用者获取新值 |
特性子句
DETERMINISTIC:相同输入必得相同输出,优化器可缓存结果。
READS SQL DATA:包含读操作(SELECT),默认 CONTAINS SQL。
MODIFIES SQL DATA:包含写操作(INSERT/UPDATE/DELETE)。
SQL SECURITY DEFINER:以定义者权限执行(默认),INVOKER 以调用者权限执行。
实战案例库
案例 1:无参查询平均年龄
CREATE PROCEDURE user_avg(OUT avg_age NUMERIC(5,2))
BEGIN
SELECT AVG(age) INTO avg_age FROM users;
END;
CALL user_avg(@a);
SELECT @a;
案例 2:IN 参数插入数据
CREATE PROCEDURE sp_insert(IN p INT)
COMMENT 'insert an int value'
BEGIN
DECLARE v1 INT;
SET v1 = p;
INSERT INTO test(id) VALUES(v1);
END;
CALL sp_insert(1);
SELECT * FROM test;
案例 3:OUT 参数返回最大 ID
CREATE PROCEDURE sp_max_id(OUT p INT)
DETERMINISTIC
BEGIN
SELECT MAX(id) INTO p FROM test;
END;
CALL sp_max_id(@pv);
SELECT @pv;
案例 4:INOUT 双向参数
CREATE PROCEDURE sp_inout(INOUT p INT)
BEGIN
IF p = 4 THEN SET @pg = 400; ELSE SET @pg = 500; END IF;
SELECT @pg;
END;
SET @pp = 4;
CALL sp_inout(@pp); -- 返回 400
案例 5:业务逻辑封装(用户是否存在)
CREATE PROCEDURE check_user(IN uname VARCHAR(20), OUT exists_flag TINYINT)
BEGIN
SELECT COUNT(*) INTO exists_flag FROM admin WHERE username = uname;
END;
CALL check_user('admin1', @flag); -- 存在返回 1
CALL check_user('admin11', @flag); -- 不存在返回 0
调用与事务
-- 事务包裹
START TRANSACTION;
CALL sp_transfer(1001, 1002, 500);
COMMIT;
优势与适用场景
- 安全性:业务逻辑下沉数据库,应用层仅调用接口,减少敏感 SQL 暴露。
- 性能:批量操作减少网络往返,避免“N+1 查询”。
- 跨平台:Java/Python/Go 等异构系统共享同一套存储逻辑。
- 维护性:修复逻辑无需重新部署应用,热加载生效。
避坑指南
- 避免在存储过程中写复杂业务流程,难以调试、版本控制弱。
- 大事务长锁表风险,拆分为小批次提交。
- 临时表优于游标处理集合操作。
- 授权最小化:
GRANT EXECUTE ON PROCEDURE ... TO 'app'@'%'。