我如何将多个值收集到一个数组中，然后使用IN子句的数组

Question

我需要在Oracle 10g/11g的存储过程中这样做:

SELECT COLUM1 INTO SOME_ARRAY FROM TAB1 WHERE COLUMN2=P_COL2;

然后我想在另一个带有in子句的查询中使用SOME_ARRAY，像这样

SELECT * FROM TAB2 WHERE TAB2.COLUMN1 IN SOME_ARRAY

可以不创建TYPE吗?也许是动态数组?只要写一点代码就可以了

Answer 1

让我们创建一个Type作为Oracle对象，作为PL/SQL和SQL的引用

CREATE TYPE SOME_ARRAY_REC AS OBJECT
(
   COLUM1 VARCHAR2(100)
);
/

现在，DB对象为上面创建的类型nested table

CREATE TYPE SOME_ARRAY_TAB AS TABLE OF SOME_ARRAY_REC;
/

PL/SQL

DECLARE
      PL_ARRAY_REC TAB1%ROWTYPE;
      TYPE PL_ARRAY_TAB IS TABLE OF PL_ARRAY_REC  
                INDEX BY PLS_INTEGER;
      v_PL_ARRAY PL_ARRAY_TAB;
      v_ARRAY SOME_ARRAY_TAB := SOME_ARRAY_TAB();
BEGIN
      /* Bulk collect the records into local PL/SQL type */
      SELECT COLUM1 
      BULK COLLECT INTO v_PL_ARRAY
      FROM TAB1 WHERE COLUMN2=P_COL2;
      /* To use it in SQL we have to use a SQL compatible Type*/
      /* So copy the contents from local type to SQL compatible type */
      FOR I IN 1..v_PL_ARRAY.COUNT
      LOOP
           v_ARRAY.EXTEND;
           v_ARRAY(I) := SOME_ARRAY_REC(v_PL_ARRAY(I).COLUM1);
      END LOOP;
      /* Use TABLE() to cast the SQL compatible Nested table as normal table */
      SELECT * BULK COLLECT INTO SOME_VARRAY 
      FROM TAB2
      WHERE COLUMN1 IN (SELECT COLUM1 FROM TABLE(v_ARRAY));
END;
/

PS:我更喜欢SubQuery