SET SERVEROUTPUT ON;
DECLARE
type r05_id_type is TABLE of r01_table.R01_ID%TYPE;
r01_ids r05_id_type;
BEGIN
SELECT r1.r01_ID as R01_ID
BULK COLLECT INTO r01_ids
FROM r01_table r1, r05_table r5
WHERE r1.r01_ID= r5.r05_R01_ID_FK
AND r5.r05_status='D' AND r5.r05_date_time_captured <= TRUNC(SYSDATE) - 1095
AND r1.r01_id NOT IN(select r01.r01_ID
FROM r01_table r01, r05_table r05
WHERE r05.r05_status !='D'
AND r01.r01_ID= r05.r05_R01_ID_FK);
dbms_output.put_line(r01_ids.COUNT);
FOR indx IN 1 .. r01_ids.COUNT
LOOP
dbms_output.put_line(r01_ids(indx));
insert all into r01_table_archived (R01_ID_TYPE,R01_IDENTITY_NUMBER,R01_PASSPORT_COUNTRY,R01_DATE_TIME_CAPTURED)
values(rr1_R01_ID_TYPE,rr1_R01_IDENTITY_NUMBER,rr1_R01_PASSPORT_COUNTRY,rr1_R01_DATE_TIME_CAPTURED)
select rr1_R01_ID_TYPE,rr1_R01_IDENTITY_NUMBER,rr1_R01_PASSPORT_COUNTRY,rr1_R01_DATE_TIME_CAPTURED
FROM (SELECT R01_ID_TYPE,R01_IDENTITY_NUMBER,R01_PASSPORT_COUNTRY,R01_DATE_TIME_CAPTURED
FROM r01_table
WHERE r01_id IN (r01_ids));
END LOOP;
END;
我正在编写上面的过程,以便检查两个表之间的某些记录,并将匹配的ID(
r01_ID
)存储在内部表调用r01_ids
中。dbms_output.put_line(r01_ids(indx))
打印正确的值,指示这些值已正确插入到r01_ids
。但是,当我尝试将值插入到LOOP中的r01_table_archived表中时,就会发生问题。
我收到以下错误
PLS-00642:SQL语句中不允许使用本地集合类型
我不能像这样从插入查询中获取
r01_ids
的值吗?如果我错了,请有人帮助我找出问题所在以及如何以正确的方式执行。 最佳答案
在Oracle 11g
之前,如果在PLSQL块的作用域内声明type
,则无法在该块内的SQL
语句中调用它,这是导致出现此错误的原因:
PLS-00642: local collection types not allowed in SQL statements
您可以在
SCOPE
块的PLSQL
之外创建类型,然后在SQL
中使用它。见下文:--Created a type of Number assuming ID is number
CREATE OR REPLACE TYPE R05_ID_TYPE IS TABLE OF NUMBER;
/
--Block
DECLARE
--TYPE R05_ID_TYPE IS TABLE OF R01_TABLE.R01_ID%TYPE;
R01_IDS R05_ID_TYPE;
BEGIN
SELECT R1.R01_ID AS R01_ID
BULK COLLECT INTO R01_IDS
FROM R01_TABLE R1,
R05_TABLE R5
WHERE R1.R01_ID = R5.R05_R01_ID_FK
AND R5.R05_STATUS = 'D'
AND R5.R05_DATE_TIME_CAPTURED <= TRUNC (SYSDATE) - 1095
AND R1.R01_ID NOT IN (
SELECT R01.R01_ID
FROM R01_TABLE R01,
R05_TABLE R05
WHERE R05.R05_STATUS != 'D'
AND R01.R01_ID = R05.R05_R01_ID_FK);
DBMS_OUTPUT.PUT_LINE (R01_IDS.COUNT);
FOR INDX IN 1 .. R01_IDS.COUNT
LOOP
DBMS_OUTPUT.PUT_LINE (R01_IDS (INDX) );
INSERT
INTO R01_TABLE_ARCHIVED
(R01_ID_TYPE,
R01_IDENTITY_NUMBER,
R01_PASSPORT_COUNTRY,
R01_DATE_TIME_CAPTURED)
SELECT RR1_R01_ID_TYPE,
RR1_R01_IDENTITY_NUMBER,
RR1_R01_PASSPORT_COUNTRY,
RR1_R01_DATE_TIME_CAPTURED
FROM (SELECT R01_ID_TYPE,
R01_IDENTITY_NUMBER,
R01_PASSPORT_COUNTRY,
R01_DATE_TIME_CAPTURED
FROM R01_TABLE
--Using the collection in SQL statement
WHERE R01_ID IN (Select column_value from table(R01_IDS) );
END LOOP;
COMMIT;
END;