Bagikan melalui

DEKLARASIKAN KURSOR (Transact-SQL)

  • Artikel

Berlaku untuk: SQL ServerAzure SQL Database Azure SQL Managed Instance

Menentukan atribut kursor server Transact-SQL, seperti perilaku gulirnya dan kueri yang digunakan untuk membangun tataan hasil tempat kursor beroperasi. DECLARE CURSOR menerima sintaks berdasarkan standar ISO dan sintaks menggunakan sekumpulan ekstensi Transact-SQL.

Konvensi sintaks transact-SQL

Sintaks

Sintaks ISO:

DECLARE cursor_name [ INSENSITIVE ] [ SCROLL ] CURSOR
    FOR select_statement
    [ FOR { READ_ONLY | UPDATE [ OF column_name [ , ...n ] ] } ]
[ ; ]

Sintaks yang diperluas Transact-SQL:

DECLARE cursor_name CURSOR [ LOCAL | GLOBAL ]
    [ FORWARD_ONLY | SCROLL ]
    [ STATIC | KEYSET | DYNAMIC | FAST_FORWARD ]
    [ READ_ONLY | SCROLL_LOCKS | OPTIMISTIC ]
    [ TYPE_WARNING ]
    FOR select_statement
    [ FOR UPDATE [ OF column_name [ , ...n ] ] ]
[ ; ]

Argumen

cursor_name

Nama kursor server Transact-SQL yang ditentukan. cursor_name harus sesuai dengan aturan untuk pengidentifikasi.

SENSITIF

Menentukan kursor yang membuat salinan sementara data yang akan digunakan oleh kursor. Semua permintaan ke kursor dijawab dari tabel sementara ini di tempdb. Oleh karena itu, modifikasi tabel dasar tidak tercermin dalam data yang dikembalikan oleh pengambilan yang dibuat untuk kursor ini, dan kursor ini tidak mengizinkan modifikasi. Ketika sintaks ISO digunakan, jika INSENSITIVE dihilangkan, penghapusan yang diterapkan dan pembaruan yang dibuat ke tabel yang mendasar (oleh pengguna mana pun) tercermin dalam pengambilan berikutnya.

SCROLL

Menentukan bahwa semua opsi pengambilan (FIRST, , LAST, PRIORNEXT, RELATIVE, ABSOLUTE) tersedia. Jika SCROLL tidak ditentukan dalam ISO DECLARE CURSOR, NEXT adalah satu-satunya opsi pengambilan yang didukung. SCROLL tidak dapat ditentukan jika FAST_FORWARD juga ditentukan. Jika SCROLL tidak ditentukan, maka hanya opsi NEXT ambil yang tersedia, dan kursor menjadi FORWARD_ONLY.

select_statement

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

SQL Server secara implisit mengonversi kursor ke jenis lain jika klausul dalam select_statement bertentangan dengan fungsionalitas jenis kursor yang diminta.

READ_ONLY

Mencegah pembaruan yang dilakukan 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.

UPDATE [ COLUMN_NAME [ ,...n ] ]

Menentukan kolom yang dapat diperbarui dalam kursor. Jika OF <column_name> [, <... n> ] ditentukan, hanya kolom yang tercantum yang mengizinkan modifikasi. Jika UPDATE ditentukan tanpa daftar kolom, semua kolom dapat diperbarui.

cursor_name

Nama kursor server Transact-SQL yang ditentukan. cursor_name harus sesuai dengan aturan untuk pengidentifikasi.

LOKAL

Menentukan bahwa cakupan kursor bersifat lokal untuk batch, prosedur tersimpan, atau pemicu tempat kursor dibuat. Nama kursor hanya valid dalam cakupan ini. Kursor dapat dirujuk oleh variabel kursor lokal dalam batch, prosedur tersimpan, atau pemicu, atau parameter prosedur OUTPUT tersimpan. Parameter OUTPUT digunakan untuk meneruskan kursor lokal kembali ke batch panggilan, prosedur tersimpan, atau pemicu, yang dapat menetapkan parameter ke variabel kursor untuk mereferensikan kursor setelah prosedur tersimpan berakhir. Kursor secara implisit dibatalkan alokasinya ketika batch, prosedur tersimpan, atau pemicu berakhir, kecuali kursor diteruskan kembali dalam OUTPUT parameter. Jika diteruskan kembali dalam OUTPUT parameter, kursor dibatalkan alokasinya ketika variabel terakhir yang merujuknya dibatalkan alokasinya atau keluar dari cakupan.

