| 今天写了第一个使用游标的mysql存储过程,下面是这个存储过程的内容：
delimITer $$
create procedure sp_poster()
begin
declare id int; --声明变量
declare cnt int; --声明变量
declare cur_channel_id cursor for select id from channel; --声明游标
declare EXIT HANDLER FOR NOT FOUND CLOSE cur_channel_id; --捕获游标NOT FOUND的条件，循环结束，并关闭游标
select count(*) into cnt from channel;
if cnt <> 0 then
open cur_channel_id; --打开游标
repeat
fetch cur_channel_id into id; --移动游标
insert into POSTER_MONTH(id, gmt_create, gmt_modified, title, short_tITle, user_id, user_nick, tags,channel_id,weight,cover_pic_path,hITs)
select id, gmt_create, gmt_modified, title, short_title, user_id, user_nick, tags,channel_id,weight,cover_pic_path,hITs
from poster where gmt_create >= date_add(now(),interval -30 day)
and channel_id = id
order by hits desc limIT 10;
insert into POSTER_WEEK(id, gmt_create, gmt_modified, title, short_title, user_id, user_nick, tags,channel_id,weight,cover_pic_path,hITs)
select id, gmt_create, gmt_modified, title, short_title, user_id, user_nick, tags,channel_id,weight,cover_pic_path,hITs
from poster where gmt_create >= date_add(now(),interval -7 day)
and channel_id = id
order by hits desc limIT 10;
commIT;
until 0 end repeat;
close cur_channel_id;
insert into POSTER_MONTH(id, gmt_create, gmt_modified, title, short_title, user_id, user_nick, tags,channel_id,weight,cover_pic_path,hITs)
select id, gmt_create, gmt_modified, title, short_title, user_id, user_nick, tags,channel_id,weight,cover_pic_path,hITs
from poster where gmt_create >= date_add(now(),interval -30 day)
order by hits desc limIT 10;
commIT;
insert into POSTER_WEEK(id, gmt_create, gmt_modified, title, short_title, user_id, user_nick, tags,channel_id,weight,cover_pic_path,hITs)
select id, gmt_create, gmt_modified, title, short_title, user_id, user_nick, tags,channel_id,weight,cover_pic_path,hITs
from poster where gmt_create >= date_add(now(),interval -7 day)
order by hits desc limIT 10;
commIT;
end if;
end $$ | 存储过程本身与oracle的存储过程并没有太大的差异，写起来也比较顺手的。其中一点要注意：SQL语句，对时间的处理，mysql与oracle就有很大的不同。在oracle里，时间可以直接相减，比如：
14:12:38 SQL> select sysdate - 5 from dual;
SYSDATE-5
-------------------
2008-03-29 14:12:49
1 row selected. |
但是在mysql里面，要使用date_add函数去完成(具体语法，在网上google一下)
mysql> select date_add(now(),interval -5 day);
+---------------------------------+
| date_add(now(),interval -5 day) |
+---------------------------------+
| 2008-03-29 14:12:49 |
+---------------------------------+
1 row in set (0.00 sec) |
上面的时间函数使用起来复杂一点，但只要能完成这个功能就可以了! |
