Udostępnij przez

Używanie parametrów wartości tabeli

pobierz sterownik JDBC

Parametry wartości tabeli umożliwiają łatwe przeprowadzanie marshalingu wielu wierszy danych z aplikacji klienckiej do programu SQL Server bez konieczności stosowania wielu rund lub specjalnej logiki po stronie serwera do przetwarzania danych. Za pomocą parametrów wartości tabeli można hermetyzować wiersze danych w aplikacji klienckiej i wysłać dane do serwera w jednym sparametryzowanym poleceniu. Przychodzące wiersze danych są przechowywane w zmiennej tabeli, która może być następnie obsługiwana przy użyciu języka Transact-SQL.

Dostęp do wartości kolumn w parametrach wartości tabeli można uzyskać przy użyciu standardowych instrukcji Transact-SQL SELECT. Parametry wartości tabeli są silnie typizowane, a ich struktura jest automatycznie weryfikowana. Rozmiar parametrów o wartości tabeli jest ograniczony tylko przez pamięć serwera.

Uwaga / Notatka

Obsługa parametrów tabelarycznych jest dostępna od sterownika Microsoft JDBC Driver 6.0 dla SQL Server.

Nie można zwracać danych w parametrze wartości tabeli. Parametry wartości tabeli są tylko danymi wejściowymi; słowo kluczowe OUTPUT nie jest obsługiwane.

Aby uzyskać więcej informacji na temat parametrów wartości tabeli, zobacz następujące zasoby.

Resource Description
Parametry tabelaryczne (silnik bazy danych) w książkach programu SQL Server Online Opisuje sposób tworzenia i używania parametrów wartości tabeli
Typy tabel zdefiniowanych przez użytkownika w dokumentacji online programu SQL Server Opis typów tabel zdefiniowanych przez użytkownika, które są używane do deklarowania parametrów z wartością tabeli

Przekazywanie wielu wierszy w poprzednich wersjach programu SQL Server

Przed wprowadzeniem parametrów wartości tabeli do programu SQL Server 2008 opcje przekazywania wielu wierszy danych do procedury składowanej lub sparametryzowanego polecenia SQL były ograniczone. Deweloper może wybrać spośród następujących opcji przekazywania wielu wierszy do serwera:

  • Użyj serii pojedynczych parametrów, aby reprezentować wartości w wielu kolumnach i wierszach danych. Ilość danych, które można przekazać przy użyciu tej metody, jest ograniczona przez dozwoloną liczbę parametrów. Procedury programu SQL Server mogą mieć co najwyżej 2100 parametrów. Logika po stronie serwera jest wymagana do złożenia tych indywidualnych wartości w zmiennej tabeli lub tabeli tymczasowej do przetwarzania.

  • Łączenie wielu wartości danych z rozdzielanymi ciągami lub dokumentami XML, a następnie przekazywanie tych wartości tekstowych do procedury lub instrukcji. Wymaga to, aby procedura lub instrukcja zawierała logikę niezbędną do weryfikacji struktur danych i rozdzielenia wartości.

  • Utwórz serię pojedynczych instrukcji SQL na potrzeby modyfikacji danych, które mają wpływ na wiele wierszy. Zmiany można przesyłać do serwera pojedynczo lub grupować w partie. Jednak nawet w przypadku przesłania w partiach zawierających wiele instrukcji każda instrukcja jest wykonywana oddzielnie na serwerze.

  • Użyj programu narzędzi bcp lub narzędzia SQLServerBulkCopy , aby załadować wiele wierszy danych do tabeli. Mimo że ta technika jest wydajna, nie obsługuje przetwarzania po stronie serwera, chyba że dane są ładowane do tymczasowej tabeli lub zmiennej tabeli.

Tworzenie typów parametrów tabelarycznych

Parametry wartości tabeli są oparte na silnie typiowanych strukturach tabel, które są definiowane przy użyciu instrukcji CREATE TYPE Transact-SQL. Musisz utworzyć typ tabeli i zdefiniować strukturę w programie SQL Server, zanim będzie można używać parametrów wartości tabeli w aplikacjach klienckich. Aby uzyskać więcej informacji na temat tworzenia typów tabel, zobacz Typy tabel definiowane przez użytkownika w dokumentacji SQL Server Books Online.

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

Po utworzeniu typu tabeli można zadeklarować parametry wartości tabeli na podstawie tego typu. Poniższy fragment Transact-SQL pokazuje, jak zadeklarować parametr o wartości tabeli w definicji procedury składowanej. Słowo kluczowe READONLY jest wymagane do deklarowania parametru tabelarycznego.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Modyfikowanie danych przy użyciu parametrów wartości tabeli (Transact-SQL)

