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执行--》右下角游标--》点开查看结果集

  层级说明:

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

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

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

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

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

写在最后

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

 相关推荐:

 

posted @ 2020-04-30 17:19  Marydon  阅读(1874)  评论(2编辑  收藏  举报