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种方式的优势将会凸显出来。