oracle – 无法使用BULK COLLECT和FORALL编译PL / SQL

我在创建此过程时遇到错误.

CREATE OR replace PROCEDURE Remove_sv_duplicate
IS
  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

解决方法

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

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,portingtn
FROM   tt
WHERE  ROWID IN (SELECT ROWID
                 FROM   (SELECT ROWID,Trunc(
                                        createddate,'MI') ORDER BY portingtn) dup
                         FROM   tt)
                 WHERE  dup > 1);

（编辑：清远站长网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!