如何在 SQL 查询中使用 Oracle 关联数组
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1715978/
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
How to use an Oracle Associative Array in a SQL query
提问by ScottCher
ODP.Net exposes the ability to pass Associative Arrays as params into an Oracle stored procedure from C#. Its a nice feature unless you are trying to use the data contained within that associative array in a sql query.
ODP.Net 公开了将关联数组作为参数从 C# 传递到 Oracle 存储过程的能力。它是一个很好的功能，除非您尝试在 sql 查询中使用该关联数组中包含的数据。
The reason for this is that it requires a context switch - SQL statements require SQL types and an associative array passed into PL/SQL like this is actually defined as a PL/SQL type. I believe any types defined within a PL/SQL package/procedure/function are PL/SQL types while a type created outside these objects is a SQL type (if you can provide more clarity on that, please do but its not the goal of this question).
这样做的原因是它需要上下文切换——SQL 语句需要 SQL 类型，并且像这样传递给 PL/SQL 的关联数组实际上被定义为 PL/SQL 类型。我相信在 PL/SQL 包/过程/函数中定义的任何类型都是 PL/SQL 类型，而在这些对象之外创建的类型是 SQL 类型（如果您能对此提供更清晰的说明，请这样做，但这不是本文的目标）题）。
So, the question is, what are the methods you would use to convert the PL/SQL associative array param into something that within the procedure can be used in a sql statement like this:
所以，问题是，您将使用哪些方法将 PL/SQL 关联数组参数转换为在过程中可以在这样的 sql 语句中使用的内容：
OPEN refCursor FOR
SELECT T.*
FROM SOME_TABLE T,
( SELECT COLUMN_VALUE V
FROM TABLE( associativeArray )
) T2
WHERE T.NAME = T2.V;
For the purposes of this example, the "associativeArray" is a simple table of varchar2(200) indexed by PLS_INTEGER. In C#, the associativeArry param is populated with a string[].
就本示例而言，“associativeArray”是由 PLS_INTEGER 索引的 varchar2(200) 的简单表。在 C# 中，associativeArry 参数填充有 string[]。
Feel free to discuss other ways of doing this besides using an associative array but know ahead of time those solutions will not be accepted. Still, I'm interested in seeing other options.
除了使用关联数组之外，请随意讨论执行此操作的其他方法，但提前知道这些解决方案不会被接受。不过，我有兴趣看到其他选择。
回答by Tony Andrews
I would create a database type like this:
我会创建一个这样的数据库类型：
create type v2t as table of varchar2(30);
/
And then in the procedure:
然后在程序中：
FOR i IN 1..associativeArray.COUNT LOOP
databaseArray.extend(1);
databaseArray(i) := associativeArray(i);
END LOOP;
OPEN refCursor FOR
SELECT T.*
FROM SOME_TABLE T,
( SELECT COLUMN_VALUE V
FROM TABLE( databaseArray )
) T2
WHERE T.NAME = T2.V;
(where databaseArray is declared to be of type v2t.)
（其中 databaseArray 被声明为 v2t 类型。）
回答by MT0
You cannot use associative arrays in the SQL scope - they are only usable in the PL/SQL scope.
您不能在 SQL 范围内使用关联数组——它们只能在 PL/SQL 范围内使用。
One method is to map the associative array to a collection (which can be used in the SQL scope if the collection type has been defined in the SQL scope and not the PL/SQL scope).
一种方法是将关联数组映射到集合（如果集合类型已在 SQL 范围而非 PL/SQL 范围中定义，则可以在 SQL 范围中使用）。
SQL:
查询语句：
CREATE TYPE VARCHAR2_200_Array_Type AS TABLE OF VARCHAR2(200);
/
PL/SQL
PL/SQL
DECLARE
TYPE associativeArrayType IS TABLE OF VARCHAR2(200) INDEX BY PLS_INTEGER;
i PLS_INTEGER;
associativeArray associativeArrayType;
array VARCHAR2_200_Array_Type;
cur SYS_REFCURSOR;
BEGIN
-- Sample data in the (sparse) associative array
associativeArray(-2) := 'Test 1';
associativeArray(0) := 'Test 2';
associativeArray(7) := 'Test 3';
-- Initialise the collection
array := VARCHAR2_200_Array_Type();
-- Loop through the associative array
i := associativeArray.FIRST;
WHILE i IS NOT NULL LOOP
array.EXTEND(1);
array(array.COUNT) := associativeArray(i);
i := associativeArray.NEXT(i);
END LOOP;
-- Use the collection in a query
OPEN cur FOR
SELECT *
FROM your_table
WHERE your_column MEMBER OF array;
END;
/
