CREATE FUNCTION (Azure Synapse Analytics dan Microsoft Fabric)

  • Artikel

Berlaku untuk:Titik akhir analitik Sistem Platform Analitik Azure Synapse Analytics(PDW)SQL di Microsoft FabricWarehouse 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

Konvensi sintaks transact-SQL

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.

Langkah selanjutnya

Membuat fungsi yang ditentukan pengguna (mesin database)