从 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)

数组的类型在包中定义:

The type for the array is defined in the package:

<代码>类型 id_array 是 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 运算符查询关联数组，只要类型在包规范中声明:https://galobalda.wordpress.com/2014/08/02/new-in-oracle-12c-querying-an-associative-array-in-plsql-programs/

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屋！