mysql 存储过程 范例_MYSQL 存储过程 范例

PROCEDURE IF EXISTSjy_gm_data_push;

delimiter//

CREATE PROCEDUREjy_gm_data_push()BEGIN

/*定义变量一*/

DECLARE MC_CODE VARCHAR(35);DECLARE DGV_DODE VARCHAR(32);DECLARE PC_CODE VARCHAR(35);DECLARE PCL_UID INT(11);DECLARE PCL_CID INT(11);DECLARE _done int default 0;/*游标*/

DECLARE _Cur CURSOR FOR

SELECTdm.MAGNETIC_CODEASMC_CODE,

dm.DEV_GATEWAY_CODEASDGV_DODE,

pc.PARK_CAR_CODEASPC_CODEFROMuser_area uaLEFT JOIN park p ON p.AREA_ID =ua.AIDLEFT JOIN park_car pc ON pc.PARK_ID =p.PARK_IDLEFT JOIN dev_magnetic dm ON dm.PARK_CAR_ID =pc.PARK_CAR_IDWHERE(ua.UID= ‘73‘ OR ua.UID = ‘82‘)AND dm.MAGNETIC_CODE IS NOT NULL;DECLARE CONTINUE HANDLER FOR SQLSTATE ‘02000‘ SET _done=1;SELECT max(pgr.PCL_ID) as PCL_ID INTO PCL_UID FROM push_gm_recode pgr LIMIT 0,1;SELECT max(psq.ID) as PSG_ID INTO PCL_CID FROM park_msg_queue psq LIMIT 0,1;/*打开光标*/

OPEN_Cur;

REPEATFETCH _Cur INTOMC_CODE, DGV_DODE,PC_CODE;IF NOT _done THEN

INSERT INTOpush_gm_recode (

MAGNETIC_CODE,

DEV_GATEWAY_CODE,

PARK_CAR_CODE,

EVENT,

EVENT_TIME,

PCL_ID,

UPDATE_TIME,

SERIAL_NO

)SELECTMC_CODE,

DGV_DODE,

PC_CODE,

pcl.`STATUS`,

pcl.TIME,max(pcl.ID) ASPCL_ID,

now(),

pcl.SERIAL_NOFROMpark_msg_queue pclWHEREpcl.DEVICE_CODE=MC_CODEAND (pcl.ID BETWEEN PCL_UID ANDPCL_CID)GROUP BYpcl.TIME,

pcl.`STATUS`

;UPDATEpush_gm_recodeSET PUSH_STATUS = 3

WHERE MAGNETIC_CODE =MC_CODEAND PUSH_ID NOT IN(SELECT

*

FROM(SELECTPUSH_IDFROMpush_gm_recodeWHEREMAGNETIC_CODE=MC_CODEGROUP BY`EVENT`,

EVENT_TIME,

MAGNETIC_CODE

)s

);END IF;

UNTIL _doneEND REPEAT; #当_done=1时退出被循/*关闭光标*/

DELETE FROM park_msg_queue WHERE ID <=PCL_CID;CLOSE_Cur;END

//

View Code

MYSQL 存储过程 范例

标签：img   until   delete   time   splay   har   for   join   The

本条技术文章来源于互联网，如果无意侵犯您的权益请点击此处反馈版权投诉

本文系统来源：https://www.cnblogs.com/virtulreal/p/9749834.html