VTYS Genel Tekrar (T-SQL)

Transaction

Transaction olayını bir banka örneği ile anlatalım, diyelim ki bir müşteri kendi hesabından başka bir hesaba 1500 TL para transferi gerçekleştiriyor. Bu işlemin iki adımı vardır. İlk adım kişinin hesabından transfer edilecek olan 1500 TL hesabından düşülmeli, ikinci adım ise gönderdiği hesaba +1500 TL eklenmesi işlemi, bunlar birbiriyle ilişkili durumlardır. Bu işlemlerden birisi gerçekleşirken diğeri gerçekleşmez ise büyük sorunlar yaşanabilir, transfer edilen tutar 15.000 TL de olabilirdi! Bu gibi durumlarda eğer bir sorun var ise işlemde ROLLBACK  denilen bir işlem uygulanır, bu işlem yapılan işlemleri tablolara fiziksel olarak işlenmesini önler ve geri alır. Tabi bir de COMMIT işlemi var. Bu işlem ise yapılan bütün işlemleri fiziksel tablolara kayıt eder, işlemi normal bir şekilde tamamlar.

Mesela:


begin transaction

update bolum set ckgrade=120

select * from bolum

rollback

bolum tablosundan ckgrade alanının hepsini 120 olarak değiştiren bir ifade, rollback yazarsak alttaki select ile listelendiğinde görürüz fakat fiziksel tablo değişmez.

Banka örneği için bir savepoint oluşturulabilir, savepoint’e örnek vermek gerekirse önce bir diagram çizelim;

akis

Şimdi sql ile yazalım;


begin transaction

save transaction deneme

update bolum set ckgrade=121

select * from bolum

rollback transaction deneme

select * from bolum

update bolum set bol_adi='burak'

select * from bolum

rollback

select * from bolum

burada save transaction deneme diyerek deneme save noktasını oluşturduk, hata denetimi yapmadık direk rollback transaction deneme dedik ve ckgrade alanı 121 oldu. Altta ise rollback kullanarak bol_adi alanına burak girilmesini engelledik, sonuç olarak ilk ifade gözükecektir fakat commit edilmediği için sadece görüntü olacaktır.

T-SQL(TRANSACT SQL)

 Veritabanı ile ilgili veri işleme ve düzenleme özelliklerine sahip standart SQL ifadeleri programlamaya yönelik ifadeleri barındırmadığı için kullanıldığı projeye göre zaman zaman yetersiz kalmıştır. T-SQL geliştirilmesiyle bu sorun çözülmüştür.

DEĞİŞKEN TANIMLAMA

 Aynı programlama dillerinde olduğu gibi T-SQL sayesinde de değişken tanımlayıp istediğimiz işleme sokabiliyoruz.

Mesela:


declare @sayi1 int , @sayi2 int, @sonuc int

set @sayi1=3

set @sayi2=6

set @sonuc=@sayi1+@sayi2

select @sonuc as sonuc

Sistem Değişkenleri

@@ERROR: Aktif bağlantılar için meydana gelen son hatanın hata numarasını içerir. Herhangi bir hata oluşmamışsa 0 değerini içerir.

@@SERVICENAME: Sql Server’ın kullandığı ve register ‘da kayıtlı olan Windows servis ismini içerir.

@@SERVERNAME: Sql Server’ın çalıştığı yerel sunucu ismini içerir.

@@VERSION: Sql Server’ın versiyon bilgisini içerir.

@@LANGUAGE: Sql Server için gerekli olan dili içerir.

@@LANGID: Sql Server için gerekli olan dilin sayısal karşılığını içerir.

@@MAX_CONNECTIONS: İzin verilen maksimum bağlantı sayısı.

@@SPID: Aktif kullanıcı için verilen oturum ID numarasını içerir.

@@DATEFIRST: Sql Server için ayarlı olan haftanın ilk gününün sayısını verir.

@@ROWCOUNT: Son kullanılan Sql ifadesi sonucu etkilenen satır sayısını verir.

@@CONNECTIONS: Sql Server’ın ilk çalışmasından bu yana bağlantı sayısını verir.

@@CPU_BUSY: Sql Server’ın son çalışmasından itibaren durmadan çalıştığı süreyi milisaniye cinsinden verir.

