mysql存储过程,游标嵌套,判断条件
来源:互联网 发布:open cursor for sql 编辑:程序博客网 时间:2024/05/17 03:22
#判断游标是否存在,存在则删除
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
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
0 0
- mysql存储过程,游标嵌套,判断条件
- mysql存储过程示例(使用嵌套游标)
- mysql存储过程游标的嵌套
- MySQL游标嵌套循环存储过程
- MySQL游标嵌套循环存储过程
- mysql存储过程游标嵌套示例
- 存储过程 游标嵌套
- MySQL 存储过程游标嵌套,触发器调用存储过程
- mysql存储过程 例子 游标cursor 循环嵌套 事物
- Mysql存储过程循环内嵌套使用游标示例
- mysql存储过程 例子 游标cursor 循环嵌套 事物
- mysql存储过程 例子 游标cursor 循环嵌套 事物
- MYSQL存储过程,while循环和游标的嵌套使用
- 存储过程 游标嵌套循环
- oracle存储过程游标嵌套
- oracle 存储过程 游标嵌套
- 存储过程 嵌套游标问题
- mysql存储过程+游标
- 如果在VS2013创建WebService并在IIS中发布
- Linux下 JNI的使用
- 删除链表中倒数第n个节点
- dhtmlTree简单实例以及基本参数设置
- Maven Assembly插件介绍
- mysql存储过程,游标嵌套,判断条件
- 机器学习基础知识、与图像处理等技术的关系
- win7配置自己的IIS服务器亲自做的图文很详细
- MeshSharder学习记录
- Python 2.6.6的坑
- Linux-定时器创建
- virsual studio等AP无法访问网络映射驱动器(map network drive)的解决办法(二)
- 初始化的init
- iPad如何使用UIPopoverController