Usando parâmetros com valor de tabela

Baixar driver JDBC

Os parâmetros com valores de tabela fornecem uma forma fácil de escalar múltiplas linhas de dados de uma aplicação cliente para o SQL Server sem necessidade de múltiplas idas e voltas ou lógica especial do lado do servidor para processar os dados. Pode usar parâmetros de tabela para encapsular linhas de dados numa aplicação cliente e enviar os dados para o servidor num único comando parametrizado. As linhas de dados recebidas são armazenadas numa variável de tabela que pode depois ser operada através do Transact-SQL.

Os valores das colunas em parâmetros de tabela podem ser acedidos usando instruções padrão Transact-SQL SELECT. Os parâmetros de tabela são fortemente tipados e a sua estrutura é automaticamente validada. O tamanho dos parâmetros com valores de tabela é limitado apenas pela memória do servidor.

Observação

O suporte para Parâmetros com Valor de Tabela está disponível a partir do Microsoft JDBC Driver 6.0 para SQL Server.

Não podes devolver dados num parâmetro com valores de tabela. Os parâmetros com valores de tabela são apenas de entrada; a palavra-chave OUTPUT não é suportada.

Para mais informações sobre parâmetros com valores em tabelas, consulte os seguintes recursos.

Resource	Description
Parâmetros com Valor de Tabela (Motor de Base de Dados) em SQL Server Books Online	Descreve como criar e utilizar parâmetros com valores de tabela
Tipos de Tabela Definidos pelo Usuário em Livros Online do SQL Server	Descreve tipos de tabela definidos pelo utilizador que são usados para declarar parâmetros com valores de tabela

Passar várias linhas em versões anteriores do SQL Server

Antes da introdução dos parâmetros de valores de tabela no SQL Server 2008, as opções para passar várias linhas de dados para um procedimento armazenado ou um comando SQL parametrizado eram limitadas. Um programador pode escolher entre as seguintes opções para passar várias linhas ao servidor:

Use uma série de parâmetros individuais para representar os valores em múltiplas colunas e linhas de dados. A quantidade de dados que pode ser passada através deste método é limitada pelo número de parâmetros permitidos. Os procedimentos do SQL Server podem ter, no máximo, 2100 parâmetros. A lógica do lado do servidor é necessária para montar estes valores individuais numa variável de tabela ou numa tabela temporária para processamento.
Agrupe múltiplos valores de dados em cadeias delimitadas ou documentos XML e depois passe esses valores de texto para um procedimento ou instrução. Isto requer que o procedimento ou instrução inclua a lógica necessária para validar as estruturas de dados e desagregar os valores.
Crie uma série de instruções SQL individuais para modificações de dados que afetem múltiplas linhas. As alterações podem ser submetidas ao servidor individualmente ou agrupadas em grupos. No entanto, mesmo quando submetidos em lotes que contêm múltiplas instruções, cada instrução é executada separadamente no servidor.
Use o programa utilitário bcp ou SQLServerBulkCopy para carregar muitas linhas de dados numa tabela. Embora esta técnica seja eficiente, não suporta processamento do lado do servidor a menos que os dados sejam carregados numa tabela ou variável de tabela temporária.

Criação de tipos de parâmetros com valores de tabela

Os parâmetros de tabela baseiam-se em estruturas de tabela fortemente tipadas que são definidas utilizando declarações Transact-SQL CREATE TYPE. Tens de criar um tipo de tabela e definir a estrutura no SQL Server antes de poderes usar parâmetros de tabela nas tuas aplicações clientes. Para mais informações sobre como criar tipos de tabelas, consulte Tipos de Tabela Definidos pelo Usuário em SQL Server Books Online.

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

Depois de criar um tipo de tabela, pode declarar parâmetros de tabela com base nesse tipo. O seguinte fragmento Transact-SQL demonstra como declarar um parâmetro com valores de tabela numa definição de procedimento armazenado. A READONLY palavra-chave é necessária para declarar um parâmetro com valor em tabela.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Modificação de dados com parâmetros de tabela (Transact-SQL)

Parâmetros com valores de tabela podem ser usados em modificações de dados baseadas em conjuntos que afetam múltiplas linhas ao executar uma única instrução. Por exemplo, pode selecionar todas as linhas de um parâmetro com valor de tabela e inseri-las numa tabela de base de dados, ou pode criar uma instrução de atualização juntando um parâmetro com valor de tabela à tabela que pretende atualizar.

