MySQL-存储过程 作者：马育民 • 2023-08-18 19:16 • 阅读：10079 # 介绍 有些操作，需要 **执行多条sql语句**，此时就应该使用存储过程 ### 好处 提高代码重用性、简化操作、减少编译次数，减少和数据库连接次数 ### 与函数的区别 - 函数必须有返回值，而存储过程没有 - 存储过程的参数可能使用 `N、OUT、INOUT` 类型，而函数的参数只能是 `IN` 类型 # 创建语法 ### 一条语句 ``` create PROCEDURE 存储过程名(过程参数列表) 一条SQL ``` ### 多条语句 ``` create PROCEDURE 存储过程名(过程参数列表) BEGIN 多条SQL END ``` ### 多条复杂语句 ``` delimiter $$ # 告诉程序使用 $$ 作为结束分隔符 CREATE PROCEDURE 存储过程名(过程参数列表) BEGIN ...... END $$ # 表示创建存储过程结束 ``` # 调用 ``` call 存储过程名(参数) ``` # 删除 ``` drop procedure 存储过程名 ``` # 例子 ### 准备工资 创建测试表： ``` create table t_test_procedure( t_id int ) ``` 查询该表： ``` select * from t_test_procedure ``` ### 例子1 向表里插入一条记录，创建无参数，只有一条SQL的存储过程 创建存储过程，向该表插入1条数据： ``` create PROCEDURE my_pro1() insert into t_test_procedure(t_id) values (1); ``` 调用： ``` call my_pro1() ``` 创建测试表： ``` create table t_test_procedure( t_id int ) ``` 可以查看到插入的数据 ### 例子2 向表里循环插入10条记录 创建无参数，多条SQL的存储过程 ``` create procedure my_pro2() BEGIN DECLARE i int ; set i = 1; while i<=10 DO insert into t_test_procedure(t_id) values (i); set i = i+1; end while; END ``` ``` call my_pro2() ``` ### 例子3 对第一个例子做优化 第一个例子缺点：只能插入数据1 改为可以插入任意数值 创建有输入参数，1条SQL的存储过程 ``` create procedure my_pro3(IN i int) insert into t_test_procedure(t_id) values (i); ``` 调用 ``` call my_pro3(100) ``` # 案例 使用存储过程实现转账功能 ### 创建账户表 ``` create table t_account( aid int auto_increment primary key, aname varchar(20), amoney DECIMAL(9,2) ) ``` ### 初始化数据 ``` insert into t_account (name,money) values ('李雷',1000000) insert into t_account (name,money) values ('韩梅梅',0) ``` ### 创建存储过程 ``` create procedure my_pro4(in m DECIMAL(9,2) ,in fromid int,in toid int ) BEGIN update t_account set money = money - m where aid = fromid; update t_account set money = money + m where aid = toid; END ``` 调用 ``` call my_pro4(10000,1,2) ``` ### bug 李雷的账户只有 100万，如果如果转200万，账户就成负数了，这是不可能的 ``` call my_pro4(2000000,1,2) ``` ### 修复bug ``` create procedure my_pro6(in m DECIMAL(9,2) ,in fromid int,in toid int ) BEGIN declare from_money DECIMAL(9,2); -- 查询转出金额账户的余额，赋值给变量 from_money，使用into关键字 select money into from_money from t_account where aid = fromid; if m > from_money THEN select '金额不足'; else update t_account set money = money - m where aid = fromid; update t_account set money = money + m where aid = toid; select '转账成功'; end if; END ``` ``` select * from t_account call my_pro6(1000,1,2) call my_pro6(1000000,1,2) ``` ### 缺点 上面例子中，执行失败，显示出结果，但程序不知道，为了方便程序做处理，要获取返回值 ### 解决上面缺点 存储过程没有返回值，通过输出参数实现的返回值 ``` create procedure my_pro7(in m DECIMAL(9,2) ,in fromid int,in toid int ,out code int) BEGIN declare from_money DECIMAL(9,2); select money into from_money from t_account where aid = fromid; -- 查询转出金额账户 if m > from_money THEN -- select '金额不足'; set code = 100; -- 100 表示金额不足 else update t_account set money = money - m where aid = fromid; update t_account set money = money + m where aid = toid; -- select '转账成功'; set code = 0; -- 0 表示转账 end if; END ``` 调用时，需要传入 **会话变量（用户变量）**，才能获取存储过程往外传的值： **提示：** 在存储过程外，只能使用 **会话变量（用户变量）** ``` call my_pro7(1000000,1,2,@code); call my_pro7(1000,1,2,@code) select @code; ``` # 会话变量（用户变量） 一个客户端定义的 **会话变量（用户变量）** 不能被其他客户端看到或使用，当客户端退出时，该客户端连接的所有变量将自动释放。 具体的说就是一个被赋值的变量，它不受 `begin…end` 的代码块限制，介入方法形式与局部变量不同 **提示：**可以在存储过程、函数外使用 ### 声明 不需要声明，可以直接赋值使用 ### 赋值方式一 可以在存储过程外使用 ``` set @var_name = 10 ``` ### 赋值方式二 将查询结果赋值给变量 ``` select max(age) into @ma from student ``` ### 查询 ``` select @var_name ``` ``` select @ma ``` # 案例 每个月分析各个岗位的最高工资、最低工资、平均工资、工资总和、人数，并存储到表中 ### 创建表 保存分析出来的数据 ``` CREATE TABLE `sal_anal` ( `id` INT NOT NULL AUTO_INCREMENT, `job` VARCHAR(50) NOT NULL DEFAULT '0', `max` DECIMAL(7,2) NOT NULL DEFAULT '0', `min` DECIMAL(7,2) NOT NULL DEFAULT '0', `avg` DECIMAL(7,2) NOT NULL DEFAULT '0', `sum` DECIMAL(7,2) NOT NULL DEFAULT '0', `count` INT NOT NULL DEFAULT '0', `gmt_create` DATETIME NOT NULL DEFAULT CURRENT_TIMESTAMP, PRIMARY KEY (`id`) ) COLLATE='utf8mb4_unicode_ci' ; ``` ### 传统方式 传统方式，需要先统计分析： ``` SELECT job,MAX(sal),MIN(sal),AVG(sal),SUM(sal),COUNT(1) FROM emp GROUP BY job; ``` 执行结果如下： [](/upload/0/0/1IX64YidZ6yR.jpg) 但统计分析后，没法通过一条sql将这些数据保存到表中 ### 创建存储过程 **注意：**变量名不要与列名相同，否则可能报错，也可能不报错，但执行会丢数据 ``` DROP PROCEDURE if exists pro_sal_anal; delimiter $$ # 告诉程序使用 $$ 作为结束分隔符 create procedure pro_sal_anal() begin -- 定义变量 DECLARE s int DEFAULT 0; DECLARE vjob VARCHAR(50); DECLARE vmax DECIMAL(7,2); DECLARE vmin DECIMAL(7,2); DECLARE vavg DECIMAL(7,2); DECLARE vsum DECIMAL(7,2); DECLARE vcount int; DECLARE report CURSOR FOR SELECT job,MAX(sal),MIN(sal),AVG(sal),SUM(sal),COUNT(1) FROM emp GROUP BY job; -- 声明当游标遍历完后将标志变量置为某个值 DECLARE CONTINUE HANDLER FOR NOT FOUND SET s = 1; -- 打开游标 OPEN report; -- 将游标中的值赋值给变量，注意：变量名不要与列名相同，变量顺序要和sql结果列的顺序一致 FETCH report INTO vjob, vMAX,vMIN,vAVG,vSUM,vCOUNT ; WHILE s <> 1 DO -- 当s等于1时代表遍历已完成，退出循环 -- 执行业务逻辑 INSERT INTO sal_anal (sjob,MAX_sal,MIN_sal,AVG_sal,SUM_sal,COUNT_emp) VALUES (vjob,vMAX,vMIN,vAVG,vSUM,vCOUNT) ; -- 将游标中的值再赋值给变量，供下次循环使用 FETCH report INTO vjob, vMAX,vMIN,vAVG,vSUM,vcount; END WHILE; -- 关闭游标 CLOSE report; END $$ # 表示创建存储过程结束 ``` ### 调用 ``` CALL pro_sal_anal() ``` 原文出处：http://malaoshi.top/show_1IX64Yj2YCAS.html