SQL Server ve Kullanıcı Tanımlı İşlevler

Veritabanlarında sorgu yapmak için temel SQL dilinde yardımcı bazı işlevler mevcut. Bunların yeterli olmadığı durumlarda veritabanı sistemlerinde kullanıcı tanımlı fonksiyonlar (user defined functions) oluşturulabiliyor.

SQL Server’da kendi işlevlerinizi yazıp bunları tüm sorgularda veri dönüşümleri için kullanabilirsiniz. Ama SQL Server’daki işlevler değer üretmekle kalmıyor, FROM öbeklerinde kullanılabilecek şekilde kayıt kümesi üretebiliyorlar.

• SQL Kodlarında Özel İşlevler
• Kullanıcı Tanımlı İşlevler (User-Defined Functions)
• İşlevden Kayıt Kümesi Üretmek

SQL KODLARINDA ÖZEL İŞLEVLER

SQL dilinde geliştiriciler için bazı temel fonksiyonlar mevcuttur. Veriler üzerinde dört işlem ve karakter dönüşümleri dışında mutlak değer, üs ve karekök almak, matematiksel hesaplar yapmak için bazı komutlar oluşturulmuş. Bununla birlikte standart SQL dili çok gelişmiş fonksiyonlar içermez. Bu yüzden veritabanı sistemlerinde bu dil genişletilip yeni diller oluşturulmuş.

SQL içinde fonksiyon kullanımının en kolay yapıldığı yerlerden biri Access’tir. Access modülleri içindeki global tüm fonksiyonlar veritabanı genelindeki her SQL kodu içinde kullanılabiliyor. Fakat bu sadece Access veritabanı içinde çalışırken geçerli oluyor.

SQL Server, Interbase gibi veritabanı sunucuları ise hem kendi fonksiyonlarının ve kayıtlı yordamlarının hem de özel hazırlanmış DLL yordamlarının SQL kodlarında kullanılmasına izin veriyorlar.

SQL kodlarında özel fonksiyon kullanma ihtiyacı hangi durumlarda oluşabilir? Standart SQL kodlamasında yer almayan işlevler, karakter dönüşümleri, sayısal fonksiyonlar, karmaşık fonksiyonlar oluşturmaya ihtiyaç duyulabilir. Her fonksiyon veri alanlarını alıp işleyebilir ya da tek başlarına yeni değerler ya da kayıt kümeleri üretebilirler.

Bu ayki uygulamamızda Access ve SQL Server’da oluşturduğumuz fonksiyonlarımızı kişilerin doğum günü bilgilerini listelemek için kullanıyoruz.

Uygulamamızda kişiler tablosunda bulunan doğum tarihi bilgisini tek başına kullanarak;
- sonraki doğum günü kutlamasının ne zaman olması gerektiği,
- tarihin hangi güne geldiği,
- kişinin kaç yaşına basacağı,
- kutlamaya kaç gün kaldığı
gibi bilgileri öğrenebiliyoruz.

Başlamak için Access’te yeni bir modül (module) oluşturun ya da var olan bir modülü açıp en alta aşağıdaki kodları ekleyin.

Function GunAdi(tarih As Date) As String
  Select Case Weekday(tarih, vbMonday)
    Case 1: GunAdi = "Pazartesi"
    Case 2: GunAdi = "Salı"
    Case 3: GunAdi = "Çarşamba"
    Case 4: GunAdi = "Perşembe"
    Case 5: GunAdi = "Cuma"
    Case 6: GunAdi = "Cumartesi"
    Case 7: GunAdi = "Pazar"
  End Select
End Function

Function SonrakiDogumGunu(DogumTarihi As Date) As Date
  Dim ay As Byte, gun As Byte
  ay = Month(DogumTarihi)
  gun = Day(DogumTarihi)
  SonrakiDogumGunu = DateSerial(Year(Date), ay, gun)
  '''Bu yılkini buldu.
  If SonrakiDogumGunu < Date Then
    '''Bu yıl geçtiyse gelecek yıla sarktı.
    SonrakiDogumGunu = DateSerial(Year(Date) + 1, ay, gun)
  End If
