Bagikan melalui

Membuat fungsi yang ditentukan pengguna (Mesin Database)

  • Artikel

Berlaku untuk: SQL Server Azure SQL Database Azure SQL Managed Instance Azure Synapse Analytics Analytics Platform System (PDW)

Artikel ini menjelaskan cara membuat fungsi yang ditentukan pengguna (UDF) di SQL Server dengan menggunakan Transact-SQL.

Pembatasan dan batasan

  • Fungsi yang ditentukan pengguna tidak dapat digunakan untuk melakukan tindakan yang mengubah status database.

  • Fungsi yang ditentukan pengguna tidak boleh berisi OUTPUT INTO klausul yang memiliki tabel sebagai targetnya.

  • Fungsi yang ditentukan pengguna tidak dapat mengembalikan beberapa kumpulan hasil. Gunakan prosedur tersimpan jika Anda perlu mengembalikan beberapa kumpulan hasil.

  • Penanganan kesalahan dibatasi dalam fungsi yang ditentukan pengguna. UDF tidak mendukung TRY...CATCH, @ERROR atau RAISERROR.

  • Fungsi yang ditentukan pengguna tidak dapat memanggil prosedur tersimpan, tetapi dapat memanggil prosedur tersimpan yang diperluas.

  • Fungsi yang ditentukan pengguna tidak dapat menggunakan tabel SQL dinamis atau temp. Variabel tabel diperbolehkan.

  • SET pernyataan tidak diizinkan dalam fungsi yang ditentukan pengguna.

  • Klausul FOR XML tidak diizinkan.

  • 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. Referensi apa pun ke kode terkelola dari fungsi yang ditentukan pengguna Transact-SQL dihitung sebagai satu tingkat terhadap batas berlapis 32 tingkat. Metode yang dipanggil dari dalam kode terkelola tidak dihitung terhadap batas ini.

  • Pernyataan Service Broker berikut tidak dapat disertakan dalam definisi fungsi yang ditentukan pengguna Transact-SQL:

    • BEGIN DIALOG CONVERSATION
    • END CONVERSATION
    • GET CONVERSATION GROUP
    • MOVE CONVERSATION
    • RECEIVE
    • SEND

Izin

CREATE FUNCTION Memerlukan izin dalam database dan ALTER izin pada skema tempat fungsi sedang dibuat. Jika fungsi menentukan jenis yang ditentukan pengguna, memerlukan EXECUTE izin pada jenis tersebut.

Contoh fungsi bernilai skalar

Fungsi skalar (UDF skalar)

Contoh berikut membuat fungsi skalar multi-pernyataan (UDF skalar) dalam database AdventureWorks2022. Fungsi ini mengambil satu nilai input, ProductID, dan mengembalikan satu nilai data, jumlah agregat dari produk yang ditentukan dalam inventori.

IF OBJECT_ID (N'dbo.ufnGetInventoryStock', N'FN') IS NOT NULL
    DROP FUNCTION ufnGetInventoryStock;
GO
CREATE FUNCTION dbo.ufnGetInventoryStock(@ProductID int)
RETURNS int
AS
-- Returns the stock level for the product.
BEGIN
    DECLARE @ret int;
    SELECT @ret = SUM(p.Quantity)
    FROM Production.ProductInventory p
    WHERE p.ProductID = @ProductID
        AND p.LocationID = '6';
     IF (@ret IS NULL)
        SET @ret = 0;
    RETURN @ret;
END;

Contoh berikut menggunakan ufnGetInventoryStock fungsi untuk mengembalikan kuantitas inventori saat ini untuk produk yang memiliki ProductModelID antara 75 dan 80.

SELECT ProductModelID, Name, dbo.ufnGetInventoryStock(ProductID)AS CurrentSupply
FROM Production.Product
WHERE ProductModelID BETWEEN 75 and 80;

Untuk informasi selengkapnya dan contoh fungsi skalar, lihat CREATE FUNCTION.

Contoh fungsi bernilai tabel

Fungsi bernilai tabel sebaris (TVF)

Contoh berikut membuat fungsi bernilai tabel sebaris (TVF) dalam database AdventureWorks2022. Fungsi ini mengambil satu parameter input, ID pelanggan (toko), dan mengembalikan kolom ProductID, , Namedan agregat penjualan tahun ke tanggal seperti YTD Total untuk setiap produk yang dijual ke toko.

IF OBJECT_ID (N'Sales.ufn_SalesByStore', N'IF') IS NOT NULL
    DROP FUNCTION Sales.ufn_SalesByStore;
GO
CREATE FUNCTION Sales.ufn_SalesByStore (@storeid int)
RETURNS TABLE
AS
RETURN
(
    SELECT P.ProductID, P.Name, SUM(SD.LineTotal) AS 'Total'
    FROM Production.Product AS P
    JOIN Sales.SalesOrderDetail AS SD ON SD.ProductID = P.ProductID
    JOIN Sales.SalesOrderHeader AS SH ON SH.SalesOrderID = SD.SalesOrderID
    JOIN Sales.Customer AS C ON SH.CustomerID = C.CustomerID
    WHERE C.StoreID = @storeid
    GROUP BY P.ProductID, P.Name
);

