java游标遍历放到对象_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执行--》右下角游标--》点开查看结果集

层级说明：

----》----》二级

----》----》----》----》三级

----》----》合计

----》----》二级

。。。依此顺序循环查询输出

写在最后

哪位大佬如若发现文章存在纰漏之处或需要补充更多内容，欢迎留言！！！

相关推荐：