Megosztás:

Táblaértékű paraméterek használata

JDBC-illesztőprogram letöltése

A táblaértékkel rendelkező paraméterek lehetővé teszik több adatsor üzembe helyezése egy ügyfélalkalmazásból az SQL Serverre anélkül, hogy több ciklikus utazást vagy speciális kiszolgálóoldali logikát kellene megkövetelni az adatok feldolgozásához. Táblaértékkel rendelkező paraméterekkel befoglalhatja egy ügyfélalkalmazás adatsorait, és egyetlen paraméteres parancsban küldheti el az adatokat a kiszolgálónak. A bejövő adatsorok egy táblaváltozóban vannak tárolva, amely a Transact-SQL használatával működtethető.

A táblaértékkel rendelkező paraméterek oszlopértékei standard Transact-SQL SELECT utasításokkal érhetők el. A táblaértékű paraméterek erősen be vannak gépelve, és a rendszer automatikusan érvényesíti a szerkezetüket. A táblaértékelt paraméterek méretét csak a kiszolgáló memóriája korlátozza.

Megjegyzés:

A Table-Valued paraméterek támogatása az SQL Server-hez készült Microsoft JDBC Driver 6.0-tól érhető el.

Táblaértékkel rendelkező paraméterben nem lehet adatokat visszaadni. Az táblaértékkel rendelkező paraméterek csak bemenetként használhatók; az OUTPUT kulcsszó nem támogatott.

A táblaértékű paraméterekkel kapcsolatos további információkért tekintse meg az alábbi erőforrásokat.

Resource Description
Table-Valued paraméterek (adatbázismotor) az SQL Server Books Online-ban A táblaértékű paraméterek létrehozásának és használatának ismertetése
Felhasználó által definiált táblázattípusok az SQL Server Books Online-ban A táblaértékelt paraméterek deklarálásához használt, felhasználó által definiált táblatípusokat ismerteti

Több sor átadása az SQL Server korábbi verzióiban

Az SQL Server 2008 táblaértékkel rendelkező paramétereinek bevezetése előtt a több sornyi adat tárolt eljárásnak vagy paraméteres SQL-parancsnak való továbbításának lehetőségei korlátozottak voltak. A fejlesztő a következő lehetőségek közül választhat, ha több sort ad át a kiszolgálónak:

  • Az egyes paraméterek sorozatával több oszlopban és adatsorban szereplő értékeket jelölhet. Az ezzel a módszerrel átadható adatok mennyiségét az engedélyezett paraméterek száma korlátozza. Az SQL Server-eljárások legfeljebb 2100 paraméterrel rendelkezhetnek. A kiszolgálóoldali logika szükséges ahhoz, hogy ezeket az egyedi értékeket egy táblaváltozóba vagy egy ideiglenes táblába állítsa össze feldolgozásra.

  • Több adatértéket csoportosított sztringekbe vagy XML-dokumentumokba csomagolhat, majd ezeket a szöveges értékeket átadhatja egy eljárásnak vagy utasításnak. Ehhez az eljárásnak vagy utasításnak tartalmaznia kell az adatstruktúrák érvényesítéséhez és az értékek szétválasztásához szükséges logikát.

  • Több sort érintő adatmódosításokhoz hozzon létre egyéni SQL-utasításokat. A módosításokat egyenként vagy csoportokba kötegelve is elküldheti a kiszolgálónak. Azonban még akkor is, ha több utasítást tartalmazó kötegekben küldik el, a rendszer minden utasítást külön hajt végre a kiszolgálón.

  • A bcp segédprogram vagy az SQLServerBulkCopy használatával több adatsort tölthet be egy táblába. Bár ez a technika hatékony, nem támogatja a kiszolgálóoldali feldolgozást, hacsak az adatokat nem tölti be egy ideiglenes tábla- vagy táblaváltozóba.

Táblaértékű paramétertípusok létrehozása

