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 : LOOPEND LOOP read_loop;

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

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

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

-- 使用v_finished变量来检查列表是否有id来终止循环。IF v_finished THENLEAVE 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 THENLEAVE 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();

总结

以上为个人经验，希望能给大家一个参考，也希望大家多多支持本站。

您可能感兴趣的文章:

• Mysql Error 1826:Duplicate foreign key constraint错误问题及解决
• 解决MySQL导入SQL时报错1067–Invalid default value for ‘ ’问题
• MySQL强制索引中USE/FORCE INDEX用法与避坑
• mysql使用 performance_schema 进行性能监控
• MySQL中的系统库(sys系统库、information_schema)调优方法
• MYSQL中information_schema的使用