PL/SQL在永久循环上找不到数据错误

问题描述

在遍历数组时，没有发现数据错误.立即执行有数据，但是forall循环没有发现数据错误并且无法遍历集合.

请在下面找到代码. code_arr.FIRST似乎有问题.表有数据，执行的sql在编辑器上提供数据.你能帮忙吗?

create or replace PACKAGE TEST AS
  FUNCTION TEST RETURN NUMBER;
 END;


create or replace PACKAGE BODY TEST AS
  FUNCTION TEST RETURN NUMBER
    IS
        TYPE typ_varchar IS TABLE OF VARCHAR2 (1000) INDEX BY BINARY_INTEGER;
        lv_statement   VARCHAR2 (1000);
        code_arr       typ_varchar;
        var1 varchar(1000);
  BEGIN
        lv_statement :=    'SELECT  lnm.code FROM employee lnm';

        EXECUTE IMMEDIATE lv_statement BULK COLLECT
        INTO code_arr;

          FORALL ix1 IN code_arr.FIRST .. code_arr.LAST SAVE EXCEPTIONS
          SELECT code_arr(ix1) into var1 FROM DUAL;

        RETURN 1;
  END;
END;

预先感谢您的帮助.

马修

解决方案

FORALL用于批量DML，而不用于遍历数据.语法图显示:

begin
    for employees in
    (
        SELECT  lnm.code FROM employee lnm
    ) loop
        --Do something here.
        null;
    end loop;
end;
/

是DML的一种形式，尽管通常认为它与修改对象的命令是分开的.这可能就是为什么原始代码可以工作但在运行时而不是在编译时抛出错误的原因.

如果您需要做的就是遍历数据，则只需使用游标进行这样的遍历即可. Oracle自动为这些类型的循环使用批量收集:

begin
    for employees in
    (
        SELECT  lnm.code FROM employee lnm
    ) loop
        --Do something here.
        null;
    end loop;
end;
/

I am getting no data found error while looping over an array. The execute immediate has data, but the forall loop is giving no data found error and not able to iterate over the collection.

Please find the code below. code_arr.FIRST seems to have some issue. Table has data and executing sql gives data on editor. Could you please help.

create or replace PACKAGE TEST AS
  FUNCTION TEST RETURN NUMBER;
 END;


create or replace PACKAGE BODY TEST AS
  FUNCTION TEST RETURN NUMBER
    IS
        TYPE typ_varchar IS TABLE OF VARCHAR2 (1000) INDEX BY BINARY_INTEGER;
        lv_statement   VARCHAR2 (1000);
        code_arr       typ_varchar;
        var1 varchar(1000);
  BEGIN
        lv_statement :=    'SELECT  lnm.code FROM employee lnm';

        EXECUTE IMMEDIATE lv_statement BULK COLLECT
        INTO code_arr;

          FORALL ix1 IN code_arr.FIRST .. code_arr.LAST SAVE EXCEPTIONS
          SELECT code_arr(ix1) into var1 FROM DUAL;

        RETURN 1;
  END;
END;

Thanks in advance for your help.

Mathew

解决方案

FORALL is meant for bulk DML and not for looping through data. The syntax diagram shows this:

To be pedantic, SELECT is a form of DML, although it's usually considered separate from commands that modify objects. That might be why the original code sort of works but throws an error at run time instead of at compile time.

If all you need to do is loop through data, just use a cursor for loop like this. Oracle automatically uses bulk collect for these types of loops:

begin
    for employees in
    (
        SELECT  lnm.code FROM employee lnm
    ) loop
        --Do something here.
        null;
    end loop;
end;
/

这篇关于PL/SQL在永久循环上找不到数据错误的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持！