Temel SQL Sorguları III

SQL Sorguları Bölüm 3

Temel SQL sorguları ile ilgili olan yazı dizimin bu bölümünde Subquery, Transaction, Trigger, Stored Procedure, Function ve View konularından bahsedeceğim.

 

Insert : Bir ilişki (tablo) içerisine yeni bir veri eklemek için kullanılır.

 

Örnek

Insert Into Ogrenci (OgrenciNo,Ad,Soyad) Values (132252,'Taner','Şahin')
// Eğer tüm alanlara sırasıyla değerlerimizi veriyorsak
Insert Into Values (132252,'Taner','Şahin')

 

Update : Bir ilişki içerisinde bulunan verilerin değiştirilmesi için kullanılır.

 

Örnek

// İzmir şehrinde bulunan ve hotmail adresi 'ts@tanersahin.com' olan kaydın ögrencino değerini
 132252 yapan örnek  bir update sorgusu
Update Ogrenci  Set Sehir = 'İzmir', EMail = 'ts@tanersahin.com'  Where OgrenciID = 132252

 

Örnek

// Aşağıdaki sorguda ise ilişkideki tüm ürünlerin fiyatları %40 arttırılmıştır.
Update Urunler Set Fiyat = (Fiyat * 1.40)

 

Delete : İlişki içerisinde bulunan bir kaydı veya kayıtları silmek için kullanılır.
 

Örnek

// İlişkide 132252 numaralı kayıt silinir
Delete From Ogrenci Where OgrenciID = 132252
Delete From Ogrenci ---> Tablodaki tüm verileri siler. Truncate kullanılarakta silinebilir.

 

İçiçe Sorgular (Subquery)

Bazen karmaşık ve uzun sorgular yazmak durumunda kalabiliriz. Farklı durumlardan alınacak sonuçlara göre sorgular çalıştırılması gerekebilir. İç içe sorgular kullanarak bu tip işlemlerin aşama aşama yapılması sağlanır. Bir sorgulama işlemi, diğer sorgulama işleminin sonucunu kullanabilir. İç içe sorgularda, asıl sorguya sonuç getiren iç sorgu bazen tek değer üretebilirken, bazı durumlarda da birden fazla değer üretebilir.

 

Örnek

// Aşağıdaki sorgu Production.Product tablosundaki ListPrice değeri en yüksek olan
// ürünü veya ürünleri getirir
Select Name, Color, ListPrice From Production.Product 
Where ListPrice = (Select Max(ListPrice) From Production.Product)

 

Bir sorgu içerisinde başka bir sorgu tek değer üretebildiği gibi birden fazla değerde üretebilir. Bu tip durumlarda Where ile birlikte In ifadesi kullanılarak alt sorgunun getirdiği değerlerin hepsi işleme tabi tutulur.

 

Örnek

// İç kısımdaki sorgu sonucunda getirilen değerler dıştaki Select ifadesinde
// kullanılarak bölüm adı B harfi ile başlayan bölümlerde okuyan öğrenciler listelenmiştir.
Select FirstName,LastName From Person.Person Where FirstName In
(Select FirstName From Person.Person Where FirstName Like 'En%' )

 

Örnek

// Aşağıdaki örnekte BusinessEntityID değeri 1540 olan kayıtların ListPrice değerini
2 kat arttıran bir iç içe update sorgusu.
Update Production.Product Set ListPrice = ListPrice * 2 Where ProductID In (Select ProductID From Purchasing.ProductVendor Where BusinessEntityID = 1540)

 

Örnek

// Delete sorgularında da iç içe sorgu yazarak silmek istediğimiz veriyi silebiliriz.
Delete From TabloAd Where KolonAd In (Select KolonAd From TabloAd Where KolonAd > 100)

 

All komutu :  Parametre olarak içerisine aldığı iç sorgudan geriye dönen sonuçların tamamı karşılaştırma kriterine uyarsa true uymazsa false değer döndürür.

 

Örnek

// SalesOrderHeader ilişkisindeki siparişlerin tamamı 5000'den büyük olan müşterileri listeler.
Select * From Sales.Customer As C  Where( 5000 < All(Select TotalDue From Sales.SalesOrderHeader As SOH
Where (SOH.CustomerID = C.CustomerID )))

 

