mysql的每隔1分钟定时_MySql定时任务

CREATE DEFINER=`usmadmin`@`%` PROCEDURE `Pro_syn_c_dprecord`(OUT out_status CHAR(5))

COMMENT 'Violation records are synchronized from DSS to USM'

BEGIN

DECLARE done INT DEFAULT 0;

DECLARE last_syn_datetime datetime;

DECLARE next_syn_datetime datetime;

DECLARE usm_reu_id VARCHAR(40) DEFAULT '0' ;-- 大保卫的的违章ID

DECLARE vid INT DEFAULT 0 ;-- 违章ID

DECLARE vcar_num varchar(12);-- 车牌号

DECLARE vcar_speed int;-- 车速

DECLARE vcar_type int ;-- 车辆类型

DECLARE usm_car_type varchar(2) DEFAULT 'F' ;-- 大保卫中车辆类型 H货车 K客车 T特种车辆 F非机动车 Q其他机动车

DECLARE vcar_color int ;-- 车辆颜色

DECLARE vcap_date datetime;-- 违章时间

DECLARE vrec_type int ;-- 违章类型

DECLARE vdev_name varchar(50) ;-- 抓拍地点

DECLARE usm_typeVio VARCHAR(2) DEFAULT '32' ;-- 大保卫违章类型 32：闯红灯 10：超速

DECLARE vcombined_pic_url varchar(255); -- 总违章图片url

DECLARE vcar_img_url varchar(255);-- 违章图片1

DECLARE vcar_img1_url varchar(255);-- 违章图片2

DECLARE dprecord_data CURSOR

FOR

SELECT ID, CAR_NUM, CAR_SPEED, CAR_TYPE, CAR_COLOR, CAP_DATE, REC_TYPE, DEV_NAME, COMBINED_PIC_URL, CAR_IMG_URL, CAR_IMG1_URL

FROM c_dprecord_link_dh

WHERE CAP_DATE > last_syn_datetime;

DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1; -- 没有下一条数据 修改标记为1

BEGIN

SELECT t.updateTime INTO last_syn_datetime FROM syn_datetime t WHERE t.systemName= 'DH';

SET next_syn_datetime= SYSDATE();

END;

OPEN dprecord_data;

FETCH NEXT FROM dprecord_data INTO vid, vcar_num, vcar_speed, vcar_type, vcar_color, vcap_date, vrec_type, vdev_name, vcombined_pic_url, vcar_img_url, vcar_img1_url;

WHILE (done<>1) DO

SET usm_reu_id = REPLACE(UUID(),"-","");

IF vcar_type=1 THEN

SET usm_car_type = 'K';

END IF;

IF vcar_type=2 THEN

SET usm_car_type = 'H';

END IF;

IF vrec_type=1 THEN

SET usm_typeVio = '10';

ELSE

SET usm_typeVio = '32';

END IF;

BEGIN

INSERT into vehicle_regulation

(

id,

recordSource,

carType,

carNumber,

datetimeVio,

siteVio,

typeVio,

processState,

recieveDatetime,

isNeedfine,

isInFactry,

sourceId,

intIsActive,

intIsDelete,

createUserId,

createDateTime,

updateUserId,

updateDateTime,

companyId

)

VALUES

(

usm_reu_id,

'1',

usm_car_type,

vcar_num,

vcap_date,

vdev_name,

usm_typeVio,

'1',

SYSDATE(),

'Y',

'1',

vid,

'1',

'0',

'000000',

SYSDATE(),

'000000',

SYSDATE(),

'1'

);

END;

BEGIN

INSERT INTO image_regulation

(

imageUrl,

regulationid,

iscombined,

intIsActive,

intIsDelete,

createUserId,

createDateTime,

updateUserId,

updateDateTime,

companyId

)

VALUES

(

CONCAT('http://172.18.**.***:8081',substring(vcombined_pic_url, 5)),

usm_reu_id,

'1',

'1',

'0',

'000000',

SYSDATE(),

'000000',

SYSDATE(),

'1'

);

END;

IF(vcar_img_url<>'0') THEN

BEGIN

INSERT INTO image_regulation

(

imageUrl,

regulationid,

iscombined,

intIsActive,

intIsDelete,

createUserId,

createDateTime,

updateUserId,

updateDateTime,

companyId

)

VALUES

(

CONCAT('http://172.18.**.***:8081',substring(vcar_img_url, 5)),

usm_reu_id,

'0',

'1',

'0',

'000000',

SYSDATE(),

'000000',

SYSDATE(),

'1'

);

END;

END IF;

IF(vcar_img1_url<>'0') THEN

BEGIN

INSERT INTO image_regulation

(

imageUrl,

regulationid,

iscombined,

intIsActive,

intIsDelete,

createUserId,

createDateTime,

updateUserId,

updateDateTime,

companyId

)

VALUES

(

CONCAT('http://172.18.**.***:8081',substring(vcar_img1_url, 5)),

usm_reu_id,

'0',

'1',

'0',

'000000',

SYSDATE(),

'000000',

SYSDATE(),

'1'

);

END;

END IF;

BEGIN

UPDATE syn_datetime SET updateTime = next_syn_datetime WHERE systemName='DH';

END;

FETCH NEXT FROM dprecord_data INTO vid, vcar_num, vcar_speed, vcar_type, vcar_color, vcap_date, vrec_type, vdev_name, vcombined_pic_url, vcar_img_url, vcar_img1_url;

END WHILE;

CLOSE dprecord_data;

set out_status='OK';

END