萬盛學電腦網

您现在的位置: 萬盛學電腦網 >> 程式設計 >  >> 資料庫 >> MySQL 教學

MySQL 存储过程入门指南:语法、参数模式、特性与实战案例

创建语法

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'@'%'。
copyright © 萬盛學電腦網 all rights reserved