Konular:
1- Stored prosedürler
2- Prosedürlerde Parametre Kullanımı
3- Triggerlar
4- Inserted ve Deleted Tabloları
5- Trigger Kullanım Örnekleri
Stored prosedürler
Stored Procedures SQL Server üzerinde saklanan önceden derlenmiş SQL ifadeleridir.Önceden derlenmiş olarak bulunduklarından her türlü sorgulamada en iyi performansı verirler. SQL Server' da sistem tarafından "sp_" ile başlayan isimlerle tanımlanmış bir çok prosedür mevcuttur. Bunlar daha çok adminstration maksatları için ve sistem tablolarından bilgi toplamak için kullanılırlar. Kullanıcı tarafından da kendi prosedürlerini tanımlamak oldukça kolay bir işlemdir. Kullanıcı kendi ihtiyaçları doğrultusunda prosedürler oluşturabilir, kullanabilir, onların hakkında bilgi toplayabilir ve birinden ötekine parametre geçişi sağlayabilir. SQL Server' ın sunduğu imkanlar oldukça geniş olmasına rağmen biz yalnızca kullandığımız kadarıyla bu konuya değineceğiz. Stored Procedure' ler, SQL Server' a güç, etkinlik ve esneklik kazandırırlar. Kullanıldıkları zaman SQL ifadelerinin ve toplu işlemlerin performansını gözle görünür bir biçimde arttırırlar.
Stored Procedure' lar:
- Parametre alabilirler,
- Başka prosedürleri çağırabilirler,
- Kedisini çağıran bir prosedür veya toplu işleme başarılı olduğunu ya da olamadığını, hata oluşması durumunda hatanın nedenini bir durum değeri olarak döndürebilirler.
- Parametrelerin değerlerini kendisini çağıran bir prosedüre döndürebilirler.
Stored Procedure' ların tanımlandıkları andan itibaren tabi tutuldukları işlemlerin farklı olması nedeniyle diğer SQL ifadelerinden ayrılırlar. Direk SQL Server üzerinde saklandıkları için oldukça hızlı çalışan veritabanı nesneleridirler. Bir Stored Procedure ilk kez çalıştırıldığında şu işlemlere tabi tutulur.
1. Öncelikle prosedürün bileşenleri parçalara ayrıştırılırlar.
2. Veritabanındaki table, view gibi batka nesnelere referans yapan biletenler için bu nesnelerin varlığı kontrol edilir.
3. Kontrol işlemi tamamlandıktan sonra prosedürün ismi sysobjects tablosunda ve de prosedürü olutturan kod da syscomments tablosunda saklanır.
4. Bu adımda derleme işlemi yapılır. Derleme esnasında normalize edilmiş plan ortaya çıkar. (Buna sorgulama ağacı da denir) Oluşturulan sorgulama ağacı sysprocedures tablosunda saklanır.
5. Stored Procedure ilk defa çalıştığı zaman, sorgulama planı okunur ve tamamen bir prosedür planı içine derlenir. Daha sonra da çalıştırılır. Bu sayede stored procedure her çalıştırıldığında parçalama, kontrol, sorgulama ağacı oluşturma işlemleri yapılmaz. Bu şekilde de zamandan önemli ölçüde tasarruf edilmiş olunur.
Bundan sonra, SQL Server’ ın çalıştığı andan itibaren prosedürün ilk kez çalıştırılmasıyla birlikte, prosedür derlenmiş bir şekilde belleğe yerleştirilir. Çünkü diğer işlemler prosedür oluşturulurken yapılmış ve bitmiştir. Tekrar yapılmazlar. Stored Procedure kullanmanın başka bir yararı da budur. Bir kez çalıştırıldıktan sonra prosedür planı procedure cache bölgesinde muhafaza edilir. Bu da bir sonraki çağrılışında direk cache’ den okunup çalıştırılması demektir. Böylece standart bir SQL sorgulamasının tekrar tekrar çalıştırılmasından çok daha üstün bir performans elde edilir.
Stored Procedure kullanmanın göze çarpan diğer faydalarını şöyle sıralayabiliriz :
Uygulamanın getirdiği bazı iş kuralları prosedür içinde tanımlanabilir. Bir kez oluştuktan sonra bu kurallar birden çok uygulama tarafından kullanılarak daha tutarlı bir veri yönetimi sağlanır. Ayrıca bir fonksiyonelliğin değişmesi ihtiyacı doğduğunda her uygulama için değişiklik yapmak yerine, sadece bir platformda değişiklik yapılır.
Tüm prosedürler üstün performansla çalışır ancak birden fazla çalıştırılacak olan prosedürler sorgulama planları procedure cache içinde saklandığından daha da hızlı çalışırlar.
Stored Procedure’ ler SQL Server start ettikten sonra otomatik olarak çalıştırılmak üzere ayarlanabilirler.
Stored Procedure’ ler harici olarak kullanılırlar. Trigger’ lardan farklı olarak prosedürler uygulama tarafından ya da script tarafından bir şekilde çağrılmak zorundadırlar. Otomatik devreye giremezler.
Stored Procedure’ lerın içinde SQL sorgulama diline ek olarak SQL Server’ ın kendi fonksiyonlarından da yararlanılabilir.
“IF … THEN” yapılarını ya da normal bir sorgulamada kulllanamayacağımız DATEADD, DATEDIFF gibi etkili bir biçimde veri manipülasyon imkanı sağlayan fonksiyonları da kullanabiliriz.
Kullanıcının bir tabloya erişim izni olmasa bile o tablo üzerinde işlem yapan bir stored procedure’ ü kullanabilir.
Stored Procedure’ lerin oluşturulması ve kullanılması ile ilgili örnekler SQL Server üzerinde mevcuttur.
Biz yalnızca oluşturduğumuz uygulamada kullandıklarımıza benzer olan yapılar üzerinde açıklamalarda bulunacağız.
Bir Stored Procedure’ ün oluşturulması için bazı kurallar mevcuttur. Örneğin en fazla 255 parametreye sahip olabilir. Referans edilen nesneler prosedür oluşturulmadan önce server üzerinde bulunmak zorundadır.
Bir stored procedure içinde Create Default, Create Procedure, Create Rule, Create Trigger, Create View gibi SQL ifadeleri kullanılamazlar. Bunlar biraz aykırı durumlarda karşımıza çıkacak olan kurallardır. Ancak önemli bir noktayı da belirtmek gerekir: Eğer prosedür içinde “SELECT *” ifadesi kullanıldıysa ve daha sonra da bu ifade içinde referans edilen tabloya yeni alanlar eklendiyse prosedür çalıştığında yeni alanlar gözükmezler. Dolayısıyla referans edilen tablo yapısında değişiklik yapılırsa, onu kullanan prosedürün de yeniden derlenmesi gereklidir.
Prosedürlerde Parametre Kullanımı
Parametre kullanmak prosedürün her çalıştığında daha farklı davranış göstermesine imkan tanır. Örnek olarak kendisine verilen test sonuçlarının ortalamasını alan bir prosedür yazabilirsiniz. Prosedürü oluştururken test sonuçlarının değerlerini bilmiyor olabilirsiniz ama prosedür her çalıştığında yeni bir sonuç alırsınız. Parameter veri tipi image tipi hariç her türlü sistem tarafından ya da kullanıcı tarafından tanımlanmış tipleri kabul eder.
Stored Procedure' ün çalışabilmesi için tanımlanmış parametrelerin tümüne değer sağlanması zorunludur. Eğer bir parametre için default bir değer belirlenmişse kullanıcı farklı bir değer almasını istemedikçe parametre default değerini alacaktır. Normal olarak parametreler sırasıyla kullanılmalıdır. Ancakprosedür içinde tanımlanma referansa göre geçirilecek şekilde yapılmışsa, istenilen sırada verilebilir.
OUTPUT opsiyonu başka bir prosedüre değer taşımaya imkan sağlar. Yani bir prosedür kendisini çağ başka bir prosedüre kendi ürettiği bir çıktı değerini geçirebilir. Bu olay aşağıdaki örnekte gösterilmiştir.
Bu prosedür beş parametre kabul eder, ortalamasını alır ve ortalamayı OUTPUT olarak dışarı verir.
CREATE PROCEDURE scores
@score1 smallint,
@score2 smallint,
@score3 smallint,
@score4 smallint,
@score5 smallint,
@myAvg smallint OUTPUT
AS SELECT @myAvg =
(@score1 + @score2 + @score3 + @score4 + @score5) / 5
Bu prosedürdeki
myAvg
değerini alabilmek için öncelikle bir değişken tanımlayıp sonra prosedürü çalıştırabiliriz.
DECLARE @AvgScore smallint
EXEC scores 10, 9, 8, 8, 10, @AvgScore OUTPUT
SELECT `Ortalama :
`, @AvgScore
GO
-----
Ortalama :
9
Dikkat edilirse parametreler tanımlandıkları sıra ile verilmişlerdir. Bu durum pozisyonuna göre verilmedir. stenirse referansa göre de yapılabilir. Bu yöntem uygulanacaksa prosedürün kullanıldığı yerde parametreler herhangi bir sıraya bağlı tutulmaksızın "parametre ismi = değeri" şeklinde geçirilebilir. Aşağıdaki örnekte bu durum incelenmiştir.
DECLARE @AvgScore smallint
EXEC scores
@score1 = 10, @score3 = 9,
@score2 = 8, @score4 = 8,
@score5 = 10, @myAvg = @AvgScore OUTPUT
SELECT `Ortalama :
`, @AvgScore
Sonuç öncekinden farksız olacaktır. Ancak görüldüğü üzere istenilen sıra kullanılabilir. Son olarak; referansa göre geçirilecekse tüm prosedür boyunca bu metod kullanılmalıdır. Prosedürü çağırırken tam ortada diğer moda geçiş yapılamaz.
Prosedürden değer döndürmek için ayrıca RETURN ifadesi kullanılabilir. Bu şekilde OUTPUT ifadelerini hem prosedür içinde hem de prosedürün çağrıldığı yerde tanımlamak zorunda kalınmadan doğrudan değer döndürülebilir. Normal olarak SQL Server 0 ile -99 arası olan değerleri bu şekilde döndürür. Sıfır değeri prosedürün çalışmasında bir hata oluşmadığını belirtir. Diğerleri ise muhtelif hata durumlarında kullanıcıyı uyarmak için kullanılırlar. Ancak kullanıcı RETURN ile bunlar haricinde değerler döndürebilir. Aşağıda kullanımına bir örnek verilmiştir.
CREATE PROC MyReturn
@t1 smallint, @t2 smallint, @retval smallint
AS SELECT @retval = @t1 + @t2
RETURN @retval
Prosedürü oluşturduktan sonra aşağıdaki şekilde kullanılarak sonuç elde edilir.
DECLARE @myReturnValue smallint
EXEC @myReturnValue = MyReturn 9, 9, 0
SELECT `Sonuç :
`, @myReturnValue
------
Sonuç :
18
Triggers
Trigger, belirli bir tablo üzerindeki kayıtlar üzerinde değişiklik yapıldığı zaman çalışan özel bir stored procedure türüdür. Trigger' lar izin verilmeyen ya da tutarsızlığa neden olacak işlemleri engelleyerek veri bütünlüğünün korunmasına yardımcı olurlar. Daha önceki veritabanı sistemlerinde triggerlar tablolar arası birbirlerine referans yapan verilerin bütünlüğünü sağlamak maksadıyla kullanılırlardı. Anca SQL Server 6.5' dan itibaren bu işlem zaten Referantial Integrity tanımlamalarıyla sağlandığı için bu maksatla kullanılmaları gereksizdir.
Triggerlar genellikle değişik tablolar üzerinde bulunan ve birbirleri arasında mantıksal ilişkilere sahip verilerin tutarlılığını sağlamak üzere oluşturulurlar. htiyaca göre uygulamanın getirdiği bazı kuralları kontrol etmek için de kullanılabilirler.
Örneğin yeni bir sipariş kaydı işleneceği zaman, siparişlerin yanına iliştirilecek olan müşteri numarasının geçerli olup olmadığını kontrol edecek bir trigger oluşturulabilir. Aynı şekilde customers tablosundan bir müşteri silinmeye çalışıldığında, bu müşterinin başka tablolarda kendine bağlı bir kayıt bulunması durumunda işlemi iptal edecek ve kullanıcıya hata mesajı döndürecek bir trigger da yazılabilir.
Triggerların getirdiği en büyük avantaj otomatik olarak devreye girmeleridir. Veri modifikasyonuna ne sebep olursa olsun çalışırlar. Her trigger bir veya daha fazla veri değiştirme işleminde harekete geçecek şekilde tanımlanır. Örneğin bir trigger yalnızca update olayında çalışabileceği gibi, hem update hem de insert durumlarında da çalıştırılabilir. Ya da update, insert ve delete işlemlerinin her biri için ayrı ayrı triggerlar tanımlanabilir.
Triggerlarda parametre kullanılmaz ve harici olarak çalıştırılamazlar. Bunun anlamı triggerin tetiklenebilmesi için mutlaka tablo üstünde veri değişikliği yapmak gerektiğidir. SQL Server' da triggerlar aynı zamanda 16 seviyeye kadar birbirini tetikleyecek şekilde tanımlanabilirler.
Nispi olarak triggerlar daha az zaman kaybına neden olurlar. Triggerın çalışmasında zamanın çoğu diğ tablolara referans yaparken harcanır. Diğer tablolar bellekte bulunuyorsa referans işlemi hızlı olur ancak eğer diskten okunması gerekiyorsa işlem oldukça yavaşlar. leride bahsedeceğimiz deleted ve inserted tabloları daima bellekte yer alırlar. Triggerın performansı da tamamen diğer tabloların nerede bulunduğuna bağlıdır.
Triggerlar her zaman transaction' ın bir parçası olarak çalışırlar. Eğer transaction' ın herhangi bir bölümünde ya da triggerda bir hata oluşursa yapılan işlemler tümüyle geri alınır. Recursive olarak çalışmazlar. Yani bir tablonun herhangi bir sütununda yapılan değişiklik üzerine bir trigger çalışıp aynı tablonun başka bir sütünunda değişikliğe neden oluyorsa ikinci yapılan değişiklik trigger için tetiklenmeye neden olmaz. Yani buradaki update triggerı tekrar tekrar çalışmaz, yalnızca bir kez çalışır.
Triggerlar bir tabloya spesifik olarak tanımlanırlar. Sadece tablonun sahibi Create Trigger ve Drop Trigger işlemlerini yapmaya yetkilidir. Bu yetkiler başka birine aktarılamazlar. Bir tabloda en fazla üç tane trigger bulunabilir. Update trigger, Insert trigger ve Delete trigger. Her bir trigger yalnızca tek bir tabloya uygulanabilir. Bunu yanında tek bir trigger birden fazla işlemde harekete geçmek üzere tanımlanabilir.
Trigger kullanırken dikkate almamız gereken diğer hususları şöyle sıralayabiliriz:
* View ya da geçici tablolar üzerinde olutturulamazlar. Fakat bunlara referans yapabilirler.
* Çalıştıktan sonra bir kullanıcıya değer kümesi gönderemezler. Dolayısıyla SELECT ifadesi dahil edileceği zaman dikkatli olunmalıdır.
* Veri bütünlüğünü, farklı tablolardan birbirine referans yapan verilerin tutarlılığını sağlamak için ve uygulamanın ihtiyacına göre bazı kuralları tanımlamak üzere kullanılabilirler.
* İstenirse syscomments tablosunda şifrelenmiş şekilde saklanabilirler.
* CREATE, DROP, ALTER TABLE, ALTER DATABASE, SELECT INTO gibi ifadeler trigger içinde kullanılmazlar.
Inserted ve Deleted Tabloları
Triggerlar çalıştığı zaman Inserted ve Deleted tablolarını kullanırlar. Bu tabloların her ikisi de ana tabloyla yani triggerın tetiklendiği tabloyla aynı yapıya sahiptirler. Bu tablolar, mantıksal tablo şeklinde RAM' de bulunurlar. Ana tabloya bir kayıt eklendiği zaman bu kayıt aynı zamanda inserted tablosuna da eklenir. htiyacımız olduğu zaman yeni eklenen değerlere bu tablodan ulaşarak, bu bilgileri tutmak maksadıyla değişken tanımlamak zorunda kalmaktan kurtuluruz. Tablodan bir kayıt silindiğinde silinen kayıt deleted tablosunda saklanır. Update itlemi ise delete ve hemen ardından yapılmış bir insert işlemi olarak ele alınır. Bir kayıt update edildiğinde orjinal kayıt deleted tablosuna işlenir, değişen kayıt da inserted tablosunda ve ana tabloda saklanır.
Trigger Kullanım Örnekleri
Aşağıdaki örnekte tabloda insert veya update işlemi yapıldığı zaman kaç kaydın etkilendiğini gösteren bir hata oluşturulmaktadır.
CREATE TRIGGER trAddAuthor
ON authors
FOR INSERT, UPDATE
AS raiserror ("%d kayıt üzerinde değişiklik yapılmıştır",0,1,@@rowcount)
RETURN
@@ROWCOUNT değişkeni tabloda yapılan son işlem sonucunda kaç kaydın etkilendiğini tutan bir SQL Server değişkenidir. Değişik maksatlar için bu değişkenden etkili bir biçimde yararlanılabilir. Aşağıdaki triggerda @@ROWCOUNT performansı artırmak için kullanılmıştır. Tabloya eklenen kayıt miktarına göre hareket ederek tek bir kayıt eklendiğinde ekstra zaman kaybına neden olan GROUP BY ifadesini kullanmaksızın diğer tabloda değişikliği gerçekleştirir.
CREATE TRIGGER intrig
ON sales
FOR INSERT AS
/* @@rowcount değerinin kontrol edilmesi*/
IF @@rowcount = 1
UPDATE titles
SET ytd_sales = ytd_sales qty
FROM inserted
WHERE titles.title_id = inserted.title_id
ELSE
/* rowcount 1' den büyük olduğu zaman,
**GROUP BY ifadesi kullanılır */
UPDATE titles
SET ytd_sales = ytd_sales (SELECT SUM(qty)
FROM inserted
GROUP BY inserted.title_id
HAVING titles.title_id = inserted.title_id)
Triggerlar ile uygulamaya özel bazı kontrol edilebildiğini belirtmiştik. Aşağıdaki örnekte 20' den fazla satışı bulunan bir satış noktasının silinmesine izin vermeyen bir kural implemente edilmiştir.
CREATE TRIGGER trDelSales
ON tblSales
FOR DELETE AS
IF (SELECT COUNT(*) FROM deleted
WHERE deleted.qty >= 20) > 0
BEGIN
PRINT `Bu satış noktalarından birini silemezsiniz.'
PRINT `Bazılarının 20' den fazla satışı mevcuttur!'
PRINT `Transaction geri alınacaktır!'
ROLLBACK TRANSACTION
END
Triggerlar farklı tablolardan birbirine referans yapan verilerin bütünlüğünü sağlamak için kullanıldığında bir hususa dikkat edilmelidir. Veri üzerinde değitiklik olduğunda en son kontrol sırası triggera gelir. Eğ tabloda bir constraint tanımlanmış ise önce o kontrol edilir. Dolayısıyla bir kısıta uyulmadığı zaman trigger hiç bir zaman çalışmayacaktır. Ancak constraint tanımlanmamış tablolarda triggerlar etkili bir biçimde delete ve update işlemlerinin veri bütünlüğünü bozup bozmadığını kontrol edebilir.
CREATE TRIGGER trInsUpdSales
ON tblSales
FOR INSERT, UPDATE AS
IF (SELECT COUNT(*) FROM tblStores, inserted
WHERE tblStores.stor_id = inserted.stor_id) = 0
BEGIN
PRINT `Girmiş olduğunuz stor_id stores tablosunda'
PRINT `mevcut değildir. Kontrol ediniz!'
ROLLBACK TRANSACTION
END
Bu trigger sadece tek bir insert ya da update olayı gerçekleştiğinde kusursuz çalışır. Birden fazla kayıtla karşı karşıya kalındığı zaman yapılması gereken işlem daha farklı olacaktır. Aşağıdaki örnek için inserted tablosuna eklenen kayıt sayısı ve eklenen satış miktarı karşılaştırılır. Çoklu kayıt problemi bu şekilde çözülür.
CREATE TRIGGER trInsUpdSales
ON tblSales
FOR INSERT, UPDATE AS
DECLARE @rc int
SELECT @rc = @@rowcount
IF (SELECT COUNT(*) FROM tblStores, inserted
WHERE tblStores.stor_id = inserted.stor_id) = 0
BEGIN
PRINT `Girmiş olduğunuz stor_id stores tablosunda'
PRINT `mevcut değildir. Kontrol ediniz!'
ROLLBACK TRANSACTION
END
IF (SELECT COUNT(*) FROM tblSales, inserted
WHERE tblSales.stor_id = inserted.stor_id) <> @rc
BEGIN
PRINT `Girilen satışların bazılarının stor_id`
PRINT `değeri stores tablosunda bulunamamıştır!'
ROLLBACK TRANSACTION
END
Trigger ve Stored Procedure' lerı karşılaştırırsak her ikisi de önceden derlenmiş SQL ifadeleri olduğundan hemen hemen aynı hızda ve aynı overhead miktarına sahiptirler. SQL Server' ın işlem sırası, önce View ve Stored Procedure' lerı sonra Trigger' ları çalıştıracak şekildedir. Daha iyi performans elde etmek için mümkün olduğu kadar trigger seviyesine inilmemeye gayret edilmelidir. Eğer problemi stored procedure içinde yakalayabiliyorsak trigger yüzünden, yapılan her şeyin geri alınması gerekmez.
