7.mysql 存储过程例子

需求1：从tb_userlog_all_20160101 到 tb_userlog_all_20160105 这五天内1001011这个渠道的所有 不重复（后面重复的imei号不插入）的imei 数据 存到表tb_imeitemp 中去。

代码及讲解如下：

CREATE DEFINER=`netsdk`@`%` PROCEDURE `c`(in bsdate varchar(200),in esdate varchar(200),in channelid int)
BEGIN
declare i int;
declare days int;
set i = 0;
set days = DATEDIFF(esdate,bsdate);
truncate tb_imeitemp;
set i = 0;

while i < days do
	set @sdate = date_format(DATE_ADD(bsdate,INTERVAL i day),'%Y%m%d');
	set @fromTable = concat('insert ignore into  tb_imeitemp(imei,lasttime,channelid) select  extraimei,lasttime,channelid from tb_userlog_all_', @sdate, ' where channelid = ', channelid);
	PREPARE sql1 from @fromTable;
	EXECUTE sql1;
	set i = i + 1;
end while;
END

需求2：有点复杂，把需要注意的细节告诉大家

CREATE DEFINER=`netsdk`@`%` PROCEDURE `c2`(in bsdate varchar(200),in esdate varchar(200),in channelid int)
BEGIN
	declare i int;
	declare days int;
	-- declare createTableSql varchar(2000);
	set i = 0;
	set days = DATEDIFF(esdate,bsdate);
	set @del = 'drop table if exists sdk.tmpTable; ';
	
	set @createTableSql = 'create table if not exists sdk.tmpTable(id int(11) primary key , '; -- 加上数据库名
	PREPARE stat from @del;
	EXECUTE stat;
	while i <= days DO
		set @createTableSql = concat(@createTableSql, 'usercount_', i, ' int(11) ');
		if i != days
		THEN
			set  @createTableSql= concat(@createTableSql, ',');
		END if;
		set i = i + 1;
	end while;
	set  @createTableSql = concat(@createTableSql, ');');
	
	PREPARE stat from @createTableSql;
	EXECUTE stat;
	truncate sdk.tmpTable; -- 清空数据库
	
	set i = 0;
	while i <= days DO
		set @j = i;
		set @z = 0;
		while @j <= days DO
			set @beginDate = date_format(DATE_ADD(bsdate, INTERVAL i day), '%Y-%m-%d');
			set @sqlStr = '';
			
			if @j = i
			THEN				
				set @fromTable = 'tb_imeitemp';
				set @sqlStr = concat('insert into sdk.tmpTable(id, usercount_0)  select ', (i + 1), ', count(*) from ', @fromTable, ' where datediff(lasttime, \'', @beginDate, '\') = 0');
			ELSE
				set @sdate = date_format(DATE_ADD(bsdate, INTERVAL @j day), '%Y%m%d');
				set @fromTable = concat('sdk.tb_userlog_all_', date_format(DATE_ADD(bsdate, INTERVAL @j day),'%Y%m%d'));
				set @sqlStr = concat('update sdk.tmpTable set usercount_', @z,' = ( select count(DISTINCT extraimei) from ', @fromTable,  ' as a1 where channelid = ', channelid, ' and exists (select * from sdk.tb_imeitemp as temp where temp.imei = a1.extraimei and datediff(lasttime, \'', @beginDate , '\') = 0 ) ) where id = ', (i + 1));
			end if;
			
			PREPARE stat from @sqlStr;
			EXECUTE stat;
			set @z = @z + 1;
			set @j = @j + 1;
		end while;
		
	
		
		set i = i + 1;
	end WHILE;
	
END

讲解如下：