本示例通过 while...end while 循环控制游标来实现插入表记录。
DROP PROCEDURE IF exists pro_initCategoryForTradingEntity;
create procedure pro_initCategoryForTradingEntity(tradingEntityId int)
begin
declare f_parent,entityId int;
declare b int default 0; /*是否达到记录的末尾控制变量*/
declare f_name varchar(100);
DECLARE cur_1 CURSOR FOR select FName,FParent,tradingEntityId from t_category_tag;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET b = 1;
OPEN cur_1;
FETCH cur_1 INTO f_name, f_parent, entityId; /*获取第一条记录*/
while b<>1 do
insert into t_category(FName,FParent,FTradingEntity) values(f_name, f_parent, entityId);
FETCH cur_1 INTO f_name, f_parent, entityId; /*取下一条记录*/
end while;
close cur_1;
end;
call pro_initCategoryForTradingEntity(2);
另外循环也可以用repeat...end repeat 来循环控制,以下是我最初所做的示例代码,但结果是插入的实际数据总数多出一条,在此列出作为一个反面示例代码。当然最后用的是上面while...end while 方案。
DROP PROCEDURE IF exists pro_initCategoryForTradingEntity;
create procedure pro_initCategoryForTradingEntity(tradingEntityId int)
begin
declare f_parent,entityId,b int;
declare f_name varchar(100);
DECLARE cur_1 CURSOR FOR select FName,FParent,tradingEntityId from t_category_tag;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO f_name, f_parent, entityId;
insert into t_category(FName,FParent,FTradingEntity) values(f_name, f_parent, entityId);
UNTIL b = 1
END REPEAT;
close cur_1;
end;
call pro_initCategoryForTradingEntity(2);
因为用到第二种方法,最后一条数据多执行一次,才发现这个帖子,
但不知道为什么第二种方法多执行一次,那位大大能帮忙解释写,是不是那里写的有问题。
转载:http://blog.csdn.net/jeamking/article/details/5348150