Sdílet prostřednictvím

Použití tabulkových parametrů

Stáhnout ovladač JDBC

Tabulkové parametry poskytují snadný způsob, jak předávat více řádků dat z klientské aplikace na SQL Server, aniž by bylo nutné provádět více přenosů nebo využívat speciální logiku na serverové straně pro zpracování dat. Parametry hodnot tabulky můžete použít k zapouzdření řádků dat v klientské aplikaci a odeslání dat na server v jednom parametrizovaném příkazu. Příchozí datové řádky se ukládají do proměnné tabulky, na které je pak možné pracovat pomocí jazyka Transact-SQL.

Hodnoty sloupců v parametrech s hodnotami tabulky jsou přístupné pomocí standardních příkazů Transact-SQL SELECT. Parametry s hodnotami v tabulce jsou silného typu a jejich struktura je automaticky ověřena. Velikost parametrů s hodnotou tabulky je omezena pouze pamětí serveru.

Poznámka:

Podpora parametrů typu Table-Valued je dostupná od Microsoft JDBC Driver 6.0 pro SQL Server.

V parametru s hodnotou tabulky nemůžete vracet data. Parametry typu tabulky jsou pouze vstupní; klíčové slovo OUTPUT není podporováno.

Další informace o tabulkových parametrech najdete v následujících zdrojích.

Resource Description
Parametry s tabulkovou hodnotou (databázový stroj) v dokumentaci SQL Server Books Online Popisuje, jak vytvořit a používat parametry s hodnotou tabulky.
Uživatelsky definované typy tabulek v SQL Server Books Online Popisuje uživatelem definované typy tabulek, které se používají k deklaraci parametrů s hodnotou tabulky.

Předávání více řádků v dřívějších verzích SQL Serveru

Před zavedením parametrů s hodnotou tabulky do SYSTÉMU SQL Server 2008 byly možnosti předávání více řádků dat uložené proceduře nebo parametrizovaný příkaz SQL omezeny. Vývojář si může vybrat z následujících možností pro předávání více řádků na server:

  • Pomocí řady jednotlivých parametrů můžete znázorňovat hodnoty ve více sloupcích a řádcích dat. Množství dat, která lze předat pomocí této metody, je omezen počtem povolených parametrů. Procedury SQL Serveru můžou mít maximálně 2100 parametrů. Logika na straně serveru je nutná k sestavení těchto jednotlivých hodnot do proměnné tabulky nebo dočasné tabulky pro zpracování.

  • Sbalte několik datových hodnot do řetězců s oddělovači nebo dokumentů XML a pak tyto textové hodnoty předejte procedurě nebo příkazu. To vyžaduje, aby procedura nebo příkaz zahrnoval logiku potřebnou k ověření datových struktur a oddělení hodnot.

  • Vytvořte řadu jednotlivých příkazů SQL pro úpravy dat, které ovlivňují více řádků. Změny lze odeslat na server jednotlivě nebo dávkově do skupin. I když je však odeslán v dávkách, které obsahují více příkazů, každý příkaz se provádí samostatně na serveru.

  • Pomocí nástroje bcp nebo SQLServerBulkCopy načtěte mnoho řádků dat do tabulky. I když je tato technika efektivní, nepodporuje zpracování na straně serveru, pokud se data nenačtou do dočasné tabulky nebo proměnné tabulky.

Vytváření typů parametrů s hodnotou tabulky

Parametry s hodnotou tabulky jsou založeny na strukturách tabulek s pevným typem, které jsou definovány pomocí příkazů Transact-SQL CREATE TYPE. Před použitím parametrů hodnot tabulky v klientských aplikacích musíte vytvořit typ tabulky a definovat strukturu v SQL Serveru. Další informace o vytváření typů tabulek naleznete v tématu User-Defined Typy tabulek v SQL Server Books Online.

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

Po vytvoření typu tabulky můžete deklarovat parametry s hodnotou tabulky na základě daného typu. Následující Transact-SQL fragment ukazuje, jak deklarovat parametr s hodnotou tabulky v definici uložené procedury. Klíčové READONLY slovo je vyžadováno pro deklarování parametru s hodnotou tabulky.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Úprava dat pomocí parametrů s hodnotou tabulky (Transact-SQL)

