<\/div>\n

Bu yaz\u0131m\u0131zda Transact Sql’e devam ediyoruz.De\u011fi\u015fkenler,\u0130f \u015fart\u0131,ge\u00e7ici tablolar ve while d\u00f6ng\u00fcs\u00fc gibi konulara de\u011finece\u011fim.
\nDE\u011e\u0130\u015eKENLER<\/span><\/strong>
\nHer programlama dilinde oldu\u011fu gibi T-SQL de de de\u011fi\u015fken vard\u0131r.A\u015fa\u011f\u0131daki yap\u0131 bir de\u011fi\u015fken tan\u0131mlamas\u0131d\u0131r.<\/p>\n

\n
\r\ndeclare @isim varchar(60)\r\n<\/pre>\n
\n
De\u011fi\u015fkenler \u201cdeclare\u201d ile tan\u0131mlan\u0131p sonras\u0131nda de\u011fi\u015fkene ad verilir.De\u011fi\u015fken adlar\u0131 \u201c@\u201d ile ba\u015flar.Son olarakta de\u011fi\u015fken tipini yazmam\u0131z yeterlidir.<\/p>\n
\n
\r\ndeclare @ad varchar(60),@soyad varchar(60),@yas int\r\n<\/pre>\n
\n
Tek sat\u0131rda birden \u00e7ok farkl\u0131 tipte de\u011fi\u015fken tan\u0131mlanabilir.<\/p>\n
\n
\r\ndeclare @isim varchar(50)\r\nset @isim = 'Gen\u00e7ay'\r\nselect @isim\r\n<\/pre>\n
\n
De\u011fi\u015fkenimize \u201cset\u201d komutu sayesinde de\u011fer aktarabiliyoruz.Yukardaki \u015fekilde select sorgusunda de\u011fi\u015fkeninin de\u011ferini alabiliyoruz.<\/p>\n
\n
\r\nprint @isim --ya da print komutunu kullanarak mesaj olarakta de\u011ferini okutabiliyoruz.\r\n<\/pre>\n
\n
Ama dikkat etmemiz gereken bir nokta var.T-SQL de se\u00e7ti\u011fimiz sat\u0131r\u0131 Execute yaparsak \u00e7al\u0131\u015f\u0131r.De\u011fi\u015fkenlerde buna dikkat etmeliyiz.\u201ddeclare\u201d ile ba\u015flayan sat\u0131r\u0131 \u00f6nce Execute yap\u0131p ondan sonra o de\u011fi\u015fken \u00fczerinden i\u015flem yapamay\u0131z.Her i\u015flemde b\u00fct\u00fcn yap\u0131y\u0131 se\u00e7ip Execute yapmam\u0131z gerekiyor.
\nBATCH KAVRAMI (GO)<\/strong><\/span>
\nTek pencerede yaz\u0131lan birbirinden ayr\u0131 sorgular\u0131n ger\u00e7ekten birbirinden ayr\u0131 oldu\u011funu derleyiciye g\u00f6stermek i\u00e7in \u201cGo\u201d kullan\u0131l\u0131r.
\nIF YAPISI<\/strong><\/span>
\nHer programlama dilinin olmazsa olmaz\u0131 if yap\u0131s\u0131 T-SQL de de bulunmaktad\u0131r.
\nHemen bir \u00f6rnek g\u00f6sterelim,<\/p>\n
\n
\r\ndeclare @ad nvarchar(50)\r\nset @ad = 'Gen\u00e7ay'\r\nif @ad='Gen\u00e7ay'\r\nprint 'Evet Gen\u00e7ay'\r\nelse\r\nprint 'Hay\u0131r Gen\u00e7ay de\u011fil'\r\n<\/pre>\n
\n
\u015eimdi bir \u00f6rnek daha g\u00f6sterebilmek i\u00e7in \u201cNorthwind\u201d veritaban\u0131na d\u00f6nelim ve \u201cM\u00fc\u015fteriler\u201d tablosunda \u00e7al\u0131\u015fal\u0131m.
\nHi\u00e7 Amerikal\u0131 m\u00fc\u015fterim var m\u0131?(Bu sorunun cevab\u0131n\u0131 evet ya da hay\u0131r olarak verdirece\u011fiz.Amerikal\u0131 m\u00fc\u015fteriler USA ile belirtilmi\u015ftir.)<\/p>\n
\n
\r\nselect * from Musteriler where Ulke='USA'\r\nif @@ROWCOUNT>0\r\nprint 'Evet Amerikal\u0131 m\u00fc\u015fteri var.'\r\nelse\r\nprint \u2018Hay\u0131r Amerikal\u0131 m\u00fc\u015fteri bulunmamakta'\r\n<\/pre>\n
\n
Select sorgusunda d\u00f6nen kay\u0131t say\u0131s\u0131n\u0131 @@ROWCOUNT verdi\u011fi i\u00e7in ona g\u00f6re \u015fart ko\u015ftuk.
\nSoru\u2019\u2019
\nPersoneller tablosuna bir isim ve soyisim kaydetmeye \u00e7al\u0131\u015f\u0131n. E\u011fer bu isim ve soyisimde bir kay\u0131t tabloda\u00a0 zaten var ise, b\u00f6yle bir kay\u0131t var desin, yok ise eklesin ve eklendi mesaj\u0131 versin.<\/p>\n
\n
\r\ndeclare @isim nvarchar(50),@soyisim nvarchar(50)\r\nset @isim = 'Gen\u00e7ay'\r\nset @soyisim = 'Y\u0131ld\u0131z'\r\nselect * from Personeller where Adi=@isim and SoyAdi=@soyisim\r\nif @@ROWCOUNT>0\r\nprint 'Bu isimden ya da soyisimden veritaban\u0131nda bulunmaktad\u0131r.'\r\nelse\r\ninsert Personeller(Adi,SoyAdi) values(@isim,@soyisim)\r\n<\/pre>\n
\n
Yukardaki yap\u0131y\u0131 ilk \u00e7al\u0131\u015ft\u0131rd\u0131\u011f\u0131n\u0131zda kay\u0131t i\u015flemi yap\u0131lacak,ikinci \u00e7al\u0131\u015ft\u0131rd\u0131\u011f\u0131n\u0131zda kay\u0131t i\u015flemi yap\u0131lmayacakt\u0131r.print ile verilen mesaj g\u00f6z\u00fckecektir.
\nNot: if ya da else den sonra bir sat\u0131rdan fazla kod i\u015flenecekse bunlar\u0131 \u201cbegin\u201d ve \u201cend\u201d komutlar\u0131 aras\u0131na yazmal\u0131y\u0131z.C# taki {} scopelarla ayn\u0131 i\u015fi g\u00f6r\u00fcr.
\nEXISTS<\/strong><\/span>
\nBir sorgunun sonucunda geriye tablo d\u00f6n\u00fcyorsa e\u011fer \u201ctrue\u201d, d\u00f6nm\u00fcyorsa \u201cfalse\u201d geriye d\u00f6nd\u00fcren bir metod gibi d\u00fc\u015f\u00fcn\u00fclebilir.<\/p>\n
\n
\r\nif exists(select * from personeller where adi='Gen\u00e7ay')\r\nprint 'True'\r\nelse\r\nprint 'False'\r\n<\/pre>\n
\n
Yukar\u0131da select sorgusu bir tablo d\u00f6n\u00fcyor ve exists metoduda bunu true olarak alg\u0131l\u0131yor.
\nWHILE D\u00d6NG\u00dcS\u00dc<\/strong><\/span>
\nT-SQL de sadece while d\u00f6ng\u00fcs\u00fc bulunmaktad\u0131r.<\/p>\n
\n
\r\ndeclare @i int\r\nset @i = 10\r\nwhile @i>=1\r\nbegin\r\nprint @i\r\nset @i=@i-1\r\nend\r\n<\/pre>\n
\n
GE\u00c7\u0130C\u0130 TABLOLAR (TEMPORARY TABLES)<\/span><\/strong>
\nGenellikle bir sql server \u00fczerinde farkl\u0131 lokasyonlarda 1 den fazla ki\u015finin \u00e7al\u0131\u015ft\u0131\u011f\u0131 durumlarda ya da verilerin test ama\u00e7l\u0131 ge\u00e7ici bir yerlerde tutulmas\u0131,i\u015flenmesi amac\u0131yla kullan\u0131l\u0131rlar.Bildi\u011fimiz tablo yap\u0131s\u0131n\u0131n ayn\u0131s\u0131 sa\u011flarlar,tek farklar\u0131 fiziksel olarak olu\u015fmazlar.Sadece bellekte ge\u00e7ici olarak olu\u015fturulurlar.Select,insert v.s i\u015flemleri yap\u0131labilir.\u0130li\u015fki kurulabilir.Sunucu kapat\u0131ld\u0131\u011f\u0131nda ya da oturum sahibi oturumu kapatt\u0131\u011f\u0131nda bellekten silinirler.<\/p>\n
\n
\r\nselect Adi,SoyAdi into otomatiktablo from Personeller\r\n<\/pre>\n
\n
Bu \u015fekilde \u201cotomatiktablo\u201d ad\u0131nda fiziksel bir tablo olu\u015fturulabiliyor.Ve ka\u00e7 tane kolon \u00e7ektiysek i\u00e7inde otomatik olarak o kadar kolon olu\u015fuyor.Sadece primary key,foreign key ler olu\u015fmuyor.<\/p>\n
\n
\r\nselect Adi,Soyadi into #otomatiktablogecici from Personeller\r\n<\/pre>\n
\n
Bu \u015fekilde ise \u201c#\u201d i\u015fareti sayesinde \u201cotomatiktablogecici\u201d ad\u0131nda ge\u00e7ici bir tablo olu\u015fturuyor.Fiziksel bir tablo olmuyor.Ge\u00e7ici olarak bellekte olu\u015fur.<\/p>\n
\n
\r\nselect * from #otomatiktablogecici\r\nselect Adi from #otomatiktablogecici\r\nselect * from #otomatiktablogecici where Adi='Gen\u00e7ay'\r\ninsert #otomatiktablogecici(Adi,SoyAdi) values('c','d')\r\ndelete from #otomatiktablogecici where Adi='Gen\u00e7ay'\r\nupdate #otomatiktablogecici set Adi='abc' where Adi='c'\r\n<\/pre>\n
\n
Yukarda g\u00f6rd\u00fc\u011f\u00fcn\u00fcz gibi ge\u00e7ici tablo \u00fczerinde her t\u00fcrl\u00fc i\u015flemi yapabiliyoruz.
\n–\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 # ile olu\u015fturulan tablo,o an sql servarda oturum a\u00e7m\u0131\u015f ki\u015finin sunucu belle\u011finde olu\u015fur.
\n–\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Sadece oturum a\u00e7an \u015fah\u0131s kullanabilir.
\n–\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 E\u011fer oturum a\u00e7an \u015fah\u0131s sql servardan disconnet olursa bu tablo bellekten silinir.<\/p>\n
\n
\r\nselect * into ##otomatiktablogecici2 from Personeller\r\nselect * from ##otomatiktablogecici2\r\n<\/pre>\n
\n
Yukarda g\u00f6rd\u00fc\u011f\u00fcn\u00fcz gibi iki \u201c##\u201d den ba\u015fl\u0131yorsa e\u011fer,<\/p>\n
–\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 ## ile olu\u015fturulan tablo,o an sql servarda oturum a\u00e7m\u0131\u015f ki\u015finin sunucu belle\u011finde olu\u015fur.
\n–\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Bu tabloyu oturum a\u00e7an \u015fah\u0131s ve onun sql servar\u0131na d\u0131\u015fardan ula\u015fan 3. \u015fah\u0131slar kullanabilir.
\n–\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 E\u011fer oturum a\u00e7an \u015fah\u0131s sql servardan disconnet olursa bu tablo bellekten silinir.
\n–\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0\u00a0 Di\u011fer b\u00fct\u00fcn \u00f6zellikleri # ile olu\u015fturulan tablo ile ayn\u0131d\u0131r.
\nUNIQUEIDENTIFIER VER\u0130 T\u0130P\u0130<\/strong><\/span>
\nBuda ayn\u0131 int,varchar v.s. gibi bir veri tipidir.Ald\u0131\u011f\u0131 de\u011fer rakamlar ve harflerden olu\u015fan \u00e7ok b\u00fcy\u00fck bir say\u0131d\u0131r.Bundan dolay\u0131 bu kolona ayn\u0131 de\u011ferin birden fazla gelmesi neredeyse imkans\u0131zd\u0131r.O y\u00fczden tekil bir veri olu\u015fturmak i\u00e7in kullan\u0131l\u0131r.<\/p>\n
\n
\r\ncreate table denemeasd\r\n(\r\nid uniqueidentifier,\r\nadi nvarchar(50),\r\nsoyadi nvarchar(50)\r\n)\r\n<\/pre>\n
\n
Yukarda g\u00f6rd\u00fc\u011f\u00fcn\u00fcz gibi \u201cid\u201d kolonu \u201cuniqueidentifier\u201d tipindendir.
\n\u201cNEWID()\u201d fonksiyonunun \u00fcretti\u011fi de\u011ferde \u201cuniqueidentifier\u201d dir.<\/p>\n
\n
\r\ninsert denemeasd values(NEWID(),'Gen\u00e7ay','Y\u0131ld\u0131z')\r\nselect * from denemeasd\r\n<\/pre>\n","protected":false},"excerpt":{"rendered":"
Bu yaz\u0131m\u0131zda Transact Sql’e devam ediyoruz.De\u011fi\u015fkenler,\u0130f \u015fart\u0131,ge\u00e7ici tablolar ve while d\u00f6ng\u00fcs\u00fc gibi konulara de\u011finece\u011fim.<\/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":[115,116,117,118,45,119,52],"class_list":["post-172","post","type-post","status-publish","format-standard","hentry","category-veritabani","tag-batch","tag-declare","tag-exists","tag-go","tag-if","tag-uniqueidentifier","tag-while"],"amp_enabled":true,"_links":{"self":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/172","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=172"}],"version-history":[{"count":0,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/posts\/172\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/media?parent=172"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/categories?post=172"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.gencayyildiz.com\/blog\/wp-json\/wp\/v2\/tags?post=172"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}