Tablo Döndüren Fonksiyonlar

Bu tür fonksiyonlar çalışma prensibi bakımından view’ lara benzerler fakat farklı olarak dışarıdan parametre de alabilirler. Tablo döndüren fonksiyonların genel kullanım biçimi şöyledir.

CREATE FUNCTION [sahip-ismi] fonksiyon_adı ( [@Parametre_adı veri_tipi, …..] )
RETUNS TABLE
AS
RETURN (SELECT cümlesi)

Yazılan fonksiyon incelendiğinde RETURNS TABLE ifadesi ile bir tablo döndürüleceği belirtilmiştir. Daha sonra RETURN ifadesinde yazılmış olan SELECT cümlesi ile kullanıcıya geri döndürülmesi gereken kayıtlar sorgulanarak gönderilmiştir. Fonksiyonun çalıştırılması şöyledir.


Örnek-2: Genellikle E-ticaret sitelerinde yer alan ve bir ürünü alanlar bunları da satın aldı şeklinde karşımıza çıkan ürünlerin listesi yer almaktadır. Aynı şekilde bir kullanıcı kodu kullanarak böyle bir liste oluşturacak bir fonksiyon yazalım. Bunun için gerekli olan tablolar şunlardır. 
tblUrun: Ürün bilgilerinin yer aldığı tablodur.
tblSiparisDetay: Sipariş detaylarının yer aldığı tablodur.
tblSiparis: Siparişi verilen ürünlerin tutulduğu tablodur.


Bu fonksiyon belli bir ürünü satın alan kullanıcıya ait kullanıcı kodunun bulunmasını sağlamaktadır. Buradan elde edilecek olan kullanıcı kodu yazılacak olan ikinci fonksiyonda kullanılacaktır.

Bu işlem iki aşamalıdır. Yani iki ayrı fonksiyon yazılmalıdır. Bunlardan birincisi şöyledir:
CREATE FUNCTION AlanlarinKodu(@urunKod int)
RETURNS TABLE
AS
RETURN SELECT kullaniciKod FROM tblSiparisDetay SD
INNER JOIN tblSiparis S ON S.faturaKod=SD.faturaKod
WHERE urunKod=@urunKod


Bu fonksiyon belli bir ürünü satın alan kullanıcıya ait kullanıcı kodunun bulunmasını sağlamaktadır. Buradan elde edilecek olan kullanıcı kodu yazılacak olan ikinci fonksiyonda kullanılacaktır.


Bu fonksiyonda ise ürün kodu girilerek bu ürünü alan kişinin başka hangi ürünleri aldığı listelenmektedir.
Burada tblUrun ve tblSiparisDetay tabloları kullanılarak siparişi verilen ürünler tespit edilmiştir.
Ardından tblSiparis tablosu ve elde edilen bu liste faturaKod değerleri kullanılarak birleştirilmiştir.
Elde edilen yeni liste kullanıcı kodlarına bakılarak bu listede hangi kullanıcının hangi ürünü satın aldığı tespit edilmiştir.


İkinci yani asıl fonksiyonumuz ise şöyle olacaktır.
CREATE FUNCTION BunuAlanBunlariAldi(@urunKod int)
RETURNS TABLE
AS
RETURN
SELECT U.urunKod,U.urunAd FROM tblUrun  U
JOIN tblSiparisDetay SD ON SD.urunKod=U.urunKod
JOIN tblSiparis S ON S.faturaKod=SD.faturaKod
JOIN AlanlarinKodu(@urunKod) FNA ON
FNA.kullaniciKod=S.kullaniciKod

Bu fonksiyonda ise ürün kodu girilerek bu ürünü alan kişinin başka hangi ürünleri aldığı listelenmektedir.
Burada tblUrun ve tblSiparisDetay tabloları kullanılarak siparişi verilen ürünler tespit edilmiştir.
Ardından tblSiparis tablosu ve elde edilen bu liste faturaKod değerleri kullanılarak birleştirilmiştir.
Elde edilen yeni liste kullanıcı kodlarına bakılarak bu listede hangi kullanıcının hangi ürünü satın aldığı tespit edilmiştir.

Skaler Kullanıcı Tanımlı Fonksiyonlar

