65.9K
CodeProject 正在变化。 阅读更多。
Home

使用 T-SQL 的整数数组

Chris_Martinez
starIconstarIconstarIconstarIcon
emptyStarIcon
starIcon

4.30/5 (30投票s)

2006年5月9日

6分钟阅读

viewsIcon

140669

downloadIcon

974

如何将整数数组传递给 SQL Server

前言

我使用 CodeProject 作为研究来源已经很多年了。我想是时候通过我自己的贡献回馈开发社区了。我还没有看到其他人采用这种传递数组的方式，所以我希望我的第一篇文章不是徒劳的。

引言

任何从事 SQL Server 一段时间的人无疑都遇到过需要一种方法将数组作为参数传递给存储过程的情况。这些情况中最常见的一种涉及需要执行的存储过程，该过程需要传递许多主键，而主键通常是基于整数的。当前，在 T-SQL 中，无法将传统数组从外部编程环境作为参数传递给存储过程或用户定义函数。一种克服这个问题的方法是在客户端使用所有文字值构建 SQL 语句。不幸的是，这种方法通常不可伸缩，并且容易受到注入攻击。我还见过 T-SQL 中的其他方法，所有这些方法都涉及创建分隔字符串并在服务器端进行拆分。虽然这确实有效，但只要参数是基于整数的，就可以使用一些T-SQL 神技以一种更具可重用性和效率的方式来执行此任务。

背景

数组不过是一块连续分配的内存。对于字符串数组，它实际上是数组的数组，其中每个字符串都是字符的数组。不幸的是，在 T-SQL 中，如果不使用动态 SQL 或分隔字符串方法，就无法表示这种类型的数据结构。但是，对于具有固定大小内存元素的数组（如整数）呢？事实证明，使用以下任何数据类型以原始二进制形式表示此类数据是可能的：BINARY、VARBINARY 和 IMAGE。由于 IMAGE 数据类型的最大值为 2GB，而在这种情况下从该类型变量中提取的最大片段小于 8000 字节，因此它成为最合适选择。

现在您知道可以在 T-SQL 中表示数组了，您可能会想知道如何访问它或使用它。事实证明，SUBSTRING 函数能够处理二进制数据。由于分割字符串不过是指针操作，因此这是完全有道理的。我们可以使用 DATALENGTH 函数来确定传递数据的实际长度，并将二进制数据的各个部分提取成适当大小的块，其中这些块可以以整数形式（分别为 TINYINT、SMALLINT、INT 和 BIGINT）使用，而无需执行任何数据类型转换。当然，整数的大小最终决定了数组实际上可以有多少个元素，但即使是 BIGINT 数组也可以拥有大约 256,000 个元素（2GB，每个元素 8 字节），这应该绰绰有余。

单个存储过程参数可以轻松地分成多个部分，但我们如何将其用作数组呢？答案是表值用户定义函数。本质上，二进制参数被传递到函数，分成各个部分，然后插入到内存表中。不可否认，这与传统的字符串分隔方法非常相似，除了它快得多，因为在分离值时不会进行字符搜索或类型转换。返回的表包含两列，Index 和 Value，它们的功能就像数组一样。您可能会发现不需要 Index 列，并且可以删除它以进一步提高性能。我假设可能会有一种情况，即需要通过其序数位置像普通数组一样访问元素。如果没有 Index 列，除非使用游标，否则将无法做到这一点。

为了简化解决方案，我创建了一个单一的、可重用的用户定义函数，名为 udfIntegerArray，它接受二进制形式的数组和数组元素的大小（以字节为单位，分别为 1、2、4 或 8）。

Using the Code

以下演示了如何在存储过程中使用该函数

CREATE PROCEDURE dbo.uspProductsSearch
(
 @ProductIDs IMAGE
)
AS
-- find all products in Northwind that match the specified list of identifiers
SELECT
  *
FROM
  Products
WHERE
  ProductID IN ( SELECT Value FROM dbo.udfIntegerArray( @ProductIDs, 4 ) );

您可以使用文字二进制值来模拟参数值。以下演示了如何用文字二进制形式表示 1、3 和 15，其中 Products.ProductID 是一个 32 位整数

-- it is not possible to declare a variable of type IMAGE, but you can pass
-- a VARBINARY variable that will implicitly cast to the IMAGE data type
DECLARE @ProductIDs VARBINARY(8000);
SET @ProductIDs = 0x00000001000000030000000F;

关注点

有趣的是，我发现 T-SQL 支持所有固有整数数据类型与其二进制等效项的相等性，从低级角度来看，这是完全可以接受的。这一发现很重要，因为它意味着只需要一个用户定义函数来支持所有整数数据类型，并且不需要显式类型转换。

客户端实现

我已经演示了如何在数据库端实现数组，但如何将数组分配给存储过程的二进制格式参数呢？令人惊讶的是，在 .NET 中这样做并不难，但为了简化任务，我创建了 BinaryIntegerArray 和 BinaryIntegerArrayFactory 类。BinaryIntegerArray 类是一个泛型类，它是整数的 List<T>，其中 T 是 System.Int16、System.UInt16、System.Int32、System.UInt32、System.Int64 或 System.UInt64。由于无法以限制可用类型的方式应用类型约束，因此构造函数被标记为内部，并且 BinaryIntegerArrayFactory 提供了将返回 BinaryIntegerArray 的适当实现的各种方法。

BinaryIntegerArray 类具有 List<T> 的所有功能，但增加了 ToBinary() 和 ToHexadecimal() 方法，分别以二进制或十六进制格式返回列表的内容。

请记住，虽然 BinaryIntegerArray 类支持无符号整数，但 SQL Server 不支持。使用这些实现中的任何一个向 SQL Server 提供数组都可能导致异常或意外结果。

整合

以下是从示例代码中摘录的一段，说明了如何创建一个整数数组并将二进制等效值分配给存储过程参数

// create a binary array of 32-bit integers
string cs = "Server=localhost;Database=Northwind;Integrated Security=SSPI";
BinaryIntegerArray<int> ids = BinaryIntegerArrayFactory.CreateInt32();

// add integers
ids.Add( 1 );
ids.Add( 3 );
ids.Add( 15 );

// create a connection
using ( SqlConnection connection = new SqlConnection( cs ) )
{
    // create a command
    using ( SqlCommand command = new SqlCommand( "dbo.uspProductsSearch", connection ) )
    {
        // set command type
        command.CommandType = CommandType.StoredProcedure;

        // add parameters
        SqlParameter param = command.Parameters.Add( "@ProductIDs", SqlDbType.Binary );
        param.Value = ids.ToBinary();

        // open connection and execute command
        connection.Open();
        SqlDataReader reader = command.ExecuteReader( CommandBehavior.CloseConnection );

        while ( reader.Read() )
        {
            // TODO: do something with the results
        }

        // close the reader
        reader.Close();
    }
}

结论

不幸的是，T-SQL 中的传统数组的实现将需要很长时间，甚至可能永远不会实现。希望我已经清楚地证明了，虽然不能使用传统方法，但如果元素是固定内存段，则可以以二进制形式传递数组。

备注

虽然这需要额外的努力，但我确实认为多语言示例对于吸引更广泛的受众至关重要。因此，提供的源代码和示例都包含 C# 和 VB.NET 的实现。

修订

  • 05/30/2006
    已将 VARBINARY(8000) 的实现替换为 IMAGE。一次只能从 IMAGE 数据类型中提取 8000 字节，而不使用 TEXTPTR 函数。由于任何元素的大小都不接近此值，因此 IMAGE 数据类型提供了更灵活的实现。相应的源 SQL 脚本已更新。
© . All rights reserved.