@@IDLE: Sql Server’ın son çalışmasından bu yana boş kaldığı süreyi verir.

@@PACK_RECEIVED: Sql Server’ın ilk çalışmasından bu yana ağdan aldığı paketlerin sayısı

@@PACK_SENT: Sql Server’ın ilk çalışmasından bu yana ağa gönderdiği paketlerin sayısı.

@@PACK_ERRORS: Sql Server’ın ilk çalışmasından bu yana ağ paket transferinde meydana gelen hata sayısını verir.

@@TOTAL_READ: Sql Server’ın ilk çalışmasından bu yana toplam disk okuma sayısını verir.

@@TOTAL_WRITE: Sql Server’ın ilk çalışmasından bu yana toplam diske yazma işlemi sayısını verir.

GO İLE YIĞIN YÖNETİMİ

Sql Server’da aynı anda birden fazla SQL ifadesi kullanılabilir. Yığın işlemi kapsamında sorgular sırayla çalıştırılabilir. Bunu sağlayan ise GO komutudur.  GO komutunda kullanılan değişkenler komuttan sonraki satırlarda kullanılamaz. O yığın içerisinde yaşar ve GO komutu ile değişken yaşamını tamamlar.

Kullanım Şekli

T-SQL ifadesi-1

GO

T-SQL ifadesi-2

GO

Örnek Sql komutları:


declare @sayi1 int,@sayi2 int

set @sayi1=10

select @sayi1

GO

declare @sayi1 int

set @sayi1=33

select @sayi1

GO

Gördüğünüz gibi iki tane yığınımız var. Birincisinde ki sayi1 değişkeni ile ikinci yığında ki sayi1 değişkeninin değerlerinin farklı olduğunu göreceksiniz eğer sql sorgusunu çalıştırırsanız. Eğer ilk GO ifadesini kaldırırsanız hata alacaksınız.

TABLO TİPİ DEĞİŞKENLER

 Değişken olarak fiziki kayıt edilmeyen bir tablo oluşturur, sql sorgusu aşağıdaki gibidir.


declare @tablo table(

sayi int,

adi varchar(20)

)

insert into @tablo values(23,'burak')

select * from @tablo

@tablo adında bir tablo değişkeni ve içinde sayi ve adi olarak iki alan tanımladık, insert ile kayıt ekledik, kayıtları getirdik ama fiziksel bir tablo değildir, değişken üzerinde tutulan bilgilerdir.

OUTPUT İŞLEMİ

 Bir ekleme, silme işlemi yaparken bu işlemi aynı anda sanal(değişken tipli) bir tabloya da aktarılabiliyor. Bunu sağlayan OUTPUT işlemidir. Bir örnek yapmak gerekirse;


declare @eklenenler table(

bolkod1 smallint,

bol_adi1 char(15),

ckgrade1 int

)

insert into bolum output inserted.bolkod,inserted.bol_adi,inserted.ckgrade into @eklenenler values(601,’bolumadi’,150)

select * from @eklenenler

output ifadesi

insert into tablo_ismi ————— values(değerler)

şeklinde —— kısmına yazılır.

AKIŞ KONTROLLERİ

İF-ELSE

 Akış kontrolleri programlamadan alışkın olduğunuz bir konu aslında. İlk kontrolümüz if-else kontrolü.


declare @sayi1 int,@sayi2 int

set @sayi1=1

set @sayi2=2

if(@sayi1>@sayi2)

print 'sayi1 daha büyük'

else print 'sayi2 daha büyük'

Gördüğünüz gibi if-else işlemi bu şekildedir, önemli bir if kullanımı da,


declare @sayi1 int,@sayi2 int

set @sayi1=1

set @sayi2=2

if exists(select * from dbo.bolum)

print 'true'

else print 'false'

 

if’in yanında exists ifadesi eğer içerdeki sorgu kayıt döndürüyorsa, true dönderir ve if içine girer aksi takdirde else tetiklenir.

CASE YAPISI

 Birden fazla koşul gerektiren işlemlerin kullanımını kolaylaştırmaktadır. Hemen sorgumuzu yazalım,


declare @tablo table(

sayi1 int

)

insert into @tablo values(1)

select sayi1=

case sayi1

when 1 then 2

end

from @tablo

