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 deyimini yazarken SELECT 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), bir SELECT deyim 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 öğesine ek olarak RETURNbir 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 yan tümcesindeki çıkışa FROM başvurabilir ve bunu diğer tablolarla birleştirebilirsiniz.

SELECT *
FROM Sales.mstvf_OrderStatus();

Performansla ilgili dikkat edilmesi gerekenler

Sorgu İyileştiricisi geçmişte çok deyimli tablo değerli işlevin kaç satır döndürdüğüne ilişkin tahminde bulundu. SQL Server 2017'den önce, altyapı sabit bir tahmin kullanıyordu ve bu da MSTVF'ler diğer tablolarla birleştirildiğinde genellikle zayıf sorgu planları üretti. İyileştirici bunları parametreli görünümler olarak değerlendirdiğinden satır içi TVF'lerde bu sınırlama yoktur.

SQL Server 2017'den (uyumluluk düzeyi 140 veya üzeri) başlayarak, Araya Eklemeli Yürütme bu boşluğu daraltıyor: altyapı asıl satır sayısını yakalamak için önce MSTVF alt ağacını yürütür, ardından iyileştirmeyi bu doğru tahminle sürdürür. Bu, diğer tablolarla birleştirilen MSTVF'ler için plan kalitesini önemli ölçüde artırır.

Interleaved Execution ile bile, iyileştirici birleştirilmiş sorguyu tamamen iyileştirebildiğinden satır içi TVF'ler mümkün olduğunda tercih edilen seçenek olmaya devam eder. MSTVF'leri gerçekten birden çok deyim veya ara değişken gerektiren mantık için ayırın. Ayrıntılar için bkz. MSTVF'ler için araya kaydedilen yürütme.

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, deyimindeki SELECT işlevi 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