A seguinte instrução Transact-SQL UPDATE demonstra como usar um parâmetro com valores de tabela ao associá-lo à tabela Categorias. Quando usa um parâmetro de tabela com um JOIN numa cláusula FROM, deve também dar-lhe um alias, como mostrado aqui, onde o parâmetro de tabela é apelidado como "ec":

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

Este exemplo Transact-SQL demonstra como selecionar linhas de um parâmetro com valores de tabela para realizar um INSERT numa única operação baseada em conjuntos.

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

Limitações dos parâmetros com valores de tabela

Existem várias limitações aos parâmetros de valores de tabela:

Não podes passar parâmetros de valores de tabela para funções definidas pelo utilizador.
Os parâmetros de tabela só podem ser indexados para suportar restrições ÚNICAS ou CHAVE PRIMÁRIA. O SQL Server não mantém estatísticas sobre parâmetros com valores de tabela.
Os parâmetros com valores de tabela são de somente leitura no código Transact-SQL. Não podes atualizar os valores das colunas nas linhas de um parâmetro de tabela e não podes inserir ou eliminar linhas. Para modificar os dados que são passados para um procedimento armazenado ou para uma instrução parametrizada num parâmetro com valores de tabela, deve inserir os dados numa tabela temporária ou numa variável de tabela.
Não podes usar instruções ALTER TABLE para modificar o desenho dos parâmetros com valores de tabela.
Podes transmitir objetos grandes num parâmetro com valor de tabela.

Configuração de um parâmetro com valores de tabela

A partir do Microsoft JDBC Driver 6.0 para SQL Server, os parâmetros de valores de tabela são suportados com uma instrução parametrizada ou um procedimento armazenado parametrizado. Parâmetros com valores de tabela podem ser preenchidos a partir de uma SQLServerDataTable, de um ResultSet ou de uma implementação fornecida pelo utilizador da interface ISQLServerDataRecord. Ao definir um parâmetro com valor de tabela para uma consulta preparada, deve especificar um nome de tipo, que deve corresponder ao nome de um tipo compatível previamente criado no servidor.

Os dois fragmentos de código seguintes demonstram como configurar um parâmetro com valores de tabela com uma SQLServerPreparedStatement e com uma SQLServerCallableStatement para inserir dados. Aqui, sourceTVPObject pode ser uma SQLServerDataTable, ou um ResultSet ou um objeto ISQLServerDataRecord. Os exemplos assumem que a conexão é um objeto de Conexão ativo.

// 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();

Observação

Consulte a Secção API de Parâmetros de Valor de Tabela para o Driver JDBC abaixo para uma lista completa de APIs disponíveis para configurar parâmetros de valor de tabela.

Passar um parâmetro com valores de tabela como objeto SQLServerDataTable

A partir do Microsoft JDBC Driver 6.0 para SQL Server, a classe SQLServerDataTable representa uma tabela de dados relacionais em memória. Este exemplo demonstra como construir um parâmetro com valores de tabela a partir de dados em memória usando o objeto SQLServerDataTable. Primeiro, o código cria um objeto SQLServerDataTable, define o seu esquema e preenche a tabela com dados. O código configura então uma SQLServerPreparedStatement que passa esta tabela de dados como parâmetro com valor de tabela para o 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();

Este exemplo é semelhante ao anterior. A única diferença é que define o Nome do TVP no SQLServerDataTable, em vez de depender do casting PreparedStatement para a SQLServerPreparedStatement usar o método 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();

Observação

Consulte a Secção API para Parâmetro com Valores de Tabela do Driver JDBC abaixo para uma lista completa de APIs disponíveis para configurar o parâmetro com valores de tabela.

Passar um parâmetro com valores de tabela como objeto ResultSet

Este exemplo demonstra como transmitir linhas de dados de um ResultSet para um parâmetro com valor em tabela. Primeiro, o código recupera dados de uma tabela fonte num objeto SQLServerDataTable, define o seu esquema e preenche a tabela com dados. O código configura então uma SQLServerPreparedStatement que passa esta tabela de dados como parâmetro com valor de tabela para o 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();

Observação

Consulte a secção abaixo API de Parâmetros com Valores em Tabela para o Driver JDBC para uma lista completa das APIs disponíveis para definir o parâmetro valorado em tabela.

Passar um parâmetro com valor de tabela como objeto ISQLServerDataRecord