Burada bir tablo değişkeni tanımladık ve sayi1 alanı tanımladık. Insert işlemi ile tablomuza 1 değerini ekledik, ayrıca case yapısı select ile kullanılıyor. Case içerisinde when 1 then 2 ifadesi dikkat çekiyor. Buradan anlamamız gereken, sayı eğer 1 ise 2 yap ve select işlemiyle listele. Umarım sınavlarda karşımıza çokça gelir ve yaparız.

 

WHILE DÖNGÜSÜ VE BREAK, CONTINUE YAPISI

 

Bir kodun veya kod bloğunun koşul gerçekleştiği sürece tekrar çalıştırılmasını sağlar. WHILE ifadesinde koşul true değeri sağladığı sürece kodların işletilmesine devam eder.


declare @sayi1 int

set @sayi1=1

while @sayi1<5

begin

print 'sayımız:'+ cast(@sayi1 as varchar(10))

set @sayi1=@sayi1+1

end

Burada @sayi1 değişkenimize 1 değerini atadıktan sonra while şartımız sayi1 değişkenimizin 5 sayısından küçük olma durumudur. Böylece her sayi1<5 şartı sağlandığında içeri girip işlemleri gerçekleştirecektir, print işleminden sonra sayımız 1 arttırılmıştır. Böylece çıktımız:

 

sayımız:1

sayımız:2

sayımız:3

sayımız:4

şeklinde olacaktır.

While döngüsünde bilinmesi gereken iki ifade: BREAK,CONTINUE

BREAK: While döngüsü sonlanmadan herhangi bir işlem basamağında döngüden çıkmak için kullanılır. Özellikle istenilen bir koşul sağlandığında döngüyü sonlandırmak için kullanılır. Döngü sonlandıktan sonra While döngüsünün bittiği noktanın sonrasında bulunan kod çalışmaya devam eder.

CONTINUE: While döngüsü devam ederken bulunan işlem basamağının atlanıp bir sonraki basamaktan döngüye devam edilmesini sağlar.

 

GOTO İFADESİ

 

GOTO ifadesi sayesinde istediğimiz gibi dallanma yapabiliriz, bir önceki while ifadesini GOTO ile yapacağız.


declare @sayi1 int

set @sayi1=1

yenile:

print 'sayımız:'+ cast(@sayi1 as varchar(10))

set @sayi1=@sayi1+1

while @sayi1<5

GOTO yenile

RETURN ifadesi: T-SQL kodlarının çalışmasını sonlandırır.

 

WAITFOR

 

Yazdığımız kod bloğunu istediğimiz bir zaman diliminde çalıştırmamız sağlar.

 

  • WAITFOR TIME ’12:15:00’ =   Kodlar 12:15 de çalıştırılır.
  • WAITFOR DELAY ’12:15:00’ =   Kodlar 12 saat 15 dakika sonra çalıştırılır.

waitfor delay '00:00:10'

select * from bolum

select sorgusunu 10 saniye sonra çalıştırır.

 

TRY-CATCH YAPISI

 

Try – Catch yapısı sql server 2005 ile birlikte kullanılmaya başlanmıştır. Programlama dillerinde olduğu gibi yazılan SQL ifadelerinde hata yakalamak için kullanılır. Hemen sql sorgularıyla gösterelim:


begin try

select 1/0

end try

begin catch

select ERROR_MESSAGE() as hata_mesaji

end catch

0’a bölünme hatası olan: Divide by zero error encountered, mesajını yazdıracaktır.

 

STORED PROCEDURE(SAKLI YORDAMLAR)

 

Stored Procedure, SQL Server üzerinde sorgulamalar yapmak için oluşturulan derkenmiş T-SQL ifadeleridir. Normal SQL ifadeleri her çalıştırıldıklarında derlenirler fakat saklı yordamlar sadece bir kere derlenir ve sonraki kullanımda derlemeye gerek kalmadan çalıştırılabilirler. Oluşturulan Store Procedure’ler sunucu üzerinde depolanır ve çalıştırıldığında sunucu üzerinde çalıştırılırlar.

 