GLOBAL

Menentukan bahwa cakupan kursor bersifat global ke koneksi. Nama kursor dapat dirujuk dalam prosedur tersimpan atau batch apa pun yang dijalankan oleh koneksi. Kursor hanya dibatalkan alokasinya secara implisit saat terputus.

Catatan

Jika tidak ada GLOBAL atau LOCAL ditentukan, default dikontrol oleh pengaturan default ke opsi database kursor lokal.

FORWARD_ONLY

Menentukan bahwa kursor hanya dapat bergerak maju dan digulir dari baris pertama ke terakhir. FETCH NEXT adalah satu-satunya opsi pengambilan yang didukung. Semua pernyataan sisipkan, perbarui, dan hapus yang dibuat oleh pengguna saat ini (atau diterapkan oleh pengguna lain) yang memengaruhi baris dalam tataan hasil, terlihat saat baris diambil. Karena kursor tidak dapat digulir mundur, namun, perubahan yang dilakukan pada baris dalam database setelah baris diambil tidak terlihat melalui kursor. Kursor khusus penerusan bersifat dinamis secara default, yang berarti bahwa semua perubahan terdeteksi saat baris saat ini diproses. Ini menyediakan pembukaan kursor yang lebih cepat dan memungkinkan tataan hasil untuk menampilkan pembaruan yang dibuat pada tabel yang mendasar. Meskipun kursor hanya maju tidak mendukung pengguliran mundur, aplikasi dapat kembali ke awal hasil yang ditetapkan dengan menutup dan membuka kembali kursor.

Jika FORWARD_ONLY ditentukan tanpa STATICkata kunci , , KEYSETatau DYNAMIC , kursor beroperasi sebagai kursor dinamis. Ketika FORWARD_ONLY atau SCROLL tidak ditentukan, FORWARD_ONLY adalah default, kecuali kata kunci STATIC, , KEYSETatau DYNAMIC ditentukan. STATIC, KEYSET, dan DYNAMIC kursor default ke SCROLL. Tidak seperti API database seperti ODBC dan ADO, FORWARD_ONLY didukung dengan STATICkursor , , KEYSETdan DYNAMIC Transact-SQL.

STATIS

Menentukan bahwa kursor selalu menampilkan tataan hasil seperti saat kursor pertama kali dibuka, dan membuat salinan sementara data yang akan digunakan oleh kursor. Semua permintaan ke kursor dijawab dari tabel sementara ini di tempdb. Oleh karena itu, sisipan, pembaruan, dan penghapusan yang dibuat untuk tabel dasar tidak tercermin dalam data yang dikembalikan oleh pengambilan yang dibuat untuk kursor ini, dan kursor ini tidak mendeteksi perubahan yang dibuat pada keanggotaan, urutan, atau nilai hasil yang ditetapkan setelah kursor dibuka. Kursor statis mungkin mendeteksi pembaruan, penghapusan, dan penyisipan mereka sendiri, meskipun tidak diperlukan untuk melakukannya.

Misalnya, kursor statis mengambil baris, dan aplikasi lain kemudian memperbarui baris tersebut. Jika aplikasi mengambil kembali baris dari kursor statis, nilai yang dilihatnya tidak berubah, meskipun perubahan yang dilakukan oleh aplikasi lain. Semua jenis pengguliran didukung.

SET KUNCI

Menentukan bahwa keanggotaan dan urutan baris dalam kursor diperbaiki saat kursor dibuka. Sekumpulan kunci yang secara unik mengidentifikasi baris dibangun ke dalam tabel yang tempdb dikenal sebagai keyset. Kursor ini menyediakan fungsionalitas antara kursor statis dan dinamis dalam kemampuannya untuk mendeteksi perubahan. Seperti kursor statis, kursor tidak selalu mendeteksi perubahan pada keanggotaan dan urutan kumpulan hasil. Seperti kursor dinamis, kursor ini mendeteksi perubahan pada nilai baris dalam tataan hasil.

