Share via

Parameters met tabelwaarden gebruiken

JDBC-stuurprogramma downloaden

Parameters met tabelwaarden bieden een eenvoudige manier om meerdere rijen met gegevens van een clienttoepassing naar SQL Server te gebruiken zonder dat er meerdere retouren of speciale logica aan de serverzijde nodig zijn voor het verwerken van de gegevens. U kunt parameters met tabelwaarden gebruiken om rijen met gegevens in een clienttoepassing in te kapselen en de gegevens naar de server te verzenden in één geparameteriseerde opdracht. De binnenkomende gegevensrijen worden opgeslagen in een tabelvariabele die vervolgens kan worden uitgevoerd met behulp van Transact-SQL.

Kolomwaarden in tabelwaardeparameters kunnen worden geopend met behulp van standaard-Transact-SQL SELECT-instructies. Parameters met tabelwaarden worden sterk getypt en hun structuur wordt automatisch gevalideerd. De grootte van parameters met tabelwaarden wordt alleen beperkt door servergeheugen.

Opmerking

Ondersteuning voor Table-Valued Parameters is beschikbaar vanaf Microsoft JDBC-stuurprogramma 6.0 voor SQL Server.

U kunt geen gegevens retourneren in een tabelwaardeparameter. Parameters met tabelwaarden zijn alleen-invoer; het trefwoord OUTPUT wordt niet ondersteund.

Zie de volgende resources voor meer informatie over parameters met tabelwaarden.

Hulpbron Description
Tabelgewaarde Parameters (Databasemotor) in SQL Server Books Online Hierin wordt beschreven hoe u parameters met tabelwaarden maakt en gebruikt
Door de gebruiker gedefinieerde Tabeltypen in SQL Server Books Online Beschrijft door de gebruiker gedefinieerde tabeltypen die worden gebruikt om parameters met tabelwaarden te declareren

Meerdere rijen doorgeven in eerdere versies van SQL Server

Voordat parameters met tabelwaarden werden geïntroduceerd in SQL Server 2008, waren de opties voor het doorgeven van meerdere rijen met gegevens aan een opgeslagen procedure of een geparameteriseerde SQL-opdracht beperkt. Een ontwikkelaar kan kiezen uit de volgende opties voor het doorgeven van meerdere rijen aan de server:

  • Gebruik een reeks afzonderlijke parameters om de waarden in meerdere kolommen en rijen met gegevens weer te geven. De hoeveelheid gegevens die met deze methode kan worden doorgegeven, wordt beperkt door het aantal toegestane parameters. SQL Server-procedures kunnen maximaal 2100 parameters hebben. Logica aan de serverzijde is vereist om deze afzonderlijke waarden samen te stellen in een tabelvariabele of een tijdelijke tabel voor verwerking.

  • Bundel meerdere gegevenswaarden in gescheiden tekenreeksen of XML-documenten en geef deze tekstwaarden vervolgens door aan een procedure of instructie. Hiervoor moet de procedure of instructie de logica bevatten die nodig is voor het valideren van de gegevensstructuren en het loskoppelen van de waarden.

  • Maak een reeks afzonderlijke SQL-instructies voor gegevenswijzigingen die van invloed zijn op meerdere rijen. Wijzigingen kunnen afzonderlijk of in batches naar de server worden verzonden. Zelfs wanneer deze in batches worden ingediend die meerdere instructies bevatten, wordt elke instructie echter afzonderlijk uitgevoerd op de server.

  • Gebruik het hulpprogramma bcp of SQLServerBulkCopy om veel rijen met gegevens in een tabel te laden. Hoewel deze techniek efficiënt is, biedt deze geen ondersteuning voor verwerking aan de serverzijde, tenzij de gegevens in een tijdelijke tabel of tabelvariabele worden geladen.

Parametertypen met tabelwaarde maken

