Membuat fungsi yang ditentukan pengguna (Mesin Database)

Berlaku untuk:SQL Server Database Azure SQLInstans Terkelola Azure SQLAzure Synapse Analytics Sistem Platform Analitik (PDW)Database SQL di Microsoft Fabric

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

Keterbatasan

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, , @ERRORatau 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 (misalnya, SET NOCOUNT ON;). Penetapan nilai variabel dapat menggunakan SET.

Klausul FOR XML tidak diizinkan.

Fungsi yang ditentukan pengguna berlapis

Fungsi yang ditentukan pengguna dapat ditumpuk. Artinya, satu fungsi yang ditentukan pengguna dapat memanggil fungsi lain. Tingkat perapatan bertambah ketika fungsi yang dipanggil mulai eksekusi, dan berkurang ketika fungsi yang dipanggil selesai.

Fungsi yang ditentukan pengguna dapat ditumpuk hingga 32 tingkat. Melebihi tingkatan maksimum bersarang menyebabkan seluruh rantai fungsi fungsional 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

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

Fungsi efek samping

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

NEWID
NEWSEQUENTIALID
RAND
TEXTPTR

Jika Anda mereferensikan salah satu fungsi ini di dalam UDF, Anda mendapatkan kesalahan berikut:

Msg 443, Level 16, State 1
Invalid use of a side-effecting operator <operator> within a function.

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

Untuk mengatasi masalah ini, Anda dapat membungkus fungsi yang memiliki efek samping dengan sebuah tampilan. Kemudian, panggil tampilan tersebut dari dalam suatu fungsi.

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 AdventureWorks2025. 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 AS INT;
    SELECT @ret = SUM(p.Quantity)
    FROM Production.ProductInventory AS 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 AdventureWorks2025. Fungsi ini mengambil satu parameter input, ID pelanggan (toko), dan mengembalikan kolom ProductID, Name dan agregat penjualan hingga saat ini sebagai 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
          INNER JOIN Sales.SalesOrderDetail AS SD
              ON SD.ProductID = P.ProductID
          INNER JOIN Sales.SalesOrderHeader AS SH
              ON SH.SalesOrderID = SD.SalesOrderID
          INNER 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 multi-pernyataan bernilai tabel (MSTVF)

Contoh berikut membuat fungsi bernilai tabel multi-pernyataan (MSTVF) dalam database AdventureWorks2025. 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 INT)
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 (
        -- Get the initial list of Employees for Manager n
        SELECT e.BusinessEntityID,
               e.OrganizationNode,
               p.FirstName,
               p.LastName,
               e.JobTitle,
               0
        FROM HumanResources.Employee AS e
             INNER JOIN Person.Person AS 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 AS e
             INNER JOIN EMP_cte
                 ON e.OrganizationNode.GetAncestor(1) = EMP_cte.OrganizationNode
             INNER JOIN Person.Person AS 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 lebih lanjut dan contoh fungsi bernilai tabel dalam satu baris (TVF dalam satu baris) serta 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:

Tentukan klausa WITH SCHEMABINDING 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 opsi SCHEMABINDING untuk mencegah optimisasi kueri menghasilkan operator spool yang tidak perlu untuk rencana kueri yang melibatkan UDF ini. Untuk informasi lebih lanjut tentang spool, lihat Referensi operator showplan logis dan fisik. 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 yang lebih lama.

Dalam SQL Server 2017 (14.x) dan versi yang lebih baru, mengoptimalkan rencana eksekusi yang menggunakan MSTVF dapat dilaksanakan dengan eksekusi berselang-seling, yang mengakibatkan penggunaan kardinalitas aktual alih-alih heuristik yang disebutkan sebelumnya.

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 char(3), lalu diatur ke nilai yang lebih besar dari tiga karakter, data dipotong sesuai ukuran yang ditentukan, dan pernyataan INSERT atau UPDATE berhasil dieksekusi.

Saran dan Komentar

Apakah halaman ini membantu?

Last updated on 2025-11-18