Transactionlar 

Bazı durumlarda veritabanı üzerinde birbirine bağlı ard arda çok sayıda işlem yapılması gerekebilir. Örneğin; bir ilişkide bir kayıt silindiğinde, bu silme işlemine bağlı olarak başka bir ilişkideki bazı kayıtların değiştirilmesi veya silinmesi gibi. Sorgulardan birinin başarılı bir şekilde çalışması ancak başka bir sorgunun başarısız olması veri bütünlüğünü bozabilir. Böyle bir durumda, yapılacak tüm işlemlerin tek bir işlem gibi düşünülmesi ve işlemlerden herhangi birinin gerçekleşememesi durumunda diğer tüm işlemlerin de iptal edilmesi gerekir. Daha küçük parçalara ayrılamayan iş parçacıklarına transaction (işlem bloğu) ismi verilmektedir. SQL Server’da tek başına çalışabilen Select, Insert, Update, Delete gibi komutlar da varsayılan olarak bir transaction içerisinde çalışmaktadır. Komut çalışırken bir hata oluşursa işlem geri alınır. SQL Server’da yapılacak transaction işlemleri olabildiğince kısa tutulmalıdır. Aynı anda, aynı ilişkiler üzerinde birden fazla transaction talebinde bulunulursa, transction’lardan biri diğerini bekleyeceği için işlemin sonuçlandırılması daha fazla zaman alacaktır.

 

Örnek

Begin Tran   // Transaction başlatılır
Update Kayitlar
Set isim = 'Taner'
Where id = '99'
Update Kayitlar
Set isim = 'Şahin'
Where sehir = 'izmir'
If @@ROWCOUNT = 2   // Eğer 2 kayıt etkilenmişse  
Commit Tran   // Transaction tamamlanır.  
Else   // Değilse   
Rollback Tran   // Transaction iptal edilir işlemler geri alınır

 

Örnek

Begin Tran   // Transaction başlatılır
Declare @i int   // int türünden i adlı bir değişken tanımlanır
Delete From Kayitlar Where id>36
Set @i = @@ROWCOUNT   // Etkilenen kayıt sayısını i değişkenime aktarırım
If @i = 1   // Eğer etkilenen kayıt sayısı 1 ise
Begin
Print 'Kayıt silindi'
Commit   // Kayıt silinir ve işlem tamamlanır
End
Else     // Değilse
Begin
Print 'İşlemler geri alındı'
Rollback   // İşlemler geri alınır ve transaction iptal edilir
End

 

Bazı durumlarda bir transaction sonlanmadan o alan  içerisinde yeni bir transaction oluşturabilirsiniz. En altta Commit veya Rollback edilen transaction diğer transactionları da Commit veya Rollback eder.  Transactionlara isimler vererek sorgularınızın daha anlamlı görünmesini sağlayabilirsiniz.

 

Örnek

Begin Tran Tran1   // Transaction işlemine başlanır
Delete From Kayit Where ID=10248
Begin Tran Tran2 //  İkinci bir transaction işlemine başlanır
Delete From Kayitlar Where ID=10249
Commit Tran Tran1   // Birinci transaction işlemini tamamla dememizle 
// beraber içinde bulunan ikinci transaction işlemimiz de tamamlanmış olur

 

Örnek

// Try bloğunda bir hata olursa kontrol Catch bloğuna geçer. Bir hata yoksa Catch blogu devreye girmez.
Begin Try
Begin Transaction   // Transaction işlemimize başlarız
Update Kayitlar Set Number-=45 Where ID =4
Update Kayitlar Set Number+=34  Where ID =5
Commit Tran     // Transaction işlemi tamamlanır
End Try
Begin Catch
Print 'Hata oluştu'
Rollback Tran   // Transaction işlemi iptal edilir
End Catch

 

Örnek

