SET @local_variable (Transact-SQL)

Artikel
12/22/2023

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

Mengatur variabel lokal yang ditentukan, yang sebelumnya dibuat dengan menggunakan DECLARE @local_variable pernyataan , ke nilai yang ditentukan.

Konvensi sintaks transact-SQL

Sintaks

Sintaks untuk SQL Server, Azure SQL Database, dan Azure SQL Managed Instance:

SET
{ @local_variable
    [ . { property_name | field_name } ] = { expression | udt_name { . | :: } method_name }
}
| { @SQLCLR_local_variable.mutator_method }
| { @local_variable
    { += | -= | *= | /= | %= | &= | ^= | |= } expression
}
| { @cursor_variable =
    { @cursor_variable | cursor_name
    | { CURSOR [ [ LOCAL | GLOBAL ] ]
        [ FORWARD_ONLY | SCROLL ]
        [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
        [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
        [ TYPE_WARNING ]
    FOR select_statement
        [ FOR { READ ONLY | UPDATE [ OF column_name [ , ...n ] ] } ]
      }
    }
}

Sintaks untuk Azure Synapse Analytics dan Gudang Data Paralel dan Microsoft Fabric:

SET @local_variable { = | += | -= | *= | /= | %= | &= | ^= | |= } expression

Argumen

@local_variable

Nama variabel dari jenis apa pun kecuali kursor, teks, ntext, gambar, atau tabel. Nama variabel harus dimulai dengan satu di tanda tangan (@). Nama variabel harus mengikuti aturan untuk pengidentifikasi.

property_name

Properti dari jenis yang ditentukan pengguna.

field_name

Bidang publik dari jenis yang ditentukan pengguna.

udt_name

Nama jenis yang ditentukan pengguna runtime bahasa umum (CLR).

{ . | :: }

Menentukan metode jenis yang ditentukan pengguna CLR. Untuk metode instans (non-statis), gunakan titik (.). Untuk metode statis, gunakan dua titik dua (::). Untuk memanggil metode, properti, atau bidang dari jenis yang ditentukan pengguna CLR, Anda harus memiliki izin EXECUTE pada jenis tersebut.

method_name ( argumen [ ,... n ] )

Metode jenis yang ditentukan pengguna yang mengambil satu atau beberapa argumen untuk mengubah status instans jenis. Metode statis harus publik.

@SQLCLR_local_variable

Variabel yang jenisnya terletak di rakitan. Untuk informasi selengkapnya, lihat Konsep pemrograman integrasi common language runtime (CLR).

mutator_method

Metode dalam rakitan yang dapat mengubah status objek. SQLMethodAttribute.IsMutator diterapkan ke metode ini.

{ += | -= | *= | /= | %= | &= | ^= | |= }

Operator penetapan gabungan:

+= - Tambahkan dan tetapkan
-= - Mengurangi dan menetapkan
*= - Mengalikan dan menetapkan
/= - Bagi dan tetapkan
%= - Modulo dan tetapkan
&= - Bitwise AND dan tetapkan
^= - Bitwise XOR dan tetapkan
|= - Bitwise OR dan tetapkan

expression

Ekspresi apa pun yang valid.

cursor_variable

Nama variabel kursor. Jika variabel kursor target sebelumnya mereferensikan kursor yang berbeda, referensi sebelumnya akan dihapus.

cursor_name

Nama kursor yang dideklarasikan dengan menggunakan DECLARE CURSOR pernyataan .

KURSOR

Menentukan bahwa pernyataan berisi SET deklarasi kursor.

SCROLL

Menentukan bahwa kursor mendukung semua opsi pengambilan: FIRST, , LAST, NEXTPRIOR, RELATIVE, dan ABSOLUTE. Anda tidak dapat menentukan SCROLL kapan Anda juga menentukan FAST_FORWARD.

FORWARD_ONLY

Menentukan bahwa kursor hanya FETCH NEXT mendukung opsi . Kursor hanya diambil dalam satu arah, dari baris pertama hingga terakhir. Ketika Anda menentukan FORWARD_ONLY tanpa STATICkata kunci , , KEYSETatau DYNAMIC , kursor diimplementasikan sebagai DYNAMIC. Jika Anda tidak menentukan atau FORWARD_ONLY SCROLL, FORWARD_ONLY adalah default, kecuali Anda menentukan kata kunci STATIC, , KEYSETatau DYNAMIC. Untuk STATIC, KEYSET, dan DYNAMIC kursor, SCROLL adalah defaultnya.

STATIS

Menentukan kursor yang membuat salinan sementara data yang akan digunakan oleh kursor. Semua permintaan ke kursor dijawab dari tabel sementara ini di tempdb. Akibatnya, modifikasi yang dilakukan pada tabel dasar setelah kursor dibuka tidak tercermin dalam data yang dikembalikan oleh pengambilan yang dibuat ke kursor. Dan, kursor ini tidak mendukung modifikasi.

SET KUNCI

Menentukan bahwa keanggotaan dan urutan baris dalam kursor diperbaiki saat kursor dibuka. Sekumpulan kunci yang secara unik mengidentifikasi baris dibangun ke dalam keysettable di tempdb. Perubahan pada nilai non-kunci dalam tabel dasar, baik yang dibuat oleh pemilik kursor atau diterapkan oleh pengguna lain, terlihat saat pemilik kursor menggulir di sekitar kursor. Penyisipan yang dibuat oleh pengguna lain tidak terlihat, dan penyisipan tidak dapat dibuat melalui kursor server Transact-SQL.

Jika baris dihapus, upaya untuk mengambil baris mengembalikan @@FETCH_STATUS dari -2. Pembaruan nilai kunci dari luar kursor mirip dengan penghapusan baris lama diikuti dengan sisipan baris baru. Baris dengan nilai baru tidak terlihat, dan mencoba mengambil baris dengan nilai lama mengembalikan @@FETCH_STATUS dari -2. Nilai baru terlihat jika pembaruan terjadi melalui kursor dengan menentukan WHERE CURRENT OF klausa.

DINAMIS

Menentukan kursor yang mencerminkan semua perubahan data yang dibuat pada baris dalam tataan hasilnya sebagai pemilik kursor menggulir di sekitar kursor. Nilai data, pesanan, dan keanggotaan baris dapat berubah pada setiap pengambilan. Opsi pengambilan absolut dan relatif tidak didukung dengan kursor dinamis.

FAST_FORWARD

FORWARD_ONLYMenentukan kursor , READ_ONLY dengan pengoptimalan diaktifkan. FAST_FORWARD tidak dapat ditentukan ketika SCROLL juga ditentukan.

READ_ONLY

KUNCI GULIR

Menentukan bahwa pembaruan atau penghapusan yang diposisikan yang dibuat melalui kursor dijamin berhasil. SQL Server mengunci baris saat dibaca ke dalam kursor untuk menjamin ketersediaannya untuk modifikasi nanti. Anda tidak dapat menentukan SCROLL_LOCKS kapan FAST_FORWARD juga ditentukan.

OPTIMIS

Menentukan bahwa pembaruan atau penghapusan yang diposisikan yang dibuat melalui kursor tidak berhasil jika baris diperbarui sejak dibaca ke dalam kursor. SQL Server tidak mengunci baris saat dibaca ke dalam kursor. Sebaliknya, ia menggunakan perbandingan nilai kolom tanda waktu, atau nilai checksum, jika tabel tidak memiliki kolom tanda waktu, untuk menentukan apakah baris dimodifikasi setelah dibaca ke dalam kursor. Jika baris dimodifikasi, upaya pembaruan atau penghapusan yang diposisikan gagal. Anda tidak dapat menentukan OPTIMISTIC kapan FAST_FORWARD juga ditentukan.

TYPE_WARNING

Menentukan bahwa pesan peringatan dikirim ke klien ketika kursor dikonversi secara implisit dari jenis yang diminta ke jenis lain.

UNTUK select_statement

Pernyataan standar SELECT yang menentukan kumpulan hasil kursor. Kata kunci FOR BROWSE, dan INTO tidak diizinkan dalam select_statement deklarasi kursor.

Jika Anda menggunakan DISTINCT, , GROUP BYUNION, atau HAVING, atau Anda menyertakan ekspresi agregat dalam select_list, kursor dibuat sebagai STATIC.

Jika setiap tabel yang mendasar tidak memiliki indeks unik dan kursor ISO SCROLL atau jika kursor Transact-SQL KEYSET diminta, kursor secara otomatis adalah STATIC kursor.

Jika select_statement berisi ORDER BY klausa di mana kolom bukan pengidentifikasi baris unik, DYNAMIC kursor dikonversi ke KEYSET kursor, atau ke STATIC kursor jika KEYSET kursor tidak dapat dibuka. Proses ini juga terjadi untuk kursor yang ditentukan dengan menggunakan sintaks ISO tetapi tanpa STATIC kata kunci.

BACA SAJA

Mencegah pembaruan dibuat melalui kursor ini. Kursor tidak dapat dirujuk dalam WHERE CURRENT OF klausul dalam pernyataan UPDATE atau DELETE . Opsi ini mengambil alih kemampuan default kursor yang akan diperbarui. Kata kunci ini bervariasi dari yang sebelumnya READ_ONLY dengan memiliki spasi alih-alih garis bawah antara READ dan ONLY.

UPDATE [ COLUMN_NAME [ ,... n ] ]

Menentukan kolom yang dapat diperbarui dalam kursor. Jika OF <column_name> [ , ...n ] disediakan, hanya kolom yang tercantum yang mengizinkan modifikasi. Ketika tidak ada daftar yang disediakan, semua kolom dapat diperbarui, kecuali kursor didefinisikan sebagai READ_ONLY.

Keterangan

Setelah variabel dinyatakan, variabel diinisialisasi ke NULL. SET Gunakan pernyataan untuk menetapkan nilai yang bukan NULL ke variabel yang dideklarasikan. Pernyataan SET yang menetapkan nilai ke variabel mengembalikan satu nilai. Saat Anda menginisialisasi beberapa variabel, gunakan pernyataan terpisah SET untuk setiap variabel lokal.

Anda hanya dapat menggunakan variabel dalam ekspresi, bukan nama objek atau kata kunci. Untuk membuat pernyataan Transact-SQL dinamis, gunakan EXECUTE.

Meskipun aturan sintaks untuk SET @cursor_variable menyertakan LOCAL kata kunci dan GLOBAL , saat Anda menggunakan SET @cursor_variable = CURSOR... sintaks, kursor dibuat sebagai GLOBAL atau LOCAL, tergantung pada pengaturan default ke opsi database kursor lokal.

Variabel kursor selalu lokal, bahkan jika mereka mereferensikan kursor global. Saat variabel kursor mereferensikan kursor global, kursor memiliki referensi kursor global dan lokal. Untuk informasi selengkapnya, lihat Contoh D, Gunakan SET dengan kursor global.

Untuk informasi selengkapnya, lihat MENDEKLARASIKAN KURSOR (Transact-SQL).

Anda dapat menggunakan operator penugasan gabungan di mana saja Anda memiliki penugasan dengan ekspresi di sisi kanan operator, termasuk variabel, dan SET dalam pernyataan , SELECT, dan RECEIVE UPDATE.

Jangan gunakan variabel dalam SELECT pernyataan untuk menggabungkan nilai (yaitu, untuk menghitung nilai agregat). Hasil kueri yang tidak terduga mungkin terjadi karena semua ekspresi dalam SELECT daftar (termasuk penugasan) belum tentu berjalan persis sekali untuk setiap baris output. Untuk informasi selengkapnya, lihat 287515 KB.

Izin

Memerlukan keanggotaan dalam peran publik. Semua pengguna dapat menggunakan SET @local_variable.

Contoh

Sampel kode Transact-SQL dalam artikel ini menggunakan AdventureWorks2022 database sampel atau AdventureWorksDW2022 , yang dapat Anda unduh dari halaman beranda Sampel Microsoft SQL Server dan Proyek Komunitas.

J. Cetak nilai variabel yang diinisialisasi dengan menggunakan SET

Contoh berikut membuat @myVar variabel, menempatkan nilai string ke dalam variabel, dan mencetak nilai @myVar variabel.

DECLARE @myVar CHAR(20);
SET @myVar = 'This is a test';
SELECT @myVar;
GO

B. Menggunakan variabel lokal yang diberi nilai dengan menggunakan SET dalam pernyataan SELECT

Contoh berikut membuat variabel lokal bernama @state dan menggunakan variabel lokal dalam SELECT pernyataan untuk menemukan nama depan (FirstName) dan nama keluarga (LastName) dari semua karyawan yang tinggal dalam status Oregon.

USE AdventureWorks2022;
GO
DECLARE @state CHAR(25);
SET @state = N'Oregon';
SELECT RTRIM(FirstName) + ' ' + RTRIM(LastName) AS Name, City
FROM HumanResources.vEmployee
WHERE StateProvinceName = @state;
GO

C. Menggunakan penetapan gabungan untuk variabel lokal

Dua contoh berikut menghasilkan hasil yang sama. Setiap contoh membuat variabel lokal bernama @NewBalance, mengalikannya dengan 10, lalu menampilkan nilai baru variabel lokal dalam pernyataan SELECT . Contoh kedua menggunakan operator penetapan gabungan.

/* Example one */
DECLARE @NewBalance INT;
SET @NewBalance = 10;
SET @NewBalance = @NewBalance * 10;
SELECT @NewBalance;
GO

/* Example Two */
DECLARE @NewBalance INT = 10;
SET @NewBalance *= 10;
SELECT @NewBalance;
GO

D. Menggunakan SET dengan kursor global

Contoh berikut membuat variabel lokal lalu mengatur variabel kursor ke nama kursor global.

DECLARE my_cursor CURSOR GLOBAL
FOR SELECT * FROM Purchasing.ShipMethod
DECLARE @my_variable CURSOR ;
SET @my_variable = my_cursor ;
--There is a GLOBAL cursor declared(my_cursor) and a LOCAL variable
--(@my_variable) set to the my_cursor cursor.

DEALLOCATE my_cursor;
GO
--There is now only a LOCAL variable reference
--(@my_variable) to the my_cursor cursor.

E. Menentukan kursor dengan menggunakan SET

Contoh berikut menggunakan SET pernyataan untuk menentukan kursor.

DECLARE @CursorVar CURSOR;

SET @CursorVar = CURSOR SCROLL DYNAMIC
FOR
SELECT LastName, FirstName
FROM AdventureWorks2022.HumanResources.vEmployee
WHERE LastName like 'B%';

OPEN @CursorVar;

FETCH NEXT FROM @CursorVar;
WHILE @@FETCH_STATUS = 0
BEGIN
    FETCH NEXT FROM @CursorVar
END;

CLOSE @CursorVar;
DEALLOCATE @CursorVar;
GO

F. Menetapkan nilai dari kueri

Contoh berikut menggunakan kueri untuk menetapkan nilai ke variabel.

USE AdventureWorks2022;
GO
DECLARE @rows INT;
SET @rows = (SELECT COUNT(*) FROM Sales.Customer);
SELECT @rows;
GO

G. Menetapkan nilai ke variabel jenis yang ditentukan pengguna dengan memodifikasi properti jenis

Contoh berikut menetapkan nilai untuk jenis yang ditentukan pengguna (UDT) Point dengan memodifikasi nilai properti X jenis .

DECLARE @p Point;
SET @p.X = @p.X + 1.1;
SELECT @p;
GO

Pelajari selengkapnya tentang membuat UDT yang Point dirujuk dalam contoh ini dan contoh berikut dalam artikel Membuat Jenis yang Ditentukan Pengguna.

H. Menetapkan nilai ke variabel jenis yang ditentukan pengguna dengan memanggil metode jenis

Contoh berikut menetapkan nilai untuk titik jenis yang ditentukan pengguna dengan memanggil metode SetXY jenis.

DECLARE @p Point;
SET @p=point.SetXY(23.5, 23.5);

I. Membuat variabel untuk jenis CLR dan memanggil metode mutator

Contoh berikut membuat variabel untuk jenis Point, lalu menjalankan metode mutator di Point.

CREATE ASSEMBLY mytest FROM 'c:\test.dll' WITH PERMISSION_SET = SAFE
CREATE TYPE Point EXTERNAL NAME mytest.Point
GO
DECLARE @p Point = CONVERT(Point, '')
SET @p.SetXY(22, 23);

Contoh: Azure Synapse Analytics and Analytics Platform System (PDW)

j. Cetak nilai variabel yang diinisialisasi dengan menggunakan SET