Kursor berbasis set kunci dikontrol oleh sekumpulan pengidentifikasi unik (kunci) yang dikenal sebagai keyset. Kunci dibangun dari sekumpulan kolom yang secara unik mengidentifikasi baris dalam tataan hasil. Set kunci adalah kumpulan nilai kunci dari semua baris yang dikembalikan oleh pernyataan kueri. Dengan kursor berbasis keyset, kunci dibuat dan disimpan untuk setiap baris di kursor dan disimpan baik di stasiun kerja klien atau di server. Saat Anda mengakses setiap baris, kunci tersimpan digunakan untuk mengambil nilai data saat ini dari sumber data. Dalam kursor berbasis keyset, keanggotaan tataan hasil dibekukan saat set kunci diisi sepenuhnya. Setelah itu, penambahan atau pembaruan yang memengaruhi keanggotaan bukan bagian dari hasil yang ditetapkan sampai dibuka kembali.

Perubahan pada nilai data (dibuat oleh pemilik set kunci atau proses lain) terlihat saat pengguna menggulir melalui kumpulan hasil:

  • Jika baris dihapus, upaya untuk mengambil baris mengembalikan dari -2 karena baris yang @@FETCH_STATUS dihapus muncul sebagai celah dalam tataan hasil. Kunci untuk baris ada di set kunci, tetapi baris tidak ada lagi dalam tataan hasil.

  • Sisipan yang dibuat di luar kursor (oleh proses lain) hanya terlihat jika kursor ditutup dan dibuka kembali. Sisipan yang dibuat dari dalam kursor terlihat di akhir tataan hasil.

  • Pembaruan nilai kunci dari luar kursor menyerupai 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 dilakukan melalui kursor dengan menentukan WHERE CURRENT OF klausa.

Catatan

Jika kueri mereferensikan setidaknya satu tabel tanpa indeks unik, kursor set kunci dikonversi ke kursor statis.

DINAMIS

Menentukan kursor yang mencerminkan semua perubahan data yang dibuat pada baris dalam tataan hasilnya saat Anda menggulir di sekitar kursor dan mengambil rekaman baru, terlepas dari apakah perubahan terjadi dari dalam kursor atau oleh pengguna lain di luar kursor. Oleh karena itu semua pernyataan sisipkan, perbarui, dan hapus yang dibuat oleh semua pengguna terlihat melalui kursor. Nilai data, pesanan, dan keanggotaan baris dapat berubah pada setiap pengambilan. Opsi ABSOLUTE ambil tidak didukung dengan kursor dinamis. Pembaruan yang dibuat di luar kursor tidak terlihat sampai dilakukan (kecuali tingkat isolasi transaksi kursor diatur ke UNCOMMITTED).

Misalnya, kursor dinamis mengambil dua baris, dan aplikasi lain kemudian memperbarui salah satu baris tersebut dan menghapus baris lainnya. Jika kursor dinamis kemudian mengambil baris tersebut, kursor tidak menemukan baris yang dihapus, tetapi menampilkan nilai baru untuk baris yang diperbarui.

FAST_FORWARD

FORWARD_ONLYMenentukan kursor , READ_ONLY dengan pengoptimalan performa diaktifkan. FAST_FORWARD tidak dapat ditentukan jika SCROLL atau FOR_UPDATE juga ditentukan. Jenis kursor ini tidak mengizinkan modifikasi data dari dalam kursor.

Catatan

Keduanya FAST_FORWARD dan FORWARD_ONLY dapat digunakan dalam pernyataan yang sama DECLARE CURSOR .

READ_ONLY

Mencegah pembaruan yang dilakukan 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.

SCROLL_LOCKS

