[mysql]mysql存储过程的

最近公司的项目里，有个要统计每个分类的商品的数量的需求，本来用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;