Bagikan melalui

DEKLARASIKAN @local_variable (T-SQL)

  • Artikel

Berlaku untuk: Titik akhir analitik SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Platform System (PDW) SQL di Microsoft Fabric Warehouse 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.

Konvensi sintaks transact-SQL

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 ]

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).

biji
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 set hasilnya.

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;

Lihat juga