无法使用BULK COLLECT和FORALL编译PL/SQL [英] Not able to compile PL/SQL with BULK COLLECT and FORALL

问题描述

创建此过程时出现错误提示.

I am getting below error while creating this procedure.

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,
                     spid,
                     nnsp,
                     onsp,
                     portingtn)
        (SELECT referencekey,
                spid,
                nnsp,
                onsp,
                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-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错误的原因.

You cannot* reference individual fields when you're using FORALL - that's why you get the PLS-00436 error.

要解决此问题，您将必须使用关联数组来引用单个 字段.

To get around this, you will have to make use of associative arrays to refer individual fields.

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,
                                      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 tt_aa LIMIT 1000;

        FORALL i IN 1..tt_aa.COUNT
          INSERT INTO soa_temp_sv_refkey_fordelete
                      (referencekey,
                       spid,
                       nnsp,
                       onsp,
                       portingtn)
          (SELECT referencekey,
                  spid,
                  nnsp,
                  onsp,
                  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

In addition, as @jonearles comments, you could just use a single SQL statement....

INSERT INTO soa_temp_sv_refkey_fordelete
            (referencekey,
             spid,
             nnsp,
             onsp,
             portingtn)
SELECT referencekey,
       spid,
       nnsp,
       onsp,
       portingtn
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); 

这篇关于无法使用BULK COLLECT和FORALL编译PL/SQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！