Oracle - 在使用多个值时的 CLAUSE 问题中，使其动态化
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/4455878/
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
Oracle - In CLAUSE question when using with multiple values, making it dynamic
提问by sarsnake
I just spent an hour on google and here trying to get a straight answer for how to do this in Oracle. What I need is the ability to use select in clause that constructed automatically such as
我刚刚在 google 上花了一个小时，在这里试图直接回答如何在 Oracle 中执行此操作。我需要的是在自动构造的子句中使用 select 的能力，例如
select col1 from table1 where id.table IN ('1','2','3');
where the id values are passed to the stored procedure inside the array. The associative array has been defined as such:
其中 id 值传递给数组内的存储过程。关联数组定义如下：
TYPE varchar_array_type IS TABLE OF VARCHAR2 (40) INDEX BY BINARY_INTEGER;
Is there a simple, concrete way to do that? thanks
有没有简单、具体的方法来做到这一点？谢谢
回答by Justin Cave
Unfortunately, if your collection type is defined in PL/SQL (rather than SQL), you cannot use it in SQL because the SQL engine doesn't know how to handle it.
不幸的是，如果您的集合类型是在 PL/SQL（而不是 SQL）中定义的，则不能在 SQL 中使用它，因为 SQL 引擎不知道如何处理它。
If instead you defined the collection type in SQL, i.e.
如果相反，您在 SQL 中定义了集合类型，即
CREATE TYPE varchar_tbl
IS TABLE OF varchar2(40);
Then you can do something like
然后你可以做类似的事情
SELECT col1
FROM table1 t1
WHERE t1.id IN (SELECT column_value
FROM TABLE( <<variable of type varchar2_tbl>> ) )
depending on the Oracle version-- the syntax for using collections in SQL has evolved over time-- older versions of Oracle had more complex syntax.
取决于 Oracle 版本——在 SQL 中使用集合的语法随着时间的推移而发展——旧版本的 Oracle 有更复杂的语法。
You can convert a PL/SQL associative array (your VARCHAR_ARRAY_TYPE) to a SQL nested table collection in PL/SQL, but that requires iterating through the associative array and filling the nested table, which is a bit of a pain. Assuming that the VARCHAR_TBLnested table collection has been created already
您可以将 PL/SQL 关联数组（您的 VARCHAR_ARRAY_TYPE）转换为 PL/SQL 中的 SQL 嵌套表集合，但这需要遍历关联数组并填充嵌套表，这有点麻烦。假设VARCHAR_TBL已经创建了嵌套表集合
SQL> CREATE OR REPLACE TYPE varchar_tbl
IS TABLE OF varchar2(40);
you can convert from the associative array to the nested table and use the nested table in a SQL statement like this (using the SCOTT.EMP table)
您可以将关联数组转换为嵌套表，并在这样的 SQL 语句中使用嵌套表（使用 SCOTT.EMP 表）
declare
type varchar_array_type
is table of varchar2(40)
index by binary_integer;
l_associative_array varchar_array_type;
l_index binary_integer;
l_nested_table varchar_tbl := new varchar_tbl();
l_cnt pls_integer;
begin
l_associative_array( 1 ) := 'FORD';
l_associative_array( 10 ) := 'JONES';
l_associative_array( 100 ) := 'NOT A NAME';
l_associative_array( 75 ) := 'SCOTT';
l_index := l_associative_array.FIRST;
while( l_index IS NOT NULL )
loop
l_nested_table.EXTEND;
l_nested_table( l_nested_table.LAST ) :=
l_associative_array( l_index );
l_index := l_associative_array.NEXT( l_index );
end loop;
SELECT COUNT(*)
INTO l_cnt
FROM emp
WHERE ename IN (SELECT column_value
FROM TABLE( l_nested_table ) );
dbms_output.put_line( 'There are ' || l_cnt || ' employees with a matching name' );
end;
Because converting between collection types is a bit of a pain, however, you would generally be better off just using the nested table collection (and passing that to the stored procedure) unless there is a particular reason that the associative array is needed.
但是，因为在集合类型之间转换有点麻烦，除非有特殊原因需要关联数组，否则您通常最好只使用嵌套表集合（并将其传递给存储过程）。