Parameters met tabelwaarden zijn gebaseerd op sterk getypte tabelstructuren die worden gedefinieerd met behulp van Transact-SQL CREATE TYPE instructies. U moet een tabeltype maken en de structuur definiëren in SQL Server voordat u parameters met tabelwaarden in uw clienttoepassingen kunt gebruiken. Zie User-Defined Tabeltypen in SQL Server Books Online voor meer informatie over het maken van tabeltypen.

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

Nadat u een tabeltype hebt gemaakt, kunt u parameters met tabelwaarden declareren op basis van dat type. In het volgende Transact-SQL fragment ziet u hoe u een tabelwaardeparameter declareert in een opgeslagen proceduredefinitie. Het READONLY trefwoord is vereist voor het declareren van een parameter met tabelwaarde.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Gegevens wijzigen met parameters met tabelwaarden (Transact-SQL)

Parameters met tabelwaarden kunnen worden gebruikt in op set gebaseerde gegevenswijzigingen die van invloed zijn op meerdere rijen door één instructie uit te voeren. U kunt bijvoorbeeld alle rijen in een tabelwaardeparameter selecteren en deze invoegen in een databasetabel, of u kunt een update-instructie maken door een parameter met tabelwaarde toe te voegen aan de tabel die u wilt bijwerken.

De volgende Transact-SQL UPDATE-instructie laat zien hoe u een parameter met tabelwaarde gebruikt door deze toe te voegen aan de tabel Categorieën. Wanneer u een tabelwaardeparameter gebruikt met een JOIN in een FROM-component, moet u deze ook aliasen, zoals hier wordt weergegeven, waarbij de parameter met tabelwaarde als 'ec' wordt aangeduid:

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

In dit Transact-SQL voorbeeld ziet u hoe u rijen uit een tabelwaardeparameter selecteert om een INSERT uit te voeren in één setbewerking.

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

Beperkingen van parameters met tabelwaarde

Er zijn verschillende beperkingen voor parameters met tabelwaarden:

  • U kunt parameters met tabelwaarden niet doorgeven aan door de gebruiker gedefinieerde functies.

  • Parameters met tabelwaarden kunnen alleen worden geïndexeerd ter ondersteuning van beperkingen van UNIEKE of PRIMAIRE SLEUTELS. SQL Server onderhoudt geen statistieken over parameters met tabelwaarden.

  • Tabelwaardeparameters zijn read-only in Transact-SQL-code. U kunt de kolomwaarden in de rijen van een tabelwaardeparameter niet bijwerken en u kunt geen rijen invoegen of verwijderen. Als u de gegevens wilt wijzigen die worden doorgegeven aan een opgeslagen procedure of geparameteriseerde instructie in een tabelwaardeparameter, moet u de gegevens invoegen in een tijdelijke tabel of in een tabelvariabele.

  • U kunt ALTER TABLE-instructies niet gebruiken om het ontwerp van parameters met tabelwaarden te wijzigen.

  • U kunt grote objecten streamen in een tabelwaardeparameter.

Een parameter met tabelwaarde configureren

Vanaf Microsoft JDBC-stuurprogramma 6.0 voor SQL Server worden parameters met tabelwaarden ondersteund met een geparameteriseerde instructie of een opgeslagen procedure. Parameters met tabelwaarden kunnen worden ingevuld vanuit een SQLServerDataTable, vanuit een ResultSet of van een gebruiker die de ISQLServerDataRecord-interface heeft geïmplementeerd. Wanneer u een parameter met tabelwaarde instelt voor een voorbereide query, moet u een typenaam opgeven die overeenkomt met de naam van een compatibel type dat eerder op de server is gemaakt.

De volgende twee codefragmenten laten zien hoe u een parameter met tabelwaarde configureert met een SQLServerPreparedStatement en met een SQLServerCallableStatement om gegevens in te voegen. Hier kan sourceTVPObject een SQLServerDataTable, een ResultSet of een ISQLServerDataRecord-object zijn. In de voorbeelden wordt ervan uitgegaan dat de verbinding een actief verbindingsobject is.

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

