Ünite 3: Saklı Yordam ve Fonksiyon Kullanımı

Giriş

Saklı yordam (stored procedure); belirli bir görevi yerine getirmek için tasarlanmış, sunucu üzerinde tutulan, birden fazla tablo üzerinde işlem yapabilen, program içinden farklı parametreler ile çağrılarak kullanılabilen SQL tabanlı komut kümesidir.

Veritabanında yer alan veriler ile yapılan seçim, ekleme, güncelleme, silme ve benzeri işlemler için SQL tabanlı komutlar her defasında öncelikle yazım kuralları açısından denetlenir, sonra ilgili tablo ve alanların olup olmadığının kontrolü yapılır. En kısa ve en hızlı sorgunun nasıl yapılacağı belirlendikten sonra da ilgili sorgu derlenerek komut çalıştırılır. Saklı yordamlar sayesinde, sorgunun derlenmesine kadar olan kısımlar bir kez yapılır ve saklı yordamın her çağrılışında sadece ilgili sorgu çalıştırılır. Daha sonraki çalıştırma işlemlerinde derlenmediklerinden saklı yordamlar oldukça hızlı çalışırlar.

Saklı yordamlar programlama dillerindeki gibi parametre içerirler. Bu parametrelere göre çalışıp farklı sonuçlar listeleyebilir. Dışarıdan değer alıp geriye değer döndürebilirler. Bir saklı yordam diğer saklı yordamlar tarafından çağırılıp kullanabilir. Saklı yordamların giriş çıkış parametrelerinde değişiklik olmadığı sürece, kullanıcı arayüzünde program değişikliği yapmadan saklı yordamların kodlanmasında değişiklik yapılabilir. Bu ise veritabanı programlamayı kullanıcı arayüzü programlamadan bağımsız hâle getirir. Modüler yazılım mimarisi oluşturmaya da önemli bir katkı sağlar. Bu sayede farklı kullanıcıların da aynı modülü kullanma imkânı olur. Kullanıcıların saklı yordama erişimi yetki iznine bağlanabilir. İzne sahip değilse doğrudan saklı yordamı çalıştıramazlar. Veritabanı yöneticileri, kullanıcılarına saklı yordam bazında kullanıcı hakları tanımlayabildiğinden aynı zamanda güvenliğe katkı sağlarlar.

Saklı yordamlar; sistem saklı yordamları (system stored procedures), genişletilmiş saklı yordamlar (extended stored procedures) ve yerel saklı yordamlar (local stored procedures) olmak üzere üçe ayrılmaktadır.

Veritabanı programlamada önemli bir esneklik sağlayan yapılardan birisi de fonksiyon’dur. Fonksiyonlar da kullanıcı tarafından tanımlanabilen fonksiyonlar ve sistem fonksiyonları olmak üzere ikiye ayrılmaktadır. İlk değerlendirmede kullanıcı fonksiyonlarının genel yapısı saklı yordamlara benzemekle beraber aralarında aşağıdaki farklar vardır:

• Saklı yordamda fonksiyonlar çağrılabilirken, fonksiyon içinde saklı yordam çağrılamaz.
• Kullanıcı tanımlı fonksiyon mutlaka bir tablo veya sayılı değer döndürmelidir. Saklı yordamların mutlaka değer döndürmeleri gerekmez.
• Saklı yordamlarda veri işleme komutları çalıştırılabilirken kullanıcı fonksiyonlarında bu işlemler yapılamaz.
• Saklı yordam içinde TRY CATCH yapısı ile hata ayıklama opsiyonu varken, fonksiyonlar içinde bu opsiyon yoktur.
• Saklı yordam içinde hareket yönetimi varken, fonksiyon içinde bu mümkün değildir.
• Saklı yordamlar derlenmiş olarak veritabanında tutulurken, fonksiyonlar çalışma zamanında derlenir ve çalıştırılır.

Sistem Saklı Yordamları

SQL Server sunucusunda ön tanımlı olarak bulunan ve “sp_” ön eki ile adlandırılmış saklı yordamlar sistem saklı yordamları olarak adlandırılır. Herhangi bir veritabanı üzerinde sistemle ilgili işlem yapmak veya bilgi alma için kullanılırlar.

