mysql 存储过程，临时表，游标

最新推荐文章于 2024-07-21 03:15:24 发布

aini_zlr2008

最新推荐文章于 2024-07-21 03:15:24 发布

阅读量123

点赞数

分类专栏： mysql 文章标签： mysql 存储过程临时表游标

本文链接：https://blog.csdn.net/aini_zlr2008/article/details/84010023

版权

mysql 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

CREATE DEFINER=`lifunet`@`%` PROCEDURE `proc_rank_article`(
in inter int
)
begin
declare temp_reply_article_id int;
declare temp_reply_total int default 0;
declare temp_article_good int default 0;
declare temp_article_sort int default 0;
declare done boolean;
--
declare tmp_article_cur cursor for select reply_article_id from tmp_article_table;
--
declare continue handler for not found set done=true;

drop temporary table if exists tmp_article_table;
drop temporary table if exists tmp_article_static_table;
create temporary table tmp_article_table(reply_article_id int);
create temporary table tmp_article_static_table(article_id int,good_click int,replyTotal int,article_sort int);
if(inter is not null) then
if(inter = 0) then
set inter = 7;
end if;
if(inter = 1) then
set inter = 360;
end if;
if(inter = 2) then
set inter = 30;
end if;
insert into tmp_article_table(reply_article_id) select reply_article_id from t_article where reply_article_id is not null and create_time > DATE_ADD(now(), INTERVAL -inter DAY) order by create_time desc limit 10000;
--
start transaction;
open tmp_article_cur;
tmp_article_cur_loop:loop
fetch tmp_article_cur into temp_reply_article_id;
if done then
leave tmp_article_cur_loop;
else
if(NOT EXISTS (select article_id from tmp_article_static_table where article_id = temp_reply_article_id )) then
--
select count(good_click) into temp_article_good from t_record where article_id=temp_reply_article_id and good_click is not null and create_time > DATE_ADD(now(), INTERVAL -inter DAY);
--
select count(reply_article_id) into temp_reply_total from tmp_article_table where reply_article_id=temp_reply_article_id;
-- X=
set temp_article_sort = temp_article_good + temp_reply_total;
insert into tmp_article_static_table (article_id,good_click,replyTotal,article_sort) values(temp_reply_article_id,temp_article_good,temp_reply_total,temp_article_sort);
end if;
end if;
end loop tmp_article_cur_loop;
close tmp_article_cur;
--
select * from tmp_article_static_table order by article_sort desc;
drop table tmp_article_table;
drop table tmp_article_static_table;
commit;
end if;
end;