oracle PL/SQL - 在 Where In 子句中使用“列表”变量
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/35231757/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
PL/SQL - Use "List" Variable in Where In Clause
提问by Bob Smith
In PL/SQL, how do I declare variable MyListOfValues that contains multiple values (MyValue1, MyValue2, etc.)
在 PL/SQL 中，如何声明包含多个值（MyValue1、MyValue2 等）的变量 MyListOfValues
SELECT *
FROM DatabaseTable
WHERE DatabaseTable.Field in MyListOfValues
I am using Oracle SQL Developer
我正在使用 Oracle SQL Developer
采纳答案by MT0
Use a collection:
使用集合：
CREATE TYPE Varchar2TableType AS TABLE OF VARCHAR2(200);
Or use a built-in type like SYS.ODCIVARCHAR2LISTor SYS.ODCINUMBERLIST:
或者使用内置类型，如SYS.ODCIVARCHAR2LISTor SYS.ODCINUMBERLIST：
VARIABLE cursor REFCURSOR;
DECLARE
your_collection SYS.ODCIVARCHAR2LIST := SYS.ODCIVARCHAR2LIST();
BEGIN
your_collection.EXTEND( 100 );
your_collection( 1) := 'Some value';
your_collection( 2) := 'Some other value';
-- ...
your_collection(100) := DBMS_RANDOM.STRING( 'x', 20 );
OPEN :cursor FOR
SELECT t.*
FROM your_table t
INNER JOIN
TABLE( your_collection ) c
ON t.id = c.COLUMN_VALUE;
END;
/
PRINT cursor;
回答by Lukas Eder
Create the SQL type like this:
像这样创建 SQL 类型：
CREATE TYPE MyListOfValuesType AS TABLE OF VARCHAR2(4000);
And then use it in a SQL statement
然后在SQL语句中使用
DECLARE
MyListOfValues MyListOfValuesType;
BEGIN
MyListOfValues := MyListOfValuesType('MyValue1', 'MyValue2');
FOR rec IN (
SELECT *
FROM DatabaseTable
WHERE DatabaseTable.Field in (
SELECT * FROM TABLE(MyListOfValues)
)
)
LOOP
...
END LOOP;
END;
Up until Oracle 11g, this only works with a SQL TABLEtype, not with a PL/SQL TABLEtype. With Oracle 12c, you could also use PL/SQL types.
直到 Oracle 11g，这仅适用于 SQLTABLE类型，不适用于 PL/SQLTABLE类型。使用 Oracle 12c，您还可以使用 PL/SQL 类型。
回答by Gary_W
How about using a WITH clause which basically builds a temp table? Not real reusable. You could use an array or I would argue joining to a lookup table would be better.
使用基本上构建临时表的 WITH 子句怎么样？不是真正的可重复使用。您可以使用数组，否则我认为加入查找表会更好。
WITH MyListOfValues(col1) AS (
select 'MyValue1' from dual union
select 'MyValue2' from dual union
select 'MyValue3' from dual
)
SELECT *
FROM DatabaseTable
WHERE Column in (
select col1
from MyListOfValues);