Sistem saklı yordamları kullanım amacına göre aşağıdaki şekilde sınıflandırılabilir:

• Katalog Saklı Yordamları (Catalog Stored Procedures): Açık Veritabanı Bağlantısı (ODBC) uygulamalarını sistem tablolarındaki değişimlerinden izole etmek
• Değişen Veri Yakalama Saklı Yordamları (Change Data Capture Stored Procedures): Değişen veri yakalama nesnelerini aktif-pasif hâle getirme veya raporlamak
• İmleç Saklı Yordamları (Cursor Stored Procedures): İmleç uygulamaları
• Veritabanı Motoru Saklı Yordamları (Database Engine Stored Procedures): SQL sunucu veritabanı motorunun bakımı
• Veritabanı E-mail Saklı Yordamı (Database Mail Stored Procedures) (T-SQL): SQL sunucu oluşumlarından e-mail atmak
• XML Saklı Yordamları (XML Stored Procedures): XML dosya yönetimi

Sistem saklı yordamlarının tüm listesine Nesne tarayıcı (Object Explorer) penceresinde “Databases › System Databases › master › Programmability › Stored Procedures › System Stored Procedures” hiyerarşisi takip edilerek ulaşılabilir.

Yerel Saklı Yordamlar

Yerel saklı yordamlar, kullanıcı tarafından oluşturulan saklı yordamlar olduğundan kullanıcı tabanlı saklı yordamlar olarak da adlandırılır. Saklı yordamlar ile farklı SQL yığınları hızlı bir biçimde kullanıcıların ortak kullanımına açılmış olur. Saklı yordamlar, veritabanı programlamada performansı yüksek işler yapmak için kullanılan kritik araçlardan birisidir. Saklı yordamları isimlendirirken “sp_” ön ekini kullanmak okunabilirliğini arttırır.

SQL Server Management Studio’da kullanıcıların sql sorgu ve nesne oluşturmalarına yönelik yardımcılar kullanılarak hızlı bir şekilde yazılabilmektedir. Bunun için Object Explorer üzerinde ilgili nesne üzerinde sağ fare tuşuna tıklanarak şablon kodlara ulaşılabilir. Örneğin bir saklı yordam yazmak için Database › Bilişim › Programmability › Stored Procedure kısmına gidilerek Stored Procedure ifadesi üzerinde sağ fare tuşuna tıklanarak New Stored Procedure seçeneğine basıldığında şablon bir stored procedure kodu yeni bir sorgu ekranında açılacaktır.

Yerel saklı yordamların amacı daha önceden optimize edilmiş komut kümelerinin oluşturulmasıdır. Ancak önemli bir işlevi ise özellikle istemcilerin standart isteklerine yanıt verecek yapılardır. Örneğin bir web sayfası kullanıcısı ya da bir banka terminalindeki yazılımın talep ettiği veriyi istemciye ulaştırmasıdır. Bu açıdan bakıldığında saklı yordamlar kullanıcıların veri isteklerini hızlı olarak hazırlayan ve gönderen yapılardır. Bir saklı yordam, istemciden bir veya daha fazla parametreyi alarak standartlaştırılmış sorgu ifadelerini çalıştırır. Çalıştırılan sorgular ile istemciye bir değer ya da bir tablo döndürülebilir, veri ekleme, güncelleme ya da silme işlemi yapılabilir.

Sistem Fonksiyonları

Sistem fonksiyonlarının bir kısmı “@@” işareti ile başlar. SQL sunucu tarafından sunulan bazı fonksiyonlar parametre içermezler. SQL Server tarafından tanımlanan fonksiyonların bir kısmı evrensel değişkenler olarak da bilinip kullanıcı tarafından oluşturulamazlar.

Sistem fonksiyonları kullanım amacına göre aşağıdaki şekilde sınıflandırılabilir:

