Bölüm 7: Microsoft Transact - SQL -113-

Transkript

Bölüm 7: Microsoft Transact - SQL
-113-
Dr. Serkan DİŞLİTAŞ
7.1. Transact – SQL
(T-SQL)
Microsoft Transact-SQL ya da kısaca T-SQL, Microsoft Server için özel yetenekleri olan
SQL dilidir. T-SQL ile SQL cümleleri yazılırken değişkenler, karar ve çevrim kontrolleri,
tetikleyiciler, saklı yordamlar gibi yapılar da kullanılmaktadır.
7.2. Operatörler
Operatörler bir veya daha çok veri üzerinde işlem yapılmasını sağlayan yapılardır.
7.1.1. Aritmetiksel Operatörler
Operatör
+
*
/
%
+
-
Açıklama
Toplama
Çıkarma
Çarpma
Bölme
Modüler Bölme
İşaret Pozitif
İşaret Negatif
7.1.2. Karşılaştırma Operatörleri
Operatör
=
>
>=
<
<=
<>
Açıklama
Eşit
Büyük
Büyük Eşit
Küçük
Küçük Eşit
Farklı
7.1.3. Mantıksal Operatörler
Operatör
Not
And
Or
-114-
Açıklama
Değil
Ve
Veya
7.1.4. Bitsel (Bitwise) Operatörler
Operatör
~
&
|
^
Açıklama
Bitsel Not
Bitsel And
Bitsel Or
Bitsel Ex-Or
7.1.5. Diğer Operatörler
Operatör
=
+
-/* …..
….. */
Açıklama
Atama
Birleştirme
Açıklama
Grup Açıklama
7.1.6. Operatör Önceliği
Genellikle diğer programlama dillerinde olduğu gibi Transact-SQL’de de operatörlerin
işletilmesinde belirli bir öncelik sırası vardır. Bu durum genellikle karmaşık ifadelerde ön
plana çıkmaktadır.
Aynı önceliğe sahip operatörlerde, işletim soldan sağa gidilerek yapılmaktadır.
Öncelik
1
2
3
4
5
6
7
8
9
Operatör
+, -, ~
*, / , %
+,= , >, >=, <, <=, <>
^, & , |
Not
And
Or
=
Açıklama
İşaret , Bit Yapısında Değil
Çarpma, Bölme, Mod Bölme
Toplama, Çıkarma
Karşılaştırma
Bit Yapısında Ex-Or, And, Or
Mantıksal Değil
Mantıksal Ve
Mantıksal Veya
Atama Operatörü
-115-
7.3. Veri Tipleri
Veritabanındaki tablo ve değişkenlerde tutulan verilerin ne tipte ve ne büyüklükte olacağını
belirlemek amacıyla veri tipleri kullanılmaktadır. Veri tipleri üç ana sınıf altında
toplanabilmektedir :
 Sayısal Veri Tipleri,
 Karakter Veri Tipleri
 Zaman Veri Tipleri
