Dela via

Använda tabellvärdesparametrar

Ladda ned JDBC-drivrutin

Tabellvärdesparametrar är ett enkelt sätt att konvertera flera rader med data från ett klientprogram till SQL Server utan att kräva flera turer eller särskild logik på serversidan för bearbetning av data. Du kan använda tabellvärdesparametrar för att kapsla in rader med data i ett klientprogram och skicka data till servern i ett enda parameteriserat kommando. Inkommande datarader lagras i en tabellvariabel som sedan kan användas med hjälp av Transact-SQL.

Kolumnvärden i tabellvärdesparametrar kan nås med hjälp av STANDARD-Transact-SQL SELECT-instruktioner. Tabellvärdesparametrar är strängt typade och deras struktur verifieras automatiskt. Storleken på tabellvärdesparametrar begränsas endast av serverminnet.

Anmärkning

Stöd för Tabellvärdeparametrar är tillgängligt från och med Microsoft JDBC Driver 6.0 för SQL Server.

Du kan inte returnera data i en tabellvärdesparameter. Tabellvärdesparametrar är endast indata. nyckelordet OUTPUT stöds inte.

Mer information om tabellvärdesparametrar finns i följande resurser.

Resource Description
Tabellvärdeparametrar (Databasmotor) i SQL Server Books Online Beskriver hur du skapar och använder tabellvärdesparametrar
Användardefinierade tabelltyper i SQL Server Books Online Beskriver användardefinierade tabelltyper som används för att deklarera tabellvärdesparametrar

Passera flera rader i tidigare versioner av SQL Server

Innan tabellvärdesparametrar introducerades i SQL Server 2008 var alternativen för att skicka flera rader med data till en lagrad procedur eller ett parameteriserat SQL-kommando begränsades. En utvecklare kan välja mellan följande alternativ för att skicka flera rader till servern:

  • Använd en serie enskilda parametrar för att representera värdena i flera kolumner och rader med data. Mängden data som kan skickas med den här metoden begränsas av antalet tillåtna parametrar. SQL Server-procedurer kan ha högst 2 100 parametrar. Logik på serversidan krävs för att montera dessa enskilda värden i en tabellvariabel eller en tillfällig tabell för bearbetning.

  • Paketera flera datavärden i avgränsade strängar eller XML-dokument och skicka sedan textvärdena till en procedur eller instruktion. Detta kräver att proceduren eller -instruktionen innehåller den logik som krävs för att verifiera datastrukturerna och separera värdena.

  • Skapa en serie enskilda SQL-instruktioner för dataändringar som påverkar flera rader. Ändringar kan skickas till servern individuellt eller batchvis i grupper. Men även när de skickas i batchar som innehåller flera instruktioner körs varje -instruktion separat på servern.

  • Använd bcp-verktygsprogrammet eller SQLServerBulkCopy för att läsa in många rader med data i en tabell. Även om den här tekniken är effektiv stöder den inte bearbetning på serversidan om inte data läses in i en tillfällig tabell eller tabellvariabel.

Skapa tabellvärdesparametertyper

Tabellvärdeparametrar baseras på starkt skrivna tabellstrukturer som definieras med hjälp av Transact-SQL-instruktioner CREATE TYPE . Du måste skapa en tabelltyp och definiera strukturen i SQL Server innan du kan använda tabellvärdesparametrar i dina klientprogram. För mer information om hur du skapar tabelltyper, se User-Defined Table Types i SQL Server Books Online.

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

När du har skapat en tabelltyp kan du deklarera tabellvärdesparametrar baserat på den typen. Följande Transact-SQL-fragment visar hur du deklarerar en tabellvärdeparameter i en definition av lagrad procedur. Nyckelordet READONLY krävs för att deklarera en tabellvärdesparameter.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Ändra data med tabellvärdesparametrar (Transact-SQL)

Tabellvärdesparametrar kan användas i uppsättningsbaserade dataändringar som påverkar flera rader genom att köra en enda instruktion. Du kan till exempel välja alla rader i en tabellvärdeparameter och infoga dem i en databastabell, eller så kan du skapa en uppdateringsinstrukitet genom att koppla en tabellvärdesparameter till den tabell som du vill uppdatera.

