mysql 游标子查询_155.mysql 游标

-- 定义变量

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;

-- 释放游标