sql - 当表中的记录数是指定限制数的倍数时，如何使用 ref 游标获取集合？

Question

创建带有 id 列的表

CREATE TABLE TEST_TAB
  (ID NUMBER
  );

创建类型

CREATE type numbertabletype IS TABLE OF NUMBER;

插入 100 条记录

  INSERT INTO TEST_TAB
  SELECT LL FROM
    (SELECT LEVEL LL FROM DUAL CONNECT BY LEVEL<=100
    );

创建在 10 次循环中遍历 100 条记录的函数，游标将获取到具有 LIMIT 10 的数字表类型集合。

CREATE OR REPLACE FUNCTION LOOP_TEST
RETURN NUMBERTABLETYPE
IS
  lv_coll NUMBERTABLETYPE ;
  LV_COUNT NUMBER:=0;
  CURSOR c1
  IS
    SELECT ID FROM TEST_TAB WHERE ROWNUM<=100;
BEGIN
  OPEN c1;
  LOOP
    dbms_output.put_line('BEFORE FETCH CURSOR COUNT '||C1%ROWCOUNT);
    FETCH c1 bulk collect INTO lv_coll limit 10;
    dbms_output.put_line('AFTER FETCH CURSOR COUNT '||C1%ROWCOUNT);
    EXIT
  WHEN c1%NOTFOUND;
    LV_COUNT:=LV_COUNT+1;
    dbms_output.put_line(' BELOW NOT FOUND '||LV_COUNT);
    dbms_output.put_line('COLLECTION COUNT '||lv_coll.count);
  END LOOP;
  CLOSE c1;
  RETURN lv_coll;
END;
/

将sql作为脚本运行，rownum = 100时返回null

CURSOR c1
IS
  SELECT ID FROM TEST_TAB WHERE ROWNUM<=100;

将sql作为脚本运行，当rownum = 99时返回值，将函数中的光标c1替换为下面的光标。

CURSOR c1
IS
  SELECT ID FROM TEST_TAB WHERE ROWNUM<=99;

那么当限制是要循环的总记录的倍数时，您如何处理这种情况。

标签： sqloracleplsql

Answer 1

代码运行良好；它只是没有做你期望的事情。

当代码循环时，它将lv_coll每次覆盖集合，使其包含不超过 10 个项目。

当它获取第 91-100 行时，它会用 10 个项目填充集合并处理它，但是由于它没有尝试读取另一行，它不知道游标中没有更多行并且尚未达到c1%NOTFOUND条件终止循环。

当它重复循环之后，它会发现游标现在已经用尽，将读取零行。因此，在最后一个循环中，lv_coll集合不是NULL，而是包含零个元素的集合，这就是将要返回的内容。

将此与游标中只有 99 行的情况进行比较。当循环尝试读取第 91 - 第 100 行时，它将读取第 91 - 第 99 行并尝试读取第 100 行，但会发现游标已用尽，并且c1%NOTFOUND将退出循环并导致集合仅返回9 项。

如果要从游标返回所有元素，则需要使用第二个集合来聚合它们，因为从游标填充的元素将在每个循环中被覆盖：

CREATE OR REPLACE FUNCTION LOOP_TEST
RETURN NUMBERTABLETYPE
IS
  lv_coll  NUMBERTABLETYPE;
  all_items  NUMBERTABLETYPE := NUMBERTABLETYPE();
  LV_COUNT NUMBER:=0;
  ids      VARCHAR2(30);
  CURSOR c1
  IS
    SELECT ID FROM TEST_TAB WHERE ROWNUM<=100;
BEGIN
  OPEN c1;
  LOOP
    dbms_output.put_line('BEFORE FETCH CURSOR COUNT '||C1%ROWCOUNT);
    FETCH c1 bulk collect INTO lv_coll limit 10;
    dbms_output.put_line('AFTER FETCH CURSOR COUNT '||C1%ROWCOUNT);
    EXIT WHEN c1%NOTFOUND;
    LV_COUNT:=LV_COUNT+1;
    all_items := all_items MULTISET UNION ALL lv_coll;
    dbms_output.put_line(' BELOW NOT FOUND '||LV_COUNT);
    dbms_output.put_line('COLLECTION COUNT '||lv_coll.count);
--    SELECT LISTAGG(COLUMN_VALUE,',') WITHIN GROUP( ORDER BY ROWNUM )
--    INTO   ids
--    FROM   TABLE(lv_coll);
--    dbms_output.put_line('IDS: '||ids);
  END LOOP;
  CLOSE c1;
  RETURN all_items;
END;
/

可以使用以下方法调用：

DECLARE
  ids numbertabletype;
  vals VARCHAR2(3000);
BEGIN
  ids := LOOP_TEST();

  DBMS_OUTPUT.PUT_LINE( 'NUMBER OF IDs: ' || CASE WHEN ids IS NULL THEN 'NULL' ELSE TO_CHAR( ids.COUNT ) END );

  SELECT LISTAGG(COLUMN_VALUE,',') WITHIN GROUP( ORDER BY ROWNUM )
  INTO   vals
  FROM   TABLE(ids);

  DBMS_OUTPUT.PUT_LINE( 'values: ' || vals );
END;
/

db<>在这里摆弄