Parametry hodnotné tabulkou lze použít v úpravách dat založených na sadě, které ovlivňují více řádků spuštěním jednoho příkazu. Můžete například vybrat všechny řádky v parametru s hodnotou tabulky a vložit je do databázové tabulky nebo můžete vytvořit aktualizační příkaz spojením parametru s hodnotou tabulky k tabulce, kterou chcete aktualizovat.

Následující příkaz Transact-SQL UPDATE ukazuje použití parametru s hodnotou tabulky jeho spojením k tabulce Categories. Pokud použijete parametr s hodnotou tabulky s join v klauzuli FROM, musíte ho také aliasovat, jak je znázorněno zde, kde parametr s hodnotou tabulky je aliasován jako "ec":

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

Tento Transact-SQL příklad ukazuje, jak vybrat řádky z parametru s hodnotou tabulky k provedení INSERT v rámci jedné operace založené na sadě.

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

Omezení parametrů tabulkových hodnot

Parametry s hodnotou tabulky mají několik omezení:

  • Uživatelem definovaným funkcím nemůžete předávat parametry s hodnotou tabulky.

  • Parametry s hodnotou tabulky je možné indexovat pouze pro podporu omezení JEDINEČNÉho nebo PRIMÁRNÍHO KLÍČE. SQL Server neudržuje statistiky o parametrech s hodnotami tabulky.

  • Parametry s hodnotou tabulky jsou v kódu Transact-SQL jen pro čtení. Hodnoty sloupců v řádcích parametru s hodnotou tabulky nelze aktualizovat a řádky nelze vložit ani odstranit. Chcete-li upravit data, která se předávají uložené proceduře nebo parametrizovanému příkazu v parametru s hodnotou tabulky, musíte data vložit do dočasné tabulky nebo do proměnné tabulky.

  • Příkazy ALTER TABLE nelze použít k úpravě návrhu parametrů hodnot tabulky.

  • Velké objekty můžete streamovat v parametru s hodnotou tabulky.

Konfigurace parametru s hodnotou tabulky

Počínaje ovladačem Microsoft JDBC 6.0 pro SQL Server jsou parametry hodnot tabulky podporovány parametrizovaným příkazem nebo parametrizovanou uloženou procedurou. Parametry typu tabulky lze naplnit z SQLServerDataTable, z ResultSet nebo z implementace rozhraní ISQLServerDataRecord, kterou poskytl uživatel. Při nastavování parametru s hodnotou tabulky pro připravený dotaz musíte zadat název typu, který se musí shodovat s názvem kompatibilního typu vytvořeného na serveru.

Následující dva fragmenty kódu ukazují, jak nakonfigurovat parametr s hodnotou tabulky pomocí SQLServerPreparedStatement a SQLServerCallableStatement pro vložení dat. Zde sourceTVPObject může být SQLServerDataTable, ResultSet nebo ISQLServerDataRecord. V příkladech se předpokládá, že připojení je aktivní objekt 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();

Poznámka:

Úplný seznam rozhraní API dostupných pro nastavení parametru s hodnotou tabulky najdete níže v části Rozhraní API pro parametry s hodnotou tabulky pro ovladač JDBC.

Předání parametru typu tabulkové hodnoty jako objektu SQLServerDataTable

Počínaje ovladačem Microsoft JDBC 6.0 pro SQL Server představuje třída SQLServerDataTable tabulku relačních dat v paměti. Tento příklad ukazuje, jak vytvořit parametr s hodnotou tabulky z dat v paměti pomocí SQLServerDataTable objektu. Nejprve kód vytvoří objekt SQLServerDataTable, definuje jeho schéma a naplní tabulku daty. Kód poté nakonfiguruje SQLServerPreparedStatement, který předá tuto datovou tabulku jako parametr s hodnotami tabulky do SQL Serveru.

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

Tento příklad je podobný předchozímu. Jediným rozdílem je, že nastaví název TVP na SQLServerDataTable místo přetypování PreparedStatement na SQLServerPreparedStatement, aby mohl použít metodu 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();

Poznámka:

Úplný seznam rozhraní API pro nastavení parametru s hodnotou tabulky najdete v části Table-Valued Parameter API pro ovladač JDBC níže.

Předání parametru s hodnotou tabulky jako objektu ResultSet

