C#/ODP.NET : large IN clause workaround

Question

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

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

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

目前该组件将插入一组以逗号分隔的命名绑定(bind)参数，然后将相应的 IDbDataParameter 对象附加到命令并执行；该组件知道它必须绑定(bind)的参数的类型。这很有效，直到调用代码提供的参数集大于数据库愿意接受的参数集。这里的目标是通过 ODP.NET 获得如此大的数据集来处理针对 Oracle 11gR2 的查询。

由于以下方法被那些设定要求的人认为是 Not Acceptable ，因此这项任务有些复杂:

• 全局临时表
• 存储过程
• 任何需要执行CREATE TYPE的东西

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

我正在尝试通过使用从其他地方获取的代码将子句绑定(bind)为一个数组来完成这项工作:

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 文档并没有带来任何乐趣。

这方面的问题是:

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

(我知道这类似于 this (unanswered) question ，但该场景并未提及对方法有相同的限制。)

Answer 1

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

使用以下命令文本创建一个 OracleCommand 对象:

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

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

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

将 {inListTableName} 替换为 Guid.NewGuid().ToString()。

将 {dbDataType} 替换为您要在 in 子句中使用的值列表的正确 oracle 数据类型。

将一个 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，以便您可以向您的用户授予适当的权限，以便仅在该模式中创建表。

所有这些都可以封装在具有以下接口(interface)的可重用类中:

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 部分的方法，您最终会得到无效的结果。以下除 UserIds 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);