MYSQL每日一用：存储过程

 

今天要做一个统计报表，想到使用存储过程来做。

 

mysql的存储过程以前没有用过，这次想试试，在编写的过程中遇到了不少问题：

 

首先，看了官方的文档 ，不过对于存储过程，还是要使用才知道。文档是死的，人是活的。

 

1.怎么创建存储过程，自然也是使用一定语法，然后运行，如果成功，会有一个对应的文

 

件产生，并可以通过 call 来调用。

 

2.设定变量要注意，不是全局变量（@）时，要先定义（DECLARE year_months VARCHAR(10)）

 

3.设置变量时的语法，在变量前面加 SET 关键字，不然报错

 

4.直接执行SQL,是可以的，但是如果我们的SQL是动态的，比如我们表名是通过参数传进来的。

 

这时间要另外一种方式了，要将sql，使用CONCAT(,,,),来讲sql连接起来。还有就是字符串拼接时，

 

最好使用CONCAT(见下面的)函数，不然会有问题。

 

下面是我写的一个实例：

 

DROP   PROCEDURE  IF   EXISTS   `crm_sms_stat`;

CREATE DEFINER=`boss`@`%` PROCEDURE `crm_sms_stat`(IN years varchar(10),IN m varchar(10),IN last_months varchar(10))
BEGIN
     DECLARE year_months VARCHAR(10);
     DECLARE year_m VARCHAR(10);
     DECLARE mt_sms_yearmonth VARCHAR(20);
     DECLARE r_year_month VARCHAR(20);
     DECLARE last_r_year_month VARCHAR(20);
    
    
     SET year_months = CONCAT(years,m);
     SET year_m = CONCAT(years,'-',m);
     SET mt_sms_yearmonth = CONCAT("mt_sms_",year_months);
     SET r_year_month = CONCAT(years,"_",m);
    
     IF m = "01" THEN
        SET years = years - 1;
        SET last_months = "12";
     END IF;
     SET last_r_year_month = CONCAT(years,"_",last_months);
    
     IF years = "2008" THEN
        SET r_year_month = CONCAT("R",r_year_month);
        SET last_r_year_month = CONCAT("R",last_r_year_month);
     END IF;
    
    
     /*---------------------------------------------------------------- */
     SET @insertStat = CONCAT(' INSERT INTO crm_sms_stat(months,user_id,username,agentid) ',
                              ' select "',year_months,'",u.user_id,u.username,u.agentid from ',mt_sms_yearmonth,
                              ' mt inner join users u on mt.user_id=u.user_id group by mt.user_id ');
     PREPARE insertS FROM @insertStat;
     EXECUTE insertS;
    
    
     SET @tempupdateRemain = CONCAT(' UPDATE crm_monthRemain cm,crm_sms_stat css SET css.last_remain = ifnull(',
                                     last_r_year_month,',0),css.this_remain = ifnull(', r_year_month,',0) ',
                                     ' WHERE cm.id = css.user_id and css.months = "',year_months,'" ');
     PREPARE remain FROM @tempupdateRemain;
     EXECUTE remain;
   
     -- 更新每个月用户所冲条数
     SET @tempadd = CONCAT(' UPDATE crm_sms_stat css inner join (select name,sum(total_count) addNum from boss_addmoney  ',
                           ' where left(add_date,7)= "',year_m,'" and total_count >=0 and name not like "%=%" and name not like "%:%" group by name) ',
                           ' x on css.username= x.name and css.months = "',year_months ,'" ',
                           ' SET css.add_num = ifnull(x.addNum,0) ');
     PREPARE addnum FROM @tempadd;
     EXECUTE addnum;
            
                  
            
       
     IF years = "08" THEN
        -- 更新通道发送数（200901之前是不分卡发和通道的）
        SET @tempChannel08 = CONCAT('UPDATE crm_sms_stat css inner join (select mt.user_id,',
                                       ' sum(length(replace(mt.dest_mobile,";","")))/11  chennel_num',
                                       ' from ',mt_sms_yearmonth,' mt ',
                                       ' group by mt.user_id) x on css.user_id = x.user_id and css.months = "',year_months,'"',
                                       ' set css.channel_num = ifnull(x.chennel_num,0) ');
       
        PREPARE channel08 FROM @tempChannel08;
        EXECUTE channel08; 
     ELSE
         -- channel send number
         SET @tempChannel = CONCAT('UPDATE crm_sms_stat css inner join (select mt.user_id,',
                                       ' sum(length(replace(mt.dest_mobile,";","")))/11  chennel_num',
                                       ' from ',mt_sms_yearmonth,' mt WHERE mt.channel_id != 312 ',
                                       ' group by mt.user_id) x on css.user_id = x.user_id and css.months = "',year_months,'"',
                                       ' set css.channel_num = ifnull(x.chennel_num,0) ');
       
         PREPARE channel FROM @tempChannel;
         EXECUTE channel; 
         -- card send number    
         SET @tempcard = CONCAT('UPDATE crm_sms_stat css inner join (select mt.user_id,',
                                   ' sum(SUBSTRING_INDEX(SUBSTRING_INDEX(mt.dest_mobile, "real", 1),":",-1)) as submit_card_num,',
                                   ' sum(SUBSTRING_INDEX(mt.dest_mobile,":",-1)) as real_card_num',
                                   ' from ',mt_sms_yearmonth,' mt WHERE mt.channel_id = 312 ',
                                   ' group by mt.user_id) x on css.user_id = x.user_id and css.months = "',year_months,'"',
                                   ' set css.submit_card_num = ifnull(x.submit_card_num,0),css.real_card_num = ifnull(x.real_card_num,0) ');
         PREPARE card FROM @tempcard;
         EXECUTE card; 
     END IF;    
    
END;