mysql存储过程 loop循环

来源：互联网发布：手机淘宝商品历史价格编辑：程序博客网时间：2024/06/05 08:10

-- loop 与 leave,iterate 实现循环
-- loop 标志位无条件循环，

leave 类似于break 语句，跳出循环，跳出 begin end，

iterate 类似于continue ，结束本次循环

create procedure pro12()

begin
declare b int;
declare use_no varchar(10);
declare use_name varchar(400);
declare card_id varchar(400);
declare count int;
declare cur_1 cursor for
select s2.member_id,
s2.realname,
s2.card_id
from bh_member.mb_member s1,bh_member.mb_member_info s2
where s1.id = s2.member_id;

-- 如果不加该行游标为空的时候就会报错

DECLARE CONTINUE HANDLER FOR NOT FOUND -- 如果不加该行的话将直接报错.No data - zero rows fetched, selected, or processed

SET b = 0;
-- （1） sql 赋值
select count(1) INTO count
from bh_member.mb_member s1,bh_member.mb_member_info s2
where s1.id = s2.member_id;

OPEN cur_1;

loop_label: loop
-- 从游标中循环取值
FETCH cur_1 INTO use_no, use_name, card_id;

insert into zh_ceshi(use_no,use_name,card_id) values(use_no, use_name, card_id);
set b=b+1;
if b = count then
leave loop_label;

end if;

end loop;
CLOSE cur_1;
end;

-- loop 与 leave,iterate 实现循环
-- loop 标志位无条件循环，leave 类似于break 语句，跳出循环，跳出 begin end，iterate 类似于continue ，结束本次循环

0 0