Sayısal Veri Tipleri
Veri Tipi
Açıklaması
Sınır Aralıkları
Int
Tamsayı
4Byte (İşaretli-İşaretsiz)
TinyInt
SmallInt
MediumInt
BigInt
Float
Double/Real
Decimal
Tamsayı
Tamsayı
Tamsayı
Tamsayı
Tek hassasiyetli Floating Point
Cift hassasiyetli Floating Point
Float, ASCII Karakter olarak
+- 1.176x10-38 den +-3.403x1038
+-2.225x10-308 den +-1.798x10308
+-2.225x10-308 den +-1.798x10308
Karakter Veri Tipleri
Veri Tipleri
Char
VarChar
Blob
TinyBlob
MediumBlob
LongBlob
Text
TinyText
MediumText
LongText
Enum
Set
-116-
Açıklama
Sabit Uzunluklu
String
Değişken
Uzunluklu String
BLOB : Binary
geniş nesne
BLOB
BLOB
BLOB
Text
Text
Text
Text
Siralamak
Deger Ayarı
Sınır Aralıkları
0-255 Karekter
0-255 Karekter
Binary veri 0-65535 byte uzunlukta
Binary veri 0-255 byte uzunlukta
Binary veri 0-16777215 byte
Binary veri 0-4294967295 byte
0-65535 byte
0-255 byte
0-16777215 byte
0-4294967295 byte
Sütun değerleri listeden alınan bir değer olmalı.
Sütun değerleri sıfır veya listeden bir değer ol.
Zaman Veri Tipleri
Veri Tipi
Açıklaması
Formatı
Date
DateTime
TimeStamp
Time
Year
Tarih
Tarih +Zaman
Zaman Parçası
Saat
Yıl
YYYY-AA-GG
YYYY-AA-GG ss:dd:ss
YYYYAAGGssddss
ss:dd:ss
Yyyy
Bir alan Char ile tanımlandığında boyuta bağlı olarak girilen verilerin sağ tarafları boşluk
ile doldurulur. VarChar ile tanımlandığında ise girilen verinin 1 karakter fazlası yer
ayrılacaktır. Bu 1 byte da string bitişi için kullanılmaktadır. Tanımlanan boyutun tamamı
veri ile dolduğunda ise fazladan 1 byte daha ekleneceğinden diskten yer kazancı da zararla
sonuçlanacaktır.
Veri Tipi
Tanımı
Ad Char (10)
Sonuç
‘Z’
‘E’
‘K’
‘E’
‘L’
‘E’
‘Y’
‘İ’
‘M’
‘N’
‘F’
‘A’
‘E’
‘’
‘L’
‘P’
‘’
‘E’
‘’
‘’
‘T’
‘’
‘’
‘T’
‘’
‘’
‘İ’
‘’
‘’
‘N’
‘N’
Null
Her zaman 10 karakter dolu
‘Z’
‘E’
Toplam 7 karakter
Ad VarChar(10)
‘Y’
‘N’
‘E’
‘L’
‘İ’
Toplam 5 karakter
‘F’
‘K’
‘E’
‘M’
Toplam 11 karakter
‘A’
‘E’
‘P’
Null
Null
‘L’
‘E’
‘T’
‘T’
‘İ’
VarChar kullanımı diskten kazanç sağlamaktadır ancak performans açısından düşüş
yapacaktır. Char tipi verilerle çalışmak performansı artıracaktır ancak daha fazla yer
tutacaktır. T.C. Kimlik No, Öğrenci No gibi sabit büyüklükteki verilerin girileceği alanların
Char ile oluşturulması daha uygun olur.
-117-
7.4. Değişkenler
Değişkenler, içeriği değişebilen bellek hücrelerini adresleyen yapılardır. Değişkenler
sadece bir bellek gözünü adresler. Bununla birlikte, veriler bellekte birden fazla byte yer
kaplayabilir. Bu nedenle verilerin, değişkenlerin tanımladığı adresten itibaren kaç byte
hücreyi daha içerdiği veri tipleri sayesinde belirlenir. Bu durumda değişken, verinin
bellekte yer aldığı başlangıç adresini tutarken; değişkenin veri tipi ile başlangıç adresinden
itibaren kaç byte hücrenin bu veriye ait olduğu belirlenir.
T-SQL’de değişkenler Yerel (Local) ve Genel (Global) olmak üzere iki türlü tanımlanır.
7.4.1. Yerel Değişkenler
Kullanıcı tarafından Declare ifadesi kullanılarak tanımlanırlar ve her değişken @ özel
karakteriyle başlar. Yerel değişkenler başlangıçta Null değere sahiptirler.
Declare (Yerel_Değişken_Adı) (Veri_Türü)
------------------------------------------------------------------------------------------------------Yerel_Değişken_Adı
: @ özel karakteriyle başlayan oluşturulacak değişkenin adı.
Veri_Türü
: Değişken veri tipi (char, varchar, int, date …)
Örnek 1 :
Declare
@Numara
@Sonuc
Char(9),
Int
Burada 9 karakterli Char tipinde @Numara adında ve Int tipinde @Sonuc adında iki yerel
değişken tanımlanmaktadır. Bu değişkenlerin ilk değerleri Null’dur.
Örnek 2 :
Declare
Set
@Numara
Char(9)
@Numara = ‘034510015’
Burada @Numara değişkenine ‘034510015’ değeri atanmaktadır.
Örnek 3 :
-- 9 karakterli Char tipinde @OgrNo adında yerel değişken tanımı
Declare @OgrNo
Char (9)
-- Kimlik tablosundaki en küçük numara @OgrNo değişkene atanır.
Select
@OgrNo = Min(OgrNo)
From Kimlik
-- @OgrNo değeri sonuç (Result Set) olarak elde edilir.
Select
@OgrNo as [İlk Ogrenci Numarası]
-- @OgrNo değeri ekrana mesaj olarak yazdır.
Print
@OgrNo
-118-
Örnek 4 :
Exec ve Declare uygulaması - I
Not : String ifadelerde karakter sayıları eşit olduğunda, sayısal büyüklük veya küçüklük
geçerlidir.
Örnek 5 :
Exec ve Declare uygulaması - II
-119-
7.4.2. Genel Değişkenler
@@ (çift) özel karakteriyle başlayan değişkenlerdir. SQL tarafından oluşturulmuşlardır ve
kullanıcı tarafından yaratılamazlar.
Bazı Genel (Global) Değişkenler
Değişken Adı
@@Version
@@Language
@@ServerName
@@Connections
@@Total_Read
@@Total_Write
@@Total_Errors
Örnek 1 :
İçeriği
Versiyon bilgileri
Kullanılan Dil
Yerel Sunucu adı
Bağlantı/Bağlantı Girişimi sayısı (Sunucunun başlatılmasında sonra)
Disk Okuma Sayısı (Sunucunun başlatılmasında sonra)
Disk Yazma Sayısı (Sunucunun başlatılmasında sonra)
Disk Okuma/Yazma Hatası Sayısı
(Sunucunun başlatılmasından sonra)
Print
@@Version
Bu komut satırı işletilince aşağıdakine benzer versiyon bilgileri elde edilmektedir.
Microsoft SQL Server 2000 - 8.00.194 (Intel X86)
Aug 6 2000 00:57:48
Copyright (c) 1988-2000 Microsoft Corporation
Örnek 2 :
print 'Server Name : ' + @@ServerName + ' - Language :' + @@language
Sonuç;
Server Name :DISLITAS - Language :British
6.4.3. Yerel Tablo Türü Değişkenler
Tablolar geçici olacak şekilde değişken olarak tanımlanabilmektedir.
Declare (Yerel_Değişken_Adı) Table (Tablo_Tanımı)
----------------------------------------------------------------------------------------------------Yerel_Değişken_Adı
: @ özel karakteriyle başlayan yaratılacak değişkenin adı.
Tablo_Tanımı
: Normal Tablo Tanımı
-120-
Örnek 1 :
-- Tablo Değişkeni Tanımla
Declare
@Gecici_Notlar
Table
( OgrNo Char(9), Vize Int, Final Int, Ortalama Float )
-- Tablo değişkenine başka tablodan değer atama
Insert Into @Gecici_Notlar (OgrNo)
Select Ogrno From Kimlik
-- Tablo Değişkenini Sorgula
Select * From @Gecici_Notlar
Not: @Gecici_Notlar tablo değişkeninin Vize, Final ve Ortalama alanları Null değer alır.
Örnek 2:
Exec (' --Tablo Degişkeni Tanımla
Declare @TabloDegiskeni Table ( OgrNo Char(9), Derskodu Char(7), Durum Char(8) )
--Tablo değişkenine başka tablodan değer atama
Insert Into @TabloDegiskeni (OgrNo, Derskodu, Durum)
Select Numara, DersKodu, Durum From Notlar
-- Tablo Değişkenini Sorgula
Select * From @TabloDegiskeni
')
Not : Burada Exec komutu ile SQL cümlesinin işletilmesi sağlanmıştır.
7.5. Fonksiyonlar
Transact-SQL’de fonksiyonlar tanımlanmalarına, ürettikleri değerlere ve türlerine göre
çeşitli sınıflara ayrılmaktadırlar.
Tanımlamasına göre fonksiyonlar :
- Yerleşik (Sistem Tanımlı) Fonksiyonlar
- Kullanıcı Tanımlı Fonksiyonlar
Ürettikleri değerlere göre fonksiyonlar :
- Kararlı Fonksiyonlar
: Her zaman aynı değeri verirler ( Abs (-12) ).
- Kararsız Fonksiyonlar : Farklı değerler verir ( Rand ( ) ).
Türlerine göre fonksiyonlar:
- Gruplama (Toplama - Aggregate) Fonksiyonları
- Matematiksel Fonksiyonlar
- Tarih ve Zaman Fonksiyonlar
- Metin Fonksiyonları
- Meta Veri Fonksiyonları
- Güvenlik Fonksiyonları
-121-
- Sistem Fonksiyonları
7.5.1. Gruplama (Aggregate) Fonksiyonları
Fonksiyon
Görev / İşleyiş
Min(Sütun)
Max(Sütun)
Sum(Sütun)
Avg(Sütun)
Count(Sütun)
Sütun içerisinde en küçük değer bulunur.
Sütun içerisinde en büyük değer bulunur.
Sütun toplamı bulunur. (Null değerler hariç)
Sütun aritmetik ortalaması bulunur. (Null değerler hariç)
Sütuna göre kayıt sayısı bulunur. (Sütundaki Null değerler hariç)
Count(*)
StdDev(Sütun)
StdDevP(Sütun)
Var(Sütun)
VarP(Sütun)
Tablodaki kayıt sayısı bulunur.
Sütuna göre standart sapma bulunur.
Sütuna göre istatistiksel standart sapma bulunur.
Sütuna göre Varyans bulunur.
Sütuna göre istatistiksel Varyans bulunur.
7.5.2. Matematiksel Fonksiyonlar
Fonksiyon
Açıklama
Abs(x)
Cos(x)
Sin(x)
Degrees(x)
Ceiling(x)
Exp(x)
Floor(x)
Log(x)
Log10(x)
PI()
Power(x,y)
Radians(x)
Rand( )
Round(x,y)
Sign(x)
X sayısının Mutlak Değeri
Radyan cinsinden x sayısının, Cos değerini verir.
Radyan cinsinden x sayısının, Sin değerini verir.
Radyan >>> Derece
X Kesirli sayıyı üst sayıya yuvarlar.
ex Exponent üs değerini verir
X Kesirli sayıyı alt sayıya yuvarlar.
X sayısının Doğal Logaritmasını alır.
X sayısının 10 tabanına göre logaritmasını alır.
Pi sayını verir.
X sayısının y. Kuvvetini verir. (xy )
Derece >>> Radyan
Real olarak parametre ister. Örn:Radians(30.0)
0 ile 1 arası rastgele değer üretir.
X sayısını y kesir hanesine yuvarlar.
X sayısının işareti öğrenilir. (x sayısı negatif ise (-1), 0 ise (0) ve pozitif ise
(+1)
X sayısının karesini verir.
X sayısının karekökünü verir.
Square(x)
Sqrt(x)
-122-
Örnek 1:
7.5.3. Tarih ve Zaman Fonksiyonları
Fonksiyon
Day()
Month()
Year()
GetDate()
DateAdd
DateDiff
DatePart
Tarih Parçası
Year
Quarter
Month
DayofYear
Day
Week
Açıklama
Sistem GÜN değerini verir.
Sistem AY değerini verir.
Sistem YIL değerini verir.
Sistem tarihini verir (Saat dahil)
Tarihe ekleme
İki tarih arası fark
Tarihin belirli kısmının alınması
Tarih Format Analizi
Kısaltılmış İfade
Tarih Parçası
yy, yyyy
qq, q
mm, m
dy, y
dd, d
wk, ww
WeekDay
Hour
Minute
Second
MilliSecond
Kısaltılmış İfade
dw
hh
mi, n
ss, s
ms
-123-
Örnek 1:
Örnek 2:
Select Ad, Soyad, Year( GetDate() ) – Year(DogTar) as Yaş From Kimlik
Burada ‘Kimlik’ tablosundan Ad, Soyad ve DogTar alanları getirilmekte ve sistem tarihine
ait yıl (fonksiyon değer üretimi) ile DogTar arasındaki yıl farkının elde edilmesi ile her
kayıt için yaş gösterilmektedir.
İlave Örnek:
Select DATEDIFF (Year, '2002/07/27', GETDATE()) as Yas
7.5.4. Metinsel Fonksiyonlar
Fonksiyon
Ascii(x)
Char(x)
CharIndex(x, y)
CharIndex(x, y, i)
Left(x, y)
Len(x)
Lower(x)
LTrim(x)
NChar(x)
Replace(x, y, z)
Right(x, y)
RTrim(x)
Space(x)
SubString(x, y, z)
Unicode(x)
Upper(x)
-124-
Açıklama
X String ifadesinin sol karakterinin Ascii kod değerini verir.
X Ascii kod değerine karşılık karakteri verir.
X string ifade Y içerisinde aranır.
X string ifade Y içerisinde, i. konumundan itibaren aranır.
X string ifadesinin soldan y karakteri alınır.
X string ifadesinin karakter sayısını verir.
X string ifadesi küçük harflerle elde edilir.
X string ifadesinin soldan boşlukları atılarak elde edilir.
X Unicode değerinin karşılı olan karakteri verir.
X string ifadesi içindeki tüm y ifadeleri z ifadeleri ile değiştirilir.
X string ifadesinin sağdan Y karakteri alınır.
X string ifadesinin sağdan boşlukları atılarak elde edilir.
X adet boşluktan oluşan string oluşturur.
X string ifadesinin y sütunundan itibaren z adetini verir.
X Unicode karakterinin karşılığı olan sayısal değeri verir.
X string ifadesi büyük harflerle elde edilir.
Örnek 1:
Select Upper(Ad), Soyad From Kimlik
Yukarıdaki kullanımda ‘Kimlik’ adlı tabloda ‘Ad’ alanındaki veriler büyük harflerle,
‘Soyad’ alanındaki veriler ise aynen tablodaki gibi elde edilir.
Örnek 2:
Select * From Kimlik
Where Lower ( Ad ) = ‘zeynep’
Yukarıdaki kullanımda ‘Kimlik’ adlı tabloda ‘Ad’ alanındaki değeri küçük harflerle
‘zeynep’ olan kayıtlar elde edilir.
Örnek 3:
-125-
Örnek 4:
-126-
7.6. Karar ve Çevrim Kontrol İşlemleri
T-SQL ile yazılan komut satırları yukarıdan aşağıya doğru sıralı olacak şekilde işletilir.
Karar ve Çevrim kontrol komutları kullanılarak komut satırlarının işletilmesi
düzenlenebilmektedir.
7.6.1. Karar Kontrol
Karar kontrol komutları, komut satır/satırlarının belirlenen bir şarta bağlı olarak işletilip
işletilmemesini ya da işletilme sırasının değiştirilmesini sağlar.
IF .. ELSE Komutu
Belirtilen şarta bağlı olarak komut ya da komut satırlarının işletilmesini sağlar.
If (Şart 1) ( İfade 1)
Else If (Şart 2) ( İfade 2)
Else ( İfade 3)
Örnek 1 :
EKSTRA ÖRNEK ------------------------If 45%2=0 Print 'ÇİFT' ELSE Print 'TEK'
---------------------------------------------------Burada @sayi değişkeni Int tipli olduğundan (eğer 2 değil de 2.0 yazılsaydı ya da @sayi
değişkeni Real olarak tanımlansaydı sonuç kesirli olarak elde edilirdi) yapılan bölme
kalansız bölme şeklindedir.
-127-
Örnek 2 :
Burada @Ogrno, Char(9) tipli bir değişken olarak tanımlanmıştır. Eğer @Ogrno, ‘11’
değerine eşitse mesaj olarak ‘Zeynep’ verilir. Eğer ‘11’ değilse ‘12’ olarak yeni bir şart
kontrol edilir. @Ogrno, ‘12’ ise ‘Hatice’, eğer bu da değilse ‘Bilinmiyor’ olarak mesaj
verilir.
CASE .. END Komutu
Program akışının çok değerli kontrolünün yapılmasını sağlayan bir işlevdir. Select veya
Update komutlarıyla birlikte kullanılır. İki şekilde kullanımı vardır.
1. Kullanım :
Case (Şart Değeri)
When (Değer 1) Then
When (Değer 2) Then
….
Else (Diğer İfade)
End
2. Kullanım :
Case
When (Şart 1) Then
When (Şart 2) Then
….
Else (Diğer İfade)
End
(İfade 1)
(İfade 2)
(İfade 1)
(İfade 2)
Not : Burada sadece ilk şartı sağlayan When satırındaki ifade çalıştırılır.
-128-
Örnek 1:
Yukarıdaki her iki kullanımda; Kimlik tablosundan Ogrno, Ad, Soyad bilgileri doğrudan
alınırken, Cins alanına ait bilgiler ‘0’ ve ‘1’ değerlerinden Case..End işlevi ile ‘ERKEK’
ve ‘KADIN’ verilerine dönüştürülerek alınmaktadır. Cins Alan değeri burada 0 veya 1’den
farklı ise sonuç Null olarak elde edilir.
Bu nedenle şarta uymayan durumlar için Else kullanılarak Null yerine istenen değer
aktarımı yapılabilmektedir. Aşağıda Else kullanılarak yapılan kontrolde şarta uymayan
durumlar için Cins alan içeriği doğrudan aktarılmaktadır.
Select
Ogrno, Ad, Soyad, Case
When Cins = '0' Then 'ERKEK'
When Cins = '1' Then 'KADIN'
Else Cins
End as Cinsiyet
From Kimlik
-129-
GOTO Komutu
Program akışının koşulsuz olarak değiştirilmesini sağlar.
Goto (Etiket)
…
(Etiket) :
…
--------------------------------------------İleri Adresleme;
Geri Adresleme;
(Etiket) :
…
Goto (Etiket)
Örnek 1:
Burada ‘Basla’ etiketi ile geri adresleme yapılarak bir çevrim oluşturulmuştur. ‘Cik’ etiketi
ile de ileri adresleme yapılarak program sonlandırılır.
-130-
7.6.2. Çevrim Kontrol
Çevrim kontrol komutları, komut satır/satırlarının belirlenen şart sağlandığı sürece
işletilmesini sağlar.
WHILE Komutu
Bir ifade bloğu belirlenen şart sağlandığı sürece işletilir. Şartın durumuna göre, blok hiç
işletilmeyeceği gibi sonsuz çevrime de girebilir.
While (Şart)
Begin
( İfade Bloğu)
End
Not : Break ve Continue komutları kullanılarak While çevriminin işlevi değiştirilebilir.
Continue : İfade Bloğu tamamlanmadan çevrimin bir sonraki turuna geçilir.
Break
: İfade Bloğu tamamlanmadan çevrim sona erdirilir.
Örnek 1:
-131-
7.7. Geçici Tablo Oluşturma
MSSQL Server ortamında T-SQL dili yardımıyla geçici tabloların oluşturularak
kullanılması mümkündür. Geçici tabloların, sistem çalışması esnasında oluşturulmasıyla
birtakım işlemlerin daha kolay ve güvenli bir şekilde yapılması sağlanabilmektedir.
Geçici tablo oluşturma işlemi birkaç yöntemle yapılabilmektedir. Ayrıca oluşturulan geçici
tablolar üzerinde Drop, Insert Into, Update vb. SQL komutlarının işletilmesi de
mümkündür.
7.7.1. Gecici Tabloların tempdb Veritabanında Oluşturulması
tempdb, geçici tabloların tutulduğu sistem veritabanıdır. Bu veritabanı içerisinde
oluşturulan tablolar da geçici olmaktadır. Server kapatılıp açıldığında bu tabloların silindiği
görülebilmektedir.
Aşağıda tempdb veritabanında, Kimlik_Gecici adlı bir tablo oluşturulmaktadır.
-132-
Yukarıdaki gibi tablo oluşturulduğunda MSSQL
Kimlik_Gecici adlı tablo aşağıdaki gibi görülmelidir.
Server
tempdb
veritabanında
-133-
7.7.2. Oturum Boyunca Geçerli Geçici Tablo Oluşturulması
Özellikle oturum kapandığında silinen tablolardır. Bu tablolar aynı zamanda MSSQL
Server kapatılıp açılarak ya da Drop komutu kullanılarak da silinebilmektedirler.
Aşağıda Kimlik_Gecici_2 adlı bir geçici tablo oluşturulmaktadır. Bu yöntemde geçici tablo
isimlerinin başında # karakteri kullanılmaktadır.
7.8. Transactions (İşler)
Bir işi oluşturan birden fazla işlemin hepsinin de başarılı bir şekilde yerine getirilmesinin
gerekli olduğu durumlar için ‘Transaction’ yapıları kullanılır. Bu sayede işlemlerden
herhangi biri ya da daha fazlası sonuçlanamazsa tüm işlemlerin iptal edilerek işe başlama
noktasına ya da belirlenen bir noktaya yeniden dönülmesi sağlanır. Bu sayede işin
bütünlüğü korunur.
Örnek 1: A listesinden B listesine bir öğrenciyi transfer etmek isteyelim. Yapılması istenen
iş, öğrencinin A listesinden silinmesi işlemi ile sonrasında B listesine bu öğrencinin kayıt
edilmesi işleminden oluşmaktadır.
İşi Başlat
1. İşlem :
2. İşlem :
İşi Bitir
-134-
Öğrenciyi A listesinden Sil
Öğrenciyi B listesine Ekle
Burada işlemlerden birinin yapılamaması durumunda ya öğrenci hiçbir listede yer
almayacak ya da her iki listede yer alacaktır. Bu durumu engellemek için, her iki işlemin de
başarılı olması durumunda işin gerçekleştirilmesini onaylamaktır. Diğer durumlarda işi
oluşturan diğer işlemler de iptal edilerek iş başlangıcına dönülmektedir.
Örnek 2: Banka hesabından para çekme işi tanımlayalım. Yapılması istenen iş, hesap
kontrolü, hesaptan parayı düşme ve para ödeme olmak üzere 3 temel işlemden oluşsun.
İşi Başlat
1. İşlem :
2. İşlem :
3. İşlem :
İşi Bitir
Hesap Kontrolü Yap
Hesaptan Parayı Düş
Para Ödemesi Yap
Buradaki işlemlerden herhangi birinin sistem arızası, elektrik kesintisi gibi çeşitli
nedenlerden dolayı gerçekleşmemesi durumunda diğer işlemlerin de iptal edilerek iş
başlangıcına dönülmesi gerekmektedir.
7.8.1. Transaction Komutları
Transaction yapısı aşağıdaki gibidir. Insert, Update ve Delete komutları Transaction için
geçerli birer işlemdir.
-- Tran yerine Transaction da kullanılabilir.
Begin Tran
…
İşlemler
…
Commit Tran
-135-
Begin Tran
: Transaction Başlangıcı
Commit Tran
: Transaction Sonu
RollBack Tran
: Transaction Geri Al, İptal Et
Save Tran (İşlem Yeri Adı)
: Transaction Ara İşlem Yeri Belirle
RollBack Tran [İşlem Yeri Adı] : Ara İşlem Yerine Geri Al
Save Tran ifadesi ile Transaction sınırları içerisinde ara yerler adlandırılabilmektedir.
RollBack Tran ifadesinde de bu adlar parametre olarak verilerek ara noktalara geri dönüş
sağlanmaktadır. Parametre verilmezse Transaction başına dönülür.
Örnek 1:
Begin Tran
-- Öğrenci B Listesine ilave edilir.
Insert Into Liste_B
Select * From Liste_A
Where Ogrno ='034526050'
-- Öğrenci A Listesinden silinir.
Delete From Liste_A
Where Ogrno ='034526050'
Commit Tran
Burada Begin Tran ve Commit Tran ifadesi arasında kalan işlemlerin her ikisi de (Ekleme
ve Silme) gerçekleşmeden iş onayı verilmez ve tablolarda fiziksel işlemler
gerçekleştirilmez.
7.9. Saklı Prosedürler (Stored Procedures)
Saklı Prosedürler, sunucu veritabanında saklanan derlenmiş Transact-SQL deyimlerinden
oluşan altyordamlardır. Saklı Prosedürler sadece ilk çalıştırılmalarında derlenirler, sonraki
çalışmalarda mevcut derlenmiş kodlar kullanılır. Bir Saklı Prosedür başka bir saklı
Prosedürü çağırabilir. (32 defa iç içe).
7.9.1. Saklı Prosedürlerin Avantajları



-136-
Sunucu üzerinde önceden derlenmiş olduklarından ağ trafiğini azaltırlar ve
dolayısıyla da performansı artırmaktadırlar.
Veritabanı ile birlikte kopyalama ile çoğaltılabilirler.(Veritabanında tutulur)
Saklı prosedürlerde parametre ile veri göndermek ve almak mümkündür.
7.9.2. Genel Saklı Prosedür Türleri
Saklı prosedürler sistem ve kullanıcının yarattığı olmak üzere iki yapıdadır.
7.9.2.1. Sistem Kaynaklı Hazır Saklı Prosedürler
Master veritabanında depolanan ve sp_ ile başlayan saklı prosedürleridir. Bazı sistem saklı
prosedürleri ve işlevleri aşağıda listelenmiştir.
Saklı Prosedür Adı
İşlevi
Sp_Help
Sp_Helpdb
Sp_Who
Sp_AddLogin
Sp_Add_JobSchedule
Sp_Rename
Sp_Renamedb
Sp_AddType
Sp_DbOption
Veritabanı nesneleri, veri tipleri hakkında yardım
Veritabanı hakkında bilgiler
Geçerli MSSQL kullanıcıları ve işlevleri
Verilen parametresi ile veritabanı için kullanıcı ekler.
İşler için zamanlama yapma
Kullanıcı veritabanı nesnelerini yeniden adlandırma
Veritabanı adı değiştirme
Yeni veri tipi oluşturma
Veritabanı hakkında bilgiler
Saklı Prosedür Çağırma
Execute (Saklı Prosedür Adı) [parame1, parametre 2 …]
Örnek 1:
Execute
Sp_Who
Burada geçerli MSSQL kullanıcıları ve işlevleri elde edilir.
Örnek 2:
Execute
Sp_AddLogin
‘Misafir’, ‘1234’, ‘myo’
‘myo’ veritabanında geçerli olmak üzere ‘Misafir’ adında, şifresi ‘1234’olan bir kullanıcı
oluşturulur.
Örnek 3 :
Sp_AddType
tisim,
‘varchar(20)’, ‘Not Null’
Aşağıda Sp_AddType saklı prosedürü kullanılarak yeni bir veri tipi oluşturulmuş ve tablo
oluşturulurken bu yeni veri tipi kullanılmıştır.
-137-
Kullanıcı Tanımlı Saklı Prosedürler
Create Procedure
[Parametre_Listesi]
As
Procedure_Bloğu
Procedure_Adi
Not 1 : Prosedürlerin çağrılmasında Master veritabanında, sp_ ile başlayanların önceliği
vardır. Bu durum göz önüne alınarak isim verilmesi gerekmektedir.
Not 2 : # [Procedure_Adi]
## [Procedure_Adi]
Örnek 1 :
-138-
>>>
>>>
Geçici Yerel Procedure tanımlanabilir.
Geçici Genel Procedure tanımlanabilir
Saklı prosedür oluşturma ve kullanma.
Not : Aşağıda ‘sysobjects’ tablosunda nesneler ve tipleri verilmektedir.
Nesneler ve Type Değerleri
Name
Stored Procedure
Table
View
Trigger
…
(‘sysobjects’ tablosu)
Type
‘P’
‘U’
‘V’
‘Tr’
…
-139-
Örnek 2 :
Parametreli Saklı Prosedür oluşturma ve kullanma.
Burada ’03’ saklı prosedüre parametre (OgrNo alanı için Where satırında kullanım için)
olarak verilmiştir.
-140-
Örnek 3 :
Default Parametreli Saklı Prosedür Oluşturma ve kullanma.
-141-
Örnek 4 :
-142-
Çıkış Parametreli (Output) Saklı Prosedür Oluşturma ve Kullanma
Örnek 5 :
Faktöriyel Hesaplayan Saklı Prosedür Oluşturma ve Kullanma
-143-
Örnek 6 :
-144-
Return Geri Dönüşlü Saklı Prosedür Oluşturma ve Kullanma
7.10. Tetikleyiciler (Trigger)
Insert, Update ve Delete komutlarıyla yapılan kayıt işlemlerinde otomatik olarak çalışan
dışarıdan parametre almayan özel Saklı Prosedürlerdir. İş bütünlüğü korunarak ilişkisiz
verilerin tutulması engellenmiş olur.
Örneğin öğrenci ‘Kimlik’ tablosundan bir öğrencinin silinmesi durumunda, ‘Notlar’
tablosundan da o öğrencinin notlarının silinmesi işlemi tetikleyiciler ile yapılabilir. Ayrıca,
‘Fatura’ giriş-çıkışı durumuna göre otomatik olarak ‘Stok’ giriş-çıkışının yapılması işlemi
de tetikleyiciler için örnek gösterilebilir.
MS SQL server iki ayrı tetikleyici destekler. Bunlar :


‘After’ Tetikleyicileri (After ve For olarak kullanılır.)
‘Instead Of’ Tetikleyicileri
7.10.1. After Tetikleyicileri





‘After’ tetikleyicileri, onu çağıran komuttan sonra çalıştırılırlar.
Insert, Update, Delete komutları için kullanılabilir.
Sadece tablolar için oluşturulabilirler (View- Görünümler için oluşturulamaz).
Alt düzey kısıtlamalar işletildikten sonra çağrılırlar. Kısıtlama ile herhangi bir
sorunlu durum ortaya çıkarsa tetikleyici çağrılmaz.
Bir tetikleyici birden çok komuta uygulanamaz. Ancak her komut için ayrı bir
tetikleyici oluşturulabilir.
7.10.2. Instead Of Tetikleyicileri




‘Instead Of’ tetikleyicileri, onu çağıran komutların yerini alırlar.
Insert, Update, Delete komutları için kullanılabilir.
Bir tetikleyici birden çok komuta uygulanabilir.
Tablo ve görünümler (View) için ‘Instead Of’ tetikleyicileri oluşturulabilir, ancak
Tablo/View’ e ait bir eylem için tek bir tetikleyici kullanılabilmektedir.
Instead Of tetikleyicileri ile bir tabloya eksik veri girildiğinde engelleme yapılabilir.
Numara, Ad ve Soyad girilmesi gerekirken bazılarının eksik girilmesi gibi durumlarda
değeri girilmeyen alanlar istenmeyen bir şekilde ‘Not Null’ olur.
7.10.3. Tetikleyici Etkilenmeleri
Tablolarda yapılan işlemlere bağlı olarak Tetikleyici etkilenmelerine ait tablo aşağıda
verilmiştir.
INSERT
DELETE
UPDATE
Tetikleyicisi
Tetikleyicisi
Tetikleyicisi
INSERTED
Eklenen Kayıtlar
Güncelleme Sonrası
DELETED
Silinen Kayıtlar
Güncelleme Öncesi
-145-
7.10.4. Tetikleyici (Trigger) Oluşturma
Tetikleyicileri oluşturmak için gerekli yapı aşağıdaki gibidir.
Create Trigger Trigger_Adi
On
(Tablo_Adı/View_Adı)
Tetikleyici_Turu Komut
As
(Tetikleyici_Tarafından_Yürütülecek_SQL_Cümleleri)
Örnek 1:
-146-
‘After’ Tetikleyicisi Oluşturma ve Kullanma
Örnek 2:
‘After’ Tetikleyicisi Oluşturma ve Kullanma
-147-
Örnek 3:
‘Instead Of ’ Tetikleyicisi Oluşturma ve Kullanma
Burada S_InsteadOf_Tr adlı trigger tanımlandığından dolayı, Kimlik tablosunda herhangi
bir değişiklik yapılmak istendiğinde (burada sadece Update için geçerli) Trigger devreye
girecek ve istenen işlemi iptal ederek onun yerine çalışacaktır.
Admin yetkili kişi bu ‘Trigger’ ifadesini devreye alıp çıkararak diğer kullanıcıların
çalışmalarını sınırlayabilmektedir. Bu sayede istenilmeyen durumlarda (güncelleme, bakım
vb.) veritabanında ilgili tablolar için istenen Ekleme-Güncelleme-Silme gibi işlemler devre
dışı bırakılmış olacaktır.
-148-
Tartışma : ‘Kimlik’ tablosunun güncelleştirilmesine yönelik hem ‘After’ hem de ‘Instead
Of’ tetikleyicisi mevcut iken (önceki iki örneğe ait tetikleyiciler), ‘Kimlik’ tablosunda bir
değişiklik yapılmak istenirse ne olur ?
Açıklama : Öncelikle ‘Instead Of ’ tetikleyicisi devreye girecektir. Bu tetikleyici de Update
işlemini engelleyip kendisi çalışacağından diğer ‘After’ tetikleyicisi de çalışmayacaktır.
7.11. İmleçler (Cursors)
İmleçler, SQL sorgusu ile elde edilen ResultSet içerisinde belli bir kaydı işaret edebilen
yapılardır. İmleçler yardımıyla ResultSet kayıtlarına birer birer sırayla ulaşmak ve
üzerlerinde güncelleme, silme gibi değişik işlemler yapmak mümkün olmaktadır.
İmleç Komutu
Açıklama
Declare cr_Staj
Cursor For
Select OgrNo, SYapGun From Staj27
İmleç Oluşturma
Open cr_Staj
İmleç Açma
Fetch From cr_Staj Into @Numara, @StajGun
İmleç Değerini Al
Fetch First From cr_Staj Into @Numara, @StajGun
Fetch Last From cr_Staj Into @Numara, @StajGun
İlk İmleç Değerini Al
Son İmleç Değerini Al
Fetch Prior From cr_Staj Into @Numara, @StajGun
Önceki İmleç Değerini Al
Fetch Next From cr_Staj Into @Numara, @StajGun
Close cr_Staj
Sonraki İmleç Değerini Al
+n : Baştan n. İmleç Değerini Al
-n : Sondan n. İmleç Değerini Al
+n : İleri n. İmleç Değerini Al
-n : Geri n. İmleç Değerini Al
İmleç Kapatma
DeAllocate cr_Staj
Set RowCount 0
Set RowCount n
İmleci Bellekten Çıkartma
Tüm ResultSet alınır.
ResultSet’in n adedi alınır.
Fetch Absolute n From cr_Staj Into @Numara, @StajGun
Fetch Relative n From cr_Staj Into @Numara, @StajGun
@@Cursor_Fetch Fonksiyonu Geri Dönüş Değerleri ve Anlamları
Geri Dönüş Değeri
0
-1
-2
Anlamı
Fetch işlemi başarılı
Fetch işlemi başarısız
ResultSet sonu (Satır yok )
-149-
Örnek 1 : Aşağıda cr_Staj adlı basit bir imleç oluşturulmuş ve bu imleç ile kayıtlar teker
teker getirilerek ekrana raporlanmıştır.
-150-
7.12. Transact-SQL Uygulamaları
Uygulama 1: 2005-2006 BAHAR dönemine ait 4526118-00 kodlu dersin öğretim
elemanı elde edilir.
Uygulama 2: Numarası 054526012 olan öğrencinin geçtiği dersler elde edilir.
-151-
Uygulama 3: Tablodan 1. döneme ait dersler Derskodu sırasına göre elde edilir.
Uygulama 4: Tablodan ‘2005-2006 BAHAR’ dönemine ait ‘4527214-00’ kodlu dersi
alan öğrenciler numara sırasına göre elde edilir.
-152-
Uygulama 5: Tablodan numarası 04 ile başlayan mezun olmuş öğrencilerin istenen
özelliklerde kayıt bilgileri elde edilmektedir.
Uygulama 6: Bu uygulamada Alt Sorgular yardımıyla; 24120 sicil numaralı öğretim
elemanının, 2005-2006 BAHAR döneminde girdiği derslere ilişkin ayrıntılı bilgi elde
edilir.
-153-
Uygulama 7: Öğrencilerin geçtikleri derslerin kredileri toplamı elde edilir.
Uygulama 8: Öğrencinin devam zorunluluğu olmayan dersleri elde edilir.
-154-
Uygulama 9: 2005-2006 BAHAR dönemine ait derslerin öğrenci mevcutları elde edilir.
Uygulama 10: 24120 sicil numaralı kullanıcının şifresi değiştirilmektedir.
-155-
Uygulama 11: 044527014 numaralı öğrencinin 2004-2005 GÜZ dönemi 4527117-00
kodlu dersinin not bilgileri güncellenmektedir. Sonrasında bu not yeniden elde
edilmektedir.
Uygulama 12: 044527014 numaralı öğrenci için, 2004-2005 BAHAR dönemindeki
4527118-00 kodlu derse kaydı(ekleme) yapılmaktadır.
-156-
Uygulama 13: 4527118-00 kodlu dersten 2004-2005 BAHAR döneminde başarılı olan
öğrencilerin mevcudu elde edilir.
Uygulama 14: 4527118-00 kodlu dersten 2005-2006 BAHAR döneminde sınava giren
öğrencilerin mevcudu elde edilir.
Uygulama 15: 2004-2005 BAHAR dönemi harç miktarı toplamı elde edilir.
-157-
Uygulama 16: Öğrencilerin yaptıkları toplam staj gün sayıları elde edilir.
Uygulama 17: Öğretim elemanlarının 2005-2006 BAHAR döneminde girdikleri Normal
Öğretim ders saatleri elde edilir.
-158-
Uygulama 18: 2005-2006 BAHAR döneminde 4527118-00 kodlu dersi alan
öğrencilerin aritmetik ortalaması elde edilir.
Uygulama 19: 2005-2006 BAHAR döneminde 4527118-00 kodlu dersi alan
öğrencilerin Standart Sapması elde edilir.
-159-
Uygulama 20: Öğrencilerin 2005-2006 BAHAR dönemindeki Kredi ve Ders Saati
toplam miktarları elde edilmektedir.
Uygulama 21: Program derslerinin yer aldığı tablo oluşturulur. DersKodu alanı Birincil
İndeks olarak tanımlanmıştır.
-160-
Uygulama 22: Program harç bilgilerinin yer aldığı tablo oluşturulur. OgrNo, HDonem
ve HarcFisNo alanları Birincil İndeks olarak tanımlanmıştır.
Uygulama 23: Program staj bilgilerinin yer aldığı tablo oluşturulur. OgrNo, SDonem ve
SBasTar alanları Birincil İndeks olarak tanımlanmıştır.
-161-
Uygulama 24: Program öğrenci ders ve not bilgilerinin yer aldığı tablo oluşturulur.
OgrNo, DersKodu ve DYilDonem alanları Birincil İndeks olarak tanımlanmıştır.
Uygulama 25: OgrKimlik10 adlı tablo mevcut ise fiziksel olarak veritabanından
silinmektedir. Not : Veritabanına ait nesne bilgileri SysObjects adlı sistem tablosunda
tutulmaktadır.
-162-
Uygulama 26: Öğrencilerin 4527118-00 kodlu dersi kaçıncı kez aldığı belirlenmektedir.
Eğer ders alam sayısı 1 ise İlk, 2 ve daha fazla sayıda ise Tekrar sonucu elde edilmektedir.
Uygulama 27: 044527012 numaralı öğrenci için, 2004-2005 BAHAR dönemindeki DC
olan dersleri dönem ortalaması 2.0 ve yukarısı ise DC+ (GEÇER), değilse DC- (TEKRAR)
olarak güncellenecektir.
-163-
Uygulama 28: Öğrencilerin her biri için 2005-2006 BAHAR döneminde aldıkları
dersler için ağırlıklı not ortalamaları hesaplanmaktadır.
-164-
Uygulama 29: 2005-2006 öğretim yılında (% nedeniyle GÜZ, BAHAR ve YAZ
OKULU dahil) numarası 05 ile başlayan öğrencilerin her biri için ağırlıklı not ortalaması
hesaplanmakta ve %10’a girenlerin listesi elde edilmektedir. Burada Top 10 ile en üstten
%10’luk dilime girenlerin elde edilmesi sağlanmaktadır.
-165-
Uygulama 30: Öğrencilerin geçtikleri dersler baz alınarak, Transkript için Toplam
Kredi Miktarı ve Ağırlıklı Not Ortalaması elde edilmektedir.
-166-
Uygulama 31: Kullanıcı giriş kontrolü yapılmaktadır. Bu amaçla S_Kullanici_Sp adlı
Saklı Prosedür kullanılmıştır. Saklı prosedüre kullanıcı adı ve şifresi gönderilmektedir.
Saklı prosedür parametrik olarak aldığı bu iki değeri veritabanında tarayarak geçerli olup
olmadığını kontrol etmekte ve sonuç olarak geriye kullanıcı açık kimliği ve görev yetkisi
döndürülmektedir. Eğer böyle bir kullanıcı yok ise geriye Null değeri dönmektedir.
S_Kullanıcı_Sp adlı saklı prosedür içeriği aşağıda verilmiştir.
-167-
-168-

Bölüm 7: Microsoft Transact - SQL -113-

Transkript

Benzer belgeler

tıme based sql ınjectıon

HB1002: ORACLE 10G VERĐTABANI – PL/SQL

TC ORMAN ve SU İŞLERİ BAKANLIĞI SÖZLEŞMELİ

Dynamics NAV Satınalmak İçin Başlıca Nedenler

BÖLÜM 6

Microsoft Dynamics AX 2012 Kullanmak Icin 10 Neden - ETG-IT

Sample CV

İndexler

PHP ile MySQL Veritabanı İşlemleri

Microsoft Dynamics AX 2012 - ETG-IT

KAP

VERİTABANI YÖNETİM SİSTEMLERİ

ÖZGEÇMİŞ Yrd. Doçent SERKAN EYMUR

Miras Hukuku Final

dosyayı indir - Öğr.Gör. Serkan AKTAŞ