MS SQL 'de User Defined Functions-(3) Multistatement Table Valued Functions

Merhaba arkadaşlar.
"In Line Table Valued" fonksiyonlar geriye bir tablo döner  ve gövdesinde sadece "select" sorguları kullanılır demiştik. "User Defined" fonksiyonların sonuncusu olan "Multistatement Table Valued" (çoklu durum tablo değerli) fonksiyonları da geriye bir tablo döner. Fakat adından da anlaşılacağı üzere tanımlanırken gövdesinde DML dilinin tüm sorgularını (select, insert, update, delete) tümünü kullanabilir. Bunu yaparken de gövdesinin "begin-end" skopları arasında olması şarttır. "RETURNS" ifadesinden sonra geriye döndüreceği geçici bir tablo tanımlanır, tip olarak da "TABLE" tipi belirtilir. Bu yüzden en sonda "RETURN" 'den sonra bir şey yazmama gerek kalmaz. Bu tablonun kolonları tipleriyle birlikte tanımlanır. Tanımladığım bu tablo remde geçici olarak tutulur.

Örneğin bir fonksiyonumuz olsun. Parametre olarak verdiğimiz "ad" yada "soyad" 'a göre "Employees" tablosundan veriler çeksin. "Select" ile çektiği veriler aynı zamanda belirttiğimiz geçici tabloma da "insert" etsin. Burada dikkat edilmesi gereken nokta tabloma ekleyeceğim verilerin, tablomun sütün sayısına ve sütunlarımın tiplerine uygun olması gerektiğidir. 

CREATE FUNCTION Kisilerim(@tip nvarchar(7))

RETURNS @Tablom TABLE

(Id INT,Isim NVARCHAR(50))

AS

BEGIN

    IF(@tip='ad')

        BEGIN

            INSERT @Tablom SELECT EmployeeID,FirstName FROM dbo.Employees

        END

    ELSE IF(@tip='adsoyad')

        BEGIN

            INSERT @Tablom SELECT EmployeeID,FirstName+' '+LastName AS Isim 

            FROM dbo.Employees

        END

    RETURN

END

Fonksiyonum "Object Explorer" penceresinde "Nortwind" veritabanının altında "Programmability", "Functions", "Table-valued Functions" sekmesi altında yerini aldı. Fonksiyonumu kullanırken "Scalar" fonksiyonlarımdaki gibi "dbo" kullanmama gerek yoktur. İki sütunluk bir tablo döndürmek istediğim için "select" ile çağırıp istediği kolonları içerisinden çekebilirim.

SELECT * FROM Kisilerim('ad')          SELECT * FROM Kisilerim('adsoyad')                             

Örneğin ben tabloma kayıt girmeye çalışsam veya bir kaydımı silmeye çalışırsam "Employees" tablomda bir değişiklik olur mu?

ALTER FUNCTION Kisilerim(@tip nvarchar(7))

RETURNS @Tablom TABLE

(Id INT,Isim NVARCHAR(50))

AS

BEGIN

    IF(@tip='ad')

    BEGIN

        INSERT @Tablom SELECT EmployeeID,FirstName FROM dbo.Employees

    END

    ELSE IF(@tip='adsoyad')

    BEGIN

        INSERT @Tablom SELECT EmployeeID,FirstName+' '+LastName AS Isim 

FROM dbo.Employees

        DELETE FROM @Tablom WHERE Id=11

    END

RETURN

END

Hayır arkadaşlar, "Employees" tablomda bir değişiklik olmaz.  Fonksiyonumun tanımını değiştirirken geçici tablom üzerinde bir silme işlemi yaptığımdan dolayı, tablomu "select" ettiğimde bilgileri sadece filtreleyerek gösterecektir. Aşağıda görüldüğü üzere orjinal bilgi "Employees" tablomdadır.

SELECT * FROM Kisilerim('adsoyad')        SELECT * FROM dbo.Employees

Başka bir yazıda görüşmek dileğiyle, hoşçakalın...

MS SQL 'de User Defined Functions-(1) Scalar Valued Functions

