使用表值参数

下载 JDBC 驱动程序

表值参数提供了一将多行数据从客户端应用程序封送到 SQL Server 的种简单方法，而无需进行多次往返或特殊的服务器端逻辑来处理数据。 可使用表值参数来封装客户端应用程序中的数据行，并以单个参数化命令将数据发送到服务器。 传入数据行存储在随后可使用 Transact-SQL 进行操作的表变量中。

可以使用标准的 Transact-SQL SELECT 语句来访问表值参数中的列值。 表值参数为强类型，其结构会自动进行验证。 表值参数的大小仅受服务器内存的限制。

若要详细了解表值参数，请参阅以下资源。

在旧版 SQL Server 中传递多行

在 SQL Server 2008 中引入表值参数之前，用于将多行数据传递到存储过程或参数化 SQL 命令的选项受到限制。 开发人员可以选择下面的一种方法，将多行传递到服务器：

• 使用一系列单独的参数来表示多列和多行数据中的值。 使用这种方法可以传递的数据量受到允许使用的参数数量限制。 SQL Server 过程最多可以有 2100 个参数。 必须使用服务器端逻辑，将这些单独的值汇编到表变量或临时表中以供处理。

• 将多个数据值绑定到分隔字符串或 XML 文档，然后将这些文本值传递到过程或语句。 这要求过程或语句包含验证数据结构和解除绑定值所需的逻辑。

• 创建一系列单独的 SQL 语句，以执行影响多行的数据修改。 更改可以单独提交给服务器，也可以批量提交给组。 不过，即使是包含多个语句的批量提交，每个语句也是在服务器上单独执行。

• 使用 bcp 实用工具或 SQLServerBulkCopy 将多行数据加载到表中。 尽管这种技术很高效，但它不支持服务器端处理，除非将数据加载到临时表或表变量中。

创建表值参数类型

表值参数基于使用 Transact-SQL CREATE TYPE 语句定义的强类型表结构。 必须先在 SQL Server 中创建一个表类型并定义结构，才能在客户端应用程序中使用表值参数。 有关创建表类型的详细信息，请参阅 SQL Server 联机丛书中的用户定义的表类型。

CREATE TYPE dbo.CategoryTableType AS TABLE  
    ( CategoryID int, CategoryName nvarchar(50) )  

创建表类型后，可以根据相应类型来声明表值参数。 下面的 Transact-SQL 片段演示如何在存储过程定义中声明表值参数。 READONLY 是声明表值参数所必需的关键字。

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)  

使用表值参数修改数据 (Transact-SQL)

表值参数可用于基于集的数据修改，这些修改通过执行一条语句影响多行。 例如，可以选择表值参数中的所有行并将它们插入数据库表，也可以通过将表值参数联接到要更新的表来创建更新语句。

下面的 Transact-SQL UPDATE 语句演示如何通过将表值参数联接到 Categories 表来使用它。 在 FROM 子句中结合使用表值参数和 JOIN 时，还必须对表值参数使用别名。如下所示，表值参数的别名为“ec”：

UPDATE dbo.Categories  
    SET Categories.CategoryName = ec.CategoryName  
    FROM dbo.Categories INNER JOIN @tvpEditedCategories AS ec  
    ON dbo.Categories.CategoryID = ec.CategoryID;  

此 Transact-SQL 示例演示如何从表值参数中选择行以在单个基于集的操作中执行 INSERT。

INSERT INTO dbo.Categories (CategoryID, CategoryName)  
    SELECT nc.CategoryID, nc.CategoryName FROM @tvpNewCategories AS nc;  

表值参数的限制

表值参数有几个限制：

• 不能将表值参数传递给用户定义的函数。

• 表值参数只能通过被编制索引来支持 UNIQUE 或 PRIMARY KEY 约束。 SQL Server 不维护表值参数的统计信息。

• 在 Transact-SQL 代码中表值参数是只读的。 既不能更新表值参数行中的列值，也不能插入或删除行。 若要修改表值参数中传递到存储过程或参数化语句的数据，必须将数据插入临时表或表变量。

