之前按照網上的cursor控制例子,發現mysql的cursor跑完后,還會跑一邊,出現重復訪問數據,在dongdongsdo0310建議下使用了loop循環,并在發現游標結束時就關閉cursor,并結束循環發現問題解決了:
下面的procedure需求是:
1、在project_id_in (專案)下 phase_name(階段) 下的stage_name (父任務)下 建立新任務
2、主要涉及到子任務的生成:
a.拆分task_type任務類型(多個任務類型之間以**連接在一起)
b、拆分任務次數(多個任務次數之間以××連在一起)
c.根據查詢出的tasktypename和任務次數拼接(任務次數是幾對應幾個任務,任務名稱中的序列從1開始)
DELIMITER //
CREATE PROCEDURE create_select_subtask (IN project_id_in INT, IN phase_name VARCHAR(50), IN stage_name VARCHAR(50), IN task_type VARCHAR(50), IN task_times VARCHAR(50))
BEGIN
BEGIN
DECLARE deptTotal INT DEFAULT 0;
DECLARE tasktypecount INT;
DECLARE i INT DEFAULT 0;
DECLARE splitResult VARCHAR(36);
DECLARE splitResult2 VARCHAR(36);
DECLARE typeCount INT;
DECLARE v_parenttask INT;
DECLARE v_parentphase INT;
DECLARE v_taskgroup CHAR(1) DEFAULT '1';
DECLARE v_level INT DEFAULT 1;
DECLARE v_subtask INT;
SELECT mt.id,mt.parent_phase,mt.subtask INTO v_parenttask,v_parentphase,v_subtask
FROM midtasks mt,midphases mp
WHERE mt.name=CONCAT(stage_name,' Stage')
AND mt.project=project_id_in
AND mt.parent_phase=mp.order_num AND mp.name=phase_name;
SET task_type=replace(task_type,'**',',');
SET task_times=replace(task_times,'**',',');
SET tasktypecount = (length(task_type) - length(replace(task_type,',','')));
#SELECT tasktypecount;
DROP TABLE IF EXISTS temp_type;
CREATE TEMPORARY TABLE temp_type(tasktype VARCHAR(36) NOT NULL,tasktime VARCHAR(36) NOT NULL) DEFAULT CHARSET=utf8;
WHILE i<tasktypecount DO
SET i = i+1;
SET splitResult = reverse(substring_index(reverse(substring_index(task_type,',',i)),',',1));
SET splitResult2 = reverse(substring_index(reverse(substring_index(task_times,',',i)),',',1));
SELECT COUNT(1) INTO typeCount FROM temp_type WHERE tasktype=splitResult and tasktime=splitResult2;
IF typeCount=0 THEN
INSERT INTO temp_type(tasktype,tasktime) VALUES(splitResult,splitResult2);
END IF;
END WHILE;
BEGIN
DECLARE no_more_rows BOOLEAN;
DECLARE v_tasktype VARCHAR(36);
DECLARE v_tasktime VARCHAR(36);
DECLARE v_taskType_name VARCHAR(155);
DECLARE j INT DEFAULT 1;
DECLARE v_cursor CURSOR FOR
SELECT tasktype,tasktime FROM temp_type;
DECLARE continue handler for not found set no_more_rows = true;
OPEN v_cursor;
the_loop_new : LOOP
FETCH v_cursor INTO v_tasktype,v_tasktime;
IF no_more_rows = true THEN
CLOSE v_cursor;
LEAVE the_loop_new;
END IF;
set j = 1;
while( j<=v_tasktime) DO
SELECT taskType_name INTO v_taskType_name
FROM p_bins_tasktype
WHERE taskType_id=v_tasktype;
SET v_taskType_name=CONCAT(v_taskType_name,'_',j);
INSERT INTO midtasks(name,project,parent_phase,created,parent_task,task_group,level)
VALUES(v_taskType_name,project_id_in,v_parentphase,sysdate(),v_parenttask,v_taskgroup,v_level);
UPDATE midtasks SET task_group='0' and subtask=v_subtask+1 WHERE id=v_parenttask;
SET j=j+1;
END while;
END LOOP the_loop_new;
END;
END;
END;//