存储过程

有这样的一个需求：

获取当前时装已有时装id为5,6,7,8的玩家，并获取相应时装剩余的时间，剩余时间为0-1天的按1天时装道具价格补偿，剩余时间为1-3天的按3天时装道具价格补偿，剩余时间为3-7天的按7天时装道具价格补偿，剩余时间为7-30天的按30天时装道具价格补偿，超过30天按永久时装道具价格补偿

·每一件时装单独邮件补偿

补偿金额如下：

1天价格：0.5两金子

3天价格：1.5两金子

7天价格：3.5两金子

30天价格：5两金子

永久价格：15两金子

·补偿后删除此玩家对该时装的拥有权 

DELIMITER //
DROP PROCEDURE IF EXISTS `clear_fashion`;
CREATE PROCEDURE `clear_fashion` ()
BEGIN

  DECLARE done INT DEFAULT 0;
  DECLARE c_player_id INT;
  DECLARE c_fashion_id INT;
  DECLARE c_valid_time INT;
  DECLARE pay_money INT;
  DECLARE cur CURSOR FOR select player_id, fashion_id, valid_time from tb_player_fashion where fashion_id in (5,6,7,8);/*创建游标*/
  DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;

  CREATE TABLE IF NOT EXISTS `tb_tmp_fashion` ( /*临时表暂存数据，方便后续查询*/
    `player_id` int(11) NOT NULL,
    `fashion_id` int(11) NOT NULL,
    `valid_time` int(11) NOT NULL DEFAULT 0,
    `created_date` datetime DEFAULT NULL,
    PRIMARY KEY(`player_id`, `fashion_id`)
  );
  truncate table `tb_tmp_fashion`;

  OPEN cur;
  end_loop:LOOP/*循环*/
    FETCH cur INTO c_player_id, c_fashion_id, c_valid_time;
    IF done = 1 THEN
      LEAVE end_loop;
    END IF;

    INSERT INTO `tb_tmp_fashion` values(c_player_id,c_fashion_id,c_valid_time, NOW());
    SET pay_money = 0;
    IF c_valid_time = 0 THEN
      SET pay_money = 15000;
    ELSEIF c_valid_time > UNIX_TIMESTAMP() THEN
      IF c_valid_time - UNIX_TIMESTAMP() < 86400 THEN
        SET pay_money = 500;
      ELSEIF c_valid_time - UNIX_TIMESTAMP() < 259200 THEN
        SET pay_money = 1500;
      ELSEIF c_valid_time - UNIX_TIMESTAMP() < 604800 THEN
        SET pay_money = 3500;
      ELSE
        SET pay_money = 5000;
      END IF;
    END IF;

    INSERT INTO `tb_player_mail` (`sender_id`,`sender_name`,`receiver_id`,`receiver_name`,`title`,`content`,`type`,`state`,`append_gold`,`append_blackgold`,`append_silver`,`acquire_date`,`append_item1_id`,`append_item2_id`,`append_item3_id`,`status`,`created_date`,`last_modified_date`) values(0, '系统通知',c_player_id, '', '时装调整','亲爱的玩家，由于系统清除了一部分时装，现对已拥有该时装的玩家进行补偿，请及时领取附件。', 0,0,pay_money,0,0,NOW(), 0,0,0,1,NOW(),NOW());

    DELETE from `tb_player_fashion` where player_id = c_player_id and fashion_id = c_fashion_id;


  END LOOP end_loop;
  CLOSE cur;

END
//
DELIMITER ;

创建结束后，执行：

call clear_fashion();

遇到问题：

1305 - PROCEDURE clear_fashion does not exist

没有权限，执行以下语句，授予权限：

GRANT EXECUTE ON PROCEDURE `test2.5`.`clear_fashion` TO 'root'@'localhost';