Bagikan melalui

Membuat Fungsi yang Ditentukan Pengguna (Mesin Database)

Topik ini menjelaskan cara membuat fungsi yang ditentukan pengguna di SQL Server dengan menggunakan Transact-SQL.

Dalam Topik Ini

Sebelum Anda mulai

Batasan dan Pembatasan

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

  • Fungsi yang ditentukan pengguna tidak boleh berisi klausa OUTPUT INTO 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.

  • Pernyataan SET tidak diizinkan dalam fungsi yang ditentukan pengguna.

  • Klausa FOR XML tidak diperbolehkan

  • 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 berikut tidak dapat disertakan dalam definisi fungsi Transact-SQL yang ditentukan pengguna:

    • MULAILAH PERCAKAPAN

    • AKHIRI PERCAKAPAN

    • TEMUKAN GRUP PERCAKAPAN

    • PINDAHKAN PERCAKAPAN

    • MENERIMA

    • KIRIM

Keamanan

Hak akses

Memerlukan izin CREATE FUNCTION dalam database dan izin UBAH pada skema tempat fungsi sedang dibuat. Jika fungsi menentukan tipe yang didefinisikan pengguna, memerlukan izin EXECUTE pada tipe itu.

Fungsi Skalar

Contoh berikut membuat fungsi skalar multistatement dalam database AdventureWorks2012. 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;  
GO

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;

Table-Valued Fungsi

Contoh berikut membuat fungsi bernilai tabel sebaris dalam database AdventureWorks2012. 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   
    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);

Contoh berikut membuat fungsi bernilai tabel dalam database AdventureWorks2012. 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  
-- Example invocation  
SELECT EmployeeID, FirstName, LastName, JobTitle, RecursionLevel  
FROM dbo.ufn_FindReports(1);

Lihat Juga

Fungsi yang Ditentukan Pengguna
CREATE FUNCTION (Transact-SQL)

  • Last updated on