• 不能使用 ALTER TABLE 语句来修改表值参数的设计。

• 可以在表值参数中流式传输大型对象。

配置表值参数

自 Microsoft JDBC Driver 6.0 for SQL Server 起，开始支持将表值参数与参数化语句或参数化存储过程一起配合使用。 可以从 SQLServerDataTable、ResultSet 或用户提供的 ISQLServerDataRecord 接口实现填充表值参数。 如果为准备的查询设置表值参数，必须指定类型名称，此名称必须与先前在服务器上创建的兼容类型的名称一致。

下面两个代码片段展示了如何使用 SQLServerPreparedStatement 和 SQLServerCallableStatement 将表值参数配置为插入数据。 其中的 sourceTVPObject 可以是 SQLServerDataTable、ResultSet 或 ISQLServerDataRecord 对象。 这些示例假定连接是活动的 Connection 对象。

// Using table-valued parameter with a SQLServerPreparedStatement.  
SQLServerPreparedStatement pStmt =
    (SQLServerPreparedStatement) connection.prepareStatement("INSERT INTO dbo.Categories SELECT * FROM ?");  
pStmt.setStructured(1, "dbo.CategoryTableType", sourceTVPObject);  
pStmt.execute();  

// Using table-valued parameter with a SQLServerCallableStatement.  
SQLServerCallableStatement pStmt =
    (SQLServerCallableStatement) connection.prepareCall("exec usp_InsertCategories ?");
pStmt.setStructured(1, "dbo.CategoryTableType", sourceTVPObject);;  
pStmt.execute();  

将表值参数作为 SQLServerDataTable 对象传递

自 Microsoft JDBC Driver 6.0 for SQL Server 起，SQLServerDataTable 类开始表示关系数据的内存中表。 下面的示例展示了如何使用 SQLServerDataTable 对象从内存中数据构造表值参数。 首选，此代码创建 SQLServerDataTable 对象，定义它的架构，并使用数据填充表。 然后，此代码配置 SQLServerPreparedStatement，用于将此数据表作为表值参数传递到 SQL Server。

/* Assumes connection is an active Connection object. */

// Create an in-memory data table.  
SQLServerDataTable sourceDataTable = new SQLServerDataTable();
  
// Define metadata for the data table.  
sourceDataTable.addColumnMetadata("CategoryID" ,java.sql.Types.INTEGER);
sourceDataTable.addColumnMetadata("CategoryName" ,java.sql.Types.NVARCHAR);
  
// Populate the data table.  
sourceDataTable.addRow(1, "CategoryNameValue1");
sourceDataTable.addRow(2, "CategoryNameValue2");
  
// Pass the data table as a table-valued parameter using a prepared statement.  
SQLServerPreparedStatement pStmt =
        (SQLServerPreparedStatement) connection.prepareStatement(  
            "INSERT INTO dbo.Categories SELECT * FROM ?;");  
pStmt.setStructured(1, "dbo.CategoryTableType", sourceDataTable);  
pStmt.execute();  

此示例与前一个示例类似。 唯一的区别在于，它在 SQLServerDataTable 上设置 TVP 名称，而不是依赖将 PreparedStatement 转换为 SQLServerPreparedStatement 来使用 setStructured 方法。

/* Assumes connection is an active Connection object. */

// Create an in-memory data table.
SQLServerDataTable sourceDataTable = new SQLServerDataTable();
sourceDataTable.setTvpName("dbo.CategoryTableType");

// Define metadata for the data table.
sourceDataTable.addColumnMetadata("CategoryID" ,java.sql.Types.INTEGER);
sourceDataTable.addColumnMetadata("CategoryName" ,java.sql.Types.NVARCHAR);

// Populate the data table.
sourceDataTable.addRow(1, "CategoryNameValue1");
sourceDataTable.addRow(2, "CategoryNameValue2");

// Pass the data table as a table-valued parameter using a prepared statement.
PreparedStatement pStmt =
        connection.prepareStatement(
            "INSERT INTO dbo.Categories SELECT * FROM ?;");
