您在使用时不能*引用单个字段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,
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+ 中不再存在此限制
另外,如@乔纳尔斯 https://stackoverflow.com/users/409172/jonearles comments https://stackoverflow.com/questions/8169429/not-able-to-compile-pl-sql-with-bulk-collect-and-forall#comment-10044945,你可以只使用一条 SQL 语句......
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);