Menggunakan parameter bernilai tabel
Parameter bernilai tabel menyediakan cara mudah untuk menyusun beberapa baris data dari aplikasi klien ke SQL Server tanpa memerlukan beberapa perjalanan bolak-balik atau logika sisi server khusus untuk memproses data. Anda dapat menggunakan parameter bernilai tabel untuk merangkum baris data dalam aplikasi klien dan mengirim data ke server dalam satu perintah berparameter. Baris data yang masuk disimpan dalam variabel tabel yang kemudian dapat dioperasikan dengan menggunakan Transact-SQL.
Nilai kolom dalam parameter bernilai tabel dapat diakses menggunakan pernyataan SELECT Transact-SQL standar. Parameter bernilai tabel diketik dengan kuat dan strukturnya divalidasi secara otomatis. Ukuran parameter bernilai tabel hanya dibatasi oleh memori server.
Catatan
Dukungan untuk Parameter Bernilai Tabel tersedia dimulai dengan Microsoft JDBC Driver 6.0 untuk SQL Server.
Anda tidak dapat mengembalikan data dalam parameter bernilai tabel. Parameter bernilai tabel hanya untuk input; kata kunci OUTPUT tidak didukung.
Untuk informasi selengkapnya tentang parameter bernilai tabel, lihat sumber daya berikut.
| Sumber daya | Deskripsi |
|---|---|
| Parameter Bernilai Tabel (Mesin Database) di Buku SQL Server Online | Menjelaskan cara membuat dan menggunakan parameter bernilai tabel |
| Jenis Tabel yang Ditentukan Pengguna di Buku SQL Server Online | Menjelaskan jenis tabel yang ditentukan pengguna yang digunakan untuk mendeklarasikan parameter bernilai tabel |
Meneruskan beberapa baris di versi SQL Server sebelumnya
Sebelum parameter bernilai tabel diperkenalkan ke SQL Server 2008, opsi untuk meneruskan beberapa baris data ke prosedur tersimpan atau perintah SQL berparameter dibatasi. Pengembang dapat memilih dari opsi berikut untuk meneruskan beberapa baris ke server:
Gunakan serangkaian parameter individu untuk mewakili nilai dalam beberapa kolom dan baris data. Jumlah data yang dapat diteruskan dengan menggunakan metode ini dibatasi oleh jumlah parameter yang diizinkan. Prosedur SQL Server dapat memiliki, paling banyak, 2100 parameter. Logika sisi server diperlukan untuk merakit nilai individu ini menjadi variabel tabel atau tabel sementara untuk diproses.
Bundel beberapa nilai data ke dalam string atau dokumen XML yang dibatasi, lalu berikan nilai teks tersebut ke prosedur atau pernyataan. Hal ini mengharuskan prosedur atau pernyataan untuk memasukkan logika yang diperlukan guna memvalidasi struktur data dan memisahkan nilai.
Buat serangkaian pernyataan SQL individual untuk modifikasi data yang memengaruhi beberapa baris. Perubahan dapat dikirimkan ke server secara individu atau dalam grup. Namun, bahkan ketika dikirimkan dalam kumpulan yang berisi banyak pernyataan, setiap pernyataan dijalankan secara terpisah di server.
Gunakan program utilitas bcp atau SQLServerBulkCopy untuk memuat banyak baris data ke dalam tabel. Meskipun teknik ini efisien, teknik ini tidak mendukung pemrosesan sisi server kecuali data dimuat ke dalam tabel sementara atau variabel tabel.
Membuat jenis parameter bernilai tabel
Parameter bernilai tabel didasarkan pada struktur tabel yang diketik dengan kuat yang ditentukan dengan menggunakan pernyataan Transact-SQL CREATE TYPE . Anda harus membuat jenis tabel dan menentukan struktur di SQL Server sebelum dapat menggunakan parameter bernilai tabel di aplikasi klien Anda. Untuk informasi selengkapnya tentang membuat jenis tabel, lihat Jenis Tabel yang Ditentukan Pengguna di SQL Server Books Online.
CREATE TYPE dbo.CategoryTableType AS TABLE
( CategoryID int, CategoryName nvarchar(50) )
Setelah membuat jenis tabel, Anda dapat mendeklarasikan parameter bernilai tabel berdasarkan jenis tersebut. Fragmen Transact-SQL berikut menunjukkan cara mendeklarasikan parameter bernilai tabel dalam definisi prosedur tersimpan. Kata READONLY kunci diperlukan untuk mendeklarasikan parameter bernilai tabel.
CREATE PROCEDURE usp_UpdateCategories
(@tvpNewCategories dbo.CategoryTableType READONLY)
Memodifikasi data dengan parameter bernilai tabel (Transact-SQL)
Parameter bernilai tabel dapat digunakan dalam modifikasi data berbasis set yang memengaruhi banyak baris dengan mengeksekusi satu pernyataan. Misalnya, Anda dapat memilih semua baris dalam parameter bernilai tabel dan menyisipkannya ke dalam tabel database, atau Anda dapat membuat pernyataan pembaruan dengan menggabungkan parameter bernilai tabel ke tabel yang ingin Anda perbarui.
Pernyataan UPDATE Transact-SQL berikut menunjukkan cara menggunakan parameter bernilai tabel dengan menggabungkannya ke tabel Kategori. Saat Anda menggunakan parameter bernilai tabel dengan JOIN dalam klausa FROM, Anda juga harus membuat alias, seperti yang ditunjukkan di sini, di mana parameter bernilai tabel memiliki alias "ec":
UPDATE dbo.Categories
SET Categories.CategoryName = ec.CategoryName
FROM dbo.Categories INNER JOIN @tvpEditedCategories AS ec
ON dbo.Categories.CategoryID = ec.CategoryID;
Contoh Transact-SQL ini menunjukkan cara memilih baris dari parameter bernilai tabel untuk melakukan INSERT dalam satu operasi berbasis set.
INSERT INTO dbo.Categories (CategoryID, CategoryName)
SELECT nc.CategoryID, nc.CategoryName FROM @tvpNewCategories AS nc;
Batasan parameter bernilai tabel
Ada beberapa batasan untuk parameter bernilai tabel:
Anda tidak dapat meneruskan parameter bernilai tabel ke fungsi yang ditentukan pengguna.
Parameter bernilai tabel hanya dapat diindeks untuk mendukung batasan UNIQUE atau PRIMARY KEY. SQL Server tidak mempertahankan statistik pada parameter bernilai tabel.
Parameter bernilai tabel bersifat baca-saja dalam kode Transact-SQL. Anda tidak dapat memperbarui nilai kolom dalam baris parameter bernilai tabel dan Anda tidak dapat menyisipkan atau menghapus baris. Untuk mengubah data yang diteruskan ke prosedur tersimpan atau pernyataan berparameter dalam parameter bernilai tabel, Anda harus memasukkan data ke dalam tabel sementara atau ke dalam variabel tabel.
Anda tidak dapat menggunakan pernyataan ALTER TABLE untuk mengubah desain parameter bernilai tabel.
Anda dapat mengalirkan objek besar dalam parameter bernilai tabel.
Mengonfigurasi parameter bernilai tabel
Dimulai dengan Microsoft JDBC Driver 6.0 untuk SQL Server, parameter bernilai tabel didukung dengan pernyataan berparameter atau prosedur tersimpan berparameter. Parameter bernilai tabel dapat diisi dari SQLServerDataTable, dari ResultSet atau dari implementasi antarmuka ISQLServerDataRecord yang disediakan pengguna. Saat mengatur parameter bernilai tabel untuk kueri yang disiapkan, Anda harus menentukan nama jenis, yang harus cocok dengan nama jenis yang kompatibel yang sebelumnya dibuat di server.
Dua fragmen kode berikut menunjukkan cara mengonfigurasi parameter bernilai tabel dengan SQLServerPreparedStatement dan dengan SQLServerCallableStatement untuk menyisipkan data. Di sini sourceTVPObject dapat berupa SQLServerDataTable, atau ResultSet atau objek ISQLServerDataRecord. Contoh mengasumsikan koneksi adalah objek Koneksi aktif.
// 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();
Catatan
Lihat Bagian API Parameter Bernilai Tabel untuk Driver JDBC di bawah ini untuk daftar lengkap API yang tersedia untuk mengatur parameter bernilai tabel.
Meneruskan parameter bernilai tabel sebagai objek SQLServerDataTable
Dimulai dengan Microsoft JDBC Driver 6.0 untuk SQL Server, kelas SQLServerDataTable mewakili tabel data relasional dalam memori. Contoh ini menunjukkan cara membuat parameter bernilai tabel dari data dalam memori menggunakan objek SQLServerDataTable. Pertama, kode membuat objek SQLServerDataTable, menentukan skemanya, dan mengisi tabel dengan data. Kode kemudian mengonfigurasi SQLServerPreparedStatement yang meneruskan tabel data ini sebagai parameter bernilai tabel ke 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();
Contoh ini serupa dengan yang sebelumnya. Satu-satunya perbedaan adalah mengatur Nama TVP pada SQLServerDataTable alih-alih mengandalkan transmisi PreparedStatement ke SQLServerPreparedStatement untuk menggunakan setStructured metode .
/* 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();
Catatan
Lihat Bagian API Parameter Bernilai Tabel untuk Driver JDBC di bawah ini untuk daftar lengkap API yang tersedia untuk mengatur parameter bernilai tabel.
Meneruskan parameter bernilai tabel sebagai objek ResultSet
Contoh ini menunjukkan cara mengalirkan baris data dari ResultSet ke parameter bernilai tabel. Pertama, kode mengambil data dari tabel sumber dalam objek SQLServerDataTable, menentukan skemanya, dan mengisi tabel dengan data. Kode kemudian mengonfigurasi SQLServerPreparedStatement yang meneruskan tabel data ini sebagai parameter bernilai tabel ke 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();
Catatan
Lihat Bagian API Parameter Bernilai Tabel untuk Driver JDBC di bawah ini untuk daftar lengkap API yang tersedia untuk mengatur parameter bernilai tabel.
Meneruskan parameter bernilai tabel sebagai objek ISQLServerDataRecord
Dimulai dengan Microsoft JDBC Driver 6.0 untuk SQL Server, antarmuka baru ISQLServerDataRecord tersedia untuk data streaming (tergantung pada bagaimana pengguna menyediakan implementasi untuknya) menggunakan parameter bernilai tabel. Contoh berikut menunjukkan cara mengimplementasikan antarmuka ISQLServerDataRecord dan cara meneruskannya sebagai parameter bernilai tabel. Untuk kesederhanaan, contoh berikut hanya meneruskan satu baris dengan nilai yang dikodekan secara permanen ke parameter bernilai tabel. Idealnya, pengguna akan mengimplementasikan antarmuka ini untuk mengalirkan baris dari sumber apa pun, misalnya dari file teks.
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();
Catatan
Lihat BAGIAN API parameter bernilai tabel untuk driver JDBC di bawah ini untuk daftar lengkap API yang tersedia untuk mengatur parameter bernilai tabel.
API parameter bernilai tabel untuk driver JDBC
SQLServerMetaData
Kelas ini mewakili metadata untuk kolom. Ini digunakan dalam antarmuka ISQLServerDataRecord untuk meneruskan metadata kolom ke parameter bernilai tabel. Metode dalam kelas ini adalah:
| Nama | Deskripsi |
|---|---|
| SQLServerMetaData publik(String columnName, int sqlType, presisi int, skala int, boolean useServerDefault, boolean isUniqueKey, SQLServerSortOrder sortOrder, int sortOrdinal) | Menginisialisasi instans baru SQLServerMetaData dengan nama kolom, jenis sql, presisi, skala, dan default server yang ditentukan. Bentuk konstruktor ini mendukung parameter bernilai tabel dengan memungkinkan Anda menentukan apakah kolom unik dalam parameter bernilai tabel, urutan pengurutan untuk kolom, dan ordinal kolom pengurutan. useServerDefault - menentukan apakah kolom ini harus menggunakan nilai server default; Nilai defaultnya adalah false. isUniqueKey - menunjukkan apakah kolom dalam parameter bernilai tabel unik; Nilai defaultnya adalah false. sortOrder - menunjukkan urutan pengurutan untuk kolom; Nilai defaultnya adalah SQLServerSortOrder.Unspecified. sortOrdinal - menentukan ordinal kolom pengurutan; sortOrdinal dimulai dari 0; Nilai defaultnya adalah -1. |
| SQLServerMetaData publik(String columnName, int sqlType) | Menginisialisasi instans baru SQLServerMetaData menggunakan nama kolom dan jenis sql. |
| SQLServerMetaData publik(String columnName, int sqlType, int length) | Menginisialisasi instans baru SQLServerMetaData menggunakan nama kolom, jenis sql, dan panjangnya (untuk data String). Panjangnya digunakan untuk membedakan string besar dari string dengan panjang kurang dari 4000 karakter. Diperkenalkan dalam driver JDBC versi 7.2. |
| SQLServerMetaData publik(ColumnName String, int sqlType, presisi int, skala int) | Menginisialisasi instans baru SQLServerMetaData menggunakan nama kolom, jenis sql, presisi, dan skala. |
| SQLServerMetaData Publik(SQLServerMetaData sqlServerMetaData) | Menginisialisasi instans baru SQLServerMetaData dari objek SQLServerMetaData lain. |
| string publik getColumName() | Mengambil nama kolom. |
| public int getSqlType() | Mengambil Jenis java sql. |
| getPrecision() int publik | Mengambil presisi jenis yang diteruskan ke kolom. |
| public int getScale() | Mengambil skala jenis yang diteruskan ke kolom. |
| SQLServerSortOrder publik getSortOrder() | Mengambil urutan pengurutan. |
| public int getSortOrdinal() | Mengambil ordinal pengurutan. |
| boolean publik isUniqueKey() | Mengembalikan apakah kolom unik. |
| boolean publik useServerDefault() | Mengembalikan apakah kolom menggunakan nilai server default. |
SQLServerSortOrder
Enum yang menentukan urutan pengurutan. Nilai yang mungkin adalah Naik, Turun, dan Tidak Ditentukan.
SQLServerDataTable
Kelas ini mewakili tabel data dalam memori yang akan digunakan dengan parameter bernilai tabel. Metode dalam kelas ini adalah:
| Nama | Deskripsi |
|---|---|
| SQLServerDataTable Publik() | Menginisialisasi instans baru SQLServerDataTable. |
| Bilangan Bulat Entri<Iterator<publik, Objek[]>> getIterator() | Mengambil iterator pada baris tabel data. |
| public void addColumnMetadata(String columnName, int sqlType) | Menambahkan metadata untuk kolom yang ditentukan. |
| publik void addColumnMetadata(kolom SQLServerDataColumn) | Menambahkan metadata untuk kolom yang ditentukan. |
| addRow(Objek batal) publik... nilai) | Menambahkan satu baris data ke tabel data. |
| bilangan bulat peta<publik, SQLServerDataColumn> getColumnMetadata() | Mengambil data meta kolom tabel data ini. |
| public void clear() | Menghapus tabel data ini. |
SQLServerDataColumn
Kelas ini mewakili kolom tabel data dalam memori yang diwakili oleh SQLServerDataTable. Metode dalam kelas ini adalah:
| Nama | Deskripsi |
|---|---|
| SQLServerDataColumn publik(String columnName, int sqlType) | Menginisialisasi instans baru SQLServerDataColumn dengan nama dan jenis kolom. |
| string publik getColumnName() | Mengambil nama kolom. |
| getColumnType() int publik | Mengambil jenis kolom. |
ISQLServerDataRecord
Kelas ini mewakili antarmuka yang dapat diterapkan pengguna untuk mengalirkan data ke parameter bernilai tabel. Metode dalam antarmuka ini adalah:
| Nama | Deskripsi |
|---|---|
| SQLServerMetaData publik getColumnMetaData(kolom int); | Mengambil data meta kolom dari indeks kolom yang diberikan. |
| getColumnCount(); | Mengambil jumlah total kolom. |
| objek publik[] getRowData(); | Mengambil data untuk baris saat ini sebagai array Objek. |
| boolean publik berikutnya(); | Berpindah ke baris berikutnya. Mengembalikan True jika pemindahan berhasil dan ada baris berikutnya, salah jika tidak. |
SQLServerPreparedStatement
Metode berikut telah ditambahkan ke kelas ini untuk mendukung pengelompokan parameter bernilai tabel.
| Nama | Deskripsi |
|---|---|
| public final void setStructured(int parameterIndex, String tvpName, SQLServerDataTable tvpDataTable) | Mengisi parameter bernilai tabel dengan tabel data. parameterIndex adalah indeks parameter, tvpName adalah nama parameter bernilai tabel, dan tvpDataTable adalah objek tabel data sumber. |
| void final publik setStructured(int parameterIndex, String tvpName, ResultSet tvpResultSet) | Mengisi parameter bernilai tabel dengan ResultSet yang diambil dari tabel lain. parameterIndex adalah indeks parameter, tvpName adalah nama parameter bernilai tabel, dan tvpResultSet adalah objek kumpulan hasil sumber. |
| public final void setStructured(int parameterIndex, String tvpName, ISQLServerDataRecord tvpDataRecord) | Mengisi parameter bernilai tabel dengan objek ISQLServerDataRecord. ISQLServerDataRecord digunakan untuk data streaming dan pengguna memutuskan cara menggunakannya. parameterIndex adalah indeks parameter, tvpName adalah nama parameter bernilai tabel, dan tvpDataRecord adalah objek ISQLServerDataRecord. |
SQLServerCallableStatement
Metode berikut telah ditambahkan ke kelas ini untuk mendukung pengelompokan parameter bernilai tabel.
| Nama | Deskripsi |
|---|---|
| void final publik setStructured(String paratemeterName, String tvpName, SQLServerDataTable tvpDataTable) | Mengisi parameter bernilai tabel yang diteruskan ke prosedur tersimpan dengan tabel data. paratemeterName adalah nama parameter, tvpName adalah nama jenis TVP, dan tvpDataTable adalah objek tabel data. |
| void final publik setStructured(String paratemeterName, String tvpName, ResultSet tvpResultSet) | Mengisi parameter bernilai tabel yang diteruskan ke prosedur tersimpan dengan ResultSet yang diambil dari tabel lain. paratemeterName adalah nama parameter, tvpName adalah nama jenis TVP, dan tvpResultSet adalah objek tataan hasil sumber. |
| void final publik setStructured(String paratemeterName, String tvpName, ISQLServerDataRecord tvpDataRecord) | Mengisi parameter bernilai tabel yang diteruskan ke prosedur tersimpan dengan objek ISQLServerDataRecord. ISQLServerDataRecord digunakan untuk data streaming dan pengguna memutuskan cara menggunakannya. paratemeterName adalah nama parameter, tvpName adalah nama jenis TVP, dan tvpDataRecord adalah objek ISQLServerDataRecord. |