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

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

介绍

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

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

循环repeat方式

存储过程传入参数

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

存储过程传出参数

create procedure maxprice(OUT maxPrice int)
begin
    select max(bill.repertory*bill.price) into maxPrice
    from 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的变量
create
    definer = root@localhost procedure prls()
begin
    declare done boolean default 0;
    declare _name varchar(20);
    declare _time datetime;
    declare _password varchar(20);

    declare ordernum cursor for
    select user_name, create_time, user_password from user_copy;
    -- 根据sqlstate设置继续与否
    declare continue handler for sqlstate '02000' set done = 1;
    open ordernum;
    repeat
    fetch 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;

外传

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