MYSQL 存储过程 范例
时间：2021-07-01 10:21:17
帮助过：17人阅读
PROCEDURE IF EXISTS jy_gm_data_push;
delimiter //
CREATE PROCEDURE jy_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
SELECT
dm.MAGNETIC_CODE AS MC_CODE,
dm.DEV_GATEWAY_CODE AS DGV_DODE,
pc.PARK_CAR_CODE AS PC_CODE
FROM
user_area ua
LEFT JOIN park p
ON p.AREA_ID
= ua.AID
LEFT JOIN park_car pc
ON pc.PARK_ID
= p.PARK_ID
LEFT JOIN dev_magnetic dm
ON dm.PARK_CAR_ID
= pc.PARK_CAR_ID
WHERE
(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;
REPEAT
FETCH _Cur
INTO MC_CODE, DGV_DODE,PC_CODE;
IF NOT _done
THEN
INSERT INTO push_gm_recode (
MAGNETIC_CODE,
DEV_GATEWAY_CODE,
PARK_CAR_CODE,
EVENT,
EVENT_TIME,
PCL_ID,
UPDATE_TIME,
SERIAL_NO
)
SELECT
MC_CODE,
DGV_DODE,
PC_CODE,
pcl.`STATUS`,
pcl.TIME,
max(pcl.ID)
AS PCL_ID,
now(),
pcl.SERIAL_NO
FROM
park_msg_queue pcl
WHERE
pcl.DEVICE_CODE = MC_CODE
AND (pcl.ID
BETWEEN PCL_UID
AND PCL_CID)
GROUP BY
pcl.TIME,
pcl.`STATUS`
;
UPDATE push_gm_recode
SET PUSH_STATUS
= 3
WHERE MAGNETIC_CODE
= MC_CODE
AND PUSH_ID
NOT IN (
SELECT
*
FROM
(
SELECT
PUSH_ID
FROM
push_gm_recode
WHERE
MAGNETIC_CODE = MC_CODE
GROUP BY
`EVENT`,
EVENT_TIME,
MAGNETIC_CODE
)s
);
END IF;
UNTIL _done END 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
