如何在光标的选择语句中传递逗号分隔值 where 子句

Question

我有以下块，该块具有光标和选择查询。我想传递select的输出，逗号分隔为光标的选择语句中的Whewher子句。我知道下面的代码会由于在声明部分中的SQL查询而引发错误，但是我如何使用数组或集合来实现此目标。

在这里，ID列是号码

代码段：

declare
test varchar2(30);
SELECT LISTAGG(value, ', ') WITHIN GROUP (ORDER BY value2) into test from table3 where value2=12;
cursor c1 (select * from table where id in (test))
begin
for i in c1 loop
null;
end loop;
end;

Answer 1

你为什么要这样做？

您可以简单地写入您的选择为：

Select * from table where id in (select value from table3 where value2=12)

编辑：

您还需要打开光标C1，以使其工作afaik。

Answer 2

您可以使用集合和MEMBER OF操作员：

Oracle设置：

CREATE TYPE IntegerList  AS TABLE OF NUMBER(8,0);
/
CREATE TABLE table1 ( value, value2 ) AS
SELECT 1, 1 FROM DUAL UNION ALL
SELECT 3, 4 FROM DUAL UNION ALL
SELECT 5, 3 FROM DUAL UNION ALL
SELECT 7, 2 FROM DUAL;
CREATE TABLE table2 ( id, value ) AS
SELECT 1, 11 FROM DUAL UNION ALL
SELECT 2, 22 FROM DUAL UNION ALL
SELECT 3, 33 FROM DUAL UNION ALL
SELECT 7, 77 FROM DUAL;

pl/sql ：

DECLARE
  test IntegerList;
  c1 SYS_REFCURSOR;
BEGIN
  SELECT value
  BULK COLLECT INTO test
  FROM   table1;
  FOR r IN ( SELECT * FROM table2 WHERE id MEMBER OF test ) LOOP
    DBMS_OUTPUT.PUT_LINE( r.id || ', ' || r.value );
  END LOOP;
END;
/

输出：

1, 11
3, 33
7, 77

db＆lt;>小提琴