【MySql】MySql存储，游标，循环的简单使用

MySql存储，游标，循环

CREATE PROCEDURE `copy_group`(IN v_sourcegroupid int,IN v_destgroupid int,IN v_projectid int)

BEGIN

      --声明变量

      declare v_oldtestcaseid int;

      declare v_testcaseid int;

      declare v_stepid int;

      declare v_id int;

      declare v_caseid  类型;

      declare v_testcasename 类型;

      declare v_description 类型;

      declare v_caselevel  类型;

      declare v_runner  类型;

      declare v_classname 类型;

      declare stop int default 0;--终止标记  

      --声明游标 

      declare cur cursor for (select caseid,testcasename,description,caselevel,runner,classname from tc_case  
            where groupid=v_sourcegroupid);

      -- 声明游标的异常处理，设置一个终止标记 

      declare CONTINUE HANDLER FOR SQLSTATE '02000' SET stop=1; 

      --打开游标 

      OPEN cur;

      --获取数据到变量

      FETCH cur INTO v_caseid,v_testcasename,v_description,v_caselevel,v_runner,v_classname;

      --判断是否终止

      WHILE stop <> 1 DO

              insert into tc_case(testcasename,description,caselevel,runner,classname,groupid,create_time,update_time)

                     values(testcase.testcasename,testcase.description,testcase.caselevel,testcase.runner,testcase.classname,v_destgroupid,now(),now());

              v_testcaseid = mysql_insert_id();

              v_oldtestcaseid = testcase.caseid;

              --获取下一行数据。

              FETCH cur INTO v_caseid,v_testcasename,v_description,v_caselevel,v_runner,v_classname;

        END WHILE;

        --关闭游标

        CLOSE cur;             

        insert into tc_group(groupname,projectid,runner,description,create_time,update_time,parentid)

         select groupname,v_projectid,runner,description,now(),now(),v_destgroupid from tc_group

            where groupid = v_sourcegroupid;

END;