A partir do Microsoft JDBC Driver 6.0 para SQL Server, está disponível uma nova interface, ISQLServerDataRecord, para streaming de dados (dependendo de como o utilizador fornece a implementação) usando um parâmetro com valor de tabela. O exemplo seguinte demonstra como implementar a interface ISQLServerDataRecord e como a passar como um parâmetro com valores de tabela. Para simplificar, o exemplo seguinte passa apenas uma linha com valores codificados fixamente para o parâmetro com valores de tabela. Idealmente, o utilizador implementaria esta interface para transmitir linhas de qualquer fonte, por exemplo, a partir de ficheiros de texto.

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();

Observação

Veja abaixo a API de parâmetros com valores em tabela para o driver JDBC para uma lista completa de APIs disponíveis para definir o parâmetro com valores em tabela.

API de parâmetros de tabela para o driver JDBC

SQLServerMetaData

Esta classe representa metadados para uma coluna. É usado na interface ISQLServerDataRecord para passar metadados de colunas para o parâmetro com valor de tabela. Os métodos desta classe são:

Nome	Description
public SQLServerMetaData(String columnName, int sqlType, int precision, int scale, boolean useServerDefault, boolean isUniqueKey, SQLServerSortOrder sortOrder, int sortOrdinal)	Inicializa uma nova instância de SQLServerMetaData com o nome da coluna especificado, tipo SQL, precisão, escala e padrão do servidor. Esta forma do construtor suporta parâmetros com valores de tabela ao permitir especificar se a coluna é única no parâmetro de valor da tabela, na ordem de ordenação da coluna e no ordinal da coluna de ordenação. useServerDefault - especifica se esta coluna deve usar o valor padrão do servidor; O valor padrão é falso. isUniqueKey - indica se a coluna no parâmetro com valores da tabela é única; O valor padrão é falso. ordem de classificação - indica a ordem de classificação de uma coluna; O valor padrão é SQLServerSortOrder.Unspecified. sortOrdinal - especifica o ordinal da coluna de sort; sortOrdinal começa em 0; O valor padrão é -1.
public SQLServerMetaData(String columnName, int sqlType)	Inicializa uma nova instância de SQLServerMetaData usando o nome da coluna e o tipo sql.
public SQLServerMetaData(String columnName, int sqlType, int length)	Inicializa uma nova instância de SQLServerMetaData usando o nome da coluna, o tipo sql e o comprimento (para dados String). O comprimento é usado para diferenciar cadeias grandes de cadeias com comprimento inferior a 4000 caracteres. Introduzido na versão 7.2 do driver JDBC.
public SQLServerMetaData(String columnName, int sqlType, int precision, int scale)	Inicializa uma nova instância de SQLServerMetaData usando o nome da coluna, tipo sql, precisão e escala.
Public SQLServerMetaData(SQLServerMetaData sqlServerMetaData)	Inicializa uma nova instância de SQLServerMetaData a partir de outro objeto SQLServerMetaData.
Public String getColumName()	Recupera o nome da coluna.
public int getSqlType()	Recupera o tipo Java SQL.
public int getPrecision()	Recupera a precisão do tipo passado para a coluna.
public int getScale()	Recupera a escala do tipo passado para a coluna.
public SQLServerSortOrder getSortOrder()	Recupera a ordem de ordenação.
public int getSortOrdinal()	Recupera a ordem de classificação.
public boolean isUniqueKey()	Devolve se a coluna é única.
public boolean useServerDefault()	Devolve se a coluna usa o valor padrão do servidor.

SQLServerSortOrder

Um Enum que define a ordem de ordenação. Os valores possíveis são Ascendente, Descendente e Não especificado.

SQLServerDataTable

Esta classe representa uma tabela de dados em memória a ser utilizada com parâmetros com valores de tabela. Os métodos desta classe são:

Nome	Description
Public SQLServerDataTable()	Inicializa uma nova instância do SQLServerDataTable.
iterador públicoInteger, Object[]> getIterator()	Recupera um iterador nas linhas da tabela de dados.
public void addColumnMetadata(String columnName, int sqlType)	Adiciona metadados para a coluna especificada.
public void adicionarMetadadosDaColuna(SQLServerDataColumn column)	Adiciona metadados para a coluna especificada.
public void addRow(Object... valores)	Adiciona uma linha de dados à tabela de dados.
public Map<Integer, SQLServerDataColumn> getColumnMetadata()	Recupera metadados das colunas desta tabela de dados.
Limpeza de vazio público ()	Limpa esta tabela de dados.

SQLServerDataColumn

Esta classe representa uma coluna da tabela de dados em memória representada pelo SQLServerDataTable. Os métodos desta classe são:

