CREATE FUNCTION (Azure Synapse Analytics dan Microsoft Fabric)
Berlaku untuk: Titik akhir analitik SQL Azure Synapse Analytics Platform System (PDW) di Microsoft Fabric Warehouse di Microsoft Fabric
Membuat fungsi yang ditentukan pengguna (UDF) di Azure Synapse Analytics, Analytics Platform System (PDW), atau Microsoft Fabric. Fungsi yang ditentukan pengguna adalah rutinitas Transact-SQL yang menerima parameter, melakukan tindakan, seperti perhitungan kompleks, dan mengembalikan hasil tindakan tersebut sebagai nilai.
Di Analytics Platform System (PDW), nilai yang dikembalikan harus berupa nilai skalar (tunggal).
Di Azure Synapse Analytics, CREATE FUNCTION dapat mengembalikan tabel dengan menggunakan sintaks untuk fungsi bernilai tabel sebaris (pratinjau) atau dapat mengembalikan satu nilai dengan menggunakan sintaks untuk fungsi skalar.
Di Microsoft Fabric dan kumpulan SQL tanpa server di Azure Synapse Analytics, CREATE FUNCTION dapat membuat fungsi nilai tabel sebaris tetapi bukan fungsi skalar. Fungsi bernilai tabel (TVF) yang ditentukan pengguna mengembalikan jenis data tabel.
Gunakan pernyataan ini untuk membuat rutinitas yang dapat digunakan kembali yang dapat digunakan dengan cara berikut:
Dalam pernyataan Transact-SQL seperti
SELECT
Dalam aplikasi yang memanggil fungsi
Dalam definisi fungsi lain yang ditentukan pengguna
Untuk menentukan batasan CHECK pada kolom
Untuk mengganti prosedur tersimpan
Menggunakan fungsi sebaris sebagai predikat filter untuk kebijakan keamanan
Sintaks
Sintaks fungsi skalar
-- Transact-SQL Scalar Function Syntax (in dedicated pools in Azure Synapse Analytics and Parallel Data Warehouse)
-- Not available in the serverless SQL pools in Azure Synapse Analytics or Microsoft Fabric
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] parameter_data_type
[ = default ] }
[ ,...n ]
]
)
RETURNS return_data_type
[ WITH <function_option> [ ,...n ] ]
[ AS ]
BEGIN
function_body
RETURN scalar_expression
END
[ ; ]
<function_option>::=
{
[ SCHEMABINDING ]
| [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
}
Sintaks fungsi bernilai tabel sebaris
-- Transact-SQL Inline Table-Valued Function Syntax
-- Preview in dedicated SQL pools in Azure Synapse Analytics
-- Available in the serverless SQL pools in Azure Synapse Analytics and Microsoft Fabric
CREATE FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] parameter_data_type
[ = default ] }
[ ,...n ]
]
)
RETURNS TABLE
[ WITH SCHEMABINDING ]
[ AS ]
RETURN [ ( ] select_stmt [ ) ]
[ ; ]
Argumen
schema_name
Nama skema tempat fungsi yang ditentukan pengguna berada.
function_name
Nama fungsi yang ditentukan pengguna. Nama fungsi harus mematuhi aturan untuk pengidentifikasi dan harus unik dalam database dan skemanya.
Catatan
Tanda kurung diperlukan setelah nama fungsi meskipun parameter tidak ditentukan.
@parameter_name
Adalah parameter dalam fungsi yang ditentukan pengguna. Satu atau beberapa parameter dapat dideklarasikan.
Fungsi dapat memiliki maksimum 2.100 parameter. Nilai setiap parameter yang dideklarasikan harus disediakan oleh pengguna ketika fungsi dijalankan, kecuali default untuk parameter ditentukan.
Tentukan nama parameter dengan menggunakan tanda (@
) sebagai karakter pertama. Nama parameter harus mematuhi aturan untuk pengidentifikasi. Parameter bersifat lokal untuk fungsi; nama parameter yang sama dapat digunakan dalam fungsi lain. Parameter hanya dapat menggantikan konstanta; mereka tidak dapat digunakan alih-alih nama tabel, nama kolom, atau nama objek database lainnya.
Catatan
ANSI_WARNINGS
tidak dihormati ketika Anda meneruskan parameter dalam prosedur tersimpan, fungsi yang ditentukan pengguna, atau ketika Anda mendeklarasikan dan mengatur variabel dalam pernyataan batch. Misalnya, jika variabel didefinisikan sebagai karakter(3), lalu diatur ke nilai yang lebih besar dari tiga karakter, data dipotong ke ukuran yang ditentukan dan pernyataan INSERT atau UPDATE berhasil.
parameter_data_type
Adalah jenis data parameter. Untuk fungsi Transact-SQL, semua jenis data skalar yang didukung di Azure Synapse Analytics diizinkan. Jenis data tanda waktu (rowversion) bukan jenis yang didukung.
[ =default ]
Adalah nilai default untuk parameter . Jika nilai default ditentukan, fungsi dapat dijalankan tanpa menentukan nilai untuk parameter tersebut.
Ketika parameter fungsi memiliki nilai default, kata kunci DEFAULT harus ditentukan ketika fungsi dipanggil untuk mengambil nilai default. Perilaku ini berbeda dari menggunakan parameter dengan nilai default dalam prosedur tersimpan di mana menghilangkan parameter juga menyiratkan nilai default.
return_data_type
Adalah nilai pengembalian dari fungsi skalar yang ditentukan pengguna. Untuk fungsi Transact-SQL, semua jenis data skalar yang didukung di Azure Synapse Analytics diizinkan. Jenis data tanda waktu rowversion/bukan jenis yang didukung. Jenis kursor dan tabel nonscalar tidak diperbolehkan.
function_body
Serangkaian pernyataan Transact-SQL. function_body tidak boleh berisi pernyataan SELECT dan tidak dapat mereferensikan data database. function_body tidak dapat mereferensikan tabel atau tampilan. Isi fungsi dapat memanggil fungsi deterministik lainnya tetapi tidak dapat memanggil fungsi nondeterministik.
Dalam fungsi skalar, function_body adalah serangkaian pernyataan Transact-SQL yang bersama-sama mengevaluasi ke nilai skalar.
scalar_expression
Menentukan nilai skalar yang dikembalikan fungsi skalar.
select_stmt
Adalah pernyataan tunggal SELECT
yang menentukan nilai pengembalian fungsi bernilai tabel sebaris. Untuk fungsi bernilai tabel sebaris, tidak ada isi fungsi; tabel adalah kumpulan hasil dari satu SELECT
pernyataan.
TABLE
Menentukan bahwa nilai pengembalian fungsi bernilai tabel (TVF) adalah tabel. Hanya konstanta dan @local_variables yang dapat diteruskan ke TVF.
Dalam TVF sebaris (pratinjau), nilai pengembalian TABLE didefinisikan melalui satu SELECT
pernyataan. Fungsi sebaris tidak memiliki variabel pengembalian terkait.
<function_option>
Menentukan bahwa fungsi memiliki satu atau beberapa opsi berikut.
SCHEMABINDING
Menentukan bahwa fungsi terikat ke objek database yang dirujuknya. Ketika SCHEMABINDING ditentukan, objek dasar tidak dapat dimodifikasi dengan cara yang akan memengaruhi definisi fungsi. Definisi fungsi itu sendiri harus terlebih dahulu dimodifikasi atau dihilangkan untuk menghapus dependensi pada objek yang akan dimodifikasi.
Pengikatan fungsi ke objek yang dirujuknya dihapus hanya ketika salah satu tindakan berikut terjadi:
Fungsi dihilangkan.
Fungsi ini dimodifikasi dengan menggunakan pernyataan ALTER dengan opsi SCHEMABINDING yang tidak ditentukan.
Fungsi dapat terikat skema hanya jika kondisi berikut benar:
Setiap fungsi yang ditentukan pengguna yang dirujuk oleh fungsi juga terikat skema.
Fungsi dan UDF lain yang dirujuk oleh fungsi dirujuk menggunakan nama satu bagian atau dua bagian.
Hanya fungsi bawaan dan UDF lain dalam database yang sama yang dapat dirujuk dalam isi UDF.
Pengguna yang menjalankan
CREATE FUNCTION
pernyataan memiliki izin REFERENCES pada objek database yang dirujuk fungsi.
Untuk menghapus SCHEMABINDING, gunakan ALTER
.
MENGEMBALIKAN NULL PADA INPUT NULL | DIPANGGIL PADA INPUT NULL
Menentukan atribut OnNULLCall dari fungsi bernilai skalar. Jika tidak ditentukan, CALLED ON NULL INPUT
tersirat secara default, dan isi fungsi dijalankan meskipun NULL
diteruskan sebagai argumen.
Praktik terbaik
Jika fungsi yang ditentukan pengguna tidak dibuat dengan klausa SCHEMABINDING, perubahan yang dilakukan pada objek yang mendasar dapat memengaruhi definisi fungsi dan menghasilkan hasil yang tidak terduga saat dipanggil. Kami menyarankan agar Anda menerapkan salah satu metode berikut untuk memastikan bahwa fungsi tidak menjadi usang karena perubahan pada objek yang mendasarnya:
WITH SCHEMABINDING
Tentukan klausa saat Anda membuat fungsi. Ini memastikan bahwa objek yang dirujuk dalam definisi fungsi tidak dapat dimodifikasi kecuali fungsi juga dimodifikasi.
Interoperabilitas
Pernyataan berikut valid dalam fungsi bernilai skalar:
Pernyataan penugasan.
Pernyataan Control-of-Flow kecuali TRY... Pernyataan CATCH.
Pernyataan DECLARE yang mendefinisikan variabel data lokal.
Dalam fungsi bernilai tabel sebaris (pratinjau), hanya satu pernyataan pilih yang diizinkan.
Batasan
Fungsi yang ditentukan pengguna tidak dapat digunakan untuk melakukan tindakan yang mengubah status database.
Fungsi yang ditentukan pengguna dapat ditumpuk; artinya, satu fungsi yang ditentukan pengguna dapat memanggil fungsi lain. Tingkat berlapis bertahpa ketika fungsi yang disebut memulai eksekusi, dan dikurangi ketika fungsi yang dipanggil menyelesaikan eksekusi. Fungsi yang ditentukan pengguna dapat ditumpuk hingga 32 tingkat. Melebihi tingkat maksimum bersarang menyebabkan seluruh rantai fungsi panggilan gagal.
Objek, termasuk fungsi, tidak dapat dibuat dalam master
database kumpulan SQL tanpa server Anda di Azure Synapse Analytics.
Metadata
Bagian ini mencantumkan tampilan katalog sistem yang dapat Anda gunakan untuk mengembalikan metadata tentang fungsi yang ditentukan pengguna.
sys.sql_modules : Menampilkan definisi fungsi yang ditentukan pengguna Transact-SQL. Contohnya:
SELECT definition, type FROM sys.sql_modules AS m JOIN sys.objects AS o ON m.object_id = o.object_id AND type = ('FN');
sys.parameters : Menampilkan informasi tentang parameter yang ditentukan dalam fungsi yang ditentukan pengguna.
sys.sql_expression_dependencies : Menampilkan objek yang mendasar yang direferensikan oleh fungsi.
Izin
Memerlukan izin CREATE FUNCTION dalam database dan izin UBAH pada skema tempat fungsi sedang dibuat.
Contoh: Azure Synapse Analytics and Analytics Platform System (PDW)
J. Menggunakan fungsi bernilai skalar yang ditentukan pengguna untuk mengubah jenis data
Fungsi sederhana ini mengambil jenis data int sebagai input, dan mengembalikan jenis data desimal (10,2) sebagai output.
CREATE FUNCTION dbo.ConvertInput (@MyValueIn int)
RETURNS decimal(10,2)
AS
BEGIN
DECLARE @MyValueOut int;
SET @MyValueOut= CAST( @MyValueIn AS decimal(10,2));
RETURN(@MyValueOut);
END;
GO
SELECT dbo.ConvertInput(15) AS 'ConvertedValue';
Catatan
Fungsi skalar tidak tersedia di kumpulan SQL tanpa server atau Microsoft Fabric.
Contoh: Azure Synapse Analytics
J. Membuat fungsi bernilai tabel sebaris
Contoh berikut membuat fungsi bernilai tabel sebaris untuk mengembalikan beberapa informasi utama pada modul, pemfilteran menurut objectType
parameter. Ini termasuk nilai default untuk mengembalikan semua modul ketika fungsi dipanggil dengan DEFAULT
parameter . Contoh ini menggunakan beberapa tampilan katalog sistem yang disebutkan dalam Metadata.
CREATE FUNCTION dbo.ModulesByType(@objectType CHAR(2) = '%%')
RETURNS TABLE
AS
RETURN
(
SELECT
sm.object_id AS 'Object Id',
o.create_date AS 'Date Created',
OBJECT_NAME(sm.object_id) AS 'Name',
o.type AS 'Type',
o.type_desc AS 'Type Description',
sm.definition AS 'Module Description'
FROM sys.sql_modules AS sm
JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE o.type like '%' + @objectType + '%'
);
GO
Fungsi kemudian dapat dipanggil untuk mengembalikan semua objek tampilan (V) dengan:
select * from dbo.ModulesByType('V');
Catatan
Fungsi nilai tabel sebaris tersedia di kumpulan SQL tanpa server, tetapi dalam pratinjau di kumpulan SQL khusus.
B. Menggabungkan hasil fungsi bernilai tabel sebaris
Contoh sederhana ini menggunakan TVF sebaris yang dibuat sebelumnya untuk menunjukkan bagaimana hasilnya dapat dikombinasikan dengan tabel lain menggunakan penerapan silang. Di sini, kita memilih semua kolom dari dan sys.objects
hasil ModulesByType
untuk semua baris yang cocok pada kolom jenis . Untuk detail selengkapnya tentang menggunakan terapkan, lihat klausa FROM ditambah JOIN, APPLY, PIVOT.
SELECT *
FROM sys.objects o
CROSS APPLY dbo.ModulesByType(o.type);
GO
Catatan
Fungsi nilai tabel sebaris tersedia di kumpulan SQL tanpa server, tetapi dalam pratinjau di kumpulan SQL khusus.