mysql游标嵌套循环

来源：互联网发布：c语言编译器推荐知乎编辑：程序博客网时间：2024/05/09 12:22

BEGIN
DECLARE p_mb_id VARCHAR(21);
DECLARE p_mpfc_id VARCHAR (21);
DECLARE done INT;

DECLARE cur_out CURSOR FOR SELECT
MPFC_ID
FROM
KM_MEMBER_PREF_FULLCUT where MPFC_STATUS = 0;

DECLARE cur_inner CURSOR FOR SELECT MB_ID FROM KM_MEMBER;

DECLARE CONTINUE HANDLER FOR NOT FOUND
SET done = 1;

OPEN cur_out;

out_loop : LOOP
FETCH cur_out INTO p_mpfc_id;

IF done = 1 THEN
LEAVE out_loop;
END IF;

OPEN cur_inner;

inner_loop :LOOP
FETCH cur_inner INTO p_mb_id;

IF done = 1 THEN
LEAVE inner_loop;
END IF;

INSERT INTO KM_MEMBER_RELA_FULLCUT(MB_ID,MPFC_ID,FULLCUT_STATUS) SELECT p_mb_id,p_mpfc_id,1 FROM DUAL
WHERE NOT EXISTS (SELECT MB_ID FROM KM_MEMBER_RELA_FULLCUT WHERE MB_ID=p_mb_id and MPFC_ID=p_mpfc_id and FULLCUT_STATUS=1);

END LOOP inner_loop;

CLOSE cur_inner;

SET done = 0;

END LOOP out_loop;

CLOSE cur_out;

END

2.loop-while嵌套

CREATE PROCEDURE sp_loop_while()
BEGIN DECLARE l_class_id     INT;
DECLARE l_class_name   VARCHAR(20);
DECLARE l_student_id   INT;
DECLARE l_student_name VARCHAR(20);
DECLARE done           INT;

DECLARE cur_out CURSOR FOR
SELECT class_id
, class_name
FROM
tb_dic_class;

DECLARE cur_inner CURSOR FOR
SELECT ID
       , student_name
FROM
    tb_students
WHERE
    class_id = l_class_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur_out;

out_loop:
LOOP
FETCH cur_out INTO l_class_id, l_class_name;

IF done = 1 THEN
LEAVE out_loop;
END IF;

OPEN cur_inner;

  SET done = 0; /* 内层循环控制*/

FETCH cur_inner INTO l_student_id, l_student_name;

WHILE done <> 1
DO

    SET @output_string = concat('班级编码:', l_class_id, ',班级名称:', l_class_name, ',学生编号:', l_student_id, ',学生名称:', l_student_name);
    SELECT @output_string;
    FETCH cur_inner INTO l_student_id, l_student_name;
END WHILE;

CLOSE cur_inner;

SET done = 0; /* 外层循环控制*/

END LOOP out_loop;
CLOSE cur_out;
END

3.loop-repeat嵌套

CREATE PROCEDURE sp_loop_repeat()
BEGIN DECLARE l_class_id     INT;
DECLARE l_class_name   VARCHAR(20);
DECLARE l_student_id   INT;
DECLARE l_student_name VARCHAR(20);
DECLARE done           INT;

DECLARE cur_out CURSOR FOR
SELECT class_id
, class_name
FROM
tb_dic_class;

DECLARE cur_inner CURSOR FOR
SELECT ID
       , student_name
FROM
    tb_students
WHERE
    class_id = l_class_id;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

OPEN cur_out;

out_loop:
LOOP
FETCH cur_out INTO l_class_id, l_class_name;

IF done = 1 THEN
LEAVE out_loop;
END IF;

OPEN cur_inner;

SET done = 0;
REPEAT
    FETCH cur_inner INTO l_student_id, l_student_name;

    if done <> 1 then
     SET @output_string = concat('班级编码:', l_class_id, ',班级名称:', l_class_name, ',学生编号:', l_student_id, ',学生名称:', l_student_name);
     SELECT @output_string;
    end if;

UNTIL done
END REPEAT;
CLOSE cur_inner;

SET done = 0; /* 外层循环控制*/

END LOOP out_loop;
CLOSE cur_out;
END

-- The End --

0 0