插入数据
DROP PROCEDURE IF EXISTS iCursor;
-- 储存过程名
CREATE PROCEDURE iCursor()
BEGIN
-- 定义变量
DECLARE tempName VARCHAR(255) DEFAULT '';
DECLARE iCursor CURSOR FOR SELECT name FROM store;
-- 游标中的内容执行完后将done设置为1
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET tempName = NULL;
OPEN iCursor;
-- 执行循环
FETCH iCursor INTO tempName;
WHILE (tempName IS NOT NULL) DO
INSERT INTO st(value) VALUES(tempName);
FETCH iCursor INTO tempName;
END WHILE;
CLOSE iCursor;
END;
CALL iCursor()
DROP PROCEDURE IF EXISTS iCursor;
CREATE PROCEDURE iCursor()
BEGIN
DECLARE tempName VARCHAR(255) DEFAULT '';
DECLARE tempCode VARCHAR(255) DEFAULT '';
DECLARE iCursor CURSOR FOR SELECT sname,product_code FROM t_supply_product_info;
DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET tempName = NULL;
OPEN iCursor;
FETCH iCursor INTO tempName,tempCode;
WHILE (tempName IS NOT NULL) DO
INSERT INTO test.t_supplier_product_info(sname,product_code) VALUES(tempName,tempCode);
FETCH iCursor INTO tempName,tempCode;
END WHILE;
CLOSE iCursor;
END;
CALL iCursor()
-
BEGIN
--定义变量
declare testrangeid BIGINT;
declare versionid BIGINT;
declare done int;
--创建游标，并存储数据
declare cur_test CURSOR for
select id as testrangeid,version_id as versionid from tp_testrange;
--游标中的内容执行完后将done设置为1
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;
--打开游标
open cur_test;
--执行循环
posLoop:LOOP
--判断是否结束循环
IF done=1 THEN
LEAVE posLoop;
END IF;
--取游标中的值
FETCH cur_test into testrangeid,versionid;
--执行更新操作
update tp_data_execute set version_id=versionid where testrange_id = testrangeid;
END LOOP posLoop;
--释放游标
CLOSE cur_test;
END
