<\/div>\n

Bu yaz\u0131mda sizlere Stored Procedure lerden bahsede\u011fce\u011fim.Normal sorgulardan daha h\u0131zl\u0131 \u00e7al\u0131\u015f\u0131rlar.\u00c7\u00fcnk\u00fc normal sorgu Execute edilirken Execute Plan i\u015flemi yap\u0131l\u0131r.Bu i\u015flem s\u0131ras\u0131nda hangi tablodan veri \u00e7ekilecek,hangi kolonlardan gelecek,bunlar nerede v.s gibi i\u015flemler yap\u0131l\u0131r.Bir sorgu her \u00e7al\u0131\u015ft\u0131r\u0131ld\u0131\u011f\u0131nda bu i\u015flemler aynen tekrar tekrar yap\u0131l\u0131r.Fakat sorgu stored procedur olarak \u00e7al\u0131\u015ft\u0131r\u0131l\u0131rsa bu i\u015flem sadece bir kere yap\u0131l\u0131r(ilk \u00e7al\u0131\u015ft\u0131rma esnas\u0131ndan).Di\u011fer \u00e7al\u0131\u015ft\u0131rmalarda bu i\u015flemler yap\u0131lmaz.Bundan dolay\u0131 h\u0131z ve performansta art\u0131\u015f sa\u011flan\u0131r.
\n–\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u0130nsert,update,select ve delete i\u015flemleri yap\u0131labilir.
\n–\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u0130\u00e7 i\u00e7e kullan\u0131labilir.
\n–\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 \u0130\u00e7lerinde fonksiyon olu\u015fturulabilir.<\/p>\n

Sorgular\u0131m\u0131z\u0131n d\u0131\u015far\u0131dan alaca\u011f\u0131 de\u011ferler parametre olarak stored procedurlere ge\u00e7irilebildi\u011finden dolay\u0131,sorgular\u0131m\u0131z\u0131n SQL INJECTION yemelerinide \u00f6nlemi\u015f oluruz.Bu y\u00f6nleriyle de daha g\u00fcvenlidirler.
\n\u015eimdi bir stored procedur olu\u015ftural\u0131m.Create ile olu\u015fturaca\u011f\u0131z.\u0130ki t\u00fcrl\u00fc olu\u015fturabiliriz.<\/p>\n

\r\ncreate procedure p_deneme\r\n(\r\n---Al\u0131nacak Parametreler---\r\n)\r\nas\r\n---Yaz\u0131lacak Sorgular,Kodlar,\u015eartlar,Fonksiyonlar,Bilmemneler---\r\n<\/pre>\n

Yukar\u0131da g\u00f6rd\u00fc\u011f\u00fcn\u00fcz gibi \u201ccreate procedure\u201d olarak tan\u0131mlayabiliriz.Bir di\u011fer \u015fema ise a\u015fa\u011f\u0131daki gibi;<\/p>\n

\r\ncreate proc p_deneme2\r\n(\r\n---Al\u0131nacak Parametreler---\r\n)\r\nas\r\n---Yaz\u0131lacak Sorgular,Kodlar,\u015eartlar,Fonksiyonlar,Bilmemneler---\r\n<\/pre>\n

\u201ccreate proc\u201d olarak tan\u0131mlayabiliriz.Fark bu kadar J
\n\u015eimdi stored procedur yap\u0131s\u0131n\u0131 bir \u00f6rnek ile inceleyelim.<\/p>\n

\r\ncreate proc p_deneme3\r\n(\r\n@id int \u2013 Aksi s\u00f6ylenmedi\u011fi taktirde bu parametrenin yap\u0131s\u0131 inputtur.\r\n)\r\nas\r\nselect * from Personeller where PersonelID = @id\r\n<\/pre>\n

Yukar\u0131da g\u00f6rd\u00fc\u011f\u00fcn\u00fcz yap\u0131da @id parametresi kullan\u0131lm\u0131\u015ft\u0131r.Bu s. Proc u a\u015fa\u011f\u0131daki \u015fekilde kullanabiliriz.<\/p>\n

\r\nexec p_deneme3 3\r\n<\/pre>\n

