-- 定义变量
DECLARE jobId varchar(500); -- id
DECLARE jobName varchar(500); -- id
DECLARE jobDepName varchar(500); -- 栏目
DECLARE jobDesc varchar(500); -- 创建时间
DECLARE flagIndex varchar(500); -- 序号
DECLARE done INT; -- 必须要
-- 创建游标,并存储数据
DECLARE cursor_test CURSOR FOR
select id AS jobId,job_name as jobName,job_belongtodep_name as jobDepName,job_desc as jobDesc from t_job;
-- 游标中的内容执行完后将done设置为1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
-- 打开游标
OPEN cursor_test;
-- 执行循环
posLoop:LOOP
-- 判断是否结束循环
IF done=1 THEN
LEAVE posLoop;
END IF;
-- 取游标中的值
FETCH cursor_test INTO jobId,jobName,jobDepName,jobDesc;
-- 进行判断的条件准备
select `index` into flagIndex from t_job t1 where t1.id= jobId;
-- 执行插入操作
IF(flagIndex<10) THEN -- 执行update操作
-- 标记 优秀
insert into t_job_cursor_test (indexid,id,name,depname,descs,inserttime,flag) values (flagIndex,jobId,jobName,jobDepName,jobDesc,SYSDATE(),'优秀');
else -- 执行insert操作
-- 标记 一般
insert into t_job_cursor_test (indexid,id,name,depname,descs,inserttime,flag) values (flagIndex,jobId,jobName,jobDepName,jobDesc,SYSDATE(),'一般');
END IF;
END LOOP posLoop;
-- 释放游标