Opmerking

Zie Sectie Table-Valued Parameter-API voor het JDBC-stuurprogramma hieronder voor een volledige lijst met API's die beschikbaar zijn voor het instellen van de parameter met tabelwaarde.

Een parameter met tabelwaarde doorgeven als een SQLServerDataTable-object

Vanaf Microsoft JDBC-stuurprogramma 6.0 voor SQL Server vertegenwoordigt de klasse SQLServerDataTable een in-memory tabel met relationele gegevens. In dit voorbeeld ziet u hoe u een tabelwaardeparameter maakt op basis van in-memory gegevens met behulp van het SQLServerDataTable-object. Eerst maakt de code een SQLServerDataTable-object, definieert het bijbehorende schema en vult de tabel met gegevens. De code configureert vervolgens een SQLServerPreparedStatement die deze gegevenstabel als een parameter met tabelwaarde doorgeeft aan 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();

Dit voorbeeld is vergelijkbaar met de vorige. Het enige verschil is dat de TVP-naam wordt ingesteld op de SQLServerDataTable in plaats van door PreparedStatement naar een SQLServerPreparedStatement te casten om de setStructured-methode te gebruiken.

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

Opmerking

Zie Sectie Table-Valued Parameter-API voor het JDBC-stuurprogramma hieronder voor een volledige lijst met API's die beschikbaar zijn voor het instellen van de parameter met tabelwaarde.

Een parameter met tabelwaarde doorgeven als een ResultSet-object

In dit voorbeeld ziet u hoe u rijen met gegevens uit een ResultSet naar een parameter met tabelwaarde kunt streamen. Eerst haalt de code gegevens op uit een brontabel in een SQLServerDataTable-object, definieert het schema en vult de tabel met gegevens. De code configureert vervolgens een SQLServerPreparedStatement die deze gegevenstabel als een parameter met tabelwaarde doorgeeft aan 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();

Opmerking

Zie Sectie Table-Valued Parameter-API voor het JDBC-stuurprogramma hieronder voor een volledige lijst met API's die beschikbaar zijn voor het instellen van de parameter met tabelwaarde.

Een parameter met tabelwaarde doorgeven als een ISQLServerDataRecord-object

Vanaf Microsoft JDBC Driver 6.0 voor SQL Server is een nieuwe interface ISQLServerDataRecord beschikbaar voor het streamen van gegevens (afhankelijk van hoe de gebruiker de implementatie ervan biedt) met behulp van een parameter met tabelwaarde. In het volgende voorbeeld ziet u hoe u de interface ISQLServerDataRecord implementeert en hoe u deze als een parameter met tabelwaarde doorgeeft. Ter vereenvoudiging wordt in het volgende voorbeeld slechts één rij met vastgelegde waarden doorgegeven aan de parameter met tabelwaarden. In het ideale geval zou de gebruiker deze interface implementeren om rijen uit elke bron te streamen, bijvoorbeeld vanuit tekstbestanden.

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

Opmerking

Zie de sectie Tabelwaardige parameter-API voor het JDBC-stuurprogramma hieronder voor een volledige lijst met API's die beschikbaar zijn voor het instellen van de tabelwaardige parameter.

Parameter-API met tabelwaarde voor het JDBC-stuurprogramma

SQLServerMetaData

Deze klasse vertegenwoordigt metagegevens voor een kolom. Deze wordt gebruikt in de interface ISQLServerDataRecord om kolommetagegevens door te geven aan de parameter met tabelwaarde. De methoden in deze klasse zijn:

Naam Description
public SQLServerMetaData(String columnName, int sqlType, int precision, int scale, boolean useServerDefault, boolean isUniqueKey, SQLServerSortOrder sortOrder, int sortOrdinal) Initialiseert een nieuw exemplaar van SQLServerMetaData met de opgegeven kolomnaam, sql-type, precisie, schaal en serverstandaard. Deze vorm van de constructor biedt ondersteuning voor parameters met tabelwaarden door op te geven of de kolom uniek is in de parameter met tabelwaarde, de sorteervolgorde voor de kolom en de rangorde van de sorteerkolom.