A táblaértékes paraméterek Transact-SQL utasítások használatával CREATE TYPE definiált, erősen gépelt táblázatstruktúrákon alapulnak. Ahhoz, hogy táblaértékű paramétereket használhasson az ügyfélalkalmazásokban, létre kell hoznia egy táblázattípust, és meg kell határoznia a struktúrát az SQL Serverben. További információ a táblatípusok létrehozásáról lásd az SQL Server Online Könyvtárban a Felhasználó Által Definiált Táblatípusok részben.

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

A táblatípus létrehozása után az adott típus alapján deklarálhatja a táblaértékelt paramétereket. Az alábbi Transact-SQL töredék bemutatja, hogyan deklarálhat táblaértékű paramétert egy tárolt eljárásdefinícióban. A READONLY kulcsszó egy táblaértékű paraméter deklarálásához szükséges.

CREATE PROCEDURE usp_UpdateCategories
    (@tvpNewCategories dbo.CategoryTableType READONLY)

Adatok módosítása táblaértékkel rendelkező paraméterekkel (Transact-SQL)

A táblaértékkel rendelkező paraméterek egy utasítás végrehajtásával több sort érintő, set-alapú adatmódosításokban használhatók. Kijelölheti például egy táblaértékű paraméter összes sorát, és beszúrhatja őket egy adatbázistáblába, vagy létrehozhat egy frissítési utasítást úgy, hogy egy táblaértékű paramétert csatlakozik a frissíteni kívánt táblához.

Az alábbi Transact-SQL UPDATE utasítás bemutatja, hogyan használható táblaértékű paraméter a Kategóriák táblához való csatlakozással. Ha táblaértékkel rendelkező paramétert használ egy JOIN záradékban, akkor azt is aliasként kell használnia, ahogy az itt látható, ahol a táblaértékként megadott paraméter "ec" néven van elnevezve:

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

Ez a Transact-SQL példa bemutatja, hogyan jelölhet ki sorokat egy táblaértékkel rendelkező paraméterből az INSERT egyetlen készletalapú műveletben való végrehajtásához.

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

A táblaértékelt paraméterek korlátozásai

A táblaértékelő paraméterekre számos korlátozás vonatkozik:

  • A táblaértékű paramétereket nem lehet átadni a felhasználó által definiált függvényeknek.

  • A táblaértékkel rendelkező paraméterek csak az EGYEDI vagy AZ ELSŐDLEGES KULCS korlátozásainak támogatásához indexelhetők. Az SQL Server nem tartja fenn a táblaértékelt paraméterek statisztikáit.

  • A táblaértékkel rendelkező paraméterek írásvédettek Transact-SQL kódban. Nem frissítheti az oszlopértékeket egy táblaértékű paraméter soraiban, és nem szúrhat be és nem törölhet sorokat. A táblaértékelő paraméterben tárolt eljárásnak vagy paraméteres utasításnak átadott adatok módosításához be kell szúrnia az adatokat egy ideiglenes táblába vagy egy táblaváltozóba.

  • Az ALTER TABLE utasításokkal nem módosíthatja a táblaértékelt paraméterek kialakítását.

  • Nagy méretű objektumokat streamelhet egy táblaértékkel rendelkező paraméterben.

Táblaértékű paraméter konfigurálása

Az SQL Server Microsoft JDBC Driver 6.0-s verziójától kezdve a táblaértékelő paraméterek paraméteres utasítással vagy paraméteres tárolt eljárással támogatottak. A táblaértékű paraméterek feltölthetők SQLServerDataTable-ból, ResultSetből vagy az ISQLServerDataRecord felület egy felhasználó által biztosított implementációjából. Ha táblaértékű paramétert állít be egy előkészített lekérdezéshez, meg kell adnia egy típusnevet, amelynek meg kell egyeznie a kiszolgálón korábban létrehozott kompatibilis típus nevével.

