mysql procedure游標控制的一個例子

之前按照網上的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;//