mysql嵌套loop循环_mysql游标嵌套循环

最近在mysql开发过程中用到了游标的嵌套循环,下面列举了3种嵌套循环(loop-loop,loop-while,loop-repeat).

程序用到的表和数据

CREATE TABLE tb_dic_class (

class_id int(11) DEFAULT NULL,

class_name varchar(20) DEFAULT NULL,

createtime datetime DEFAULT 'CURRENT_TIMESTAMP',

modifytime datetime DEFAULT 'CURRENT_TIMESTAMP'

)

ENGINE = INNODB

AVG_ROW_LENGTH = 5461

CHARACTER SET utf8

COLLATE utf8_general_ci;

CREATE TABLE tb_students (

id int(11) NOT NULL,

student_name varchar(30) DEFAULT NULL,

class_id int(11) DEFAULT NULL,

createtime datetime DEFAULT 'CURRENT_TIMESTAMP',

modifytime datetime DEFAULT 'CURRENT_TIMESTAMP',

PRIMARY KEY (id)

)

ENGINE = INNODB

AVG_ROW_LENGTH = 5461

CHARACTER SET utf8

COLLATE utf8_general_ci;

INSERT INTO tb_dic_class(class_id, class_name, createtime, modifytime) VALUES

(1, '一班', now(), now());

INSERT INTO tb_dic_class(class_id, class_name, createtime, modifytime) VALUES

(2, '二班', now(), now());

INSERT INTO tb_dic_class(class_id, class_name, createtime, modifytime) VALUES

(3, '三班', now(), now());

SET NAMES 'utf8';

INSERT INTO tb_students(id, student_name, class_id, createtime, modifytime) VALUES

(10, '张三', 1, now(), now());

INSERT INTO tb_students(id, student_name, class_id, createtime, modifytime) VALUES

(20, '李四', 1, now(), now());

INSERT INTO tb_students(id, student_name, class_id, createtime, modifytime) VALUES

(30, '王五', 3, now(), now());

1.loop-loop嵌套

CREATE PROCEDURE hxl.sp_loop_loop()

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;

inner_loop:LOOP

FETCH cur_inner INTO l_student_id,l_student_name;

IF done = 1 THEN

LEAVE inner_loop;

end IF;

SET @output_string= CONCAT('班级编码:',l_class_id,',班级名称:',l_class_name,',学生编号:',l_student_id,',学生名称:',l_student_name);

SELECT @output_string;

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 --