Begin Tran Tran1   // Transaction işlemimize başlarız
Delete From Kayitlar Where ID=10252
Save Tran Save1   // Transaction işlemi kaydedilir.
Delete From Kayitlar Where ID=10253   // Kayıt silinmez çünkü altındaki Rollback komutu                                       
Rollback Tran Save1  // Son kaydettiğimiz yer olan Save1 kısmını göstermektedir     
Commit Tran Tran1   // Transaction işlemi tamamlanır sadece 1 kayıt silinmiş oldu.

 

VİEW

View sanal bir ilişki gibi çalışan veritabanı nesnesidir. View, bir SQL ifadesinin sonuç kümesinden oluşan sanal bir ilişkidir. Aynı gerçek ilişkilerde olduğu gibi satırlar ve kolonlardan oluşur. View içerisindeki alanlar, veritabanındaki bir veya daha fazla ilişkiden gelen alanlardan oluşur. View üzerinde Where ve Join gibi ifadeler kullanılabilir ve birden fazla ilişkiden oluşsa dahi, veriler tek bir ilişkideymiş gibi sorgulanabilir. Alter komutunu kullanarak söz konusu View üzerinde değişiklik yapabiliriz ve Drop komutuyla da söz konusu View nesnesini silebiliriz. Bir View nesnesi çalıştırmak için "Select * From ViewAdı" şeklinde bir sorgu yazarız.

 

Örnek

// UnitPrice değeri ortalamadan yüksek olan ürünler getirilir
Create View LuksUrunler
As
Select ProductName,UnitPrice From Production.Product Where UnitPrice>(Select Avg(UnitPrice) From Production.Product)

 

Örnek

// HumanResources.Employee tablosunda Evli olan erkekleri gösterir.
Create View EvliOlanErkekler
As
Select * From HumanResources.Employee Where Gender = 'M' And MaritalStatus = 'M'

 

STORED PROCEDURE

Stored Procedure Programlama dillerindeki fonksiyonlar gibi parametre alabilirler. Alınan parametreler kullanılarak bir sorgu çalıştırıp cevap döndürebilirler. Stored Procedure'ler server tarafında saklanmalarından dolayı daha hızlı çalışırlar. Çünkü; bir stored procedure ilk çalıştırıldığında derlenir ve sonraki çağrılarda terkrar derlenmeden çalışır. Oysa ki SQL ifadeleri her çalıştırıldığında server tarafında bu ifade derlenir. Bu da zaman kaybına sebep olur. Bir Store Procedure çalıştırmak için ise Exec StoreProcedureAdı (Girilecek Değerler)

 

Örnek

// BolumdekiOgrenciler isminde, int tipinden parametre alan bir stored procedure yazmış olduk.
Create Proc BolumdekiOgrenciler   // Procedure de diyebiliriz
(
@BolumID Int
)
As         
Select * From Ogrenci Where BolumID = @BolumID

 

Örnek

// Int tipinden Ort isimli parametre alan ve aldığı parametre 44'den küçük ise Başarısız
// büyük ise başarılı şeklinde sonuç bölümüne yazı yazdıran bir stored procedure.
Create Procedure Hesapla
@Ort Int
As
If @Ort>44 Print ‘Başarılı’
If @Ort<45 Print ‘Başarısız’
Go

 

Örnek

// Varchar tipinden veri alan @Ara isimli parametre alan ve aldığı parametre Null değilse
// içerisinde @Ara isimli parametrenin geçtiği kayıtlar getirilir.
Create Procedure OgrAra
@Ara Varchar(10)=Null
As
If @Ara Is Not Null
Select * From Sinif
Where Ad Like ‘%’+@Ara+’%’
Go

 

Örnek

// int cinsinden @yas isimli parametremiz eğer 19'dan büyük ise hemen altında bulunan
// select sorgusu değilse Else blogunun altındaki select sorgusu çalışır.
Create Procedure Listele
@yas Int
As
If (yas > 19)
Select * From Kayitlar Where YAS > @yas Order By YAS
Else
Select * From Kayitlar Where YAS > @yas And Eriskin=1 Order By YAS

 

Örnek

