mysql存储过程的一个商用例子
描述
mysql的存储过程,一个商用的例子,大家主要看语法,以及写法,内容可以不关注
BEGIN
DECLARE last_id INT DEFAULT 0;
DECLARE dt varchar(20) default date_format(now(), '%m%d%H%i%s');
DECLARE error_flag int default 0;
DECLARE exe_sql varchar(1000);
DECLARE tem_table varchar(100) default concat('temp_sndata_run_',dt);
DECLARE tem_table2 varchar(100) default concat('temp_sndata_run2_',dt);
/* exit CONTINUE*/
DECLARE exit HANDLER for SQLEXCEPTION SET error_flag=1;
insert into t_timeslog(ipaddress,batchId,flag,timesname,insertDate) values('localhost',dt,91,'p_sndata_run',now());
/* start transaction;
set autocommit=0;
***************/
select max(id) into last_id from t_sndata_lastid;
if last_id is null then
insert into t_sndata_lastid(id,insertDate) values(0,now());
set last_id=0;
end if;
/*t_sndata zd
insert into t_sndata_send(id,sn,zd) select max(id),sn,zd from t_sndata where zd is not null
and zd<>0 and e is not null and e<>0 and zf is not null and zb is not null
and parsedata is not null
and id>last_id
group by sn,zd;*/
insert into t_timeslog(ipaddress,batchId,flag,timesname,insertDate) values('localhost',dt,92,'p_sndata_run',now());
set @sql = concat('create table IF NOT EXISTS ',tem_table2,' as select max(id) id,
sn,zd from t_sndata where zd is not null and zd<>0 and e is not null and e<>0 and zf is not null and zb is not null
and parsedata is not null
and id>',last_id,'
group by sn,zd');
prepare exe_sql from @sql;
EXECUTE exe_sql;
/*添加索引*/
set @sql = concat('ALTER TABLE ',tem_table2,' ADD PRIMARY KEY (id) ');
prepare exe_sql from @sql;
EXECUTE exe_sql;
insert into t_timeslog(ipaddress,batchId,flag,timesname,insertDate) values('localhost',dt,93,'p_sndata_run',now());
set @sql=concat('select count(1) into @ppp from ',tem_table2);
prepare exe_sql from @sql;
EXECUTE exe_sql;
if @ppp>0 then
set @sql=concat('insert into t_sndata_send(id,sn,zd) select id,sn,zd from ',tem_table2,' ');
prepare exe_sql from @sql;
EXECUTE exe_sql;
set @sql=concat('update t_sndata_lastid set id=(select max(id) from ',tem_table2,'),insertDate=now()');
prepare exe_sql from @sql;
EXECUTE exe_sql;
end if;
insert into t_timeslog(ipaddress,batchId,flag,timesname,insertDate) values('localhost',dt,94,'p_sndata_run',now());
/*同时查询 t_sndata_send_log,把超过10分钟未发送成功的数据重新放到发送池*/
set @sql = concat('create table IF NOT EXISTS ',tem_table,' as select id,sn,zd from t_sndata_send_log where status=1 and insertDate<= date_sub(NOW(),interval 10 minute)');
prepare exe_sql from @sql;
EXECUTE exe_sql;
/*添加索引*/
insert into t_timeslog(ipaddress,batchId,flag,timesname,insertDate) values('localhost',dt,951,'p_sndata_run',now());
set @sql = concat('ALTER TABLE ',tem_table,' ADD PRIMARY KEY (id) ');
prepare exe_sql from @sql;
EXECUTE exe_sql;
insert into t_timeslog(ipaddress,batchId,flag,timesname,insertDate) values('localhost',dt,952,'p_sndata_run',now());
set @sql=concat('insert into t_sndata_send(id,sn,zd) select id,sn,zd from ',tem_table,' ');
prepare exe_sql from @sql;
EXECUTE exe_sql;
insert into t_timeslog(ipaddress,batchId,flag,timesname,insertDate) values('localhost',dt,953,'p_sndata_run',now());
set @sql=concat('delete a from t_sndata_send_log a where id in (select id from ',tem_table,' b )');
prepare exe_sql from @sql;
EXECUTE exe_sql;
/****************/
if error_flag=1 THEN
/*ROLLBACK;
set autocommit=1;*/
set @sql=concat('drop table IF EXISTS ',tem_table,' ');
prepare exe_sql from @sql;
EXECUTE exe_sql;
/**/
set @sql=concat('drop table IF EXISTS ',tem_table2,' ');
prepare exe_sql from @sql;
EXECUTE exe_sql;
ELSE
/*commit;
set autocommit=1;*/
set @sql=concat('update t_timeslog set endDate=now() where batchId=',dt,' and timesname=''p_sndata_run''');
prepare exe_sql from @sql;
EXECUTE exe_sql;
insert into t_timeslog(ipaddress,batchId,flag,timesname,insertDate) values('localhost',dt,96,'p_sndata_run',now());
set @sql=concat('drop table IF EXISTS ',tem_table,' ');
prepare exe_sql from @sql;
EXECUTE exe_sql;
/**/
set @sql=concat('drop table IF EXISTS ',tem_table2,' ');
prepare exe_sql from @sql;
EXECUTE exe_sql;
end if;
END
javawebxx.com由javaweb学习网所有 网站地图 备案号:苏ICP备17055254号-1
留言