【DATABASE-Mysql(2)】更新存储过程

DROP PROCEDURE IF EXISTS ssdds.`aaup`;
CREATE PROCEDURE `ssdds`.`aaup`()
BEGIN
DECLARE bkup_min_pre_id int;
DECLARE interval_day int;
DECLARE interval_day_bk int;
DECLARE interval_day_bk_pa int;
SET @interval_day=1;
SET @interval_day_bk_pa=90;
SET @interval_day_bk=180;


SELECT MAX(pre_id) into bkup_min_pre_id FROM prescription WHERE create_time < date_sub(CURDATE(),INTERVAL @interval_day_bk_pa DAY) LIMIT 1;
	if bkup_min_pre_id is not NULL then 
		INSERT INTO ssdds_backup.prescription_medicine_list SELECT * FROM prescription_medicine_list WHERE pre_id <= bkup_min_pre_id ;
    INSERT INTO ssdds_backup.prescription_split SELECT * FROM prescription_split WHERE pre_id <= bkup_min_pre_id ;
		INSERT INTO ssdds_backup.work_queue SELECT * FROM work_queue WHERE pre_id <= bkup_min_pre_id ;
		DELETE FROM prescription_medicine_list WHERE pre_id <= bkup_min_pre_id ;
    DELETE FROM prescription_split WHERE pre_id <= bkup_min_pre_id ;
		DELETE FROM work_queue WHERE pre_id <= bkup_min_pre_id ;
	end IF;
#处方
INSERT INTO ssdds_backup.prescription select * from prescription where create_time<date_sub(curdate(),interval @interval_day_bk_pa day);
INSERT INTO ssdds_backup.user_prescription select * from user_prescription where binding_time < date_sub(curdate(),interval @interval_day_bk_pa day);
INSERT INTO ssdds_backup.help_list select * from help_list where create_time<date_sub(curdate(),interval @interval_day_bk_pa day);
INSERT INTO ssdds_backup.device_command select * from device_command where create_time<date_sub(curdate(),interval @interval_day_bk_pa day);
INSERT INTO ssdds_backup.duty_list select * from duty_list where create_time<date_sub(curdate(),interval @interval_day_bk_pa day);
INSERT INTO ssdds_backup.review_records select * from review_records where create_time<date_sub(curdate(),interval @interval_day_bk_pa day);
INSERT INTO ssdds_backup.rsscs_mid select * from rsscs_mid where time_create<date_sub(curdate(),interval @interval_day_bk_pa day);
INSERT INTO ssdds_backup.system_log select * from system_log where time<date_sub(curdate(),interval @interval_day_bk_pa day);
INSERT INTO ssdds_backup.system_log_jxs select * from system_log_jxs where time<date_sub(curdate(),interval @interval_day_bk_pa day);
INSERT INTO ssdds_backup.sys_ticketauth select * from sys_ticketauth where create_time<date_sub(curdate(),interval @interval_day_bk_pa day);
INSERT INTO ssdds_backup.patient select * from patient where create_time<date_sub(curdate(),interval @interval_day_bk_pa day);
INSERT INTO ssdds_backup.client_command select * from client_command where create_time<date_sub(curdate(),interval @interval_day_bk_pa day);
INSERT INTO ssdds_backup.statistics SELECT * from statistics;
INSERT INTO ssdds_backup.statistics_location SELECT * from statistics_location where dist_time<date_sub(curdate(),interval @interval_day_bk_pa day);
INSERT INTO ssdds_backup.statistics_med SELECT * from statistics_med where dist_time<date_sub(curdate(),interval @interval_day_bk_pa day);
commit;

# 将主库所有表中当天零点以前的数据插入到备份库相应的数据表中
# 如果要转存主库所有表中一个月以前的数据，请将上述“SET @interval_day=0;”更改为：“SET @interval_day=day(last_day(date_sub(curdate(), interval 1 month)));”



delete from prescription where create_time < date_sub(curdate(),interval @interval_day_bk_pa day);
#delete from prescription_medicine_list where create_time < date_sub(curdate(),interval @interval_day day);
#  删除prescription表中当天零点以前的数据，并触发prescription中的触发器
delete from help_list where create_time < date_sub(curdate(),interval @interval_day_bk_pa day);
delete from duty_list where create_time < date_sub(curdate(),interval @interval_day_bk_pa day);
delete from rsscs_mid where time_create < date_sub(curdate(),interval @interval_day_bk_pa day);
delete from review_records where create_time < date_sub(curdate(),interval @interval_day_bk_pa day);
delete from client_command where create_time < date_sub(curdate(),interval @interval_day_bk_pa day);
delete from system_log where time < date_sub(curdate(),interval @interval_day_bk_pa day);
delete from system_log_jxs where time < date_sub(curdate(),interval @interval_day_bk_pa day);
delete from user_prescription where binding_time < date_sub(curdate(),interval @interval_day_bk_pa day);
delete from device_command where lastdeal_time < date_sub(curdate(),interval @interval_day_bk_pa day);
delete from rfid_exec_records where create_time < date_sub(curdate(),interval @interval_day_bk_pa day);
delete from patient where create_time < date_sub(curdate(),interval @interval_day_bk_pa day);
delete from statistics;
delete from statistics_location;
delete from statistics_med;

