如何在SQL查询中使用Oracle关联数组 [英] How to use an Oracle Associative Array in a SQL query
问题描述
ODP.Net公开了将关联数组作为参数从C#传递到Oracle存储过程的能力.除非您试图在sql查询中使用该关联数组中包含的数据,否则它是一个很好的功能.
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.
这样做的原因是它需要上下文切换-SQL语句需要SQL类型,并且像这样传递给PL/SQL的关联数组实际上定义为PL/SQL类型.我相信在PL/SQL包/过程/函数中定义的任何类型都是PL/SQL类型,而在这些对象之外创建的类型是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).
所以,问题是,将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:
OPEN refCursor FOR
SELECT T.*
FROM SOME_TABLE T,
( SELECT COLUMN_VALUE V
FROM TABLE( associativeArray )
) T2
WHERE T.NAME = T2.V;
对于此示例而言,"associativeArray"是由PLS_INTEGER索引的varchar2(200)的简单表.在C#中,associativeArry参数填充有字符串[].
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[].
可以随意讨论使用关联数组之外的其他方法,但是提前知道这些解决方案将不被接受.不过,我仍然希望看到其他选项.
推荐答案
我将创建这样的数据库类型:
I would create a database type like this:
create type v2t as table of varchar2(30);
/
然后在过程中:
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;
(其中,databaseArray声明为v2t类型.)
(where databaseArray is declared to be of type v2t.)
这篇关于如何在SQL查询中使用Oracle关联数组的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持IT屋!