FAYDALARI

  • Uzun SQL ifadeleri yerine tek satırlık saklı yordam ağın trafiğini azaltır.
  • Saklı yordamlar çoklu kullanıcı ve istemci programlar tarafından kullanılabilir.
  • Kullanıcıların karmaşık ifadeler yazmasına gerek kalmaz.
  • Oluşturulan saklı yordamlar güncellenebilir olduğu için tekrar kullanılabilir kod sağlarlar.
  • Ağ trafiğini azalttığı için çok kullanıcılı sistemlerde kitlenmeyi azaltır.
  • Açık olarak yazılan SQL ifadelerine göre daha güvenlidir.

 

STORED PROCEDURE TÜRLERİ

  • System Stored Procedure(Sistem Saklı Yordamları)
  • Extended Stored Procedure(Genişletilmiş Saklı Yordamlar)
  • Local Stored Procedure(Yerel Saklı Yordamlar)

 

Bizim işimiz LOCAL STORED PROCEDURE olacak. Kullanıcı tarafından oluşturulan saklı yordamlar yani.

 

Bir örnek yapalım:


create procedure tablolari_birlestirelim

as

select ogr.adi,(nots.final*60/100)+(nots.vize*40/100) as ortalamasi,ders.ders_adi from ogrenci ogr , notlar nots,dersler ders where ogr.no=nots.no and ders.op_kod=nots.op_kod

go 

ögrenci,dersler,notlar tablolarından ilgili ilişkileri kurduktan sonra öğrenci adını, final ve vize notlarının ortalamasını hesaplayan ve dersin adını yazan sorgumuzu ‘tablolari_birlestirelim’ adlı procedure olarak çalıştırdığımız zaman Object Explorer>Databases>’kullandiginiz veritabanı isminize tıklayın’>Programmability>Stored Procedures tıkladığınız zaman oluşturduğunuz saklı yordamı görebilirsiniz eğer gözükmüyorsa Stored Procedure üzerine sağ tıklayıp Refresh yaparsanız gözükecektir.

Oluşturduğumuz saklı yordamı kullanmak için New Query demeniz ve use ‘veritabanı_ismi’ dedikten sonra saklı yordamın ismini yazmanız yeteridirTablolari_birlestirelimYukarıdaki ifadeyi yazarsanız, sorgunuz derlenmeden sunucuda çalışarak önünüze gelecektir.Bir de parametre alan saklı yordamlar vardır. Tek farkları parametre almasıdır.


create procedure tablolari_birlestirelim(

@parametre int,

@parametre1 int

)

as

select ogr.adi,(nots.final*60/100)+(nots.vize*40/100) as ortalamasi,ders.ders_adi from ogrenci ogr , notlar nots,dersler ders where ogr.no=nots.no and ders.op_kod=nots.op_kod

go 

Parametreleri istediğimiz gibi kullanabiliriz.

 

KULLANICI TANIMLI FONKSİYONLAR

 

Kullanıcılar tarafından oluşturulan, bildiğimiz nesne tabanlı programlamadaki metotlar gibi kullanılan fonksiyonlardır.


create function buyuk_harf(@gelenstring varchar(max))

returns varchar(max)

as

begin

return upper(@gelenstring)

end

Burada buyuk_harf diye bir fonksiyon oluşturduk, @gelenstring diye parametre alıyor, hemen altında returns ‘döndüreceği değer’ hani metotlarda public int yazarsak int değer döndürmesi lazımdı, burada da işte aynı mantık ile 2. Satırdaki returns bize döndürülecek değeri veriyor. As yazdıktan sonra istediğimiz satırları yazıyoruz, biz gelen string değerini büyük harfe çevirip bunu döndüren bir fonksiyon yazdık.

 

New Query deyip;


print dbo.buyuk_harf('burak secer')

Yazdığımız zaman bize BURAK SECER değerini döndürecektir, use ‘database name’ yapmayı unutmayın. Unutmadan hatırlatayım, bu fonksiyonlar tablo tipli de olabilir. Returns kısmında Returns Table dersek tablo döndermesi gerekir.

 

CURSOR İLE İMLEÇ KULLANIMI

 

İmleçler, Microsoft Word gibi metin editörlerindeki imleçlerle aynı göreve sahiptir. Word içerisinde imleç nerede ise klavyeden basılan tuş oraya yazılır. Veritabanı sistemlerinde de imleç o an hangi satırdaysa oradaki veriler üzerinde işlem yapılması sağlanır. Yani elde edilen bir kayıt kümesinde satır satır işlem yapmak için kullanılır.

 

