Stored Procedure- Fonksiyonlar- Tetikleyiciler(Triggers)
Stored Procedure
Stored Procedure’lar belirli bir işlevi, görevi yerine getirmek için özellikle yapılandırılmış bir veya daha fazla tablo, stored procedure vs. ile ilişki kod parçacıklarıdır. Stored Procedure’lar ilk çalıştıklarında derlenirler. Tekrar çalıştıklarında derlenmezler. SQL sorguları(ad hoc query) çağırıldığında ayrıştırma, derleme ve çalıştırma aşamalarından geçmektedir. Adımları aşağıda paylaştım. Stored procedure’lar daha önceden derlendikleri için, normal kullanılan bir SQL sorgusundan çok daha performanslı olup ayrıca ağ trafiğini de yormayan yapısıyla SQL yapılarında çok kullanılan bir kod parçacığıdır.
Query → parse → optimize →compile →execute →result
Execution Plan çıkarıldığı ve derlendiği için ilk 4 aşamayı atlar ve daha hızlı çalışır. Execution Plan, sorguların nasıl çalışacağını gösteren “çalışma planı”dır.
Client Server mimarisinin aksine toplu işlemler kendi içerisinde çalıştığı için sonuçlar network hızında değil RAM hızında çalışır. Yazılım güncellemeden değişiklik yapılabilir. Güvenlidir. SQL injection saldırılarına kesin çözümdür. SQL injection, Zararsız SQL cümlelerinin arasına bir takım zararlı olabilecek SQL cümleciklerinin enjekte edilip saldırı yapmak, zarar vermek için yapılan eylemdir.
Kritik raporlar için stored procedure bazında yetki verilebilir. Herhangi bir programlama dilinde yapılabilecek her türlü komut burada da yazılıp çalıştırılabilir. Stored procedure’lar birbiri içerisinden çağırılabilir. Parametresiz kullanılabilir. Performansı ölçülebilir. Kaç kez çalıştırılmış en son ne zaman çalıştırılmış gibi bilgiler takip edilebilir.
Fonksiyonlar
System Defined Functions: Sistem Tanımlı İşlevler olmadan uygulanması zor olan bazı temel veya önemli işlemler için SQL Server’da önceden mevcut olan işlevlerdir. Dolayısıyla, Sistem Tanımlı İşlevler, bu tarz işlemlerle başa çıkmada önemli rol oynar.
User Defined Function: fonksiyonlar değer döndüren yapılardır. İçine parametre alır ya da almaz ama sonuç olarak değer döndüren yapılardır.
Scalar Valued Function: bir veya daha fazla değer alan ancak tek bir değer döndüren fonksiyonlardır. Skaler fonksiyonlar, kodu basitleştirmeye yardımcı olur. Örneğin; birçok sorguda görülen karmaşık bir sorgu hesaplaması olabilir. Formülü her sorguya dahil yerine formülü kapsayan ve her sorguda kullanılan bir Skaler fonksiyon oluşturulabilir.
SELECT dbo.<fonksiyon.adi> olarak çalıştırılabilir.
Table Valued Function: tek fonksiyonda tablo halinde çoklu veri döndürebilmek için oluşturulur. Scalar Valued Function’da SELECT dbo.<fonksiyon.adi> şeklinde döndürülür, Table Valued Function’da SELECT * FROM dbo.<fonksiyon.adi> şeklinde yani tablodan veri çeker gibi çağırılır. JOIN kullanmak istenirse de normal tablolardaki INNER, OUTER JOIN vs. gibi komutlar yerine CROSS APPLY diyerek fonksiyon joinlenir.
Stored Procedure’lar ve Fonksiyonları aynı yapılarmış gibi düşünebiliriz. Farklılıklarını aşağıdaki tabloda daha net olarak görebiliriz. Stored Procedure ve Fonksiyonların karşılaştırılması;
(UDF: user defined function)
OLTP(Online Transactional Processing) — Transaction
Bütün bir işlem bloku bir transaction içerisinde gerçekleşmesi gerekir. Ve herhangi bir problem olduğunda da sistemin kendisini bu işlemi gerçekleştirmeden önceki haline geriye alması gerekir. Tüm bunları yaparken bunun veritabanı yönetim sistemi bazında yapılması gerekir. Yani herhangi bir ara yazılım, herhangi bir ekstra kaynaka ihtiyaç duymadan tüm bu işlemleri otomatik olarak geri alabiliyor olması gerekir.
SQL Server’da .mdf ve .ldf dosyaları bulunur. (mdf file, primary data file. ldf file, log data file.) Bir veritabanında bir tabloda bir işlem gerçekleştirildiğinde örneğin INSERT işlemi, buradaki veri bloku öncelikle log datasına yazılır. 2.işlem geldiğinde o da log datasında ilk işlemin arkasına gelir. Buradaki işlemler bittikten sonra, mdf datasına yazılmak üzere işlem başlatılır. Bu işlemlerde herhangi problem yaşanırsa sistem rollback yapar. Rollback yapıldığında bu veriler mdf datasından atılır ve log datasından da temizlenerek sistem hiçbir şey olmamış gibi yoluna devam eder. Eğer herhangi bir problem yaşanmamışsa ve sistem commit olmuşsa, sistem dataları mdf datasına yazar ve datalar kaydedilmiş olur. SQL Server’da bu iki dosyanın bulunma nedeni de budur. Rollback: açılmış transaction’ı geri döndürür.
BEGIN TRAN diyerek transaction’ı başlatırız. Transaction’ı işlemlerimiz bittikten sonra COMMIT etmezsek transaction’ın etkilediği tabloya yeni sorgu çalıştırmayı denediğimizde executing query sürekli çalışmaya devam eder. Çünkü transaction açık bırakıldı yani tamamlanmadı. Bundan dolayı farklı oturumlar bu veri değişimini veritabanında bulamaz. Yani diyelimki veriyi INSERT ettik ve COMMIT etmedik. Farklı bir oturumda bu veriye sorgu çektiğimizde executing query sürekli çalışacaktır. COMMIT ederek mdf datasına yükleriz ve veritabanına kaydetmiş oluruz.
Transaction ile INSERT ettikçe log file ve data file boyutları veri girdisi kadar artar. COMMIT edince mdf datası ile veritabanına kaydedilir. Bundan sonra log datasının içinden atılmış olur fakat windows dosya sisteminde de bakarsak log file boyutu küçülmez. Bunu manuel olarak shrink vasıtasıyla yapabiliriz. Veritabanına sağ tık → tasks →shrinks ve log file seçersek min olarak ayarlayıp dosya boyutunu başlamış olduğu temel seviyesine döndürebiliriz.
WITH(NOLOCK): devam eden transaction’dan dolayı sorgu sonucu gelmiyorsa sorgunun sonuna WITH(NOLOCK) ifadesi eklenerek sonuçlar görülebilir. Bu durumda veriyi logdan çekecek ve güncelleme olduktan sonraki veriyi döndürecek. Yani transaction işleminin COMMIT edilmiş halini dönecektir.
DBCC OPENTRAN: aktif transaction’lar gözlemlenir. output →SPID(server process ID):51
SP_WHO 51 : kullanıcının kim olduğu gözlemlenebilir. output → loginname: OKAN\Administrator
DBCC INPUTBUFFER(51): 51 numaralı kullanıcının yaptığı sorgu gözlemlenebilir.
KILL 51: diyerek kullanıcın işlemi sonlandırılır. Transaction’ı otomatik rollback yaparak sonlandırır. Sorgu sonucu transaction öncesi haline döner.
Yazılımcı ve Sistemden bağımsız veritabanı bazında toplu işlemler transaction ile kontrol edilebilir.
Tetikleyiciler(Triggers)
Tetikleyiciler, veritabanı sunucusunda bir olay meydana geldiğinde otomatik olarak çalışan özel bir stored procedure türüdür. Tetikleyiciler genelde otomatik toplam hesaplama yada loglama amacı ile kullanılır. loglama: birileri tabloda bir şeyler değiştirdiğinde, sildiğinde, güncellediğinde vs. buradaki işlemleri takip edebilmek ve silinen, değiştirilen kayıtların eski hallerine ulaşabilmek amacıyla kullanılır. Tetikleyiciler, INSERT, DELETE, UPDATE gibi işlemlerden sonra otomatik olarak çalışan yapılardır. Tetikleyicilerin içinde sanal olarak oluşan Inserted ve Deleted tabloları bulunur. INSERT, UPDATE, DELETE işlemleri gerçekleştiğinde eski değerler ve yeni değerler veya silinen değerlere ulaşmak için bu tablolar kullanılabilir. Gerçekte var olmayan tablolardır. Ancak tetikleyicinin içerisinde doğrudan Inserted ve Deleted yazarak çağırılan tablolardır.
Inserted tablosu yeni eklenen verilerin yada update edilen, değişen verilerin yeni değerlerini tutan tablodur.
Deleted tablosu ise silinen kaydı yada değiştirilen kaydın eski değerini tutar.
Yukarda transaction kısmında göstermiş olduğum basit transaction işleminin trigger yaratılarak oluşturulmuş halini aşağıda inceleyebilirsiniz.
Tetikleyiciler de stored procedure, Fonksiyon, view gibi bir SQL server objesidir. Bir SQL server objesi CREATE ile oluşturulur. ALTER ile değiştirilir. DROP ile silinir. Tetikleyiciler transaction’ın doğal bir parçasıdır. Tetikleyicilerin içerisinde gerçekleşen bir işlem otomatik olarak transaction gibi gerçekleşir. Yani tetikleyicilerde herhangi bir hata olması durumunda sistem kendisini otomatik olarak ROLLBACK yapar.
HOST_NAME() →bağlanan kullanıcının bilgisayarının ismini döndürür
PROGRAM_NAME() → SQL Server’a bağlanılan programın adını döndürür.
Bu notları çıkarırken yararlandığım eğitimi de sizlerle paylaşmak isterim.