Parametry wartości tabeli mogą być używane w modyfikacjach danych opartych na zestawie, które wpływają na wiele wierszy, wykonując jedną instrukcję. Można na przykład wybrać wszystkie wiersze w parametrze z wartością tabeli i wstawić je do tabeli bazy danych lub utworzyć instrukcję aktualizacji, łącząc parametr z wartością tabeli do tabeli, którą chcesz zaktualizować.

Poniższa instrukcja Transact-SQL UPDATE pokazuje, jak używać parametru o wartości tabeli przez dołączenie go do tabeli Categories. W przypadku używania parametru typu table-valued z klauzulą JOIN w klauzuli FROM należy również aliasować go, jak pokazano tutaj, gdzie parametr wartości tabeli ma alias "ec":

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

W tym przykładzie Transact-SQL pokazano, jak wybrać wiersze z parametru wartości tabeli w celu wykonania operacji INSERT w ramach jednej operacji opartej na zestawie.

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

Ograniczenia parametrów wartości tabeli

Istnieje kilka ograniczeń dotyczących parametrów wartości tabeli:

  • Nie można przekazać parametrów wartości tabeli do funkcji zdefiniowanych przez użytkownika.

  • Parametry typu tabelarycznego można indeksować tylko w celu obsługi ograniczeń przy unikalnych lub podstawowych kluczach. Program SQL Server nie obsługuje statystyk dotyczących parametrów wartości tabeli.

  • Parametry wartości tabeli są tylko do odczytu w kodzie Transact-SQL. Nie można zaktualizować wartości kolumn w wierszach parametru wartości tabeli i nie można wstawiać ani usuwać wierszy. Aby zmodyfikować dane przekazywane do procedury składowanej lub sparametryzowanej instrukcji w parametrze wartości tabeli, należy wstawić dane do tabeli tymczasowej lub do zmiennej tabeli.

  • Nie można użyć instrukcji ALTER TABLE, aby zmodyfikować projekt parametrów wartości tabeli.

  • Duże obiekty można przesyłać strumieniowo w parametrze typu tabeli.

Konfigurowanie parametru wartości tabeli

Począwszy od sterownika JDBC firmy Microsoft 6.0 dla programu SQL Server, parametry wartości tabeli są obsługiwane za pomocą sparametryzowanej instrukcji lub sparametryzowanej procedury składowanej. Parametry wartości tabeli mogą być wypełniane z SQLServerDataTable, z ResultSetu lub z implementacji interfejsu ISQLServerDataRecord dostarczonej przez użytkownika. Podczas ustawiania parametru wartości tabeli dla przygotowanego zapytania należy określić nazwę typu, która musi być zgodna z nazwą zgodnego typu utworzonego wcześniej na serwerze.

W poniższych dwóch fragmentach kodu pokazano, jak skonfigurować parametr wartości tabeli za pomocą parametru SQLServerPreparedStatement i sqlServerCallableStatement w celu wstawienia danych. Tutaj sourceTVPObject może być obiektem SQLServerDataTable, lub ResultSet, lub obiektem typu ISQLServerDataRecord. W przykładach przyjęto założenie, że połączenie jest aktywnym obiektem Połączenia.

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

Uwaga / Notatka

Zobacz sekcję Table-Valued Parameter API for the JDBC Driver poniżej, aby uzyskać pełną listę dostępnych interfejsów API służących do ustawiania parametru typu tabela.

Przekazywanie parametru o wartości tabeli jako obiektu SQLServerDataTable

Począwszy od sterownika JDBC firmy Microsoft 6.0 dla programu SQL Server, klasa SQLServerDataTable reprezentuje tabelę danych relacyjnych w pamięci. W tym przykładzie pokazano, jak utworzyć parametr o wartości tabeli z danych w pamięci przy użyciu obiektu SQLServerDataTable. Najpierw kod tworzy obiekt SQLServerDataTable, definiuje jego schemat i wypełnia tabelę danymi. Następnie kod konfiguruje parametr SQLServerPreparedStatement, który przekazuje tę tabelę danych jako parametr o wartości tabeli do programu 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();

Ten przykład jest podobny do poprzedniego. Jedyną różnicą jest to, że ustawia nazwę TVP na SQLServerDataTable zamiast polegać na rzutowaniu PreparedStatement na SQLServerPreparedStatement, aby użyć metody 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();

Uwaga / Notatka

Zobacz sekcję Interfejs API parametrów tabelarycznych dla sterownika JDBC poniżej, aby uzyskać pełną listę interfejsów API dostępnych do ustawiania parametru tabelarycznego.

Przekazywanie parametru o wartości tabeli jako obiektu ResultSet