Följande Transact-SQL UPDATE-instruktion visar hur du använder en tabellvärdesparameter genom att koppla den till tabellen Kategorier. När du använder en tabellvärdesparameter med en JOIN i en FROM-sats måste du även alias den, som du ser här, där parametern table-valued aliaseras som "ec":

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

Det här Transact-SQL exemplet visar hur du väljer rader från en tabellvärdeparameter för att utföra en INSERT i en enda uppsättningsbaserad åtgärd.

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

Begränsningar för tabellvärdesparametrar

Det finns flera begränsningar för tabellvärdesparametrar:

  • Du kan inte skicka tabellvärdesparametrar till användardefinierade funktioner.

  • Tabellvärdeparametrar kan bara indexeras för att stödja begränsningar för UNIK eller PRIMÄR NYCKEL. SQL Server behåller inte statistik om tabellvärdesparametrar.

  • Tabellvärdeparametrar är skrivskyddade i Transact-SQL kod. Du kan inte uppdatera kolumnvärdena i raderna i en tabellvärdeparameter och du kan inte infoga eller ta bort rader. Om du vill ändra data som skickas till en lagrad procedur eller parameteriserad instruktion i tabellvärdesparametern måste du infoga data i en tillfällig tabell eller i en tabellvariabel.

  • Du kan inte använda ALTER TABLE-instruktioner för att ändra utformningen av tabellvärdesparametrar.

  • Du kan strömma stora objekt i en tabellvärdesparameter.

Konfigurera en tabellvärdesparameter

Från och med Microsoft JDBC Driver 6.0 för SQL Server stöds tabellvärdesparametrar med en parameteriserad instruktion eller en parametriserad lagrad procedur. Tabellvärdesparametrar kan fyllas i från en SQLServerDataTable, från en ResultSet eller från en användartillhandahållen implementering av gränssnittet ISQLServerDataRecord. När du anger en tabellvärdesparameter för en förberedd fråga måste du ange ett typnamn som måste matcha namnet på en kompatibel typ som tidigare skapats på servern.

Följande två kodfragment visar hur du konfigurerar en tabellvärdeparameter med en SQLServerPreparedStatement och med en SQLServerCallableStatement för att infoga data. Här kan sourceTVPObject vara en SQLServerDataTable, en ResultSet eller ett ISQLServerDataRecord-objekt. Exemplen förutsätter att anslutningen är ett aktivt anslutningsobjekt.

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

Anmärkning

Se avsnittet Table-Valued Parameter-API för JDBC-drivrutinen nedan för en fullständig lista över API:er som är tillgängliga för att ange parametern tabellvärde.

Skicka en tabellvärdesparameter som ett SQLServerDataTable-objekt

Från och med Microsoft JDBC Driver 6.0 för SQL Server representerar klassen SQLServerDataTable en minnesintern tabell med relationsdata. Det här exemplet visar hur du konstruerar en tabellvärdesparameter från minnesintern data med hjälp av SQLServerDataTable-objektet. Först skapar koden ett SQLServerDataTable-objekt, definierar dess schema och fyller tabellen med data. Koden konfigurerar sedan en SQLServerPreparedStatement som skickar den här datatabellen som en tabellvärdeparameter till 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();

Det här exemplet liknar det föregående. Den enda skillnaden är att den ställer in TVP-namnet på SQLServerDataTable i stället för att förlita sig på gjutning PreparedStatement till en SQLServerPreparedStatement för att använda setStructured metoden.

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

Anmärkning

Se avsnittet Table-Valued Parameter-API för JDBC-drivrutinen nedan för en fullständig lista över API:er som är tillgängliga för att ange parametern tabellvärde.

Skicka en tabellvärdeparameter som ett ResultSet-objekt

Det här exemplet visar hur du strömmar rader med data från en ResultSet till en tabellvärdeparameter. Först hämtar koden data från en källtabell i ett SQLServerDataTable-objekt, definierar dess schema och fyller tabellen med data. Koden konfigurerar sedan en SQLServerPreparedStatement som skickar den här datatabellen som en tabellvärdeparameter till 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();

Anmärkning

