Derinlemesine yazılım eğitimleri için kanalımı takip edebilirsiniz...

Transact Sql-Stored Procedures(SAKLI YORDAMLAR)

Yazar: Gençay · Yayımlanmış 14 Mart 2012 · Güncellendi 27 Nisan 2012

Bu yazımda sizlere Stored Procedure lerden bahsedeğceğim.Normal sorgulardan daha hızlı çalışırlar.Çünkü normal sorgu Execute edilirken Execute Plan işlemi yapılır.Bu işlem sırasında hangi tablodan veri çekilecek,hangi kolonlardan gelecek,bunlar nerede v.s gibi işlemler yapılır.Bir sorgu her çalıştırıldığında bu işlemler aynen tekrar tekrar yapılır.Fakat sorgu stored procedur olarak çalıştırılırsa bu işlem sadece bir kere yapılır(ilk çalıştırma esnasından).Diğer çalıştırmalarda bu işlemler yapılmaz.Bundan dolayı hız ve performansta artış sağlanır.
–          İnsert,update,select ve delete işlemleri yapılabilir.
–          İç içe kullanılabilir.
–          İçlerinde fonksiyon oluşturulabilir.

Sorgularımızın dışarıdan alacağı değerler parametre olarak stored procedurlere geçirilebildiğinden dolayı,sorgularımızın SQL INJECTION yemelerinide önlemiş oluruz.Bu yönleriyle de daha güvenlidirler.
Şimdi bir stored procedur oluşturalım.Create ile oluşturacağız.İki türlü oluşturabiliriz.

create procedure p_deneme
(
---Alınacak Parametreler---
)
as
---Yazılacak Sorgular,Kodlar,Şartlar,Fonksiyonlar,Bilmemneler---

Yukarıda gördüğünüz gibi “create procedure” olarak tanımlayabiliriz.Bir diğer şema ise aşağıdaki gibi;

create proc p_deneme2
(
---Alınacak Parametreler---
)
as
---Yazılacak Sorgular,Kodlar,Şartlar,Fonksiyonlar,Bilmemneler---

“create proc” olarak tanımlayabiliriz.Fark bu kadar J
Şimdi stored procedur yapısını bir örnek ile inceleyelim.

create proc p_deneme3
(
@id int – Aksi söylenmediği taktirde bu parametrenin yapısı inputtur.
)
as
select * from Personeller where PersonelID = @id

Yukarıda gördüğünüz yapıda @id parametresi kullanılmıştır.Bu s. Proc u aşağıdaki şekilde kullanabiliriz.

exec p_deneme3 3

“p_deneme3” yazdıktan sonra boşluk bırakıp yazdığım “3” değeri parametreye gönderilen değerdir.Bu kodu Execute yaparsak eğer “PersonelID” si “3” olan personelimin bilgileri gelecektir.
NOT : Prosedürün parametrelerini tanımlarken parantez kullanmak zorunlu değildir ama okunabilirliği artırmak için kullanmakta fayda var.
Bir başka örnek yaparsak eğer,

create proc p_deneme4
(
@id int,
@adbasharf varchar(50)
)
as
select * from Personeller where PersonelID&gt;@id and Adi like @adbasharf + '%'

Kullanımı

exec p_deneme4 3,'a'

Stored procedurler geriye değer gönderebilirler.

create proc p_deneme5
(
@id int,
@adbasharf varchar(50)
)
as
select * from Personeller where Adi like @adbasharf + '%' and PersonelID&gt;3
return @@rowcount

Yukarıdaki örnek oluşturulan procedur de “return @@rowcount” komutu ile geriye bu sorgu sonucunda kaç eleman etkilenmiş onu dönecektir.
Şimdi döndüğü değeri alalım.

exec p_deneme5 3,'a' – Bu şekilde kullanırsak eğer gene prosedürümüz çalışacaktır.
declare @geriyedonendeger varchar(50)
exec @geriyedonendeger= p_deneme5 3,'a'
select @geriyedonendeger

Eğer yukardaki gibi kullanırsak prosedürümüz hem çalışacak hemde geriye dönen değeri “@geriyedonendeger” adlı degişkene atıp onuda bize sunacaktır.
OUTPUT İLE DEĞER DÖNDÜRME
Bunun açıklamasını yapabilmek için öncelikle bir örnek yapalım.

create proc outputdeneme
(
@id int,
@adi varchar(50) output
)
as
select @adi=Adi from Personeller where PersonelID=@id

Yukardaki yapıya dikkat ederseniz “@adi” değişkenini output olarak belirttik.”@id” değişkeni belirtilmediği için varsayılan olarak input tur.as komutundan sonra select sorgusunda “@adi” değişkenine “Adi” kolonundan gelen değeri attık.Şimdi kullanımını görelim,

declare @deger varchar(50)
exec outputdeneme 3,@deger output
select @deger

