mysql的一个存储过程

delimiter $$
CREATE PROCEDURE `createTableTest` (
	IN v_year VARCHAR (32),
	IN v_month VARCHAR (32)
)
BEGIN

DECLARE v_tableName VARCHAR (32) ;
DECLARE v_sql VARCHAR (32) ;
DECLARE v_time_begin VARCHAR (32) ;
DECLARE v_time_end VARCHAR (32) ;
set v_time_begin=CONCAT('''',v_year,'-',v_month,'-','01 00:00:00','''');#引入引号''''
set v_time_end=CONCAT('''',v_year,'-',v_month,'-','01 03:59:59',''''); 
SET v_tableName = CONCAT(
	'smp_equip_realtimedata_',
	v_year,
	v_month
) ;
SET v_sql = CONCAT(
	'insert into ',
	v_tableName,
	' (id,create_by,create_date,update_by,update_date,remarks,del_flag,equip_id,equip_code,equip_name,equip_type,func_code,data_lenth,uv_a,uv_b,uv_c,ia_a,ia_b,ia_c,leakage_current,temperature_i,temperature_ii,temperature_iii,temperature_iv,ta_trans_ratio,alarm_leakage_current,alarm_overload,alarm_short_circuit,alarm_overvoltage,alarm_under_voltage,alarm_broken_circuit,alarm_open_phase,alarm_phase_stagger,alarm_electric_arc,alarm_smoke_detector,alarm_flammable_gas,alarm_toxic_gas,alarm_temp_i,alarm_temp_ii,alarm_temp_iii,alarm_temp_iv,di1,di2,di3,di4,di5,di6,di7,di8,do1,do2,do3,do4,do5,do6,do7,do8,data_time,fault_leakage_current,fault_temp_i,fault_temp_ii,fault_temp_iii,fault_temp_iv,pt,active_energy,reactive_energy		) select 		  id,create_by,create_date,update_by,update_date,remarks,del_flag,equip_id,equip_code,equip_name,equip_type,func_code,data_lenth,uv_a,uv_b,uv_c,ia_a,ia_b,ia_c,leakage_current,temperature_i,temperature_ii,temperature_iii,temperature_iv,ta_trans_ratio,alarm_leakage_current,alarm_overload,alarm_short_circuit,alarm_overvoltage,alarm_under_voltage,alarm_broken_circuit,alarm_open_phase,alarm_phase_stagger,alarm_electric_arc,alarm_smoke_detector,alarm_flammable_gas,alarm_toxic_gas,alarm_temp_i,alarm_temp_ii,alarm_temp_iii,alarm_temp_iv,di1,di2,di3,di4,di5,di6,di7,di8,do1,do2,do3,do4,do5,do6,do7,do8,data_time,fault_leakage_current,fault_temp_i,fault_temp_ii,fault_temp_iii,fault_temp_iv,pt,active_energy,reactive_energy  from smp_equip_realtimedata  a where a.create_date>=',
	v_time_begin,
	'and a.create_date<=',
	v_time_end
) ;
SET @v_sql = v_sql ; #--注意很重要，将连成成的字符串赋值给一个变量（可以之前没有定义，但要以@开头）
PREPARE stmt FROM @v_sql ; #--预处理需要执行的动态SQL，其中stmt是一个变量
	EXECUTE stmt ; #执行SQL语句
	DEALLOCATE PREPARE stmt ; #释放掉预处理段
	END$$

delimiter ;

 

转载于:https://my.oschina.net/u/2842177/blog/3075620