sql - SQL 语句中不允许的本地集合类型 - Oracle

Question

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: local collection types not allowed in SQL statements

我不能像这样从插入查询中获取 r01_ids 的值吗？如果我错了，请有人帮我找出问题所在以及如何以正确的方式执行。

Answer 1

直到Oracle 11g，如果在PLSQL block 的范围内声明type，则不能在 block 内的SQL语句中调用这就是您收到此错误的原因:

PLS-00642: local collection types not allowed in SQL statements

您可以在 PLSQL block 的 SCOPE 之外创建一个类型，然后在 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;