Menentukan bahwa pembaruan atau penghapusan yang diposisikan yang dibuat melalui kursor dijamin berhasil. SQL Server mengunci baris saat dibaca ke dalam kursor untuk memastikan ketersediaannya untuk modifikasi nanti. SCROLL_LOCKS tidak dapat ditentukan jika FAST_FORWARD atau STATIC 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. Sebagai gantinya 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. OPTIMISTIC tidak dapat ditentukan jika FAST_FORWARD juga ditentukan.

Jika STATIC ditentukan bersama dengan OPTIMISTIC argumen kursor, kombinasi keduanya secara implisit dikonversi ke yang setara dengan kombinasi penggunaan STATIC dan READ_ONLY argumen, atau STATIC argumen dan FORWARD_ONLY .

TYPE_WARNING

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

Tidak ada peringatan yang dikirim ke klien ketika kombinasi OPTIMISTIC argumen kursor dan STATIC digunakan, dan kursor secara implisit dikonversi ke setara dengan STATIC READ_ONLY kursor atau STATIC FORWARD_ONLY . Konversi untuk READ_ONLY berubah menjadi FAST_FORWARD kursor dan READ_ONLY dari perspektif klien.

select_statement

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

Catatan

Anda bisa menggunakan petunjuk kueri dalam deklarasi kursor. Namun, jika Anda juga menggunakan FOR UPDATE OF klausul , tentukan OPTION (<query_hint>) setelah FOR UPDATE OF.

SQL Server secara implisit mengonversi kursor ke jenis lain jika klausul dalam select_statement bertentangan dengan fungsionalitas jenis kursor yang diminta.

UNTUK PEMBARUAN [ column_name [ ,...n ] ]

Menentukan kolom yang dapat diperbarui dalam kursor. Jika OF <column_name> [, <... n>] disediakan, hanya kolom yang tercantum yang mengizinkan modifikasi. Jika UPDATE ditentukan tanpa daftar kolom, semua kolom dapat diperbarui, kecuali READ_ONLY opsi konkurensi ditentukan.

Keterangan

DECLARE CURSOR menentukan atribut kursor server Transact-SQL, seperti perilaku gulirnya dan kueri yang digunakan untuk membangun tataan hasil tempat kursor beroperasi. Pernyataan mengisi OPEN kumpulan hasil, dan FETCH mengembalikan baris dari kumpulan hasil. Pernyataan ini CLOSE merilis kumpulan hasil saat ini yang terkait dengan kursor. Pernyataan ini DEALLOCATE merilis sumber daya yang digunakan oleh kursor.

Bentuk DECLARE CURSOR pertama pernyataan menggunakan sintaks ISO untuk mendeklarasikan perilaku kursor. Bentuk DECLARE CURSOR kedua menggunakan ekstensi Transact-SQL yang memungkinkan Anda menentukan kursor menggunakan jenis kursor yang sama yang digunakan dalam fungsi kursor API database ODBC atau ADO.

Anda tidak dapat mencampur dua formulir. Jika Anda menentukan SCROLL kata kunci atau INSENSITIVE sebelum CURSOR kata kunci, Anda tidak dapat menggunakan kata kunci apa pun antara CURSOR kata kunci dan FOR <select_statement> . Jika Anda menentukan kata kunci antara CURSOR kata kunci dan FOR <select_statement> , Anda tidak dapat menentukan SCROLL atau INSENSITIVE sebelum CURSOR kata kunci.

DECLARE CURSOR Jika menggunakan sintaks Transact-SQL tidak menentukan READ_ONLY, , OPTIMISTICatau SCROLL_LOCKS, defaultnya adalah sebagai berikut:

  • SELECT Jika pernyataan tidak mendukung pembaruan (izin tidak mencukup, mengakses tabel jarak jauh yang tidak mendukung pembaruan, dan sebagainya), kursornya adalah READ_ONLY.

  • STATIC dan FAST_FORWARD kursor default ke READ_ONLY.

  • DYNAMIC dan KEYSET kursor default ke OPTIMISTIC.

Nama kursor hanya dapat dirujuk oleh pernyataan Transact-SQL lainnya. Mereka tidak dapat dirujuk oleh fungsi API database. Misalnya, setelah mendeklarasikan kursor, nama kursor tidak dapat direferensikan dari fungsi atau metode OLE DB, ODBC, atau ADO. Baris kursor tidak dapat diambil menggunakan fungsi pengambilan atau metode API; baris hanya dapat diambil oleh pernyataan Transact-SQL FETCH .