Az alábbi két kódrészlet bemutatja, hogyan konfigurálhat táblaértékű paramétert egy SQLServerPreparedStatement és egy SQLServerCallableStatement használatával az adatok beszúrásához. Itt a sourceTVPObject lehet SQLServerDataTable, ResultSet vagy ISQLServerDataRecord objektum. A példák feltételezik, hogy a kapcsolat aktív kapcsolatobjektum.

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

Megjegyzés:

Az elérhető API-k teljes listáját, amelyek a táblaértékű paraméterek beállításához használhatók, az alábbi Table-Valued Parameter API for the JDBC Driver szakasz tartalmazza.

Táblaértékű paraméter átadása SQLServerDataTable objektumként

Az SQL Server Microsoft JDBC Driver 6.0-s verziójától kezdve az SQLServerDataTable osztály a relációs adatok memóriában lévő tábláját jelöli. Ez a példa bemutatja, hogyan hozhat létre táblaértékű paramétert a memóriában lévő adatokból az SQLServerDataTable objektum használatával. A kód először létrehoz egy SQLServerDataTable objektumot, meghatározza annak sémáját, és feltölti a táblát adatokkal. A kód ezután konfigurál egy SQLServerPreparedStatement parancsot, amely táblaértékű paraméterként továbbítja ezt az adattáblát az SQL Servernek.

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

Ez a példa az előzőhöz hasonló. Az egyetlen különbség az, hogy beállítja a TVP-nevet a SQLServerDataTable helyett, hogy a PreparedStatement a SQLServerPreparedStatement metódus használatához setStructured történő öntésére támaszkodna.

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

Megjegyzés:

A táblaértékű paraméter beállításához elérhető API-k teljes listáját lásd az alábbi JDBC-illesztő Table-Valued Parameter API szakaszában.

Táblaértékű paraméter átadása ResultSet-objektumként

Ez a példa bemutatja, hogyan streamelhet adatsorokat egy ResultSetből egy táblaértékű paraméterbe. A kód először lekéri az adatokat egy SQLServerDataTable objektum forrástáblájából, meghatározza annak sémáját, és feltölti a táblát adatokkal. A kód ezután konfigurál egy SQLServerPreparedStatement parancsot, amely táblaértékű paraméterként továbbítja ezt az adattáblát az SQL Servernek.

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

Megjegyzés:

A táblaértékű paraméter beállításához elérhető API-k teljes listáját az alábbi JDBC-illesztő Table-Valued Paraméter API szakaszában találja.

Táblaértékű paraméter átadása ISQLServerDataRecord objektumként

Az SQL ServerHez készült Microsoft JDBC Driver 6.0-tól kezdve egy új ISQLServerDataRecord interfész érhető el a streamelési adatokhoz (attól függően, hogy a felhasználó hogyan biztosítja a megvalósítást) egy táblaértékű paraméter használatával. Az alábbi példa bemutatja, hogyan implementálható az ISQLServerDataRecord interfész, és hogyan adható át táblaértékű paraméterként. Az egyszerűség kedvéért az alábbi példa csak egy olyan sort ad át a táblaértékkel rendelkező paraméternek, amely a rögzített értékeket tartalmazza. Ideális esetben a felhasználó implementálná ezt az felületet, hogy sorokat streameljen bármilyen forrásból, például szövegfájlokból.

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

Megjegyzés:

A táblaértékelő paraméter beállításához elérhető API-k teljes listáját az alábbi JDBC-illesztő táblaértékelő API-jának szakaszában találja.

Táblázat-értékű paraméter API a JDBC illesztőprogramhoz

SQLServerMetaData

Ez az osztály egy oszlop metaadatait jelöli. Az ISQLServerDataRecord felületen használják az oszlop metaadatainak a táblaértékű paraméternek való átadására. Az osztály metódusai a következők:

