Veri Tabanı Yönetim Sistemleri 1
Ders 11
TSQL'e Giriş:
Prosedür, Fonksiyon ve Tetikleyici
Yrd. Doç. Dr. Altan MESUT
Trakya Üniversitesi
Bilgisayar Mühendisliği Bölümü
Değişken tanımlama ve kullanma
• Değişken tanımalama DECLARE ile yapılır:
DECLARE @maas money
DECLARE @sayi1 int, @sayi2 int
• Değişkene değer atama SET ile yapılır:
SET @sayi1 = 0, @sayi2 = 100
SET @maas = $10
• SQL ifadesinde değişkene değer atama:
SELECT @maas = Maas FROM Personel
WHERE PerNo = 991
Kod bloğu
• Pascal ve Delphi dillerinde benzer olarak
TSQL'de de kod bloğu tanımlamak için
BEGIN…END kullanılır.
• C, C++, C# ve Java dillerindeki { … } ifadesi ile
benzerdir.
IF…ELSE
• Bir ifadenin veya bloğun belirli bir koşula bağlı
olarak çalışması istenen durumlarda IF, eğer
tersi durumda başka bir ifadenin veya bloğun
çalışması gereken durum varsa da ELSE
kullanılır:
IF (@BurutMaas > 5000)
SET @NetMaas = @BurutMaas*0.80
ELSE
SET @NetMaas = @BurutMaas*0.85
1.2.3 CASE … WHEN
• Çoklu koşullarda kullanılan C# ve Java'daki
Switch … Case , VB'deki Select … Case gibidir.
SELECT CASE BasariNotu
WHEN 4 THEN 'AA'
WHEN 3.5 THEN 'BA'
WHEN 3 THEN 'BB'
WHEN 2.5 THEN 'CB'
END as HarfNotu
FROM Notlar
WHILE
• While döngüsü belirli bir koşul sağlandığı sürece bir
ifadeyi veya bloğu tekrar eder.
• Aşağıdaki döngü Personel tablosundaki ortalama maaş
5000'den küçük olduğu sürece tüm personelin maaşını
%10 arttırır. Ayrıca en yüksek maaş 10.000'den büyük
olursa da Break ile döngüden çıkılır.
WHILE (SELECT AVG(Maas) FROM Personel) < 5000
BEGIN
UPDATE Personel SET Maas = Maas * 1.1
IF (SELECT MAX(Maas) FROM Personel) > 10000 BREAK
ELSE CONTINUE
END
SELECT MAX(Maas) AS "En Yüksek Maaş" FROM Personel
Stored Procedure (Saklı Yordam)
• Saklı yordamlar veritabanında saklanan ve gerektiğinde ismi
ile çağrılabilen kod bloklarıdır.
• Programlama dillerinde olduğu gibi, SQL'de de değer
döndüren prosedürlere fonksiyon (function) denir.
• Prosedürler RETURN ile değer döndürmeseler de OUTPUT
parametreleri kullanılırsa dışarı değer aktarabilirler.
• Aynı SQL ifadesini defalarca çağırdığınızda her seferinde
sentaks kontrolü ve derleme yapılırken, bu ifade bir saklı
yordam içinde yer aldığında, derlenmiş bir halde
veritabanında saklanacağı için performans artışı olur.
Stored Procedure (Devam …)
• Sunucu üzerinde tutulduğundan, yükü istemci tarafına değil
de, sunucu tarafına yükleyerek te performansı arttırır.
(Sunucunuz güçlüyse bu seçimi tercih edebilirsiniz.)
• Belirli girdi ve çıktı parametreleri olduğu için, saklı yordamların
kullanımı ile güvenlik açısından kendimizi sağlama almış
oluruz.
• Ağ trafiğini azaltır. İstemci tarafından birçok satıra sahip SQL
komutunun sunucuya gitmesindense, sadece saklı yordamın
adının sunucuya gitmesi ağı daha az meşgul etmiş olur.
Saklı Yordam Örneği (1)
CREATE PROCEDURE KomisyonVer
@PerNo numeric(18)
AS
BEGIN
DECLARE @Maas numeric(10,2)
DECLARE @Komisyon numeric(9)
SELECT @Maas = Maas FROM Personel
WHERE PerNo = @PerNo
if(@Maas > 5000) set @Komisyon = 1000
else set @Komisyon = 500
UPDATE Personel SET Komisyon = @Komisyon
WHERE PerNo = @PerNo
END
Saklı Yordam Örneği (2)
CREATE PROCEDURE HerkeseKomisyonVer
Bu örnekte kullanılan CURSOR, RecordSet
AS
gibidir. Eğer sorgudan dönen sonuç tek bir
BEGIN
değer olmayıp çok satır ve çok sütunlu
DECLARE @PerNo numeric;
ise, değişkende saklayamayacağımız için
DECLARE @Maas numeric(10,2);
CURSOR'da saklarız.
DECLARE @Komisyon numeric(9);
DECLARE Maaslar CURSOR FOR SELECT PerNo, Maas FROM Personel;
OPEN Maaslar
FETCH NEXT FROM Maaslar INTO @PerNo, @Maas
WHILE @@FETCH_STATUS = 0
BEGIN
if(@Maas > 5000) set @Komisyon = 1000
else set @Komisyon = 500
UPDATE Personel SET Komisyon = @Komisyon
WHERE PerNo = @PerNo;
FETCH NEXT FROM Maaslar INTO @PerNo, @Maas
END
CURSOR'da bir sonraki kayda gitmek için
CLOSE Maaslar
FETCH NEXT kullanılır. FETCH_STATUS sıfır
END
ise CURSOR'un sonuna gelinmiş demektir.
Sistem Saklı Yordamları
• SQL Server'da birçok işletimsel görev sistem saklı
yordamları ile gerçekleştirilebilir.
• Her VT altında çok sayıda sistem SP tanımlıdır.
Bunlardan bazıları:
– sp_help: nesneler hakkında bilgi verir
– sp_who: sisteme bağlı kullanıcılar, oturum ve işlem
bilgileri öğrenilir
– sp_spaceused: parametre olarak verilen tablonun kaç
satırı olduğu, onun için rezerve edilen ve onun
kullandığı disk büyüklükleri hakkında bilgi verir.
Parametre verilmezse geçerli veritabanının kapladığı
büyüklükler hakkında bilgi verir.
sp_help ile Personel tablosu hakkında
bilgi edinme
sp_help @objname = 'Personel'
NOT: Prosedürler aslında exec (veya execute) komutu ile çağrılır. Ama SQL Server
Management Studio'da yazmak zorunda değiliz.
Sistem Fonksiyonları
• Her SQL Server veritabanında
sistem fonksiyonlarının tanımları
Programmability\Functions\System
Functions altında mevcuttur
• Sorgularda kullandığımız çok-satır
alıp tek satır döndüren Avg, Sum
Max, Min ve Count gibi
fonksiyonlar Aggregate Functions
altındadır.
Kullanıcı Tanımlı Fonksiyonlar
• Sistem fonksiyonlarına ilave olarak kullanıcılar
da 3 farklı türde fonksiyon tanımlayabilir:
– Table Valued Functions: Tablo döndürebildiği için
sorguda tablo gibi kullanılabilen (from sonrasında
yazılabilen) fonksiyonlar
– Scalar Valued Functions: bir yada birkaç
parametre alıp, tek bir değer döndüren
fonksiyonlar
– Aggregate Functions: Sum, Avg gibi çok değer alıp
tek değer döndüren fonksiyonlar
Fonksiyon Örneği
CREATE FUNCTION NetMaasHesapla
( @BurutMaas numeric(10,2) )
RETURNS numeric(10,2)
AS
BEGIN
DECLARE @NetMaas numeric(10,2)
IF (@BurutMaas > 5000)
SET @NetMaas = @BurutMaas*0.80
ELSE
SET @NetMaas = @BurutMaas*0.85
RETURN @NetMaas
END
Fonksiyonu Kullanma
• Kullanıcı tanımlı fonksiyonlar da, yerleşik SQL
fonksiyonları gibi sorgu içinde kullanılabilirler
(SP kullanılamaz).
• Yerleşik (built in) fonksiyonlardan farklı olarak
öncesinde şema ismi (dbo) yazılmalıdır.
Select PerNo, Ad, Soyad, Maas AS [Bürüt Maaş],
dbo.NetMaasHesapla(Maas) AS [Net Maaş]
From Personel;
Prosedür ile Fonksiyon arasındaki
10 Temel Fark:
Trigger (Tetikleyici)
• Bir tabloda değişiklik olduğunda otomatik olarak
başlatılan prosedürlere DML Trigger, veritabanı
tanımlarında değişiklik olduğunda tetiklenenlere ise
DDL Trigger denir.
• DML Trigger insert, delete veya update ifadelerin
belirtilen tabloda oluşması gibi bir olaydan ve bir
hareketten (ilgili prosedür) oluşur.
– Örneğin; personel tablosundan bir kaydın silinmesi olayı
gerçekleştiğinde, eski_personel tablosuna personelin
numarasını ve işten ayrılma tarihini belirtmek için o anki
tarihi kaydeden bir prosedür yazılması ile bir DML Trigger
oluşturulabilir.
Enable/Disable & Inserted/Deleted
• Trigger'ın tetiklenmesini engellemek için Disable trigger
<triggerAdı> on <tabloAdı> kullanılabilir. Tekrar aktif
etmek için aynı ifadede Disable yerine Enable kullanılır.
– Her tablonun altındaki Triggers düğümünden ilgili trigger
üzerine sağ tıklanarak Enable/Disable seçimi de yapılabilir.
• Sadece DML Trigger içinden kullanılabilen ve geçici
olarak hafızada saklanan inserted ve deleted adında iki
tablo vardır.
– INSERT işleminde yeni eklenen kayıt inserted tablosunda
saklanır.
– DELETE işleminde silinen kayıt deleted tablosunda saklanır.
– UPDATE işleminde ise güncelleme öncesi eski değer
deleted tablosunda, değişen yeni değer inserted
tablosunda saklanır.
DML Trigger Örneği (1)
CREATE TRIGGER TrigDegisimTarihi
Gerektiğinde UPDATE
ON Personel
işlemlerinde FROM
AFTER UPDATE
kullanılabilir.
AS
declare @Personel numeric(18)
SELECT @Personel = PerNo FROM inserted
UPDATE Personel SET tarih = GETDATE()
WHERE Personel.PerNo = @Personel
--Değişken tanımlamadan da yapılabilir:
UPDATE Personel SET tarih = GETDATE()
FROM inserted
WHERE Personel.PerNo = inserted.PerNo
DML Trigger Örneği (2)
CREATE TRIGGER TrigMaasDegisimKaydi
ON Personel
UPDATE fonksiyonu içine aldığı alanın güncellenmesi
durumunda true döndürür. Bu satır silinirse Personel
AFTER UPDATE
tablosunda başka alanlar güncellense bile aşağıdaki
AS
ifade yürütülür (ki Maas aynı kaldı ise gereksiz olur)
if UPDATE(Maas)
INSERT INTO LogTable VALUES (
(Select PerNo from deleted),
(Select Maas from deleted),
(Select Maas from inserted),
GETDATE()
)
LogTable tablosunda PerNo char türünde ama Personel
tablosunda numeric (deleted tablosunda da). Bu durum
sorun yaratmaz. Diğer alanlar da char türünde olabilirdi.
DML Trigger'da INSTEAD OF kullanımı
• Eğer trigger tanımında INSTEAD OF yer alırsa
tetikleyen olay aslında gerçekleştirilmez, onun
yerine verilen prosedürdeki işlem yapılır (DML
Trigger'lar olaydan sonra, INSTEAD OF olanlar
ise olaydan önce tetiklenir)
CREATE TRIGGER DerslerDelete
"Edit 200 Top Rows" kullanarak veya DELETE
ON Dersler
komutu ile kayıt sildiğinizde Dersler
INSTEAD OF DELETE
tablosundan silme işlemi yapıldı gibi görünür
ama sorguladığınızda silinmediği anlaşılır.
AS
Print 'Silme işlemi şu anda kapalı'
DDL Trigger
• CREATE, DROP, ALTER gibi DDL işlemlerini geçici
bir süre askıya almak, veya bu işlemlerden sonra
bir prosedürün yürütülmesini sağlamak amacıyla
DDL Trigger kullanılabilir.
CREATE TRIGGER DDL_Trigger ON DATABASE
FOR DROP_TABLE, ALTER_TABLE
AS
Tablonun sahibi olmayanlar için
aşağıdaki kod bloğu çalıştırılacak
IF IS_MEMBER ('db_owner') = 0
BEGIN
PRINT 'Tablolar üzerinde drop ve alter yapamazsınız'
ROLLBACK TRANSACTION
END
DDL Trigger ile INSTEAD OF kullanılamaz. Bu nedenle, eğer
Transaction Rollback ile geri alınmaz ise yukarıdaki mesaj
görüntülenir, fakat DROP/ALTER işlemi engellenmemiş olur.
LOGON Trigger
• Kullanıcı sisteme bağlanırken tetiklenen sunucu
düzeyinde trigger'lar da yaratılabilir.
CREATE TRIGGER <triggerAdı>
ON ALL SERVER [WITH <seçenek>]
FOR LOGON AS …
DML Trigger
DDL Trigger
LOGON Trigger
Download

Ders 11 - TSQL, Prosedür, Fonksiyon ve Tetikleyici