Se avsnittet Table-Valued Parameter-API för JDBC-drivrutinen nedan för en fullständig lista över API:er som är tillgängliga för att ange parametern tabellvärde.

Skicka en tabellvärdeparameter som ett ISQLServerDataRecord-objekt

Från och med Microsoft JDBC Driver 6.0 för SQL Server är ett nytt gränssnitt ISQLServerDataRecord tillgängligt för strömmande data (beroende på hur användaren tillhandahåller implementeringen för den) med hjälp av en tabellvärdesparameter. I följande exempel visas hur du implementerar GRÄNSSNITTET ISQLServerDataRecord och hur du skickar det som en tabellvärdesparameter. För enkelhetens skull skickar följande exempel bara en rad med hårdkodade värden till parametern table-valued. Helst skulle användaren implementera det här gränssnittet för att strömma rader från valfri källa, till exempel från textfiler.

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

Anmärkning

Se Avsnittet Tabellvärdesparameter-API för JDBC-drivrutinen nedan för en fullständig lista över API:er som är tillgängliga för att ange parametern tabellvärde.

Tabellvärdesparameter-API för JDBC-drivrutinen

SQLServerMetaData

Den här klassen representerar metadata för en kolumn. Den används i gränssnittet ISQLServerDataRecord för att skicka kolumnmetadata till en table-valued parameter. Metoderna i den här klassen är:

Namn Description
public SQLServerMetaData(String columnName, int sqlType, int precision, int scale, boolesk useServerDefault, boolesk isUniqueKey, SQLServerSortOrder sortOrder, int sortOrdinal) Initierar en ny instans av SQLServerMetaData med angivet kolumnnamn, sql-typ, precision, skalning och serverstandard. Den här formen av konstruktorn stöder tabellvärdesparametrar genom att du kan ange om kolumnen är unik i parametern tabellvärde, sorteringsordningen för kolumnen och ordningsföljden för sorteringskolumnen.

useServerDefault – anger om den här kolumnen ska använda standardservervärdet. Standardvärdet är falskt.
isUniqueKey – anger om kolumnen i parametern table-valued är unik. Standardvärdet är falskt.
sortOrder – anger sorteringsordningen för en kolumn. Standardvärdet är SQLServerSortOrder.Unspecified.
sortOrdinal – anger ordningstalet för sorteringskolumnen. sortOrdinal börjar från 0; Standardvärdet är -1.
public SQLServerMetaData(String columnName, int sqlType) Initierar en ny instans av SQLServerMetaData med kolumnnamnet och sql-typen.
public SQLServerMetaData(String columnName, int sqlType, int length) Initierar en ny instans av SQLServerMetaData med kolumnnamnet, sql-typen och längden (för Strängdata). Längden används för att skilja stora strängar från strängar med en längd på mindre än 4 000 tecken. Introducerades i version 7.2 av JDBC-drivrutinen.
public SQLServerMetaData(String columnName, int sqlType, int precision, int scale) Initierar en ny instans av SQLServerMetaData med kolumnnamnet, SQL-typen, precisionen och skalan.
Offentlig SQLServerMetaData(SQLServerMetaData sqlServerMetaData) Initierar en ny instans av SQLServerMetaData från ett annat SQLServerMetaData-objekt.
public String getColumName() Hämtar kolumnnamnet.
public int getSqlType() Hämtar java sql-typen.
public int getPrecision() Hämtar precisionen av den datatyp som skickas till kolumnen.
public int getScale() Hämtar skalan för den typ som skickas till kolumnen.
public SQLServerSortOrder getSortOrder() Hämtar sorteringsordningen.
public int getSortOrdinal() Hämtar sorteringsordningen.
public boolean isUniqueKey() Returnerar om kolumnen är unik.
public boolean useServerDefault() Returnerar om kolumnen använder standardservervärdet.

SQLServerSortOrder

En enumerationstyp som definierar sorteringsordningen. Möjliga värden är Stigande, Fallande och Ospecificerad.

SQLServerDataTable

Den här klassen representerar en minnesintern datatabell som ska användas med tabellvärdesparametrar. Metoderna i den här klassen är:

Namn Description
Offentlig SQLServerDataTable() Initierar en ny instans av SQLServerDataTable.
public Iterator<Entry<Integer, Object[]>> getIterator() Hämtar en iterator på raderna i datatabellen.
public void addColumnMetadata(String columnName, int sqlType) Lägger till metadata för den angivna kolumnen.
public void addColumnMetadata(SQLServerDataColumn column) Lägger till metadata för den angivna kolumnen.
public void addRow(Object... värden) Lägger till en rad med data i datatabellen.
public Map<Integer, SQLServerDataColumn> getColumnMetadata() Hämtar kolumnmetadata för den här datatabellen.
public void clear() Rensar den här datatabellen.

SQLServerDataColumn

Den här klassen representerar en kolumn i den minnesinterna datatabellen som representeras av SQLServerDataTable. Metoderna i den här klassen är:

Namn Description
public SQLServerDataColumn(String columnName, int sqlType) Initierar en ny instans av SQLServerDataColumn med kolumnnamnet och typen.
public String getColumnName() Hämtar kolumnnamnet.
public int getColumnType() Hämtar kolumntypen.

ISQLServerDataRecord

Den här klassen representerar ett gränssnitt som användarna kan implementera för att strömma data till en tabellvärdesparameter. Metoderna i det här gränssnittet är:

Namn Description
public SQLServerMetaData getColumnMetaData(int column); Hämtar kolumnmetadata för det angivna kolumnindexet.
public int getColumnCount(); Hämtar det totala antalet kolumner.
public Object[] getRowData(); Hämtar data för den aktuella raden som en matris med objekt.
public boolean next(); Flyttar till nästa rad. Returnerar Sant om flytten lyckas och det finns en nästa rad, annars falskt.

SQLServerPreparedStatement

Följande metoder har lagts till i den här klassen för att stödja överföring av tabellvärdesparametrar.

Namn Description
public final void setStructured(int parameterIndex, String tvpName, SQLServerDataTable tvpDataTable) Fyller i en tabellvärdesparameter med en datatabell. parameterIndex är parameterindexet, tvpName är namnet på tabellvärdesparametern, och tvpDataTable är datakällans tabellobjekt.
public final void setStructured(int parameterIndex, String tvpName, ResultSet tvpResultSet) Fyller i en tabellvärdesparameter med en resultatuppsättning som hämtats från en annan tabell. parameterIndex är parameterindexet, tvpName är namnet på tabellvärdesparametern, och tvpResultSet är källresultatuppsättningsobjekt.
public final void setStructured(int parameterIndex, String tvpName, ISQLServerDataRecord tvpDataRecord) Fyller i en tabellvärdesparameter med ett ISQLServerDataRecord-objekt. ISQLServerDataRecord används för strömmande data och användaren bestämmer hur den ska användas. parameterIndex är parameterindexet, tvpName är namnet på parametern table-valued och tvpDataRecord är ett ISQLServerDataRecord-objekt.

SQLServerCallableStatement

Följande metoder har lagts till i den här klassen för att stödja överföring av tabellvärdesparametrar.

Namn Description
public final void setStructured(String parameterName, String tvpName, SQLServerDataTable tvpDataTable) Fyller i en tabellvärdesparameter som skickas till en lagrad procedur med en datatabell. parameterName är namnet på parametern, tvpName är namnet på typen TVP och tvpDataTable är datatabellobjektet.
public final void setStructured(String parameterName, String tvpName, ResultSet tvpResultSet) Befolkar en tabellvärdesparameter som skickas till en lagrad procedur med en resultatmängd som hämtats från en annan tabell. parameterName är namnet på parametern, tvpName är namnet på typen TVP och tvpResultSet är källresultatuppsättningsobjektet.
public final void setStructured(String parameterName, String tvpName, ISQLServerDataRecord tvpDataRecord) Fyller i en tabellvärdesparameter som skickas till en lagrad procedur med ett ISQLServerDataRecord-objekt. ISQLServerDataRecord används för strömmande data och användaren bestämmer hur den ska användas. parameterName är namnet på parametern, tvpName är namnet på typen TVP och tvpDataRecord är ett ISQLServerDataRecord-objekt.

Se även

Översikt över JDBC-drivrutinen

  • Last updated on