Onur SALKAYA (Matematik Mühendisi)

Adından da anlaşıldığı gibi, geriye tablo dönen fonksiyonlardır. Sorgu içerisinde kullanılabildikleri gibi, FROM anahtar sözcüğünden sonra referans olarak da verilebilirler. Table-valued Function’lar, InLine ve MultiLine olmak üzere ikiye ayrılırlar.

IN-LINE

Parametre alırlar. Üzerinde insert, update ve delete işlemleri yapılabilir. Geriye döndürdüğü tablo tip(kolon sayısı,boyutu vs) olarak, sorgu içerisinde return edilen tablo ile birebir aynıdır.

AdventurWorks veritabanında, SubCategoryID’si 1 olan ürünleri listelemek isteyelim. Bunun için bir view yazmamız yeterlidir.

CREATE VIEW VW_ProductBySubCatId_I

AS

      SELECT * FROM Production.Product

      WHERE ProductSubcategoryID=1

Şimdi de SubCategoryID’si 2 olan ürünleri listelemek isteyelim. Bunun için ayrı bir view yazmamız gerekecek. Çünkü view’ler parametre alamazlar.

CREATE VIEW VW_ProductBySubCatId_II

AS

      SELECT * FROM Production.Product

      WHERE ProductSubcategoryID=2

Başka bir SubCategoryID’ye göre listelemek istersek tekrar tekrar view yazmamız gerekecek. Ancak bütün bunların yerine, parametre olarak SubCategoryID alan ve geriye tablo dönen bir fonksiyon yazmamız bizim için daha iyi bir çözüm olacaktır.

CREATE FUNCTION FN_GetProductBySubCat(@subCatId INT)

RETURNS TABLE

AS

      RETURN

            SELECT * FROM Production.Product

            WHERE ProductSubcategoryID=@subCatId

SubCategoryID’si 1 olan ürünleri elde etmek için,

SELECT * FROM FN_GetProductBySubCat(1)

Production.Product tablosunun tüm kolonlarını istediğimiz filtreleme işlemine göre elde ettik. Yani elimizde sadece SubCategoryID’si 1 olan ürünlerin satırları mevcut. Sonuc tablomuz tip olarak, Productin.Product tablosu ile birebir aynı.

MULTI-LINE

Returns kısmında ‘TABLE’ yazmak yerine, manuel olarak tablo oluşturup, sonuçları bu tabloya insert ederiz.

Yukarıdaki aynı örneği multi-line function ile yapalım. Buradaki fark; geri dönecek olan tabloyu manuel olarak set edebilmemizdir. Ayrıca fonksiyon içerisinde extra kodlamalar da yapabiliriz.(cursor kullanmak gibi…)

CREATE FUNCTION FN_GetProductBySubCat_Multi(@subCatId INT)

RETURNS @TABLO TABLE

(

      ProductId INT,

      Name NVARCHAR(50),

      ListPrice MONEY,

      Color NVARCHAR(100)

)

AS

      BEGIN

            INSERT INTO @TABLO

                  SELECT ProductID,Name,ListPrice,Color FROM Production.Product

                  WHERE ProductSubcategoryID=@subCatId

            RETURN

      END

Artık sonuç tablomuzda sadece Id,Ad,Fiyat ve Renk bilgisi kolonları bulunacak.

SELECT * FROM FN_GetProductBySubCat_Multi(1)

Aldığımız sonuç in-line fonksiyon yazdığımızdaki ile aynı. Ancak orada, sonuç kümesindeki kolon sayısı Production.Product tablosundaki kolon sayısı ile aynıdır. Yani sonuç kümemize dahil etmek istemediğimiz kolonları da elde etmiş olduk. Multi-line daki fark ise , kolonları manuel olarak oluşturabilmemizdir. Dolayısı ile elimizdeki toplam kolon sayısı 4’tür ve sonuç kümemize istemediğimiz kolonları dahil etmemiş olduk.

Şimdi başka bir örnek yapalım. Person.Contact tablosunda, kişilere ait iletişim bilgileri tutulmaktadır. Yazacağımız fonksiyon parametre olarak metinsel bir ifade alsın. Eğer parametre ‘UZUN’ ise, kişinin tam adını (ünvan+ad+soyad), mail adresini ve telefon bilgisini dönsün. Eğer ‘KISA’ ise, sadece soyadını, mail adresini ve telefon bilgisini dönsün.

CREATE FUNCTION FN_GetContactInformation(@Len VARCHAR(10))

RETURNS @TABLO TABLE

(

      Name NVARCHAR(100),

      Email NVARCHAR(50),

      Phone NVARCHAR(25)

)

AS

      BEGIN

            IF @Len = 'UZUN'

            BEGIN

                  INSERT INTO @TABLO

                        SELECT ISNULL(Title,'') + ' ' + FirstName + ' ' + ISNULL(MiddleName,'') + ' ' + LastName,EmailAddress,Phone  FROM Person.Contact

            END

            ELSE IF @Len = 'KISA'

            BEGIN

                  INSERT INTO @TABLO

                        SELECT LastName,EmailAddress,Phone FROM Person.Contact

            END

      RETURN

      END

Şimdi de istediğimiz parametreleri verelim ve sonuçları görelim.

SELECT * FROM FN_GetContactInformation('UZUN')

SELECT * FROM FN_GetContactInformation('KISA')

Parametre olarak bu iki metini dışında bir değer girilirse, sonuç kümemiz boş bir tablo olacaktır.