// Diğer parametrelerin dışında @Secim adlı parametreye verdiğiniz değere göre
// kayıt ekleme güncelleme ve silme işlemlerinden birtanesini yaptırmış olursunuz.
Create Procedure Kullanici
@Secim Int,
@ID Bigint,
@KullaniciAd Nvarchar(500),
@KullaniciSifre Nvarchar(500)
As
If @Secim=1
Begin
Insert Into Kullanicilar
(Kullanici_Ad,Kullanici_Sifre) Values (@KullaniciAd,@KullaniciSifre)
End
If @Secim=2
Begin
Update Kullanicilar
Set Kullanici_Ad=@KullaniciAd,Kullanici_Sifre=@KullaniciSifre Where ID=@ID
End
If @Secim=3
Begin
Delete From Kullanicilar
Where ID=@ID
End

 

Function

T-SQL içerisinde yer alan programlama öğelerinden function, aynı programlama dillerindeki gibi benzer ihtiyaçlarda tekrar tekrar aynı kodu yazma olayının önüne geçerek sorgulamalar sırasında daha esnek bir kullanım sunar.

 

Örnek

// Kod blogu dışarıdan parametre olarak gelen değerin yüzde 18’ini hesaplayıp sonuç döndürecektir.
Create Function KdvHesapla
(
 @Fiyat Money
)
 Returns Money
 As
 Begin  
 Return @Fiyat * 0.18
 End 
// Örnek olarak Select Ad, Fiyat, KdvHesapla (Fiyat) As KDV From Urun şeklinde fonksiyonumuzu çalıştırabiliriz.

 

Örnek

// Parametre alarak e-posta adresi üreten function
Create Function MailAdresiUret
(
@Name Varchar(50),
@Surname Varchar(50)
)
Returns Varchar(100)
Begin
Declare @Mail Varchar(100)
Set @Mail=Lower (@Name+'.'+@Surname+'@microsoft.com')
Return @Mail
End
// Örnek olarak Select Ad, Soyad, MailAdresiUret (Ad,Soyad) As Mail From Kayitlar
şeklinde sorgumuzu çalıştırabiliriz.

 

Örnek

// ProductSubcategoryID değeri @ID parametresi olan kayıtları getiren bir function
// Bir select sorgusunun sonucu döneceği için dönüş kısmına Table yazıyoruz.
Create Function Getir
(
@ID Int
)
Returns Table
As
Return
Select * From Production.Product
Where ProductSubcategoryID=@ID

 

Örnek

// Returns kısmında ‘TABLE’ yazmak yerine, manuel olarak tablo oluşturup, sonuçları bu tabloya insert ederiz.
Create Function Getir
(
@ID Int
)
Returns @Table Table
(
UrunID Int,
Ad Nvarchar(50),
Fiyat Money,
Renk NVARCHAR(100)
)
As
Begin
Insert Into @Table
Select UrunID,Ad,Fiyat,Renk From Urunler
Where Urun_ID=@ID
Return
End
// Select * From Getir (Rakam) şeklinde fonksiyonumuzu çalıştırırız.

 

Örnek

// Eğer parametre ‘UZUN’ ise, kişinin tam adını (ünvan+ad+soyad), mail adresini 
ve telefon bilgisini dönsün. Eğer ‘KISA’ ise, sadece soyadını, mail adresini ve 
telefon bilgisini dönsün. IsNull ifadesiyle söz konusu alandaki veri Null ise 
boşluk (' ') olarak belirtilmesi istenmiştir.
Create Function BilgileriGetir
(
@Len Varchar(10)
)
Returns @Table Table
(
Name Nvarchar(100),
Email Nvarchar(50),
Phone Nvarchar(25)
)
As
Begin
If @Len = 'UZUN'
Begin
Insert Into @Table
Select Isnull(Unvan,' ') + ' ' + Ad + ' ' + Isnull(KisaltmaAd,' ') + ' ' + Soyad,Mail,Telefon
From Kisiler
End
Else If @Len = 'KISA'
Begin
Insert Into @Table
Select Ad,Mail,Telefon From Kisiler
End
Return
End
// Select * From BilgileriGetir ('UZUN') veya Select * BilgileriGetir('KISA') 
şeklinde fonksiyonumuzu çalıştırırız.

 

Her ne kadar Stored Procedure ile Function arasında benzerlikler var gibi görünsede aralarında bir çok fark vardır.

 

Stored Procedure ve Function arasındaki farklar 

