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 ;