本文共 4061 字，大约阅读时间需要 13 分钟。
最近在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转载地址：http://yoyti.baihongyu.com/