DROP
PROCEDURE
IF EXISTS PRO_ALARM;
CREATE
PROCEDURE
PRO_ALARM()
BEGIN
DECLARE
userId
VARCHAR
(32);
#这个用于处理游标到达最后一行的情况
DECLARE
s
INT
DEFAULT
0;
#声明游标cursor_name(cursor_name是个多行结果集)
DECLARE
cursor_data
CURSOR
FOR
SELECT
tmp.USER_ID
FROM
(
SELECT
e.USER_ID,
MAX
(e.TIME_GMT) TIME_GMT
FROM
EVENTS e
GROUP
BY
e.USER_ID
HAVING
MAX
(e.TIME_GMT) < UNIX_TIMESTAMP() - 60 * 30
AND
MAX
(e.TIME_GMT) > UNIX_TIMESTAMP() - 60 * 60 * 24) tmp
INNER
JOIN
EVENTS t
ON
tmp.USER_ID = t.USER_ID
AND
tmp.TIME_GMT = t.TIME_GMT
WHERE
TYPE_ID !=
'34001'
;
#设置一个终止标记
DECLARE
CONTINUE
HANDLER
FOR
SQLSTATE
'02000'
SET
s = 1;
OPEN
cursor_data;
#获取游标当前指针的记录,读取一行数据并传给变量a,b
FETCH
cursor_data
INTO
userId;
#开始循环,判断是否游标已经到达了最后作为循环条件
WHILE s <> 1 DO
INSERT
INTO
EVENTS_NOTIFICATION
VALUES
(
NULL
, SYSDATE(), UNIX_TIMESTAMP(SYSDATE()),
UNIX_TIMESTAMP(SYSDATE()),
'00000'
, userId,
'1'
,
'0'
);
#读取下一行的数据
FETCH
cursor_data
INTO
userId;
END
WHILE;
#关闭游标
CLOSE
cursor_data;
END
;