[mysql]mysql存储过程的

gggggds

　　最近公司的项目里，有个要统计每个分类的商品的数量的需求，本来用php连接mysql传递sql去统计的，后面发现老是出问题，最后换成了使用mysql的存储过程去实现，以前听过存储过程，但是从来没有动手去写过，所以今天学习了一下，统计品牌代码如下：
　　

begin
declare num int default 0;
declare cid int default 0;
declare tip int default 0;
declare cursor_goods cursor for select count(*) as c,category_id as i from goods group by category_id;
declare continue handler for SQLSTATE '02000' set tip=1;
#reset
update brand set number=0;
#update
open cursor_goods;
repeat
fetch cursor_goods into num,cid;
update brand set number=num where id=cid;
until tip end repeat;
close cursor_goods;
end
begin
declare num int default 0;
declare tip int default 0;
declare cid int default 0;
declare lii int default 0;
declare lee int default 0;
declare len int default 0;
declare sid varchar(250) default '';
declare tmp varchar(250) default '';
declare yii varchar(250) default '';
declare cursor_goods cursor for select count(*) as c,category_id as i from goods group by category_id;
declare continue handler for sqlstate '02000' set tip=1;
#reset
update category set number=0;
#update
open cursor_goods;
repeat
set sid='';
set num=0;
fetch cursor_goods into num,cid;
select sorder into sid from category where id=cid limit 1;
if(sid!='') then
set len=length(sid);
set sid=substr(sid,2);
set lee=1;
set yii = sid;
while lee<len do
set tmp = substr(sid,lee,1);
if(tmp=',') then
set lii = substring_index(yii,',',1);
update category set number=number+num where id = lii;
set yii = substr(sid,lee+1);
end if;
set lee=lee+1;
end while;
end if;
until tip end repeat;
close cursor_goods;
end
　　3.预处理，因为存储过程不支持in参数的传递执行，可以用预处理解决类似的代码段2的出现，不用循环字符串,传递的参数的时候可以是这样的 - ‘1，2，3，4’（加上单引号即可）
　　

drop procedure if exists `proc_test_prepare`
create definer = `root`@`%` procedure `proc_test_prepare`(c text)
begin
#test
declare pre_select varchar(250);
#select
set @testsql = concat('select * from product_category where id in(', c, ')');
prepare pre_select from @testsql;
execute pre_select;
end;
　　4.哈，再加上定时器就能够定时执行了统计任务了
　　

CREATE EVENT `event_count_brand` ON SCHEDULE EVERY 2 HOUR STARTS '2013-01-03 11:40:10' ON COMPLETION NOT PRESERVE ENABLE DO call proc_count_brand
　　5.可以加上想要的定时执行效果
　　

#从现在开始每隔九天定时执行  
CREATE  EVENT EVENT1  
ON SCHEDULE EVERY 9 DAY STARTS NOW()  
ON COMPLETION PRESERVE ENABLE  
DO  
BEGIN  
CALL TOTAL();  
END  

#每个月的一号凌晨1 点执行  
CREATE  EVENT EVENT2     
ON SCHEDULE EVERY 1 MONTH STARTS DATE_ADD(DATE_ADD(DATE_SUB(CURDATE(),INTERVAL DAY(CURDATE())-1 DAY), INTERVAL 1
MONTH),INTERVAL 1 HOUR)  
ON COMPLETION PRESERVE ENABLE  
DO  
BEGIN  
CALL STAT();  
END  

#每个季度一号的凌晨2点执行  
CREATE  EVENT TOTAL_SEASON_EVENT  
ON SCHEDULE EVERY 1 QUARTER STARTS DATE_ADD(DATE_ADD(DATE( CONCAT(YEAR(CURDATE()),'-',ELT(QUARTER(CURDATE
()),1,4,7,10),'-',1)),INTERVAL 1 QUARTER),INTERVAL 2 HOUR)  
ON COMPLETION PRESERVE ENABLE  
DO  
BEGIN  
CALL SEASON_STAT();  
END  


#每年1月1号凌晨四点执行  
CREATE  EVENT TOTAL_YEAR_EVENT  
ON SCHEDULE EVERY 1 YEAR STARTS DATE_ADD(DATE(CONCAT(YEAR(CURDATE()) + 1,'-',1,'-',1)),INTERVAL 4 HOUR)  
ON COMPLETION PRESERVE ENABLE  
DO  
BEGIN  
CALL YEAR_STAT();  

　　5.定时器的开启和关闭
　　

#开启DROP PROCEDURE IF EXISTS `proc_count_attribure`;
alter
event event_count_brand on completion preserve enable;
#关闭
alter
event event_count_brand on completion preserve disable;
　　6.mysql函数
　　

DROP FUNCTION IF EXISTS `proc_count_attribure`;
CREATE DEFINER = `root`@`%` FUNCTION `proc_count_attribure`(sid int)
RETURNS varchar(250) CHARSET utf8
begin
#return count mark attrite
declare iid int default 0;
declare iname varchar(250) default '';
declare iscore int default 0;
declare iflag int default 0;
declare istring varchar(250) default '';
declare cursor_attribute cursor for select item_id,item_name,sum(score) as item_score from shop_assess_attribute where shop_id=sid group by item_id;
declare continue handler for sqlstate '02000' set iflag=1;
open cursor_attribute;
repeat
fetch cursor_attribute into iid,iname,iscore;
set istring = concat(istring,iid,',',iname,',',iscore,'|');
until iflag end repeat;  
close cursor_attribute;
return substr(istring,1,char_length(istring)-1);
end;