Oracle SQL 中的数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1401801/
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
Arrays in Oracle SQL
提问by Thorsten
Here's a simplified pseudo-code version of what I'd like to be able to do in PL-SQL (Oracle):
这是我希望能够在 PL-SQL (Oracle) 中执行的操作的简化伪代码版本：
DECLARE
mylist as ARRAY
BEGIN
mylist (1) := '1'
mylist (2) := '3'
...
SELECT *
FROM aTable
WHERE aKey IN mylist;
END;
The SELECT should return the matching records for mylist(1), mylist(2) etc. It should be similar to ORing all the values, but of course we don't know in advance how many values we get.
SELECT 应该返回 mylist(1)、mylist(2) 等的匹配记录。它应该类似于对所有值进行 ORing，但当然我们事先不知道我们得到了多少个值。
How can I achieve this? I know that PL/SQL has some collection datatypes, but I can't seem to get them to work properly in SQL statements.
我怎样才能做到这一点？我知道 PL/SQL 有一些集合数据类型，但我似乎无法让它们在 SQL 语句中正常工作。
Thanks for any ideas.
感谢您的任何想法。
回答by APC
This is easy to do with the TABLE()function. The one catch is that the array variable must use a type declared in SQL. This is because SELECTuses the SQL engine, so PL/SQL declarations are out of scope.
使用该TABLE()函数很容易做到这一点。一个问题是数组变量必须使用在 SQL 中声明的类型。这是因为SELECT使用了 SQL 引擎，所以 PL/SQL 声明超出了范围。
SQL> create or replace type numbers_nt as table of number
2 /
Type created.
SQL>
SQL> declare
2 l_array numbers_nt;
3 begin
4 l_array := numbers_nt (7521,7566,7654);
5 for r in ( select ename
6 from emp
7 where empno in ( select *
8 from table (l_array)
9 )
10 )
11 loop
12 dbms_output.put_line ( 'employee name = '||r.ename);
13 end loop;
14 end;
15 /
employee name = PADFIELD
employee name = ROBERTSON
employee name = BILLINGTON
PL/SQL procedure successfully completed.
SQL>
回答by cagcowboy
A couple of suggestions:
几个建议：
1.) There's a CAST SQL keyword that you can do that might do the job... it makes your collection be treated as if it were a table.
1.) 您可以使用 CAST SQL 关键字来完成这项工作……它使您的集合被视为一个表。
2.) Pipelined functions. Basically a function returns data that looks like a table.
2.) 流水线函数。基本上，函数返回看起来像表格的数据。
This link summarises the options and has a number of code listings that explain them.
此链接总结了这些选项，并提供了许多解释它们的代码清单。
