Fungsi yang ditentukan pengguna

Artikel
05/23/2023

Berlaku untuk:SQL Server Azure SQL DatabaseAzure SQL Managed Instance titik akhir analitik SQL di Microsoft Fabric Warehouse di Microsoft Fabric

Seperti fungsi dalam bahasa pemrograman, fungsi yang ditentukan pengguna SQL Server adalah rutinitas yang menerima parameter, melakukan tindakan, seperti perhitungan kompleks, dan mengembalikan hasil tindakan tersebut sebagai nilai. Nilai yang dikembalikan dapat berupa nilai skalar tunggal atau tataan hasil.

Manfaat fungsi yang ditentukan pengguna

Mengapa menggunakan fungsi yang ditentukan pengguna (UDF)?

Pemrograman modular. Anda dapat membuat fungsi sekali, menyimpannya di database, dan memanggilnya beberapa kali dalam program Anda. Fungsi yang ditentukan pengguna dapat dimodifikasi secara independen dari kode sumber program.
Eksekusi yang lebih cepat. Mirip dengan prosedur tersimpan, fungsi yang ditentukan pengguna Transact-SQL mengurangi biaya kompilasi kode Transact-SQL dengan menyimpan paket dan menggunakannya kembali untuk eksekusi berulang. Ini berarti fungsi yang ditentukan pengguna tidak perlu direparasi dan dioptimasi ulang dengan setiap penggunaan menghasilkan waktu eksekusi yang jauh lebih cepat.

Fungsi CLR menawarkan keunggulan performa yang signifikan daripada fungsi Transact-SQL untuk tugas komputasi, manipulasi string, dan logika bisnis. Fungsi Transact-SQL lebih cocok untuk logika intensif akses data.
Kurangi lalu lintas jaringan. Operasi yang memfilter data berdasarkan beberapa batasan kompleks yang tidak dapat diekspresikan dalam ekspresi skalar tunggal dapat diekspresikan sebagai fungsi. Fungsi kemudian dapat dipanggil dalam klausul WHERE untuk mengurangi jumlah baris yang dikirim ke klien.

Penting

UDF Transact-SQL dalam kueri hanya dapat dijalankan pada satu utas (rencana eksekusi serial). Oleh karena itu, menggunakan UDF menghambat pemrosesan kueri paralel. Untuk informasi selengkapnya tentang pemrosesan kueri paralel, lihat Panduan Arsitektur Pemrosesan Kueri.

Jenis-jenis fungsi

Fungsi bernilai skalar

Fungsi skalar yang ditentukan pengguna mengembalikan nilai data tunggal dari jenis yang ditentukan dalam klausa RETURNS. Untuk fungsi skalar sebaris, nilai skalar yang dikembalikan adalah hasil dari satu pernyataan. Untuk fungsi skalar multistatement, isi fungsi dapat berisi serangkaian pernyataan Transact-SQL yang mengembalikan nilai tunggal. Jenis pengembalian dapat berupa jenis data apa pun kecuali teks, ntext, gambar, kursor, dan tanda waktu. Misalnya, lihat Membuat fungsi yang ditentukan pengguna (mesin database).

Fungsi bernilai tabel

Fungsi bernilai tabel (TVF) yang ditentukan pengguna mengembalikan jenis data tabel . Untuk fungsi bernilai tabel sebaris, tidak ada isi fungsi; tabel adalah kumpulan hasil dari satu pernyataan SELECT. Misalnya, lihat Membuat fungsi yang ditentukan pengguna (mesin database).

Fungsi sistem

SQL Server menyediakan banyak fungsi sistem yang dapat Anda gunakan untuk melakukan berbagai operasi. Mereka tidak dapat dimodifikasi. Untuk informasi selengkapnya, lihat Fungsi Bawaan (Transact-SQL), Fungsi Tersimpan Sistem (Transact-SQL), dan Tampilan dan Fungsi Manajemen Dinamis (Transact-SQL).

Panduan

Kesalahan T-SQL yang menyebabkan pernyataan dibatalkan dan dilanjutkan dengan pernyataan berikutnya dalam modul (seperti pemicu atau prosedur tersimpan) diperlakukan secara berbeda di dalam fungsi. Dalam fungsi, kesalahan tersebut menyebabkan eksekusi fungsi berhenti. Hal ini pada gilirannya menyebabkan pernyataan yang memanggil fungsi dibatalkan.

Pernyataan dalam BEGIN...END blok tidak dapat memiliki efek samping. Efek samping fungsi adalah setiap perubahan permanen pada status sumber daya yang memiliki cakupan di luar fungsi seperti modifikasi pada tabel database. Satu-satunya perubahan yang dapat dilakukan oleh pernyataan dalam fungsi adalah perubahan pada objek lokal ke fungsi, seperti kursor atau variabel lokal. Modifikasi pada tabel database, operasi pada kursor yang tidak lokal ke fungsi, mengirim email, mencoba modifikasi katalog, dan menghasilkan kumpulan hasil yang dikembalikan ke pengguna adalah contoh tindakan yang tidak dapat dilakukan dalam fungsi.