useServerDefault - geeft aan of deze kolom de standaardserverwaarde moet gebruiken; De standaardwaarde is onwaar.
isUniqueKey - geeft aan of de kolom in de parameter met tabelwaarde uniek is; De standaardwaarde is onwaar.
sortOrder - geeft de sorteervolgorde voor een kolom aan; De standaardwaarde is SQLServerSortOrder.Unspecified.
sortOrdinal - specificeert de ordinale positie van de sorteerkolom; sortOrdinal begint bij 0; De standaardwaarde is -1.
public SQLServerMetaData(String columnName, int sqlType) Initialiseert een nieuw exemplaar van SQLServerMetaData met behulp van de kolomnaam en het sql-type.
openbare SQLServerMetaData(String columnName, int sqlType, int length) Initialiseert een nieuw exemplaar van SQLServerMetaData met behulp van de kolomnaam, het sql-type en de lengte (voor tekenreeksgegevens). De lengte wordt gebruikt om grote tekenreeksen te onderscheiden van tekenreeksen met een lengte van minder dan 4000 tekens. Geïntroduceerd in versie 7.2 van het JDBC-stuurprogramma.
openbare SQLServerMetaData(String columnName, int sqlType, int precision, int scale) Initialiseert een nieuw exemplaar van SQLServerMetaData met behulp van de kolomnaam, het sql-type, de precisie en de schaal.
Public SQLServerMetaData(SQLServerMetaData sqlServerMetaData) Initialiseert een nieuw exemplaar van SQLServerMetaData van een ander SQLServerMetaData-object.
openbare String getColumnName() Haalt de kolomnaam op.
public int getSqlType() Hiermee wordt het java sql-type opgehaald.
public int getPrecision() Hiermee haalt u de precisie op van het type dat aan de kolom is doorgegeven.
public int getScale() Hiermee wordt de schaal opgehaald van het type dat aan de kolom is doorgegeven.
public SQLServerSortOrder getSortOrder() Hiermee wordt de sorteervolgorde opgehaald.
public int getSortOrdinal() Hiermee haalt u de sorteervolgorde op.
openbare Booleaanse waarde isUniqueKey() Geeft aan of de kolom uniek is.
public boolean useServerDefault() Retourneert of de kolom de standaardserverwaarde gebruikt.

SQLServerSortOrder

Een enum waarmee de sorteervolgorde wordt gedefinieerd. Mogelijke waarden zijn oplopend, aflopend en niet opgegeven.

SQLServerDataTable

Deze klasse vertegenwoordigt een gegevenstabel in het geheugen die moet worden gebruikt met parameters met tabelwaarden. De methoden in deze klasse zijn:

Naam Description
Openbare SQLServerDataTable() Initialiseert een nieuw exemplaar van SQLServerDataTable.
openbaar Iterator<Entry<Integer, Object[]>> getIterator() Hiermee haalt u een iterator op de rijen van de gegevenstabel op.
public void addColumnMetadata(String columnName, int sqlType) Voegt metagegevens toe voor de opgegeven kolom.
public void addColumnMetadata(SQLServerDataColumn column) Voegt metagegevens toe voor de opgegeven kolom.
public void addRow(Object... waarden) Voegt één rij met gegevens toe aan de gegevenstabel.
public Map<Integer, SQLServerDataColumn> getColumnMetadata() Hiermee worden kolommetagegevens van deze gegevenstabel opgehaald.
public void clear() Hiermee wordt deze gegevenstabel gewist.

SQLServerDataColumn

Deze klasse vertegenwoordigt een kolom van de gegevenstabel in het geheugen die wordt vertegenwoordigd door SQLServerDataTable. De methoden in deze klasse zijn:

