Kullanıcı tanımlı işlevler oluşturma

  • 5 dakika

Kullanıcı tanımlı işlevler (UDF), veritabanındaki tablolardan ayrı olarak depolandıkları saklı yordamlara benzer. Bu işlevler parametreleri kabul eder, bir eylem gerçekleştirir ve ardından eylem sonucunu tek bir (skaler) değer veya sonuç kümesi (tablo değerli) olarak döndürür. Ardından SELECT deyimi yazarken bir tablonun yerine işlevini kullanabilirsiniz. Kullanıcı tanımlı işlevler hesaplamalar yapmak ve bu sonucu başka bir deyim içinde kullanmak içindir. Saklı yordamlar işlevi ve deyimi kapsülleyebilir ve hatta veritabanındaki verileri değiştirebilir.

Üç tür kullanıcı tanımlı işlevi gözden geçireceğiz. Farklı işlevlerin diğer ayrıntıları için T-SQL başvuru belgelerini gözden geçirin.

Satır içi tablo değerli işlevler

Satır içi tablo değerli işlevler (TVF), SELECT deyimi temelinde oluşturulan en basit işlevdir ve performans için tercih edilen seçenektir.

Aşağıdaki örnekte unitprice için giriş parametresiyle tablo değerli bir işlev oluşturulur.

CREATE FUNCTION SalesLT.ProductsListPrice(@cost money)  
RETURNS TABLE  
AS  
RETURN  
    SELECT ProductID, Name, ListPrice  
    FROM SalesLT.Product  
    WHERE ListPrice > @cost;

Tablo değerli işlev parametresi için bir değerle çalıştırıldığında, birim fiyatı bu değerden daha fazla olan tüm ürünler döndürülür.

Aşağıdaki kod, tablo yerine tablo değerli işlevini kullanır.

SELECT Name, ListPrice  
FROM SalesLT.ProductsListPrice(500);

Çok deyimli tablo değerli fonksiyonlar

Satır içi TVF'den farklı olarak, çoklu ifadeli tablo değerli bir işlev (MSTVF), birden fazla ifadeye sahip olabilir ve söz dizimi gereksinimleri farklıdır.

Aşağıdaki kodda RETURN'e ek olarak BEGIN/END kullandığımıza dikkat edin:

CREATE FUNCTION Sales.mstvf_OrderStatus ()
RETURNS
@Results TABLE
     ( CustomerID int, OrderDate datetime )
AS
BEGIN
     INSERT INTO @Results
     SELECT SC.CustomerID, OrderDate
     FROM Sales.Customer AS SC
     INNER JOIN Sales.SalesOrderHeader AS SOH
        ON SC.CustomerID = SOH.CustomerID
     WHERE Status >= 5
 RETURN;
END;

Oluşturulduktan sonra, yukarıdaki önceki satır içi işlevinde olduğu gibi bir tablonun yerine MSTVF'ye başvurursunuz. Ayrıca FROM ifadesindeki çıktıya başvurabilir ve bunu diğer tablolarla birleştirebilirsiniz.

SELECT *
FROM Sales.mstvf_OrderStatus();

Performansla ilgili dikkat edilmesi gerekenler

Sorgu İyileştiricisi, çok deyimli tablo değerli bir işlev için kaç satır döndürüleceğini tahmin edemez, ancak satır içi tablo değerli işlevle bunu yapabilir. Bu nedenle, daha iyi performans için mümkün olduğunda satır içi TVF'yi kullanın. MSTVF'yi diğer tablolarla birleştirmeniz gerekmiyorsa ve/veya sonucun yalnızca birkaç satır olacağını biliyorsanız, performans etkisi o kadar da önemli değildir. Büyük bir sonuç kümesi bekliyorsanız ve diğer tablolarla birleştirmeniz gerekiyorsa, bunun yerine sonuçları depolamak için geçici tablo kullanmayı ve ardından geçici tabloya katılmayı göz önünde bulundurun.

MICROSOFT, SQL Server 2017 ve üzeri sürümlerde MSTVF performansını geliştirmek için akıllı sorgu işleme özellikleri kullanıma sunulmuştur. T-SQL Başvuru Belgeleri'nde Akıllı Sorgu İşleme özellikleri hakkında daha fazla ayrıntıya bakın.

Skaler kullanıcı tanımlı işlevler

Skaler kullanıcı tanımlı işlev, tablo değerli işlevlerden farklı olarak yalnızca bir değer döndürür ve bu nedenle genellikle basit, sık kullanılan deyimler için kullanılır.

Belirli bir günde belirli bir ürünün ürün listesi fiyatını alma örneği aşağıda verilmiştir:

CREATE FUNCTION dbo.ufn_GetProductListPrice
(@ProductID [int], @OrderDate [datetime])
RETURNS [money] 
AS 
BEGIN
    DECLARE @ListPrice money;
        SELECT @ListPrice = plph.[ListPrice]
        FROM [Production].[Product] p 
        INNER JOIN [Production].[ProductListPriceHistory] plph 
        ON p.[ProductID] = plph.[ProductID] 
            AND p.[ProductID] = @ProductID 
            AND StartDate = @OrderDate
    RETURN @ListPrice;
END;
GO

Bu işlev için, değeri almak için her iki parametre de sağlanmalıdır. İşleve bağlı olarak, işlevi SELECT deyiminde daha karmaşık bir sorguda listeleyebilirsiniz.

    SELECT dbo.ufn_GetProductListPrice (707, '2011-05-31')

Referans verilen nesnelere işlev bağlama

SCHEMABINDING, işlev oluşturulurken isteğe bağlıdır. SCHEMABINDING'i belirttiğinizde, işlevi referans verilen nesnelere bağlar, bu durumda da işlevi değiştirmeden nesneler üzerinde değişiklik yapılamaz. nesneyi değiştirmeden önce bağımlılıkları kaldırmak için işlevin değiştirilmesi veya bırakılması gerekir.

Aşağıdakilerden biri gerçekleşirse SCHEMABINDING kaldırılır:

  • İşlev bırakıldı
  • İşlev, SCHEMABINDING belirtilmeden ALTER deyimiyle değiştirilir