Oracle 将集合 (或等效集合) 连接到表。

create or replace type numberList 
    is table of number;
/

create table COLLECTION_TEST (
    I number, 
    J number, 
    A varchar2(100) 
    );
/

insert into COLLECTION_TEST 
    (I, J, A) 
select 1, 1, 'Type One1' from dual
union
select 1, 2, 'Type One2' from dual
union
select 2, 1, 'Type Two1' from dual
union
select 2, 2, 'Type Two2' from dual
union
select 3, 1, 'Type Three1' from dual
union
select 3, 3, 'Type Three3' from dual
;
commit; 
/

declare 
    COLLECTION_TEST2 numberList;
begin 
    COLLECTION_TEST2 := numberList(); 
    
    COLLECTION_TEST2.extend;
    COLLECTION_TEST2(1) := 1;
    COLLECTION_TEST2.extend;
    COLLECTION_TEST2(2) := 2; 
    
/*
    select ct.A 
        collect or bulk collect into a collection 
    from COLLECTION_TEST ct 
    join COLLECTION_TEST2 ct2 
        on ct2 = ct.j;
*/
end;
/

drop table COLLECTION_TEST;
/

SQL> create or replace type numberList
  2      is table of number;
  3  /

Type created.

SQL>
SQL> create table COLLECTION_TEST (
  2      I number,
  3      J number,
  4      A varchar2(100)
  5      );

Table created.

SQL>
SQL> insert into COLLECTION_TEST
  2      (I, J, A)
  3  select 1, 1, 'Type One1' from dual
  4  union
  5  select 1, 2, 'Type One2' from dual
  6  union
  7  select 2, 1, 'Type Two1' from dual
  8  union
  9  select 2, 2, 'Type Two2' from dual
 10  union
 11  select 3, 1, 'Type Three1' from dual
 12  union
 13  select 3, 3, 'Type Three3' from dual
 14  ;

6 rows created.

SQL> commit;

Commit complete.

SQL> /

Commit complete.

SQL>
SQL> set serverout on
SQL> declare
  2      n numberList;
  3  begin
  4      n := numberList();
  5
  6      n.extend;
  7      n(1) := 1;
  8      n.extend;
  9      n(2) := 2;
 10
 11      for i in (
 12      select ct.A
 13      from COLLECTION_TEST ct
 14      join table(n) ct2
 15          on ct2.column_value = ct.j
 16      )
 17      loop
 18        dbms_output.put_line(i.a);
 19      end loop;
 20
 21  end;
 22  /
Type One1
Type One2
Type Two1
Type Two2
Type Three1

PL/SQL procedure successfully completed.