Nome	Description
public SQLServerDataColumn(String columnName, int sqlType)	Inicializa uma nova instância de SQLServerDataColumn com o nome e tipo da coluna.
Public String getColumnName()	Recupera o nome da coluna.
public int getColumnType()	Recupera o tipo de coluna.

ISQLServerDataRecord

Esta classe representa uma interface que os utilizadores podem implementar para transmitir dados para um parâmetro com valores de tabela. Os métodos nesta interface são:

Nome	Description
public SQLServerMetaData getColumnMetaData(int column);	Recupera os metadados da coluna do índice indicado.
public int getColumnCount();	Recupera o número total de colunas.
public Object[] getRowData();	Recupera os dados da linha atual como um array de Objetos.
public boolean next();	Passa para a fila seguinte. Retorna verdadeiro se a jogada for bem-sucedida e houver uma próxima linha; caso contrário, retorna falso.

SQLServerPreparedStatement

Os seguintes métodos foram adicionados a esta classe para suportar a passagem de parâmetros com valores de tabela.

Nome	Description
public final void setStructured(int parameterIndex, String tvpName, SQLServerDataTable tvpDataTable)	Preenche um parâmetro com valores de tabela com uma tabela de dados. ParameterIndex é o índice de parâmetros, tvpName é o nome do parâmetro com valores na tabela, e tvpDataTable é o objeto da tabela de dados de origem.
public final void setStructured(int parameterIndex, String tvpName, ResultSet tvpResultSet)	Preenche um parâmetro com valores de tabela com um ResultSet recuperado de outra tabela. ParameterIndex é o índice de parâmetros, tvpName é o nome do parâmetro com valores de tabela e tvpResultSet é o objeto de conjunto de resultados de origem.
public final void setStructured(int parameterIndex, String tvpName, ISQLServerDataRecord tvpDataRecord)	Preenche um parâmetro com valores de tabela com um objeto ISQLServerDataRecord. O ISQLServerDataRecord é usado para transmitir dados e o utilizador decide como o utilizar. parameterIndex é o índice de parâmetros, tvpName é o nome do parâmetro com valores de tabela e tvpDataRecord é um objeto ISQLServerDataRecord.

SQLServerCallableStatement

Os seguintes métodos foram adicionados a esta classe para suportar a passagem de parâmetros com valores de tabela.

Nome	Description
public final void setStructured(String parameterName, String tvpName, SQLServerDataTable tvpDataTable)	Preenche um parâmetro de tipo tabela, passado para um procedimento armazenado, com uma tabela de dados. parameterName é o nome do parâmetro, tvpName é o nome do tipo TVP, e tvpDataTable é o objeto da tabela de dados.
public final void setStructured(String parameterName, String tvpName, ResultSet tvpResultSet)	Preenche um parâmetro com valor de tabela passado para um procedimento armazenado com um conjunto de resultados (ResultSet) recuperado de outra tabela. parameterName é o nome do parâmetro, tvpName é o nome do tipo TVP, e tvpResultSet é o objeto de conjunto de resultados fonte.
público final void setStructured(String parameterName, String tvpName, ISQLServerDataRecord tvpDataRecord)	Preenche um parâmetro com valor de tabela passado para um procedimento armazenado com um objeto ISQLServerDataRecord. O ISQLServerDataRecord é usado para transmitir dados e o utilizador decide como o utilizar. parameterName é o nome do parâmetro, tvpName é o nome do tipo TVP, e tvpDataRecord é um objeto ISQLServerDataRecord.

Consulte também

Visão geral do driver JDBC

Feedback

Esta página foi útil?

Last updated on 2026-01-16

Partilhar via

Usando parâmetros com valor de tabela

Passar várias linhas em versões anteriores do SQL Server

Criação de tipos de parâmetros com valores de tabela

Modificação de dados com parâmetros de tabela (Transact-SQL)

Limitações dos parâmetros com valores de tabela

Configuração de um parâmetro com valores de tabela

Passar um parâmetro com valores de tabela como objeto SQLServerDataTable

Passar um parâmetro com valores de tabela como objeto ResultSet

Passar um parâmetro com valor de tabela como objeto ISQLServerDataRecord

API de parâmetros de tabela para o driver JDBC

SQLServerMetaData

SQLServerSortOrder

SQLServerDataTable

SQLServerDataColumn

ISQLServerDataRecord

SQLServerPreparedStatement

SQLServerCallableStatement

Consulte também

Feedback

Recursos adicionais