CREATE FUNCTION Jika pernyataan menghasilkan efek samping terhadap sumber daya yang tidak ada saat CREATE FUNCTION pernyataan dikeluarkan, SQL Server menjalankan pernyataan. Namun, SQL Server tidak menjalankan fungsi saat dipanggil.

Berapa kali fungsi yang ditentukan dalam kueri dijalankan dapat bervariasi di antara rencana eksekusi yang dibangun oleh pengoptimal. Contohnya adalah fungsi yang dipanggil oleh subkueri dalam WHERE klausa. Berapa kali subkueri dan fungsinya dijalankan dapat bervariasi dengan jalur akses yang berbeda yang dipilih oleh pengoptimal.

Fungsi deterministik harus terikat skema. SCHEMABINDING Gunakan klausa saat membuat fungsi deterministik.

Untuk informasi selengkapnya dan pertimbangan performa pada fungsi yang ditentukan pengguna, lihat Membuat Fungsi yang Ditentukan Pengguna (Mesin Database).

Pernyataan yang valid dalam fungsi

Jenis pernyataan yang valid dalam fungsi meliputi:

DECLARE pernyataan dapat digunakan untuk menentukan variabel data dan kursor yang lokal untuk fungsi.
Penetapan nilai ke objek lokal ke fungsi, seperti menggunakan SET untuk menetapkan nilai ke variabel lokal skalar dan tabel.
Operasi kursor yang mereferensikan kursor lokal yang dideklarasikan, dibuka, ditutup, dan dibatalkan alokasinya dalam fungsi. FETCH pernyataan yang mengembalikan data ke klien tidak diizinkan. Hanya pernyataan FETCH yang menetapkan nilai ke variabel lokal menggunakan INTO klausa yang diizinkan.
Pernyataan kontrol aliran kecuali TRY...CATCH pernyataan.
SELECT pernyataan yang berisi daftar pilihan dengan ekspresi yang menetapkan nilai ke variabel yang bersifat lokal untuk fungsi.
UPDATE, INSERT, dan DELETE pernyataan memodifikasi variabel tabel yang bersifat lokal ke fungsi.
EXECUTE pernyataan yang memanggil prosedur tersimpan yang diperluas.

Fungsi sistem bawaan

Fungsi bawaan nondeterministik berikut dapat digunakan dalam fungsi yang ditentukan pengguna Transact-SQL.

CURRENT_TIMESTAMP
GET_TRANSMISSION_STATUS
GETDATE
GETUTCDATE
@@CONNECTIONS
@@CPU_BUSY
@@DBTS
@@IDLE
@@IO_BUSY
@@MAX_CONNECTIONS
@@PACK_RECEIVED
@@PACK_SENT
@@PACKET_ERRORS
@@TIMETICKS
@@TOTAL_ERRORS
@@TOTAL_READ
@@TOTAL_WRITE

Fungsi bawaan nondeterministik berikut tidak dapat digunakan dalam fungsi yang ditentukan pengguna Transact-SQL.

NEWID
NEWSEQUENTIALID
RAND
TEXTPTR

Untuk daftar fungsi sistem bawaan deterministik dan nondeterministik, lihat Fungsi Deterministik dan Nondeterministik.

Fungsi terikat skema

CREATE FUNCTION mendukung klausa SCHEMABINDING yang mengikat fungsi ke skema objek apa pun yang dirujuknya, seperti tabel, tampilan, dan fungsi lain yang ditentukan pengguna. Upaya untuk mengubah atau menghilangkan objek apa pun yang dirujuk oleh fungsi terikat skema gagal.

Kondisi ini harus dipenuhi sebelum Anda dapat menentukan SCHEMABINDING dalam CREATE FUNCTION:

Semua tampilan dan fungsi yang ditentukan pengguna yang dirujuk oleh fungsi harus terikat skema.
Semua objek yang dirujuk oleh fungsi harus berada dalam database yang sama dengan fungsi . Objek harus dirujuk menggunakan nama satu bagian atau dua bagian.
Anda harus memiliki REFERENCES izin pada semua objek (tabel, tampilan, dan fungsi yang ditentukan pengguna) yang dirujuk dalam fungsi.

Anda dapat menggunakan ALTER FUNCTION untuk menghapus pengikatan skema. Pernyataan ALTER FUNCTION harus menentukan ulang fungsi tanpa menentukan WITH SCHEMABINDING.

Menentukan parameter

Fungsi yang ditentukan pengguna mengambil parameter input nol atau lebih dan mengembalikan nilai skalar atau tabel. Fungsi dapat memiliki maksimum 1024 parameter input. Ketika parameter fungsi memiliki nilai default, kata kunci DEFAULT harus ditentukan saat memanggil fungsi untuk mendapatkan nilai default. Perilaku ini berbeda dari parameter dengan nilai default dalam prosedur tersimpan yang ditentukan pengguna di mana menghilangkan parameter juga menyiratkan nilai default. Fungsi yang ditentukan pengguna tidak mendukung parameter output.