mysql 存储过程覆盖_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 ;