Skaler fonksiyonlar, bir işlem neticesinde sadece tek bir değer döndüren fonksiyonlardır.
SQL içerisinde tanımlı birçok skaler fonksiyon vardır.
Matematiksel, karakter, tarih fonksiyonları vb.
Örneğin SELECT GETDATE (l) komutu FROM ifadesi olmadan kullanılabilir. Bu tür yani GETDATE ( )  şeklindeki fonksiyonlar skaler fonksiyonlardır.
Bazı durumlarda kullanıcılarda bu tür fonksiyonlara ihtiyaç duyabilirler. Örneğin yapılan alışveriş neticesinde sepette kaç ürün olduğunu döndüren fonksiyon skaler bir fonksiyondur.

Skaler fonksiyonların genel tanımı şöyledir:
CREATE FUNCTION [sahip-ismi] fonksiyon_adı
(
@parametre_adı veri_türü,
….
)
RETURNS skaler_veri_tipi
AS
BEGIN
SQL_ifadeleri
RETURN skaler_deger veya değişken
END

Örnek: Bir müşteri kodunu girerek müşterinin sepetinde kaç adet ürün olduğunu bulan bir fonksiyon yazalım.

Yazmış olduğumuz fonksiyona dikkat ettiğimizde RETUNS deyimi ile fonksiyonun integer tipte bir veri döndüreceği anlaşılmaktadır.
@Urun_sayisi olarak bir değişken tanımlandı.
Daha sonra SQL cümlesinde dışarıdan parametre olarak verilen kullanıcıKod bilgisi ile veritabanındaki kullaniciKod bilgisi karşılaştırılarak toplam ürün_sayısı hesaplandı ve @Urun_sayisi değişkenine aktarıldı.
RETURN komutu ile elde edilen bu ürün sayısı dışarıya tek bir değer olarak gönderildi.

Kullanıcı Tanımlı Fonksiyonlar

Kullanıcı tanımlı fonksiyonlar SQL Server 2000 ile gelen özelliklerden biridir.  Bu yapılar standart programlama dillerindeki fonksiyon mantığı ile aynıdır.
Fonksiyonlar belli bir sonucu geri döndürmek için tasarlanmış bir veya birden fazla yerde kullanılan yapılardır.
Kullanıcı tanımlı fonksiyonlar, tıpkı Stored Procedure’ler gibi dışarıdan parametre alabilirler.
Aynı zamanda IF…ELSE gibi T-SQL’in diğer ifadelerinin de kullanılmasına imkan tanırlar.
Kullanıcı tanımlı fonksiyonlar,  SP’ ler gibi derlendikten sonra tampon bir belleğe atanır ve daha sonra bu tampon bellekten çağrılarak kullanılabilirler.
Bir diğer özelliği de view (görünüm)’lar gibi bir tablo şeklinde kayıt döndürebilirler.

Bu fonksiyonları, View ve SP’lerden ayıran ve özellikle kullanılması gereken durumlar şöyledir:
Dışarıdan parametre alan view tanımlanamamaktadır. Fakat      böyle bir durum fonksiyonlarda kullanılabilmektedir.
SP’ler bir sorgunun parçası olarak birkaç durum dışında çalışmazken fonksiyonlar sorguların içerisinde kullanılabilirler.
Örneğin sp_helpdb, sp_helptext gibi prosedürler tek başlarına çalıştırılabilirler, fakat bir sorgunun parçası olarak çalıştırılamazlar.

SP’lerde Parametre Kullanmak

Standart prosedürlerde olduğu gibi SP’lerde de prosedürlere parametre gönderilerek bir sonuç üretilebilmektedir. Yani dışarıdan girdi parametreleri ile değer alabilmektedirler. Parametreli SP’ lerin genel kullanım biçimi şöyledir.

CREATE PROCEDURE prosedür_ismi
( @parametre_ismi veri_tipi, …..)
AS
SQL ifadeleri


Örnek: Bir kullanıcı kodu alındığında o kullanıcıya ait sepette yer alan ürünleri görmek istiyoruz. Bu işlem için tblSepet ve tblUrun tabloları kullanılacaktır.

SP’lerde Değişiklik Yapmak

Bir SP’de değişiklik yapmak için ALTER komutu kullanılmaktadır.
ALTER komutu ile geçen dersimizde anlatmış olduğumuz WITH ENCRYPTION komutu da kullanılabilmektedir. Bildiğiniz gibi WITH ENCRYPTION komutu kodların başkaları tarafından görülmesini engellemektedir.
ALTER komutunun genel kullanım biçimi aşağıdaki gibidir:

ALTER PROCEDURE Prosedür_ismi
AS
SQL ifadeleri

Sp_helptext ‘gunluk’ şeklinde çalıştırıp kodları görmek istediğimizde SP kodları görüntülenmeyecektir.

