DEKLARASIKAN @local_variable (T-SQL)
Berlaku untuk:
SQL ServerAzure SQL DatabaseAzure SQL Managed InstanceAzure Synapse Analytics AnalyticsPlatform System (PDW)Titik akhir analitik SQL di Microsoft FabricWarehouse di Microsoft Fabric
Variabel dideklarasikan dalam isi batch atau prosedur dengan pernyataan DECLARE dan ditetapkan nilai dengan menggunakan pernyataan SET atau SELECT. Variabel kursor dapat dideklarasikan dengan pernyataan ini dan digunakan dengan pernyataan terkait kursor lainnya. Setelah deklarasi, semua variabel diinisialisasi sebagai NULL, kecuali nilai disediakan sebagai bagian dari deklarasi.
Sintaks
Sintaks berikut adalah untuk SQL Server dan Azure SQL Database:
DECLARE
{
{ @local_variable [AS] data_type [ = value ] }
| { @cursor_variable_name CURSOR }
} [ ,...n ]
| { @table_variable_name [AS] <table_type_definition> }
<table_type_definition> ::=
TABLE ( { <column_definition> | <table_constraint> | <table_index> } } [ ,...n ] )
<column_definition> ::=
column_name { scalar_data_type | AS computed_column_expression }
[ COLLATE collation_name ]
[ [ DEFAULT constant_expression ] | IDENTITY [ (seed, increment ) ] ]
[ ROWGUIDCOL ]
[ <column_constraint> ]
[ <column_index> ]
<column_constraint> ::=
{
[ NULL | NOT NULL ]
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
[ WITH FILLFACTOR = fillfactor
| WITH ( < index_option > [ ,...n ] )
[ ON { filegroup | "default" } ]
| [ CHECK ( logical_expression ) ] [ ,...n ]
}
<column_index> ::=
INDEX index_name [ CLUSTERED | NONCLUSTERED ]
[ WITH ( <index_option> [ ,... n ] ) ]
[ ON { partition_scheme_name (column_name )
| filegroup_name
| default
}
]
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
<table_constraint> ::=
{
{ PRIMARY KEY | UNIQUE }
[ CLUSTERED | NONCLUSTERED ]
( column_name [ ASC | DESC ] [ ,...n ]
[ WITH FILLFACTOR = fillfactor
| WITH ( <index_option> [ ,...n ] )
| [ CHECK ( logical_expression ) ] [ ,...n ]
}
<table_index> ::=
{
{
INDEX index_name [ UNIQUE ] [ CLUSTERED | NONCLUSTERED ]
(column_name [ ASC | DESC ] [ ,... n ] )
| INDEX index_name CLUSTERED COLUMNSTORE
| INDEX index_name [ NONCLUSTERED ] COLUMNSTORE ( column_name [ ,... n ] )
}
[ WITH ( <index_option> [ ,... n ] ) ]
[ ON { partition_scheme_name ( column_name )
| filegroup_name
| default
}
]
[ FILESTREAM_ON { filestream_filegroup_name | partition_scheme_name | "NULL" } ]
}
<index_option> ::=
{
PAD_INDEX = { ON | OFF }
| FILLFACTOR = fillfactor
| IGNORE_DUP_KEY = { ON | OFF }
| STATISTICS_NORECOMPUTE = { ON | OFF }
| STATISTICS_INCREMENTAL = { ON | OFF }
| ALLOW_ROW_LOCKS = { ON | OFF }
| ALLOW_PAGE_LOCKS = { ON | OFF }
| OPTIMIZE_FOR_SEQUENTIAL_KEY = { ON | OFF }
| COMPRESSION_DELAY = { 0 | delay [ Minutes ] }
| DATA_COMPRESSION = { NONE | ROW | PAGE | COLUMNSTORE | COLUMNSTORE_ARCHIVE }
[ ON PARTITIONS ( { partition_number_expression | <range> }
[ , ...n ] ) ]
| XML_COMPRESSION = { ON | OFF }
[ ON PARTITIONS ( { <partition_number_expression> | <range> }
[ , ...n ] ) ] ]
}
Sintaks berikut adalah untuk Azure Synapse Analytics dan Parallel Data Warehouse dan Microsoft Fabric:
DECLARE
{ { @local_variable [AS] data_type } [ = value [ COLLATE <collation_name> ] ] } [ ,...n ]
Catatan
Untuk melihat sintaks Transact-SQL untuk SQL Server 2014 (12.x) dan versi yang lebih lama, lihat Dokumentasi versi sebelumnya.
Argumen
@local_variable
Nama variabel. Nama variabel harus dimulai dengan tanda di (@). Nama variabel lokal harus mematuhi aturan untuk pengidentifikasi.
data_type
Jenis tabel yang disediakan sistem, common language runtime (CLR) yang ditentukan pengguna, atau jenis data alias. Variabel tidak boleh dari jenis data teks, ntext, atau gambar .
Untuk informasi selengkapnya tentang jenis data sistem, lihat Jenis Data (Transact-SQL). Untuk informasi selengkapnya tentang jenis atau jenis data alias yang ditentukan pengguna CLR, lihat CREATE TYPE (Transact-SQL).
= value
Menetapkan nilai ke variabel dalam baris. Nilai dapat berupa konstanta atau ekspresi, tetapi harus cocok dengan jenis deklarasi variabel atau secara implisit dapat dikonversi ke jenis tersebut. Untuk informasi selengkapnya, lihat Ekspresi (Transact-SQL).
@cursor_variable_name
Nama variabel kursor. Nama variabel kursor harus dimulai dengan tanda di (@) dan sesuai dengan aturan untuk pengidentifikasi.
KURSOR
Menentukan bahwa variabel adalah variabel kursor lokal.
@table_variable_name
Nama variabel tabel jenis. Nama variabel harus dimulai dengan tanda di (@) dan sesuai dengan aturan untuk pengidentifikasi.
<table_type_definition>
Menentukan jenis data tabel. Deklarasi tabel mencakup definisi kolom, nama, jenis data, dan batasan. Satu-satunya jenis batasan yang diizinkan adalah PRIMARY KEY, UNIQUE, NULL, dan CHECK. Jenis data alias tidak dapat digunakan sebagai jenis data skalar kolom jika aturan atau definisi default terikat ke jenis tersebut.
<table_type_definition>
Subset informasi yang digunakan untuk menentukan tabel di CREATE TABLE. Elemen dan definisi penting disertakan di sini. Untuk informasi selengkapnya, lihat CREATE TABLE (Transact-SQL).
n
Tempat penampung yang menunjukkan bahwa beberapa variabel dapat ditentukan dan ditetapkan nilainya. Saat mendeklarasikan variabel tabel , variabel tabel harus menjadi satu-satunya variabel yang dideklarasikan dalam pernyataan DECLARE.
column_name
Nama kolom dalam tabel.
scalar_data_type
Menentukan bahwa kolom adalah tipe data skalar.
computed_column_expression
Ekspresi yang menentukan nilai kolom komputasi. Ini dihitung dari ekspresi menggunakan kolom lain dalam tabel yang sama. Misalnya, kolom komputasi dapat memiliki biaya definisi harga AS * qty. Ekspresi dapat berupa nama kolom, konstanta, fungsi bawaan, variabel, atau kombinasi apa pun yang disambungkan oleh satu atau beberapa operator. Ekspresi tidak boleh berupa subkueri atau fungsi yang ditentukan pengguna. Ekspresi tidak dapat mereferensikan jenis yang ditentukan pengguna CLR.
[ KOLUSI collation_name ]
Menentukan kolas untuk kolom. collation_name dapat berupa nama kolase Windows atau nama kolase SQL, dan hanya berlaku untuk kolom tipe data karakter, varchar, teks, nchar, nvarchar, dan ntext. Jika tidak ditentukan, kolom ditetapkan baik kolase jenis data yang ditentukan pengguna (jika kolom adalah jenis data yang ditentukan pengguna) atau kolase database saat ini.
Untuk informasi selengkapnya tentang nama kolase Windows dan SQL, lihat COLLATE (Transact-SQL).
DEFAULT
Menentukan nilai yang disediakan untuk kolom saat nilai tidak disediakan secara eksplisit selama penyisipan. Definisi DEFAULT dapat diterapkan ke kolom apa pun kecuali yang didefinisikan sebagai tanda waktu atau yang memiliki properti IDENTITY. Definisi DEFAULT dihapus saat tabel dihilangkan. Hanya nilai konstanta, seperti string karakter; fungsi sistem, seperti SYSTEM_USER(); atau NULL dapat digunakan sebagai default. Untuk mempertahankan kompatibilitas dengan versi SQL Server yang lebih lama, nama batasan dapat ditetapkan ke DEFAULT.
constant_expression
Fungsi konstanta, NULL, atau sistem yang digunakan sebagai nilai default untuk kolom.
IDENTITY
Menunjukkan bahwa kolom baru adalah kolom identitas. Saat baris baru ditambahkan ke tabel, SQL Server menyediakan nilai bertahap unik untuk kolom tersebut. Kolom identitas umumnya digunakan dengan batasan KUNCI PRIMER untuk berfungsi sebagai pengidentifikasi baris unik untuk tabel. Properti IDENTITY dapat ditetapkan ke kolom tinyint, smallint, int, decimal(p,0), atau numerik(p,0). Hanya satu kolom identitas yang dapat dibuat per tabel. Batasan default dan DEFAULT terikat tidak dapat digunakan dengan kolom identitas. Anda harus menentukan benih dan kenaikan, atau tidak. Jika tidak ditentukan, defaultnya adalah (1,1).
Benih
Nilai yang digunakan untuk baris pertama yang dimuat ke dalam tabel.
Kenaikan
Nilai inkremental ditambahkan ke nilai identitas baris sebelumnya yang dimuat.
ROWGUIDCOL
Menunjukkan bahwa kolom baru adalah kolom pengidentifikasi unik global baris. Hanya satu kolom pengidentifikasi unik per tabel yang dapat ditetapkan sebagai kolom ROWGUIDCOL. Properti ROWGUIDCOL hanya dapat ditetapkan ke kolom pengidentifikasi unik.
NULL | BUKAN NULL
Menunjukkan apakah null diizinkan dalam variabel. Defaultnya adalah NULL.
KUNCI PRIMER
Batasan yang memberlakukan integritas entitas untuk kolom atau kolom tertentu melalui indeks unik. Hanya satu batasan KUNCI PRIMER yang dapat dibuat per tabel.
UNIQUE
Batasan yang menyediakan integritas entitas untuk kolom atau kolom tertentu melalui indeks unik. Tabel dapat memiliki beberapa batasan UNIK.
BERKLUSTER | NONCLUSTERED
Menunjukkan bahwa indeks berkluster atau non-kluster dibuat untuk batasan KUNCI PRIMER atau UNIK. Batasan KUNCI PRIMER menggunakan batasan CLUSTERED, dan UNIQUE menggunakan NONCLUSTERED.
CLUSTERED hanya dapat ditentukan untuk satu batasan. Jika CLUSTERED ditentukan untuk batasan UNIK dan batasan KUNCI PRIMER juga ditentukan, KUNCI PRIMER menggunakan NONCLUSTERED.
CHECK
Batasan yang memberlakukan integritas domain dengan membatasi kemungkinan nilai yang dapat dimasukkan ke dalam kolom atau kolom.
logical_expression
Ekspresi logika yang mengembalikan TRUE atau FALSE.
<index_option>
Menentukan satu atau beberapa opsi indeks. Indeks tidak dapat dibuat secara eksplisit pada variabel tabel, dan tidak ada statistik yang disimpan pada variabel tabel. Dimulai dengan SQL SQL Server 2014 (12.x), sintaks baru diperkenalkan yang memungkinkan Anda membuat jenis indeks tertentu sebaris dengan definisi tabel. Dengan menggunakan sintaks baru ini, Anda dapat membuat indeks pada variabel tabel sebagai bagian dari definisi tabel. Dalam beberapa kasus, performa dapat meningkat dengan menggunakan tabel sementara sebagai gantinya, yang memberikan dukungan dan statistik indeks penuh.
Untuk deskripsi lengkap tentang opsi ini, lihat CREATE TABLE.
Variabel tabel dan perkiraan baris
Variabel tabel tidak memiliki statistik distribusi. Dalam banyak kasus, pengoptimal akan membangun rencana kueri dengan asumsi bahwa variabel tabel memiliki baris nol atau satu baris. Untuk informasi selengkapnya, tinjau jenis data tabel - Batasan dan batasan.
Untuk alasan ini, Anda harus berhati-hati menggunakan variabel tabel jika Anda mengharapkan jumlah baris yang lebih besar (lebih dari 100). Pertimbangkan alternatif berikut:
- Tabel sementara mungkin merupakan solusi yang lebih baik daripada variabel tabel ketika memungkinkan rowcount menjadi lebih besar (lebih besar dari 100).
- Untuk kueri yang menggabungkan variabel tabel dengan tabel lain, gunakan petunjuk RECOMPILE, yang akan menyebabkan pengoptimal menggunakan kardinalitas yang benar untuk variabel tabel.
- Di Azure SQL Database dan dimulai dengan SQL Server 2019 (15.x), fitur kompilasi yang ditangguhkan variabel tabel akan menyebarkan perkiraan kardinalitas yang didasarkan pada jumlah baris variabel tabel aktual, memberikan jumlah baris yang lebih akurat untuk mengoptimalkan rencana eksekusi. Untuk informasi selengkapnya, lihat Pemrosesan kueri cerdas di database SQL.
Keterangan
Variabel sering digunakan dalam batch atau prosedur sebagai penghitung untuk WHILE, LOOP, atau untuk IF... Blok LAIN.
Variabel hanya dapat digunakan dalam ekspresi, bukan sebagai pengganti nama objek atau kata kunci. Untuk membuat pernyataan SQL dinamis, gunakan EXECUTE.
Cakupan variabel lokal adalah batch tempat variabel dideklarasikan.
Variabel tabel belum tentu merupakan residen memori. Di bawah tekanan memori, halaman milik variabel tabel dapat didorong ke tempdb.
Anda dapat menentukan indeks sebaris dalam variabel tabel.
Variabel kursor yang saat ini memiliki kursor yang ditetapkan untuknya dapat direferensikan sebagai sumber dalam:
- Pernyataan CLOSE
- Pernyataan DEALLOCATE
- Pernyataan FETCH
- Pernyataan OPEN
- Pernyataan DELETE atau UPDATE yang Diposisikan
- MENGATUR pernyataan variabel KURSOR (di sisi kanan)
Dalam semua pernyataan ini, SQL Server menimbulkan kesalahan jika variabel kursor yang dirujuk ada tetapi tidak memiliki kursor yang saat ini dialokasikan untuknya. Jika variabel kursor yang direferensikan tidak ada, SQL Server memunculkan kesalahan yang sama yang dimunculkan untuk variabel yang tidak dinyatakan dari jenis lain.
Variabel kursor:
Dapat menjadi target jenis kursor atau variabel kursor lainnya. Untuk informasi selengkapnya, lihat SET @local_variable (Transact-SQL) .
Dapat direferensikan sebagai target parameter kursor output dalam pernyataan EXECUTE jika variabel kursor tidak memiliki kursor yang saat ini ditetapkan untuknya.
Harus dianggap sebagai penunjuk ke kursor.
Contoh
J. Menggunakan DECLARE
Contoh berikut menggunakan variabel lokal bernama @find untuk mengambil informasi kontak untuk semua nama belakang yang dimulai dengan Man.
USE AdventureWorks2022;
GO
DECLARE @find VARCHAR(30);
/* Also allowed:
DECLARE @find VARCHAR(30) = 'Man%';
*/
SET @find = 'Man%';
SELECT p.LastName, p.FirstName, ph.PhoneNumber
FROM Person.Person AS p
JOIN Person.PersonPhone AS ph ON p.BusinessEntityID = ph.BusinessEntityID
WHERE LastName LIKE @find;
Berikut adalah hasil yang ditetapkan.
LastName FirstName Phone
------------------- ----------------------- -------------------------
Manchepalli Ajay 1 (11) 500 555-0174
Manek Parul 1 (11) 500 555-0146
Manzanares Tomas 1 (11) 500 555-0178
(3 row(s) affected)
B. Menggunakan DECLARE dengan dua variabel
Contoh berikut mengambil nama perwakilan penjualan Adventure Works Cycles yang terletak di wilayah penjualan Amerika Utara n dan memiliki setidaknya $ 2.000.000 penjualan untuk tahun itu.
USE AdventureWorks2022;
GO
SET NOCOUNT ON;
GO
DECLARE @Group nvarchar(50), @Sales MONEY;
SET @Group = N'North America';
SET @Sales = 2000000;
SET NOCOUNT OFF;
SELECT FirstName, LastName, SalesYTD
FROM Sales.vSalesPerson
WHERE TerritoryGroup = @Group and SalesYTD >= @Sales;
C. Mendeklarasikan variabel tabel tipe
Contoh berikut membuat table variabel yang menyimpan nilai yang ditentukan dalam klausul OUTPUT pernyataan UPDATE. Dua SELECT pernyataan mengikuti yang mengembalikan nilai di @MyTableVar dan hasil operasi pembaruan dalam Employee tabel. Hasil dalam INSERTED.ModifiedDate kolom berbeda dari nilai dalam ModifiedDate kolom dalam Employee tabel. Ini karena pemicu AFTER UPDATE , yang memperbarui nilai ModifiedDate ke tanggal saat ini, ditentukan pada Employee tabel. Namun, kolom yang dikembalikan dari OUTPUT mencerminkan data sebelum pemicu diaktifkan. Untuk informasi selengkapnya, lihat Klausul OUTPUT (Transact-SQL).
USE AdventureWorks2022;
GO
DECLARE @MyTableVar TABLE (
EmpID INT NOT NULL,
OldVacationHours INT,
NewVacationHours INT,
ModifiedDate DATETIME);
UPDATE TOP (10) HumanResources.Employee
SET VacationHours = VacationHours * 1.25
OUTPUT INSERTED.BusinessEntityID,
DELETED.VacationHours,
INSERTED.VacationHours,
INSERTED.ModifiedDate
INTO @MyTableVar;
--Display the result set of the table variable.
SELECT EmpID, OldVacationHours, NewVacationHours, ModifiedDate
FROM @MyTableVar;
GO
--Display the result set of the table.
--Note that ModifiedDate reflects the value generated by an
--AFTER UPDATE trigger.
SELECT TOP (10) BusinessEntityID, VacationHours, ModifiedDate
FROM HumanResources.Employee;
GO
D. Mendeklarasikan variabel tabel tipe, dengan indeks sebaris
Contoh berikut membuat table variabel dengan indeks sebaris berkluster dan dua indeks sebaris non-kluster.
DECLARE @MyTableVar TABLE (
EmpID INT NOT NULL,
PRIMARY KEY CLUSTERED (EmpID),
UNIQUE NONCLUSTERED (EmpID),
INDEX CustomNonClusteredIndex NONCLUSTERED (EmpID)
);
GO
Kueri berikut mengembalikan informasi tentang indeks yang dibuat di kueri sebelumnya.
SELECT *
FROM tempdb.sys.indexes
WHERE object_id < 0;
GO
E. Mendeklarasikan variabel jenis tabel yang ditentukan pengguna
Contoh berikut membuat parameter bernilai tabel atau variabel tabel yang disebut @LocationTVP. Ini memerlukan jenis tabel yang ditentukan pengguna yang sesuai yang disebut LocationTableType. Untuk informasi selengkapnya tentang cara membuat jenis tabel yang ditentukan pengguna, lihat CREATE TYPE (Transact-SQL). Untuk informasi selengkapnya tentang parameter bernilai tabel, lihat Menggunakan Parameter Bernilai Tabel (Mesin Database).
DECLARE @LocationTVP
AS LocationTableType;
Contoh: Azure Synapse Analytics and Analytics Platform System (PDW)
F. Menggunakan DECLARE
Contoh berikut menggunakan variabel lokal bernama @find untuk mengambil informasi kontak untuk semua nama belakang yang dimulai dengan Walt.
-- Uses AdventureWorks
DECLARE @find VARCHAR(30);
/* Also allowed:
DECLARE @find VARCHAR(30) = 'Man%';
*/
SET @find = 'Walt%';
SELECT LastName, FirstName, Phone
FROM DimEmployee
WHERE LastName LIKE @find;
G. Menggunakan DECLARE dengan dua variabel
Contoh berikut mengambil menggunakan variabel untuk menentukan nama depan dan belakang karyawan dalam DimEmployee tabel.
-- Uses AdventureWorks
DECLARE @lastName VARCHAR(30), @firstName VARCHAR(30);
SET @lastName = 'Walt%';
SET @firstName = 'Bryan';
SELECT LastName, FirstName, Phone
FROM DimEmployee
WHERE LastName LIKE @lastName AND FirstName LIKE @firstName;
Baca juga
Saran dan Komentar
Segera hadir: Sepanjang tahun 2024 kami akan menghentikan penggunaan GitHub Issues sebagai mekanisme umpan balik untuk konten dan menggantinya dengan sistem umpan balik baru. Untuk mengetahui informasi selengkapnya, lihat: https://aka.ms/ContentUserFeedback.
Kirim dan lihat umpan balik untuk