Contoh berikut memanggil fungsi dan menentukan ID pelanggan 602.

SELECT * FROM Sales.ufn_SalesByStore (602);

Fungsi bernilai tabel multi-pernyataan (MSTVF)

Contoh berikut membuat fungsi bernilai tabel multi-pernyataan (MSTVF) dalam database AdventureWorks2022. Fungsi ini mengambil parameter input tunggal, EmployeeID dan mengembalikan daftar semua karyawan yang melapor ke karyawan yang ditentukan secara langsung atau tidak langsung. Fungsi ini kemudian dipanggil menentukan ID karyawan 109.

IF OBJECT_ID (N'dbo.ufn_FindReports', N'TF') IS NOT NULL
    DROP FUNCTION dbo.ufn_FindReports;
GO
CREATE FUNCTION dbo.ufn_FindReports (@InEmpID INTEGER)
RETURNS @retFindReports TABLE
(
    EmployeeID int primary key NOT NULL,
    FirstName nvarchar(255) NOT NULL,
    LastName nvarchar(255) NOT NULL,
    JobTitle nvarchar(50) NOT NULL,
    RecursionLevel int NOT NULL
)
--Returns a result set that lists all the employees who report to the
--specific employee directly or indirectly.*/
AS
BEGIN
WITH EMP_cte(EmployeeID, OrganizationNode, FirstName, LastName, JobTitle, RecursionLevel) -- CTE name and columns
    AS (
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, 0 -- Get the initial list of Employees for Manager n
        FROM HumanResources.Employee e
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
        WHERE e.BusinessEntityID = @InEmpID
        UNION ALL
        SELECT e.BusinessEntityID, e.OrganizationNode, p.FirstName, p.LastName, e.JobTitle, RecursionLevel + 1 -- Join recursive member to anchor
        FROM HumanResources.Employee e
            INNER JOIN EMP_cte
            ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
INNER JOIN Person.Person p
ON p.BusinessEntityID = e.BusinessEntityID
        )
-- copy the required columns to the result of the function
   INSERT @retFindReports
   SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
   FROM EMP_cte
   RETURN
END;
GO

Contoh berikut memanggil fungsi dan menentukan ID karyawan 1.

SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel
FROM dbo.ufn_FindReports(1);

Untuk informasi selengkapnya dan contoh fungsi bernilai tabel sebaris (TVF sebaris) dan fungsi bernilai tabel multi-pernyataan (MSTVF), lihat CREATE FUNCTION.

Praktik terbaik

Jika fungsi yang ditentukan pengguna (UDF) tidak dibuat dengan SCHEMABINDING klausul, 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 UDF. Ini memastikan bahwa objek yang dirujuk dalam definisi fungsi tidak dapat dimodifikasi kecuali fungsi juga dimodifikasi.

  • Jalankan prosedur tersimpan sp_refreshsqlmodule setelah memodifikasi objek apa pun yang ditentukan dalam definisi UDF.

Jika membuat UDF yang tidak mengakses data, tentukan SCHEMABINDING opsi untuk mencegah pengoptimal kueri menghasilkan operator penampung yang tidak perlu untuk rencana kueri yang melibatkan UDF ini. Untuk informasi selengkapnya tentang penampung, lihat Referensi Operator Logis dan Fisik Showplan. Untuk informasi selengkapnya tentang membuat fungsi terikat skema, lihat Fungsi terikat skema.

Bergabung ke MSTVF dalam FROM klausul dimungkinkan, tetapi dapat mengakibatkan performa yang buruk. SQL Server tidak dapat menggunakan semua teknik yang dioptimalkan pada beberapa pernyataan yang dapat disertakan dalam MSTVF, menghasilkan rencana kueri suboptimal. Untuk mendapatkan performa terbaik, jika memungkinkan, gunakan gabungan antara tabel dasar alih-alih fungsi.

MSTVF memiliki tebakan kardinalitas tetap 100 yang dimulai dengan SQL Server 2014 (12.x), dan 1 untuk versi SQL Server sebelumnya.

Dimulai dengan SQL Server 2017 (14.x), mengoptimalkan rencana eksekusi yang menggunakan MSTVF dapat menggunakan eksekusi interleaved, yang menghasilkan penggunaan kardinalitas aktual alih-alih heuristik di atas.

Untuk informasi selengkapnya, lihat Eksekusi interleaved untuk fungsi bernilai tabel multi-pernyataan.

ANSI_WARNINGS tidak dihormati saat Anda meneruskan parameter dalam prosedur tersimpan, fungsi yang ditentukan pengguna, atau saat 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 INSERT pernyataan atau UPDATE berhasil.

Konten terkait