MySql 存储过程游标使用示例 [转载]

DROP PROCEDURE IF exists pro_removeBillRemindByUserId;
create procedure pro_removeBillRemindByUserId(userId int)
begin
    declare ts varchar(500);
    declare cnt int default 0;
    declare f_ID,b  int;
    DECLARE cur_1 CURSOR FOR select  FID from t_bill  where FUserId=userId;
DECLARE CONTINUE HANDLER FOR NOT FOUND
SET b = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO f_ID;
    delete from t_bill_remind where FBillID=f_ID;
UNTIL b = 1
END REPEAT;
    close cur_1;

end;

//调用存储过程
call pro_removeBillRemindByUserId(678);

---

 

本示例通过 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/5344675