Naam Description
public SQLServerDataColumn(String columnName, int sqlType) Initialiseert een nieuw exemplaar van SQLServerDataColumn met de kolomnaam en het type.
public String getColumnName() Haalt de kolomnaam op.
public int getColumnType() Hiermee wordt het kolomtype opgehaald.

ISQLServerDataRecord

Deze klasse vertegenwoordigt een interface die gebruikers kunnen implementeren om gegevens te streamen naar een parameter met tabelwaarde. De methoden in deze interface zijn:

Naam Description
openbare SQLServerMetaData getColumnMetaData(int-kolom); Hiermee worden de kolommetagegevens van de opgegeven kolomindex opgehaald.
public int getColumnCount(); Hiermee haalt u het totale aantal kolommen op.
public Object[] getRowData(); Haalt de gegevens voor de huidige rij op als een matrix van objecten.
public boolean volgende(); Hiermee gaat u naar de volgende rij. Retourneert True als de bewerking is geslaagd en er een volgende rij is, anders False.

SQLServerPreparedStatement

De volgende methoden zijn toegevoegd aan deze klasse ter ondersteuning van het doorgeven van parameters met tabelwaarden.

Naam Description
public final void setStructured(int parameterIndex, String tvpName, SQLServerDataTable tvpDataTable) Hiermee wordt een parameter met tabelwaarde gevuld met een gegevenstabel. parameterIndex is de parameterindex, tvpName is de naam van de tabelwaardeparameter en tvpDataTable is het brongegevenstabelobject.
public final void setStructured(int parameterIndex, String tvpName, ResultSet tvpResultSet) Hiermee wordt een parameter met tabelwaarde gevuld met een ResultSet die is opgehaald uit een andere tabel. parameterIndex is de parameterindex, tvpName is de naam van de parameter met tabelwaarde en tvpResultSet is het bronresultaatsetobject.
public final void setStructured(int parameterIndex, String tvpName, ISQLServerDataRecord tvpDataRecord) Hiermee wordt een tabelwaardeparameter gevuld met een ISQLServerDataRecord-object. ISQLServerDataRecord wordt gebruikt voor het streamen van gegevens en de gebruiker bepaalt hoe deze moet worden gebruikt. parameterIndex is de parameterindex, tvpName is de naam van de parameter met tabelwaarde en tvpDataRecord is een ISQLServerDataRecord-object.

SQLServerCallableStatement

De volgende methoden zijn toegevoegd aan deze klasse ter ondersteuning van het doorgeven van parameters met tabelwaarden.

Naam Description
public final void setStructured(String parameterName, String tvpName, SQLServerDataTable tvpDataTable) Hiermee vult u een tabelwaardige parameter in die wordt doorgegeven aan een opgeslagen procedure met een gegevenstabel. parameterName is de naam van de parameter, tvpName is de naam van het type TVP en tvpDataTable is het gegevenstabelobject.
public final void setStructured(String parameterName, String tvpName, ResultSet tvpResultSet) Hiermee wordt een tabelwaardeparameter ingevuld die is doorgegeven aan een opgeslagen procedure met een ResultSet die is opgehaald uit een andere tabel. parameterName is de naam van de parameter, tvpName is de naam van het type TVP en tvpResultSet is het bronresultaatsetobject.
public final void setStructured(String parameterName, String tvpName, ISQLServerDataRecord tvpDataRecord) Hiermee wordt een tabelwaardeparameter ingevuld die wordt doorgegeven aan een opgeslagen procedure met een ISQLServerDataRecord-object. ISQLServerDataRecord wordt gebruikt voor het streamen van gegevens en de gebruiker bepaalt hoe deze moet worden gebruikt. parameterName is de naam van de parameter, tvpName is de naam van het type TVP en tvpDataRecord is een ISQLServerDataRecord-object.

Zie ook

Overzicht van het JDBC-stuurprogramma

  • Last updated on