End Function

Modülün içine eklenen yukarıdaki GunAdi ve SonrakiDogumGunu fonksiyonları global tanımlı olup hem diğer tüm modüllerde, hem de Access içinde iken tüm sorgularda (ve tüm SQL kodlarında) kullanılabilirler.

SELECT
    Kisi,
    DogumTarihi,
    SonrakiDogumGunu(DogumTarihi) as KutlamaTarihi,
    GunAdi(KutlamaTarihi) as HangiGun
FROM
    Kisiler



Yukarıdaki fonksiyonların karşılığını doğrudan SQL kodu içinde tanımlamak da mümkündür. Office 2000 ile gelen yeni VBA fonksiyonları bu tür işlemleri kolaylaştırıyor.

CHOOSE fonksiyonu SELECT-CASE bloklarının karşılığıdır. Gün adını Choose ile aşağıdaki şekilde bulabiliyoruz:

Choose(Weekday([KutlamaTarihi]);"Pazar";"Pazartesi";"Salı";"Çarşamba";"Perşembe";"Cuma";"Cumartesi")

Birinci parametrenin değeri, sonucun, devamındaki kaçıncı parametreden alınacağını belirliyor. Sonraki doğum günü tarihini doğrudan bulmak ise biraz daha karmaşık:

DateSerial( IIf(DateSerial(Year(Date()); Month([DogumTarihi]); Day([DogumTarihi]))>=Date(); Year(Date()); Year(Date())+1); Month([DogumTarihi]); Day([DogumTarihi]))

VBA’daki IIF fonksiyonu Sinan’ın doğum gününün bugün olduğunu, DateSerial fonksiyonu da 29 Şubat’ta doğan Sevil için 1 Mart’ta doğum günü partisi yapılması gerektiğini hatırlatıyor.

Örnek dosya: dogumgunleri_mdb.zip


USER-DEFINED FUNCTIONS (KULLANICI TANIMLI İŞLEVLER)

Access dışında SQL Server, Interbase gibi veritabanı sunucularında da bu tür fonksiyonlar kullanılabiliyor. Access’te bizim yazdığımız fonksiyonların buradaki adı User-Defined Functions.

SQL Server’daki fonksiyonlar, değer döndüren standart bir fonksiyondan daha fazlasını yapabilir, bir değer yerine kayıt kümesi döndürebilirler.

Exterprise Manager’da veritabanı altındaki User-Defined Functions ağacında kullanıcı tanımlı fonksiyonlar yer alıyor.

Bu fonksiyonlar parametre alabilmelerine karşın bazı kısıtlamalar sözkonusudur. Parametre değerleri, kayıtlı yordamlarda olduğu gibi dinamik tanımlanamıyor. Bu, fonksiyona parametre olarak gönderilen tanımlı bir değişkenin fonksiyon içerisinde değiştirilemeyeceği anlamına geliyor. Bu durumda sadece fonksiyonun kendi değeri elde edilir. Ayrıca kullanım esnasında bütün parametrelerin belirtilmesi gereklidir. VBA fonksiyonlarında kullanılan Optional gibi bir etiket burada yok ne yazık ki.