Merhaba arkadaşlar.
"User Defined Functions" lardan ilki "Scalar" fonksiyonlarını inceleyeceğiz. "User Defined Functions" Giriş makalemde genel olarak fonksiyonlardan bahsetmiştik. Fonksiyonların birer veritabanı nesneleri olduğundan, bu yüzden DDL ile (create, alter, drop) yönetildiğinden. Geriye mutlaka değer döndürdüklerinden, bu sebeple "return" ve "returns" anahtar sözcüklerini kullanmamız gerektiğinden söz etmiştik. Bu hatırlatmalardan  sonra "Scalar" fonksiyonları adından da anlaşılacağı üzere geriye tek bir değer dönen fonksiyonlardır. Bu sebeple "returns" ifadesinden sonra geriye dönen değerin tipi belirtilir. Hemen basit bir örnek üzerinden konuyu kavramaya çalışalım. Oluştururken "CREATE" 'den sonra, nesnenin ne olduğunu yani "FUNCTION" yazmalıyım. Daha sonra fonksiyonumun ismini yazıyorum.

CREATE FUNCTION BugununTarihi()

RETURNS DATETIME

AS

BEGIN

      DECLARE @Tarih DATETIME

      set @Tarih= GETDATE()

      RETURN @Tarih

END

Fonsiyonumu parametresiz tanımladım. Geriye bugünün tarihini döndüreceğim için " RETURNS " 'den sonra "DATETIME" tipini belirttim. "AS" 'den sonra fonksiyonumun gövdesini "BEGIN" ve "END" skoplarının arasında tanımlamalıyım. "RETURN" diyip geriye değerini döndürmek istediğim değişkenin adını yazıyorum. En son fonksiyonumu çalıştırdım (execute).

Fonksiyonum başarılı bir şekilde oluşturuldu. "Object Explorer" penceresinde, database 'imin altında "Programmability", "Functions", "Scalar-valued Functions" sekmesinin altında imiyle birlikte yer aldı. Şimdi sıra geldi çalıştırmaya. Geriye tek değer döneceğim için "from" kullanmama gerek yok. "Select" yada "print" ile çağırabilirim. "Select"  ile çağırırsam "result" tipinde, "print" olarak çağırırsam "message" tipinde dönecektir. Fakat fonksiyonumu ikinci kısmıyla (dbo) birlikte çağırmalıyım, "Database Owner" olduğumu belirtmem gerek. Çağırmazsam "böyle bir fonksiyon tanımlı değil" hatası ile karşılaşırım. Bunun için "Scalar" fonksiyonlar "dbo.fonksiyonAdi" ile çağırılır.

SELECT dbo.BugununTarihi()

PRINT dbo.BugununTarihi()

Örneğin bir tane de parametre alan bir fonksiyon tanımlayalım. "Northwind" veritabanını kullanarak, hangi kategoriden kaç adet ürün satıldığını görelim.

CREATE FUNCTION KatAdet(@KatId INT)

RETURNS INT

AS

BEGIN

      RETURN 

      (SELECT SUM(dbo.[Order Details].Quantity) 

       FROM dbo.[Order Details]

       INNER JOIN dbo.Products 

       ON dbo.Products.ProductID=dbo.[Order Details].ProductID

       WHERE dbo.Products.CategoryID=@KatId)   

END 

SELECT CategoryName,dbo.KatAdet(CategoryId) AS 'Toplam Miktar' FROM Categories

Son örnek olarak bir fonksiyonumuz olsun. Parametre olarak verdiğimiz tarihi, istediğimiz formatta ve yine istediğimiz ayracı ay, gün ve yılın arasında kullanarak bize geri dönsün. Verdiğimiz tarihteki ay ve gün değeri tek haneliyse başına "0" eklesin. Bunun için ay ve günün uzunluğunu almalıyım. Uzunluğun "1" ise "string methods" kullanarak "0" ile "1" rakamlarını birleştirmeliyim. 

create function dbo.tarihFormatla

(@tarih datetime,@ayrac nchar(1),@format nchar(3))

returns nchar(10)

as

begin

      declare @yil nchar(4) = year(@tarih)

      declare @ay nchar(2) = month(@tarih)

      declare @gun nchar(2) = day(@tarih)

      declare @formatliTarih nchar(10)

      if len(@gun) = 1

            set @gun = '0' + @gun

      if len(@ay) = 1

            set @ay = '0' + @ay

           

      if @format = 'dmy'

            set @formatliTarih = @gun + @ayrac + @ay + @ayrac + @yil

      else if @format = 'mdy'

          set @formatliTarih = @ay + @ayrac + @gun + @ayrac + @yil

      else if @format = 'ymd'

          set @formatliTarih = @yil + @ayrac + @ay + @ayrac + @gun

      return @formatliTarih

end

go

select dbo.tarihFormatla(getdate(),'/','ymd')

Başka bir yazıda görüşmek dileğiyle, hoşçakalın...