oracle – 无法使用BULK COLLECT和FORALL编译PLSQL

概述我在创建此过程时遇到错误. CREATE OR replace PROCEDURE Remove_sv_duplicateIS TYPE sv_bulk_collect IS TABLE OF tt%ROWTYPE; sv_rec SV_BULK_COLLECT; CURSOR cur_data IS SELECT * FROM tt WHERE 我在创建此过程时遇到错误.

CREATE OR replace PROCEDURE Remove_sv_duplicateIS  TYPE sv_bulk_collect    IS table OF tt%rOWTYPE;  sv_rec SV_BulK_ColLECT;  CURSOR cur_data IS    SELECT *    FROM   tt    WHERE  ROWID IN (SELECT ROWID                     FROM   (SELECT ROWID,Row_number () over (PARTITION BY portingtn,nnsp,onsp,spID,Trunc(                                            createddate,'MI') ORDER BY portingtn) dup                             FROM   tt)                     WHERE  dup > 1);BEGIN  OPEN cur_data;  LOOP      FETCH cur_data BulK ColLECT INTO sv_rec liMIT 1000;      FORALL i IN 1..sv_rec.COUNT        INSERT INTO soa_temp_sv_refkey_fordelete                    (referencekey,portingtn)        (SELECT referencekey,portingtn         FROM   tt         WHERE  portingtn = Sv_rec(i).portingtn                AND spID = Sv_rec(i).spID                AND nnsp = Sv_rec(i).nnsp                AND onsp = Sv_rec(i).onsp                AND svID IS NulL);      EXIT WHEN cur_data%notfound;  END LOOP;  CLOSE cur_data;  COMMIT;END;

程序

Error(23,5): PL/sql: sql Statement ignored  Error(25,27): PLS-00382: Expression is of wrong type  Error(25,27): PLS-00436: implementation restriction: cannot reference fIElds of BulK In-BIND table of records  Error(26,27): PLS-00382: Expression is of wrong type   Error(27,27): PLS-00436: implementation restriction: cannot reference fIElds of BulK In-BIND table of records        Error(27,27): PLS-00382: Expression is of wrong type   Error(28,27): PL/sql: ORA-22806: not an object or REF    Error(28,27): PLS-00382: Expression is of wrong type  Error(28,27): PLS-00436: implementation restriction: cannot reference fIElds of BulK In-BIND table of records

解决方法 当您使用FORALL时,您不能*引用单个字段 – 这就是您收到PLS-00436错误的原因.

要解决这个问题,您必须使用关联数组来引用个体
领域.

DECLARE    TYPE tt_rectype IS RECORD (      referencekey tt.referencekey%TYPE,spID tt.spID%TYPE,nnsp tt.hiredate%TYPE,onsp tt.deptno%TYPE,portingtn tt.portingtn%TYPE);    TYPE tt_aa_type      IS table OF TT_RECTYPE INDEX BY PLS_INTEGER;    tt_aa TT_AA_TYPE;    CURSOR cur_data IS      SELECT *      FROM   tt      WHERE  ROWID IN (SELECT ROWID                       FROM   (SELECT ROWID,Trunc(                                              createddate,'MI') ORDER BY portingtn) dup                               FROM   tt)                       WHERE  dup > 1);BEGIN    OPEN cur_data;    LOOP        FETCH cur_data BulK ColLECT INTO tt_aa liMIT 1000;        FORALL i IN 1..tt_aa.COUNT          INSERT INTO soa_temp_sv_refkey_fordelete                      (referencekey,portingtn)          (SELECT referencekey,portingtn           FROM   tt           WHERE  portingtn = Tt_aa(i).portingtn                  AND spID = Tt_aa(i).spID                  AND nnsp = Tt_aa(i).nnsp                  AND onsp = Tt_aa(i).onsp                  AND svID IS NulL);        EXIT WHEN cur_data%notfound;    END LOOP;    CLOSE cur_data;    COMMIT;END;

*请注意Oracle 11g中不再存在此限制

另外,作为@ jonearles comments,您可以使用单个SQL语句….

INSERT INTO soa_temp_sv_refkey_fordelete            (referencekey,portingtn)SELECT referencekey,portingtnFROM   ttWHERE  ROWID IN (SELECT ROWID                 FROM   (SELECT ROWID,Trunc(                                        createddate,'MI') ORDER BY portingtn) dup                         FROM   tt)                 WHERE  dup > 1);

总结

以上是内存溢出为你收集整理的oracle – 无法使用BULK COLLECT和FORALL编译PL / SQL全部内容，希望文章能够帮你解决oracle – 无法使用BULK COLLECT和FORALL编译PL / SQL所遇到的程序开发问题。

如果觉得内存溢出网站内容还不错，欢迎将内存溢出网站推荐给程序员好友。