我在创建此过程时遇到错误.
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);
