在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();