mysql 结果集问题

zzyoucan 2015-07-14 11:27:35



BEGIN

	#Routine body goes here...

	DECLARE int_unreaded_mail_del_time INT DEFAULT 0;

	DECLARE int_readed_mail_del_time INT DEFAULT 0;



	DECLARE int_send_time INT DEFAULT 0;

	DECLARE int_staus INT DEFAULT 0;

	DECLARE int_mailid INT DEFAULT 0;



	DECLARE _DONE INT DEFAULT 0;

	DECLARE cur CURSOR FOR SELECT  `mail_id`, UNIX_TIMESTAMP(`send_time`), `status` FROM `user_mail` WHERE `user_id` = in_user_id;

	DECLARE CONTINUE HANDLER FOR SQLSTATE '02000' SET _DONE = 1;

	

	SELECT `value` INTO int_unreaded_mail_del_time FROM `sys_setting` WHERE `key` = 'Mail_UnReaded_Mail_Valid_Time';

	SELECT `value` INTO int_readed_mail_del_time FROM `sys_setting` WHERE `key` = 'Mail_Readed_Mail_Valid_Time';



	#遍历邮件将无效邮件删除

	OPEN cur;

	REPEAT 

		FETCH	cur INTO int_mailid, int_send_time, int_staus;



		IF NOT _DONE THEN

			

			IF int_staus = 1 THEN#已读

				IF int_send_time + int_readed_mail_del_time < UNIX_TIMESTAMP(Now()) THEN

					#给玩家添加道具

					#CALL _give_out_of_date_mail_attach_to_user(int_mailid);

					DELETE FROM `user_mail` WHERE `mail_id` = int_mailid;

					DELETE FROM `user_mail_attach` WHERE `mail_id` = int_mailid;

				END IF;

			ELSEIF int_staus = 2 THEN#未读

				IF int_send_time + int_unreaded_mail_del_time < UNIX_TIMESTAMP(Now()) THEN

					#CALL _give_out_of_date_mail_attach_to_user(int_mailid);

					DELETE FROM `user_mail` WHERE `mail_id` = int_mailid;

					DELETE FROM `user_mail_attach` WHERE `mail_id` = int_mailid;

				END IF;

			END IF;	

		END IF;

	UNTIL _DONE END REPEAT;

	CLOSE cur;

	

	#获取玩家邮件信息

	SELECT `mail_id`, UNIX_TIMESTAMP(`send_time`)  as  `send_time`,  `content`, `status`, `type` FROM user_mail WHERE `user_id` = in_user_id;

	SELECT `mail_id`, `id`, `element_id`, `type`, `num` FROM `user_mail_attach` WHERE `user_id` = in_user_id;



END