MySQL存储过程+游标的使用
一、MySQL存储过程
MySQL储存过程_long-way-to-go的博客-CSDN博客_mysql存储过程
存储过程中一个语句结束加“分号”
1、创建存储过程 需要定义结束符号
-- 声明结束符。因为MySQL默认使用‘;’作为结束符，而在存储过程中，会使用‘;’作为一段语句的结束，导致‘;’使用冲突
-- 声明$$为结束 否则在；就以为结束
delimiter $$
CREATE PROCEDURE Proc_CRP_RoomHCLogAdd(
-- 输入参数
IN RID varchar(50), -- 防止数据重复标识
-- 输出参数
OUT ResultInfo int -- 输出运行结果 1失败 0成功
)
label:begin
-- 代码功能逻辑编写此处
end $$
delimiter ; -- 重新声明分号；为结束
Cursor fetch语句用来获取游标指定数据集的下一行数据并将各个字段值赋予后面的变量 • 数据集中的字段需要和INTO语句中定义的变量一一对应（fetch后面的字段要提前声明）
例如：
declare roomid varchar(50) default ''; -- 教室id
declare nshowid varchar(50) default ''; -- 新序号
fetch newroominfo into roomid,nshowid; --使用fetch从数据集中取出这两个
•数据集中的数据都fetch完之后，则返回NOT FOUND
例如：(数据遍历完后返回的结果通过声明句柄决定)
-- 声明句柄 -- 指定游标循环结束时(没有数据)的返回值 --
declare continue handler for 1329; -- 返回MySQL的错误码，整数类型
DECLARE CONTINUE HANDLER FOR NOT FOUND set ifContinue = false; -- 返回NOT FOUND
2、与SqlServer输入参数写法不同
create procedure 存储过程名称(in/out/inout 参数名 参数类型(长度))
begin
SQL语句;
end;
说明：
in：该类型参数作为输入，也就是需要调用时传入值
out：该类型参数只负责输出，也就是该参数可以作为返回值
inout：既可以作为输入参数，也可以作为输出参数
参数类型长度：不指定长度时mysql会默认一个长度，如int会默认int(11)，为什么是11，因为int的有符号类型的最大长度就是-2147483648，是11位的。
注意：输入参数名和输出参数名与表字段名相同的情况
-- 不带参数的存储过程
create procedure 存储过程名称()
begin
SQL语句;
end;
3、执行带参数存储过程
--有输出参数需要这一步定义操作（此变量为定义用户变量，不需要declare）（out类型这步可以不写，inout类型这里可以初始化赋值，不赋值好像也可以不用写）
set @ResultInfo=1;（输出参数类型为inout类型时这里赋值会带入存储过程，否则out类型这里赋值多少都不影响,out类型这步也可以不写） -- 执行存储过程 call Proc_CRP_RoomHCLogAdd('输入参1','输入参2',@输出参ResultInfo);
-- 查看输出参数
select @输出参ResultInfo;
--执行不带参存储过程
call Proc_CRP_RoomHCLogAdd();
4、变量定义
局部变量：在begin/end块中有效
语法：
声明变量 declare var_name type [default var_value];
举例：declare nickname varchar(32);
-- set赋值
create procedure sp_var01()
begin
declare nickname varchar(32) default 'unkown';
set nickname = 'ZS';
-- set nickname := 'SF';
select nickname;
end$$
-- into赋值
delimiter $$
create procedure sp_var_into()
begin
declare emp_name varchar(32) default 'unkown' ;
declare emp_no int default 0;
select e.empno,e.ename into emp_no,emp_name from emp e where e.empno = 7839;
select emp_no,emp_name;
end$$
用户变量：是用户根据需求自己定义的变量，用户变量不用提前声明，在用是时候直接用‘@变量名’使用就可以。其作用域为当前连接。当前会话（连接）有效。
语法：
@var_name
不需要提前声明，使用即声明
用户变量：是用户根据需求自己定义的变量，用户变量不用提前声明，在用是时候直接用‘@变量名’使用就可以。其作用域为当前连接。
赋值
set @var_name = 值;
set @var_name := 值; --推荐使用这种冒号等于的方式定义
select @var_name := 值;
select 字段名 into @var_name from 表名;
查看变量
select @var_name ; --如果直接select 一个不存在的用户变量名会返回null而不会报错
例子：--赋值
set @myname := 'xiaoming';
set @myage := 18;
--使用
select @myname,@myage
会话变量： 由系统提供，当前会话(连接)有效
语法：
@@session.var_name
show session variables; -- 查看会话变量
select @@session.unique_checks; -- 查看某会话变量
set @@session.unique_checks = 0; --修改会话变量
全局变量： 由系统提供，整个mysql服务器有效
语法：
@@global.var_name
-- 查看全局变量中变量名有char的记录
show global variables like '%char%';
-- 查看全局变量character_set_client的值
select @@global.character_set_client;
6、退出存储过程 mysql存储过程不支持quit, exit或return的方式退出
https://www.cnblogs.com/Braveliu/p/10149070.html
7、流程控制-循环+判断
官网说明
https://dev.mysql.com/doc/refman/5.6/en/flow-control-statements.html
IF Else判断
-- 语法（注意：elseif是连着的没有空格）
if 条件1 then
执行语句1;
elseif 条件2 then
执行语句2;
elseif 条件3 then
执行语句3;
………………
else
执行语句4；
end if;(表示结束，必须有，不是和else搭配的)
case （IF用的偏多）
此语法是不仅可以用在存储过程，查询语句也可以用！(类似于switch)
两种语法：一种在case后有条件值（when后面填写条件值等于的值）、另一种在case后没有条件值（when后面写值得逻辑运算）
-- 语法一（类比switch）： 例如--年做条件值
CASE 条件值（year）
when 条件值1(2021) then -- year是2021年执行语句1
执行语句1；
when 条件值2(2022) then -- year是2022执行语句2
执行语句2；
…………
else -- year值是以上情况外时执行else语句
执行语句；
end case;
-- 语法二：
CASE
when 条件1（year==‘2021’） then -- year是2021年执行语句1
执行语句1；
when 条件2(year==‘2022’) then -- year是2022年执行语句2
执行语句2；
…………
else
执行语句； -- year值是以上情况外时执行else语句
end case;
-- 需求：入职年限年龄<=38是新手 >38并 <=40老员工 >40元老
delimiter $$
create procedure sp_hire_case()
begin
declare result varchar(32);
declare message varchar(64);
case
when timestampdiff(year,'2001-01-01',now()) > 40
then
set result = '元老';
set message = '老爷爷';
when timestampdiff(year,'2001-01-01',now()) > 38
then
set result = '老员工';
set message = '油腻中年人';
else
set result = '新手';
set message = '萌新';
end case;
select result;
end$$
delimiter ;
8.循环
loop （死循环）（和interate、leave结合使用）
-- 语法
loopname: loop -- loopname是自命名循环名
执行语句;（语句中通过interate/leave决定继续或结束）
end loop loopname;
--需求：循环打印1到10
-- leave控制循环的退出
delimiter $$
create procedure sp_flow_loop()
begin
declare c_index int default 1;
declare result_str varchar(256) default '1';
cnt:loop
if c_index >= 10
then leave cnt;
end if;
set c_index = c_index + 1;
set result_str = concat(result_str,',',c_index);
end loop cnt;
select result_str;
end$$
-- iterate + leave控制循环
delimiter $$
create procedure sp_flow_loop02()
begin
declare c_index int default 1;
declare result_str varchar(256) default '1';
cnt:loop
set c_index = c_index + 1;
set result_str = concat(result_str,',',c_index);
if c_index < 10 then
iterate cnt;
end if;
-- 下面这句话能否执行到？什么时候执行到？ 当c_index < 10为false时执行
leave cnt;
end loop cnt;
select result_str;
end$$
退出、继续循环
iterate 循环名； -- 继续循环
leave 循环名； -- 退出循环
repeat (类似do while) （通过until进行终止循环）
repeatname: repeat -- repeatname是自命名循环名
执行语句；
until 条件语句 -- 直到…为止，才退出循环(满足条件跳出循环 这个语句后面不用加分号)
end repeat repeatname;
-- 需求：循环打印1到10
delimiter $$
create procedure sp_flow_repeat()
begin
declare c_index int default 1;
-- 收集结果字符串
declare result_str varchar(256) default '1';
count_lab:repeat
set c_index = c_index + 1;
set result_str = concat(result_str,',',c_index);
until c_index >= 10
end repeat count_lab;
select result_str;
end$$
while
whilename: while 条件 do -- whilename是自命名循环名
执行语句；
end while whilename
-- 需求：循环打印1到10
delimiter $$
create procedure sp_flow_while()
begin
declare c_index int default 1;
-- 收集结果字符串
declare result_str varchar(256) default '1';
while c_index < 10 do
set c_index = c_index + 1;
set result_str = concat(result_str,',',c_index);
end while;
select result_str;
end$$
二、MySQL存储过程游标的使用
9、游标 --定义+使用（使用游标必须和上面的循环一块使用）
声明游标 --必须在存储过程中定义变量的后面 （游标存储的时SQL语句获取到的数据集合）
declare 游标名 cursor for SQL语句; -- 定义游标
打开游标--使用前先打开才可使用
open 游标名; -- 打开游标
获取游标数据（一个fetch拿数据集里的一行数据）--并赋值给定义变量
fetch 游标名 into 变量1,变量2，变量3,……; -- 获取游标数据
注意：
获取游标数据中into后面的变量值必须在声明游标前声明，名称必须和声明游标时sql语句的字段名不一致（声明变量用来存放从游标中提取的数据，注意变量名不能与游标中使用的列名相同，否则得到的都是NULL)
关闭：使用结束需要关闭游标
-- 关闭游标
close 游标名; -- 关闭游标
10、遍历游标获取数据的完整方法（使用loop循环体）
-- 需求：按照部门名称查询员工，通过select查看员工的编号、姓名、薪资。（注意，此处仅仅演示游标用法）
delimiter $$
create procedure sp_create_table02(in dept_name varchar(32))
begin
declare e_no int;
declare e_name varchar(32);
declare e_sal decimal(7,2);
declare lp_flag boolean default true;
declare emp_cursor cursor for
select e.empno,e.ename,e.sal
from emp e,dept d
where e.deptno = d.deptno and d.dname = dept_name;
-- handler 句柄
declare continue handler for NOT FOUND set lp_flag = false;
open emp_cursor;
emp_loop:loop
fetch emp_cursor into e_no,e_name,e_sal;
if lp_flag then
select e_no,e_name,e_sal;
else
leave emp_loop;
end if;
end loop emp_loop;
set @end_falg = 'exit_flag';
close emp_cursor;
end$$
call sp_create_table02('RESEARCH');
特别注意：
在语法中，变量声明、游标声明、handler声明是必须按照先后顺序书写的，否则创建存储过程出错。
必须有句柄，用来在遍历游标的时候判断什么时候结束--或者用相同的SQL语句获取这个语句的数据条数用循环来获取游标数据
11、参考存储过程案例 --使用了游标
delimiter $$ -- 声明$$为结束 否则在；就以为结束
CREATE PROCEDURE Proc_CRP_RoomHCLogAdd(
-- 输入参数
IN RID varchar(50), -- 防止数据重复标识
IN RoomID varchar(50), -- 教室ID
IN Jie varchar(10),-- 节次
IN week_no int,-- 周次
IN UCount int, -- 最新采集人数
IN Analyse_Time varchar(50),-- 分析时间
IN Raw_URL varchar(1000),-- 原始图片地址
IN Analyse_URL varchar(1000),-- 图片地址
IN CreateBy varchar(500),-- 建立人
IN UpdateBy varchar(500),-- 修改人
OUT ResultInfo int -- 输出运行结果 1失败 0成功
)
label:begin
-- 定义局部变量 拼接SQL语句变量
declare SqlStrs varchar(500);
declare IDInfo int;
declare IsDel varchar(50) default 0; -- 是否删除 0
declare SeatRate float; -- 上座率
declare ArriveRate float; -- 到课率
declare CNumber int default 0; -- 采集次第
declare TeacherName varchar(50) default ''; -- 教师名称
declare TeacherNo varchar(50) default ''; -- 教师工号
declare DName varchar(50) default ''; -- 部门名称
declare CourseName varchar(50) default ''; -- 课程名称
declare CName varchar(50) default''; -- 班级名称
declare CID varchar(50) default ''; -- 班级编号
declare OughtPNum int default 0; -- 应到人数
declare DepartmentID varchar(50) default ''; -- 部门ID
declare heSeatRate varchar(500) default'';-- 合班各个班级上座率
declare heArriveRate varchar(500) default '';-- 合班各个班级到课率
declare RoomName nvarchar(50) default '';-- 教室名称
declare chairNum int default 0; -- 教室座位数
declare ifContinue boolean default true;-- 是否继续循环标志
declare ifhave int default 0;-- RID是否重复
declare ifhaveClass int default 0;-- 是否有班级上课
declare CName1 varchar(50) default''; -- 班级名称1(多个班级拼接)
declare CID1 varchar(50) default ''; -- 班级级编号1(多个班级拼接)
-- 声明游标
declare courseInfo cursor for
select course.TeacherID,course.DepID,course.DepName,course.CourseName,course.ClsName,course.ClsID from TB_SysCourse course where course.CourseIndex=Jie and course.Week_No=Week_No and course.RoomID=RoomID limit 0,1;
-- 声明句柄
-- 指定游标循环结束时(没有数据)的返回值
declare continue handler for 1329 set ifContinue = false;
-- declare continue handler for not found set ifContinue = 0;
-- 查询之前记录是否有RID
select count(*) into ifhave from tb_roomhclog r where r.RID=RID;
if ifhave>0 then
-- RID已存在
select 'RID已存在';
set ResultInfo=1;
LEAVE label; # 退出存储过程
else
-- 获取教室
select room.RoomName into RoomName from TB_SysRoom room where room.RoomID=RoomID;
-- 获取座位数
select room.RSeatNum into chairNum from TB_SysRoom room where room.RoomID=RoomID;
-- 如果课节为0
if Jie=0 then
set Jie=0;
else
-- 2.获取该周该借此该日当前教室是否采集过，采集过获取最后采集次第
select log.CNumber into CNumber from tb_roomhclog log where DATE_FORMAT(log.Analyse_Time,'%Y-%m-%d')= DATE_FORMAT(Analyse_Time,'%Y-%m-%d') and log.Jie=Jie and log.RoomID=RoomID ORDER BY log.Analyse_Time desc LIMIT 0,1;
-- 是否采集过
if CNumber >=1 then
-- 采集过
set CNumber=CNumber+1;
else
-- 没有采集过，采集次第为1
set CNumber=1;
end if;
-- 获取当前时间该教室上课课表信息
-- 打开游标
open CourseInfo;
-- 防止被修改，重新再次给个默认值1
set ifContinue=true;
-- 获取此节课上课班级ID和名称数据
course_loop:loop
fetch CourseInfo into TeacherNo,DepartmentID,DName,CourseName,CName1,CID1;
if ifContinue then
set ifhaveClass=ifhaveClass+1; -- 有班级上课
-- 多个班级上课情况拼接班级名称
if ifhaveClass>1 then
set CName1=CONCAT(',',CName1);
set CName=CONCAT(CName,CName1);
set CID1=CONCAT(',',CID1);
set CID=CONCAT(CID,CID1);
else
set CName=CName1;
set CID=CID1;
end if;
else
-- 跳出游标循环
leave course_loop;
end if;
-- 结束游标循环
end loop course_loop;
-- 获取老师名称
select UserName into TeacherName from TB_UserInfo where UserID=TeacherNo;
-- 关闭游标
close CourseInfo;
-- 是否有班级上课
-- 一个班级上课
if ifhaveClass=1 then
-- 班级应到人数
select c.UCount into OughtPNum from TB_SysClass c where c.ClsID=CID;
-- else if ifhaveClass>1 then -- 多个班级上课
else -- 没有班级上课
-- 应到人数为0
set OughtPNum=0;
end if;
-- 上座率
-- 4-1-1在线人数大于0
if UCount>0 then
-- 4-1-1.1至少有一个班级上课
if ifhaveClass>0 then
-- 4-1-1-1 如果实到人数大于应到人数 则到课率为100
if UCount>OughtPNum then
set ArriveRate=100;
else
-- 4-1-1-2 否则 实到人数/班级人数
set ArriveRate= CAST((UCount*1.0/OughtPNum)*100 AS decimal(5,2));
end if;
else
set ArriveRate=0;
end if;
else
-- 4-1-2在线人数小于等于0
set ArriveRate=0;
end if;
-- 到课率
-- 4-2-1如果座位数大于0
if chairNum>0 then
-- 4-2-1-1 如果实到人数大于座位数
if UCount> chairNum then
set SeatRate=100;
else
-- 4-2-1-2 如果实到人数小于座位数 实到人数/座位数
set SeatRate= CAST((UCount*1.0/chairNum)*100 AS decimal(5,2));
end if;
else -- 4-2-2如果座位数小于等于0
set SeatRate=0;
end if;
-- 更新最新记录HCState表的节次，上座率和到课率
update TB_RoomHCState set RoomID=RoomID,UCount=UCount,Analyse_Time=Analyse_Time,Jie=Jie,SeatRate=SeatRate,ArriveRate=ArriveRate,UpdateDate=NOW() where RoomID=RoomID;
-- 执行添加
insert into TB_RoomHCLog (RID,RoomID,RoomName,UCount,Raw_URL,Analyse_URL,Analyse_Time,CreateDate,CreateBy, UpdateDate,UpdateBy,IsDel,Jie,SeatRate,ArriveRate,CNumber,TeacherName,TeacherNo,DName,CourseName,CName,CID,OughtPNum,DepartmentID) values(RID,RoomID,RoomName,UCount,Raw_URL,Analyse_URL,DATE_FORMAT(Analyse_Time,'%Y-%m-%d %H:%i:%s'),NOW(),CreateBy,NOW(),UpdateBy,0,Jie,SeatRate,ArriveRate,CNumber,TeacherName,TeacherNo,DName,CourseName,CName,CID,OughtPNum,DepartmentID);
-- 是否添加成功
if @@IDENTITY>0 then
select '添加成功';
set ResultInfo=0;
LEAVE label; # 退出存储过程
else
select '添加失败';
set ResultInfo=1;
LEAVE label; # 退出存储过程
end if;
end if;
end if;
end $$
delimiter ; -- 重新声明分号；为结束
12、多个游标使用 -- 声明$$为结束 否则在；就以为结束delimiter $$
CREATE PROCEDURE proc_kiop_roomenergy(
-- 输入参数
IN time varchar(50), -- 时间 (时间必须为当前时间前一天) 格式：2023-11-24
IN bvalue varchar(50), -- 基准值
IN type varchar(50), -- 操作类型 1、初始化 2、计算某一天的教室能耗并更新至能耗表
IN roomidstr varchar(4000), -- 操作教室
-- 输出参数
OUT `ret` varchar(4000)
)
label:begin
declare cifcontinue boolean default true; -- 是否继续循环标志
-- 声明游标1
declare c_roomenergy cursor for
select roomid roomid,sum(TimeValue)v from tb_histroomlog where date_format(CreateDate,'%Y-%m-%d')<=time group by roomid;
-- 声明句柄 -- 指定游标循环结束时(没有数据)的返回值
declare continue handler for sqlstate '02000' set cifcontinue = false
-- 打开游标
open c_roomenergy;
-- 循环
c_roomenergy_loop:loop
fetch c_roomenergy into rid,ev; -- 获取游标数据
if cifcontinue then
begin
declare cifcontinue_inner boolean default true; -- 内层游标是否继续循环标志
-- 声明内层游标1 要进行初始化的教室
declare c_roomdayenergy cursor for
SELECT SUBSTRING_INDEX(SUBSTRING_INDEX(roomidstr,',',help_topic_id+1),',',-1) AS num FROM mysql.help_topic WHERE
help_topic_id < LENGTH(roomidstr)-LENGTH(REPLACE(roomidstr,',',''))+1;
-- 声明句柄 -- 指定游标循环结束时(没有数据)的返回值
declare continue handler for sqlstate '02000' set cifcontinue_inner = false;
end;
else
call proc_system_create_result(0,"初始化教室能耗信息成功",ret);
-- 跳出游标循环
leave c_roomenergy_loop;
end if;
end loop c_roomenergy_loop;
-- 关闭游标
close c_roomenergy;
-- LEAVE label;
end $$
delimiter ; -- 重新声明分号；为结束
三、其他知识
12、sql查询值给变量赋值
-- 在存储过程中使用
-- 声明变量
declare OughtPNum int default 0;
-- sql语句取值并赋值
select c.UCount into OughtPNum from tb_sysclass c where c.ClsID=CID;
--编译过程除出现问题
1、执行存储过程--输入对应参数-报错
解决：
SHOW VARIABLES WHERE VARIABLE_NAME LIKE 'character_set_%' OR VARIABLE_NAME LIKE 'collation%';
alter table 表名 convert to character set utf8mb4 collate utf8mb4_general_ci;
2、存储过程使用游标出现错误
原因：mysql中使用游标必须遵守声明变量、游标声明、handler句柄这个顺序声明否则报错
解决： 
注意
-使用句柄时注意--(小坑)，
因为他们三者都必须在存储过程开始定义，如上图，在复杂存储过程中，句柄肯定不是一开始就需要用的，肯定在后面的逻辑中用到的，如上图定义的句柄中的参数1329表示当使用select 语句获取的数据没有值时，句柄就会抓取到错误从而执行句柄set后面的事件（会在不应该使用句柄的地方，句柄给执行了），所以我们需要在真正使用句柄之前重新给标签赋初始值。（本图逻辑是：定义一个boolean的标签默认为true，如果句柄抓取到错误则修改标签为false。本人是在遍历游标时使用的，当遍历游标没有数据时，这个句柄会抓取到错误，并把标签改为false，退出游标循环。因此我需要在打开游标语句前或后重新给标签赋值，如图）。
--切记，本人因为这个原因找了好久错误（我最终认为需要这么操作下，如有更好的办法，欢迎留言哦！）
3、存储过程中编写添加和修改语句 --生成发现有rows提示而不是显示OK 没有关系，存储过程创建成功（0代表没有任何一行进行修改）
本文来自博客园，作者：じ逐梦，转载请注明原文链接：https://www.cnblogs.com/ZhuMeng-Chao/p/16812340.html
浙公网安备 33010602011771号