oracle 遍历双重游标（动态游标）&三级关联查询&存储过程使用临时表返回游标
1.情景展示
根据第一个游标的数据产生第二个游标的数据，如何实现遍历？
三级关联查询如何实现？
存储过程如何使用临时表返回游标数据？
表结构展示
2.创建临时表
方式一：直接创建
/**
* 创建临时指标表
*/
CREATE GLOBAL TEMPORARY TABLE TAB_INDEX_TEMP(A_ID NUMBER,--一级指标ID
A_INDEXNAME VARCHAR2(50), --一级指标名称
A_TOTALWEIGHT NUMBER, --权重占比
B_ID NUMBER, --二级指标ID
B_INDEXNAME VARCHAR2(200), ----二级指标名称
B_INDEXSCORE NUMBER, --指标分值
B_COUNTER VARCHAR2(200), --计算方法
B_REMARK VARCHAR2(200),--备注
B_ISTOTAL VARCHAR2(2),--是否合计
B_ISCHILD VARCHAR2(2))ON COMMIT DELETE ROWS;/*事务提交时，清空临时表数据*/
方式二：动态创建
DECLARE
/* TABLE计数，用于判断临时表是否存在*/
V_TAB_COUNT NUMBER(1);
/* 用于创建临时表SQL */
V_TAB_SQL VARCHAR2(1000);
BEGIN
-- 1.创建临时表
SELECT COUNT(1)
INTO V_TAB_COUNT
FROM ALL_TABLES
WHERE TABLE_NAME = 'TAB_INDEX_TEMP';
--不存在就创建
IF V_TAB_COUNT = 0 THEN
V_TAB_SQL := 'CREATE GLOBAL TEMPORARY TABLE TAB_INDEX_TEMP(A_ID NUMBER,
A_INDEXNAME VARCHAR2(50),
A_TOTALWEIGHT NUMBER,
B_ID NUMBER,
B_INDEXNAME VARCHAR2(200),
B_INDEXSCORE NUMBER,
B_COUNTER VARCHAR2(200),
B_REMARK VARCHAR2(200),
B_ISTOTAL VARCHAR2(2),
B_ISCHILD VARCHAR2(2))';
V_TAB_SQL := V_TAB_SQL || ' ON COMMIT delete ROWS'; /*事务提交时，清空临时表数据*/
EXECUTE IMMEDIATE V_TAB_SQL; --执行SQL
END IF;
END;
说明：临时表类型采用事务类型，至于好处见文章底部链接。
也可以使用会话型临时表，只不过需要在每次插入前先清空表数据。
3.解决方案
三级关联查询思路：先查一级指标，根据一级查二级，判断二级查三级
CREATE OR REPLACE PROCEDURE PRC_INDEX_SEARCH(OUT_CURSOR OUT SYS_REFCURSOR) IS
/**
* 内容：三级指标关联查询
* 日期：2020/04/21
* 作者：MARYDON
* 流程：一级指标-->二级指标(合计除外)-->三级指标-->二级指标(只有合计)
*/
/*查询一级指标（一级指标下如果没有二级指标的话，是不会插入到临时表的）*/
CURSOR CUR_FIRST_INDEX IS
SELECT A.ID A_ID, --一级指标ID
A.INDEXNAME A_INDEXNAME --一级指标名称
FROM INDEX_A A
WHERE A.STATUS = 1 --一级指标处于启用状态
ORDER BY A_ID;
/*定义游标变量，该变量的类型为基于游标CUR_FIRST_INDEX的行记录*/
ROW_CUR_FIRST_INDEX CUR_FIRST_INDEX%ROWTYPE;
/*一级指标ID*/
V_A_ID INDEX_A.ID%TYPE;
/*查询一级指标对应的二级指标
*动态游标数据
*/
CURSOR CUR_SECOND_INDEX IS
SELECT A.ID A_ID, --一级指标ID
A.INDEXNAME A_INDEXNAME, --一级指标名称
A.TOTALWEIGHT A_TOTALWEIGHT, --权重占比
B.ID B_ID, --二级指标ID
B.INDEXNAME B_INDEXNAME, ----二级指标名称
B.INDEXSCORE B_INDEXSCORE, --指标分值
B.COUNTER B_COUNTER, --计算方法
B.REMARK B_REMARK, --备注
B.ISTOTAL B_ISTOTAL, --是否合计
B.ISCHILD B_ISCHILD --是否有子指标
FROM INDEX_B B, INDEX_A A
WHERE B.STATUS = 1 --二级指标处于启用状态（不用担心一级指标是否禁用问题）
AND B.INDEXTYPE = 2 --二级指标
AND B.ISTOTAL = 2 --非合计
AND B.INDEXA_ID = V_A_ID/*必须用变量*/
AND B.INDEXA_ID = A.ID --两表关联
ORDER BY B_ID;
/*定义游标变量，该变量的类型为基于游标CUR_SECOND_INDEX的行记录*/
ROW_CUR_SECOND_INDEX CUR_SECOND_INDEX%ROWTYPE;
/*定义子指标变量*/
V_ISCHILD INDEX_B.ISCHILD%TYPE;
BEGIN
-- 1.创建临时表
--2.遍历双重游标（一级指标）
/*2.1遍历第一层游标*/
FOR ROW_CUR_FIRST_INDEX IN CUR_FIRST_INDEX LOOP
--2.1.1一级指标ID 赋值（第二重游标数据的关键，V_A_ID变化，CUR_SECOND_INDEX数据也会随之改变）
V_A_ID := ROW_CUR_FIRST_INDEX.A_ID;
/*2.2一级指标对应的二级指标*/
FOR ROW_CUR_SECOND_INDEX IN CUR_SECOND_INDEX LOOP
--2.2.1将二重游标行数据插入临时表
INSERT INTO TAB_INDEX_TEMP
VALUES
(ROW_CUR_SECOND_INDEX.A_ID,
ROW_CUR_SECOND_INDEX.A_INDEXNAME,
ROW_CUR_SECOND_INDEX.A_TOTALWEIGHT,
ROW_CUR_SECOND_INDEX.B_ID,
ROW_CUR_SECOND_INDEX.B_INDEXNAME,
ROW_CUR_SECOND_INDEX.B_INDEXSCORE,
ROW_CUR_SECOND_INDEX.B_COUNTER,
ROW_CUR_SECOND_INDEX.B_REMARK,
ROW_CUR_SECOND_INDEX.B_ISTOTAL,
ROW_CUR_SECOND_INDEX.B_ISCHILD);
--2.2.2指标分值 赋值
V_ISCHILD := ROW_CUR_SECOND_INDEX.B_ISCHILD;
--2.2.3插入三级指标（1说明有子指标）
IF V_ISCHILD = 1 THEN
INSERT INTO TAB_INDEX_TEMP
SELECT A.ID,
A.INDEXNAME,
A.TOTALWEIGHT,
B.ID B_ID,
B.INDEXNAME,
B.INDEXSCORE,
B.COUNTER,
B.REMARK,
B.ISTOTAL,
B.ISCHILD
FROM INDEX_B B, INDEX_A A
WHERE B.STATUS = 1 --三级指标启用
AND B.INDEXB_ID = ROW_CUR_SECOND_INDEX.B_ID --三级指标的父级ID
AND B.INDEXA_ID = A.ID --两表关联
ORDER BY B_ID;
END IF;
END LOOP;
/*2.3插入二级指标合计行（既可以查询也可以自己手动添加合计行）*/
INSERT INTO TAB_INDEX_TEMP
SELECT A.ID,
A.INDEXNAME,
A.TOTALWEIGHT,
B.ID,
B.INDEXNAME,
B.INDEXSCORE,
B.COUNTER,
B.REMARK,
B.ISTOTAL,
B.ISCHILD
FROM INDEX_B B, INDEX_A A
WHERE B.STATUS = 1 --二级指标处于启用状态（不用担心一级指标是否禁用问题）
AND B.INDEXTYPE = 2 --二级指标
AND B.ISTOTAL = 1 --合计项
AND B.INDEXA_ID = V_A_ID
AND B.INDEXA_ID = A.ID --两表关联
ORDER BY B.ID;
END LOOP;
--3.返回最终数据
OPEN OUT_CURSOR FOR
--查询临时表数据作为游标结果集
SELECT * FROM TAB_INDEX_TEMP;
--不能也不用清空临时表
/*COMMIT;*/
END PRC_INDEX_SEARCH;
第一步：第一个游标CUR_FIRST_INDEX，取每行数据的A_ID当作变量V_A_ID的值，这样当你的V_A_ID发生变化时，第二个游标CUR_SECOND_INDEX的数据也会发生变化；
第二步：遍历双层游标即可。
说明：
1.变量V_A_ID必须得用，如果在定义第二个游标的时候直接用第一个游标的行数据代替，
虽然可以编译成功，但是，将永远查不到数据。
所以，当第二个游标的数据依赖于第一个游标的数据时，必须用变量来代替。
2.Oracle的if语句判断值是否相等，不同于Java和js，使用的是一个等号=；
3. 另外，表示elseif的关键词也与那两种语言不同，使用的是ELSIF，少一个E；
4.表示赋值的用，:=；
5.不等于使用，<>；
6.在存储过程里，指定变量的数据类型时，可以动态指定，也就是
一整行数据的数据类型：表名%ROWTYPE，如：ROW_INDEX_A INDEX_A%ROWTYPE;；
某列的数据类型：表名.字段名%TYPE，如：V_A_ID INDEX_A.ID%TYPE;。
4.结果展示
在plsql中选中存储过程，右键--》测试--》F8执行--》右下角游标--》点开查看结果集
层级说明：
----》----》二级
----》----》----》----》三级
----》----》合计
----》----》二级
。。。依此顺序循环查询输出
写在最后
哪位大佬如若发现文章存在纰漏之处或需要补充更多内容，欢迎留言！！！
相关推荐：
本文来自博客园，作者：Marydon，转载请注明原文链接：https://www.cnblogs.com/Marydon20170307/p/12809995.html
