mysql存储过程赋值

最新推荐文章于 2024-07-28 02:53:29 发布

原创最新推荐文章于 2024-07-28 02:53:29 发布 · 3.6k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql #存储 #date #null

MySql 专栏收录该内容

7 篇文章

订阅专栏

本文介绍了一个用于清理数据库中过期资源及其关联记录的存储过程。该过程通过遍历资源表并根据指定条件删除内容、访问记录、详细记录、评论及资源本身，同时还清除了相关的小图标。

IN expireDate VARCHAR(20),IN resType INT

//-----------------------------------------------------------------------------------

BEGIN
DECLARE a,b,icon_id INT;
DECLARE cur_1 CURSOR FOR SELECT id FROM `tbl_resource` WHERE discriminator="RC_CON" and robot_type=resType and add_date<=expireDate;/*robot_type 代替资源类型*/
DECLARE CONTINUE HANDLER FOR NOT FOUND

SET b = 1;
OPEN cur_1;
REPEAT
FETCH cur_1 INTO a;
SELECT smallIcon INTO icon_id FROM tbl_resource WHERE id=a; /*得到缩略图片的id*/

DELETE FROM `tbl_resource` WHERE parent_id=a;                /*删除这个content所含的资源*/
DELETE FROM `tbl_visitrecords` WHERE resource_id=a;        /*删除访问信息*/
DELETE FROM `tbl_detailrecord` WHERE resource_id=a;       /*删城祥细访问信息 */
DELETE FROM `tbl_comment` WHERE resource_id=a;           /*删城相关评论*/
DELETE FROM `tbl_resource` WHERE id=a;                          /*删城自己*/
IF icon_id >0 THEN
DELETE FROM `tbl_resource` WHERE id=icon_id;       /*删除图标*/
/*SELECT "删除了"+icon_id;*/
END IF;
UNTIL b = 1
END REPEAT;
CLOSE cur_1;

/*SELECT count(id) FROM `tbl_resource` WHERE discriminator="RC_CON" and robot_type=0 and add_date<=expireDate;*/
END

//相关语法

if Null is Null then
SELECT 23;
else
SELECT 45;
end IF;

//赋值

select @cnt := count(1) from tablenmae
和
select count(1) into @cnt from tablename

是等价的。