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); (编辑:上饶站长网) 【声明】本站内容均来自网络,其相关言论仅代表作者个人观点,不代表本站立场。若无意侵犯到您的权利,请及时与联系站长删除相关内容! |
站长推荐
热点阅读