c# / ODP.NET:大型IN子句的解决方案

本文关键字：IN 子句 解决方案 大型 ODP NET | 更新日期: 2023-09-27 18:18:44

我们有一个c#组件来处理将任意大小的元素列表附加到半任意SQL SELECT查询的IN子句中。本质上，这可以归结为接收如下内容:

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

…其中"…"是查询中唯一允许组件修改的部分。

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

这个任务有点复杂，因为设置需求的人认为以下方法是不可接受的:

    全局临时表<<li>存储过程/gh>
  • 任何要求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文档还没有带来任何乐趣。

这个问题是:

  • 是否有办法使上述参数附件按预期工作?
  • 有没有其他可行的方法来实现这一目标，而不使用被否决的方法之一?

(我知道这与这个(未回答的)问题类似，但那个场景没有提到对方法有相同的限制。)

c# / ODP.NET:大型IN子句的解决方案

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

用以下命令文本创建oracleccommand对象:

@"BEGIN
CREATE TABLE {inListTableName}
(
  inValue   {dbDataType}
)
INSERT INTO {inListTableName}(inValue) VALUES(:inValue);
END"

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

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

用正确的oracle数据类型替换{dbDataType}，以便在in子句中使用值列表

添加一个OracleParameter到oracleccommand名为"inValue"，并将参数的值设置为一个数组，其中包含您在in子句中需要的值。如果您有一个Hashset(我建议使用它来避免发送不必要的重复)，请在其上使用.ToArray()来获得一个数组。

执行该命令。这是你的准备命令。

然后在select sql语句中使用以下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将创建您的prep命令，添加参数并执行prep命令。

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

RemoveValueList清理。

该类的构造函数将接受值列表和oracle数据库数据类型，并生成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);

也许这对于您正在进行的查询类型过于简单，但是您是否有任何理由不能将其拆分为几个查询并将结果组合在一起?

。让我们假设5个元素对于查询来说太多了…

select COUNT(*) from A where B in (1,2,3,4,5)

你会单独执行

select COUNT(*) from A where B in (1,2,3)
select COUNT(*) from A where B in (4,5)

，然后将这些结果相加。当然，你必须确保in-clause list是不同的，这样你就不会重复你的计数。

如果您可以这样做，如果您允许多个连接，则可以增加并行性的机会。
相关文章：