Setelah kursor dinyatakan, prosedur tersimpan sistem ini dapat digunakan untuk menentukan karakteristik kursor.

Prosedur tersimpan sistem Deskripsi
sp_cursor_list Mengembalikan daftar kursor yang saat ini terlihat pada koneksi dan atributnya.
sp_describe_cursor Menjelaskan atribut kursor, seperti apakah itu kursor forward-only atau scrolling.
sp_describe_cursor_columns Menjelaskan atribut kolom dalam tataan hasil kursor.
sp_describe_cursor_tables Menjelaskan tabel dasar yang diakses oleh kursor.

Variabel dapat digunakan sebagai bagian dari select_statement yang mendeklarasikan kursor. Nilai variabel kursor tidak berubah setelah kursor dideklarasikan.

Izin

DECLARE CURSOR Izin default untuk setiap pengguna yang memiliki SELECT izin pada tampilan, tabel, dan kolom yang digunakan dalam kursor.

Batasan

Anda tidak dapat menggunakan kursor atau pemicu pada tabel dengan indeks penyimpan kolom berkluster. Pembatasan ini tidak berlaku untuk indeks penyimpan kolom nonclustered. Anda dapat menggunakan kursor dan pemicu pada tabel dengan indeks penyimpan kolom yang tidak terkluster.

Contoh

J. Menggunakan kursor dan sintaks dasar

Tataan hasil yang dihasilkan pada pembukaan kursor ini mencakup semua baris dan semua kolom dalam tabel. Kursor ini dapat diperbarui, dan semua pembaruan dan penghapusan diwakili dalam pengambilan yang dibuat terhadap kursor ini. FETCH NEXT adalah satu-satunya pengambilan yang SCROLL tersedia karena opsi tidak ditentukan.

DECLARE vend_cursor CURSOR
    FOR SELECT * FROM Purchasing.Vendor
OPEN vend_cursor
FETCH NEXT FROM vend_cursor;

B. Gunakan kursor berlapis untuk menghasilkan output laporan

Contoh berikut menunjukkan bagaimana kursor dapat disarangkan untuk menghasilkan laporan yang kompleks. Kursor dalam dideklarasikan untuk setiap vendor.

SET NOCOUNT ON;

DECLARE @vendor_id INT, @vendor_name NVARCHAR(50),
    @message VARCHAR(80), @product NVARCHAR(50);

PRINT '-------- Vendor Products Report --------';

DECLARE vendor_cursor CURSOR FOR
SELECT VendorID, Name
FROM Purchasing.Vendor
WHERE PreferredVendorStatus = 1
ORDER BY VendorID;

OPEN vendor_cursor

FETCH NEXT FROM vendor_cursor
INTO @vendor_id, @vendor_name

WHILE @@FETCH_STATUS = 0
BEGIN
    PRINT ' '
    SELECT @message = '----- Products From Vendor: ' +
        @vendor_name

    PRINT @message

    -- Declare an inner cursor based
    -- on vendor_id from the outer cursor.

    DECLARE product_cursor CURSOR FOR
    SELECT v.Name
    FROM Purchasing.ProductVendor pv, Production.Product v
    WHERE pv.ProductID = v.ProductID AND
    pv.VendorID = @vendor_id  -- Variable value from the outer cursor

    OPEN product_cursor
    FETCH NEXT FROM product_cursor INTO @product

    IF @@FETCH_STATUS <> 0
        PRINT '         <<None>>'

    WHILE @@FETCH_STATUS = 0
    BEGIN

        SELECT @message = '         ' + @product
        PRINT @message
        FETCH NEXT FROM product_cursor INTO @product
        END

    CLOSE product_cursor
    DEALLOCATE product_cursor
        -- Get the next vendor.
    FETCH NEXT FROM vendor_cursor
    INTO @vendor_id, @vendor_name
END
CLOSE vendor_cursor;
DEALLOCATE vendor_cursor;

Konten terkait