MySQL 存储过程，游标双重循环

最新推荐文章于 2024-07-30 03:53:23 发布

AlexKate

最新推荐文章于 2024-07-30 03:53:23 发布

阅读量979

点赞数 2

分类专栏： MySQl 文章标签：存储过程游标 MySQl函数双重游标循环

本文链接：https://blog.csdn.net/AlexKate/article/details/90232799

版权

MySQl 专栏收录该内容

2 篇文章 0 订阅

订阅专栏

-- 等价于JAVA双重for循环
CREATE DEFINER=`root`@`%` PROCEDURE `ref_view_rp_jk_day_1`(IN time VARCHAR(20))
BEGIN

DECLARE ename VARCHAR(100);
DECLARE starttime VARCHAR(100);
DECLARE  endtime  VARCHAR(100);

DECLARE  standardele  DOUBLE;

DECLARE  id BIGINT(10);
SET starttime=NOW();

DECLARE sdostep VARCHAR(255);
DECLARE sfcode INT;
DECLARE sdeptid BIGINT;
DECLARE stask VARCHAR(255);

DECLARE row_deptid BIGINT;
DECLARE row_fcode INT;
DECLARE row_startime VARCHAR(20);
DECLARE row_endtime VARCHAR(20);
DECLARE row_tenantid BIGINT;

DECLARE row_sys VARCHAR(50);
DECLARE row_reason VARCHAR(255);
DECLARE row_area VARCHAR(50);
DECLARE row_deptname VARCHAR(50);
DECLARE row_gztype VARCHAR(50);
DECLARE done INT;

DECLARE edone INT;

BEGIN
-- 定义游标
DECLARE rs_cursor CURSOR FOR
-- 外取数据的源
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done=1;

OPEN rs_cursor; 
cursor_loop:LOOP
FETCH rs_cursor INTO row_deptid,row_fcode,row_startime,row_endtime,row_tenantid,row_sys,row_reason,row_area,row_deptname,row_gztype; -- 取数据
IF done=1 THEN
		leave cursor_loop;-- 跳出循环
ELSE
		SET edone = 0;-- 重置标识
			IF THEN

				BEGIN
					-- 定义游标
					DECLARE rs_cursor1 CURSOR FOR
					-- 内取数据的源
					DECLARE CONTINUE HANDLER FOR NOT FOUND SET edone=1;

					OPEN rs_cursor1;
					cursor_loop1:LOOP
					FETCH rs_cursor1 INTO sfcode,sdostep,sdeptid,stask; -- 取数据
					IF edone=1 THEN
						leave cursor_loop1;
					ELSE
						IF  THEN
							
						END IF;
					END IF;
					END LOOP cursor_loop1;
					-- 内层for循环外操作
					IF  THEN

					END IF;
					
					CLOSE rs_cursor1;
				END;
			END IF;
		END IF;
		END LOOP cursor_loop;
		CLOSE rs_cursor;

	END;

SET endtime=NOW();
SET ename='rp_jk_day';
INSERT INTO view_records_event(ename,starttime,endtime) VALUES(ename,starttime,endtime);

END