Név Description
public SQLServerMetaData(String columnName, int sqlType, int precision, int scale, boolean useServerDefault, boolean isUniqueKey, SQLServerSortOrder sortOrder, int sortOrdinal) Inicializálja az SQLServerMetaData új példányát a megadott oszlopnévvel, SQL-típussal, pontossággal, skálázással és kiszolgáló alapértelmezés szerint. A konstruktor ezen formája támogatja a táblaértékű paramétereket azáltal, hogy megadhatja, hogy az oszlop egyedi-e a táblaértékű paraméterben, az oszlop rendezési sorrendjét, valamint a rendezendő oszlop sorszámát.

useServerDefault – megadja, hogy ennek az oszlopnak az alapértelmezett kiszolgálóértéket kell-e használnia; Az alapértelmezett érték hamis.
isUniqueKey – azt jelzi, hogy a táblaértékelő paraméter oszlopa egyedi-e; Az alapértelmezett érték hamis.
sortOrder – egy oszlop rendezési sorrendjét jelzi; Az alapértelmezett érték az SQLServerSortOrder.Unspecified.
sortOrdinal – a rendezési oszlop sorszámát adja meg; a sortOrdinal 0-tól kezdődik; Az alapértelmezett érték -1.
public SQLServerMetaData(String columnName, int sqlType) Inicializálja az SQLServerMetaData új példányát az oszlopnév és az SQL-típus használatával.
public SQLServerMetaData(String columnName, int sqlType, int length) Inicializálja az SQLServerMetaData új példányát az oszlop nevével, az SQL-típussal és a hosszsal (sztringadatok esetében). A hossz a nagy sztringek és a 4000 karakternél kisebb hosszúságú sztringek megkülönböztetésére szolgál. A JDBC-illesztőprogram 7.2-es verziójában jelent meg.
public SQLServerMetaData(String columnName, int sqlType, int precision, int scale) Inicializálja az SQLServerMetaData új példányát az oszlopnév, SQL típus, skála és pontosság használatával.
Nyilvános SQLServerMetaData(SQLServerMetaData sqlServerMetaData) Inicializálja az SQLServerMetaData új példányát egy másik SQLServerMetaData objektumból.
public String getColumName() Lekéri az oszlop nevét.
public int getSqlType() Lekéri a java sql-típust.
public int getPrecision() Az oszlophoz megadott típus pontosságát határozza meg.
public int getScale() Lekéri az oszlopnak átadott típus skáláját.
nyilvános SQLServerSortOrder getSortOrder() Lekéri a rendezési sorrendet.
public int getSortOrdinal() Lekéri a rendezési sorrendet.
nyilvános boolean isUniqueKey() Azt adja vissza, hogy az oszlop egyedi-e.
public boolean useServerDefault() Azt adja vissza, hogy az oszlop az alapértelmezett kiszolgálóértéket használja-e.

SQLServerSortOrder

Az Enum, amely meghatározza a rendezési sorrendet. A lehetséges értékek növekvőek, csökkenőek és meghatározatlanok.

SQLServerDataTable

Ez az osztály egy memóriabeli adattáblát jelöl, amelyet táblaértékkel rendelkező paraméterekkel kell használni. Az osztály metódusai a következők:

Név Description
Nyilvános SQLServerDataTable() Inicializálja az SQLServerDataTable új példányát.
public Iterator<Entry<Integer, Object[]>> getIterator() Egy iterátort kér le az adattábla soraiból.
public void addColumnMetadata(String columnName, int sqlType) Metaadatokat ad hozzá a megadott oszlophoz.
public void addColumnMetadata(SQLServerDataColumn oszlop) Metaadatokat ad hozzá a megadott oszlophoz.
public void addRow(Object... értékek) // Sor hozzáadása tetszőleges számú objektummal Egy adatsort ad hozzá az adattáblához.
public Map<Integer, SQLServerDataColumn> getColumnMetadata() Az adattábla oszlop metaadatait kéri le.
public void clear() Törli ezt az adattáblát.