\u201cp_deneme3\u201d yazd\u0131ktan sonra bo\u015fluk b\u0131rak\u0131p yazd\u0131\u011f\u0131m \u201c3\u201d de\u011feri parametreye g\u00f6nderilen de\u011ferdir.Bu kodu Execute yaparsak e\u011fer \u201cPersonelID\u201d si \u201c3\u201d olan personelimin bilgileri gelecektir.
\nNOT : Prosed\u00fcr\u00fcn parametrelerini tan\u0131mlarken parantez kullanmak zorunlu de\u011fildir ama okunabilirli\u011fi art\u0131rmak i\u00e7in kullanmakta fayda var.
\nBir ba\u015fka \u00f6rnek yaparsak e\u011fer,<\/p>\n

\r\ncreate proc p_deneme4\r\n(\r\n@id int,\r\n@adbasharf varchar(50)\r\n)\r\nas\r\nselect * from Personeller where PersonelID&gt;@id and Adi like @adbasharf + '%'\r\n<\/pre>\n
\nKullan\u0131m\u0131<\/p>\n
\n\r\nexec p_deneme4 3,'a'\r\n<\/pre>\n
\nStored procedurler geriye de\u011fer g\u00f6nderebilirler.<\/p>\n
\n\r\ncreate proc p_deneme5\r\n(\r\n@id int,\r\n@adbasharf varchar(50)\r\n)\r\nas\r\nselect * from Personeller where Adi like @adbasharf + '%' and PersonelID&gt;3\r\nreturn @@rowcount\r\n<\/pre>\n
\nYukar\u0131daki \u00f6rnek olu\u015fturulan procedur de \u201creturn @@rowcount\u201d komutu ile geriye bu sorgu sonucunda ka\u00e7 eleman etkilenmi\u015f onu d\u00f6necektir.
\n\u015eimdi d\u00f6nd\u00fc\u011f\u00fc de\u011feri alal\u0131m.<\/p>\n
\n\r\nexec p_deneme5 3,'a' \u2013 Bu \u015fekilde kullan\u0131rsak e\u011fer gene prosed\u00fcr\u00fcm\u00fcz \u00e7al\u0131\u015facakt\u0131r.\r\ndeclare @geriyedonendeger varchar(50)\r\nexec @geriyedonendeger= p_deneme5 3,'a'\r\nselect @geriyedonendeger\r\n<\/pre>\n
\nE\u011fer yukardaki gibi kullan\u0131rsak prosed\u00fcr\u00fcm\u00fcz hem \u00e7al\u0131\u015facak hemde geriye d\u00f6nen de\u011feri \u201c@geriyedonendeger\u201d adl\u0131 degi\u015fkene at\u0131p onuda bize sunacakt\u0131r.
\nOUTPUT \u0130LE DE\u011eER D\u00d6ND\u00dcRME<\/span><\/strong>
\nBunun a\u00e7\u0131klamas\u0131n\u0131 yapabilmek i\u00e7in \u00f6ncelikle bir \u00f6rnek yapal\u0131m.<\/p>\n
\n\r\ncreate proc outputdeneme\r\n(\r\n@id int,\r\n@adi varchar(50) output\r\n)\r\nas\r\nselect @adi=Adi from Personeller where PersonelID=@id\r\n<\/pre>\n
\nYukardaki yap\u0131ya dikkat ederseniz \u201c@adi\u201d de\u011fi\u015fkenini output olarak belirttik.\u201d@id\u201d de\u011fi\u015fkeni belirtilmedi\u011fi i\u00e7in varsay\u0131lan olarak input tur.as komutundan sonra select sorgusunda \u201c@adi\u201d de\u011fi\u015fkenine \u201cAdi\u201d kolonundan gelen de\u011feri att\u0131k.\u015eimdi kullan\u0131m\u0131n\u0131 g\u00f6relim,<\/p>\n
\n\r\ndeclare @deger varchar(50)\r\nexec outputdeneme 3,@deger output\r\nselect @deger\r\n<\/pre>\n
\nBurdaki mant\u0131k \u015fudur.@adi de\u011fi\u015fkenine Adi kolonundan de\u011ferler g\u00f6nderiliyor.@adi de\u011fi\u015fkenindeki de\u011feri @deger de\u011fi\u015fkenine set edip d\u0131\u015far\u0131ya geri yollayabiliyoruz.
\nSoru\u2019\u2019
\nD\u0131\u015far\u0131dan ald\u0131\u011f\u0131 isim,soyisim ve \u015fehir bilgilerini Personeller tablosunda ilgili kolonlara ekleyen proc yaz\u0131n\u0131z.<\/p>\n
\n\r\ncreate proc Sp_Ekle\r\n(\r\n@isim varchar(20),\r\n@soyisim varchar(20),\r\n@sehir varchar(20)\r\n)\r\nas\r\ninsert Personeller(Adi,SoyAdi,Sehir) values(@isim,@soyisim,@sehir)\r\n<\/pre>\n
\nKullanal\u0131m<\/p>\n
\n\r\nexec Sp_Ekle 'Gen\u00e7ay','Y\u0131ld\u0131z','Artvin'\r\n<\/pre>\n
\nDikkat etmemiz gereken birka\u00e7 nokta var.
\n–\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Prosed\u00fcr\u00fcn b\u00fct\u00fcn parametrelerine de\u011fer g\u00f6nderilmelidir.
\n–\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Olmayan parametreye de\u011fer g\u00f6nderilmemelidir.
\nStored prosed\u00fcrlerde her hangi bir parametreye de\u011fer g\u00f6nderilmedi\u011fi taktirde varsay\u0131lan bir de\u011fer o parametreye g\u00f6nderilebilir.B\u00f6ylelikle b\u00fct\u00fcn parametrelere d\u0131\u015far\u0131dan de\u011fer g\u00f6ndermek zorunda kalmay\u0131z.<\/p>\n
\n\r\ncreate proc sp_deneme\r\n(\r\n@ad varchar(50) = '\u0130simsiz',\r\n@soyad varchar(50) = 'Soyads\u0131z',\r\n@sehir varchar(50) = '\u015eehir girimemi\u015f'\r\n)\r\nas\r\ninsert Personeller(Adi,SoyAdi,Sehir) values(@ad,@soyad,@sehir)\r\n<\/pre>\n
\nYukar\u0131da g\u00f6rd\u00fc\u011f\u00fcn\u00fcz gibi parametreleri olu\u015ftururken varsay\u0131lan de\u011ferlerinide girebiliyoruz.E\u011fer herhangi bir parametreye de\u011fer g\u00f6nderilmezse varsay\u0131lan de\u011fer verilecektir.
\nKullan\u0131m\u0131n\u0131 g\u00f6sterelim.<\/p>\n
\n\r\nexec sp_deneme 'Gen\u00e7ay','Y\u0131ld\u0131z','Ankara'\r\n<\/pre>\n
\nB\u00fct\u00fcn parametrelere de\u011fer g\u00f6nderebiliyoruz.Varsay\u0131lan de\u011ferler burada devreye girmeyecektir.<\/p>\n
\n\r\nexec sp_deneme\r\n<\/pre>\n
\nNormal olarak bu \u015fekilde \u00e7al\u0131\u015fmamas\u0131 laz\u0131m.Ama parametrelerde varsay\u0131lan de\u011ferler oldu\u011fu i\u00e7in bu \u015fekilde \u00e7al\u0131\u015facakt\u0131r.Ve de\u011fer olarak varsay\u0131lan de\u011ferler ilgili kolonlara i\u015flenecektir.<\/p>\n
\n\r\nexec sp_deneme 'Gen\u00e7ay'\r\n<\/pre>\n
\nE\u011fer bu \u015fekilde \u00e7al\u0131\u015ft\u0131r\u0131rsak Ad\u0131 kolonuna Gen\u00e7ay i\u015flenecek di\u011fer kolonlara de\u011fer g\u00f6nderilmedi\u011fi i\u00e7in varsay\u0131lan de\u011ferler g\u00f6nderilecektir.
\nExec komutu ile normal sorgu \u00e7al\u0131\u015ft\u0131rabiliriz.<\/p>\n
\n\r\nexec select * from Personeller\r\n<\/pre>\n
\nTabi bu \u015fekilde sorgu \u00e7al\u0131\u015ft\u0131ramay\u0131z.Sorguyu parantez ve t\u0131rnak i\u015fareti i\u00e7inde yazmal\u0131y\u0131z.<\/p>\n
\n\r\nexec ('select * from Personeller')\r\n<\/pre>\n
\nBu \u015fekilde \u00e7al\u0131\u015facakt\u0131r.
\n\u015eimdi stored proc. ile veritaban\u0131,tablo vs. gibi nesneler olu\u015ftural\u0131m.Tabi d\u0131\u015fardan tablo ya da veritaban\u0131 ismini alacak,kolonlar\u0131n isimlerini ve tiplerinide d\u0131sardan alacak.vsvs i\u015fte gerekli teferruatlar\u0131 d\u0131sardan alacak bir nesne yapal\u0131m.
\nTablo olu\u015ftural\u0131m.<\/p>\n
\n\r\ncreate proc Sp_TabloOlustur\r\n(\r\n@tabloadi nvarchar(50),\r\n@kolon1adi nvarchar(50),\r\n@kolon1tipi nvarchar(50),\r\n@kolon2adi nvarchar(50),\r\n@kolon2tipi nvarchar(50),\r\n@kolon3adi nvarchar(50),\r\n@kolon3tipi nvarchar(50)\r\n)\r\nas\r\ncreate table @tabloadi\r\n(\r\n@kolon1adi @kolon1tipi,\r\n@kolon2adi @kolon2tipi,\r\n@kolon3adi @kolon3tipi\r\n)\r\n<\/pre>\n
\nYukardaki \u015fekilde stored proc. derlenmeyecektir.\u00c7\u00fcnk\u00fc tablo ad\u0131 olarak ger\u00e7ek bir de\u011fer istiyor.Parametreden gelen de\u011feri kabul etmiyor.Tablo olu\u015fturulan k\u0131sm\u0131 (as den sonraki kodlar\u0131) exec(\u2018\u2019) komutu i\u00e7inde yazmam\u0131z gerekiyor.
\nA\u015fa\u011f\u0131daki gibi,<\/p>\n
\n\r\ncreate proc Sp_TabloOlustur2\r\n(\r\n@tabloadi nvarchar(50),\r\n@kolon1adi nvarchar(50),\r\n@kolon1tipi nvarchar(50),\r\n@kolon2adi nvarchar(50),\r\n@kolon2tipi nvarchar(50),\r\n@kolon3adi nvarchar(50),\r\n@kolon3tipi nvarchar(50)\r\n)\r\nas\r\nexec ('create table ' + @tabloadi + '(' + @kolon1adi + ' ' + @kolon1tipi + ',' + @kolon2adi + ' ' + @kolon2tipi + ',' + @kolon3adi + ' ' + @kolon3tipi + ')')\r\n<\/pre>\n
\nKullan\u0131m\u0131<\/p>\n
\n\r\nexec Sp_TabloOlustur2 'Gencay','Id','int primary key identity(1,1)','Adi','nvarchar(50)','Soyadi','nvarchar(50)'\r\n<\/pre>\n
\nYukardaki kodu execute ettikten sonra art\u0131k \u201cGencay\u201d ad\u0131ndan bir tablomuz olu\u015fmu\u015ftur.\u201dId\u201d ad\u0131nda bir kolona sahip.Bu kolon primary key \u00f6zelli\u011fine sahip ve identity \u00f6zelli\u011fi var.nvarchar tipinde \u201cAdi\u201d ve \u201cSoyadi\u201d kolonlar\u0131 olu\u015fmu\u015ftur.<\/p>\n","protected":false},"excerpt":{"rendered":"
Bu yaz\u0131mda sizlere Stored Procedure lerden bahsede\u011fce\u011fim.Normal sorgulardan daha h\u0131zl\u0131 \u00e7al\u0131\u015f\u0131rlar.\u00c7\u00fcnk\u00fc normal sorgu Execute edilirken Execute Plan i\u015flemi yap\u0131l\u0131r.Bu i\u015flem s\u0131ras\u0131nda hangi tablodan veri \u00e7ekilecek,hangi kolonlardan gelecek,bunlar nerede v.s gibi i\u015flemler yap\u0131l\u0131r.Bir sorgu her...<\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"open","ping_status":"open","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[22],"tags":[124],"class_list":["post-179","post","type-post","status-publish","format-standard","hentry","category-veritabani","tag-stored-procedure"],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/179","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/comments?post=179"}],"version-history":[{"count":0,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/179\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/media?parent=179"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/categories?post=179"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/tags?post=179"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}