Tento příklad ukazuje, jak streamovat řádky dat z ResultSet do parametru s hodnotou tabulky. Nejprve kód načte data ze zdrojové tabulky v objektu SQLServerDataTable, definuje jeho schéma a naplní tabulku daty. Kód pak nakonfiguruje SQLServerPreparedStatement, který předá tuto tabulku jako parametr s hodnotami tabulky na SQL Serveru.

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

Poznámka:

Úplný seznam API dostupných pro nastavení parametru s hodnotou tabulky najdete v části API pro parametr s hodnotou tabulky pro ovladač JDBC níže.

Předání parametru s hodnotou tabulky jako objektu ISQLServerDataRecord

Počínaje ovladačem Microsoft JDBC 6.0 pro SQL Server je k dispozici nové rozhraní ISQLServerDataRecord pro streamovaná data (v závislosti na tom, jak pro něj uživatel poskytuje implementaci) pomocí parametru s hodnotou tabulky. Následující příklad ukazuje, jak implementovat ISQLServerDataRecord rozhraní a jak jej předat jako parametr s hodnotou tabulky. Pro zjednodušení následující příklad předá pouze jeden řádek s pevně zakódovanými hodnotami do parametru table-valued. V ideálním případě by uživatel toto rozhraní implementoval tak, aby streamoval řádky z libovolného zdroje, například z textových souborů.

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

Poznámka:

Úplný seznam rozhraní API dostupných pro nastavení parametru s hodnotou tabulky najdete v části Rozhraní API pro ovladač JDBC níže.

Rozhraní API pro parametr tabulkové hodnoty pro ovladač JDBC

SQLServerMetaData

Tato třída představuje metadata pro sloupec. Používá se v rozhraní ISQLServerDataRecord k předávání metadat sloupců do parametru s hodnotou tabulky. Metody v této třídě jsou:

Název Description
public SQLServerMetaData(String columnName, int sqlType, int precision, int scale, boolean useServerDefault, boolean isUniqueKey, SQLServerSortOrder sortOrder, int sortOrdinal) Inicializuje novou instanci SQLServerMetaData se zadaným názvem sloupce, typem SQL, přesností, škálováním a výchozím nastavením serveru. Tato forma konstruktoru podporuje parametry hodnot tabulky tím, že umožňuje určit, jestli je sloupec jedinečný v parametru s hodnotou tabulky, pořadí řazení sloupce a pořadové číslo sloupce řazení.

useServerDefault – určuje, jestli má tento sloupec použít výchozí hodnotu serveru; Výchozí hodnota je false.
isUniqueKey - označuje, zda sloupec v parametru table-valued je jedinečný; Výchozí hodnota je false.
sortOrder – označuje pořadí řazení sloupce; Výchozí hodnota je SQLServerSortOrder.Unspecified.
sortOrdinal – určuje pořadový řádek sloupce řazení; sortOrdinal začíná od 0; Výchozí hodnota je -1.
public SQLServerMetaData(String columnName, int sqlType) Inicializuje novou instanci SQLServerMetaData pomocí názvu sloupce a typu SQL.
public SQLServerMetaData(String columnName, int sqlType, int length) Inicializuje novou instanci SQLServerMetaData pomocí názvu sloupce, typu SQL a délky (pro data String). Délka se používá k rozlišení velkých řetězců od řetězců s délkou menší než 4 000 znaků. Představeno ve verzi 7.2 ovladače JDBC.
public SQLServerMetaData(String columnName, int sqlType, int precision, int scale) Inicializuje novou instanci SQLServerMetaData pomocí názvu sloupce, typu SQL, přesnosti a škálování.
Veřejná SQLServerMetaData(SQLServerMetaData sqlServerMetaData) Inicializuje novou instanci SQLServerMetaData z jiného sqlServerMetaData objektu.
public String getColumName() Načte název sloupce.
public int getSqlType() Načte typ dat Java SQL.
public int getPrecision() Určuje přesnost typu, který je předán do sloupce.
public int getScale() Načte měřítko typu předaného do sloupce.
public SQLServerSortOrder getSortOrder() Načte pořadí řazení.
public int getSortOrdinal() Načte pořadí řazení.
public boolean jeUnikátníKlíč() Vrátí, zda je sloupec jedinečný.
public boolean useServerDefault() Vrátí, zda sloupec používá výchozí hodnotu serveru.

SQLServerSortOrder

Výčet, který definuje pořadí řazení. Možné hodnoty jsou vzestupné, sestupné a nezadané.

