MySQL 存储过程 循环处理数据 while repeat

最近搞数据，需要搞搞mysql 的存储过程，很多忘记了，就查查，然后总结下。。

介绍

MySQL存储过程中使用循环处理数据

存储过程就像一份写好的由多条SQL组合的代码，这份SQL就像函数一样，可以接受传入参数，可以返回结果。传入参数使用in，传出参数使用out，参数名不要使用关键字！

循环repeat方式

存储过程传入参数

create procedure maxprice(IN _title varchar(20), OUT maxPrice int)
beginselect max(bill.repertory*bill.price) into maxPricefrom bill where title = _title;
end;

存储过程传出参数

create procedure maxprice(OUT maxPrice int)
beginselect max(bill.repertory*bill.price) into maxPricefrom bill;
end;

调用上述存储过程

call maxprice(@maxPrice);
select @maxPrice;

存储过程中使用游标，处理数据
重点

declare my_cursor cursor for -- 申明游标
open my_cursor ;
close my_cursor -- 打开游标，关闭游标
repeat; until done end repeat; -- 循环，直到done为1停止
declare done boolean default 0; -- 申明监视sqlstate变量
declare continue handler for sqlstate '02000' set done = 1; -- done是一个监视sqlstate的变量
createdefiner = root@localhost procedure prls()
begindeclare done boolean default 0;declare _name varchar(20);declare _time datetime;declare _password varchar(20);declare ordernum cursor forselect user_name, create_time, user_password from user_copy;-- 根据sqlstate设置继续与否declare continue handler for sqlstate '02000' set done = 1;open ordernum;repeatfetch ordernum into _name,_time,_password;insert into user(user_name, create_time, user_password) value (_name,_time,_password);until done end repeat;close ordernum;
end;

循环while方式

-- 多个入参数
CREATE PROCEDURE role_permission_data (IN _existCode VARCHAR(20), IN _insertCode varchar(20), IN _excludeCode varchar(20), IN _excludeCode2 varchar(20), IN _excludeCode3 varchar(20)) BEGIN-- 定义变量declare _role_id int;declare _merchant_id int;declare role_count int;-- 获取循环的数据declare roles CURSOR FOR select t.role_id,m.merchant_id from t_role_module_permission t, t_merchant_permission m  where  t.module_permission_id = m.id AND t.delete_flag = 0 AND m.`code` = _existCode and  m.status = 1 GROUP BY t.role_id ,m.merchant_id;-- 获取循环的计数SET role_count=(SELECT count(*) from (select t.role_id from t_role_module_permission t, t_merchant_permission m  where  t.module_permission_id = m.id AND t.delete_flag = 0 AND m.`code` = _existCode and  m.status = 1 GROUP BY t.role_id,m.merchant_id) as dddd);-- 打开游标OPEN roles;-- 对参数赋值FETCH roles INTO _role_id,_merchant_id;-- while循环WHILE role_count > 0 DO-- 获取动态参数select @insertPermissionId:=(select id from t_merchant_permission where code=_insertCode and `status`=1 and merchant_id = _merchant_id);-- 监控下数据SELECT @msg:=CONCAT('_merchant_id:',_merchant_id,',_role_id:',_role_id,',@insertPermissionId:',@insertPermissionId);INSERT INTO t_role_module_permission SELECT null,_role_id,id,0,sub_type from t_merchant_permission where id=@insertPermissionId and `STATUS`=1 and `code` not in (_excludeCode,_excludeCode2,_excludeCode3);INSERT INTO t_role_module_permission SELECT null,_role_id,id,0,sub_type from t_merchant_permission where parent_id = @insertPermissionId and `STATUS`=1 and `code` not in (_excludeCode,_excludeCode2,_excludeCode3);-- 计数器计算SET role_count = role_count - 1;-- 赋值下个游标中的参数FETCH roles INTO _role_id,_merchant_id;END WHILE;-- 关闭游标CLOSE roles;
END;

外传

😜 原创不易，如若本文能够帮助到您的同学
🎉 支持我：关注我+点赞👍+收藏⭐️
📝 留言：探讨问题，看到立马回复
💬 格言：己所不欲勿施于人 扬帆起航、游历人生、永不言弃！🔥