Bilinmesi Gerekenler:

  • FETCH NEXT: Bir sonraki satıra geçer.
  • FETCH PRIOR: Bir önceki satıra geçer.
  • FETCH LAST: En son satıra geçer.
  • FETCH FIRST: İlk satıra geçer.
  • FETCH ABSOLUTE n: n pozitif ise n. Satıra geçer, negatif ise sonran n. Satıra geçer, n sıfır ise hiçbir satıra geçmez.
  • FETCH RELATIVE n: n pozitif ise en son gidilen elemandan n satır ileri gider, negatif ise en son gidilen satırdan n satır geri gider.

declare @degisken char(15)

declare imlec cursor

for select ogrenci.adi from ogrenci

open imlec

fetch next from imlec into @degisken

while @@FETCH_STATUS=0

begin

print @degisken

fetch next from imlec into @degisken

end

close imlec

deallocate imlec

 

Değişken sayesinde bütün kayıtları satır satır geziyoruz, en önemli kısmı ise:

Fetch next from ‘imlec_ismi’ into ‘degisken_ismi’ bu satır bir sonraki imlece gider, @@FETCH_STATUS imlecin en son işletilen komutun durumunu tutar. Biz basit bir print işlemi ile öğrenci tablosundaki adi alanını yazdırdık.

 

Eğer sondan gitmek istiyorsanız SCROLL imleci eklemeniz yeterlidir. (cursor’dan sonra)

 

 

TRIGGER(TETİKLEYİCİ) KULLANIMI

 

Adından da anlaşılacağı gibi Trigger bir olayı tetikler bu olay bir insert,update ya da delete işlemi olabilir. Bazı şartlar sağlandığı zaman belirli kayıtların silinmesini isteyebiliriz. Kayıt silme işlemi için şartlar sağlandığında o sql kodunu tetiklememizi sağlayan yapıya Trigger denir. Genelde çok korkulan bir konudur fakat içine girince basit olduğunu göreceksiniz(VTYS finalinden 36 aldı) 😀

 

Tetikleyici türlerini teker teker anlata anlata gidelim.

 

DDL TETİKLEYİCİLERİ

Create,alter ve drop kullanıldığında devreye girecek tetikleyiciler.  DDL tetikleyiciler kullanılan sql ifadesi gerçekleştikten sonra devreye girer. İşlem gerçekleşme anında değil.


create trigger silme_engeli

on database

for drop_table

as

begin

print 'tablo silme engellenmiştir'

rollback

end

Burada silme işlemini engeliyoruz. Drop_table yerine yazılacak diğer alternatifler aşağıdadır:

  • CREATE_TABLE
  • CREATE_VIEW
  • CREATE_INDEX
  • CREATE_FUNCTION
  • ALTER_TABLE
  • ALTER_VIEW
  • ALTER_INDEX
  • ALTER_FUNCTION
  • DROP_TABLE
  • DROP_VIEW
  • DROP_INDEX
  • DROP_FUNCTION

 

LOGON TETİKLEYİCİLER

 

Sql Server’a yapılan girişlerde devreye giren tetikleyicilerdir. Kimlik doğrulama aşamasından sonra devreye girmektedir.

 

DML TETİKLEYİCİLER

 

Tablo ya da view üzerinden insert,update ve delete işlemleri gerçekleştirildiğinde devreye giren tetikleyicilerdir.

UYGULAMA: Örnek kütüphane veritabanımız için kategoriler tablosuna eklenen yeni kategorinin var olup olmadığını kontrol ederek aynısı var ise kullanıcıyı uyararak işlemi iptal eden bir trigger yazaacağız.


create trigger trigger_ismi

on tablo_ismi

for insert (ekleme işlemi için)

as

declare @degisken_adi int

select @degisken_adi=tablodakideger from inserted

if exists(select * from tablo_ismi where

tablodakideger=@degisken_adi)

begin

print 'aynı isimde kategori zaten var'

rollback

end

Siz eğer insert into tablo_ismi values(1,’dram’) yazarsanız size ‘aynı isimde kategori zaten var’ mesajı verecektir. Eğer kategori daha önce girilmediyse ekleme işlemi gerçekleşir.

 

