• Oracle 遍历游标的四种方式汇总(for、fetch、while、BULK COLLECT)


    本文原创:https://www.cnblogs.com/Marydon20170307/p/12869692.html 感谢博主分享

    注意:原文中方式四FORALL处有语法错误,应该使用FOR。

    1.情景展示

      Oracle 遍历游标的四种方式(for、fetch、while、bulk collect+forall)

    2.问题分析

      我们可以把游标想象成一张表,想要遍历游标,就要取到游标的每行数据,所以问题的关键就成了:如何取到行数据?

    3.解决方案

      方式一:FOR 循环(推荐使用)

      变形一:遍历显式游标

    /* 如果是在存储过程外使用显式游标,需要使用DECLARE关键字 */
    DECLARE
    /*创建游标*/
    CURSOR CUR_FIRST_INDEX IS
    SELECT A.ID A_ID, --一级指标ID
    A.INDEXNAME A_INDEXNAME --一级指标名称
    FROM INDEX_A A
    ORDER BY A_ID;
    /*定义游标变量,该变量的类型为基于游标CUR_FIRST_INDEX的行记录*/
    ROW_CUR_FIRST_INDEX CUR_FIRST_INDEX%ROWTYPE;
    /*游标处理*/
    BEGIN
    /*遍历显式游标*/
    --FOR 循环
    FOR ROW_CUR_FIRST_INDEX IN CUR_FIRST_INDEX LOOP
    --循环体
    DBMS_OUTPUT.PUT_LINE('{"ID":"' || ROW_CUR_FIRST_INDEX.A_ID || '","名称":"' || ROW_CUR_FIRST_INDEX.A_INDEXNAME || '"}');
    END LOOP;
    END;  

    执行,输出结果

       变形二:遍历隐式游标(推荐使用)

      for循环遍历游标,其实又可以分为两种方式,一种是显式游标的遍历,另一种是隐式游标的遍历。

    /* 如果是在存储过程外使用隐式游标,如果用不到变量无需声明DECLARE关键字 */
    /*游标处理*/
    BEGIN
    /*遍历隐式游标*/
    --FOR 循环
    FOR ROW_CUR_FIRST_INDEX IN (SELECT A.ID A_ID, --一级指标ID
    A.INDEXNAME A_INDEXNAME --一级指标名称
    FROM INDEX_A A
    ORDER BY A_ID) LOOP
    --循环体
    DBMS_OUTPUT.PUT_LINE('{"ID":"' || ROW_CUR_FIRST_INDEX.A_ID || '","名称":"' || ROW_CUR_FIRST_INDEX.A_INDEXNAME || '"}');
    END LOOP;
    END;

    隐式游标相较于显式游标用法更加简单,无需声明直接调用即可。    

      方式二:FETCH 循环

    /*游标声明代码和方式一一致,此处省略,直接展示游标处理代码*/
    BEGIN
    /*遍历游标*/
    --FETCH 循环
    OPEN CUR_FIRST_INDEX; --必须要明确的打开和关闭游标
    LOOP
    FETCH CUR_FIRST_INDEX INTO ROW_CUR_FIRST_INDEX;
    EXIT WHEN CUR_FIRST_INDEX%NOTFOUND;
    --循环体
    DBMS_OUTPUT.PUT_LINE('{"ID":"' || ROW_CUR_FIRST_INDEX.A_ID || '","名称":"' || ROW_CUR_FIRST_INDEX.A_INDEXNAME || '"}');
    END LOOP;
    CLOSE CUR_FIRST_INDEX;
    END;

    方式三:WHILE 循环

    /*游标声明代码和方式一一致,此处省略,直接展示游标处理代码*/
    BEGIN
    /*遍历游标*/
    OPEN CUR_FIRST_INDEX; --必须要明确的打开和关闭游标
    FETCH CUR_FIRST_INDEX
    INTO ROW_CUR_FIRST_INDEX;
    WHILE CUR_FIRST_INDEX%FOUND LOOP
    --循环体
    DBMS_OUTPUT.PUT_LINE('{"ID":"' || ROW_CUR_FIRST_INDEX.A_ID || '","名称":"' || ROW_CUR_FIRST_INDEX.A_INDEXNAME || '"}');
    FETCH CUR_FIRST_INDEX
    INTO ROW_CUR_FIRST_INDEX;
    END LOOP;
    CLOSE CUR_FIRST_INDEX;
    END;  

    注意:使用while循环时,需要fetch两次。

      方式四:BULK COLLECT+FORALL(速度最快)

    /* 如果是在存储过程外使用显示游标,需要使用DECLARE关键字 */
    /*声明游标*/
    DECLARE
    /*创建显式游标*/
    CURSOR CUR_FIRST_INDEX IS
    SELECT A.ID A_ID, --一级指标ID
    A.INDEXNAME A_INDEXNAME --一级指标名称
    FROM INDEX_A A
    ORDER BY A_ID;
    /*定义表类型,该表的表结构为游标CUR_FIRST_INDEX的行记录(可以存储多条游标记录)*/
    TYPE TABLE_CUR_FIRST_INDEX IS TABLE OF CUR_FIRST_INDEX%ROWTYPE;
    /* 声明表变量*/
    TAB_FIRST_INDEX TABLE_CUR_FIRST_INDEX;
    /*游标处理过程*/
    BEGIN
    /*遍历游标*/
    OPEN CUR_FIRST_INDEX;
    LOOP
    --将n行游标数据放到表中
    FETCH CUR_FIRST_INDEX BULK COLLECT
    INTO TAB_FIRST_INDEX LIMIT 1; -- 数据量太少,仅当前测试使用哦,实际开发建议 500 左右
    -- 退出条件
    EXIT WHEN TAB_FIRST_INDEX.COUNT = 0;
    --循环表数据
    FOR I IN TAB_FIRST_INDEX.FIRST .. TAB_FIRST_INDEX.LAST LOOP
    DBMS_OUTPUT.PUT_LINE('{"ID":"' || TAB_FIRST_INDEX(I).A_ID || '","名称":"' || TAB_FIRST_INDEX(I).A_INDEXNAME || '"}');
    END LOOP;
    END LOOP;
    CLOSE CUR_FIRST_INDEX;
    END;

    注意上面语句的FOR,原为写成了FORALL是不正确的(我被成功带到沟里才发现不对)。

    以下内容出自另一篇博客,更加全面详细可参考 https://www.cnblogs.com/hellokitty1/p/4584333.html 

    DECLARE
       CURSOR emp_cur IS
          SELECT empno, ename, hiredate FROM emp;
    
       TYPE emp_rec_type IS RECORD
       (
          empno      emp.empno%TYPE,
          ename      emp.ename%TYPE ,
          hiredate   emp.hiredate%TYPE
       );
       -- 定义基于记录的嵌套表
       TYPE nested_emp_type IS TABLE OF emp_rec_type;
       -- 声明集合变量
       emp_tab     nested_emp_type;
       -- 定义了一个变量来作为limit的值
       v_limit     PLS_INTEGER := 5;
       -- 定义变量来记录FETCH次数
       v_counter   PLS_INTEGER := 0;
    BEGIN
       OPEN emp_cur;
    
       LOOP
          -- fetch时使用了BULK COLLECT子句
          FETCH emp_cur
          BULK   COLLECT INTO emp_tab
          LIMIT v_limit; -- 使用limit子句限制提取数据量
    
          EXIT WHEN emp_tab.COUNT = 0; -- 注意此时游标退出使用了emp_tab.COUNT,而不是emp_cur%notfound
          v_counter   := v_counter + 1;  -- 记录使用LIMIT之后fetch的次数
    
          FOR i IN emp_tab.FIRST .. emp_tab.LAST
          LOOP
             DBMS_OUTPUT.PUT_LINE( '当前记录: '
                        ||emp_tab(i).empno||CHR(9)
                        ||emp_tab(i).ename||CHR(9)
                        ||emp_tab(i).hiredate);
          END LOOP;
       END LOOP;
    
       CLOSE emp_cur;
    
       DBMS_OUTPUT.put_line( '总共获取次数为:' || v_counter );
    END;

    4.总结

      使用for循环的优势在于:

      不需要手动打开&关闭游标(声明游标的开启和关闭);

      不需要手动捕获数据(自动将数据fetch到记录型变量);

      不需要关注何时要退出,也就是不需要写退出循环的满足条件(遍历完成就会退出)。

      第4方式与前3种的区别在于:

      前三种的游标变量:ROW_CUR_FIRST_INDEX,只能存储游标的一条数据;

      第四种的表变量:TAB_FIRST_INDEX,可以存储游标的多条数据。

      大数据批量处理的时候,第4种方式的优势将会凸显出来。

    凡哥,别他妈吹牛逼了
  • 相关阅读:
    easyui改变tab标题
    java获取request中的参数、java解析URL问号后的参数
    java生成word文档
    jquery即时获取上传文件input file文件名
    微信公众号开发(三)
    Linux中文乱码 更改Linux字符集
    微信公众号开发(五)
    NSCache
    MIT神技术绘制用户界面至任意物体
    导弹工厂到摩托车间:制造业如何应用大数据
  • 原文地址:https://www.cnblogs.com/sdlz/p/15556846.html
Copyright © 2020-2023  润新知