mysql存储过程，游标嵌套，判断条件

#判断游标是否存在，存在则删除
DROP PROCEDURE
IF EXISTS statisticalPingTime;


#申明一个游标，并带输入参数
CREATE PROCEDURE statisticalPingTime (IN p_date VARCHAR(100))
BEGIN
DECLARE done Boolean DEFAULT TRUE;


DECLARE d_userId VARCHAR (100);


DECLARE d_name VARCHAR (100);


DECLARE d_email VARCHAR (100);


DECLARE d_ip VARCHAR (100);


DECLARE d_dept VARCHAR (100);


DECLARE d_PersonID VARCHAR (100);


#申明游标　查询a_user中所有符合条件的数据
DECLARE
userCursor CURSOR FOR SELECT
u.userId,
u. NAME,
u.email,
u.ip,
u.dept,
u.PersonID
FROM
a_user u
WHERE
u.delFlag = 0;


#每个游标必须使用不同的declare continue handler for not found set done=false来控制游标的结束
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = FALSE;


#打开游标
OPEN userCursor;


#得到游标中的第一条数据
FETCH userCursor INTO d_userId,
 d_name,
 d_email,
 d_ip,
 d_dept,
 d_PersonID;


#循环输出游标中的数据
WHILE done DO
#申明第二个游标
BEGIN
DECLARE d_done Boolean DEFAULT TRUE;


DECLARE d_d_statusId BIGINT (22);


#申明游标　查询a_status_detail中所有符合条件的数据
DECLARE
statusDetailCursor CURSOR FOR SELECT
d.statusId
FROM
a_status_detail d
WHERE
d.userId = d_userId
AND d.dateStatus = DATE_FORMAT(p_date, '%Y-%m-%d')
AND delFlag = 0;


#每个游标必须使用不同的declare continue handler for not found set d_done=false来控制游标的结束
DECLARE CONTINUE HANDLER FOR NOT FOUND SET d_done = FALSE;


#打开游标
OPEN statusDetailCursor;


#得到游标中的第一条数据
FETCH statusDetailCursor INTO d_d_statusId;


#循环输出游标中的数据
WHILE d_done DO
#申明第三个游标
BEGIN
DECLARE d_d_done Boolean DEFAULT TRUE;


DECLARE d_d_timeIp datetime;


DECLARE timeIpEarly datetime;


DECLARE timeIpLast datetime;


#申明游标　查询a_status_log中所有符合条件的数据
DECLARE
statusLogCursor CURSOR FOR SELECT
l.timeIp
FROM
a_status_log l
WHERE
l.userId = d_userId
AND DATE_FORMAT(l.timeIp, '%Y-%m-%d') = DATE_FORMAT(p_date, '%Y-%m-%d')
AND l.delFlag = 0
AND l. STATUS = 0
ORDER BY
l.timeIp DESC;


#每个游标必须使用不同的declare continue handler for not found set d_d_done = false来控制游标的结束
DECLARE CONTINUE HANDLER FOR NOT FOUND SET d_d_done = FALSE;


#打开第三个游标
OPEN statusLogCursor;


#得到游标中的第一条数据
FETCH statusLogCursor INTO d_d_timeIp;




IF d_d_timeIp IS NOT NULL THEN


SET timeIpLast = d_d_timeIp;


#循环输出游标中的数据
WHILE d_d_done DO


SET timeIpEarly = d_d_timeIp;


#得到游标中的第一条数据
FETCH statusLogCursor INTO d_d_timeIp;




END
WHILE;


#更新数据为数据
UPDATE a_status_detail d
SET d.timeIpEarly = timeIpEarly,
 d.timeIpLast = timeIpLast,
 d.updateTime = SYSDATE(),
 d.updator = 'admin'
WHERE
d.statusId = d_d_statusId;


CLOSE statusLogCursor;




ELSE
#申明第四个游标
BEGIN
DECLARE d_d_d_done Boolean DEFAULT TRUE;


DECLARE d_d_d_timeIp datetime;


DECLARE d_d_d_timeIpEarly datetime;


DECLARE d_d_d_timeIpLast datetime;


#申明游标　查询a_status_log_history中所有符合条件的数据
DECLARE statusLogHistoryCursor CURSOR FOR SELECT
l.timeIp
FROM
a_status_log_history l
WHERE
l.userId = d_userId
AND DATE_FORMAT(l.timeIp, '%Y-%m-%d') = DATE_FORMAT(p_date, '%Y-%m-%d')
AND l.delFlag = 0
AND l. STATUS = 0
ORDER BY
l.timeIp DESC;


#每个游标必须使用不同的declare continue handler for not found set d_d_d_done = false来控制游标的结束
DECLARE CONTINUE HANDLER FOR NOT FOUND SET d_d_d_done = FALSE;


#打开第四个游标
OPEN statusLogHistoryCursor;


#得到游标中的第一条数据
FETCH statusLogHistoryCursor INTO d_d_d_timeIp;




IF d_d_d_timeIp IS NOT NULL THEN


SET d_d_d_timeIpLast = d_d_d_timeIp;


#循环输出游标中的数据
WHILE d_d_d_done DO


SET d_d_d_timeIpEarly = d_d_d_timeIp;


#得到游标中的第一条数据
FETCH statusLogHistoryCursor INTO d_d_d_timeIp;




END
WHILE;


#更新数据为数据
UPDATE a_status_detail d
SET d.timeIpEarly = d_d_d_timeIpEarly,
 d.timeIpLast = d_d_d_timeIpLast,
 d.updateTime = SYSDATE(),
 d.updator = 'adminHistory'
WHERE
d.statusId = d_d_statusId;


#关闭第四个游标
CLOSE statusLogHistoryCursor;




END
IF;




END;




END
IF;




END;


#得到游标中的下一条数据
FETCH statusDetailCursor INTO d_d_statusId;




END
WHILE;


#关闭第二个游标
CLOSE statusDetailCursor;




END;


#得到游标中的下一条数据
FETCH userCursor INTO d_userId,
 d_name,
 d_email,
 d_ip,
 d_dept,
 d_PersonID;




END
WHILE;


#关闭游标
CLOSE userCursor;




END