SQLServerDataTable

Tato třída představuje tabulku dat v paměti, která se má použít s parametry hodnot tabulky. Metody v této třídě jsou:

Název Description
Veřejná tabulka SQLServerDataTable() Inicializuje novou instanci SQLServerDataTable.
public Iterator<Entry<Integer, Object[]>> getIterator() Načte iterátor na řádcích tabulky dat.
public void addColumnMetadata(String columnName, int sqlType) Přidá metadata pro zadaný sloupec.
public void addColumnMetadata(SQLServerDataColumn column) Přidá metadata pro zadaný sloupec.
public void addRow(Object... values) Přidá do tabulky dat jeden řádek dat.
public Map<Integer, SQLServerDataColumn> getColumnMetadata() Načte metadata sloupců této datové tabulky.
public void clear() Vymaže tuto tabulku dat.

SQLServerDataColumn

Tato třída představuje sloupec tabulky dat v paměti reprezentované SQLServerDataTable. Metody v této třídě jsou:

Název Description
public SQLServerDataColumn(String columnName, int sqlType) Inicializuje novou instanci SQLServerDataColumn s názvem sloupce a typem.
public String getColumnName() Načte název sloupce.
public int getColumnType() // Vrátí typ sloupce Načte typ sloupce.

ISQLServerDataRecord

Tato třída představuje rozhraní, které mohou uživatelé implementovat pro streamování dat do parametru s hodnotou tabulky. Metody v tomto rozhraní jsou:

Název Description
public SQLServerMetaData getColumnMetaData(int column); Načte metadata pro daný index sloupce.
public int getColumnCount(); Načte celkový počet sloupců.
public Object[] getRowData(); Načte data pro aktuální řádek jako pole objektů.
public boolean next(); Přesune se na další řádek. Vrátí hodnotu True, pokud je přesunutí úspěšné a existuje další řádek, jinak je false.

SQLServerPreparedStatement

Do této třídy byly přidány následující metody, které podporují předávání parametrů hodnot tabulky.

Název Description
public final void setStructured(int parameterIndex, String tvpName, SQLServerDataTable tvpDataTable) Naplní parametr tabulkového typu datovou tabulkou. parameterIndex je index parametru, tvpName je název parametru typu tabulka a tvpDataTable je objekt zdrojové datové tabulky.
public final void setStructured(int parameterIndex, String tvpName, ResultSet tvpResultSet) Naplní parametr hodnoty tabulky parametrem ResultSet načteným z jiné tabulky. parameterIndex je index parametrů, tvpName je název parametru tabulkové hodnoty a tvpResultSet je zdrojový objekt sady výsledků.
public final void setStructured(int parameterIndex, String tvpName, ISQLServerDataRecord tvpDataRecord) Naplní parametr s hodnotou tabulky pomocí objektu ISQLServerDataRecord. ISQLServerDataRecord se používá pro streamovaná data a uživatel se rozhodne, jak ho používat. parameterIndex je index parametrů, tvpName je název parametru table-valued a tvpDataRecord je ISQLServerDataRecord objekt.

SQLServerCallableStatement

Do této třídy byly přidány následující metody, které podporují předávání parametrů hodnot tabulky.

Název Description
public final void setStructured(String parameterName, String tvpName, SQLServerDataTable tvpDataTable) Naplní parametr s hodnotou tabulky předaný uložené proceduře tabulkou dat. parameterName je název parametru, tvpName je název typu TVP a tvpDataTable je objekt tabulky dat.
public final void setStructured(String parameterName, String tvpName, ResultSet tvpResultSet) Naplní parametr s hodnotou tabulky předaný uložené proceduře hodnotou ResultSet načtenou z jiné tabulky. parameterName je název parametru, tvpName je název typu TVP a tvpResultSet je objekt sady výsledků zdroje.
public final void setStructured(String parameterName, String tvpName, ISQLServerDataRecord tvpDataRecord) Naplní parametr s hodnotou tabulky předaný uložené procedurě objektem ISQLServerDataRecord. ISQLServerDataRecord se používá pro streamovaná data a uživatel se rozhodne, jak ho používat. parameterName je název parametru, tvpName je název typu TVP a tvpDataRecord je objekt ISQLServerDataRecord.

Viz také

Přehled ovladače JDBC

  • Last updated on