加入收藏 | 设为首页 | 会员中心 | 我要投稿 上饶站长网 (https://www.0793zz.com.cn/)- 数据库平台、视觉智能、智能搜索、决策智能、迁移!
当前位置: 首页 > 站长百科 > 正文

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

发布时间:2021-03-06 13:12:37 所属栏目:站长百科 来源:网络整理
导读:我在创建此过程时遇到错误. 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

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

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

解决方法

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

(编辑:上饶站长网)

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

    热点阅读