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...

MS SQL 'de User Defined Functions-(2) In Line Table Valued Functions

Merhaba arkadaşlar.
"View" nesnelerini hatırlayacak olursak, kaydedilmiş "select" sorguları tanımını yapabiliriz. "In Line Table Valued" fonksiyonları bu bağlamda "view" nesnelerine çok benziyor. Çünkü "In Line Table Valued" fonksiyonlarında da sadece "select" sorguları kullanılabilir. Fakat "In Line Table Valued" fonksiyonları dışarıdan parametre alabilirler.

Bir tane "In Line Table Valued" fonksiyon tanımlayarak daha yakından inceleyelim. İstediğimiz kategorideki ürünlerimizi listeleyelim. KatUrunler adında bir "In Line Table Valued" fonksiyonu tanımlayalım.
NOT: Yeni başlayanların zorluk çekmemesi için, herkes tarafından bilinen "Northwind" veritabanı ile çalışacağım. Örneklerim de bu yönde olacak arkadaşlar.

CREATE FUNCTION KatUrunler(@CatId INT)

RETURNS TABLE

AS

      RETURN (SELECT * FROM dbo.Products WHERE CategoryID=@CatId)

"User Defined Functions" foksiyonları database nesneleri olduğundan dolayı, oluştururken DDL (Data Manipulation Language-Veri Yönetim Dili) dilindeki "create" anahtar sözcüğü ile tanımlanır. Daha sonra "function" ve ismi ne olacaksa o yazılır. parametre olarak t-sql dilinde değişken tanımlar gibi değişkenin ismi ve tipi belirtilir. "User Defined" fonksiyonlarında olmazsa olmaz "returns" ve "return" ifadeleridir. "Returns" diyerek geriye ne tipte bir değer dönecekse o belirtilir. "In Line Table Valued" ve "Multistatement Table Valued" fonksiyonlarında geriye "table" yani tablo döner demiştik. Bu yüzden "returns table" ifadesini kullandık. "Products" tablosunda "CategoryId" 'si, dışarıdan parametre olarak verdiğimiz id 'ye eşit olan ürünleri getiren, bir "select" sorgusu yazdık. Bu "select"sorgusunu da parantez içine alıp, başında "return" ifadesi kullanmamız gerekir. 

Fonksiyonumuzu tanımladık ama sisteme kayıt olabilmesi için tanımladığımız fonksiyonu "execute" etmeliyiz. Çalıştırdıktan sonra yüzümüzü güldüren ifadeyi (Query Executed Successfully-Sorgu başarılı bir şekilde çalıştı) gördük. Şimdi veritabanımıza kaydolmuş mu bir bakalım. "Object Explorer" penceresini bir "refresh" ettim

Fonksiyonum hazır, şimdi geldi tanımladığım fonksiyonumu kullanmaya. "View" 'lerden farkı parametre alması dedik, sadece "select" sorgusu kullanılır dedik, sorgu içerisinde de kullanılabilir dedik. O zaman arkadaşlar "select" ile çağırabilirim. Bu fonksiyonun çalışması sonucunda "Products" tablosunda, parametre olarak verdiğimiz "CategoryId" 'sinde birden fazla ürün gelecektir. Yani tablo gelecektir. Bu yüzden "select" sorgusu ile, "from" dan sonra çağırmalıyım. İçerisinden istediğim kolonları da çekebilirim. 

SELECT * FROM KatUrunler(1) 

yada

SELECT * FROM dbo.KatUrunler(1)

Parametre olarak 1 verdim. Result olarak tablo tipinde ürünlerim geldi. "Scalar" fonksiyonlardaki gibi fonksiyonun ikinci kısmıyla yazılmasına gerek yoktur. Ama fonksiyonları ikinci kısımlarıyla birlikte kullanma alışkanlığı kazanılmasında fayda var. 

Ben bu "select" ile birlikte kullandığım fonksiyon üzerinde istediğim gibi "where, group by, order by, vs.." sorgularıyla filtrelemeler yapabilirim. Bu da "User Defined" fonksiyonlarının sorgu içinde çalıştığına bir örnektir.

SELECT SupplierId,SUM(UnitPrice) 'Toplam Fiyat'

FROM KatUrunler(1)

WHERE unitsInStock >50

GROUP BY supplierId

ORDER BY 'Toplam Fiyat' DESC

Eğer fonksiyonumuz verilerini tek bir tablodan çeksin yada çekmesin önemi yok, fonksiyonumuz üzerinden o tabloya "insert, update, delete" işlemleri yapabiliriz. Tek şart "insert" yaparken "not null" kolonlarını boş bırakmamak.

insert dbo.KatUrunler(1)(productname) values ('elma')

Sorgumu çalıştırdım. "Products" tablosuna "insert" işlemi gerçekleşti. Buradaki fonksiyonumun parametresinin hiç bir önemi yok. Çünkü fonksiyonumu tanımlarken, fonksiyon içerisinde kullanmadım. 8 tane kategorim var. Parametre değerini 9 versem de hata vermeyecektir. Fakat hiç parametre vermezsem sorgum hata verecektir.

Peki eğer fonksiyonumun gövdesini değiştirmek istiyorsam ne yapacağım? Tabi ki "alter" deyimini kullanacağız. Hemen fonksiyonumuzu değiştirelim.

ALTER FUNCTION KatUrunler(@CatId INT)

RETURNS TABLE

AS

      RETURN

      (

            SELECT p.CategoryId,c.CategoryName,p.ProductName

            FROM dbo.Products p

            JOIN Categories c

            ON c.CategoryId=p.CategoryId

            WHERE p.CategoryID=@CatId

      )

  

GO

SELECT * FROM dbo.KatUrunler(1)

Böylelikle arkadaşlar iki farklı tablodan verilerimi çektim ve çalıştırdım. Arada "go" adında "batch seperator" ayracı kullanmakta fayda var. Çünkü "go" kendinden önceki komut çalışmasını bitirmeden, kendinden sonraki komutları çalıştırmaya başlamaz. Yani komutları part part çalıştırır. Peki arkadaşlar, fonksiyonumu tanımlarken çağırmadığım kolonları, fonksiyonumu çağırırken o kolonları da çağırabilir miyim?

SELECT UnitsInStock FROM KatUrunler(1)

Hayır arkadaşlar çağıramam. "Invalid Column" hatası alırım.
Şimdi fonksiyonumuzu silmeye çalışalım. Tahmin ettiğiniz sürece "drop" kullanacağız.

DROP FUNCTION KatUrunler

Silerken function olduğunu belirtmeliyim. Yoksa silmez. Ayrıca fonksiyonu silerken parametre değerini bir önemi yoktur. Bu yüzden parametre yazılmaz. Yazılırsa hata verir.

Umarım yardımcı olabilmişimdir. 

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