游标是否一次取出所有记录

我们可以用一个有错误的查询来验正：

DROP TABLE SUBTEST PURGE;
CREATE TABLE SUBTEST AS
SELECT '1' AS NUM,'A' AS FLAG FROM dual UNION ALL
SELECT '2' AS NUM,'B' AS FLAG FROM dual UNION ALL
SELECT '3' AS NUM,'B' AS FLAG FROM dual UNION ALL
SELECT '2B' AS NUM,'B' AS FLAG FROM dual UNION ALL
SELECT '3' AS NUM,'C' AS FLAG FROM dual;

对于这个表，如果用下面的查询一次取出所有数据，肯定会直接报错：

SQL> SELECT flag, to_number(num) num FROM subtest;
SELECT flag, to_number(num) num FROM subtest
ORA-01722: invalid number

那在游标里呢？

SQL> set serveroutput on
SQL> 
SQL> DECLARE
  2    i INTEGER := 0;
  3    v_SQLERRM  VARCHAR2(1000);
  4  BEGIN
  5    FOR cur IN (SELECT flag, to_number(num) num FROM subtest) LOOP
  6      i := i + 1;
  7      dbms_output.put_line(i);
  8    END LOOP;
  9  EXCEPTION
 10    WHEN OTHERS THEN
 11      v_SQLERRM:=SQLERRM;
 12      dbms_output.put_line(v_SQLERRM || ': i = ' || i);
 13  END;
 14  /
1
2
3
ORA-01722: invalid number: i = 3
PL/SQL procedure successfully completed

可以看到循环到第四次才报错，也就是说fetch时才会取里面的数据。