pStmt.setObject(1, sourceDataTable);
pStmt.execute();

将表值参数作为 ResultSet 对象传递

下面的示例展示了如何将 ResultSet 中的数据行流式传输到表值参数。 首先，代码从 SQLServerDataTable 对象中的源表中检索数据，定义它的架构，并使用数据填充表。 然后，此代码配置 SQLServerPreparedStatement，用于将此数据表作为表值参数传递到 SQL Server。

/* Assumes connection is an active Connection object. */

// Create the source ResultSet object. Here SourceCategories is a table defined with the same schema as Categories table.
ResultSet sourceResultSet = connection.createStatement().executeQuery("SELECT * FROM SourceCategories");  

// Pass the source result set as a table-valued parameter using a prepared statement.  
SQLServerPreparedStatement pStmt =
        (SQLServerPreparedStatement) connection.prepareStatement(  
                "INSERT INTO dbo.Categories SELECT * FROM ?;");  
pStmt.setStructured(1, "dbo.CategoryTableType", sourceResultSet);  
pStmt.execute();  

将表值参数作为 ISQLServerDataRecord 对象传递

自 Microsoft JDBC Driver 6.0 for SQL Server 起，新接口 ISQLServerDataRecord 可用于使用表值参数流式传输数据（具体取决于用户如何实现它）。 下面的示例展示了如何实现 ISQLServerDataRecord 接口，以及如何将它作为表值参数传递。 为简单起见，下面的示例仅将包含硬编码值的一行传递给表值参数。 理想情况下，用户会实现此接口，用于流式传输任何源（例如，文本文件）中的行。

class MyRecords implements ISQLServerDataRecord  
{  
    int currentRow = 0;  
    Object[] row = new Object[2];  
  
    MyRecords(){  
        // Constructor. This implementation has just one row.
        row[0] = new Integer(1);  
        row[1] = "categoryName1";  
    }  
  
    public int getColumnCount(){  
        // Return the total number of columns, for this example it is 2.  
        return 2;  
    }  
  
    public SQLServerMetaData getColumnMetaData(int columnIndex) {  
        // Return the column metadata.  
        if (1 == columnIndex)  
            return new SQLServerMetaData("CategoryID", java.sql.Types.INTEGER);  
        else  
            return new SQLServerMetaData("CategoryName", java.sql.Types.NVARCHAR);  
    }  
  
    public Object[] getRowData(){  
        // Return the columns in the current row as an array of objects. This implementation has just one row.  
        return row;
    }  
  
    public boolean next(){  
        // Move to the next row. This implementation has just one row, after processing the first row, return false.  
        currentRow++;  
        if (1 == currentRow)  
            return true;  
        else  
            return false;  
    }
}

// Following code demonstrates how to pass MyRecords object as a table-valued parameter.  
MyRecords sourceRecords = new MyRecords();  
SQLServerPreparedStatement pStmt =
        (SQLServerPreparedStatement) connection.prepareStatement(  
                "INSERT INTO dbo.Categories SELECT * FROM ?;");  
pStmt.setStructured(1, "dbo.CategoryTableType", sourceRecords);  
pStmt.execute();  

JDBC 驱动程序的表值参数 API

SQLServerMetaData

此类表示列的元数据。 它在 ISQLServerDataRecord 接口中使用，以将列元数据传递到表值参数。 此类中的方法为：

SQLServerSortOrder

定义排序顺序的枚举。 可取值为 Ascending、Descending 和 Unspecified。

SQLServerDataTable

此类表示要与表值参数一起使用的内存中数据表。 此类中的方法为：

SQLServerDataColumn

此类表示由 SQLServerDataTable 表示的内存中数据表的列。 此类中的方法为：

ISQLServerDataRecord

此类表示用户可以实现的接口，用于将数据流式传输到表值参数。 此接口中的方法为：

SQLServerPreparedStatement

为了支持传递表值参数，已将下面的方法添加到此类。

SQLServerCallableStatement

为了支持传递表值参数，已将下面的方法添加到此类。

另请参阅

JDBC 驱动程序概述