Burdaki mantık şudur.@adi değişkenine Adi kolonundan değerler gönderiliyor.@adi değişkenindeki değeri @deger değişkenine set edip dışarıya geri yollayabiliyoruz.
Soru’’
Dışarıdan aldığı isim,soyisim ve şehir bilgilerini Personeller tablosunda ilgili kolonlara ekleyen proc yazınız.

create proc Sp_Ekle
(
@isim varchar(20),
@soyisim varchar(20),
@sehir varchar(20)
)
as
insert Personeller(Adi,SoyAdi,Sehir) values(@isim,@soyisim,@sehir)

Kullanalım

exec Sp_Ekle 'Gençay','Yıldız','Artvin'

Dikkat etmemiz gereken birkaç nokta var.
– Prosedürün bütün parametrelerine değer gönderilmelidir.
– Olmayan parametreye değer gönderilmemelidir.
Stored prosedürlerde her hangi bir parametreye değer gönderilmediği taktirde varsayılan bir değer o parametreye gönderilebilir.Böylelikle bütün parametrelere dışarıdan değer göndermek zorunda kalmayız.

create proc sp_deneme
(
@ad varchar(50) = 'İsimsiz',
@soyad varchar(50) = 'Soyadsız',
@sehir varchar(50) = 'Şehir girimemiş'
)
as
insert Personeller(Adi,SoyAdi,Sehir) values(@ad,@soyad,@sehir)

Yukarıda gördüğünüz gibi parametreleri oluştururken varsayılan değerlerinide girebiliyoruz.Eğer herhangi bir parametreye değer gönderilmezse varsayılan değer verilecektir.
Kullanımını gösterelim.

exec sp_deneme 'Gençay','Yıldız','Ankara'

Bütün parametrelere değer gönderebiliyoruz.Varsayılan değerler burada devreye girmeyecektir.

exec sp_deneme

Normal olarak bu şekilde çalışmaması lazım.Ama parametrelerde varsayılan değerler olduğu için bu şekilde çalışacaktır.Ve değer olarak varsayılan değerler ilgili kolonlara işlenecektir.

exec sp_deneme 'Gençay'

Eğer bu şekilde çalıştırırsak Adı kolonuna Gençay işlenecek diğer kolonlara değer gönderilmediği için varsayılan değerler gönderilecektir.
Exec komutu ile normal sorgu çalıştırabiliriz.

exec select * from Personeller

Tabi bu şekilde sorgu çalıştıramayız.Sorguyu parantez ve tırnak işareti içinde yazmalıyız.

exec ('select * from Personeller')

Bu şekilde çalışacaktır.
Şimdi stored proc. ile veritabanı,tablo vs. gibi nesneler oluşturalım.Tabi dışardan tablo ya da veritabanı ismini alacak,kolonların isimlerini ve tiplerinide dısardan alacak.vsvs işte gerekli teferruatları dısardan alacak bir nesne yapalım.
Tablo oluşturalım.

create proc Sp_TabloOlustur
(
@tabloadi nvarchar(50),
@kolon1adi nvarchar(50),
@kolon1tipi nvarchar(50),
@kolon2adi nvarchar(50),
@kolon2tipi nvarchar(50),
@kolon3adi nvarchar(50),
@kolon3tipi nvarchar(50)
)
as
create table @tabloadi
(
@kolon1adi @kolon1tipi,
@kolon2adi @kolon2tipi,
@kolon3adi @kolon3tipi
)

Yukardaki şekilde stored proc. derlenmeyecektir.Çünkü tablo adı olarak gerçek bir değer istiyor.Parametreden gelen değeri kabul etmiyor.Tablo oluşturulan kısmı (as den sonraki kodları) exec(‘’) komutu içinde yazmamız gerekiyor.
Aşağıdaki gibi,

create proc Sp_TabloOlustur2
(
@tabloadi nvarchar(50),
@kolon1adi nvarchar(50),
@kolon1tipi nvarchar(50),
@kolon2adi nvarchar(50),
@kolon2tipi nvarchar(50),
@kolon3adi nvarchar(50),
@kolon3tipi nvarchar(50)
)
as
exec ('create table ' + @tabloadi + '(' + @kolon1adi + ' ' + @kolon1tipi + ',' + @kolon2adi + ' ' + @kolon2tipi + ',' + @kolon3adi + ' ' + @kolon3tipi + ')')

Kullanımı

exec Sp_TabloOlustur2 'Gencay','Id','int primary key identity(1,1)','Adi','nvarchar(50)','Soyadi','nvarchar(50)'

Yukardaki kodu execute ettikten sonra artık “Gencay” adından bir tablomuz oluşmuştur.”Id” adında bir kolona sahip.Bu kolon primary key özelliğine sahip ve identity özelliği var.nvarchar tipinde “Adi” ve “Soyadi” kolonları oluşmuştur.