• Kümeleme Fonksiyonları (Aggregate Functions): Belli bir veri kümesinde işlem yapıp tek değer döndüren fonksiyonlardır.
• Yapılandırma Fonksiyonları (Configuration Functions): Sunucunun mevcut yapılandırılması hakkında bilgi veren fonksiyonlardır.
• İmleç Fonksiyonları (Cursor Functions): İmleçler ile ilgili veri döndüren fonksiyonlardır.
• Tarih ve zaman fonksiyonlar (Date and Time Functions): Tarih ve zaman üzerinde işlemler yapan ve karakter, nümerik değer veya tarih bilgisi döndüren fonksiyonlardır.
• Matematiksel Fonksiyonlar (Mathematical Functions): Girdi değerlerine bağlı nümerik değer döndüren fonksiyonlardır.
• Metaveri Fonksiyonları (Metadata Functions): Veritabanı ve veritabanı nesneleri üzerinde bilgi döndüren fonksiyonlardır.
• Güvenlik Fonksiyonları (Security Functions): Kullanıcılar ve rolleri hakkında bilgi döndüren fonksiyonlardır.
• Dizgi Fonksiyonları (String Functions): Dizgiler üzerinde işlemler yapan fonksiyonlardır.
• Sistem ile ilgili statiksel Fonksiyonlar (System Statistical Functions): İstatistiki bilgi sağlayan fonksiyonlardır.

Sistem fonksiyonlarına nesne tarayıcısı penceresinde “Bilisim › Programmability › Functions › System Functions” dizini altından ulaşılabilir.

Kullanıcı Tanımlı Fonksiyonlar

Kullanıcı tanımlı fonksiyonlar, kullanıcılar tarafından tanımlanan tek bir değer veya tablo döndürmek için kullanılan ilişkisel veritabanı nesneleridir. SQL sunucu içerisinde tanımlı olmayan fonksiyonları hazırlamak için kullanıcı tanımlı fonksiyonlar kullanılır. Örnek veritabanı olan Bilisim veritabanımızın üzerinde yer alan kullanıcı tanımlı fonksiyonlara erişebilmek için, nesne tarayıcı penceresinde “Bilisim › Programmability › Functions” dizininden erişebiliriz. Kullanıcı tanımlı fonksiyon oluşturmak için kullanılan temel SQL komutları aşağıda verilmiştir:

• CREATE FUNCTION: Fonksiyon oluşturmak için kullanılır.
• ALTER FUNCTION: Fonksiyonda değişiklik yapmak için kullanılır.
• DROP FUNCTION: Mevcut olan fonksiyonu silmek için kullanılır.

Sayıl değerli fonksiyonlar (Scalar-valued functions), Tablo değerli fonksiyonlar (Table-valued functions) ve Kümeleme fonksiyonları (Aggregate functions) olmak üzere üç çeşit kullanıcı tanımlı fonksiyon mevcuttur.

Sayıl değerli fonksiyonlar, tek bir sayısal değer döndüren fonksiyonlardır. Aşağıdaki şekilde yazılır:

CREATE FUNCTION Fonksiyon_Adi(Parametreler)
RETURNS geriDonusTipi
AS
BEGIN
-- Sorgular
RETURN geriDonusDegeri
END

Tablo değerli fonksiyonlar (table-valued functions) çalıştıktan sonra geriye bir tablo ile dönen fonksiyonlardır. Bu nedenle bu tür sorgular genellikle bir seçme sorgusunun tablosu yerine yazılabilirler. Genel yazım şekli aşağıdadır:

CREATE FUNCTION Fonksiyon_Adi(Parametreler)
RETURNS TABLE
AS
RETURN (SELECT SQL_sorgusu)

Tablo değerli fonksiyonlarda tablo tanımlanarak da geri dönüş sağlanabilir. Bu bir sorgu sonucunu döndürmek yerine daha karmaşık işlemler sonucu çıkan çıktı, tabloyu geri döndürmek için kullanılabilir. Genel yazım şekli aşağıdadır:

CREATE FUNCTION Fonksiyon_Adi(Parametreler)
RETURNS @TableName TABLE (Alan_tanımlaması)
AS
BEGIN
SQL_Sorgu
RETURN
END

Kümeleme fonksiyonları, kullanıcı tanımlı fonksiyonların sık kullanılmayan türüdür. Sistem fonksiyonları altında bulunan kümeleme fonksiyonları, ihtiyaç olan bir işlem için yeterli değil ise kullanıcı tanımlı fonksiyon tanımlanır. Kümeleme fonksiyonları diğer SQL fonksiyonlar gibi oluşturulmazlar. NET platformunda yeni bir sınıf oluşturarak SQL server yazılım kodları ile birleştirilmesi gerekir.