INSERT TETİKLEYİCİLER

 

Adından da anlayabileceğimiz gibi tabloya bir kayıt eklendiğinde devreye giren tetikleyicilerdir. Bir uygulama yapacağız karışık gibi görülebilir ama iyi okursak anlayabiliriz.

 

Bir firma için alınan ürünler ve satılan ürünlerin olduğu iki tablomuz var. Birisi firmanın aldığı ALIMLAR diğeri müşterilerin firmadan aldığı SATISLAR tablosu ve URUNLER tablosu olsun. Yapacağımız Trigger, şirket ürün aldığında URUNLER tablosuna alınan ürünü ekleyecek, müşteriler ürün aldığında ise URUNLER tablosundan toplam üründen düşülecek ve eğer satışı yapılan miktar genel miktardan fazla ise buna izin vermeyecektir.

 

İlk tetikleyicimizin görevi alınan ürünü ALIMLAR tablomuza eklemek.


create trigger stok_kontrol

on ALIMLAR

AFTER INSERT

AS

declare @a_miktar bigint

declare @u_kod int

select @a_miktar=miktar,@u_kod=kodu from inserted

update URUNLER SET miktar=miktar+@a_miktar where urun_kodu=@u_kod

Şimdi ise eğer satılacak ürün stokta yoksa yani SATISLAR>ALIMLAR durumu var ise buna engel olacağız.


create trigger stok_kontrol_satis

on SATISLAR

after insert

as

declare @satis_miktar bigint

declare @stok_miktar bigint

declare @u_kod int

select @satis_miktar=miktar,@u_kod=urun_kodu from inserted

select @stok_miktar=miktar from URUNLER where urun_kodu=@u_kod

if(@satis_miktar>@stok_miktar)

begin

print 'yetersiz stok miktarı'

rollback

end

else

update URUNLER set miktar=miktar-@satis_miktar where urun_kodu=@u_kod

DELETE TETİKLEYİCİLER

 

Bir tablodan satır silindiğinde tetiklenmektedir. Özellikle birbiriyle ilişkili tablolardan satır silindiği zaman kullanılır.

 

Örnek verirsek, kütüphane veri tabanı için KITAPLAR tablosundaki kitap silindiğinde ilişkili olduğu tablolardaki o kitaba ait bilgileri silmek için gerekli olan tetikleyiciyi yazacağız.


create trigger silme_kontrolu

on KITAPLAR

after delete

as

declare @ISBN VARCHAR(255)

if @@ROWCOUNT>0

BEGIN

select @ISBN=ISBN FROM DELETED

DELETE FROM EMANET WHERE ISBN=@ISBN

DELETE FROM KITAP_KATEGORI WHERE ISBN=@ISBN

DELETE FROM  KITAP_KUTUPHANESI WHERE ISBN=@ISBN

END

UPDATE TETİKLEYİCİ

 Satırlar üzerinde güncelleme yapıldığı zaman kullanılan bir tetikleyicidir. Aynı silme işlemindeki gibi birbiriyle ilişkili tabloların aynı anda güncellemesini sağlar.

 

Yine KUTUPHANE veri tabanı için KITAPLAR tablosundaki ISBN numarası değiştirildiğinde ilişkili satırlarda da güncellenmesi için bir trigger yazacağız.


create trigger isbn_guncelleme

on KITAPLAR

AFTER UPDATE

AS

declare @eski_isbn varchar(255)

declare @yeni_isbn varchar(255)

if @@rowcount>0

begin

select @eski_isbn=isbn from deleted

select @yeni_isbn=isbn from inserted

update EMANET set isbn=@yeni_isbn

where isbn=@eski_isbn

update KITAP_KATEGORI set isbn=@yeni_isbn

where isbn=@eski_isbn

end

Unutmamak lazım @@rowcount etkilenen satır sayısını verir.

 

 

NOT: Bu not sadece eğitim için hazırlanmıştır, hiç bir ticari amacı yoktur. Daha ayrıntılı bilgi için VERİ TABANI YÖNETİM SİSTEMLERİ – 2 – Turgut ÖZSEVEN kitabından faydalanabilirsiniz, iyi çalışmalar dilerim.

1 thought on “VTYS Genel Tekrar (T-SQL)

Bir Cevap Yazın