W tym przykładzie pokazano, jak przesyłać strumieniowo wiersze danych z elementu ResultSet do parametru o wartości tabeli. Najpierw kod pobiera dane z tabeli źródłowej w obiekcie SQLServerDataTable, definiuje jego schemat i wypełnia tabelę danymi. Następnie kod konfiguruje parametr SQLServerPreparedStatement, który przekazuje tę tabelę danych jako parametr o wartości tabeli do programu 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();

Uwaga / Notatka

Zobacz sekcję interfejs API parametru o wartości tabeli dla sterownika JDBC poniżej, aby uzyskać pełną listę interfejsów API dostępnych do ustawiania parametru o wartości tabeli.

Przekazywanie parametru o wartości tabeli jako obiektu ISQLServerDataRecord

Począwszy od sterownika JDBC firmy Microsoft 6.0 dla programu SQL Server, nowy interfejs ISQLServerDataRecord jest dostępny dla danych przesyłanych strumieniowo (w zależności od sposobu, w jaki użytkownik udostępnia jego implementację) przy użyciu parametru wartości tabeli. W poniższym przykładzie pokazano, jak zaimplementować interfejs ISQLServerDataRecord i jak przekazać go jako parametr o wartości tabeli. Dla uproszczenia, poniższy przykład przekazuje tylko jeden wiersz z wpisanymi na stałe wartościami do parametru typu tabela. W idealnym przypadku użytkownik implementuje ten interfejs w celu strumieniowego przesyłania wierszy z dowolnego źródła, na przykład z plików tekstowych.

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

Uwaga / Notatka

Zobacz Sekcję Interfejs API parametrów wartości tabeli dla sterownika JDBC poniżej, aby uzyskać pełną listę interfejsów API dostępnych do ustawiania parametru wartości tabeli.

Interfejs API parametrów wartości tabeli dla sterownika JDBC

SQLServerMetaData

Ta klasa reprezentuje metadane dla kolumny. Jest on używany w interfejsie ISQLServerDataRecord do przekazywania metadanych kolumn do parametru wartości tabeli. Metody w tej klasie to:

Name Description
public SQLServerMetaData(String nazwaKolumny, int typSQL, int precyzja, int skala, boolean uzycieDomyslneSerwera, boolean jestKluczemUnikalnym, SQLServerSortOrder porzadekSortowania, int sortOrdynalny) Inicjuje nowe wystąpienie SQLServerMetaData z określoną nazwą kolumny, typem SQL, precyzją, skalą i domyślną wartością serwera. Ta forma konstruktora obsługuje parametry wartości tabeli, umożliwiając określenie, czy kolumna jest unikatowa w parametrze wartości tabeli, kolejności sortowania kolumny i porządkowości kolumny sortowania.

useServerDefault — określa, czy ta kolumna powinna używać domyślnej wartości serwera; Wartość domyślna to false.
isUniqueKey — wskazuje, czy kolumna w parametrze table-valued jest unikatowa; Wartość domyślna to false.
sortOrder — wskazuje kolejność sortowania dla kolumny; Wartość domyślna to SQLServerSortOrder.Unspecified.
sortOrdinal — określa porządkowość kolumny sortowania; sortOrdinal zaczyna się od 0; Wartość domyślna to -1.
public SQLServerMetaData(String columnName, int sqlType) Inicjuje nowe wystąpienie SQLServerMetaData przy użyciu nazwy kolumny i typu SQL.
public SQLServerMetaData(String columnName, int sqlType, int length) Inicjuje nowe wystąpienie SQLServerMetaData przy użyciu nazwy kolumny, typu SQL i długości (dla danych ciągów). Długość służy do rozróżniania dużych ciągów od ciągów o długości mniejszej niż 4000 znaków. Wprowadzony w wersji 7.2 sterownika JDBC.
publiczny SQLServerMetaData(String columnName, int sqlType, int precision, int scale) Inicjuje nowe wystąpienie SQLServerMetaData przy użyciu nazwy kolumny, typu SQL, precyzji i skali.
Public SQLServerMetaData(SQLServerMetaData sqlServerMetaData) Inicjuje nowe wystąpienie SQLServerMetaData na podstawie innego obiektu SQLServerMetaData.
public String getColumnName() Pobiera nazwę kolumny.
public int getSqlType() Pobiera typ sql języka Java.
public int getPrecision() Pobiera dokładność typu przekazanego do kolumny.
public int getScale() Pobiera skalę typu przekazanego do kolumny.
public SQLServerSortOrder getSortOrder() Pobiera kolejność sortowania.
public int getSortOrdinal() Pobiera porządkowy numer sortowania.
wartość logiczna public isUniqueKey() Zwraca, czy kolumna jest unikatowa.
public boolean useServerDefault() Zwraca, czy kolumna używa domyślnej wartości serwera.

SQLServerSortOrder

Wyliczenie definiujące kolejność sortowania. Możliwe wartości to Rosnąco, Malejąco i Nieokreślone.

