navicat中怎么编写存储过程

发布网友 发布时间：2022-04-22 00:14

我来回答

共2个回答

懂视网 时间：2022-04-10 13:31

在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
		)SELECT
		id id,
		imei imei,
		travelid travelId,
		gps_time gpsTime,
		receive_time receiveTime,
		transmit_time transmitTime,
		altitude altitude,
		speed speed,
		latitude
		latitude,
		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 updatePerson
		FROM
		rd01_device_callback_data
		WHERE
		TO_DAYS(NOW()) - TO_DAYS(create_time) > 1;
/*删除rd01_device_callback_data 2天以前的数据*/
	DELETE
		FROM
		rd01_device_callback_data
		WHERE
		TO_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
		)SELECT
		id id,
		message_id
		messageId,
		message_property messageProperty,
		imei imei,
		serial_number
		serialNumber,
		message_split messageSplit,
		message_body messageBody,
		media_id meidiaId,
		check_code checkCode,
		create_time createTime,
		create_person createPerson,
		update_time updateTime,
		update_person
		updatePerson,
		is_used isUsed
		FROM
		rd02_device_info
		WHERE
		TO_DAYS(NOW()) -
		TO_DAYS(create_time) > 1;
/*删除rd02_device_info 2天前的数据*/
DELETE
		FROM
		rd02_device_info
		WHERE
		TO_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
		)SELECT
		id 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 gpsTime
		FROM
		rd_track_info
		WHERE
		TO_DAYS(NOW()) - TO_DAYS(gps_time) > 7;
/*删除rd_track_info 7天前的数据*/
DELETE
		FROM
		rd_track_info
		WHERE
		TO_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编写调用存储过程

标签：mysq   cal   编写   track   使用   media   procedure   datetime   数据   

热心网友 时间：2022-04-10 10:39

存储过程就是可存储于数据库中的一段可执行代码块。
create
or
replace
procere
过程名
(
参数列表
)
as
变量列表
begin
业务逻辑代码块
open
ref_cursor
for
v_sqltext2;
exception
异常处理代码块
end
过程名
create
or
replace
procere