1) Stored Procedure 0 ya da daha fazla değer döndürebiliyor fakat Function tek bir değer veya ilişki (tablo) döndürebilir.

2) Stored Procedure transaction yapısını destekler fakat Function desteklemez.

3) Stored Procedure içinde Function çalıştırılabilir ancak Function içinde Stored Procedure çalıştırılamaz.

4) İçerisinde Where deyimi bulunan Select sorguları içerisinde Stored Procedure çalıştıramayız ancak Function çalıştırabiliriz.

5) Stored Procedure içerisinde try-catch yapısı kullanabiliriz fakat Function içerisinde kullanamayız.

 

Triggerlar

Trigger tetikleyici manasına gelir programlarınızda belirli bir işlem gerçekleştiğinde programımızı tetiklemeye yarar. Triggerlar bir çeşit store procedure tek farkı kendiliğinden çalışmasıdır.

 

Söz dizimi

Create Trigger TriggerAd
On TabloAd
After veya Instead Of Triggerler (Insert veya Update veya Delete)
As
Sql Sorgusu

 

Trigger Aktif / Pasif yapmak için

Disable Trigger TriggerAd On TabloAd
Enable Trigger TriggerAd On TabloAd

Trigger silmek için --> Drop Trigger TriggerAd

 

After Triggerler: After Triggerler, kendiyle ilişkili işlem gerçekleştikten hemen sonra devreye girer. Sadece tablolar için tanımlanır.

 

Örnek

// Insert Trigger devreye girdikten sonra Inserted tablosunda yeni eklenen kayıtların
bir kopyası tutulur. Inserted tablosu, asıl tablonun yapısal bir kopyası olup 
Trigger sonlanana kadar saklanır. Deneme amaçlı Urunler tablosuna bir insert işlemi
yaptığınızda tablo ekrana gelir.
Create Trigger InsertTrigger
On Urunler
After Insert
As
Select * From Inserted

 

Örnek

// Delete Triggeri çalıştıktan sonra silinen kayıt Deleted adlı yapay bir tabloya kaydedilir.
Deleted tablosunun Inserted tablosundan farkı, asıl tablodan silinen kayıt artık Deleted tablosunda
yer almaktadır. Urunler tablosunda bir delete işlemi yaptığınızda trigger çalışır ve tablo ekrana gelir.
Create Trigger DeleteTrigger
On Urunler
After Delete
As
Select * From Deleted

 

Örnek

// Update Trigger devreye girdiğinde Inserted sahte tablosu asıl tablodaki kayıtlardan,
düzenlenmiş kayıtların kopyasını, Deleted sahte tablosu ise kayıtların düzenleme işleminden
önceki hallerini tutar
Create Trigger UpdateTrigger
On Urunler
After Update
As
Select * From Deleted
Select * From Inserted

 

Örnek

// Kayıt eklemeyi iptal eden Trigger
Create Trigger Goster On Kayitlar
After Insert
As
Begin
Rollback Transaction
End

 

Instead Of Triggerler: Belirlenen işlem gerçekleşirken devreye girer ve kendi içinde tanımlanan komutları çalıştırırlar. Yani, belirlenen işlemin yerine geçer.

 

Örnek

Create Trigger AfterInsert
On Urunler
Instead Of Insert
As
Print('Instead Of Trigger Tetiklendi..')

 

Örnek

// Başka bir örnek olarak silinen ürün sipariş tablosunda mevcut ise silme işlemini iptal edelim.
Create Trigger UrunSil On Urunler
After Delete
As
Begin
Declare @ID Int
Select @ID=UrunID From Deleted
If Exists(Select * From Siparisler Where UrunID=@ID)
Begin
Print 'Ürün siparişlerde mevcut,Silinenmez'
Rollback Transaction
Return
End
End

 

Örnek

// Kullanıcının birden fazla kayıt silmesini engellemek için yazılan bir Delete Trigger örneği
Create Trigger TriggerDelete
On Kisiler
For Delete
As
Declare @Sayi Int
Select @Sayi = Count(*) From Deleted
If @Sayi>1
Begin
Print 'Birden fazla kayıt silinemez'
Rollback Transaction
End