mysql存储过程游标的嵌套循环

假设你已知

• 数据库表、存储过程的创建基本语法
• 存储过程游标的使用语法

关键技巧

• begin end语法代表的含义

示例

-- 第一个循环表
drop table if exists n3h_db.test_1;
create table n3h_db.test_1(
    id binary(16),
    age int(11)
);
-- 第二个循环表p_id --> test_1.id
drop table if exists n3h_db.test_2;
create table n3h_db.test_2(
    id binary(16),
    p_id binary(16),
    bp int(11)
);
-- 创建测试数据
set @p_1 = uuid_to_bin(uuid());
set @p_2 = uuid_to_bin(uuid());
insert into n3h_db.test_1 values (@p_1, 11);
insert into n3h_db.test_1 values (@p_2, 12);
insert into n3h_db.test_2 values (uuid_to_bin(uuid()), @p_1, 99);
insert into n3h_db.test_2 values (uuid_to_bin(uuid()), @p_1, 78);
insert into n3h_db.test_2 values (uuid_to_bin(uuid()), @p_2, 54);
insert into n3h_db.test_2 values (uuid_to_bin(uuid()), @p_2, 68);
insert into n3h_db.test_2 values (uuid_to_bin(uuid()), @p_2, 23);
-- 结果表
drop table if exists n3h_db.test_p;
create table n3h_db.test_p(
    id binary(16)
);
-- 测试rollback
drop table if exists n3h_db.test_error;
create table n3h_db.test_error(
    id binary(16),
    name varchar(50) not null
);

drop procedure if exists n3h_db.test_repeat;
delimiter &&
create procedure n3h_db.test_repeat()
begin
    -- 定义游标标示
    declare done_1 boolean default 0;
    declare p_id binary(16);
    declare cursor_1 cursor for select id from n3h_db.test_1;
    declare continue handler for not found set done_1 = 1;
    -- 如果发生异常回滚
    declare exit handler for sqlexception rollback;
    -- 开启事务
    start transaction;

    open cursor_1;
    repeat
        fetch cursor_1 into p_id;
        if not done_1 then
            -- 这里需要用 begin end
            begin
                declare done_2 boolean default 0;
                declare b_id binary(16);
                declare cursor_2 cursor for select id from n3h_db.test_2 where test_2.p_id = p_id;
                declare continue handler for not found set done_2 = 1;
                open cursor_2;
                repeat
                    fetch cursor_2 into b_id;
                    if not done_2 then
                        begin
                            insert into n3h_db.test_p(id) values (b_id);
                        end;
                    end if;
                until done_2 end repeat;
                close cursor_2;
            end;
        end if;
    until done_1 end repeat;
    -- 这里取消注释会回滚
    -- insert into n3h_db.test_error(id, name) values (uuid_to_bin(uuid()), null);
    close cursor_1;
    commit;
end &&
DELIMITER ;

call n3h_db.test_repeat;
select * from n3h_db.test_p;