SET bkup_min_pre_id = NULL;
#  删除带有时间字段的表中当天零点以前的数据
DELETE from ssdds_backup.basket_mapping;
DELETE from ssdds_backup.location;
DELETE from ssdds_backup.location_chest;
DELETE from ssdds_backup.location_dismounted;
DELETE from ssdds_backup.location_shelf;
DELETE from ssdds_backup.machine;
DELETE from ssdds_backup.medicine;
DELETE from ssdds_backup.medicine_lable;
DELETE from ssdds_backup.medicine_machineid;
DELETE from ssdds_backup.secondary_storage;
DELETE from ssdds_backup.secondary_storage_ip;
DELETE from ssdds_backup.`user`;
DELETE from ssdds_backup.global_variables;
DELETE from ssdds_backup.device_monitor;
DELETE from ssdds_backup.sys_area;
DELETE from ssdds_backup.sys_button;
DELETE from ssdds_backup.sys_code;
DELETE from ssdds_backup.sys_data;
DELETE from ssdds_backup.sys_menu;
DELETE from ssdds_backup.sys_privilege;
DELETE from ssdds_backup.sys_role;
DELETE from ssdds_backup.sys_unit;
DELETE from ssdds_backup.sys_upload_file;
DELETE from ssdds_backup.sys_user;
DELETE from ssdds_backup.sys_userrole;
DELETE from ssdds_backup.sys_version;
DELETE from ssdds_backup.video_monitor;
DELETE from ssdds_backup.wastage_link;
DELETE from ssdds_backup.windows;
#  删除备份数据库中表信息
INSERT into ssdds_backup.basket_mapping SELECT * from basket_mapping;
INSERT into ssdds_backup.location SELECT * from location;
INSERT into ssdds_backup.location_chest SELECT * from location_chest;
INSERT into ssdds_backup.location_dismounted SELECT * from location_dismounted;
INSERT into ssdds_backup.location_shelf SELECT * from location_shelf;
INSERT into ssdds_backup.machine SELECT * from machine;
INSERT into ssdds_backup.medicine SELECT * from medicine;
INSERT into ssdds_backup.medicine_lable SELECT * from medicine_lable;
INSERT into ssdds_backup.medicine_machineid SELECT * from medicine_machineid;
INSERT into ssdds_backup.secondary_storage SELECT * from secondary_storage;
INSERT into ssdds_backup.secondary_storage_ip SELECT * from secondary_storage_ip;
INSERT into ssdds_backup.`user` SELECT * from `user`;
INSERT into ssdds_backup.global_variables SELECT * from global_variables;
INSERT into ssdds_backup.device_monitor SELECT * from device_monitor;
INSERT into ssdds_backup.sys_area SELECT * from sys_area;
INSERT into ssdds_backup.sys_button SELECT * from sys_button;
INSERT into ssdds_backup.sys_code SELECT * from sys_code;
INSERT into ssdds_backup.sys_data SELECT * from sys_data;
INSERT into ssdds_backup.sys_menu SELECT * from sys_menu;
INSERT into ssdds_backup.sys_privilege SELECT * from sys_privilege;
INSERT into ssdds_backup.sys_role SELECT * from sys_role;
INSERT into ssdds_backup.sys_unit SELECT * from sys_unit;
INSERT into ssdds_backup.sys_upload_file SELECT * from sys_upload_file;
INSERT into ssdds_backup.sys_user SELECT * from sys_user;
INSERT into ssdds_backup.sys_userrole SELECT * from sys_userrole;
INSERT into ssdds_backup.sys_version SELECT * from sys_version;
INSERT into ssdds_backup.video_monitor SELECT * from video_monitor;
INSERT into ssdds_backup.wastage_link SELECT * from wastage_link;
INSERT into ssdds_backup.windows SELECT * from windows;

# 清理备份库

SELECT MAX(pre_id) into bkup_min_pre_id FROM ssdds_backup.prescription WHERE create_time < date_sub(CURDATE(),INTERVAL @interval_day_bk DAY) LIMIT 1;
	if bkup_min_pre_id is not NULL then 
		DELETE FROM ssdds_backup.prescription_medicine_list WHERE pre_id <= bkup_min_pre_id ;
    DELETE FROM ssdds_backup.prescription_split WHERE pre_id <= bkup_min_pre_id ;
		DELETE FROM ssdds_backup.work_queue WHERE pre_id <= bkup_min_pre_id ;
	end IF;

delete from ssdds_backup.client_command where create_time < date_sub(curdate(),interval @interval_day_bk day);
delete from ssdds_backup.prescription where create_time < date_sub(curdate(),interval @interval_day_bk day);
delete from ssdds_backup.help_list where create_time < date_sub(curdate(),interval @interval_day_bk day);
delete from ssdds_backup.duty_list where create_time < date_sub(curdate(),interval @interval_day_bk day);
delete from ssdds_backup.rsscs_mid where time_create < date_sub(curdate(),interval @interval_day_bk day);
delete from ssdds_backup.review_records where create_time < date_sub(curdate(),interval @interval_day_bk day);
delete from ssdds_backup.system_log where time < date_sub(curdate(),interval @interval_day_bk day);
delete from ssdds_backup.system_log_jxs where time < date_sub(curdate(),interval @interval_day_bk day);
delete from ssdds_backup.user_prescription where binding_time < date_sub(curdate(),interval @interval_day_bk day);
delete from ssdds_backup.device_command where lastdeal_time < date_sub(curdate(),interval @interval_day_bk day);
delete from ssdds_backup.rfid_exec_records where create_time < date_sub(curdate(),interval @interval_day_bk day);
delete from ssdds_backup.patient where create_time < date_sub(curdate(),interval @interval_day_bk day);
delete from ssdds_backup.statistics_location where dist_time < date_sub(curdate(),interval @interval_day_bk day);
delete from ssdds_backup.statistics_med where dist_time < date_sub(curdate(),interval @interval_day_bk day);

END;