如何在 SQL 查询中使用 Oracle 关联数组

2021-11-30 00:00:00 sql associative-array oracle plsql odp.net

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

相关文章