SP’lerin Oluşturulması

Bir kullanıcı tanımlı prosedür oluşturmanın genel biçimi aşağıda verilmektedir.

CREATE PROC/PROCEDURE prosedür_adı
AS
SQL ifadeleri

NOT: SP oluştururken PROC deyimi yada PROCEDURE deyimi kullanılmaktadır.

Örnek-1: Öğrenciler tablosu aşağıda verilmektedir. Bu tabloyu göz önüne alarak ortalaması 50’nin üzerinde olan öğrencileri bulan bir Stored Procedure yazalım.


CREATE PROCEDURE elliustu
AS
SELECT * FROM Ogrenciler WHERE ortalama >= 50


Oluşturulan SP’yi çalıştıralım. Bir prosedürü çalıştırmak için iki yol vardır. Doğrudan SP’nin ismini yazabilirsiniz yada exec SP_adi yazarak çalıştırabilirsiniz.




Örnek-2: Gün içerisinde yapılan satışları bulan bir SP oluşturalım. Bunun için tblSiparis ve tblSiparisDetay tabloları aşağıda verilmiştir.


Oluşturulacak olan SP aşağıdaki gibi olacaktır.

SP’lerin Çalıştırılması

SP’lerin performansı açısından ne sağladığını anlamak için kodlama aşamasından çalıştırılma aşamasına kadar bütün aşamaları çok iyi anlamak ve bilmek gerekmektedir.
Bu aşamaları şöyle sıralayabiliriz:
Ayrıştırma (Parsing),
Derleme (Compiling),
Çalıştırma (Executing).
Ayrıştırma işlemi sırasında SQL ifadelerinin geçerli olup olmadığı denetlenir.
Veritabanı içinde tablo, VIEW gibi başka nesneler varsa ve bunlara gönderme yapılıyorsa, bu nesnelere ait izinlerin olup olmadığı kontrol edilir.
Sorgu ağacı yada sıra ağacı denilen bir yapı ortaya çıkarılır.
Ardından SP’nin ismi sysobjects tablosuna kaydedilir. Daha sonra da syscomments tablosuna SP’nin kodları kaydedilir.

Derleme aşamasında, bir önceki aşamada oluşturulan sıra ağacı ele alınarak bir çalışma planı çıkartılır.
Bütün çalışma planı üzerinde güvenlik ve yetkiler denetlenir.
Bu çalışma planı, hangi aşamada hangi kontrollerin kullanılacağını veya tabloların kontrol edileceğini içermektedir.

Çalıştırma aşamasında, bir önceki aşamada elde edilen çalışma planı ele alınarak çalıştırma işlemi gerçekleştirilir.
Örneğin bir SELECT ifadesi kullanılmışsa sorgu veri işlemeden sorumlu DML yöneticisine iletilir.
Bir SP eğer ilk defa çağrılıyorsa, bu üç aşama gerçekleştirilir. Eğer daha önce çalıştırıldıysa sorgulama ağacı oluşturma işlemleri yapılmaz ve oldukça hızlı bir şekilde SP’nin derlenmiş hali çalıştırılır. Bunun nedeni SP’lerin derlenmiş birer nesne olmasıdır.

Kullanıcı Tanımlı Stored Procedure

Programcı tarafından programlanan prosedürlerdir.
Konu olarak en fazla üzerinde duracağımız prosedürlerdir.
Geçerli oldukları duruma göre üç’ e ayrılırlar:
Geçici SP’ler,
Yerel SP’ler,
Uzak SP’ler.

Geçici Stored Procedure’ler, özellikle SQL Server’ın eski sürümlerinde kullanılan bir türdür.
Her oturum açıldığında derlenmesi gereken SP’lerdir.
Yerel SP’ler ise, kullanıcı tarafından oluşturulan ve bu ders boyunca üzerinde duracağımız SP’lerdir.
Uzak SP’ler, dağıtık bir modelde tasarlanan ve uzaktaki bir sunucuda yer alan SP’lerdir.

Sistem Stored Procedure

sp_ ile başlayan prosedürlerdir.
Master veri tabanında tutulurlar.
Extended Stored Procedure’de olduğu gibi tam adlarının yazılması zorunluluğu yoktur. Sadece adını yazmak yeterlidir.
İstenirse tam ismi kullanılarak ta çalıştırılabilir fakat böyle bir zorunluluk yoktur.

Örneğin sp_helpdb sistem prosedürü veritabanı yönetim sisteminde yer alan veritabanlarını listeler.