从PLSQL关联数组中选择? [英] Select from PLSQL Associative array?
问题描述
使用关联数组时是否可以使用SELECT FROM?我正在通过.NET应用程序将数组传递给存储过程,并且我希望能够从另一个表中进行选择时将该数组用作条件.可以说我要向该过程传递一个ID数组,我想能够做到这一点:
Is it possible to use SELECT FROM when using an associative array? I'm passing an array to a stored procedure through a .NET application, and I wanna be able to use that array as a condition when selecting from another table. Lets say I'm passing an array of IDs to the procedure, I wanna be able to do this:
select * from table1 where userID in (select column_value from array)
select * from table1 where userID in (select column_value from array)
数组的类型在包中定义:
The type for the array is defined in the package:
type id_array is type of number index by pls_integer
type id_array is type of number index by pls_integer
推荐答案
是的,可以通过使用流水线函数包装数组来实现.这是有关流水线函数的良好入门:
Yes, it is possible, by wrapping the array with a pipelined function. Here's a good primer on pipelined functions:
http://www.oracle-developer.net/display.php? id = 429
更新:现在,Oracle 12c现在支持使用TABLE运算符查询关联数组,只要该类型在程序包规范中声明即可:
UPDATE: Oracle 12c now supports querying associative arrays using the TABLE operator, as long as the type is declared in a package spec: https://galobalda.wordpress.com/2014/08/02/new-in-oracle-12c-querying-an-associative-array-in-plsql-programs/
例如
select * from table1
where userID in (select column_value from table(array));
这篇关于从PLSQL关联数组中选择?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!