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;

【MySql】MySql儲存，遊標，迴圈的簡單使用

相關文章