Catatan
Akses ke halaman ini memerlukan otorisasi. Anda dapat mencoba masuk atau mengubah direktori.
Akses ke halaman ini memerlukan otorisasi. Anda dapat mencoba mengubah direktori.
Berlaku untuk:Endpoint SQL Analytics di Microsoft Fabric dan Warehouse di Microsoft Fabric
CREATE FUNCTION membuat fungsi bernilai tabel dan fungsi skalar.
Catatan
UDF eksternal adalah fitur pratinjau di Fabric Data Warehouse.
Artikel ini berlaku untuk Microsoft Fabric Data Warehouse dan endpoint analitik SQL dari item Fabric. Untuk platform lain, lihat CREATE FUNCTION (Transact-SQL).
Fungsi yang ditentukan pengguna adalah rutinitas Transact-SQL yang menerima parameter, melakukan tindakan seperti perhitungan kompleks, dan mengembalikan hasil tindakan tersebut sebagai nilai. Fungsi skalar mengembalikan nilai skalar, seperti angka atau string. Fungsi bernilai tabel (TVF) yang ditentukan pengguna mengembalikan tabel.
Gunakan CREATE FUNCTION untuk membuat rutinitas T-SQL yang dapat digunakan kembali yang dapat Anda gunakan dengan cara berikut:
- Dalam Transact-SQL pernyataan seperti
SELECT. - Di Warehouse, dalam Transact-SQL pernyataan manipulasi data (DML) seperti
UPDATE,INSERT, danDELETE. - Dalam aplikasi yang memanggil fungsi.
- Dalam definisi fungsi lain yang didefinisikan pengguna.
- Untuk menggantikan prosedur tersimpan.
Tentukan CREATE OR ALTER FUNCTION untuk membuat fungsi baru jika tidak ada fungsi dengan nama tersebut, atau mengubah fungsi yang sudah ada, dalam satu pernyataan.
Sintaks
Sintaks fungsi skalar
CREATE [ OR ALTER ] 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>::=
{
[ INLINE = AUTO ]
| [ SCHEMABINDING ]
| [ RETURNS NULL ON NULL INPUT | CALLED ON NULL INPUT ]
}
Sintaks fungsi bernilai tabel sebaris
CREATE [ OR ALTER ] FUNCTION [ schema_name. ] function_name
( [ { @parameter_name [ AS ] parameter_data_type
[ = default ] }
[ ,...n ]
]
)
RETURNS TABLE
[ WITH SCHEMABINDING ]
[ AS ]
RETURN [ ( ] select_stmt [ ) ]
[ ; ]
Sintaks fungsi eksternal
UDF eksternal adalah fungsi yang merujuk pada Fungsi Data Pengguna Fabric eksternal.
CREATE FUNCTION [ schema_name. ] function_name
[ RETURNS return_data_type ]
AS EXTERNAL FUNCTION external_function_set_name.external_function_name
[ ; ]
Catatan
UDF eksternal adalah fitur pratinjau di Fabric Data Warehouse.
Pernyataan ini CREATE FUNCTION secara otomatis menurunkan tipe pengembalian, parameter, dan tipe parameter dari definisi Fungsi Data Pengguna Fabric eksternal yang direferensikan. Jika tanda tangan Fungsi Data Pengguna Fabric eksternal berubah, Anda harus membuat ulang fungsi tersebut untuk menyinkronkan definisinya dengan tanda tangan yang diperbarui.
Argumen
schema_name
Nama skema tempat fungsi yang ditentukan pengguna berada.
function_name
Nama fungsi yang ditentukan pengguna. Nama fungsi harus mengikuti aturan untuk pengidentifikasi dan unik dalam database dan skemanya.
Untuk definisi fungsi skalar T-SQL native dan fungsi bernilai tabel inline, sertakan tanda kurung setelah nama fungsi meskipun tidak ada parameter.
Definisi fungsi eksternal menggunakan sintaks terpisah AS EXTERNAL FUNCTION .
@ parameter_name
Parameter dalam fungsi yang ditentukan pengguna. Anda dapat mendeklarasikan satu atau beberapa parameter.
Sebuah fungsi dapat memiliki hingga 2.100 parameter. Ketika pengguna atau aplikasi memanggil fungsi, Anda harus memberikan nilai untuk setiap parameter yang dideklarasikan kecuali Anda menentukan nilai default untuk parameter tersebut.
Tentukan nama parameter dengan menggunakan tanda (@) sebagai karakter pertama. Nama parameter harus mengikuti aturan untuk pengidentifikasi. Parameter bersifat lokal untuk fungsi. Anda dapat menggunakan nama parameter yang sama di fungsi lain. Parameter hanya dapat menggantikan konstanta. Mereka tidak dapat digunakan sebagai pengganti nama tabel, nama kolom, atau nama objek database lain.
ANSI_WARNINGS tidak dihormati saat Anda mengirim parameter dalam prosedur tersimpan, fungsi yang didefinisikan pengguna, atau ketika Anda mendeklarasikan dan mengatur variabel dalam pernyataan batch. Misalnya, jika Anda mendefinisikan variabel sebagai char(3), dan kemudian mengaturnya ke nilai yang lebih besar dari tiga karakter, data dipotong ke ukuran yang ditentukan dan pernyataan SQL berhasil.
parameter_data_type
Jenis data parameter. Untuk fungsi Transact-SQL, semua jenis data skalar yang didukung diizinkan.
[ = default ]
Nilai default untuk parameter. Jika Anda menentukan nilai default , Anda dapat menjalankan fungsi tanpa menentukan nilai untuk parameter tersebut.
Ketika parameter fungsi memiliki nilai default, Anda harus menentukan kata kunci DEFAULT saat memanggil fungsi 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
Nilai yang dikembalikan dari fungsi skalar yang ditentukan pengguna.
Untuk fungsi di Fabric Data Warehouse, Anda dapat menggunakan semua tipe data kecualitimestamprowversion/. Jenis nonskalar seperti tabel tidak diizinkan.
Untuk UDF eksternal (Pratinjau), klausul ini RETURNS bersifat opsional. Jika dihilangkan, tipe pengembalian secara otomatis disimpulkan dari tipe pengembalian Fungsi Data Pengguna Fabric yang direferensikan. Anda dapat menentukan RETURNS klausa untuk menimpa tipe return yang disimpulkan jika diperlukan.
function_body
Serangkaian pernyataan Transact-SQL.
Dalam fungsi skalar, function_body adalah serangkaian pernyataan Transact-SQL yang bersama-sama mengevaluasi ke nilai skalar, yang dapat mencakup:
- Ekspresi pernyataan tunggal
- Ekspresi multi-pernyataan (
IF/ELSEdanBEGIN/ENDblok) - Variabel lokal
- Panggilan ke fungsi SQL bawaan tersedia
- Panggilan ke UDF lain
-
SELECTpernyataan, dan referensi ke tabel, tampilan, dan fungsi bernilai tabel inline - Pernyataan kontrol aliran, termasuk
IF/ELSE,WHILE, ,BREAKCONTINUE, danRETURN
Untuk UDF eksternal (Pratinjau), Anda tidak dapat menentukan function_body karena implementasi fungsi didefinisikan secara eksternal dalam Fungsi Data Pengguna Fabric yang terkait. Database hanya menyimpan metadata fungsi. Logika eksekusi berada pada definisi fungsi eksternal.
scalar_expression
Menentukan nilai skalar yang dikembalikan fungsi skalar.
select_stmt
Pernyataan tunggal SELECT yang menentukan nilai pengembalian fungsi bernilai tabel sebaris. Untuk fungsi bernilai tabel inline, tidak ada badan fungsi. Tabel adalah himpunan hasil dari satu pernyataan tunggal SELECT .
TABLE
Menentukan bahwa nilai pengembalian fungsi bernilai tabel (TVF) adalah tabel. Anda hanya dapat meneruskan konstanta dan @local_variables ke TVF.
Dalam TVF sebaris (pratinjau), Anda menentukan TABLE nilai yang ditampilkan melalui satu SELECT pernyataan. Fungsi sebaris tidak memiliki variabel pengembalian terkait.
<function_option>
Dalam Fabric Data Warehouse, ENCRYPTION kata kunci dan EXECUTE AS tidak didukung.
Opsi fungsi yang didukung meliputi:
INLINE = OTOMATIS
Menentukan apakah fungsi skalar yang didefinisikan pengguna dapat dibuat atau diubah tanpa memandang kebutuhan inline. Klausa INLINE bersifat opsional. Untuk UDF skalar inlineable, menentukan INLINE = AUTO tidak mengubah kemampuan inlineabilitas, atau perilaku eksekusinya.
Untuk pola panggilan mandiri yang didukung, lihat Buat UDF skalar untuk eksekusi mandiri.
SCHEMABINDING
Menentukan bahwa fungsi terikat ke objek database yang dirujuknya. Saat menentukan SCHEMABINDING, Anda tidak dapat memodifikasi objek yang mendasarinya (seperti tampilan atau tabel, misalnya) dengan cara yang memengaruhi definisi fungsi. Anda harus terlebih dahulu memodifikasi atau menghapus definisi fungsi untuk menghapus dependensi pada objek yang ingin Anda modifikasi.
Pengikatan fungsi ke objek yang dirujuknya dihapus hanya ketika salah satu tindakan berikut terjadi:
Anda menjatuhkan fungsinya.
Anda
ALTERpernyataan fungsi dan hapus opsi.SCHEMABINDING
Anda hanya dapat mengikat skema fungsi jika kondisi berikut benar:
Setiap fungsi yang ditentukan pengguna yang direferensikan fungsi juga terikat skema.
Fungsi mereferensikan objek dengan menggunakan nama dua bagian.
Di dalam isi UDF, Anda hanya dapat mereferensikan fungsi bawaan dan UDF lainnya dalam database yang sama.
Pengguna yang menjalankan
CREATE FUNCTIONpernyataan memiliki izin REFERENSI pada objek database yang direferensikan fungsi.
Untuk menghapus SCHEMABINDING, gunakan ALTER.
MENGEMBALIKAN NULL PADA INPUT NULL | DIPANGGIL PADA INPUT NULL
OnNULLCall Menentukan atribut fungsi bernilai skalar. Jika Anda tidak menentukan atribut ini, CALLED ON NULL INPUT tersirat secara default, dan isi fungsi dieksekusi meskipun NULL diteruskan sebagai argumen.
SEBAGAI EKSTERNAL FUNCTION
Merujuk pada fungsi data pengguna Fabric (UDF). Ketika fungsi dipanggil, eksekusi didelegasikan ke Fungsi Data Pengguna Fabric yang direferensikan, di mana logika dijalankan dan hasilnya dikembalikan ke pemanggil T-SQL.
Klausul ini menerima parameter berikut:
- Nama set fungsi: Nama set fungsi yang berisi Fungsi Data Pengguna Fabric. Setiap Fungsi Data Pengguna Fabric harus termasuk dalam satu set fungsi.
- Nama fungsi: Nama Fungsi Data Pengguna Fabric yang akan direferensikan.
Sintaks berikut menunjukkan bagaimana parameter ini ditentukan:
CREATE FUNCTION [schema_name.]function_name
AS EXTERNAL FUNCTION <<functionset_name>>.<<python_function_name>>
Karena fungsi dijalankan secara jarak jauh, pemanggilan fungsi eksternal biasanya menimbulkan latensi lebih tinggi dibandingkan menjalankan fungsi T-SQL asli yang didefinisikan pengguna. Gunakan fungsi eksternal ketika logika bisnis yang diperlukan diimplementasikan di Fabric dan tidak dapat diekspresikan langsung di T-SQL.
Pernyataan ini CREATE FUNCTION secara otomatis menurunkan tipe pengembalian, parameter, dan tipe parameter dari definisi Fungsi Data Pengguna Fabric eksternal yang direferensikan. Jika tanda tangan Fungsi Data Pengguna Fabric eksternal berubah, Anda harus membuat ulang fungsi tersebut untuk menyinkronkan definisinya dengan tanda tangan yang diperbarui.
Praktik terbaik
Tulis fungsi yang ditentukan pengguna agar dapat di-inlineable. Untuk informasi lebih lanjut tentang konsep inline, lihat Inlining UDF skalar. Untuk contoh cara membuat UDF skalar menjadi inlineable, lihat Buat UDF skalar di Microsoft Fabric Data Warehouse.
Jika Anda tidak membuat fungsi yang ditentukan pengguna dengan pengikatan skema, perubahan pada objek yang mendasarinya dapat memengaruhi definisi fungsi dan menyebabkan hasil yang tidak terduga saat Anda memanggil fungsi. Saat menentukan
WITH SCHEMABINDINGkapan Anda membuat fungsi, Anda memastikan bahwa perubahan selanjutnya pada objek yang mendasarinya tidak dapat mengubah atau merusak perilaku fungsi.Jika memungkinkan, implementasikan logika Anda sebagai fungsi pengguna T-SQL (UDF). Gunakan UDF eksternal hanya untuk fungsionalitas yang tidak dapat diimplementasikan dalam bahasa T-SQL. Pendekatan ini membantu meminimalkan overhead eksekusi jarak jauh dan umumnya memberikan performa kueri yang lebih baik.
Interoperabilitas
Fungsi yang ditentukan pengguna bernilai tabel sebaris
Fungsi bernilai tabel sebaris hanya menerima satu SELECT pernyataan.
Fungsi skalar yang ditentukan pengguna
Ketika UDF skalar tidak dapat diinline,
CREATEpernyataan ORALTERakan menolak pernyataan DDL dengan pesan kesalahan khusus.Fungsi non-inlineable dapat dibuat dengan menentukan
INLINE=AUTOopsi fungsi. Fungsi non-inlineable tidak dapat digunakan dalamSELECT ... FROMquery pada tabel pengguna.INLINE=AUTOtidak membuat fungsi menjadi inlineable.Pernyataan berikut valid dalam fungsi bernilai skalar:
- Pernyataan penugasan.
- Pernyataan kontrol alur kecuali
TRY...CATCHdanGOTOpernyataan. -
DECLAREpernyataan yang mendefinisikan variabel data lokal. - Panggilan ke fungsi bawaan.
- Referensi ke tabel/tampilan/iTVF/UDF skalar lainnya.
Pernyataan DML tidak diperbolehkan dalam fungsi skalar yang didefinisikan pengguna.
Metainformasi
Bagian ini mencantumkan tampilan katalog sistem yang dapat Anda gunakan untuk mengembalikan metadata tentang fungsi yang ditentukan pengguna.
sys.sql_modules: Menampilkan definisi fungsi Transact-SQL yang didefinisikan pengguna, serta informasi inlineability. Contohnya:
SELECT SCHEMA_NAME(o.schema_id) AS SchemaName, o.name AS FunctionName, m.definition AS FunctionDefinition, m.is_inlineable AS Inlineable, m.inline_eligibility_mask AS InlineEligibilityMask FROM sys.objects o JOIN sys.sql_modules m ON o.object_id = m.object_id WHERE o.type = 'FN';sys.parameters: Menampilkan informasi tentang parameter yang didefinisikan dalam fungsi yang ditentukan pengguna. Contoh berikut menggunakan
sys.parameterstampilan katalog untuk menampilkan tanda tangan fungsi, termasuk tipe pengembalian dan tipe data parameter:WITH metadata AS ( SELECT o.schema_id, o.object_id, o.name, p.parameter_id, p.name AS parameter_name, t.name AS type_name FROM sys.objects o JOIN sys.parameters p ON p.object_id = o.object_id JOIN sys.types t ON t.user_type_id = p.user_type_id WHERE o.type IN ('FN', 'XF') ), signature AS ( SELECT SCHEMA_NAME(schema_id) as schema_name, name, ANY_VALUE(CASE WHEN parameter_id = 0 THEN type_name END) return_type, CONCAT( SCHEMA_NAME(schema_id), '.', name, '(',STRING_AGG(CASE WHEN parameter_id > 0 THEN CONCAT(parameter_name, ' ', type_name) END, ', ' ) WITHIN GROUP (ORDER BY parameter_id), ') -> ', MAX(CASE WHEN parameter_id = 0 THEN type_name END) ) AS signature FROM metadata GROUP BY schema_id, object_id, name ) SELECT * FROM signature;sys.sql_expression_dependencies: Menampilkan objek dasar yang direferensikan oleh fungsi.
Izin
Anggota peran Administrator, Anggota, dan Kontributor ruang kerja Fabric dapat membuat fungsi.
Inlining UDF Skalar
Microsoft Fabric Data Warehouse secara otomatis menginlinekan UDF skalar yang memenuhi syarat selama kompilasi query.
Inline Blok Ekspresi mengompilasi pernyataan fungsi, termasuk cabang dan loop, menjadi ekspresi skalar yang dijalankan sebagai bagian dari query yang memanggil. Inlining UDF skalar mengubah badan fungsi menjadi ekspresi atau subquery sebagai bagian dari query yang memanggil.
Sebuah fungsi dapat memenuhi persyaratan untuk kedua teknik tersebut, tetapi mesin memilih satu berdasarkan definisinya. Fungsi yang berbeda dalam kueri yang sama dapat menggunakan teknik yang berbeda.
Seiring Microsoft Fabric Data Warehouse memperluas kemampuan inline-nya, fungsi yang sudah ada akan mendapatkannya secara otomatis.
Konstruksi fungsi yang didukung
Tabel berikut membandingkan konstruksi badan-fungsi yang didukung.
| Konstruksi fungsi-badan | Inlining Blok Ekspresi | Inlining UDF skalar |
|---|---|---|
Ekspresi skalar, variabel lokal, dan IF/ELSE |
Dukungan | Dukungan |
WHILE loop, termasuk BREAK dan CONTINUE |
Dukungan | Tidak didukung |
Beberapa RETURN pernyataan |
Dukungan | Tidak didukung |
NEWID() |
Dukungan | Tidak didukung |
Fungsi yang bergantung waktu (misalnya, GETDATE() dan SYSDATETIME()) |
Dukungan | Tidak didukung |
| Akses TVF meja, tampilan, atau inline | Tidak didukung | Dukungan |
| Panggilan ke UDF skalar* | Tidak didukung | Dukungan |
* Fungsi yang didefinisikan pengguna dapat bersarang: satu UDF dapat memanggil yang lain. Tingkat nesting meningkat saat fungsi yang dipanggil mulai dan menurun saat fungsi selesai. Microsoft Fabric Data Warehouse mendukung hingga empat tingkat nesting ketika badan UDF merujuk pada tabel, tampilan, atau TVF inline, atau hingga 32 level jika tidak. Melebihi batas nesting menyebabkan rantai fungsi pemanggil gagal.
Fungsi bawaan
Dukungan untuk fungsi bawaan, termasuk fungsi sistem, berbeda antara kedua teknik inlining tersebut.
- Inlining Blok Ekspresi tidak mendukung fungsi AI, fungsi agregat, fungsi metadata, atau fungsi keamanan di badan fungsi.
- Inlining UDF skalar memiliki pembatasan fungsi bawaan yang dijelaskan dalam persyaratan inlining UDF skalar.
Misalnya, membuat UDF yang menggunakan @@ROWCOUNT tanpa INLINE = AUTO mengembalikan error 19841. Pesan kesalahan mencakup teks berikut:
Function <function_name> cannot be inlined.
Expression block: Unsupported built-in @@ROWCOUNT found.
Scalar UDF inlining: Unsupported built-in @@ROWCOUNT found.
Untuk definisi fungsi tertentu, lihat Periksa apakah UDF skalar dapat diinline.
Panggilan kueri yang didukung
Tabel berikut membandingkan dukungan calling-query untuk UDF skalar inlineable.
| Fitur kueri | Inlining Blok Ekspresi | Inlining UDF skalar |
|---|---|---|
Panggilan UDF dalam SELECT daftar, WHERE, atau JOIN ... ON |
Dukungan | Dukungan |
Panggilan langsung UDF atau GROUP BYHAVING |
Dukungan | Tidak didukung |
Panggilan langsung dari UDF ORDER BY |
Dukungan | Tidak didukung |
| CTE dalam kueri pemanggil | Dukungan | Tidak didukung |
ROLLUP, CUBE, atau GROUPING SETS |
Tidak didukung | Tidak didukung |
Jika ada panggilan UDF yang bergantung pada inlining UDF skalar, seluruh kueri harus memenuhi persyaratan kueri pemanggil teknik tersebut.
Dengan inlining UDF skalar, proyeksikan hasil UDF di SELECT daftar dan urutkan berdasarkan alias kolom atau ordinalnya, bukan memanggil UDF langsung di ORDER BY.
Untuk contoh penulisan ulang kueri, lihat Adaptasi kueri pemanggil untuk inlining UDF skalar.
Untuk batas eksekusi lainnya, lihat Batasan.
Periksa apakah UDF skalar dapat di-inlin
Tampilan sys.sql_modules katalog menjelaskan kelayakan berdasarkan definisi fungsi.
is_inlineable adalah 1 untuk definisi inlineable dan 0 lainnya.
inline_eligibility_mask mengidentifikasi teknik yang memenuhi syarat:
inline_eligibility_mask |
Kelayakan definisi |
|---|---|
0 |
Tidak ada teknik yang sama |
1 |
Inlining UDF skalar |
2 |
Inlining Blok Ekspresi |
3 |
Kedua teknik tersebut |
Nilai-nilai ini menggambarkan definisi fungsi, bukan rencana eksekusi dari kueri tertentu.
Gunakan contoh query berikut untuk memeriksa apakah UDF skalar dapat diinline:
SELECT
SCHEMA_NAME(b.schema_id) AS function_schema_name,
b.name AS function_name,
b.type_desc AS function_type,
a.is_inlineable,
a.inline_eligibility_mask
FROM sys.sql_modules AS a
INNER JOIN sys.objects AS b
ON a.object_id = b.object_id
WHERE b.type IN ('FN');
Batasan
- Memanggil UDF skalar dalam konteks query yang tidak didukung dapat mengembalikan kesalahan
Scalar UDF execution is currently unavailable in this context. - Query yang bergantung pada inlining UDF Scalar dapat gagal jika berisi lebih dari 10 panggilan UDF.
Contoh
J. Membuat fungsi bernilai tabel sebaris
Contoh berikut membuat fungsi bernilai tabel sebaris yang mengembalikan informasi kunci pada modul, memfilter berdasarkan objectType parameter. Ini termasuk nilai default untuk mengembalikan semua modul saat Anda memanggil fungsi 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',
sm.is_inlineable AS 'Inlineable'
FROM sys.sql_modules AS sm
INNER JOIN sys.objects AS o ON sm.object_id = o.object_id
WHERE o.type LIKE '%' + @objectType + '%'
);
GO
Panggil fungsi untuk mengembalikan semua fungsi bernilai tabel sebaris (IF):
SELECT * FROM dbo.ModulesByType('IF'); -- SQL_INLINE_TABLE_VALUED_FUNCTION
Atau, temukan semua fungsi skalar (FN):
SELECT * FROM dbo.ModulesByType('FN'); -- SQL_SCALAR_FUNCTION
B. Menggabungkan hasil fungsi bernilai tabel sebaris
Contoh sederhana ini menggunakan TVF sebaris yang dibuat sebelumnya untuk menunjukkan bagaimana Anda dapat menggabungkan hasilnya dengan tabel lain dengan menggunakan CROSS APPLY. Di sini, Anda memilih semua kolom dari keduanya sys.objects dan hasilnya ModulesByType untuk semua baris yang cocok di type kolom. Untuk informasi selengkapnya tentang menggunakan APPLY, lihat klausa FROM ditambah JOIN, APPLY, PIVOT (Transact-SQL).
SELECT *
FROM sys.objects AS o
CROSS APPLY dbo.ModulesByType(o.type);
GO
C. Membuat fungsi UDF skalar
Contoh berikut membuat UDF skalar sebaris yang menutupi teks input.
CREATE OR ALTER FUNCTION [dbo].[cleanInput] (@InputString VARCHAR(100))
RETURNS VARCHAR(50)
AS
BEGIN
DECLARE @Result VARCHAR(50);
DECLARE @CleanedInput VARCHAR(50);
-- Trim whitespace
SET @CleanedInput = LTRIM(RTRIM(@InputString));
-- Handle empty or null input
IF @CleanedInput = '' OR @CleanedInput IS NULL
BEGIN
SET @Result = '';
END
ELSE IF LEN(@CleanedInput) <= 2
BEGIN
-- If string length is 1 or 2, just return the cleaned string
SET @Result = @CleanedInput;
END
ELSE
BEGIN
-- Construct the masked string
SET @Result =
LEFT(@CleanedInput, 1) +
REPLICATE('*', LEN(@CleanedInput) - 2) +
RIGHT(@CleanedInput, 1);
END
RETURN @Result
END
Anda dapat memanggil fungsi seperti ini:
DECLARE @input varchar(100) = '123456789';
SELECT dbo.cleanInput (@input) AS function_output;
Contoh selengkapnya tentang bagaimana Anda dapat menggunakan UDF skalar di Fabric Data Warehouse:
Dalam pernyataan SELECT :
SELECT TOP 10
t.id, t.name,
dbo.cleanInput (t.name) AS function_output
FROM dbo.MyTable AS t;
WHERE Dalam klausa:
SELECT t.id, t.name, dbo.cleanInput(t.name) AS function_output
FROM dbo.MyTable AS t
WHERE dbo.cleanInput(t.name) = '1*******9';
JOIN Dalam klausa:
SELECT t1.id, t1.name,
dbo.cleanInput (t1.name) AS function_output,
dbo.cleanInput (t2.name) AS function_output_2
FROM dbo.MyTable1 AS t1
INNER JOIN dbo.MyTable2 AS t2
ON dbo.cleanInput(t1.name)=dbo.cleanInput(t2.name);
ORDER BY Dalam klausa:
SELECT t.id, t.name, dbo.cleanInput (t.name) AS function_output
FROM dbo.MyTable AS t
ORDER BY function_output;
Di Warehouse, Anda juga dapat memanggil UDF skalar dari pernyataan manipulasi data seperti INSERT, UPDATE, atau DELETE. Contoh modifikasi data ini tidak berlaku untuk endpoint SQL analytics.
SELECT t.id, t.name, dbo.cleanInput (t.name) AS function_output
INTO dbo.MyTable_new
FROM dbo.MyTable AS t;
UPDATE t
SET t.mycolumn_new = dbo.cleanInput (t.name)
FROM dbo.MyTable AS t;
DELETE t
FROM dbo.MyTable AS t
WHERE dbo.cleanInput(t.name) = '1*******9';
D. Buat fungsi eksternal
Contoh berikut membuat fungsi eksternal yang dinamai geohash dalam dbo skema yang merujuk pada geohash Fungsi Data Pengguna Fabric dalam spatial_functions set fungsi. Karena definisi parameter dan tipe return disimpulkan dari Fungsi Data Pengguna Fabric yang direferensikan, definisi tersebut tidak perlu ditentukan secara eksplisit dalam CREATE FUNCTION pernyataan.
CREATE FUNCTION dbo.geohash
AS EXTERNAL FUNCTION spatial_functions.geohash;
Setelah dibuat, fungsi eksternal dapat digunakan dalam kueri T-SQL seperti fungsi yang didefinisikan pengguna atau bawaan lainnya. Fungsi ini dapat dirujuk di mana pun pemanggilan fungsi didukung, memungkinkan integrasi mulus Fungsi Data Pengguna Fabric ke dalam kode T-SQL.
E. Buat UDF skalar untuk eksekusi mandiri
Pembuatan tabel dan pengaturan pemuatan data berikut memerlukan Warehouse. Pada endpoint SQL analytics, gunakan tabel yang sudah ada dengan sumber dengan kolom dan data yang diperlukan.
CREATE TABLE dbo.Customer
(
CustomerID INT NOT NULL,
CustomerName VARCHAR(100) NOT NULL,
CustomerType VARCHAR(20) NOT NULL,
MemberSince DATE NOT NULL,
YearToDateRevenue DECIMAL(18, 2) NOT NULL
);
GO
INSERT INTO dbo.Customer
(
CustomerID,
CustomerName,
CustomerType,
MemberSince,
YearToDateRevenue
)
VALUES
(1, 'Customer 1', 'Regular', '2020-01-15', 12000.00),
(2, 'Customer 2', 'Premium', '2022-06-01', 24000.00),
(3, 'Customer 3', 'VIP', '2024-03-10', 36000.00);
GO
Fungsi di bawah ini menggabungkan akses tabel dengan GETUTCDATE(). Akses tabel mencegah inlining Blok Ekspresi, dan GETUTCDATE() mencegah inlining UDF skalar.
Tanpa WITH INLINE = AUTO, definisi ditolak saat penciptaan atau perubahan.
Kesalahan tersebut melaporkan bahwa fungsi tidak dapat diinline dan memberikan alasan penolakan untuk kedua teknik tersebut. Tentukan WITH INLINE = AUTO untuk mengizinkan pembuatan:
CREATE OR ALTER FUNCTION dbo.GetCustomerTenureDays (@CustomerID INT)
RETURNS INT
WITH INLINE = AUTO
AS
BEGIN
DECLARE @MemberSince DATE;
SELECT @MemberSince = MemberSince
FROM dbo.Customer
WHERE CustomerID = @CustomerID;
RETURN DATEDIFF(DAY, @MemberSince, GETUTCDATE());
END;
GO
Fungsi dibuat tetapi tetap tidak inlineable: is_inlineable dan inline_eligibility_mask keduanya 0adalah . Panggilan mandiri berhasil:
DECLARE @udf_output int;
SET @udf_output = dbo.GetCustomerTenureDays(1);
SELECT @udf_output as 'udf_output';
Memanggil fungsi dalam kueri melalui tabel pengguna tidak didukung:
SELECT
CustomerID,
dbo.GetCustomerTenureDays(CustomerID) AS TenureDays
FROM dbo.Customer;
Kueri ini mengembalikan Scalar UDF execution is currently unavailable in this context.
Petunjuk / Saran
Untuk menggunakan logika ini dalam kueri pada tabel pengguna, evaluasi nilai waktu saat ini pada pemanggil dan berikan sebagai parameter. Untuk fungsi yang ditulis ulang dan kueri pemanggil, lihat Pass a time-dependent value into a data-access UDF.
Konten terkait
Berlaku untuk:Azure Synapse Analytics
Membuat fungsi yang didefinisikan pengguna (UDF) di Azure Synapse Analytics. Fungsi yang ditentukan pengguna adalah rutinitas Transact-SQL yang menerima parameter, melakukan tindakan, seperti perhitungan kompleks, dan mengembalikan hasil tindakan tersebut sebagai nilai. Fungsi bernilai tabel (TVF) yang ditentukan pengguna mengembalikan jenis data tabel.
Petunjuk / Saran
Untuk sintaks dalam Fabric Data Warehouse, lihat versi CREATE FUNCTION for Fabric Data Warehouse.
Di Azure Synapse Analytics,
CREATE FUNCTIONdapat mengembalikan tabel dengan menggunakan sintaks untuk fungsi bernilai tabel sebaris (pratinjau) atau dapat mengembalikan satu nilai dengan menggunakan sintaks untuk fungsi skalar.Di kumpulan SQL tanpa server di Azure Synapse Analytics,
CREATE FUNCTIONdapat membuat fungsi nilai tabel sebaris tetapi bukan fungsi skalar.Gunakan pernyataan ini untuk membuat rutinitas yang dapat digunakan kembali yang dapat Anda gunakan dengan cara berikut:
Dalam pernyataan Transact-SQL seperti
SELECTDalam 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)
-- Not available in the serverless SQL pools in Azure Synapse Analytics
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
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 mengikuti aturan untuk pengidentifikasi dan unik dalam database dan skemanya.
Catatan
Anda harus menyertakan tanda kurung setelah nama fungsi meskipun Anda tidak menentukan parameter.
@ parameter_name
Parameter dalam fungsi yang ditentukan pengguna. Anda dapat mendeklarasikan satu atau beberapa parameter.
Sebuah fungsi dapat memiliki hingga 2.100 parameter. Ketika pengguna atau aplikasi memanggil fungsi, Anda harus memberikan nilai untuk setiap parameter yang dideklarasikan kecuali Anda menentukan nilai default untuk parameter tersebut.
Tentukan nama parameter dengan menggunakan tanda (@) sebagai karakter pertama. Nama parameter harus mengikuti aturan untuk pengidentifikasi. Parameter bersifat lokal untuk fungsi; Anda dapat menggunakan nama parameter yang sama di fungsi lain. Parameter hanya dapat menggantikan konstanta; Mereka tidak dapat digunakan sebagai pengganti nama tabel, nama kolom, atau nama objek database lainnya.
Catatan
ANSI_WARNINGS tidak dihormati saat Anda mengirim parameter dalam prosedur tersimpan, fungsi yang didefinisikan pengguna, atau ketika Anda mendeklarasikan dan mengatur variabel dalam pernyataan batch. Misalnya, jika Anda mendefinisikan variabel sebagai char(3), lalu mengaturnya ke nilai yang lebih besar dari tiga karakter, data dipotong ke ukuran yang ditentukan dan INSERT pernyataan atau UPDATE berhasil.
parameter_data_type
Jenis data parameter. Untuk fungsi Transact-SQL, semua jenis data skalar yang didukung di Azure Synapse Analytics diizinkan. Tipe data stempel waktu (rowversion) bukanlah jenis yang didukung.
[ = default ]
Nilai default untuk parameter. Jika Anda menentukan nilai default , Anda dapat menjalankan fungsi tanpa menentukan nilai untuk parameter tersebut.
Ketika parameter fungsi memiliki nilai default, Anda harus menentukan kata kunci DEFAULT saat memanggil fungsi 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
Nilai yang dikembalikan dari fungsi skalar yang ditentukan pengguna. Untuk fungsi Transact-SQL, semua jenis data skalar yang didukung di Azure Synapse Analytics diizinkan. Tipe datastempel waktu/ bukanlah jenis yang didukung. Jenis nonskalar kursor dan tabel tidak diizinkan.
function_body
Serangkaian pernyataan Transact-SQL.
function_body tidak dapat berisi SELECT pernyataan dan tidak dapat mereferensikan data database.
function_body tidak dapat mereferensikan tabel atau tampilan. Badan 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
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. Anda hanya dapat meneruskan konstanta dan @local_variables ke TVF.
Dalam TVF sebaris (pratinjau), Anda menentukan TABLE nilai yang ditampilkan 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. Saat menentukan SCHEMABINDING, Anda tidak dapat memodifikasi objek yang mendasarinya (seperti tampilan atau tabel, misalnya) dengan cara yang memengaruhi definisi fungsi. Anda harus terlebih dahulu memodifikasi atau menghapus definisi fungsi untuk menghapus dependensi pada objek yang ingin Anda modifikasi.
Pengikatan fungsi ke objek yang dirujuknya dihapus hanya ketika salah satu tindakan berikut terjadi:
Anda menjatuhkan fungsinya.
Anda
ALTERpernyataan fungsi dan hapus opsi.SCHEMABINDING
Anda hanya dapat mengikat skema fungsi jika kondisi berikut benar:
Setiap fungsi yang ditentukan pengguna yang direferensikan fungsi juga terikat skema.
Referensi fungsi menggunakan nama satu bagian atau dua bagian.
Di dalam isi UDF, Anda hanya dapat mereferensikan fungsi bawaan dan UDF lainnya dalam database yang sama.
Pengguna yang menjalankan
CREATE FUNCTIONpernyataan memiliki izin REFERENSI pada objek database yang direferensikan fungsi.
Untuk menghapus SCHEMABINDING, gunakan ALTER.
MENGEMBALIKAN NULL PADA INPUT NULL | DIPANGGIL PADA INPUT NULL
OnNULLCall Menentukan atribut fungsi bernilai skalar. Jika Anda tidak menentukan atribut ini, CALLED ON NULL INPUT tersirat secara default, dan isi fungsi dieksekusi meskipun NULL diteruskan sebagai argumen.
Praktik terbaik
Jika Anda tidak membuat fungsi yang ditentukan pengguna dengan klausa SCHEMABINDING, perubahan pada objek yang mendasarinya dapat memengaruhi definisi fungsi dan menyebabkan hasil yang tidak terduga saat Anda memanggilnya. Tentukan WITH SCHEMABINDING klausa saat Anda membuat fungsi. Klausa ini memastikan bahwa Anda tidak dapat memodifikasi objek yang direferensikan dalam definisi fungsi kecuali Anda juga memodifikasi fungsi.
Interoperabilitas
Pernyataan berikut valid dalam fungsi bernilai skalar:
Pernyataan penugasan.
Pernyataan Control-of-Flow, kecuali TRY... CATCH.
DECLARE yang menentukan variabel data lokal.
Dalam fungsi bernilai tabel sebaris (pratinjau), Anda hanya dapat menggunakan satu pernyataan pilih.
Batasan
Anda tidak dapat menggunakan fungsi yang ditentukan pengguna untuk melakukan tindakan yang memodifikasi status database.
Anda dapat menyarangkan fungsi yang ditentukan pengguna. Satu fungsi yang ditentukan pengguna dapat memanggil fungsi lain. Tingkat bersarang meningkat saat fungsi yang dipanggil memulai eksekusi, dan berkurang saat fungsi yang dipanggil selesai dieksekusi. Jika Anda melebihi tingkat maksimum bersarang, seluruh rantai fungsi pemanggilan gagal.
Anda tidak dapat membuat objek, termasuk fungsi, dalam master database kumpulan SQL nirserver Anda di Azure Synapse Analytics.
Metainformasi
Bagian ini mencantumkan tampilan katalog sistem yang dapat Anda gunakan untuk mengembalikan metadata tentang fungsi yang ditentukan pengguna.
sys.sql_modules: Menampilkan definisi fungsi Transact-SQL yang ditentukan pengguna. 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 didefinisikan dalam fungsi yang ditentukan pengguna.
sys.sql_expression_dependencies: Menampilkan objek dasar yang direferensikan oleh fungsi.
Izin
Memerlukan CREATE FUNCTION izin dalam database dan izin ALTER pada skema tempat fungsi dibuat.
Contoh
J. Menggunakan fungsi bernilai skalar yang ditentukan pengguna untuk mengubah jenis data
Fungsi sederhana ini mengambil tipe data int sebagai input, dan mengembalikan tipe 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.
B. Membuat fungsi bernilai tabel sebaris
Contoh berikut membuat fungsi bernilai tabel sebaris yang mengembalikan informasi kunci pada modul, memfilter berdasarkan objectType parameter. Ini termasuk nilai default untuk mengembalikan semua modul saat Anda memanggil fungsi 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
Anda dapat memanggil fungsi untuk mengembalikan semua objek view (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.
C. Menggabungkan hasil fungsi bernilai tabel sebaris
Contoh sederhana ini menggunakan TVF sebaris yang dibuat sebelumnya untuk menunjukkan bagaimana Anda dapat menggabungkan hasilnya dengan tabel lain dengan menggunakan CROSS APPLY. Dalam contoh ini, Anda memilih semua kolom dari keduanya sys.objects dan hasilnya ModulesByType untuk semua baris yang cocok di type kolom. Untuk informasi selengkapnya tentang menggunakan APPLY, lihat klausa FROM ditambah JOIN, APPLY, PIVOT (Transact-SQL).
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.