MySQL存储过程

一、存储过程的核心概念​​

1. ​​定义​​
   存储过程是一段预编译的SQL代码块，封装复杂逻辑以提高效率和复用性。

2. ​​作用​​

   • ​​提高性能​​：一次编译多次执行，减少解析开销。
   • ​​减少网络流量​​：只需传递过程名和参数，而非多行SQL。
   • ​​模块化与复用​​：封装业务逻辑，便于统一维护。
   • ​​安全性​​：通过权限控制保护数据。

3. ​​与函数的区别​​

   • 函数必须返回单一值，而存储过程可返回多个结果集或通过参数输出。
   • 函数可在SELECT中调用，存储过程需用CALL执行。

---

​​二、存储过程语法详解​​

​​1. 创建存储过程​​

DELIMITER //  -- 避免分号冲突CREATE PROCEDURE procedure_name(IN input_param1 INT,       -- 输入参数OUT output_param2 VARCHAR(50), -- 输出参数INOUT inout_param3 FLOAT  -- 输入输出参数
)
BEGIN-- 逻辑代码
END //DELIMITER ;

​​2. 参数类型​​

• ​​IN​​（默认）：调用者传入值，内部不可修改。
• ​​OUT​​：存储过程返回的结果，初始值为NULL。
• ​​INOUT​​：输入并可在过程中修改的值。

---

​​三、流程控制与语法结构​​

​​1. 变量声明与赋值​​

DECLARE var_name INT;           -- 声明变量
SET var_name = 10;              -- 赋值
SELECT column INTO var_name FROM table; -- 查询结果赋值

​​2. 条件判断​​

• ​​IF语句​​

  IF condition THEN...
  ELSEIF condition THEN...
  ELSE...
  END IF;

• ​​CASE语句​​

  CASE variableWHEN value1 THEN ...WHEN value2 THEN ...ELSE ...
  END CASE;

​​3. 循环​​

• ​​WHILE循环​​

  WHILE condition DO...
  END WHILE;

• ​​REPEAT循环​​

  REPEAT...
  UNTIL condition 
  END REPEAT;

• ​​LOOP循环​​

  loop_label: LOOPIF condition THENLEAVE loop_label;  -- 退出循环END IF;
  END LOOP;

---

​​四、实例演示​​

​​1. 简单查询示例​​

-- 创建存储过程：根据ID查询用户名
DELIMITER //
CREATE PROCEDURE GetUserName(IN user_id INT, OUT user_name VARCHAR(50))
BEGINSELECT name INTO user_name FROM users WHERE id = user_id;
END //
DELIMITER ;-- 调用
CALL GetUserName(1, @name);
SELECT @name;

​​2. 事务处理示例​​

-- 转账存储过程（事务）
DELIMITER //
CREATE PROCEDURE TransferFunds(IN from_acc INT, IN to_acc INT, IN amount DECIMAL(10,2)
)
BEGINDECLARE EXIT HANDLER FOR SQLEXCEPTION BEGINROLLBACK;RESIGNAL;END;START TRANSACTION;UPDATE accounts SET balance = balance - amount WHERE id = from_acc;UPDATE accounts SET balance = balance + amount WHERE id = to_acc;COMMIT;
END //
DELIMITER ;

---

​​五、错误处理​​

• ​​定义错误处理器​​

  DECLARE handler_action HANDLER FOR condition_value [, ...]statement;-- 示例
  DECLARE CONTINUE HANDLER FOR SQLEXCEPTION SET has_error = 1;

---

​​六、常见操作​​

1. ​​查看存储过程​​

   SHOW PROCEDURE STATUS;  -- 列出所有存储过程
   SHOW CREATE PROCEDURE procedure_name;  -- 查看定义语句

2. ​​删除存储过程​​

   DROP PROCEDURE IF EXISTS procedure_name;

---

​​七、优缺点分析​​

• ​​优点​​

  • 执行速度快（预编译）。
  • 减少网络通信量。
  • 隐藏业务逻辑，增强安全性。

• ​​缺点​​

  • 调试复杂。
  • 增加数据库服务器负载。
  • 维护成本高（需熟悉存储过程语法）。