mysql使用navicat編寫調用預存程序

標籤：mysq   cal   編寫   track   使用   media   procedure   datetime   資料   

在Navicat裡面，找到函數，右鍵，建立函數，選擇過程，如果有參數就填寫函數，如果沒有就直接點擊完成

在BEGIN......END中間編寫要執行的sql語句，例如下面預存程序取名為pro_data_bak:

BEGIN/*把rd01_device_callback_data 2天前的資料備份到rd01_device_callback_data_bak*/  insert into rd01_device_callback_data_bak(id,imei,travelid,gps_time,receive_time,transmit_time,altitude,speed,latitude,longitude,course,pdop,satnum,alarm_type0,alarm_name0,alarm_type1,alarm_name1,alarm_photo_linkurl,startup_time,shudown_time,is_used,create_time,create_by,update_time,update_by)SELECTid id,imei imei,travelid travelId,gps_time gpsTime,receive_time receiveTime,transmit_time transmitTime,altitude altitude,speed speed,latitudelatitude,longitude longitude,course course,pdop pdop,satnum satNum,alarm_type0 alarmType0,alarm_name0 alarmName0,alarm_type1 alarmType1,alarm_name1 alarmName1,alarm_photo_linkurl alarmPhotoLinkUrl,startup_time startUpTime,shudown_time shudownTime,is_used isUsed,create_time createTime,create_by createPerson,update_time updateTime,update_by updatePersonFROMrd01_device_callback_dataWHERETO_DAYS(NOW()) - TO_DAYS(create_time) > 1;/*刪除rd01_device_callback_data 2天以前的資料*/DELETEFROMrd01_device_callback_dataWHERETO_DAYS(NOW()) - TO_DAYS(create_time) > 1;/*把rd02_device_info 2天前的資料備份到rd02_device_info_bak*/insert into rd02_device_info_bak(id,message_id,message_property,imei,serial_number,message_split,message_body,media_id,check_code,create_time,create_person,update_time,update_person,is_used)SELECTid id,message_idmessageId,message_property messageProperty,imei imei,serial_numberserialNumber,message_split messageSplit,message_body messageBody,media_id meidiaId,check_code checkCode,create_time createTime,create_person createPerson,update_time updateTime,update_personupdatePerson,is_used isUsedFROMrd02_device_infoWHERETO_DAYS(NOW()) -TO_DAYS(create_time) > 1;/*刪除rd02_device_info 2天前的資料*/DELETEFROMrd02_device_infoWHERETO_DAYS(NOW()) -TO_DAYS(create_time) > 1;/*把rd_track_info 7天前的資料備份到rd_track_info_bak*/insert into rd_track_info_bak(id,license_plate,device_id,address_name,altitude,speed,driving_direction,longitude,latitude,gps,back_time,road_name,road_code,road_level,road_speed_limit,back_seq_no,create_time,del_flag,alarm_type0,alarm_name0,alarm_type1,alarm_name1,alarm_photo_linkurl,gps_time)SELECTid id,license_plate licensePlate,device_id deviceId,address_name addressName,altitude altitude,speed speed,driving_direction drivingDirection,longitude longitude,latitude latitude,gps gps,back_time backTime,road_name roadName,road_code roadCode,road_level roadLevel,road_speed_limit roadSpeedLimit,back_seq_no backSeqNo,create_time createTime,del_flag delFlag,alarm_type0 alarmType0,alarm_name0 alarmName0,alarm_type1 alarmType1,alarm_name1 alarmName1,alarm_photo_linkurl alarmPhotoLinkurl,gps_time gpsTimeFROMrd_track_infoWHERETO_DAYS(NOW()) - TO_DAYS(gps_time) > 7;/*刪除rd_track_info 7天前的資料*/DELETEFROMrd_track_infoWHERETO_DAYS(NOW()) - TO_DAYS(gps_time) > 7;END

然後在xml裡面引用

<mapper namespace="com.ra.truck.mapper.DataBakMapper"><select id="callProcedureOfDataBak">{call pro_data_bak()}</select></mapper>

通過java定時調度調用這個預存程序就OK了

mysql使用navicat編寫調用預存程序