Mysql存储过程

自己学着写的，除了调试比较麻烦，没碰到啥问题。是禅道的数据库操作，用来按迭代的主任务类别为其他的情况下自动生成相关子任务。

CREATE DEFINER=`zjm`@`%` PROCEDURE `procedure_inserttask`(IN executionID INT)
BEGIN
	-- 遍历需更新数据
	DECLARE flag int DEFAULT 0;
	DECLARE storynow int;
	DECLARE nConut int;
	#设定参数日期，预定设计2天内，开发3天内，联调1天，测试2天，根据自己需要调整，
	DECLARE nowDate datetime;
	DECLARE nowDateTime datetime;
	DECLARE vardesigndead date;
	DECLARE vardevelead date;
	DECLARE vartrandead date;
	DECLARE vartestdead date;
	DECLARE varopenedDate date;
	DECLARE estStarted date;
	DECLARE nowdateformat date;
	DECLARE tempID int; #id
	DECLARE tempProject int;#Project
	DECLARE tempModule int;#module
	DECLARE temptype VARCHAR(20);
	DECLARE curStory	CURSOR for (SELECT id, project, module, story,type,count(*) from zt_task WHERE execution=executionID and	deleted = '0'  GROUP BY story HAVING count(*)=1);
	DECLARE CONTINUE HANDLER for not FOUND set flag =1;
	#查不到数据时跳出循环
  open curStory;

		Fetch curStory into tempID,tempProject,tempModule,storynow,temptype,nConut;
    WHILE flag <>1 DO -- 设置循环条件
		if temptype= 'misc' then
			set nowDate= now();
	set nowdateformat=(select DATE_FORMAT(nowDate, '%Y-%m-%d'));
	set nowDateTime=(select DATE_FORMAT(nowDate, '%Y-%m-%d %H:%i:%S'));
	set vardesigndead=(select DATE_FORMAT(DATE_ADD(nowDate, INTERVAL 2 DAY), '%Y-%m-%d'));
	set vardevelead=(select DATE_FORMAT(DATE_ADD(nowDate, INTERVAL 5 DAY), '%Y-%m-%d'));
	set vartrandead=(select DATE_FORMAT(DATE_ADD(nowDate, INTERVAL 6 DAY), '%Y-%m-%d'));
	set vartestdead=(select DATE_FORMAT(DATE_ADD(nowDate, INTERVAL 8 DAY), '%Y-%m-%d'));
	
				-- 根据需求修改值
        INSERT INTO `zt_task`(`project`, `parent`, `execution`, `module`, `design`, `story`, `name`, `type`, `pri`, `deadline`, `version`, `openedBy`, `openedDate`, `assignedTo`, `estStarted`) 
				VALUES ( tempProject, tempID, executionID, tempModule, 0, storynow, '设计', 'design', 3, vardesigndead, 1, 'admin', nowDateTime, 'chenj', nowdateformat);
				INSERT INTO `zt_task`(`project`, `parent`, `execution`, `module`, `design`, `story`, `name`, `type`, `pri`, `deadline`, `version`, `openedBy`, `openedDate`, `assignedTo`, `estStarted`) 
				VALUES (tempProject, tempID, executionID, tempModule, 0, storynow,  '前端开发', 'devel', 3, vardevelead, 1, 'admin', nowDateTime, 'yangxq', nowdateformat);
				INSERT INTO `zt_task`(`project`, `parent`, `execution`, `module`, `design`, `story`, `name`, `type`, `pri`, `deadline`, `version`, `openedBy`, `openedDate`, `assignedTo`, `estStarted`) 
				VALUES (tempProject, tempID, executionID, tempModule, 0, storynow,  '后端开发', 'devel', 3, vardevelead, 1, 'admin', nowDateTime, 'liuwm', nowdateformat);
				INSERT INTO `zt_task`(`project`, `parent`, `execution`, `module`, `design`, `story`, `name`, `type`, `pri`, `deadline`, `version`, `openedBy`, `openedDate`, `assignedTo`, `estStarted`) 
				VALUES (tempProject, tempID, executionID, tempModule, 0, storynow,  '联调', 'devel', 3, vartrandead, 1, 'admin', nowDateTime, 'liuwm', nowdateformat);
				INSERT INTO `zt_task`(`project`, `parent`, `execution`, `module`, `design`, `story`, `name`, `type`, `pri`, `deadline`, `version`, `openedBy`, `openedDate`, `assignedTo`, `estStarted`) 
				VALUES (tempProject, tempID, executionID, tempModule, 0, storynow,  '测试', 'test', 3, vartestdead, 1, 'admin', nowDateTime, 'huly', nowdateformat);
				update zt_task SET parent='-1' WHERE id=tempID;
		END if;
   Fetch curStory into tempID,tempProject,tempModule,storynow,temptype,nConut;
    END WHILE;
  CLOSE curStory;
END