7.mysql 存储过程例子

来源：互联网发布：路由器封掉微信端口编辑：程序博客网时间：2024/06/08 07:59

需求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)BEGINdeclare i int;declare days int;set i = 0;set days = DATEDIFF(esdate,bsdate);truncate tb_imeitemp;set i = 0;while i < days doset @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)BEGINdeclare 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 DOset @createTableSql = concat(@createTableSql, 'usercount_', i, ' int(11) ');if i != daysTHENset  @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 DOset @j = i;set @z = 0;while @j <= days DOset @beginDate = date_format(DATE_ADD(bsdate, INTERVAL i day), '%Y-%m-%d');set @sqlStr = '';if @j = iTHENset @fromTable = 'tb_imeitemp';set @sqlStr = concat('insert into sdk.tmpTable(id, usercount_0)  select ', (i + 1), ', count(*) from ', @fromTable, ' where datediff(lasttime, \'', @beginDate, '\') = 0');ELSEset @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

讲解如下：

1 0