MySQL存储过程for循环处理查询结果

在MySQL数据库中，存储过程是一种预编译的SQL语句集，可以被多次调用。在MySQL中使用存储过程查询到结果后，有时候需要对这些结果进行循环处理。

1. 创建表

CREATE TABLE `t_job` (
  `job_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `job_name` varchar(50) DEFAULT NULL,
  `next_time` timestamp NULL DEFAULT NULL COMMENT '下次执行时间',
  `last_task` int(11) DEFAULT NULL,
  PRIMARY KEY (`job_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8mb4;

CREATE TABLE `t_task` (
  `task_id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `start_time` datetime DEFAULT NULL,
  `end_time` datetime DEFAULT NULL,
  `status` tinyint(1) DEFAULT NULL,
  `job_id` int(11) NOT NULL,
  PRIMARY KEY (`task_id`)
) ENGINE=InnoDB AUTO_INCREMENT=25 DEFAULT CHARSET=utf8mb4;

2. 存储过程查询结果

2.1 创建存储过程

创建一个简单的存储过程来查询数据

CREATE DEFINER=`root`@`%` PROCEDURE `p_sayn_job`()
BEGIN
	#Routine body goes here...
	DECLARE v_cnt INT;
	DECLARE v_job_id INT; 
	
	
	SELECT count( 1 ) INTO v_cnt 
	  FROM 	t_job j 
	 WHERE 	j.next_time < SYSDATE();
	 
	IF 	v_cnt > 0 THEN	
	
	 -- 插入数据
		INSERT INTO t_task ( start_time, end_time, STATUS, job_id )
			VALUES
	  (SYSDATE(), SYSDATE()+ 1, 1, v_job_id );			

		-- 更新数据
		UPDATE t_job j 
			 SET j.last_task = ( SELECT MAX( t.task_id ) FROM t_task t WHERE t.job_id = j.job_id ),
					 j.next_time = DATE_ADD( j.next_time, INTERVAL 1 DAY ) 
		 WHERE j.job_id = v_job_id;

	
	END IF;
	
END

2.2 添加for循序语句

DECLARE语句声明游标jobs

-- DECLARE语句声明游标
DECLARE jobs CURSOR FOR
(SELECT j.job_id FROM t_job j WHERE j.next_time > SYSDATE());	

DECLARE语句声明结束标识v_finished

-- 声明变量
DECLARE v_finished  int DEFAULT FALSE;
-- 结束标识
DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = TRUE;

OPEN语句打开游标

-- 	OPEN语句打开游标
OPEN  jobs ;

循环迭代jobs

-- 	循环迭代 jobs 
read_loop : LOOP

END LOOP read_loop;

使用FETCH语句检索光标指向的下一行，并将光标移动到结果集中的下一行。

-- 	使用FETCH语句检索光标指向的下一行，并将光标移动到结果集中的下一行。
FETCH jobs into v_job_id;

使用v_finished变量来检查列表是否有id来终止循环。

-- 	使用v_finished变量来检查列表是否有id来终止循环。
IF v_finished THEN
	LEAVE read_loop; 
END IF;

写入自己的处理业务SQl，然后CLOSE语句以停用游标并释放与其关联的内存。

-- CLOSE语句以停用游标并释放与其关联的内存
CLOSE jobs;

完整的存储过程，如下：

CREATE DEFINER=`root`@`%` PROCEDURE `p_sayn_job`()
BEGIN#Routine body goes here...
	DECLARE v_cnt INT;
	DECLARE v_finished  int DEFAULT FALSE;
	DECLARE v_job_id INT; 
	-- DECLARE语句声明游标
	DECLARE jobs CURSOR FOR
		  (SELECT j.job_id FROM t_job j WHERE j.next_time > SYSDATE());	
	-- 结束标识
	DECLARE CONTINUE HANDLER FOR NOT FOUND SET v_finished = TRUE;

	SELECT count( 1 ) INTO v_cnt 
	  FROM 	t_job j 
	 WHERE 	j.next_time < SYSDATE();
		
	IF 	v_cnt > 0 THEN
		
		-- 	OPEN语句打开游标
		OPEN  jobs ;
		-- 	循环迭代 jobs 
		read_loop : LOOP
				-- 	使用FETCH语句检索光标指向的下一行，并将光标移动到结果集中的下一行。
		   FETCH jobs into v_job_id;

			-- 	使用v_finished变量来检查列表是否有id来终止循环。
			IF v_finished THEN
				LEAVE read_loop; 
			END IF; 
			-- 处理业务SQl 就在这了
				INSERT INTO t_task ( start_time, end_time, STATUS, job_id )
			      VALUES
				 ( SYSDATE(), SYSDATE()+ 1, 1, v_job_id );			
			
			
			  UPDATE t_job j 
					 SET j.last_task = ( SELECT MAX( t.task_id ) FROM t_task t WHERE t.job_id = j.job_id ),
					   	 j.next_time = DATE_ADD( j.next_time, INTERVAL 1 DAY ) 
			 	 WHERE j.job_id = v_job_id;
			
			
		END LOOP read_loop;
		-- CLOSE语句以停用游标并释放与其关联的内存
		CLOSE jobs;

		
	END IF;
END

2.3 保存执行存储过程

CALL p_sayn_job();