SQLServerDataTable

Ta klasa reprezentuje tabelę danych w pamięci, która ma być używana z parametrami wartości tabeli. Metody w tej klasie to:

Name Description
Public SQLServerDataTable() Inicjuje nową instancję SQLServerDataTable.
public Iterator<Entry<Integerer, Object[]>> getIterator() Pobiera iterator w wierszach tabeli danych.
public void addColumnMetadata(String columnName, int sqlType) Dodaje metadane dla określonej kolumny.
public void addColumnMetadata(kolumna SQLServerDataColumn) Dodaje metadane dla określonej kolumny.
public void addRow(Object... wartość) Dodaje jeden wiersz danych do tabeli danych.
public Map getColumnMetadata() Pobiera metadane kolumn tej tabeli danych.
public void clear() Czyści tę tabelę danych.

SQLServerDataColumn

Ta klasa reprezentuje kolumnę tabeli danych w pamięci reprezentowanej przez tabelę SQLServerDataTable. Metody w tej klasie to:

Name Description
public SQLServerDataColumn(String columnName, int sqlType) Inicjuje nowe wystąpienie klasy SQLServerDataColumn z nazwą i typem kolumny.
public String getColumnName() Pobiera nazwę kolumny.
public int getColumnType() Pobiera typ kolumny.

ISQLServerDataRecord

Ta klasa reprezentuje interfejs, który użytkownicy mogą implementować w celu przesyłania strumieniowego danych do parametru tabelarycznego. Metody w tym interfejsie to:

Name Description
public SQLServerMetaData getColumnMetaData(kolumna int); Pobiera metadane kolumny z danego indeksu kolumny.
public int getColumnCount(); // Zwraca liczbę kolumn Pobiera łączną liczbę kolumn.
public Object[] getRowData(); Pobiera dane dla bieżącego wiersza jako tablicę obiektów.
public boolean next(); Przechodzi do następnego wiersza. Zwraca wartość True, jeśli przeniesienie zakończy się pomyślnie i istnieje następny wiersz, w przeciwnym razie wartość false.

SQLServerPreparedStatement

Następujące metody zostały dodane do tej klasy w celu obsługi przekazywania parametrów wartości tabeli.

Name Description
public final void setStructured(int parameterIndex, String tvpName, SQLServerDataTable tvpDataTable) Uzupełnia parametry tabelaryczne przy pomocy tablicy danych. parameterIndex jest indeksem parametrów, tvpName jest nazwą parametru o wartości tabeli, a tvpDataTable jest obiektem tabeli danych źródłowych.
public final void setStructured(int parameterIndex, String tvpName, ResultSet tvpResultSet) Wypełnia parametr o wartości tabeli parametrem ResultSet pobranym z innej tabeli. parameterIndex jest indeksem parametrów, tvpName jest nazwą parametru wartości tabeli, a tvpResultSet jest obiektem zestawu wyników źródłowych.
public final void setStructured(int parameterIndex, String tvpName, ISQLServerDataRecord tvpDataRecord) Wypełnia parametr o wartości tabeli obiektem ISQLServerDataRecord. ISQLServerDataRecord jest używany do przesyłania strumieniowego danych, a użytkownik decyduje, jak go używać. parameterIndex jest indeksem parametrów, tvpName jest nazwą parametru wartości tabeli, a tvpDataRecord jest obiektem ISQLServerDataRecord.

SQLServerCallableStatement

Następujące metody zostały dodane do tej klasy w celu obsługi przekazywania parametrów wartości tabeli.

Name Description
public final void setStructured(String parameterName, String tvpName, SQLServerDataTable tvpDataTable) Wypełnia tabelą danych parametr typu tabelarycznego przekazywany do procedury składowanej. parameterName to nazwa parametru, tvpName jest nazwą typu TVP, a tvpDataTable jest obiektem tabeli danych.
public final void setStructured(String parameterName, String tvpName, ResultSet tvpResultSet) Wypełnia parametr typu tabelarycznego przekazany do procedury składowanej używając ResultSet pobranego z innej tabeli. parameterName to nazwa parametru, tvpName jest nazwą typu TVP, a tvpResultSet jest obiektem zestawu wyników źródłowych.
public final void setStructured(String parameterName, String tvpName, ISQLServerDataRecord tvpDataRecord) Wypełnia parametr o wartości tabeli przekazany do procedury składowanej za pomocą obiektu ISQLServerDataRecord. ISQLServerDataRecord jest używany do przesyłania strumieniowego danych, a użytkownik decyduje, jak go używać. parameterName to nazwa parametru, tvpName jest nazwą typu TVP, a tvpDataRecord jest obiektem ISQLServerDataRecord.

Zobacz także

Omówienie sterownika JDBC

  • Last updated on