Kullanıcı tanımlı fonksiyonları kayıtlı yordamlarla (stored procedure) kıyasladığımızda her ikisinin de diğerine göre avantajları olduğunu görüyoruz. Stored procedure’lerin parametrelere değer atabilmelerine ve kümesi üretebilmelerine karşın fonksiyonlar hem değer döndürürler, hem SQL kodlarından kayıt kümesi üretirler, hem de içeride yeni bir kayıt kümesini geçici olarak oluşturup istemciye gönderebilirler. Bununla birlikte kayıtlı yordamlar (stored procedure'ler), fonksiyonların aksine bir değişkeni içeride değiştirip geri gönderebilirler.

SQL Server’da yeni bir fonksiyon oluşturmak için Enterprise Manager ekranında iken User-Defined Functions ağacı üzerinde sağ tıklatıp New User-Defined Function komutunu seçin.

CREATE FUNCTION fn_gunadi (@tarih DATETIME) RETURNS VarChar(10)
AS  
BEGIN 
  RETURN (  
    CASE (SELECT DATEPART(dw, @tarih))
      WHEN 1 THEN 'Pazar'
      WHEN 2 THEN 'Pazartesi'
      WHEN 3 THEN 'Salı'
      WHEN 4 THEN 'Çarşamba'
      WHEN 5 THEN 'Perşembe'
      WHEN 6 THEN 'Cuma'
      WHEN 7 THEN 'Cumartesi'
    END
   ) 
END

Yukarıdaki fonksiyonun tanımlamasının VBA’daki karşılığı aşağıdakine benzerdir.

Function fn_gunadi(tarih as DateTime) as Varchar ‘yani string

Kullanım aşamasında anlaşılır olması için fonksiyon adları bir gelenek olarak “fn_” ile başlatılıyor. Fonksiyonun değerini atamak için RETURN deyimi kullanılıyor. RETURN'dan sonraki parantez içinde bulunan değer fonksiyonun sonucudur.

Fonksiyonlar oluşturulurken isminin devamında parantez içinde varsa parametreleri virgül ile ayırarak tanımlamak gerekir. Sonrasında RETURNS ile fonksiyonun ne tür değer üreteceği belirtilir.

Fonksiyonları kullanırken sahip adını belirtmek gereklidir. Sahip adı geçerli olarak dbo olabilir. Tarih bilgisinden Türkçe gün adını bulan fonksiyonumuzu aşağıdaki şekillerde kullanmamız mümkün.

SELECT dbo.fn_gunadi('2002-05-01')
SELECT dbo.fn_gunadi(GetDate())
SELECT DogumTarihi, dbo.fn_gunadi(DogumTarihi) FROM Kisiler

Sonraki doğum günü bilgisini bulmak için Access'te olduğu gibi bir fonksiyon daha oluşturmak gerekiyor.

CREATE FUNCTION fn_sonraki_dogum_gunu(@dogumtarihi SMALLDATETIME, @orjin SMALLDATETIME)
  RETURNS SMALLDATETIME
AS  
BEGIN 

  DECLARE @tarih SMALLDATETIME

  -- dogum gününü simdiki yila getir.
  SET @tarih= DATEADD (yy, YEAR(@orjin) - YEAR(@dogumtarihi), @dogumtarihi)

  -- dogum günü geçmisse sonraki yila sarksin.
  IF DATEDIFF(d,@tarih,@orjin) > 0 SET @tarih= DATEADD (yy,1,@tarih)

  RETURN (@tarih)

END

Fonksiyonlar içinde, başka fonksiyonlar kullanılabilse de istisnalar mevcuttur. Örneğin en basit GETDATE() komutu fonksiyonlar içinden kullanılamaz. Bu yüzden yukarıdaki fn_sonraki_dogum_gunu fonksiyonu, Access'tekinin aksine güncel tarih bilgisini doğrudan bulamadığı için @orjin parametresini oluşturmak zorunda kaldık.

Doğum tarihi @orjin değeri ile kıyaslanacak ve @orjin tarihinden sonraki ilk doğum günü bilgisi elde edilmeye çalışılacak.

Sonuç olarak fn_gun_adi ve fn_sonraki_dogum_gunu fonksiyonları nihai doğum günü sorgumuzu oluşturmak için yeterlidir.

SELECT *, YEAR(KutlamaTarihi) - YEAR(DogumTarihi) AS Yas, 
  dbo.fn_gunadi(KutlamaTarihi) AS GunAdi,
 (CASE WHEN DATEDIFF(d,GetDate(),KutlamaTarihi)=0 THEN 'Bugün' ELSE 
       CONVERT(Varchar(15), DATEDIFF(d,GetDate(),KutlamaTarihi) ) 
              + ' gün sonra'
  END) AS NeZaman
FROM
 (SELECT Kisi, DogumTarihi, 
    dbo.fn_sonraki_dogum_gunu(DogumTarihi, getdate()) AS KutlamaTarihi
    FROM Kisiler
  ) AS Tablo
ORDER BY KutlamaTarihi

29 Şubat'ta doğan Sevil için Access'tekine karşılık SQL Server'daki listede 1 Mart yerine 28 Şubat'ta kutlama yapılması gerektiği gösteriliyor.

Access'teki DateSerial komutuna karşılık burada kullandığımız DateAdd fonksiyonu, sonraki ayın ilk günü yerine, o ayın sonunu getirmeyi yeğledi.

İŞLEVDEN KAYIT KÜMESİ ÜRETMEK

SQL Server'daki kullanıcı tanımlı fonksiyonların bir başka özelliği, değer dışında kayıt kümesi döndürebilmeleri. Bu, fonksiyonun FROM içtümcesi içinde doğrudan kullanılabileceği anlamına geliyor. Bu durumda haftanın günlerini listelemek için bir tablo oluşturmak yerine fonksiyon kullanmak mümkündür.

SQL Server komut dilinde tablolar değişken olarak ifade edilebiliyorlar. Bir fonksiyon içinde yeni bir tablo değişkeni tanımlayıp buna kayıt eklemek ve fonksiyonun sonucu olarak bu tabloyu göndermek kolay.

CREATE FUNCTION fn_gunler( @Tur INT)
/*
  Tur= 0,(BOŞ) Tüm günler
  Tur= 1 Sadece haftaiçi
  Tur= 2 Sadece haftasonu
*/
RETURNS @Gunler TABLE (Sira INT, GunAdi VARCHAR(10) )
AS
BEGIN

IF (@TUR=0 OR @TUR=1) 
BEGIN
  INSERT @Gunler VALUES(1,'Pazartesi')
  INSERT @Gunler VALUES(2,'Salı')
  INSERT @Gunler VALUES(3,'Çarşamba')
  INSERT @Gunler VALUES(4,'Perşembe')
  INSERT @Gunler VALUES(5,'Cuma')
END
IF (@TUR=0 OR @TUR=2)
BEGIN
  INSERT @Gunler VALUES(6,'Cumartesi')
  INSERT @Gunler VALUES(7,'Pazar')
END

RETURN
 
END

fn_gunler @tur parametresinin değerine göre tüm günleri, haftaiçi günleri ya da haftasonu günleri isteleyebiliyor.

SELECT * FROM fn_gunler(1)

cümlesi haftaiçi günleri gösteriyor. Üstteki RETURNS satırında tanımlanan @Gunler tablo değişkeni fonksiyonun sonucu olacak. TABLE deyiminin yanında parantez içinde tablonun alanları tanımlanıyor. Bu tanımlama sadece fonksiyonun o an çalışması için geçerli olup veritabanında bir değişiklik yapılmıyor.

Tablo üretmeyen fonksiyonlarda RETURNS ile birlikte sadece tip tanımlaması yapılırken, tablo değerli fonksiyonlarda sonuç tablosu RETURNS yanında tanımlanıyor. Bu durumda, fonksiyonun sonucunun gönderilmesi için RETURN deyimini tek başına kullanmak yeterli oluyor.

Tablo değerli fonksiyonların satırlarını oluşturmak için INSERT deyimini birkaç şekilde kullanmak mümkün. Kayıtları VALUES ile doğrudan tanımlayabileceğimiz gibi, başka bir tablo değişkeninden ya da bir kayıt kümesinden alıp oluşturabiliriz.

CREATE FUNCTION fn_kisiler()
RETURNS @tablo TABLE (kisi VARCHAR(50) ) 
AS  
BEGIN
  INSERT @tablo values ('KİŞİLER')
  INSERT INTO @tablo
      SELECT kisi FROM kisiler ORDER BY kisi
  RETURN
END

Yukarıdaki fn_kisiler fonksiyonunu FROM içtümcelerinde aşağıdaki gibi kullanabiliriz artık.

SELECT * FROM dbo.fn_kisiler()





 

Dosya: dogumgunleri_mdb.zip (24 kb.)

Serkan ŞAHİNOĞLU
(Chip Dergisi, Haziran 2002)