【MySql】存储过程限定月份，限定某天等基础的使用

BEGIN
    declare begin_time varchar(10);  /****一月的第一天**/
    declare end_time varchar(10);  	 /****月的最后一天**/
    declare month_time varchar(10);  	 /****月份，格式：2013-11**/

    declare releaseCount int default 0; /***首页开屏广告点击量*/
    declare math int default 0;

    set @execSql = concat(' select ifnull( count(*) , 0 ) into @releaseCount from REP_CLICK_COUNT where data_time>=\'',@begin_time,'\' and data_time<=\'',@end_time,'\' and DATA_TYPE=1');
    prepare stmt from @execSql;
    execute stmt;
	
    #set @end_time = dataTime;
    set @end_time = concat(substring(dataTime,1,7),'-31');

    set @begin_time = concat(substring(@end_time,1,7),'-01');

    set @month_time = substring(@end_time,1,7);

   /**********数字的使用********/
    set @math =\'2102\' ;

    /**********字符 或 变量 的使用********/
     set @math =\'',dataTime,'\' ;

   /************限定insert_time日的*******************/
    where str_to_date(concat(\'',dataTime,'\',\'00:00:00\'),\'%Y-%m-%d %H:%i:%s\')<=insert_time ',' and insert_time<=str_to_date(concat(\'',dataTime,'\',\'23:59:59\'),\'%Y-%m-%d %H:%i:%s\') and version=\'2\' and event_id=\'2102\'  and event_source=\'2006\'  '); 

   /************限定data_time月份的*******************/
    where data_time>=\'',@begin_time,'\' and data_time<=\'',@end_time,'\' and (action_type=1 or action_type=2)  and (client_name=\'aMarket2.0\' or client_name=\'aMarket30_Pad\' or client_name=\'aMarketSDK\' or client_name=\'aMarket_Pad\' or client_name=\'GfanSDK\')  GROUP BY channel_code  ORDER BY count(DISTINCT device_imei,device_mac) DESC'); 

   /****设置值的写法1*****/
    set @execSql = concat('insert into REP_CLICK_COUNT(data_time,release_count,forum_count,h5_count,add_count,admin_forum_count)',
									' values(\'',@month_time,'\',\'',@releaseCount,'\',\'',@forumCount,'\',\'',@h5Count,'\',\'',@addCount,'\',\'',@adminforumCount,'\',now())');
    prepare stmt from @execSql;
    execute stmt;

    /****设置值的写法2*****/
    set @execSql = concat('insert into report_gfan_column(data_time,version, column_code,click_count,down_count,click_down_rate,install_count,down_install_rate,insert_time) ',
		               ' select data_time, client_version,data_type, count, 0,0.00,0,0.00,now() from report_gfan_to_column where data_time=\'',dataTime,'\' and data_type<=20 '); 

    prepare stmt from @execSql;
    execute stmt;

   /************练习1*******************/
    set @execSql = concat('insert into report_gfan_data(data_time,client_version_code,client_version,data_type,count) select \'',dataTime,'\', \'0\', \'0\', 48, count(1) from client_event_log ',
										' where str_to_date(concat(\'',dataTime,'\',\'00:00:00\'),\'%Y-%m-%d %H:%i:%s\')<=insert_time ',
                    ' and insert_time<=str_to_date(concat(\'',dataTime,'\',\'23:59:59\'),\'%Y-%m-%d %H:%i:%s\') and version=\'2\' and event_id=\'2102\'  and event_source=\'2006\'  '); 

    prepare stmt from @execSql;
    execute stmt;
		
   /************练习2*******************/
   set @execSql = concat('insert into  fact_active_month(data_time,channel_code,version,active_count,insert_time) ',
								' select  \'',@month_time,'\', channel_code,\'all\',count(DISTINCT device_imei,device_mac),now() ',
								' from log_device_active where data_time>=\'',@begin_time,'\' and data_time<=\'',@end_time,'\' and (action_type=1 or action_type=2)  and (client_name=\'aMarket2.0\' or client_name=\'aMarket30_Pad\' or client_name=\'aMarketSDK\' or client_name=\'aMarket_Pad\' or client_name=\'GfanSDK\')  GROUP BY channel_code  ORDER BY count(DISTINCT device_imei,device_mac) DESC'); 
    prepare stmt from @execSql;
    execute stmt;
END