C#/ODP.NET：大 IN 子句解决方法

我们有一个 C# 组件，可以处理将任意大小的元素列表附加到半任意 SQL 的 IN 子句中SELECT查询。本质上这可以归结为接收类似的东西：

SELECT COUNT(*) FROM a WHERE b IN (...)

...其中“...”是允许组件修改的查询的唯一部分。

目前，该组件将插入一组以逗号分隔的命名绑定参数，然后将相应的 IDbDataParameter 对象附加到命令并执行；组件知道它必须绑定的参数的类型。这种方法效果很好，直到调用代码提供的参数集大于数据库愿意接受的参数集为止。这里的目标是让如此大的集合通过 ODP.NET 对 Oracle 11gR2 进行查询。

这项任务有些复杂，因为以下方法被制定要求的人认为是不可接受的：

• 全局临时表
• 存储过程
• 任何需要的东西CREATE TYPE已被处决

解决这一问题的方法并不需要只执行一个查询。

我试图通过使用来自其他地方的代码将子句绑定为数组来完成这项工作：

IList<string> values;

//...

OracleParameter parameter = new OracleParameter();
parameter.ParameterName = "parm";
parameter.DbType = DbType.String;
parameter.Value = values.ToArray();
int[] sizes = new int[values.Count];
for (int index = 0; index < values.Count; index++)
{
    sizes[index] = values[index].Length;
}
parameter.ArrayBindSize = sizes;

//...

该命令随后执行，不会引发异常，但 COUNT 返回的值为零（与预期值相比，在 SQLDeveloper 中使用嵌套的查询运行）SELECT返回相同的参数集）。到目前为止，浏览 ODP.NET 文档并没有带来任何乐趣。

对此的问题是：

• 有没有办法让上面的参数附件按预期工作？
• 是否有另一种可行的方法可以在不使用被否决的方法之一的情况下实现这一目标？

（我知道这类似于这个（未回答的）问题 https://stackoverflow.com/questions/21412243/passing-list-of-numbers-as-parameter-to-managed-odp-net-from-c-sharp，但该场景没有提到对方法有相同的限制。）

好吧，既然不允许使用全局临时表，那么至少可以创建普通表吗？如果是这样，这里有一个方法：

使用以下命令文本创建 OracleCommand 对象：

@"BEGIN
CREATE TABLE {inListTableName}
(
  inValue   {dbDataType}
)

INSERT INTO {inListTableName}(inValue) VALUES(:inValue);
END"

将命令对象上的 ArrayBindCount 设置为列表中所需的项目数。

Replace {inListTableName}与Guid.NewGuid().ToString().

更换{dbDataType}使用正确的 Oracle 数据类型来表示要在 in 子句中使用的值列表。

将 OracleParameter 添加到名为“inValue”的 OracleCommand 中，并将参数的值设置为包含 in 子句中所需值的数组。如果您有哈希集（我建议使用它以避免发送不必要的重复项），请使用.ToArray()就可以得到一个数组。

执行这个命令。这是您的准备命令。

然后使用以下 sql 片段作为 select sql 语句中 in 子句的值部分：(SELECT {inListTableName}.inValue FROM {inListTableName})

例如：

SELECT FirstName, LastName FROM Users WHERE UserId IN (SELECT {inListTableName}.inValue FROM {inListTableName});

执行此命令以获取读取器。

最后，还有一个命令，其命令文本如下：

DROP TABLE {inListTableName};

这是您的清理命令。执行这个命令。

您可能想要创建一个备用架构/用户来创建inListTable这样您就可以向用户授予适当的权限，使其仅在该架构中创建表。

所有这些都可以封装在具有以下接口的可重用类中：

public interface IInListOperation
{
    void    TransmitValueList(OracleConnection connection);
    string  GetInListSQLSnippet();
    void    RemoveValueList();
}

TransmitValueList将创建您的准备命令，添加参数并执行准备命令。

GetInListSQLSnippet只会返回(SELECT {inListTableName}.inValue FROM {inListTableName});

RemoveValueList清理干净。

该类的构造函数将采用值列表和 oracle db 数据类型，并生成inListTableName.

如果您可以使用全局临时表，我建议您不要创建和删除表。

编辑： 我想补充一点，如果您的条款涉及NOT IN列表或其他不等式运算符。以下面为例：

SELECT FirstName, LastName FROM Users WHERE Status == 'ACTIVE' OR UserID NOT IN (1,2,3,4,5,6,7,8,9,10);

如果使用拆分的方法NOT IN分开，你最终会得到无效的结果。以下对上一个示例进行划分的示例将返回所有用户，而不是除 UserId 1-10 之外的所有用户。

SELECT FirstName, LastName FROM Users WHERE UserID NOT IN (1,2,3,4,5)
UNION
SELECT FirstName, LastName FROM Users WHERE UserID NOT IN (6,7,8,9,10);