mysql 游标使用范例！！

DELIMITER $$

DROP PROCEDURE IF EXISTS `ucenter`.`tttt`$$

CREATE DEFINER=`root`@`localhost` PROCEDURE `tttt`()

BEGIN

-- 定义本地变量

    DECLARE o,z int;  

    declare no_more_departments int;

   -- 定义游标

   DECLARE ordernumbers CURSOR

   FOR

   select ids from t ;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET no_more_departments=1;

SET no_more_departments=0;

 

   -- 打开游标

   OPEN ordernumbers;

set z=0;

FETCH ordernumbers INTO o; -- 取一行到 o

     WHILE no_more_departments!=1 DO        

      -- 开始处理一行

                -- select o;

set z=z+1;

insert into uc_ego_members_vip (id,`user`, addpoint,addtime) select id,`user`,'5000' addpoint ,now() from uc_ego_members where id =o;

insert into uc_ego_point (`user`, buy, point, `time`,`type`,bigClassId,bigClassName,smallClassId,smallClassName,brandId,brandName,price,productId,productModel,productName) select `user`,'VIP升级加分','5000' addpoint ,now() `time`,1 `type`,0,'',0,'',0,'',0,0,'','' from uc_ego_members where id =o;

UPDATE uc_ego_members set EgoPoint=(EgoPoint+5000) where id =o;

    FETCH ordernumbers INTO o; -- 取一行到 o

     END WHILE;

select concat('共处理：',z,'行');-- 记数器

select no_more_departments;-- 条件值

   -- 关闭游标

   CLOSE ordernumbers;

 

    END$$

DELIMITER ;