SQLServerDataColumn

Ez az osztály az SQLServerDataTable által képviselt memóriabeli adattábla oszlopát jelöli. Az osztály metódusai a következők:

Név Description
public SQLServerDataColumn(String columnName, int sqlType) Inicializálja az SQLServerDataColumn új példányát az oszlop nevével és típusával.
public String getColumnName() Lekéri az oszlop nevét.
public int getColumnType() Lekéri az oszloptípust.

ISQLServerDataRecord

Ez az osztály egy olyan felületet jelöl, amelyet a felhasználók implementálhatnak, hogy adatokat streameljenek egy táblaértékű paraméterbe. Az ezen a felületen található módszerek a következők:

Név Description
nyilvános SQLServerMetaData getColumnMetaData(int column); Lekéri az adott oszlopindex oszlop metaadatait.
public int getColumnCount(); Lekéri az oszlopok teljes számát.
public Object[] getRowData(); Az aktuális sor adatait objektumtömbként kéri le.
public boolean next(); Ugrás a következő sorra. Igaz értéket ad vissza, ha az áthelyezés sikeres, és van egy következő sor, máskülönben hamis.

SQLServerPreparedStatement

Az alábbi metódusok lettek hozzáadva ehhez az osztályhoz, hogy támogassák a táblaértékű paraméterek átadását.

Név Description
public final void setStructured(int parameterIndex, String tvpName, SQLServerDataTable tvpDataTable) Egy táblaértékű paramétert tölt fel egy adattáblával. A parameterIndex a paraméterindex, a tvpName a táblaértékű paraméter neve, a tvpDataTable pedig a forrásadattábla objektuma.
public final void setStructured(int parameterIndex, String tvpName, ResultSet tvpResultSet) Egy táblaértékű paramétert tölt fel egy másik táblából lekért ResultSettel. A parameterIndex a paraméterindex, a tvpName a táblaértékkel rendelkező paraméter neve, a tvpResultSet pedig a forrás eredményhalmaz objektuma.
public final void setStructured(int parameterIndex, String tvpName, ISQLServerDataRecord tvpDataRecord) Egy táblaértékű paramétert tölt fel egy ISQLServerDataRecord objektummal. Az ISQLServerDataRecord az adatok streameléséhez használatos, és a felhasználó dönti el, hogyan kell használni. A parameterIndex a paraméterindex, a tvpName a táblaértékkel rendelkező paraméter neve, a tvpDataRecord pedig egy ISQLServerDataRecord objektum.

SQLServerCallableStatement

Az alábbi metódusok lettek hozzáadva ehhez az osztályhoz, hogy támogassák a táblaértékű paraméterek átadását.

Név Description
public final void setStructured(String parameterName, String tvpName, SQLServerDataTable tvpDataTable) Egy tárolt eljárásnak átadott táblaértékű paramétert tölt fel egy adattáblával. a parameterName a paraméter neve, a tvpName a TVP típus neve, a tvpDataTable pedig az adattábla-objektum.
public final void setStructured(String parameterName, String tvpName, ResultSet tvpResultSet) Tárolt eljárásnak átadott táblaérték-paramétert tölt fel egy másik táblából lekért ResultSet adatokkal. a parameterName a paraméter neve, a tvpName a TVP típus neve, a tvpResultSet pedig a forrás eredményhalmaz-objektuma.
public final void setStructured(String parameterName, String tvpName, ISQLServerDataRecord tvpDataRecord) Egy ISQLServerDataRecord objektummal feltölt egy tárolt eljárás számára átadott táblaértékű paramétert. Az ISQLServerDataRecord az adatok streameléséhez használatos, és a felhasználó dönti el, hogyan kell használni. A parameterName a paraméter neve, a tvpName a TVP típus neve, a tvpDataRecord pedig egy ISQLServerDataRecord objektum.

Lásd még

A JDBC-illesztőprogram áttekintése

  • Last updated on