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