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