mysql存储过程 --游标的使用

delimiter $$$
 DROP PROCEDURE IF EXISTS e_test $$$
 CREATE PROCEDURE e_test()
 BEGIN
   --声明变量
   DECLARE done INT DEFAULT FALSE;
   DECLARE user_id INT(11);
   DECLARE user_money DECIMAL(11,2);
   DECLARE user_scores DECIMAL(11,2);
   -- 游标
   DECLARE cur CURSOR FOR SELECT userid FROM user u LEFT JOIN store s on u.userid = s.uid WHERE UNIX_TIMESTAMP(NOW()) - u.reg_date > 48*3600 AND s.fengmi_num < (SELECT tip FROM ysk_config WHERE name = 'aaa');
   -- 将结束标志绑定到游标
   DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;
   -- 打开游标
   OPEN cur;
   -- 遍历
   label1: LOOP
   -- 取值
   FETCH cur INTO user_id;
    IF done THEN
     LEAVE label1;
    END IF;
    -- 你自己想做的操作
    SET user_money = (SELECT cangku_num FROM ysk_store WHERE uid = user_id);
    SET user_scores = (SELECT fengmi_num FROM ysk_store WHERE uid = user_id);
    UPDATE store SET s.cangku_num = 0,s.fengmi_num = 0 WHERE uid = user_id;
    INSERT INTO usermoney_record(master_id,get_nums,get_type,now_nums) VALUES(user_id,user_money,43,0);
    INSERT INTO usermoney_record(master_id,get_nums,get_type,now_nums) VALUES(user_id,user_scores,44,0);
   END LOOP label1;
   CLOSE cur;
 END; $$$