MySQL的存储过程--循环，嵌套循环，变量定义及其使用

DROP PROCEDURE IF EXISTS update_chapter_id;  -- 存在存储过程就先删除

CREATE PROCEDURE update_chapter_id()  --定义无参数输入，输出的存储过程

BEGIN
  -- 定义变量
  
	DECLARE num_change INT DEFAULT 0; 
	DECLARE s int DEFAULT 0;
	DECLARE novel_id_n int;
	DECLARE chapter_id_n int;
--   DECLARE CONTINUE HANDLER FOR SQLSTATE 23000; 
  
	-- 定义游标
  DECLARE report CURSOR FOR SELECT novel_id, chapter_id from tb_chapter_info WHERE prev_id=0 AND  next_id<>2 AND next_id<>0;
	-- 声明当游标遍历完后将标志变量置成某个值
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET s=1;
--   DECLARE EXIT HANDLER FOR SQLSTATE '23000'
	-- 打开游标
	open report;
		-- 将游标中的值赋值给变量，注意：变量名不要和返回的列名同名，变量顺序要和sql结果列的顺序一致
		fetch report into novel_id_n,chapter_id_n;
		-- 当s不等于1，也就是未遍历完时，会一直循环
		SELECT novel_id_n, chapter_id_n;
		while s<>1 do
				-- 执行业务逻辑
-- 					SELECT chapter_id_n;
					-- 开始定义内层游标
						BEGIN -- inner BEGIN
								DECLARE in_chapter_id INT ;
								DECLARE in_prev_id INT ;
								DECLARE in_next_id INT ;
								DECLARE inner_done int DEFAULT FALSE ;
								DECLARE inner_cursor CURSOR FOR (SELECT chapter_id,prev_id,next_id FROM tb_chapter_info WHERE novel_id=novel_id_n ORDER BY novel_id,chapter_id);
								DECLARE CONTINUE HANDLER FOR NOT FOUND SET inner_done = TRUE ;
								
								OPEN inner_cursor ; -- 打开内层游标
								WHILE NOT inner_done DO -- inner WHILE
									FETCH inner_cursor INTO  in_chapter_id, in_prev_id, in_next_id; -- 从【内层游标】中获取数据，赋值到定义变量中
									IF NOT inner_done THEN

										UPDATE tb_chapter_info SET chapter_id=if(in_chapter_id>=chapter_id_n,in_chapter_id-chapter_id_n+1,1),
																										prev_id=if(in_prev_id>=chapter_id_n,in_prev_id-chapter_id_n+1,0),
                                                    next_id=if(in_next_id>chapter_id_n,in_next_id-chapter_id_n+1,0)
																				        where novel_id=novel_id_n AND chapter_id=in_chapter_id;
										UPDATE tb_user_read SET chapter_id=in_chapter_id-chapter_id_n+1 WHERE novel_id=novel_id_n AND chapter_id=in_chapter_id;
					 
									END IF;
								END WHILE ; -- END inner WHILE
								CLOSE inner_cursor; -- 循环结束后，关闭内层游标
						END; -- END inner BEGIN

				-- 将游标中的值再赋值给变量，供下次循环使用
				fetch report into novel_id_n,chapter_id_n;
		-- 当s等于1时表明遍历以完成，退出循环
		end while;
	CLOSE report;

END;

CALL update_chapter_id();  --调用存储过程执行

参考链接： https://blog.csdn.net/haha_sir/article/details/103403980
https://blog.csdn.net/haha_sir/article/details/103403980
https://blog.csdn.net/yjclsx/article/details/84566009
https://blog.csdn.net/xiaosheng_papa/article/details/80635727
https://blog.csdn.net/wang_kill/article/details/71159406