Veritabani Program Lama Ii-ygozudeli Wwwjavadilicom

om

ili. c

Şubat 2004

Veritabanı Programlama 2 jav w.

ww

Veritabanı Programlama 2

ad

Yaşar Gözüdeli

Eğitim dizisi

ww ili. c

ad

jav

w.

om

om

ad

ili. c

Veritabanı Programlama 2 Şubat 2004

jav

Yaşar GÖZÜDELİ

Bu ek kopyalanamaz, satılamaz. Her hakkı saklıdır. BYTE’ın okurlarına ücretsiz armağanıdır.

ww

w.

© 2004 BYTE

BYTE bir Acar Yayıncılık Yayınıdır

om

İÇİNDEKİLER

GİRİŞ..............................................................................8 Belli başlı VTYS’ler ............................................................. 8 Bir Proje İçin Hangi VTYS’yi Seçilmeli? ...................................... 9

ili. c

Veritabanı Programlama-2 Kitapçığı Neleri İçeriyor..................... 10 Teknik Destek ................................................................. 10

1.MS SQL Server 2000’e giriş ...........................12 Web Matrix ve MS SQL Server Desktop Engine............................ 12 A. MSDE’nin Kurulumu ve Kaldırılması .................................... 13 Gereksinimler ................................................................. 13 Kurulumu....................................................................... 13

ad

Kaldırılması.................................................................... 16 SQL Server İstemci Taraf Bileşenleri ..................................... 16 SQL Server Enterprice Manager ............................................ 16 Enterprise Manager’e Bir SQLServer Kaydetme .......................... 17 Enterprise Manager ile Veritabanı Bakım ve Yönetimi .................. 18

jav

Enterprise Manager ile Yeni bir Veritabanı Oluşturmak................. 19 Bir Veritabanını Ölçeklemek ................................................ 20 SQL Query Analyzer .......................................................... 21 Query Analyzer ile Veritabanı Oluşturmak ................................ 23 Loglamadan Tablo Boşaltma: Truncate Table............................. 24 Bir Sorguyu Query Analyzer ile Analiz Etme .............................. 25

w.

Import & Export Data ........................................................ 27

SQL Service Manager ......................................................... 28 SQL Server ve XML Desteği ................................................ 29

SQL Server’de Veritabanı Nesneleri ..................................... 29

ww

SQL Server ve Kullanıcı Yönetimi........................................... 33 Roller ........................................................................... 33 Kullanıcılar .................................................................... 33 Yetki verme (Grant Komutu) ................................................ 34

Yetki Kaldırma (Revoke Komutu) ........................................... 35

om

2.T-SQL ve VTYS temelli programlama ...............36 T-SQL ile ANSI-SQL komutlarını çalıştırma ................................ 37 Değişken Tanımlama ve Kullanma .......................................... 37

ili. c

Değişkenler ve Sistem Fonksiyonları....................................... 38 Print komutu .................................................................. 39 Akış Kontrolleri ............................................................... 39 Go Komutu..................................................................... 40

1.IF ... ELSE karar yapısı .................................................... 40 2.CASE deyimi................................................................. 42 3.While Döngüsü .............................................................. 43

ad

Geçici Tablolar ................................................................ 45 Kontrol Zorlayıcıları (Check Constraints) ................................. 46

3.Saklı Prosedürler (Stored Procedures) .............48 Prosedür ne demektir? ....................................................... 48

jav

SP oluşturma .................................................................. 48 SP üstünde değişiklik yapmak .............................................. 49 Bir SP’yi Silmek ............................................................... 50 SP’ye Parametre Yollama.................................................... 50 Sp’den Değer Döndürme..................................................... 52 Bu prosedürün döndürdüğü sonucu nasıl alabiliriz? ..................... 52

w.

SP’yi VTYS Uzayı Dışından MDACs ile Çağırmak .......................... 54 Cursor’ler ...................................................................... 56

Başlıca Sistem Saklı Prosedürleri ........................................... 57

4.İşlemler(Transactions) –Tetikler(Triggers) .........61

ww

Transactions................................................................... 61 Trigger’lar ..................................................................... 62 Eklenen-Silinen Kayıtlar ..................................................... 64

om

Önsöz

Yönetici Editör: İbrahim Özdemir [email protected]

jav

Tasarım ve Uygulama: Yağız Akay [email protected]

ad

Editörler: Burak Kahyaoğlu [email protected] Üsame İldar Özdemir [email protected]

ACAR Yayıncılık adına imtiyaz sahibi: Murat Yıldız Reklam Müdürü: Ahmet Aslantürk

w.

Baskı: ETAM A.Ş.

Veritabanı kavramının ilk olarak ortaya atıldığı 1980’li yılların üstünden henüz 23 yıl geçti. Ancak bu gün, operasyonel iş dünyası, Veritabanı Yönetim Sistemleri (VTYS) olmadan hiçbir şey yapamaz hale geldi. İşletim sistemlerinden sonra en popüler ve en çok kazandıran yazılımlar Veritabanı Yönetim Sistemleridir. Günümüzde, bir çok alanda operasyonel veri işlemlerinde VTYS’ler yaygın olarak kullanılmakta. Bu programlar için bir çok nesne birbiri ile aynı temel işlevi yerine getirir ve yaklaşık olarak aynı teorilere dayanarak çalışır. VTYS’lerinden orta ve büyük ölçekli olanlarından biri olan MS SQL Server 2000 bu kitapçıkta teferruatlı olarak ele alınıyor. MS SQL Server 2000’i tanırken daha çok VTYS’ler için ortak olan noktalara değinildi. Böylelikle kitapçıkta anlatılan konular sadece MS SQL Server kullanıcıları için değil, Sybase, Oracle hatta Advantage (Türk programcılar tarafından geliştirilmiş bir VTYS. Bu vesile ile projede emeği geçenleri da tebrik etmek gerekir.) için bile bir çok teknik olarak uygulanabilirlik seviye-

ili. c

Sorumlu Yazı İşleri Müdürü: Murat Yıldız [email protected]

Film Çıkış: Figür

ww

Merkez Ofis: Fulya Cad. Fulya Bayırı Sk. Bilsan Plaza 11/4 80300 Mecidiyeköy - İSTANBUL Tel: (212) 212 62 06 Faks: (212) 212 62 11 www.byte.com.tr

om

programlamadan ziyade yönetimi ile daha çok ilgili olan, Kullanıcı Yönetimi, Yedekleme-Kurtarma, DTS-BCP gibi konulara da kısıtlı sayfa sayısı nedeniyle değinilmedi. Başta, gece gündüz demeden yoğun bir çalışmayla siz değerli okurlara bu seriyi ulaştırmak için uğraşan Yönetici Editör İbrahim Özdemir ve ekibine, Veritabanı konusunda tecrübelerini çekinmeden aktaran ve kitap için düzeltmeler yapan sayın Nurullah Kılıç’a ve kitabın içerdiği örnekleri test eden eşim Lale Gözüdeli’ye olmak üzere kitapçıkta emeği geçen herkese sonsuz teşekkürlerimi sunarım. Bununla birlikte, seride yer alan bütün hatalar bana aittir. -Yaşar GÖZÜDELİ [email protected]

ww

w.

jav

ad

ili. c

sinde yer almaktadır. Bu kapsamda, hemen her VTYS’de ortak olarak yer alan saklı prosedürler, trigger’ler, kursörler, transaction’lar, zorlayıcılar ve diğer nesneler yine sırayla bu kitapta ele alınan konular arasında. Bu kitapçıkta genel olarak MS SQLServer ve Sybase’de kullanılan TSQL komutları yer alıyor. Oracle tarafından kullanılan PL/SQL komutları da aslında T-SQL ile bir çok noktada hemen hemen aynıdır. Bu nedenle VTSY’ler için bir noktaya kadar ortak bir kitapçık ortaya çıktığını söyleyebiliriz. Tüm bunlarla birlikte son yıllarda popülerlik kazanan VTYS uygulama şekillerinden replication (çoğaltmalı çalışma) ve Data Warehousing (Veri ambarlama) konuları, kitapçığın alanının kısıtlı olması nedeniyle yer verilemeyen konular arasında. XML veritabanları da yavaş yavaş popülerleşen bir konu ama bu konu da bu kitapçığın içeriği dışında. Bütün bunlar kitapçıkta yer almamalarına rağmen veritabanı konusunda kendisini geliştirmek isteyen arkadaşlara fikir vermesi açısından burada zikredilmiştir. Ayrıca veritabanı

Veritabanı Programlama 2

BYTE

7

om

Giriş

V

nesneler yazılabilmektedir.

Belli başlı VTYS’ler

ili. c

eritabanı Yönetim Sistemleri, verilerin fiziksel hafızadaki durumlarını, kullanıcıların erişimlerini düzenleyen sistemlerdir. İlişkisel VTYS’ler günümüzde yaygın olarak kullanılmaktadır. VTYS’ler fiziksel hafızayı ve veri tiplerini kullanıcılar adına şekillendirip denetleyen ve kullanıcılarına standart bir SQL arayüzü sağlayarak onların dosya yapıları, veri yapısı, fiziksel hafıza gibi sorunlarla ilgilenmek yerine veri giriş-çıkışı için uygun arayüzler geliştirmelerine olanak sağlayan yazılımlardır. VTYS’de verileri tutmak üzere bir çok türde nesne ve bu nesnelere erişimleri düzenlemek üzere kullanıcılar, roller ve gruplar yer alır. Her bir kullanıcının belli hakları vardır. Bu haklar, kısıtlanabilir. Örneğin bir tablo ya da programcığı bir kullanıcı kullanabilirken bir başkasının hakları veritabanı yöneticisi tarafından kısıtlanmış olabilir. VTYS’lerin bir çoğu ANSI SQL’in karşılayamadığı durumlarda kullanılmak üzere ek programlama komutları barındırırlar. Bu iş için MS SQL Server ve Sybase SQL Server Transact SQL denilen komut takımlarını içerir. Oracle ise PL/SQL ile bu işe çözüm getirir. Bu diller sayesinde, bu konu içerisinde öğreneceğimiz Stored Procedure (saklı prosedürler), Trigger, Fonksiyon gibi veritabanları için vazgeçilmez olmuş

MS SQL Server: Bir orta ve büyük ölçekli VTYS’dir. ANSI SQL’e eklentiler yazmak için T-SQL’i destekler.

ad

Oracle: Daha çok yüksek ölçekli uygulamalarda tercih edilen bir VTYS’dir. ANSI SQL’e eklentiler yapmak için PL/SQL geliştirilmiştir.

ww

w.

jav

Sybase: Bir orta ve büyük ölçekli VTYS’dir. ANSI SQL’e eklentiler yazmak için T-SQL komutlarını destekler. Ülkemizde daha çok bankacılık ve kamusal alanlarda tercih edilmektedir.

8

BYTE

Veritabanı Programlama 2

Informix: Bir orta ve büyük ölçekli VTYS’dir. MySQL: Genellikle Unix-Linux temelli Web uygulamalarında tercih edilen bir VTYS’dir. Açık kod bir yazılımdır. Küçük-orta ölçeklidir. Özellikle Web için geliştirilmiş bir VTYS’dir denilebilir. Postrage SQL: Bu da MySQL gibi açık kod bir VTYS’dir. MS Access: Çoklu kullanıcı desteği yoktur. İşletim sisteminin sağladığı güvenlik seçeneklerini kullanır. Bunun

om

IBM ....................... Oracle.................... Microsoft ................ Informix ................. Sybase ................... Digerleri .................

ili. c

yanında belli sayıda kayda kadar (1000000 civarı) ya da belli bir boyutun (yaklaşık 25MB) altına kadar bir sorun çıkartmadan kullanılabilecek bir küçük ölçekli VTYS’dir. Advantage: Türk programcılar tarafından geliştirilen bir orta ve büyük ölçekli VTYS’dir.

%37.8 %26.3 %15.4 %3.2 %3 %14.3

2001 yılında bir araştırmaya göre Avrupa çapında VTYS’lerinin pazar payları [kaynak: Gartner]

Bir Proje İçin Hangi VTYS’yi Seçmeli?

ad

DB/2: IBM’in framework’lere yönelik büyük ölçekli VTYS’dir. Bunların dışında daha bir çok VTYS mevcut olabilir. Burada yer alanlarına popüler olmaları nedeni ile değinildi.

ww

w.

jav

VTYS’lerinin Avrupa genelindeki pazar payları yaklaşık olarak şu şekilde: En büyük pay IBM(DB/2) ile Oracle arasında. Hemen arkasında MS SQL Server geliyor. Informix ve Sybase ise onu takip ediyor. Yeni başlayanlar için hangi VTYS’yi öğrenmem en iyisi olur sorusunu yanıtlamak gerekebilir. Ülkemizde insan kaynakları açısından en çok kalifiye elaman aranan VTYS Oracle ve arkasından da MS SQL Server gelmekte. Çok nadir olarak Sybase bilen elemanlar da aranmaktadır. Bu konuda TBD(Türkiye Bilişim Vakfı) yayın organında yayınlanmış bir araştırmaya göz atmak için aşağıdaki adresi tıklayabilirsiniz.

Bir projede hangi veritabanının seçileceği, projenin çapı ile ilgili bir karardır. Şu soruları kendinize sormanız projenizin çapı konusunda karar vermenize yardımcı olacaktır. •Projede kaç tablo kullanacak? •Her bir tabloda en fazla kaç satır yer alabilir? •Projenize aynı anda en fazla kaç kullanıcı bağlanacak? •Projeniz günlük kaç transaction (INSERT-DELETE-UPDATE) gerçekleştirecek? •Projeniz en fazla ne kadarlık yer kaplayacak bir veritabanı dosyasına ihtiyaç duyacak?

http://www.tbd.org.tr/sayi77_html/

insan.htm

•Projeniz için güvenlik ne derece Veritabanı Programlama 2

BYTE

9

Veritabanı Programlama 2 Kitapçığı Neleri İçeriyor

om

hazırda yer alan Stored Procedure’leri de yakından tanıyacağız.

4.Bölüm: Transaction’lar ve Trigger’ler ele alınıyor. T-SQL ile Stored Procedure’ler dışında Transaction’lar ve Trigger’ler de programlanabilir. Transaction, Daha küçük parçalara ayrılamayan en küçük işlem birimi demektir. Trigger’ler ise Stored Procedure’lerin bir özel halidir ve bir tabloda değişiklik yapıldığında otomatik olarak devreye girerler. Zorlayıcılar ve SP’ler ile sağlayamadığımız veri kararlığını ve bütünlüğünü trigger’ler ile sağlamaya çalışacağız.

ili. c

önemli? Ancak bir VTYS kullanarak proje geliştirecekseniz, hangisini seçmeniz gerektiğinin dışında, hangi sürümleri kullanacağınız ya da hangi donanımlar üstünde çalıştıracağınız da önemlidir.

ad

1.Bölüm: Veritabanı Yönetim Sistemleri ele alınıyor. VTYS’ler, SQL’den anlayabilen programlardır. Verdiğimiz SQL komutlarını yerine getiren, sahne arkasındaki iş bitirici veritabanı yönetim sistemidir. VTYS’ler SQL ile ifade edebildiğimiz tüm işlemleri yerine getirebilirler. Bu bölümde VTYS’ler hakkında detaylı bilgi edineceğiz.

Eğitim serisi ile ilgili iletmek istediğiniz öneri, istek, şikayet ya da düzeltmelerinizi doğrudan BYTE Türkiye dergisine iletmek için www.byte.com.tr/bizeulas.asp adresini tıklayınız. Kitapçık ve içerdiği konular hakkındaki her türlü soru ve sorunlarınızla ilgili olarak [email protected] adresine e-posta göndermekten çekinmeyin. Yine Veritabanı Programlama serisi hakkındaki görüş, şikayet ve önerileriniz için www.verivizyon.com/sqlkitabi/ adresindeki formdan faydalanabilirsiniz. Seri hakkındaki duyuruları da aynı adresten takip edebilirsiniz.

w.

jav

2.Bölüm: T-SQL komutları ele anıyor. Bazen, SQL’de yer alan komutlar, derdimizi anlatmamız için yeterli olmayabilir. Bu tür durumlarda Transact SQL içerisinde yer alan komutlardan faydalanacağız. Transact SQL, kısaca T-SQL olarak anılır. Bu konudaki detaylı bilgiyi ikini ünitede edineceğiz.

Teknik Destek

ww

3.Bölüm: Stored Procedure ele alınıyor. T-SQL ile Stored Procedure’ler yazmak, VTYS’lerinin daha etkin bir şekilde kullanılmasını sağlar ve çeşitli performans artırıcı etkileri vardır. Stored Procedure ile, tekrar tekrar kullanmak üzere paketlenmiş T-SQL komutları oluşturacağız. Ayrıca, SYBASE ve MSSQLServer tarafından kullanılan ve sistemde hali

10

BYTE

Veritabanı Programlama 2

ww ili. c

ad

jav

w.

om

1. Bölüm

ili. c

V

om

MS SQL Server 2000’e Giriş

çok veri yönlendirmeli Web sitesi bu gün bu program üstünde inşa edilmiş durumdadır. Bu bölümde MS SQLServer 2000 Developer Edition(artık MSDE olarak anılacak)’in kurulumu ve kaldırılması anlatılacaktır. MSDE, Eğitim ve kişisel kullanım maksatlı en geniş SQL Server 2000 sürümüdür. Bu bölüm boyunca verilen uygulamalar SQL Server’in bahsedilen sürümü üstünde test edilmiştir.

ad

TYS temelli programlamayı öğrenmek istiyorsanız, öncelikle bu ünitede anlatılan her şeyi anlamaya çalışmayınız. 2.Üniteyi çok iyi etüt ettikten sonra bu üniteyi daha iyi anlayabilirsiniz. Ancak SQL Server konusunda başlangıç seviyesinde bilginiz varsa, bu bölüm ile onu daha etkili bir şekilde nasıl kullanacağınızı öğrenmiş olacaksınız. MS SQL Server, orta ve büyük ölçekli VTYS’ler arasında en yaygın olanlarından biridir. Bunun sonucu olarak da bir

jav

MSDE’nin Kurulumu ve Kaldırılması

Web Matrix ve MS SQL Server Desktop Engine nindeki setup.exe programını tıklayın. Kurulum esnasında bir güvenlik sorunu ile karşılaşmamak için MSDE dizininde bulunan setup.ini dosyasına şu iki satırın yer aldığından emin olun [Options] SAPWD=sasifresi ve daha sonra setup.exe programını

ww

w.

MSDE ayrıca MS SQL Server Desktop Edition için de kullanılmaktadır. Desktop Edition’da bir istemci arayüz bulunmamakla birlikte SQL Server kurulumunu gerçekleştirebilirsiniz. Bu programı ücretsiz olarak www.microsoft.com adresinden indirebilirsiniz. Desktop Edition’ı kurmak için, MSDE dizi-

12

BYTE

Veritabanı Programlama 2

tıklayın. Böylece, SQL Server Desktop edition kurulduğunda sa kullanıcı adında ve sasifresi şifresinde temel veritabanı kullanıcısını oluşturacak bir kurulumu başlatmış olursunuz. MS SQL Server’in bu sürümü için kısıtlı da olsa bir arayüz sağlayabilmek üzere ücretsiz olarak Web Matrix prog-

om

XP Professional yüklü bir PC’ye ihtiyacınız olacaktır. Donanımsal ihtiyacınız ise bu işletim sistemlerinin duyduğu minimum ihtiyaçlardır. Bu, bütün programın kurulumu için geçerli gereksinimdir. Sadece istemci programları kurup, hali hazırda zaten olan bir MSDE sunucusunu kontrol etmek isterseniz, Windows 98 de yeterli olabilir. Ancak, buradaki bütün uygulamaları bir tek PC’de test etmek istiyorsanız, MSDE’nin tamamını kurmanız gerekir.

ili. c

SQL Server Developer Edition kurulumu, kullanıcı ayarlarını saymazsak ve sadece kendi bilgisayarınıza kurmak veya kaldırmak isterseniz, herhangi bir program kurulumundan farkı yoktur.

Gereksinimler

Kurulumu

ad

Gerçek hayatta veritabanı uygulamaları ciddi manada kaynak ihtiyacı olan uygulamalardandır. Hafıza büyüklüğü, işlemci hızı ve benzeri gibi sistem paramatrelerini, üstünde kurgulamayı planladığınız projeye göre şekillendirmeniz gerekir. Burada verilen gereksinimler sadece öğrenme amaçlı uygulamaların denenebileceği bir sistem elde etmek için yeterlidir. MSDE kurulumu için en azından bir Windows 2000 Professional veya

jav

Bu kısımda bir tek PC üzerine tüm MSDE’nin nasıl kurulacağı anlatılacaktır.

MDACs bileşenlerini kullanarak kendiniz için bir arayüz hazırlayabilirsiniz. Bunun bir benzerini

w w w. v e r i v i z y o n . c o m

w.

download adresinden indirebilirsiniz. Desktop edition üstünde burada anlatılan işlemlerin bir çoğunu yapamazsınız. Yapabilmek için en azından bu ünitede anlatılan istemci programlarını da kurmanız gerekir. Ancak

ww

ramını kullanabilirsiniz. Web matrix programını www.asp.net/webmatrix

adresinden ücretsiz indirebilirsiniz. Ya da

sonuçta ücretsiz bir MS SQL Server sürümünü elde etmiş olursunuz. Bu iki yazılımın birlikte kullanımı konusunda daha geniş bilgiyi ADO.NET ile .NET temelli veritabanı uygulamaları geliştirme ve Web Servisleri konulu serinin 3. kitabında bulabilirsiniz.

Veritabanı Programlama 2

BYTE

13

om ili. c

Resim 1

Resim 2

retli bırakın. Daha önce bu programı kurdunuz ve üstünde değişiklik yapmak istiyorsanız ikinci seçeneği işaretleyebilirsiniz. Instance, SQL Server’in bir tek makinede iki ayrı uzayda çalışmak üzere yeniden kurulmasını kasteder. Bu durumda ikinci Instance(kopya)’ya MAKINEADI/INSTANCEADI notasyonu ile erişilir. Bir makinede birden fazla kopya çalışma özelliği tam olarak MSSQL Server 2000 sürümünde mevcuttur. 6,0 sürümünde ise bu konuda hiçbir destek yer almaz. Aynı makinede ne fazla 16 kopya çalışabilmektedir.

ad

1. Adım: IIS ve diğer SQL Server’i kullanacak servisleri durdurun. Registry’e erişecek gereksiz uygulamalar varsa kapatın.

jav

2. Adım: Sıkıştırılmış paketi bir yere açın ve x86\setup\setupsql.exe programını çalıştırın. (Resim 1)

ww

w.

3. Adım: Bu aşamada programı nereye kuracağınız sorulmaktadır. Kendi makinenize kuracaksanız, Local Computer seçeneğini işaretleyin. Uzaktaki bir bilgisayara bağlanıp o bilgisayara kurmak isterseniz, Remote Computer seçeneğin işaretleyebilirsiniz. Virtual Server seçeneği, Clustered Server kurmak için gerekli bir seçenek olup biraz ileri bir konudur. Özetle Clustered: Birden fazla SQL Server kurulu makinenin birbirinin devamı gibi iş yükü ve trafik dengeleme işlemlerini yerine getirebilmesi amacıyla kurulmasıdır. (Resim 2) 4. Adım: İlk seçenek olan create new instance of SQLServer seçeneğini işa-

14

BYTE

Veritabanı Programlama 2

5. Adım: Adınızı ve firmanızı giriniz. (Resim 3) 6. Adım: Sözleşmeyi onaylamak için [yes] botununu tıklayın. 7. Adım: Server and client tools seçeneğini işaretli bırakın. Server olarak SQL Server daha önce kurulu ise veya bulunduğunuz ağ ortamında başka bir bilgisayarda kurulu ise ilk seçeneği de

om ili. c

Resim 3

Resim 4

işaretleyebilirsiniz.

ad

8. Adım: SQL Server yapılandırma konusunda tecrübeli değilseniz, bu adımda default seçeneğini işaretli bırakın. Bir makinede, birden fazla SQL Server kurulumu çalıştırabilirsiniz. Bu durumda her bir kurulum farklı bir uzayda çalışacaktır. Aynı ismi iki SQL Server kurulumuna veremezsiniz. (Resim 4)

neğinin karşısında yer alan [browse] butonunu tıklayın ve aynı işlemi yapın. Windows konusunda yeterli bilginiz yoksa, bu kısımla da oynamamanız tavsiye edilir.

jav

10. Adım: Gelen seçenekleri aynen onaylayın. SQL Server ve Active Directory konusunda bilgi sahibi iseniz bu konudaki ayarları uygun hale getirebilirsiniz.

ww

w.

9. Adım: Karşınıza çıkan pencereden, Typical seçeneğini işaretli bırakın. SQL Server bileşenleri hakkında yeterli bilginiz varsa, Costum seçeneğini, sistem kaynaklarınızın kısıtlı olduğunu düşünüyorsanız, minimum seçeneğini kullanın. SQL Server’in programı farklı bir diske kurmasını istiyorsanız, program files seçeneğinin önündeki [browse] butonunu tıklayın ve kurmak istediğiniz klasörü seçtikten sonra [ok]’i tıklayın. SQL Server’in veri dosyalarını(veritabanı dosyalarını) farklı bir adreste saklamasını istiyorsanız, data files seçe-

11. Adım: SQL Server, iki çeşit yetkilendirmeyi destekleyebilir. a. İşletim sistemi ve domain ayarlarından gelen yetkilendirmeleri geçerli kabul eder ve SQL Server kaynaklarına erişimini geçerli kabul edebilir.(Bu durumda, sistem yetkilendirmesi olan kişi dbo(veritabanı sahibi) kullanıcısı olarak yetkilendirilir.) b. Sistem yetkilendirmelerinin yanı sıra SQLServer’in kendi kullanıcılarını da oluşturup yetkilendirmesini isteyebiliriz. Bu durumda veritabanı kaynaklarına erişim için iki yetkilendirmeden biri Veritabanı Programlama 2

BYTE

15

om

SQL Server İstemci Taraf Bileşenleri SQL Server Enterprise Manager:

Enterprise manager, SQL Server’in görsel bir arayüzden yönetilmesi için geliştirilmiş bir istemci taraf parçasıdır. Kullandığı temel arayüz MMC (Microsoft Management Console) temelli bir arayüzdür. Bu konuda, MMC’nin nasıl kullanıldığı anlatılmayacaktır. Sadece yapılabilecekler hakkında özet bilgiler bulabilirsiniz. Enterprise manager ile aynı zamanda birden fazla veritabanı sunucusuna bağlanılabilir. Bu işlem için veritabanı sunucularının (SQL Server) kaydının yapılması gerekir. Kaydın yapılabilmesi için de yetkilendirilmiş bir kullanıcı ile erişim şarttır. •Bir veritabanı sunucusuna eriştikten sonra, üstünde yer alan kullanıcıları, yetkimiz olan veritabanı dosyalarını, stored procedure, view, tablo gibi veritabanı öğelerini görebiliriz. •Yetkimiz dahilinde değişiklerde bulunabiliriz. •SQL server grupları tanımlanabilir •Her bir grup içerisinde yer alacak tekil SQL Server’ler kaydedilebilir. •Kaydı yapılan her bir SQL server için istenilen ayar ve değişiklikler yapılabilir. •Herhangi bir SQL Server üstünde veritabanları, nesneler, kullanıcılar, yetkilendirmeler ve izinler oluşturulup düzenlenebilir.

ili. c

tarafından yetkilendirilmiş bir kullanıcının erişimi geçerli sayılır. Uygun olan seçeneği işaretleyin. Şayet kendiniz SQL Server için bir kullanıcı oluşturursanız, bu kullanıcının kullanıcı adını(muhtemelen bu isim sa olacak) ve şifresini unutmayacağınız bir şekilde güvenli bir yerlere kaydedin. Daha sonra gerekli olacaktır. Ancak a’ya göre bir yetkilendirme tercih ederseniz, işletim sistemi şifrenizi korumanız gerekecektir.

jav

Kaldırılması

ad

12. Adım [Next] butonunu tıklayarak dosyaların kurulumunu sağlayın. Dosyaların kopyalanması bittiğinde [Finish] butonunu tıklayın. Böylelikle kurulum tamamlanmış olur.

w.

MSDE’yi ya da SQL Server’i kaldırmaya karar vermeden önce, üstünde çalışacak projelerinizin olmadığından emin olmanız gerekir. Aksi halde kaldırıldıktan sonra MSDE’yi kullanan projeleriniz çalışamayacaktır.

ww

1. SQL Server’i kullanmakta olan bütün servisleri durdurun. 2. SQL Server hizmetini durdurun. Açık olan tüm SQL server toollarını kapatın 3. [Denetim masası\program ekle kaldır’dan SQL Server 2000]’i seçin ve normal bir program kaldırır gibi kaldırın.

16

BYTE

Veritabanı Programlama 2

om

tıklamak yeterli olacaktır. Enterprise Manager’i çalıştırmak tek başına hiçbir anlam ifade etmez. Hangi veritabanını yönetmek istiyorsanız o sunucuyu (ya da veritabanı kurulum kopyası – ‘installation instance’) kaydettirmeniz gerekir.

ili. c

Resim 5

Enterprise Manager’e Bir SQLServer Kaydı Eklemek:

Herhangi bir SQL Server’i Enterprise Manager ile yönetebilmek için kaydettirmek gerektiğini biliyoruz. Bu işlem için şu aşamalar takip edilir: 1. Bu işlem için öncelikle Enterprise Manager bir önceki konuda anlatıldığı şekilde başlatmak gerekir. 2. Ardından, Microsoft SQL Server>SQL Server Group ağaç yapılarını sırayla tıklayarak açın. 3. SQL Server Group seçeneğini, fare ile sol tıklayın. Ardından açılan menüden New SQL Server Registration seçeneğini tıklayın. 4. Register SQL Server Wizard başlatılacaktır. [Next] butonunu tıklayın. 5. Ağda geçerli SQL Server’lerin bir listesi sol tarafta belirecektir. Kendi makinenizdeki SQL kopyasını normal olarak kurduysanız, (local) şeklinde gösteriliyordur. Uygun sunucuyu seçtikten sonra [Add >] butonunu tıklayın ve ardından [Next]’i tıklayın. 6. Hangi yetkilendirme seçeneğini kullanacağınız belirleyin. Bunu, ağda daha önceden kurulmuş bir SQL Server için

ww

w.

jav

ad

•Yetki çerçevesinde, kaydedilmiş SQL Server’ler üstünde SQL Server yönetim görevleri çalıştırılabilir, tanımlanabilir. •SQL ifadelerinin, yığınlarının ve scriptlerinin çalıştırılması için gereken yerden Query Analyzer’e geçiş sağlar. •Veri ithal-ihraç etme işlemleri başta olmak üzere bir çok işlem için kolaylaştırıcı sihirbazların başlangıç noktasıdır. MMC, Microsoft’un sunucu uygulamalarını yönetmek için geliştirdiği bir genel arayüzdür. Bu nedenle bir çok sunucu uygulamada bu standart arayüz ile kullanıcıların sunucu uygulamalarını yönetmeleri sağlanmıştır. (IIS de bu türden bir yönetim paneli kullanıcı arayüzüne sahiptir). Her bir uygulamanın kendisi için bir bileşen yüklenir ve arayüz bu bileşen sayesinde o uygulamanın özelliklerini yönetecek bir hal alır. SQL Server’ın de böyle bir eklentisi mevcuttur. Bu eklenti ile MCC’nin birlikte oluşturduğu yönetim programının adı da SQL Enterprise Manager olarak anılmaktadır. (Resim 5) SQL Enterprise Manager’i başlatmak için (tabi öncelikle MSDE’yi kurduğunuzu var sayıyoruz.) yukarıdaki şekilde görüldüğü gibi Başlar>Programlar>SQL Server>Enterprise Manager’i

Veritabanı Programlama 2

BYTE

17

om ili. c

Resim 6

Resim 7

diniz ise resim 7’deki gibi bir ekran elde edeceksinizdir. (Resim 7) SQL Server kaydını kaldırmak oldukça kolaydır: 1. Kaydını kaldırmak istediğiniz SQL Server’i fareniz ile sol tıklayın. (Resim 8) 2. Gelen doğrulatma mesaj kutusunda [yes]’ i tıklayın.

w.

jav

ad

yapacaksanız, Veritabanı Yöneticisine danışmanız daha iyi olacaktır. Kendi kurduğunuz SQL Server’e bağlanacaksanız, kurulum aşamasında hangi yetkilendirmeyi seçtiğinizi hatırlayın ve aynı seçeneği burada da seçip [Next]’i tıklayın. (Resim 6) 7. Hangi SQL Server Goup’a ekleyeceğinize karar verin ve işaretleyin. İlk defa kullanıyorsanız, standart ayarları takip etmeniz önerilir. [Next]’i tıklayın. 8. Uygun sunucuyu seçin ve [Next]’i tıklayın. 9. Kayıt işlemi burada sona erdi. Başarılı bir şekilde ekleme işlemini yerine getir-

İPUCU:

ww

Bir SQL Server kayıt bilgisi silindiğinde SQL Server’de yer alan bilgiler kaybolmaz, silinmez sadece sizin Enterprice Manager’iniz kaydı silinen SQL Server’i tekrar kaydetmediğiniz sürece yönetemez.

18

BYTE

Veritabanı Programlama 2

Enterprise Manager ile Veritabanı Bakım ve Yönetimi Herhangi bir VTYS’de olduğu gibi SQLServer’de de asıl verilerin tutulduğu yer veritabanıdır. Bir veritabanı bir çok nesne ve verilerle birlikte kullanıcı tanımları ve haklar gibi rolleri de içerir. (Resim 9) SQL Server 2000’de verilerin ve tanımlamaların yer aldığı bir veritabanı dosyasının yanı sıra bir de log dosyası yer alır. Veritabanı dosyası *.mdf uzantılıdır. Log dosyası ise *.ldf uzantılı bir

om ili. c

Resim 8

Resim 9

log dosyasına işlenir. Veritabanı yönetimi, veritabanı dosyalarının düzenlenmesi, oluşturulması ve silinmesi işlemlerinden ibarettir. Bir SQL Server’deki en yetkili standart kullanıcı sa(system admin-sistem yöneticisi) kullanıcısıdır. Bir veritabanı dosyasındaki en yetkili standart rol dbo (database owner – veritabanı sahibidir)

ww

w.

jav

ad

dosyadır. Bu tür veritabanı dosyalarına ait bilgileri görmek için Enterprise Manager’de herhangi bir veritabanı dosyasını sol tıklayın ve çıkan menüden properties’i tıklayarak detay bilgilerine erişebilir, bir kısım değerleri değiştirebilirsiniz. Transaction log dosyaları aslında ilk bakışta pek de önemli bir dosya değil gibi görünür. Ama aslında bir çok bilgi işlem en kara günlerden bu loglar sayesinde kurtulur. Transaction log dosyaları, SQL Server’da verilerin depolanması ve kurtarılması durumunda oldukça büyük önem arzetmektedir. Bir tablonun içerdiği verilerde yapılan değişiklikler önce transaction log dosyasına yazılır, ardından veritabanına kaydedilir. Bu sistem başta veritabanına yapılan kayıt işleminin tutarlılığını sağlamak için böyle geliştirilmiştir ve hemen her VTYS’de aynı şekilde çalışır. SQL Server’da INSERT, UPDATE veya DELETE komutları çalıştırıldığında oluşan değişiklikler transaction

Enterprise Manager ile Yeni bir Veritabanı Oluşturmak Enterprise Manager ile bir çok işlem gibi veritabanı oluşturma işi de görsel bir arayüzden gerçekleştirilebilir. Bu işlem için, öncelikle Enterprise Manager’e girin. Ardından Databases üzerinde sağ tıklayarak New Database seçin. Sizden veritabanı adı girmeniz istenecektir. İstediğiniz bir veritabanı adını girin. Bir veritabanı tanımlanma aşamasında ya da tanımlandıktan sonra şu parametrelerini de ölçeklemek gerekir. Ölçeklemek’ten kasıt projenin büyüklüVeritabanı Programlama 2

BYTE

19

om

ğüne göre performans-kapasite dengesini oturtmaktır. Bu biraz da veritabanı konusunda tecrübeli olmanızı gerektiren bir işlemdir.

yacağını düşündüğünüz transaction log kayıtlarını truncate edebilirsiniz (silebilirsiniz). Database Seçenekleri: Veritabanı seçenekleri, veritabanın ölçeklenmesinde etkili bir diğer faktördür. Bu seçenekler kısaca:

ili. c

Bir Veritabanını Ölçeklemek

Autoshrink: Veritabanının otomatik olarak küçültülmesini ayarlayan özelliktir. Bu özellik seçili iken veritabanı dosyası ve log dosyası belli aralıklarla sistem tarafından otomatik olarak gereksiz alanları dosyadan çıkarılmaya zorlanır.

ad

Enterprise manager ile bir veritabanının özelliklerini görmek için öncelikle o veritabanına dosyası seçilir. Daha sonra bu veritabanının üstünde sağ tık yapılır. Ardından properties seçeneği tıklanır. Ardından options sekmesi tıklanarak üstünde değişiklikler yapılır.

dbo use only: Bu özellik seçildiği anda sistemde login olan kullanıcılar ve db_owner sabit sistem rolüne haiz kullanıcılar dışında diğer kullanıcılar bu veritabanı dosyasını kullanamazlar. Halihazırda sisteme login olan kullanıcılar ise sistemden çıkmaları halinde db_owner rolüne haiz değil ise tekrardan veritabanına erişemezler.

jav

Veritabanının boyutu: Veritabanına kayıt girdikçe ya da yeni tanımlamalar - nesneler girdikçe boyutu büyür. Bir azami boyut tanımlanması halinde bu değere çıkılmasından itibaren depolama biriminde (harddisk) yer gerektiren veritabanı işlemleri yapılamaz.

ww

w.

Transaction log dosyası azami boyutu: Bir veritabanında yapılan kayıt ekleme, kayıt silme ve kayıt güncelleme işlemlerinin veritabanı log dosyasında işaretlendiğini biliyoruz. Bu dosya üstünden veritabanının bütünlüğünün sağlandığına da değinmiştik. İşte bu dosya da belirtilen azami boyutun üstüne çıkarsa UPDATE-INSERT-DELETE işlemleri gerçekleştirilemeyecektir. Transaction Log’a işaretlenmesi gereken hiçbir şey çalıştırılamayacaktır. Bu tür durumlarda bir alternatif olarak artık işinize yarama-

20

BYTE

Veritabanı Programlama 2

read only: Veritabanı dosyasının sadece okunabilir olmasını sağlayan seçenektir. Bu seçenek seçildiğinde kullanıcılar veritabanından veri okuma (SELECT) komutlarını çalıştırabilir ancak DELETE, UPDATE, INSERT gibi DML komutları ile ALTER, CREATE ve DROP gibi DDL komutlarını çalıştıramaz. single user: Veritabanı dosyasını aynı

om

nekleri kullanır. Farklı olarak toplu işlemler için daha az log tutar. Örneğin SELECT INTO vb. gibi komutlar için daha kısıtlı bir log tutulur.

3. Simple Recovery Model: En basit transaction log tutma seçeneğidir. Daha çok küçük ölçekli ve deneme amaçlı veritabanlarında veya sadece okuma amaçlı kullanılan veritabanı dosyaları için kullanılır. Bunun dışında ticari amaçlı veritabanı uygulamalarında bu türden bir kurtarma modelinin seçilmemesi gerekir.

ili. c

anda bir tek kişinin kullanmasını sağlar. Bu komut çalıştırıldığı anda veritabanında birden fazla kişi bağlıysa bu özellik açıldıktan sonra kullanmaya devam ederler ancak bağlantı kesildiği andan itibaren bir tek kullanıcı kuralı dikkate alınır. Kurtarma Modeli Seçenekleri: Veritabanı ölçeklemede bir diğer seçenek de veri kurtarma modelidir. Bu işlem için olan seçenekler de aşağıdaki şekildedir.

ad

1. Full Recovery Model: Bu seçenekte veritabanı üstünde yapılan işlemlerin logları tutulur. Bir veri kurtarma işleminde, log dosyaları ile birlikte veritabanı kayıtları da elde edildikten sonra veritabanı kurtarma işlemi gerçekleştirilebilir. Standart olan kurtarma seçeneği bu seçenektir.

Query Analyzer, SQL ve T-SQL temelli komutlarla Veritabanı Sunucularının konfigüre edilmesini sağlamanın yanı sıra SQL ifadelerini çalıştırma ve analiz etme konusunda faydalanılmak üzere geliştirilmiş bir SQL Server bileşenidir. Yine Query Analyzer ile bir veritabanına erişebilmek için yetkilendirilmiş bir işletim sistemi kullanıcısı veya domain kullanıcısı yahut veritabanı sunucusu yöneticisi hesabına sahip olmak gerekir. Bu kullanıcı grubunun hangisi veya hangilerinin geçerli olacağı, Enterprise Manager ile bir veritabanına sa (sistem yöneticisi) veya eşdeğeri bir hesap ile bağlandıktan sonra [properties\Security] sekmesinden düzenlenebilir. Yine nesneler için yetkiler Enterprise Manager sayesinde düzenlenebilir. Bir sonraki ünitede anlatılan T-SQL

jav

2. Bulk_Logged Recovery Model: Full Recovery Model ile yaklaşık aynı seçe-

w.

İPUCU:

SQL Query Analyzer

ww

Query Analyzer’i Enterprice Manager’den çağırmak mümkündür. Bunun en büyük faydası yetkilendirme bilgilerini yeniden girmemize gerek kalmamasıdır. Bu işlem için Enterprice Manager’dan bağlanmak istediğiniz veritabanını seçtikten sonra [Tools\SQL Query Analyzer]’i tıklamak yeterlidir.

Veritabanı Programlama 2

BYTE

21

om ili. c ad

jav

Resim 10. (1) Query Analyzer’de T-SQL komutlarının yazıldığı sorgu penceresi. (2) Sonucun ne olarak görünmesini istediğinizi bu menüden belirleyin. (3) Sorgunuzda bir sentaks hatası olup olmadığını denetlemek için bu kısmı kullanabilirsiniz. (4) Sorgunuzu çalıştırmak için bu kısmı kullanın.(F5 tuşuna da basabilirsiniz.) (5) Hangi veritabanında çalıştığınızı buradan görüp değiştirebilirsiniz. (6) Çalıştırma planını görmek için burayı tıklayın. (7) Nesneleri görmek için kullanılan menü ve nesneler. (8) Sorguların sonuç ekranı. (9) Şablonlara erişmek için bu menüyü kullanın.

w.

komutları ve Saklı Prosedür, Trigger, Cursor gibi veritabanın nesnelerinin oluşturulması esnasında Query Analyzer programı kullanılacaktır.

ww

SQL Server Query Analyzer’in belli başlı özellikleri: 1. T-SQL ifadelerini çalıştırmak için bir metin editörü 2. ANSI-SQL ve T-SQL için Sentaks

22

BYTE

Veritabanı Programlama 2

boyama 3. Nesne gezgini ve nesne arama seçenekleri ile bir veritabanını veya üstünde yer alan nesnelere erişim kolaylığı. 4. Tablo oluşturmak gibi bir çok işlem için kullanılabilir hazır T-SQL şablonları. 5. Bir etkileşimli hata ayıklayıcı ile Saklı yordamlar için analiz imkanı. 6. Resultset’leri metin (text) temelli veya ızgara (grid) temelli sunabilme. 7. Grafik ve metin temelli Sorgu çalış-

om

ad

Veritabanında, kullanıcı bilgileri, haklar, nesneler ve özellikleri gibi bilgilerin tutulduğu veri tabloları için birçok literatürde Veri Sözlüğü (Data Dictionary) terimi kullanılır. Veritabanı oluşturulduğunda bir çok verinin yer aldığı bir çok tablo sistem tarafından oluşturulur ve sistemin kendi ihtiyacı olan bilgiler tutulur. Bir de proje tarafından referans alınan ve il listesi, tür kodları ve benzeri gibi sabit kayıtları içeren tablolar vardır ki bu türden tablolara da look-up table(referans tablosu) denir.

zer ile de yapmak mümkündür. Hatta bu ortamda doğrudan kodlarla muhatap olduğumuz için daha fazla detaya hükmetme şansına sahibiz. Bu kısımda, bir önceki bölümde Enterprise manager ile nasıl yapılacağını anlattığımız veritabanı oluşturma işini T-SQL komutlarını kullanarak nasıl yapabileceğimize değineceğiz. Bu işlem için kullanacağımız komutu, Create Database deyimini bir önceki kitapçıkta özet olarak anlatmıştık. Genel kullanımı şu şekildedir:

ili. c

İPUCU:

jav

CREATE DATABASE veritabani_adi [ON [PRIMARY] (NAME= veri_dosyasi_adi, FILENAME= fiziksel_veri_dosyasi_adi [, SIZE = veri_dosyasi_boyutu ] [, MAXSIZE= maksimum_veri_dosyasi_ boyutu ] [, FILEGROWTH=veri_artim_miktari]) [LOG ON (NAME= log_dosyasi_adi, FILENAME= fiziksel_log_dosyasi_adi [, SIZE = log_dosyasi_boyut] [, MAXSIZE= maksimum_veri_dosyasi_ boyutu ] [, FILEGROWTH=veri_artim_miktari] ) ]

w.

tırma planı ile Query analiz etme seçeneği ile daha az kaynak tüketen sorgulamalar geliştirme imkanı 8. Index ayarlama sihirbazı ile T-SQL komutlarını analiz ederek daha iyi sorgu performansı için bir tablo üstünde ek bir index alana gerek olup olmadığını bulabilme.(İndeksler hakkında geniş bilgi için serinin ilk kitabına bakınız.) (Resim 10)

ww

Query Analyzer ile Veritabanı Oluşturmak: Create Database

Enterprise manager ile görsel ortamda yaptığımız hemen her şeyi Query Analy-

PRIMARY: Bazen veritabanı tanımlanırken birden fazla dosya üstünde veri saklayabilecek bir şekilde tanımlanabilir. Bu durumda birinci dosya PRIMARY ile belirtilen dosyadır. Bir dosya bir ana gruba sahip olabilir. PRIMARY deyimi kullanılmazsa bir veritabanı için tanımlanan ilk dosya PRIMARY dosya olarak kabul edilir. Bütün veritabanı tanımlama tabloları ve değerleri birincil dosyada saklanır. Birincil veri dosyası *.mdf ’ dir. İkincil veri Veritabanı Programlama 2

BYTE

23

om

Bir önceki kitapçıkta da kullandığımız dbKutuphane veritabanını oluşturalım: Başlangıç boyutu 1MB olsun. En fazla 1GB‘a kadar olmak üzere her seferinde veri dosyası %20’si kadar artırılmak üzere en fazla 1GB’a kadar çıkarılabilsin. Veri dosyaları için isim kutuphane_data olsun ve fiziksel dosya adı ‘D: \data\dbKutuphane.mdf ’ olsun. Loglar için başlangıçta sadece 1 MB yer ayrılsın. Ancak bu alan dolarsa, her seferinde 1MB olmak üzere en fazla 1GB’a kadar sistem tarafından çıkarılabilisin. Loglar için sistem dosya adı kutuphane_log olsun ve fiziksel dosya adı ‘D:\data\dbKutuphane.ldf ’ olsun.

ili. c

dosyalarının uzantısı ise *.ndf ’ dir. veritabani_adi: Veritabanına vermeyi planladığınız isim. Bu isim VTYS tarafından geçerli kabul edilen ve T-SQL ile gönderimde bulunmak istediğiniz (ileri seviyeli işlemlerde) isim. veri_dosyasi_adi: Veritabanının fiziksel adı. Bu isim işletim sistemi tarafından bilinene ismidir.

ad

fiziksel_veri_dosyasi_adi: İşletim sistemi üzerinde saklanacak dosyanın adını ve yolunu belirtir.

CREATE DATABASE dbKutuphane ON PRIMARY (NAME =kutuphane_data, FILENAME= ‘D:\data\dbKutuphane.mdf’, SIZE = 1, MAXSIZE = 1GB, FILEGROWTH = 20% ) LOG ON( NAME = kutuphane_log, FILENAME= ‘D:\data\dbKutuphane.ldf’, SIZE = 1MB, MAXSIZE = 1GB, FILEGROWTH = 1MB )

jav

veri_dosyasi_boyut: Veri dosyasının MB cinsinden boyutunu belirtir. En az ve standart değer 1MB’tır. Bir birim belirtilmezse MB olarak alınır ancak diğer hafıza birimlerini de belirterek kullanmak mümkündür. GB, MB, KB gibi.

w.

maksimum_veri_dosyasi_boyutu: Bu veritabanının ne kadar boyut ile sınırlandırılacağını belirtir. Şayet bir değer girilmezse, tüm disk doluncaya kadar veritabanı dosyasına veri eklenebilir.

ww

artim_miktari: Veritabanının başlangıçta belirtilen boyutu dolduğunda sistem tarafından boyutu otomatik olarak artırılır. Bu aşamada veritabanının boyunun ne kadar artırılacağı bu parametre ile belirtilir. % oran değeri verilebileceği gibi sabit bir artım boyutu da girilebilir. Örnek 1:

24

BYTE

Veritabanı Programlama 2

Loglamadan Tablo Boşaltma: Truncate Table Bir tabloda yer alan tüm kayıtları silmek ama bu silme işlemleri için de satır bazlı birer transaction log oluşturulmasının istenmediği durumlar olabilir. (Özellikle replication ile ilgili uygulamalarda). Bu tür durumlarda TRUNCATE TABLE komutu kullanılır.

TRUNCATE TABLE tablo_adi

fark olurdu? 1. işlemden sonra, kitapNo alanına en son verilen kitapNo değerinin bir fazlası(bir sonraki daha doğru olur.) verilirdi. 2. işlemde ise kitap no tekrardan 1 değerinden(ilk verilen değerden) başlar ve eklenen her kayıt için artmaya devam eder. TRUNCATE TABLE komutu, yabancı anahtar zorlayıcılarına takılır. Bunun anlamı şudur, ödünç tablosunda, kitapNo yabancı anahtar olarak tanımlandığı için; kitap tablosu üstünde TRUNCATE komutu çalıştırıldığında, komut çalıştırılmayacaktır. Aksine

ad

ili. c

Burada, tablo_adi argümanı, İçeriğini satır satır loglamaksızın boşaltacağımız tablo adını ifade eder. TRUNCATE TABLE ile WHERE cümleciği olmayan DELETE komutu aynı işlevi yapar; bir tablonun tüm satırlarını boşaltır. TRUNCATE TABLE komutunu, WHERE koşulu olmayan DELETE cümlelerinin yerine kullanmak daha hızlı bir tablo boşaltma yöntemidir. Sistem kaynaklarını da TRUNCATE komutu daha az meşgul eder. Çünkü, her bir silinen satır için log oluşturmasında gerek yoktur. TRUNCATE TABLE komutunun neticesinde, tablonun sadece içerdiği veriler silinir. Kolonlar, zorlayıcılar, indeksler, trigger’ler ve benzeri diğer tablo üstünde yer alan tanımlamalar silinmez. Bu tür tanımlamaların da silinmesini istiyorsanız, DROP TABLE komutu ile tabloyu silmek gerekir.

om

Genel kullanımı şu şekildedir:

çalıştırılır. Çünkü DELETE komutu log tutar ve bu nedenle bir TRIGGER tetikleyebilir. Ancak TRUNCATE komutu log tutmadığı için trigger harekete geçiremeyeceği önlem olarak komut çalıştırılmaz. TRUNCATE TABLE komutu son olarak, indeksli görünüm(view) ‘lere iştirak eden tablolar için de çalıştırılamaz. TRUNCATE TABLE komutu, standart olarak tablonun sahibine(owner) aittir. Bu hak başka rollere ya da kullanıcılara aktarılamaz.

jav

w.

Örnek 2:

DELETE * FROM kitap

DELETE * FROM kitap

ile

ww

TRUNCATE TABLE kitap

Komutlarının herhangi biri çalıştırılsa idi, daha sonra eklenecek bir kitap için sistem tarafından verilen kitapNo(kitap tablosunun otomatik artan anahtar alanı) alanı değeri arasında ne

Bir Sorguyu Query Analyzer ile Analiz Etme SQL Query Analyzer ile bir sorgunun çalıştırılma planını görsel ve metin bazlı olarak detaylı görerek üstünde optimiVeritabanı Programlama 2

BYTE

25

om ili. c ad

Bu özelliği tekrar kapatmak için çalıştırmamız gereken komut oldukça basit: SET SHOWPLAN_TEXT OFF Go

w.

jav

zasyon yapmak mümkündür. Böylece aynı sonucu daha hızlı üreten sorgular yazmak mümkün olabilmektedir. SQL Server Query Analyzer ile görsel çalıştırma planını görmek için 1. [Query>Display Execution Plan] seçeneğini işaretleyin. Kısayol olarak CTRL+L’yi de kullanabilirsiniz. 2. Daha sonra analiz etmek istediğiniz sorguyu yazıp çalıştırın. 3. Ardından [Estimated Execution Plan] sekmesini tıklayın. SQL Server’in çalıştırma planını metin ortamında bir resultset olarak döndürmesi için şu komutu kullanabilirsiniz:

ww

SET SHOWPLAN_TEXT ON go

Dedikten sonra çalıştırdığınız bütün sorgular için bir metin bazlı çalıştırma planı da görüntülenecektir.

26

BYTE

Veritabanı Programlama 2

dememiz yeterli olacaktır. Ayrıca daha özet ama kapsamı geniş bir çalışma planı çıktısı almak için SQL Server’in SHOWPLAN_ALL özelliğini açabiliriz. Bunun için de şu komutu

UYARI Bir tablo oluşturacak SQL cümleciğini çalıştırma planını görmek üzere Query Analyzer ile SQL Server’e gönderdiğimizi varsayalım. Bu durumda tablo oluşmayacağı için bu tabloya kayıt eklemeye kalkıştığımızda böyle bir tablonun olmadığına dair bir hata alırız.

SET SHOWPLAN_ALL ON go

metin temelli çalıştırma planı daha tek düzedir. Her bir parametere birer satır gibi gösterilir. Görsel çalıştırma planı ise her bir düğüm için detaylı istatistikleri düğümün üstüne gelindiğinde gösterir. Ayrıca her bir düğüm için bu düğümde yapılan işlemleri özetleyen bir ikon ile gösterilir. Bu ikonların anlamı için SQL Server ile birlikte kurulan Books Online ya da MSDN’den yardım alabilirsiniz. Books Online’den Query Analyzer’in komut ekranına ‘Query Analyzer’ yazıp bu iki kelimeyi seçtikten sonra SHIFT + F1 tuşuna basmanız yeterlidir. Bulunan konulardan en baştakini tıklayarak ikonlar hakkındaki detaylara erişebilirsiniz. Diğer konularda da bilgi almak için aynı yolu kullanabilirsiniz.

ili. c

dedikten sonra çalıştırılan bütün sorgular özet çalışma planı verilecektir. Aynı şekilde bu özelliği tekrardan kapatmak için bu özelliği kapatmak yeterli olacaktır bunun için de şu komutu çalıştıracağız:

om

çalıştırmamız gerekir:

SET SHOWPLAN_ALL OFF Go

ww

w.

jav

ad

Çalıştırma Planını gösterirken SQL server kendisine gelen sorguları çalıştırmak yerine analiz ederek hangi işlemlerden geçerek çalıştırılacağını, hangi işlemin yaklaşık ne kadar zaman alacağını gösterir, öngörür. Çalıştırma planı özellikle DML(Veri İşleme Dili) için ve T-SQL ifadeleri için kullanılır. Bir Saklı Prosedür’ün, bir komut tarafından çağrılan Tetikleme(Trigger)’in daha az kaynak tüketerek daha kısa sürede nasıl gerçeklenebileceğini analiz etmede faydalanacağız. Örneğin bir SELECT ifadesini gerçeklemek için SQL Server tüm tabloyu taramak zorunda kalabilir(table scan) Bunu önlemek için bu tabloda bir birincil anahtar alan oluşturursak, bu sorgu artık table scan yerine indeks bulma (index seeking) yapacaktır. Bunun maliyeti de bir çok durumda table scan’a göre daha kısadır. SHOWPLAN_TEXT ve SHOWPLAN_ALL özellikleri yardımı ile alınan

Resim 11

(Resim 11)

Import & Export Data Bazen MS Access(*.mdb), text dosyası ya da MS Excel ve benzeri gibi yapılanVeritabanı Programlama 2

BYTE

27

om ili. c

Resim 12

jav

İPUCU:

ww

w.

MSDOS komut ortamından veritabanına erişmek için eski adı ile isql(Sybase’de halen aynı adla kullanılıyor), SQL Server’de ise osql.exe programı kullanılabilir. Bunun için komut satırını açtıktan sonra osql deyip enter’i tuşlayın. Ardından kullanıcı adı - şifreyi girin. SQL komutlarınızı yazıp go komutu ile bitirdikten sonra enter tuşuna basarak sonucunu görebilirsiniz. Bu programı sonlandırmak için exit yazıp enter’i tuşlamanız yeterlidir. Programın daha detaylı kullanımı için BOL(Books Online)’den yardım alabilirsiniz.

28

BYTE

1. Bunun için öncelikle [Başlat\ Programlar\Microsoft SQL Server\ Import and Export Data] takip edilerek program başlatılır. 2. Uygun olan kaynak seçilir. Örneğin MS Access(*.mdb) 3. Uygun olan hedef seçilir. Örneğin MS Access’den SQL Server’e veri alacaksak, hedef SQL Server’dir. 4. Kaynakta var olan tablolar getirilir ve kullanıcının hangi tabloları aktarmak istediği sorulur. İstediğiniz tabloları işaretleyin. 5. Hemen çalıştırmak için Run Immediately seçeneğini kullanın. 6. Bir sonuç ekranı ile size kaç tablonun aktarıldığı gösterilecektir. SQL Server’den başka bir formata veri aktarmak için de yapılması gereken işlemler aynıdır. Değişen tek şey hedef ile kaynak türlerinin değişmesinden ibarettir.

ad

dırılmış dosyalardan veri almak veya bu türden bir dosyaya veri aktarmak durumunda kalabiliriz. Bu durumda Import & Export Data toolu kullanılır. (Resim 12)

Veritabanı Programlama 2

SQL Service Manager

om

düşey çizgi 4. Durduruldu: Yuvarlak içerisinde kırmızı kare

ili. c

SQL Server ve XML Desteği

Resim 13

ww

w.

jav

ad

SQL Server’i başlatmak, durdurmak veya duraklatmak için kullanılan basit bir hizmet yönetim programcığıdır. (Resim 13) Bu servis çalışırken sistem çubuğunda (sistem saatinin hemen yanı) durumunu gösteren (çalışıyor, duruyor, duraklatılmış) ikon ile birlikte görünür. Bu ikon çift tıklanırsa resimde görünen ekran açılır. İşletim Sistemi kapatılıp açıldığında otomatik olarak başlatılmak için bu seçeneği işaretli olarak bırakmak gerekir. SQL Server’de 4 farklı durum için durum ikonu mevcuttur. Aynı durum ikonunu, Enterprise Manager ile kaydettirdiğiniz her bir Veritabanı için de görebilirsiniz. Bu 4 ikon şu şekildedir:

XML, yazılım ve donanımdan bağımsız veri taşıma ve saklama standardı olup eXtensible Markup Language (Genişletilebilir İşaretleme Dili) kelimelerinden elde edilmiş bir ankronimdir. XML ile işaretlenmiş bir bilgi bir cep telefonunda bir PC’de gösterildiği kadar kolay gösterilebilir. XML ayrıca farklı platformlarda çalışan uygulamalar arasında veri paylaşımı için ve HTML’den arındırılmış olarak bilgilerin Web’e açılması için de kullanılır. XML de verinin görünümü ve sunumu ile ilgilenen işaretleme dili HTML (Hiper Metin İşaretleme Dili) gibi SGML (Standartd Generalized Markup Language)’den türetilmiştir. SGML çok geniş bir dil olduğundan Web’de tam olarak kullanılması oldukça zordur. Bu nedenle bu türden geliştirmelere gidilerek amaca özel kısıtlı elemanlardan oluşan işaretleme dilleri elde edilmiştir. SQL Server 2000 veri aktarımı ve saklama konusundaki bu son teknoloji için şu destekleri sağlar: •SQL Server’e URL üstünden erişim. •XML Data Schema desteği ve bu şemalar üstünde XPath sorgulama. •XML içerikli veri çekme ve XML’den veri ekleyebilme.

1. Çalışıyor, bağlanılmadı: yeşil ok 2. Çalışıyor ve bağlanıldı: Yeşil Yuvarlak içinde beyaz ok 3. Duraklatıldı: Yuvarlak içinde iki

Veritabanı Programlama 2

BYTE

29

om

ili. c

Nesneler veritabanı yapıları içerisinde yer alır. SQL Server ilk kurulduğunda kendi ihtiyaçları için bir dize veritabanı ve her bir veritabanı da kendi içerisinde sabit bir dize nesne içerir. Bunlara VTYS’nin kendisinin ihtiyacı vardır. Bir SQL Server kurulduğunda şu veritabanları standart olarak kurulur: Master: Hangi veritabanları oluşturuldu, disk kullanımı, kullanıcı hesapları, sistem ayarları vb. gibi veritabanı ile ilgili ortam değer ve değişkenleri bu veritabanında yer alır.

Resim 14

ad

XML Web Servisleri ile de bir veritabanınızı XML destekli olarak Web’e veya başka bir kurumun kullanımına istediğiniz kısıtlar doğrultusunda, açabilirsiniz. XML konusunu serinin son kitabında detaylı olarak serinin son kitabı ‘XML ve Web Servisleri’nde bulabilirsiniz.

jav

Örnek 3: Bir sorguda tablolara arsında hiyerarşik ilişkiyi de içeren XML çıkış üretmek İçin SELECT * FROM kitap FOR XML RAW

w.

kullanılır.

SQL Server’de Veritabanı Nesneleri

ww

Bu kısımda anlatılan nesnelerin bir kısmı bir önceki kitapçıkta detaylı olarak incelenmişti. Diğer bir kısmı da bu kitapçıkta anlatılacaktır. Önemli olduğu düşünülen nesneler hakkında detaylı bilgi bu seride yer almamaktadır.

30

BYTE

Model: Yeni oluşturulacak tüm veritabanları için bu veritabanı model alınır. Yani şablon veritabanıdır. Oluşturulacak her veritabanında olması istenen değişiklikler (tablo, saklı prosedür, trigger vs.) bu veritabanında yapılır.

Veritabanı Programlama 2

Msdb: SQL Server Agent servisi tarafından kullanılan veritabanıdır. Tempdb: Geçici bilgilerin saklandığı veritabanıdır. Bir sonraki ünitede, kullanımı hakkında bilgi verilecektir. Pubs: Örnek bilgilerin yer aldığı veritabanıdır. (Resim 14) Her bir veritabanı içerisinde yer alan nesneler şu gruplardan birine aittir: Diyagramlar: Veritabanındaki tabloların birbiri ile ilişkisini gösterir. Burada mantıksal olarak zorlayıcılar vb. gibi öğelerle ile ilişkilendirilmiş tabloların ilişkilerinin fiziksel olarak görünümü yer alır.

om

SQL Server2000’de Tanımlı Veritabanı Rolleri AÇIKLAMA

db_accessadmin

Veritabanı Erişim Yöneticisi

db_backupoperator

Veritabanı Yedekleme Operatörü

db_datareader

Veritabanı Veri Okuyucusu

db_datawriter

Veritabanı Veri Yazıcısı

db_ddladmin

Veritabanı DDL Yöneticisi

db_denydatareader

Veritabanı engelli veri okuyucu

db_denydatawriter

Veritabanı engelli veri yazıcı

ad

db_owner

ili. c

Rol

Veritabanı Sahibi

db_None

Boş

Veritabanı Güvenlik Yöneticisi

jav

db_securityadmin

ww

w.

Tablolar: Veritabanının kayıtları saklayan mxn boyutlu matrisleridir. Bir çoğu kullanıcı tarafından tanımlanır. Sistem tarafından kurulum aşamasında oluşturulup kullanılan tablolar da yer alır. Tablolar hakkında ilk kitapta yeterince konuşmuştuk, burada sadece şunu da eklemek yeterli olur sanırım: Bir tablo 8060byte’ten daha uzun olmamalıdır. Bu, SQL Server’in disk yönetim birimi ‘sayfa’nın boyutundan kaynaklanan bir sınırlamadır. Tabloları meydana getiren satırlar, sayfalar halinde saklanır ve bir tablonun bir satırı en fazla bir sayfa yer kaplayabilir, ikinci sayfaya taşamaz. Görünümler (view): Gerçekte olmayan

tablolardır. Veritabanında var olan tablolar üstünden select işlemi ile getirdikleri sonuçları ifade eden nesnelerdir. (Bir önceki kitapçıkta hakkında geniş bilgi verilmiştir.) Saklı prosedürler (Stored Procedures): Bir dize T-SQL(MSSQLServer - Sybase) veya PL/SQL(Oracle) komutunun bir işlevi yerine getirmek üzere paketlenmiş halidir. Kullanıcılar: Sistemdeki nesnelere erişim hakkı verilmiş birer kullanıcı adı ve şifrenin sistemdeki eşdeğeridir. Bir kısmı sistem tarafından oluşturulabileceği gibi örneğin (dbo) daha sonra veritabanı yöneticisi tarafından da ekleVeritabanı Programlama 2

BYTE

31

om

SQL Server 2000’de Tanımlı Server Rolleri AÇIKLAMA

dbcreator

Veritabanı oluşturucusu

diskadmin

Disk Yöneticisi

processadmin

İşlem Yöneticisi

securityadmin

Güvenlik Yöneticisi

serveradmin

Sunucu Yöneticisi

setupadmin

Kurulum Yöneticisi

sysadmin

Sistem Yöneticisi

ad

ili. c

Rol

bulkadmin

Toplu Ekleme Yöneticisi

lamalarına olanak sağlarlar. Kullanıcı Tanımlı Fonksiyonlar: ANSI SQL’de tanımlı fonksiyonların bir kısmını ve bazı VTYS’ler tarafından sağlanan ek fonksiyonları bir önceki kitapçıkta anlatılmıştı. Tüm bu ANSI ve Sistem fonksiyonlarının dışında kullanıcı ihtiyaç duyduğunda fonksiyon da tanımlayabilmektedir. Her ne kadar saklı prosedürler(SP) fonksiyon benzeri bir görevi yerine getirse de kullanıcı tanımlı fonksiyonlar işlev olarak daha özel işlemlerde kullanılır. Tetiklemeler (Trigger): Trigger’ler bir tabloya kayıt eklendiğinde veya silindiğinde otomatik olarak devreye giren özel saklı prosedürlerdir. Özellikle veritabanı tutarlılığını sağlamak için kısıtlayıcıların yetersiz kaldığı durumlarda (örneğin

ww

w.

jav

nebilir. Roller: Veritabanında yapılabilecek belli başlı işlemler, belli nesneler üstünde belli haklar için erişim tanımlayarak oluşturulan hak grubu. Bu gruplar daha sonra kullanıcılara aktarılır. Böylece VTYS üstünde var olan sayısız erişim ve yetkilendirme için denetleme işlemi gruplar üstünden denetlenebilir. Default’lar: Bir alana değer girilmemesi halinde, bir değer ile başlaması için default nesneleri tanımlanır. Örneğin bir hesap yeni açıldığında para yatırmadığınız sürece hesabınızda 0 lira görünür. Kullanıcı Tanımlı Tipler: Her ne kadar ANSI SQL’de standartta tanımlı veri tiplerinin dışındaki veri tiplerini kullanamasak da VTYS’ler bunu aşarak kullanıcının gerektiğinde veri tipi tanım-

32

BYTE

Veritabanı Programlama 2

om

basamaklı güncelleme gibi) kullanılır.

kullanılır. Genel kullanımı şu şekildedir. CREATE USER (Kullanici_adi) identified by (sifre) default

SQL Server ve Kullanıcı Yönetimi

tablespace kullanici_verileri;

Kullanici_adi: kullanıcı adı. Zorunlu.

ili. c

SQL Server’de kullanıcı yönetimi ile ilgili iki tip öğe bulunmaktadır: Roller ve kullanıcılar. Kullanıcı tek bir kişiye ait tanımlamaları içerirken roller yetki ve erişim tanımlamalarını gruplamada kullanılır. Bir rol bir çok kullanıcıya atanarak, haklar üstünde bir gruplamalı erişim yönetimi uygulanabilir. Böylelikle her bir kullanıcının haklarını teker teker oluşturup yönetmek yerine roller ciddi şekilde denetlenebilir. Çünkü bir çok kullanıcı için neredeyse aynı haklar ve yetkiler verilir. Bir okul otomasyonu gerçekleştiriyorsak, bir çok öğretmen kullanıcısının aynı tablo ya da T-SQL ifadelerine erişimleri olmak durumundadır. Öğrenciler için de aynı şey geçerlidir.

ad

sifre: Kullanıcının şifresidir. kullanici_verileri: Kullanıcı verilerinin nerede tutulduğunu belirtir. sa (systemAdmin) rolüne sahip temel kullanıcıdır. Güvenlik nedeniyle bu kullanıcının adını değiştirilmesi önerilir. Ancak bu ayrı bir tartışma konusu.

Yetki verme (Grant Komutu)

jav

Grant komutu ile bir kullanıcının veya tüm kullanıcıların bir T-SQL ifadesine ya da veritabanına erişimi için güvenlik sisteminde izin açmaya yarar. Bu komut ile yapılabilecek hemen her şey Enterprise Manager programı ile de yapılabilir. Genel kullanımı şu şekildedir:

ww

w.

Roller: Birden fazla kişiye verilebilen ve yetkilendirmeler için bir grup oluşturmada kullanılan kullanıcılara yönelik tanımlamalardır. Halihazırda var olan ve değiştirilemeyen rollere sabit sistem rolü(fixed system role) denir. İki grup sabit sistem rolü vardır: sabit server rolleri ve sabit veritabanı rolleri... Kullanıcılar: SQL Query Analyzer’de yeni bir kullanıcı oluşturmak için, create user deyimi

GRANT {ALL [PRIVILEGES] |izin[,....n]} { [(kolon[,...n])] ON {tablo|view} [(sutun[,...n])] |ON {sp|ep|udf} } TO guvenlik_hesabi[,...n] [WITH GRANT OPTION] [AS {kullanici_grubu|rol}]

ALL: Tüm verilebilecek hakların verileceğini gösterir. db_owner, sysadmin ve database object owner tarafından kullaVeritabanı Programlama 2

BYTE

33

om

geçen sütun ad(lar)ı WITH GRANT OPTION: Sadece nesneler için geçerli olan bu deyim ile izin verilen bir kullanıcının bu nesne üstünde başka birine daha kullanım izni verebilme hakkı sağlayabilmesine yarar. AS: Nesne için yetki tanımlaması, kullanım yetkisi olanların dışında Bir kullanıcı grubuna ya da role de yetkilendirme yetkisi açmak için kullanılır.

ili. c

nılabilir. TO: iznin kimlere verileceğini belirtir. guvenlik_hesabi: Bir veritabanı giriş hesabı, işletim sistemi kullanıcısı ya da domain kullanıcısı olabilir. n: Bir önünde geçen şeyin n defa tekrar edebilir olduğunu gösteren yer tutucu. PRIVILEGES: ANSI-92’ye uyumlu olsun diye konmuş bir kelime olup kullanımı isteğe bağlıdır. sp: Saklı Prosedür adı. ep: Genişletilmiş Prosedür adı. udf: Kullanıcı Tanımlı Fonksiyon adı. izin: •Tablolar için SELECT, INSERT, DELETE, REFERENCES veya UPDATE gibi izinlerdir. UPDATE ve SELECT için gerekirse sütun tanımları da verilebilir. Aksi halde verilen izin tüm sütunlar için geçerli olur. REFERENCES ile tablo üstünde bir alana yabancı anahtar olarak referans içeren bir başka tablo vs. oluşturabilme izni verir.

ad

Örnek 4: sp_kitapbul adında bir saklı yordam üstünde SQLServer’e giriş hakkı olan herkese tüm haklarını açalım: GRANT ALL ON sp_kitapbul TO public;

ile bu iş tanımlanır.

jav

Yetki Kaldırma (Revoke Komutu)

ww

w.

•Nesneler için (SP, EP, UDF) ek olarak tek izin vardır; EXECUTE. EXECUTE ile bir nesneye çalıştırılma izni verilebilir. Ayrıca REFERENCES ile referans verebilme izni de sağlanabilir. Nesneler için REFERENCES, View oluştururken kullanılan WITH SCHEMABINDING deyimi ile bu nesnenin ürettiği sonuçları içerebilmesine hak sağlar. tablo: Veritabanında yer alan tablo adı view: Veritabanında tanımlı view adı sutun: adı verilen tablo ya da view’de

34

BYTE

Veritabanı Programlama 2

GRANT komutu ile verilen hakların bir çoğu Enterprise Manager kullanılarak görsel bir ortamda geri alınabilir ancak Query Analyzer ile kod yazarak bu hakları geri almak için REVOKE komutu kullanılır. Genel kullanımı şu şekildedir: REVOKE [GRANT OPTIONS FOR] {ALL [PRIVILEGES] |izin[,....n]} { [(kolon[,...n])] ON {tablo|view} [(sutun[,...n])] |ON {sp|ep|udf} } {TO|FROM} guvenlik_hesabi[,...n] [CASCADE] [AS {kullanici_grubu|rol}]

om

DİKKAT:

Bir saklı prosedür için yetki verme yetkisi dbo’ya aittir.(Yani bu nesneyi oluşturan kişiye) Bu nedenle REVOKE komutu çalıştırıldığında bu saklı prosedürü oluşturan kişinin nesne üstündeki yetkilendirme ve kullanma hakkı kaybolmaz.

ili. c

REVOKE komutunu çalıştırabilmek için sys_admin sabit sunucu rolüne veya db_owner, db_securityAdmin sabit veritabanı rollerine sahip kullanıcı olmak ve elbette nesne için dbo olmak gerekir.

•Nesneler için (SP, EP, UDF) ek olarak tek izin vardır; EXECUTE. EXECUTE ile bir nesneye çalıştırılma izni verilebilir.Ayrıca REFERENCES ile referans verebilme izni de sağlanabilir. Nesneler için REFERECES, View oluştururken kullanılan WITH SCHEMABINDING deyimi ile bu nesnenin ürettiği sonuçları içerebilmesine hak sağlar. tablo: Veritabanında yer alan tablo adı view: Veritabanında tanımlı view adı sutun: Adı verilen tablo ya da view’de geçen sütun ad(lar)ı CASCADE: Hak verme hakkını kaldırırken kullanılır. Yetki verme yetkisi kaldırıldıktan sonra, bu kişiler tarafından yetkilendirilen kişilerin yetkilerini de kaldırır.

ww

w.

jav

ad

ALL: Nesne üstünde verilen tüm izinlerin iptal edileceğini anlatır. FROM: Güvenlik hesabı listesi belirtmede kullanılır. GRANT OPTION FOR: Bu kelimelerle REVOKE komutu çalıştırıldığında kullanıcılılar nesneleri kullanmaya devam eder ancak yeni kullanım hakkı tanımlayamaz. guvenlik_hesabi: Bir veritabanı giriş hesabı, işletim sistemi kullanıcısı ya da domain kullanıcısı olabilir. n: Bir önünde geçen şeyin n defa tekrar edebilir olduğunu gösteren yer tutucu. PRIVILEGES: ANSI-92’ye uyumlu olsun diye konmuş bir kelime olup kullanımı isteğe bağlıdır. sp: Saklı Prosedür adı. ep: Genişletilmiş Prosedür adı. udf: Kullanıcı Tanımlı Fonksiyon adı. izin: •Tablolar için SELECT, INSERT, DELETE, REFERENCES veya UPDATE gibi izinlerdir. UPDATE ve SELECT için gerekirse sütun tanımları da verilebilir. Aksi halde verilen izin tüm sütunlar için geçerli olur. REFERENCES ile tablo üstünde bir alana yabancı anahtar olarak referans içeren bir başka tablo vs. oluşturabilme izni verir.

Örnek 5: ogrenci kullanıcısından saklı prosedür ve tablo oluşturma yetkilerini kaldıralım: REVOKE CREATE PROC, CREATE TABLE FROM ogrenci

Örnek 6: Bir önceki bölümde üstünde yetki tanımVeritabanı Programlama 2

BYTE

35

ww ili. c

ad

jav

w.

om

2. Bölüm

om

T-SQL ve VTYS temelli programlama

ili. c

A

bu dili kullanacağız.

T-SQL ile ANSI-SQL komutlarını çalıştırma: T-SQL ile SQL komutlarını VTYS’ye çalıştırmasını söyleyebiliriz: Bir defada, SQLServer birden fazla SQL komutunu arka arkaya çalıştırabilir. Ancak her bir komutun sonuna go eklememizi ister. Genel yapısı şu şekildedir:

ad

NSI SQL, veritabanı ile ilgili veri işleme ve düzenleme işlevleri için geliştirilmiş bir standart dildir. Ancak bu dil zaman zaman bir programlama dili olmadığından kayıt işlemede yetersiz kalabilmektedir. Çünkü, gelişmiş programlama dillerinde yer aldığı halde bu dilde WHILE, IF gibi temel mantık ve döngü yapıları mevcut değildir. Değişken kullanımı da tanımda yer almaz. İşte Transact-SQL bu noktada devreye giren ANSI 92 dahilinde bir standart dışı dildir. T-SQL’in geliştirilmesindeki en temel amaç, VTYS dışında döngü veya mantıksal karşılaştırma gerektiren konularda ayrıca bir derleyiciye, programlama dili öğrenmeye, sistem oturtmaya gerek kalmadan bu türden işlemleri yerine getirebilecek olanakları sağlamaktır. Bir sonraki konuda yer alan stored procedures, triggers gibi programcıkları yazarken

w.

jav

ANSI-SQL KOMUTLARI BU SATIRLARA YAZILIR

DİKKAT:

ww

T-SQL Microsoft SQL Server ve Sybase için geçerli bir dildir. Oracle kullananların, PL/SQL öğrenmeleri gerekir. Ancak bu iki dil birbiri ile yaklaşık aynı özellikleri haizdir.

go

Örnek 7: Bu projede kullandığımız veritabanını oluşturalım: CREATE database dbKutuphane on default go use dbKutuphane go CREATE TABLE kitaplar( kitapNo INTEGER NOT NULL, kitapAdi VARCHAR(63) NOT NULL, ISBNNo VARCHAR(15), sayfaSayisi INTEGER, kitapOzeti VARCHAR(255)) go

Değişken Tanımlama ve Kullanma: T-SQL kullanmamızın en büyük nedenVeritabanı Programlama 2

BYTE

37

om

tanımlayabiliriz:

DİKKAT:

Örnek 9:

declare @kitapNo INT,@kitapAdi VARCHAR(63)

ili. c

SQL’deki ve bir çok diğer programlama dillerinin aksine, T-SQL’deki değişkenler @ ile başlamak zorundadır.

go

Değişkenlere değer atama,

declare @kitapNo INT, @kitapAdi VARCHAR(63) SET @kitapNo=255 SET @kitapAdi= ‘107 Kimya Öyküsü’ SET @kitapNo=256 -- Artık kitapNo değişkeninin değeri 256,255 silindi. go

ad

lerinden birinin değişken tanımlama olduğunu söylemiştik. Değişken: Programlama dillerinde, değeri daha sonra akışlara göre değişecek bir değer için hafızada(RAM’de) bir yer ayırmak için kullanılan yapıdır. Hafızadaki bu değere daha sonra değişkenin adı ile erişilir ve yeni değeri atanır. Değişkene atanan her yeni değer bir eski değeri siler. Değişkenler şu şekilde tanımlanır:

Örnek 8:

jav

declare @degisken_adi veritipi[(boyut)]

declare @kitapNo INT declare @kitapAdi VARCHAR(63) go

w.

ya da bir satırda birden fazla değişken

İPUCU:

ww

Değişken isimlerinin nelerden oluşması gerektiği hakkında genel bilgi için serinin ilk kitabında [Veritabanı Programlama-I, s .15, Temmuz 2003] bulabilirsiniz. Seride yer alması nedeniyle kurallara burada yeniden bu konuya yer verilmedi.

38

BYTE

şeklinde yapılır. Ancak T-SQL’in asıl amacı SQL’in yeteneklerini artırmaktır. Bundan dolayı değişkenlerin en genel kullanım amacı, bir sorgunun sonucundaki değerlerden birini alıp bir değişkene aktarmaktır.

Veritabanı Programlama 2

Örnek 10: declare @enSonEklenenKitap INT SELECT @enSonEklenenKitap=MAX(kitapNo) FROM Kitap go

Değişkenler ve Sistem Fonksiyonları: Bir veritabanına aynı anda bir çok kişi oturum açabilir. Bir tek oturumda geçerli değişkenlere yerel değişken denir. Bazen bütün oturumlarda geçerli değişkenlere ihtiyaç duyulur. Bu tür durumlarda, ortam değerlini ayarlamak üzere veya çeşitli durumları tanımlamak üzere fonksiyonlar kullanılır. Bu fonksiyonların bir listesini kitabın sonundaki ek kısmında bulabilirsiniz.

om

DİKKAT:

SYBASE’de yapılandırma fonksiyonlarının yerine ortam değerleri, global değişkenler adı ile anılırlar. Kullanım konusunda bariz bir fark olmamasına rağmen her türlü teknik dokümanda isimler bu şekilde kullanılmaktadır.

ili. c

Örnek 11: Üstünde çalışmakta olduğunuz SQLServer’e açıldığından beri toplam kaç kere kullanıcılar veya programlar tarafından bağlanılmıştır? SELECT @@connections; ->>27

başlayıp END ile biten bloklar arasında yazılır. Genel Kullanımı şu şekildedir:

ad

SQL Server’i durdurup yeniden başlatın ve aynı şeyi tekrar deneyin. Şimdi ne görüyorsunuz? Sayının tekrar sıfırdan başladığını göreceksiniz.

BEGIN Program kodları .... ... END

T-SQL’in sunduğu belli başlı akış kontrolleri şunlardır:

jav

Print komutu: Bazen değişkenlerin değerini, hata mesajlarını vs. mesaj olarak görmek isteriz. Bu tür durumlarda, print komutu kullanılır.

Örnek 12: Kitap numarasını SQL sorgusu ile bulalım ve bu sonucu yazdıralım:

w.

declare @sonuc int SELECT @sonuc= COUNT(*) FROM Kitap print @sonuc go ->> 16

ww

Akış Kontrolleri: T-SQL’i ANSI-SQL’e eklenti olarak gerektiren en önemli nedenlerden biri, ANSI-SQL’in akış kontrollerine izin vermemesidir. T-SQL bir programlama dili kadar geniş olmasa da belli başlı akış kontrollerini sağlar. T-SQL’de akış kontrolleri BEGIN ile

İPUCU: T-SQL’de bir satırın dikkate alınmaması isteniyorsa, ‘--’ ile bu belirtilebilir. MSSQLServer ve Sybase’de aynı işlemi C, C++, C#, Java’da olduğu gibi ‘/*..... */’ ile de yapabilirsiniz. SYBASE, ‘--’ işaretini bazı toolarında dikkate almazken, MS SQL Query Analyzer de ‘//’ işaretini dikkate almamaktadır. Aslında ‘--’ SQL92’de tanımlanan bir açıklama belirtici işarettir.

Veritabanı Programlama 2

BYTE

39

om

else if(şartlar) begin ... end

dermek için bir işaret olarak yorumlarlar. En sonunda GO komutu verilmedikçe, Client uygulamaları komutları sunucuya göndermezler, doğaçlama sorgu oturumunun kapanması için de yine GO komutuna ihtiyaç duyulur. T-SQL komutları, GO komutu ile aynı satırda yer alamaz, ancak Go komutundan sonra açıklama gelebilir. Kullanıcının komut yığınları hususunda kurallar riayet etmelidir. Örneğin, Stored Procedure (SP için bkz. Bölüm: 3) çağırırken, SP ya yığının ilk komutu olmalıdır

ww

w.

jav

Bir SQL Server’e bir seferde birden fazla komut gönderilebilir. SQL Server bu komutları yığınlar halinde almaktadır. GO komutu, T-SQL komut yığının sonunu belirtmek için kullanılır. Genel kullanımı, GO şeklindedir. GO aslında kendisi T-SQL’in bir parçası değildir. Query Analyzer, osql ve isql tooları tarafından algılanabilen bir komuttur. SQL Server tooları GO komutunu, T-SQL komutlarından oluşan yığını SQL Server’e gön-

if(şartlar) begin .... end

ad

Go Komutu

BEGIN ile END deyimleri arasındaki kodlar çalıştırılır, if bloğunun en sonuna gider ve takip eden kodları çalıştırır. Şart3 yanlış ise bu aradaki kodları da çalıştırmadan geçer. genel yapısı şu şekildedir:

ili. c

1.IF ... ELSE karar yapısı if(şart1) deki şart1 doğru ise if(şart1)’den sonra gelen BEGIN ile END arasındaki kodlar çalıştırılır. Daha sonra if bloğunun en altındaki kodlardan devam eder. Şart1 doğru değil ise, elseif(şart2)deki şart2’ye bakılır. Bu şart doğru ise, şartı takip eden BEGIN ile END ifadeleri arasındaki kodlar çalıştırılır, if bloğunun sonuna gider ve takip eden kodları çalıştırır. Şart2 de yanlışsa, elseif(şart3)’deki şart3’e bakılır. Şart3 doğru ise takip eden

40

BYTE

Veritabanı Programlama 2

veya EXEC komutu ile birlikte kullanılmalıdır. Ayrıca, bir yerel değişkenin etki alanı komut yığını ile sınırlıdır. Bir yerde GO dendikten sonra, yeni bir yığın başladığından GO komutundan önce tanımlanan ya da değer atanan skaler değişkenler ve içerikleri kaybedilecektir. Ancak aynı şey veriye satır satır erişmek için kullandığımız Cursor’lerde geçerli değildir. Yine SP tanımlayacaksak, bu komutun yığının ilk satırında yer alması gerekir. Aksi halde SQL server hata verir ve SP oluşturmaz. Örnek 13: -- dbKutuphane veri-

om

else begin .... end

•kitabın sayfa sayısı 500’den fazla ise burası çalışır.

declare @sayfaSayisi int SELECT @sayfaSayisi=MAX(sayfaSayisi) FROM Kitap if (@sayfaSayisi>500) BEGIN

END else if(@sayfaSayisi>100) BEGIN

•kitabın sayfa sayısı 100’den büyük, 500 den küçük ise burası çalışır print ‘kitaplıktaki en kalın kitap çok da kalın değil.’

ad

-- Hata verecek çünkü ilk satır olmayan bir yerde saklı prosedür çağrılıyor. -- SP ya yığının ilk komutu olmalı veya EXEC komutu ile birlikte kullanılmalıdır. sp_who GO

jav

tabanını kullanmaya başlaması için -- veritabanına bir kod yığını gönderelim. -- Bir satırlık bir yığın. USE dbKutuphane GO -- Bir T-SQL komut yığını bitti ve yenisi başladı. Bu satır -- bir üst satırda, GO komutu ile birlikte açıklama satırı kullanıldı. DECLARE @degisken VARCHAR(50) SELECT @degisken = ‘Merhaba T-SQL.’ GO -- @degisken adlı değişken artık geçerli değil çünkü T-SQL komut yığını bitirildi.. PRINT @degisken -- Haliyle de bu kısımda hata meydana gelecektir. Çünkü değişken artık yok.. GO -- Yeni bir T-SQL komut yığını burada başladı. -- Burada çağrılan saklı prosedür düzgün olarak çağrılacaktır. -- çünkü yığının ilk komutu... sp_who SELECT @@VERSION;

print ‘Bu kitaplıkta çok kalın bir kitap varmış.’

ili. c

Örnek 14: Kitaplıktaki en kalın kitap hakkında fikir beyan eden T-SQL kodu:

ww

w.

ODBC, OLE-DB ve diğer bileşenler üstünden çalışan uygulamalar, GO komutunu SQLServer’e göndermezler. Böyle

bir komut bu bileşenler üstünden gönderilmeye çalışılırsa bir hata meydana gelir. GO komutunu kullanabilmek için bir izin gerekmez. Sistemdeki herhangi bir kullanıcı tarafından bu komut çalıştırılabilir. Çünkü tek başına veritabanı için hiçbir şey demektir.

Veritabanı Programlama 2

BYTE

41

om

print ‘bu kitaplıkta kalın kitap yok’ END --

İPUCU:

>> bu kitaplıkta kalın kitap yok

if(SELECT MAX(SayfaSayisi) FROM Kitap)>=500 print ‘bu kitaplıkta kalın kitaplar var’ else

END else BEGIN

END go ->> kitaplıktaki en kalın kitap çok da

Bir sorguya bağlı if yapıları kullanılabilir:

w.

if(SELECT MAX(SayfaSayisi) FROM Kitap)>=500 BEGIN print ‘bu kitaplıkta kalın kitaplar var’ END else BEGIN

ww

İPUCU:

Prosedürel dillerde olduğu gibi, tek satırlık if için blok kullanımına gerek yoktur.

BYTE

Herhangi bir dallanmayı iki satıra tamamlayın. Ne görüyorsunuz? (Query Analyzer hata verecektir.) IF yapısının en yaygın kullanımlarından biri de IF EXISTS yapısıdır. Genellikle bir veri ya da nesnenin daha önceden var olup olmadığı bu yapı ile test edilir.

jav

kalın değil.

42

print ‘bu kitaplıkta kalın kitap yok’

ad

•kitabın sayfa sayısı 100’den küçük ise bu kısım çalışır. print ‘bu kütüphanede broşürden başka bir şey yok mu?’

Örnek 15:

Bu durumda bir önceki örneğimizi şu şekilde de kodlayabiliriz:

ili. c

BEGIN ve END, C/C++/C#/Java gibi programlama dillerindeki ‘{‘ ve ‘}’ gibi kullanılır. Aynı işlev için Delphi’de BEGIN ve END kullanılır.

Veritabanı Programlama 2

Örnek 16: Hiçbir kitap kaydı yoksa kitap tablosunu silmek için: IF NOT EXISTS(SELECT * FROM Kitap) DROP TABLE kitap ->>(hiç satır silinmez)

2.CASE deyimi: Case yapısı ANSI92 standartları ile uyumlu bir karar döngüsüdür ve oldukça kısa kod ile etkin programlar oluşturmamıza yarar. Örneğin dışarıdaki kitaplar için ‘dışarıda’ yazabilecek bir kod bu komut sayesinde basitçe yazılabilir. Genel yapısı şu şekildedir:

om

Örnek 18:

...

declare @degisken VARCHAR(29) SELECT ISNULL(@degisken,’boşluk ise bu değer seçilecek’) --

CASE WHEN şart THEN değer [ ELSE değer] END

ili. c

>> boşluk ise bu değer seçilecek

Örnek 17: Ödünç tablosunda yer alan kitap numaralarının bir dökümünü ve içerde ise yanına içeride; dışarıda ise dışarıda yazdıracak T-SQL kodunu oluşturalım:

3.While Döngüsü

WHILE ile, bir işlemi istediğimiz kadar tekrarlatabiliriz. Bu bize tekrar gerektiren işlemlere kolaylık sağlar. Genel yapısı şu şekildedir:

ad

WHILE şart BEGIN Tekrarlaması gereken kodlar buraya yazılır END

Şart sağlanıncaya kadar, BEGIN ile END arasına yazılan kodlar çalıştırılacaktır.

jav

Örnek 19: Integer bir değişken tanımlayalım ve değeri 15 oluncaya kadar bir artıralım. 15 olduğunda da yazdıralım:

SELECT kitapNo,geldiMi, ‘kitapDurumu’= Case WHEN geldiMi = 0 THEN ‘Dışarıda’ WHEN geldiMi = 1 THEN ‘İçeride’ End

w.

FROM odunc

ww

ISNULL fonksiyonu: ISNULL(kontrol_edilecek_deger, null_ ise_cikacak_deger) şeklinde kullanılır. kontrol_edilecek_deger: Bir sütun adı ya da değişken adı olabilir. null_ise_cikacak_deger: Bir sütun adı, değişken adı veya sabit bir değer olabilir.

İPUCU: Query Analyzer’de bir komut ya da kelime hakkında yardım almak isterseniz, (normal bir kurulum için) o kelimeyi seçtikten sonra, klavyenizden Shift + F1 tuşlarına basmanız yeterli olacaktır. Böylelikle, MSSQL Server’iniz ile birlikte kurulan Books Online’den aradığınız kelime ile ilgili konular seçilecektir.

Veritabanı Programlama 2

BYTE

43

om

DECLARE @sayac INT SELECT @sayac = 1 WHILE (@sayac < 15) BEGIN SELECT @sayac = @sayac END

>>Henüz bir kısır döngü hatası olmadı >>Henüz bir kısır döngü hatası olmadı >>Bir alt satır olmasa kısır döngü hatası olacaktı >>sayac : >>16

+ 1

CONTINUE komutu: Break komutu gibi, WHILE döngüsünde bir uç şartı kontrol etmek için kullanılır. Ancak bu komut, break’in aksine WHILE yapısının başlangıcına götürür ve eldeki değerlerle oradan programcığın devam etmesini sağlar. Örnek 21:

ili. c

print ‘sayac :’ print @sayac ->> sayac : >>15

DECLARE @sayac INT SELECT @sayac = 1 WHILE (@sayac < 15) BEGIN SELECT @sayac = @sayac if (@sayac=11) BEGIN Continue END print ‘sayac : ‘ print @sayac

ad

BREAK komutu: Break komutu, WHILE döngüsünden bir uç şarttan dolayı çıkmak üzere kullanılır. BREAK ile while döngüsünden çıkıldığında, WHILE’in END’ini takip eden kodlardan devam edilir.

END

jav

Örnek 20: Yukarıdaki örnek için, sayac değerinin 15 olması durumu bizim için yeterlidir ve bu durumda döngüden çıkmamız gereksin:

+ 1

ww

w.

DECLARE @sayac INT SELECT @sayac = 0 WHILE (@sayac <> 15) BEGIN SELECT @sayac = @sayac + 2 if @sayac > 15 BEGIN print ‘Bir alt satır olmasa kısır döngü hatası olacaktı’ break END else BEGIN Print ‘Henüz bir kısır döngü hatası olmadı’ END END print ‘sayac :’ print @sayac ->>Henüz bir kısır döngü hatası olmadı >>Henüz bir kısır döngü hatası olmadı >>Henüz bir kısır döngü hatası olmadı >>Henüz bir kısır döngü hatası olmadı >>Henüz bir kısır döngü hatası olmadı

44

BYTE

Veritabanı Programlama 2

go

Sonucunda, 1,2,3,4,5,6,7,8,9,10,12,13, 14,15 yazdırılacaktır ama 11 yazdırılmayacaktır. T-SQL ile oluşturulup yönetilebilecek nesneler şunlardır: Örnek 22: (Ev Ödevi)Bir önceki örneği, continue yerine break koyarak tekrar ediniz. Ne sonuç ile karşılaştınız? Cevap: 1,2,3,4,5,6,7,8,9,10

Geçici Tablolar Bazen geçici bir süre için ek tablolara ihtiyaç duyabiliriz. Bu tür durumlarda, T-SQL ile geçici tablolar oluşturup onları

om

DİKKAT:

Geçici bir kitaplar tablosu oluşturalım: CREATE TABLE #kitap( KitapNo INT, KitapAdi VARCHAR(55), ISBNNo CHAR(16) )

ili. c

Daha sonra tabloya, normal bir tabloymuş gibi kayıt ekleyebiliriz: INSERT INTO #kitap SELECT kitapNo,kitapAdi,ISBNNo FROM Kitap WHERE kitapNo < 10

Ya da tabloda yer alan kayıtları

SELECT * FROM #kitap

ad

ÖLÜMCÜL HATA!!! WHILE döngüsünde, döngünün devam edip etmeyeceğini tayin eden şart kısmına, mutlaka sağlanacak bir koşul yazdığınızdan emin olun. Aksi halde, sonsuza kadar çalışacak bir kod yazabilirsiniz ki böyle bir şey pratikte mevcut değildir. Sadece SQL Server’in kilitlenmesine neden olursunuz. Bir önceki örnekte, @sayac değişkeni 14 ve 16 değerlerini alacak anacak asla 15 değerini alamayacaktır. Bu durumda sonsuza kadar çalışacak bir kod yazmış oluruz. Durumu if deyimi içerisinde kontrol ederek ölümcül hatayı engelledik.

jav

İle görebiliriz. Kayıtları güncelleyebiliriz ya da silebiliriz. Ancak bu tablo, oturumu kapattığımız anda veya SQLServer kapatıldığı anda silinecektir. Bunun dışında biz de istediğimiz zaman bu tabloyu bildiğimiz yöntemle silebiliriz:

w.

kullanabiliriz. Geçici tablolar, kullanıcı çıkış yaptığında veya SQLServer kapatıldığında otomatik olarak silinirler. Geçici tablolar oluşturmanın iki yolu vardır: 1.Yöntem: Oturum boyunca geçerli geçici tablolar oluşturmak için kullanılır. CREATE TABLE #tablo_adi( Alan1 tur1[(boyut1)] [[NOT] NULL], Alan2 tur2[(boyut2)] [[NOT] NULL], .... Alann turn[(boyutn)] [[NOT] NULL]

ww

)

Bu şekilde oluşturulan tablolar oturum kapandığında veya SQL Server durdurulduğunda silinir. Örnek 23:

DROP TABLE #kitap go

2.Yöntem: Geçici tablo oluşturmanın bir diğer yolu, tempdb adlı veritabanı dosyasına bir tablo açmaktır. Bu veritabanındaki tablolar, sadece SQLServer kapatıldığında silinir. Genel yapısı şöyledir: CREATE TABLE tempdb..tablo_adi( Alan1 tur1[(boyut1)] [[NOT] NULL], Alan2 tur2[(boyut2)] [[NOT] NULL], .... Alann turn[(boyutn)] [[NOT] NULL] )

Bu yöntemle oluşturulan geçici tablolar, SQL Server durdurulduğu anda kaybolur. Çünkü tempdb adlı veritaVeritabanı Programlama 2

BYTE

45

Örnek 24:

om Resim 15

tanımlanır ve veri bütünlüğünü sağlamayı amaçlayan tanımlamalardır.Bir tablo oluşturulurken, herhangi bir alan için girilebilecek veri türlerini bir grup veya kural ile sınırlamak için kontrol zorlayıcıları kullanılır.

ad

CREATE TABLE tempdb..kitap( KitapNo INT, KitapAdi VARCHAR(55), ISBNNo CHAR(16) )

ili. c

banını SQL server açıldığı anda boşlatacaktır. Bazen, kullanıcı çıkış yaptığı halde geçici tablonun saklanması ihtiyacı olabilir. Bu tür durumlarda, geçici tablo SQL Server kapanıncaya kadar hafızada kalıp, SQL server kapatıldığında silinecekse, tempdb’de tablo açma yöntemi kullanılabilir.

go

(Resim 15)

ww

w.

jav

Örnek 25: (ev ödevi) Yöntem ile geçici tabloyu oluşturun. Daha sonra bu tabloya bir değer girin. Enterprise manager ile tabloyu görmeye çalışın. Query Analyzer ile tabloyu görmeye çalışın (select ile). Query Analyzer’i kapatıp açın. Tablodaki kayıtları seçmeyi deneyin. Ne gördünüz? Aynı işlemi ikinci şekilde geçici tablo açarak onun üstünde test edin.

Örnek 26: Bir il tablosu oluşturacağız. Tüm illerin trafik kodlarının (ülkemizde) 1-81 arasında olduğunu biliyoruz ve telefon kodlarının da 111 ile 999 arasında olduğunu biliyoruz. Bu tanımları tabloya yansıtalım:

Kontrol Zorlayıcıları (Check Constraints): Diğer zorlayıcılar hakkında daha geniş bilgi ilk kitapçıkta verilmişti. Zorlayıcılar genel olarak bir tablo oluşturulurken

46

BYTE

Veritabanı Programlama 2

CREATE TABLE tblIL( ilTrKod INT CONSTRAINT cns_ilTrKod CHECK ( ilTrKod BETWEEN 1 AND 8181), ilAdi VARCHAR(15), ilTelKod INT CONSTRAINT cns_ilTelKod CHECK (ilTelKod BETWEEN 111 AND 999), )

şeklinde oluşturulan il tablosundaki zorlayıcılar il trafik kodu olarak 1-81 aralığının dışında, il telefon kodu olarak da 100 ile 999 arasındaki sayıların dışında bir değer girilmesini önleyecektir.

ww ili. c

ad

jav

w.

om

3. Bölüm

om

Prosedür ne demektir?

ili. c

Saklı Prosedürler (Stored Procedures)

ww

w.

jav

ad

Nesneye dayalı programlama bu kadar popüler değilken, programlar sadece prosedür denilen parçacıklardan oluşurdu. Her bir prosedür, belli bir işlevi yerine getirmek için özenle yapılandırılmış program parçacığıdır. Mesela, iki sayı alıp bunların toplamlarını hesaplayan bir kod parçasını toplayıcı adında bir prosedür içerisine paketleyebiliriz. Bir prosedür, başka bir prosedür içerisinden çağrılabilir. Bu da sık kullanılan işlemler için yazılmış kodların bir defa yazılıp çok defa kullanılmasını böylelikle de programlamayı kolaylaştırmayı amaçlar. Saklı prosedürler, bir çok gelişmiş programlama dilindeki fonksiyon yapılarına karşılık gelir. Birden fazla işlemi, paketlenmiş bir halde bir tek komut ile çalıştırmamız gerektiğinde stored procedures kullanılır. İşlemden kasıt T-SQL ile yapılabilen her şeydir. Stored procedure, 1980’li yılların sonunda Sybase SQLServer ile birlikte kullanıma girdi. En büyük özelliği sorguların önceden hazırlanması (derlenmesi) ve VTYS ile aynı uzayda çalışmasından dolayı daha hızlı sonuç vermesidir. Bir SP oluşturulduktan sonra, veritabanı sunucusunda saklanır. Her ihtiyaç

duyulduğunda aynı sp defalarca çağrılabilir. Cursor gibi oturum kapandığında silinmez. Network bazlı çalışmalarda ağ trafiği ve sistem kaynaklarının kullanımını düzenleyerek de performans artışı sağlar. Bir dize işlem, bir tek paket içerisinde yer alır. Gerektiğinde bir tek komut ile tetiklenebilir. Paketin tamamı çalışıncaya kadar istemde bulunan terminale hiçbir şey gönderilmez. Tüm komutlar bittiğinde bir tek sonuç gönderilir. Bu da bazı durumlarda ağ trafiğini rahatlatır. Bir SP sistem tarafından oluşturulduğu anda şu aşamalara tabi tutulur:

48

BYTE

Veritabanı Programlama 2

1. SP’nin bileşenleri parçalara ayrıştırılır 2. Veritabanı içerisinde table,view gibi başka nesnelere atıfta bulunan referanslar varsa, geçerli olup olmadıkları kontrol edilir. (Geçerli:1-nesne varmı, 2-izin var mı) 3. Kontrollerden geçen SP’nin adı sysobjects tablosuna, kodları ise syscomments tablosuna saklanır. 4. Bu işlemlerle birlikte derleme işlemi yapılır. Normalizasyon işlemleri olarak da anılan bu işlemler sonucunda, ağaç şeması elde edilir. Bu şema da sysprocedures tablosunda saklanır. 5. SP herhangi bir anda çağrıldığında,

om

yapan bir stored procedure’ü kullanma izni olabilir.

SP oluşturma:

ili. c

ilk kez çalışıyorsa bu işlemler gerçekleştirilir. İlk sefa çağrılmıyorsa, kontrol, sorgulama ağacı oluşturma işlemleri yapılmaz ve oldukça hızlı bir şekilde SP’nin derlenmiş hali çalışır. Bundan dolayı sp’ler derlenen nesnelerden biri olarak anılır.

Örnek 27: Çalıştırıldığı tarih itibariyle, aldığı kitapları getirmeyen üyelerin adını-soyadını veren bir SP yazalım:

ad

SP’ler şu faydaları sağlar: 1. Uygulamanın getirdiği bazı iş kuralları prosedür içinde tanımlanabilir. Bir kez oluştuktan sonra bu kurallar birden çok uygulama tarafından kullanılarak daha tutarlı bir veri yönetimi sağlanır. Ayrıca bir fonksiyonelliğin değişmesi ihtiyacı doğduğunda her uygulama için değişiklik yapmak yerine, sadece bir platformda değişiklik yapılır. 2. Tüm prosedürler üstün performansla çalışır ancak birden fazla çalıştırılacak olan prosedürler sorgulama planları procedure tamponcache içinde saklandığından daha da hızlı çalışırlar. 3. Stored Procedure’ler SQL Server start ettikten sonra otomatik olarak çalıştırılmak üzere ayarlanabilirler. 4. Stored Procedure’ler harici olarak kullanılırlar. Trigger’lardan farklı olarak prosedürler uygulama tarafından ya da script tarafından bir şekilde çağrılmak zorundadırlar. Otomatik devreye giremezler. 5. Stored Procedure’lerın içinde SQL sorgulama diline ek olarak T-SQL komutlarını kullanabiliriz. 6. Kullanıcının bir tabloya erişim izni olmasa bile o tablo üzerinde işlem

CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type } [ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ]

ww

w.

jav

CREATE PROCEDURE sp_cezaliUye AS DECLARE @buGun DATETIME SET @buGun = GetDate() SELECT uye.Adi,uye.soyadi FROM odunc INNER JOIN uye ON uye.UyeNo = odunc.UyeNo WHERE geldiMi=0 AND VermeTarihi + VermeSuresi < @buGun GO ->>The command(s) completed successfully.

Daha sonra bu SP şu komut ile çağrılır: EXEC sp_cezaliUye

(Resim 16)

Veritabanı Programlama 2

BYTE

49

om

DECLARE @buGun DATETIME SET @buGun = GetDate() SELECT uyeAdi FROM odunc WHERE geldiMi=0 AND VermeTarihi + VermeSuresi < @buGun ->> The command(s) completed successfully.

Bir SP’nin bittiği yerde go deyimini kullanmak, sp’nizin bittiği yeri tam olarak belirtmenizi sağlayacağından istemediğiniz kodların sp ile birlikte derlenmesine ve istenmeyen hatalar meydana getirmesine engel olur.

PROCEDURE sp_cezaliUye

DROP PROC sp_adi go

Örnek 29: Oluşturduğumuz sp’yi silmek istersek: DROP PROC sp_cezaliUye Go ->>The command(s) completed

jav

DECLARE @buGun DATETIME SET @buGun = GetDate()

Bir SP’yi Silmek: Bir sp’yi artık kullanmayacağımıza karar verdiğimizde silmemiz gerekir. Bu durumda şu komut ile silinir:

ad

Örnek 28: En çok yapılan hata, bir SP’nin çağrılma kodunu da yanlışlıkla SP ile birlikte derlemektir. Aşağıda yanlış yazılmış bir sp yer almaktadır. Doğru ‘go’ ifadesi sizce nereye konmalı? CREATE AS

SP’nin yeni halini kaydettirebiliriz.

ili. c

İPUCU:

SELECT uye.Adi,uye.soyadi FROM odunc INNER JOIN uye ON uye.UyeNo = odunc.UyeNo WHERE geldiMi=0 AND VermeTarihi + VermeSuresi < @buGun --go burada yer almalı EXEC sp_cezaliUye

w.

SP üstünde değişiklik yapmak: sp_helptext sp_adi Sp_helptext sp_cezaliUye

successfully.

SP’ye Parametre Yollama: Bazen SP’ler dışarıdan parametre alabilirler: Örnek 30: Herhangi bir tarih verildiğinde, bu tarihte süresi bittiği halde teslim edilmeyen

ww

dersek: (Resim 17)’de ki sonuç elde edilir. Daha sonra bu içeriği Query Analyzer kod penceresine yapıştırıp, CREATE yerine ALTER yazarsak: ALTER PROCEDURE sp_cezaliUye AS

50

BYTE

Veritabanı Programlama 2

Resim 17

om

DECLARE @buGun DATETIME SET @buGun = GetDate()

Bir çok işlemi kod yazmaksızın yapmayı sağlayan Enterprice Manager’i kullanarak sp’leri de kolayca değiştirebilirisiniz. Bunun için Enterprice manager ile bir sp’yi seçip çift tıklamak yeterlidir.

diyerek sp’yi çağırabiliriz.

Örnek 32: Kitaplar tablosu üstünde aşağıdaki parametrelere göre arama yapabilecek bir SP yazalım. Parametrelerden gelenler için filtreleme yapılsın. Dışarıdan alınabilecek Parametreler: ISBNNo, KitapAdi, Ozeti (Resim 18)

ad

kitapları bulan bir SP yazalım ancak tarih olarak bu günden daha büyük bir parametre alamasın. Böyle bir durum olduğunda, bu günün tarihini versin. CREATE PROCEDURE sp_cezaliUye_1 @referansTarih DATETIME AS DECLARE @buGun DATETIME SET @buGun = GetDate() IF @referansTarih > @buGun

IF (@referansTarih IS NULL) OR (@referansTarih>@buGun) SET @referansTarih = @buGun SELECT uye.Adi,uye.soyadi FROM odunc INNER JOIN uye ON uye.UyeNo = odunc.UyeNo WHERE geldiMi=0 AND VermeTarihi + VermeSuresi < @referansTarih Go şeklinde yazabiliriz. Daha sonra EXEC sp_cezaliUye_2 @referansTarih=’06.20.2003’ ->>(HİÇ SONUÇ DÖNMEYECEKTİR) EXEC sp_cezaliUye_2

ili. c

İPUCU:

jav

SET @referansTarih = @buGun

SELECT uye.Adi,uye.soyadi FROM odunc INNER JOIN uye ON uye.UyeNo = odunc.UyeNo WHERE geldiMi=0 AND VermeTarihi + VermeSuresi < @referansTarih

ww

w.

Bazen dışarıdan gelen parametrelerin isteğe bağlı olması istenebilir. Bu durumda DEFAULT değer atama seçeneği kullanılır. Şayet dışarıdan parametreye değer atanmazsa, geçerli değer default atanmış değer olarak alınır ve işlem yapılır: Örnek 31:

CREATE PROCEDURE sp_cezaliUye_2 @referansTarih DATETIME = NULL -- dışarıdan referans tarihi gelmedi ise, NULL olarak kabul et. AS

ALTER PROCEDURE sp_kitapBul @ISBNNo CHAR(16) =NULL, @KitapAdi VARCHAR(55)=NULL, @KitapOzeti VARCHAR(55)=NULL AS DECLARE @sSQL VARCHAR(500) Set @sSQL= ‘SELECT * FROM Kitap WHERE 1=1 ‘ IF @ISBNNo IS NOT NULL SET @sSQL = @sSQL + ‘ AND ISBNNo = ‘’’ + @ISBNNo + ‘’’’ IF @KitapAdi IS NOT NULL SET @sSQL =@sSQL + ‘ AND KitapAdi LIKE ‘’%’ + @KitapAdi + ‘%’’’ IF @KitapOzeti IS NOT NULL SET @sSQL = @sSQL + ‘ AND KitapOzeti LIKE ‘’%’ + @KitapOzeti + ‘%’’’ --print @sSQL --nasıl bir SQL oluşturduk? EXEC(@sSQL) Go Farklı parametre değerleri ile SP’yi çağırabiliriz: EXEC Sp_kitapBul @ISBNNo=’12345’ EXEC Sp_kitapBul @ISBNNo=’12345’, @kitapAdi=’Yol’

...

Veritabanı Programlama 2

BYTE

51

om Bir sp, başka bir sp’yi çağırabilir.

ili. c

Resim 18

GO

Bu prosedürün döndürdüğü sonucu nasıl alabiliriz?

ad

Sp’den Değer Döndürme: OUTPUT opsiyonu başka bir prosedüre değer döndürmeye yarar. Yani bir prosedür kendisini çağıran başka bir prosedüre kendi ürettiği bir çıktı değerini yanıt olarak verebilir.

@sayi3 + @sayi4 + @sayi5) / 5

jav

Örnek 33: 5 adet tamsayı girildiğinde ortalamalarını hesaplayıp, OUTPUT parametresi ile döndürecek bir sp yazalım:

Bu prosedürdeki ortalama değerini alabilmek için öncelikle bir değişken tanımlayıp sonra prosedürü çalıştırabiliriz.

AS

w.

CREATE PROC ortalayici @sayi1 smallint, @sayi2 smallint, @sayi3 smallint, @sayi4 smallint, @sayi5 smallint, @ortalama smallint OUTPUT

SELECT @ortalama = (@sayi1 + @sayi2 +

ww

DİKKAT:

SP’de bir parametre için default değer tanımı yaparken, bu değerin sabit bir değer veya NULL olması gerektiğine dikkat etmelidir.

52

BYTE

Veritabanı Programlama 2

Örnek 34: Output olarak tanımlı parametre örnek33’te en son parametredir. Daha önce tanımladığımız değişkeni buraya verirsek bu değişkende SP’nin ürettiği değer döndürülecektir. Değeri görmek için daha sonra SELECT komutunu çalıştırıyoruz. (Resim 19) DECLARE @sonuc smallint EXEC ortalayici 1,2,11,20,21,@sonuc OUTPUT SELECT ‘ORTALAMA:’,@sonuc go

Prosedürden değer döndürmek için bir başka seçenek olarak RETURN ifadesi kullanılabilir. Bu şekilde OUTPUT ifadelerini hem prosedür içinde hem

om

Bazı durumlarda, SP içerisinde SQL ifadesini bir değişkene atamak suretiyle durumlara göre dinamik olarak meydana getirip, daha sonra da onu çalıştırabiliriz. Bu durumda da EXEC komutu kullanılır. EXEC komutunun başka nerelerde kullanıldığını öğrenmek için yardım menüsünü kullanabilirsiniz.

rında kullanıcıyı uyarmak için kullanılırlar veya SQL Server’in ileri sürümlerinde kullanılmak üzere ayrılmışlardır. Kullanıcı, RETURN ile bunlar haricindeki (-1 ila –99 haricindeki) değerleri döndürebilir. Örnek 35:

ili. c

DİKKAT:

CREATE PROC toplayici @toplayan smallint, @toplanan smallint, @toplam smallint AS

SELECT @toplam = @toplayan + @toplanan RETURN @toplam go Prosedürü oluşturduktan sonra aşağıdaki şekilde dönen sonucu yakalayabiliriz:

ad

de prosedürün çağrıldığı yerde tanımlamak zorunda kalınmadan doğrudan değer döndürülebilir. Normal olarak SQL Server 0 ile -99 arasında değerleri bu şekilde döndürebilir. Bu değerler, sistem bazlı durumları belirlemek üzere ayrılmış kodlardır. Örneğin sıfır değeri prosedürün çalışmasında bir hata oluşmadığını belirtir. Bu haliyle de elbette kullanıcı tarafından da kullanılabilir. Diğerleri ise muhtelif hata durumla-

DECLARE @toplamlar smallint EXEC @toplamlar = toplayici 1,11,0 SELECT ‘Sonuç : ‘, @toplamlar

w.

jav

(Resim 20)

ww

Resim 19

Resim 20

Örnek 36: Bir kitap ödünç verilmek istendiğinde, bu kitabı öncelikle içeride mi diye kontrol eden bir sp yazınız. Girdi parametre: KitapNo Daha sonra ödünç verilen kitap no, tarih, üye No , kalma süresi verildiğinde bu kitap dışarıda değil ise yeni bir ödünç verme işlemi başlatan (odunc tablosuna kayıt ekleyen) bir sp yazınız. Sizce böyle bir sp hatalı bir kayıt girilme ihtimalini kökten halleder mi? (Tabloya hatalı kayıt girilmemesi için onu her yönü ile korur mu?) Öncelikle, Kitapların durumunu kontrol eden SP’yi oluşturalım: kitap Veritabanı Programlama 2

BYTE

53

IF (SELECT COUNT(*) FROM odunc WHERE kitapNO=@kitapNo AND geldiMi=0 ) > 0 SET @sonuc=0 else SET @sonuc=1

print ‘kitap dışarda ödünç verilemedi.....’ END Go

Test etmek için: EXEC sp_oduncVer @kitapNo=1,

@vermeTarihi=’01.01.2003’,@uyeNo=1

Örnek 39: SQL Server’in performansını izleyip performans istatistiklerini alalım:

ad

return @sonuc go Sonra da ödünç bilgisini kaydedecek sp’yi yazalım: CREATE PROC sp_oduncVer @kitapNo INT, @vermeTarihi DATETIME =’01.01.2003’, @uyeNo INT, @vermeSuresi INT = 15

print ‘içerde.....’ INSERT INTO odunc(kitapNo,UyeNo, VermeTarihi,vermeSuresi,geldiMi) VALUES(@kitapNo,@uyeNo,@vermeTarihi,@vermeSuresi,0) END ELSE BEGIN

ili. c

CREATE PROC sp_kitapIcerdeMi @kitapNo SMALLINT AS declare @sonuc INTEGER

om

içerde ise 1, değil ise 0 döndürüyor. (Resim 21)

Resim 21

jav

AS declare @sonuc SMALLINT EXEC @sonuc=sp_kitapIcerdeMi @kitap No=@kitapNo IF (@sonuc=1) BEGIN

SP’yi VTYS Uzayı Dışından MDACs ile Çağırmak eden kitapçıklarda özet olarak yer almakta-

lamasından, herhangi bir programlama dili

dır. Ancak bu süre içerisinde VTYS uzayının

ile yazılmış veritabanı uygulamasından para-

dışındaki uygulamalardan SP çağırmak için bu

metre ile çağrılması mümkündür ve aynen

örnekleri kullanabilirsiniz.

w.

Bir sp’nin ASP uygulamasından, ASP.NET uygu-

yukarıdaki şekillerde çağrılır. Bu durumlarda veritabanına erişmek için MDACs (ADO, OLEDB,ODBC,ADO.NET vs.) için kullanılır.

EXEC sp_adi [@parametre=değer,...n]

ww

Böylelikle, T-SQL’in avantajlarını VTYS

uzayının dışında bir uygulamadan da kullanabiliriz. Burada anlatılan kodları anlayabilmek için VB Script-ASP veya ASP.NET-VB.NET bilmeniz gerekecektir. Bu konular, takip

54

BYTE

Veritabanı Programlama 2

Örnek 37: İşte cezali uyelerimizi Web’de teşhir edecek bir ASP-VBScript kodu: Örneği daha iyi anlamak için daha önce örnekte oluşturduğumuz sp_cezaliUye SP’sinin tanımına bakınız.

<meta http-equiv=”Content-Type” content=”text/html; charset= windows-1254”>

om

ili. c

<% response.write “CEZALI ÜYELERİMİZ
” set objConn = Server.CreateObject(“ADODB.Connection”) objConn.Open “DRIVER={SQL Server};” _ ”server=BILISIMCI;UID=yasar;” _ ”PWD=yasar;Database=dbKutuphane” strGlobSQL=”EXEC sp_cezaliUye” set rsCezaliUye = objConn.Execute(strGlobSQL) DO UNTIL rsCezaliUye.EOF response.write rsCezaliUye(“adi”) & _ “ “ & rsCezaliUye(“soyadi”) & “
” rsCezaliUye.MoveNext LOOP %>

ad

Örnek-38: İşte aynı işlemi yapacak ASP.NET-VB.NET kodları:

ww

w.

jav

<%@ Page Language=”VB” %> <script runat=”server”> sub page_Load() dataGrid1.dataSource=exec_sp_proc() dataGrid1.dataBind() end sub ‘ Function exec_sp_proc() As System.Data.DataSet Dim connectionString As string = “server=BILISIMCI;user id=yasar;password=yasar;Database=dbKutuphane” Dim objConn As System.Data.SqlClient.SqlConnection = New em.Data.SqlClient.SqlConnection(connectionString) ‘Dim queryString As String = “SELECT [Kitap].[kitapNo], [Kitap].[kitapAd i], [Kitap].[ISBNNo], [Kitap].[sayfaSay”& _ “isi], [Kitap].[kitapOzeti] FROM [Kitap]” Dim strGlobSQL=”EXEC sp_cezaliuye” Dim sqlCommand As System.Data.SqlClient.SqlCommand = New System.Data.SqlCl ient.SqlCommand(strGlobSQL, objConn) Dim dataAdapter As System.Data.SqlClient.SqlDataAdapter = New System.Data. SqlClient.SqlDataAdapter(sqlCommand) Dim dataSet As System.Data.DataSet = New System.Data.DataSet dataAdapter.Fill(dataSet) Return dataSet End Function

Veritabanı Programlama 2

BYTE

55

om

ili. c

Resim 22

sp_monitor

ad

SP’ler hakkında son olarak şunu da bilmemiz gerekir, SP’ler sadece select işlemi için kullanılmaz. INSERT, UPDATE ve DELETE işlemlerinin tamamını SP’ler aracılığıyla yapmak, veritabanını uygulamadan izole etmek için iyi bir yöntemdir. (Resim 22)

Cursor’ler

tanımlanır. 2. Cursor veriler üzerinde gezinilmek üzere, OPEN komutu ile açılır. 3. Resultset’in sonuna gelinceye kadar her seferinde bir kayıt olmak üzere FETCH NEXT komutu ile kayıtlar üstünde ilerlenir. 4. Resultset ile ilgili işlemler sona erdiğinde cursor CLOSE ile kapatılır. Ancak kapatılan cursor henüz hafızada yer kaplamaya devam eder. 5. Cursor ile ilgili işlerimiz bittiği anda hafızadan da silmek için cursor DEALLOCATE ile hafızadan boşaltılır.

ww

w.

jav

VTYS’deki cursor’lar metin editörlerindeki cursorler ile aynı işi yapar; metin editörlerinde o an için cursor nerede ise, oradaki verileri baz alan işlemler yapabilirsiniz. Veritabanı sistemlerinde ise, cursor’ün bulunduğu yerdeki verileri yazdırabiliriz. SQL sorgularının sonucunda elde edilen kayıtların tamamına resultset denir. Bazen bir resultset üstünde gezinmemiz gerekebilir. Bu tür durumlarda, Cursor’leri kullanırız. Curserler özetle bir select ifadesinin döndürdüğü resultset’e satır satır erişmeye olanak verir. Cursor’leri kullanmak biraz karmaşık gibi gelebilir ama normalde 5 adımlık ardışık işlemler dizisinden ibarettir. 1. Cursor bir SELECT ifadesi için

Örnek 40: Kitaplar listesini ekrana yazdıracak(resultset olarak değil de teker teker) bir kod parçası yazalım:

56

BYTE

Veritabanı Programlama 2

1.Cursor tanımlanır: DECLARE cursor_adi CURSOR FOR SELECT İFADESİ

Şeklinde tanımlanır. DECLARE cr_kitapListesi CURSOR FOR SELECT kitapNo, kitapAdi FROM Kitap GO

2.Cursor açılır: OPEN cursor_adi OPEN cr_kitapListesi GO

3.Cursor üstünde dolaşmak için FETCH komutundan faydalanılır:

Başlıca Sistem Saklı Prosedürleri

FROM Kitap go DECLARE @kitapNo INTEGER,@kitapAdi VARCHAR(55) Go FETCH cr_KitaplarListesi INTO @kitapNo, @kitapAdi WHILE (@@FETCH_STATUS = 0) BEGIN PRINT @kitapNo

DDL’e yönelik Sistem sp’leri: sp_ addtype

ili. c

FETCH cr_KitapListesi INTO @kitapNo, @kitapAdi -- ilk satırın üstüne geldik ve ilk kayıtta yer alan iki değeri yazdırdık. print @kitapNo print @kitapAdi

om

FETCH NEXT FROM cursor_adi [INTO icine_doldurulacak_degisken1 [, ....] ]DECLARE @kitapNo INTEGER,@kitapAdi VARCHAR(55)

go

FETCH komutu ile ikinci kaydı yazdırmayı deneyin.

•bir önceki FETCH ile aldığımız sp_primarykey kaydı yazdırdık. Bir sonraki kayda sp_help geçelim:

sp_helptext

FETCH cr_kitaplarListesi INTO @kitapNo, @kitapAdi

sp_helpdb

•kayıtlarımızı doldurduk. Başa döndüğünde yazılacak. sp_helpconstraint

ad

Cursor’ün sona gelip gelmediğini anlamak için @@FETCH_STATUS ve @@rowcount global fonksiyonlarından faydalanacağız: @@FETCH_STATUS fonksiyonu, en son çalıştırılan FETCH komutunun sonucu hakkında bize bilgi verir. Bu fonksiyon, şu üç değerden birini verecektir: 0: Bir önceki FETCH komutu başarı ile gerçekleştirildi. -1: Bir önceki FETCH komutunda bir hata ile karşılaşıldı. -2: Resultsetteki tüm kayıtlar bittiği için en sona gelindi, daha fazla kayıt yer almıyor. (end of resultset) @@rowcount, bir önceki FETCH komutu icra edildikten sonra resultsette toplam kaç kayıt kaldığını tutar. Hiç FETCH komutu kullanılmadı ise Cursor’ün işaretlediği resultsette toplam kaç kayıt yer aldığını gösterir. Şimdi artık, kitaplar listesini sonuna kadar yazdırabiliriz. Bu iş için WHILE yapısını kullanacağız:

sp_foreignkey

PRINT @kitapAdi

sp_rename

END Go

sp_spaceused

ww

w.

jav

Kitaplar listesini yazdıran cursor ile bizim işimiz bitti ancak yapmamız geresp_depends ken işlemler bitmedi.

DECLARE cr_KitaplarListesi CURSOR FOR SELECT kitapNo,KitapAdi

sp_dropkey

4.Cursor’ı kapatmamız gerekiyor:

sp_rename

CLOSE cursor_adi

sp_dbremove

CLOSE cr_kitapListesi

sp_renamedb CLOSE komutu ile sadece bir cursor’ün içerdiği resultset boşaltılır. Ancak sp_helptrigger Cursor için hafızada açılan yer halen kullanımdadır ve Mesaj bir cursor Kullanıcı Tanımlı Sistemhafızadan sp’leri: tam olarak silinmediği sürece aynı adda sp_addmessage başka bir cursor daha oluşturulamaz. Cursor yeniden açılırsa, aynı resultsete ensp_dropmessage baştan erişilebilir. sp_getmessage

5. Cursor şu şekilde hafızadan silinir:

sp_altermessage Veritabanı Programlama 2

BYTE

57

om

ap.KitapNo DECLARE @kitapAdi VARCHAR(25), @Durumu BIT open cr_kitapDurumu

FETCH NEXT FROM cr_kitapDurumu INTO @kitapAdi, @Durumu

ili. c

WHILE @@FETCH_STATUS =0 BEGIN print @kitapAdi IF @Durumu = 0 print ‘dışarıda’ ELSE Print ‘içeride’ FETCH NEXT FROM cr_kitapDurumu INTO @kitapAdi, @Durumu END GO Close cr_kitapDurumu

Resim 23

Deallocate cr_kitapDurumu

Resim 24 DEALLOCATE cursor_adi

(Resim 23)

ww

w.

Örnek 41: Projelerin çeşitli aşamalarında, özellikle de dışarıdan veri alınırken, bazı kayıtların bire bir tekrarladığı olabilir. Bu durumda kayıtlardan sadece birini bırakıp diğerlerini silecek bir Cursor oluşturmak gerekir. Varsayalım ki bizim de kitap tablomuz üstünde hiçbir indeks alan yoktu ve iki- üç kere aynı bilgiler tekrarladı. (Hatta bazı kitaplar 2, diğerleri 3 diğerleri n kere ...)Bunları silecek bir Cursor oluşturalım: DECLARE cr_kitapDurumu CURSOR FOR SELECT kitap.kitapAdi,odunc.GeldiMi FROM Kitap LEFT JOIN odunc ON odunc.KitapNo=Kit-

58

BYTE

Örnek 42: Kitaplar tablosunda aynı kitap numarasından iki tane olan varsa sildirecek bir cursor yazalım:

jav

DEALLOCATE cr_kitapListesi

ad

(Resim 24)

Veritabanı Programlama 2

SET ROWCOUNT 0 DECLARE @kitapNo INTEGER, @tekrarSayisi INTEGER, @sayac BIT DECLARE crKitaplar CURSOR FOR SELECT kitapNo, COUNT(kitapNo)-1 FROM Kitap GROUP BY kitapNo HAVING COUNT(kitapNo)>1 ORDER BY kitapNo --kitapNo bir kereden fazla geçenleri sileceğimize göre --doğru kitap numaralarını seçelim: OPEN crKitaplar FETCH NEXT FROM crKitaplar INTO @KitapNo, @tekrarSayisi WHILE @@FETCH_STATUS=0 BEGIN BEGIN SET ROWCOUNT @tekrarSayisi --tekrar sayısının bir eksiği kadar kayıt silinecek DELETE FROM Kitap WHERE kitapNo = @kitapNo --sonra tekrardan tüm kayıtlar --dikkate alınacak şekilde ROWCOUNT=0 atanıyor SET ROWCOUNT 0 END

om

FETCH NEXT FROM crKitaplar INTO @KitapNo,@tekrarSayisi --print CAST(@kitapNO VARCHAR(2)) + ‘ nolu kitap silindi’ END close crKitaplar deallocate crKitaplar

ili. c

(Resim 25) Örnek 43: Sayfa sayısına göre ilk beş kitabı görmek için: SELECT TOP 5 * FROM kitap

Resim 25

İPUCU:

jav

ad

ORDER BY sayfaSayisi DESC

ww

w.

Herhangi bir kritere göre sıralandıktan sonra ilk n kaydı getirip ötesiyle ilgilenmeyeceksek SELECT TOP N deyimini kullanırız.

Veritabanı Programlama 2

BYTE

59

ww ili. c

ad

jav

w.

om

4. Bölüm

om

İşlemler (Transactions)

ili. c

İşlemler (Transactions) Tetiklemeler (Triggers)

gerçekleşir ve geçerli kabul edilir veya bir kısım işlemler yolunda gitse bile, blok sona ermeden bir işlem bile yolunda gitmese hiçbir işlem olmamış kabul edilir. Transaction tanımına girebilen işlemler UPDATE, INSERT ve DELETE işlemleridir. Bir Transaction bloğunu işletmenin temel mantığı şu şekildedir: 1.Transaction bloğu başlatılır. Bu işlemden sonra gerçekleşen işlem logları işaretlenir. Bu arada işlem kullandığı kaynakları diğer kullanıcılara karşı kilitleyerek korur. 2.Transaction bloğu arasında yapılan her bir işlem bittiği anda başarılı olup olmadığına bakılır, başarılı olmadığı anda geri alım işlemine geçilir (ROLLBACK). Başarılı ise, bir sonraki işleme geçilir. 3.Tüm işlemler tamamlandığı anda COMMIT ile bilgiler yeni hali ile sabitlenir. Başarısız bir sonuç ise ROLLBACK ile en başa alınır ve bilgiler ilk hali ile sabitlenir. Bu örnek için aşağıdaki tabloyu kullanacağız:

ww

w.

jav

ad

Bir banka uygulamasını düşünün. Bir kullanıcı başka bir kullanıcıya havale yaptığında ne olur? Öncelikle havale yapanın hesap bilgilerinden havale yaptığı miktar düşülür. Ardından alıcının hesabına bu miktar eklenir ve havale gerçekleşmiş olur. Ancak her zaman şartlar istendiği gibi olmayabilir. Örneğin, gönderenin hesabından para düşüldüğü anda elektrik kesilebilir ya da program takılabilir. Bu durumda, ne olur? Gönderenin hesabından para düşülmüştür ama Alıcının hesabına da geçmemiştir yani bir kısım paranın sahibinin kimliği kaybedilmiş olur. Ya da siz tam da bir hesaba havale yapacakken, düşük bir ihtimal ama arkadaşınız da aynı hesabı kapattırdı ise?... Bu da sistemin olası durumlar dışında veri kaybetmeye müsait bir hal alması demektir, önlenmesi gerekir. İşte Transaction bu nedenle, verileri korumak için kilitler (‘LOCK’ koyar) Daha küçük parçalara ayrılamayan en küçük işlem yığınına transaction denir. Geçerli kabul edilmesi bir dize işlemlerin tamamının yolunda gitmesine bağlı durumlarda transaction kullanılır. Transaction bloğu ya hep ya hiç mantığı ile çalışır. Ya tüm işlemler düzgün olarak

Hesap tablosu Alanlar: HesapNo CHAR(20) NOT NULL PRIMARY KEY Adi VARCHAR(55)

Veritabanı Programlama 2

BYTE

61

om

Soyadi VARCHAR(55) Sube INTEGER Bakiye FLOAT

COMMIT

BEGIN TRAN[SACTION] [transaction_adi]

ad

İle bir transaction başlatılır.

Sabitleme noktaları: Bazen, bir noktaya kadar gelindikten sonra, işlemlerin buraya kadar olanını geçerli kabul etmek isteriz ama, bundan sonraki işlemler için de transaction (geri alabilme seçeneği)’ne ihtiyaç duyarız. Bu türden durumlarda sabitleme noktalarından faydalanılır. Bir sabitleme noktası başlatıldığı anda, en başa dönme seçeneği saklı kalmak üzere, noktanın oluşturulduğu yere de dönme seçeneği sunar Genel yapısı şu şekildedir: SAVE TRANSACTION sabitleme_ notkasi_adi

ili. c

Genel Yapısı şu şekildedir: 1.Transaction başlatılır:

Örnek 44:

Örnek 45:

SELECT * FROM hesap --İlk baştaki hesap durumlarına dikkat: BEGIN TRANSACTION UPDATE Hesap SET bakiye = 5000000 WHERE hesapNo=’1’ -- transaction açıldıktan sonra bir UPDATE geldi. --Yeni Durum: SELECT * FROM hesap SAVE TRANSACTION svp_kaydet DELETE FROM Hesap WHERE HesapNo=’1’; --Kaydetme noktasından sonra bir nolu hesap silindi --yeni durum: SELECT * FROM hesap ROLLBACK TRAN svp_kaydet; --kaydetme noktasına dönüldü(Silinene hesap geri geldi) SELECT * FROM Hesap; ROLLBACK TRAN ; --İşte son durum(havale geri alındı, her şey ilk haline geldi.) SELECT * FROM Hesap;

jav

DECLARE @havaleMiktar FLOAT DECLARE @aliciHesap VARCHAR(20), @gonderenHesap VARCHAR(20) SET @aliciHesap=’1’ SET @gonderenHesap=’2’ SET @havaleMiktar=20000000 -- 20 milyon havale edilecek BEGIN TRANSACTION UPDATE tblHesap SET bakiye=bakiye - 20000000 WHERE hesapNo=@gonderenHesap

w.

UPDATE tblHesap SET bakiye=bakiye - 20000000 WHERE hesapNo=@aliciHesap

ww

2.İşlem başarılı olursa, COMMIT ile transaction bitirilir. Başarısız olduğunun anlaşılması haline ROLLBACK komutu ile transaction başarısız olarak bitirilebilir.(Yani en baştaki duruma geri dönülür)

62

BYTE

Veritabanı Programlama 2

Örnek 46:

Genel kullanımı:

CREATE TRIGGER trigger_adi ON tablo_adi FOR INSERT [,UPDATE,DELETE] AS SQL ifadesi

Şeklindedir. Trigger’lerin bir UPDATE, INSERT veya DELETE komutuna cevap olarak çalışması, Transaction mantığı ile aynıdır: 1. Bir tablo için TRIGGER tanımlı ise INSERT, DELETE veya UPDATE işlemi başlamadan hemen önce bir Transaction başlatılır. 2. INSERT, DELETE veya UPDATE komutu yerine getirilir. 3. Trigger çağrılır ve içindeki SQL ifadesi çalıştırılır. 4. Trigger işlemi onaylarsa geçerli kılar veya onaylamaz ve geçersiz bulur. Transaction geri alınır. (ROLLBACK TRANSACTION). Bir Trigger oluştururken şu durumlara dikkat etmek gerekir: 1. View ya da geçici tablolar üzerinde oluşturulamazlar. Fakat bunlara referans

ili. c

SELECT kitap.* FROM kitap WITH(NOLOCK) WHERE kitapNo>5

om

Kitap tablosu üstünde kilitlemeden seçme işlemi yapmak için:

Trigger’lar

ww

w.

jav

ad

Triggers (Tetikler), aslında özel bir Stored Procedure’dür. SP’lerden farkı, bir tablo üstünde UDATE , INSERT, DELETE komutları ile bir işlem yapılmak istendiğinde, ilk olarak bu SP’lere bakılması ve gerekli ise çalıştırılmasıdır. Nesneye dayalı Programlamada var olan, event based programlama bu mantık ile örtüşür. Trigger’lar izin verilmeyen ya da tutarsızlığa neden olacak işlemleri engelleyerek veri bütünlüğünün korunmasına yardımcı olurlar. Daha önceki veritabanı sistemlerinde triggerlar tablolar arası birbirlerine referans yapan verilerin bütünlüğünü sağlamak maksadıyla kullanılırlardı (yabancı anahtar zorlayıcısını hatırlayınız). Ancak SQL Server 6.5’ dan itibaren bu işlem zaten Referantial Integrity tanımlamalarıyla sağlandığı için bu maksatla kullanılmaları gereksizdir. Triggerlar genellikle değişik tablolar üzerinde bulunan ve birbirleri arasında mantıksal ilişkilere sahip verilerin tutarlılığını sağlamak üzere oluşturulurlar. İhtiyaca göre uygulamanın getirdiği bazı kuralları kontrol etmek için de kullanılabilirler.

UYARI: Recursive olarak çalışmazlar. Yani bir tablonun herhangi bir sütununda yapılan değişiklik üzerine bir trigger çalışıp aynı tablonun başka bir sütununda değişikliğe neden oluyorsa ikinci yapılan değişiklik trigger için tetiklenmeye neden olmaz. Kısaca update trigger’ı tekrar tekrar çalışmaz, yalnızca bir kez çalışır.

Veritabanı Programlama 2

BYTE

63

om

Ardından odunc için

DİKKAT:

DELETE FROM ODUNC WHERE oduncNo=8

Gibi bir cümlecik çalıştırdığımızda,

ili. c

Genellikle bir tablo için en fazla UPDATE,INSERT ve DELETE trigger’i olmak üzere 3 Trigger kullanılır. Yapılacak bir çok iş varsa, hangi olay ile birlikte tetiklenecekse o trigger içerisinde yerleştirilir.

Mesajı gelir.

Eklenen-Silinen Kayıtlar: Triggerlar çalıştığı zaman Inserted ve Deleted tablolarını kullanırlar. Bu tabloların her ikisi de ana tabloyla yani triggerın tetiklendiği tabloyla eşdeğer alanlara sahiptirler. Bu tablolar, mantıksal tablo şeklinde RAM’ de bulunurlar. Ana tabloya bir kayıt eklendiği zaman bu kayıt aynı zamanda inserted tablosuna da eklenir. İhtiyacımız olduğu zaman yeni eklenen değerlere bu tablodan ulaşarak, bu bilgileri tutmak maksadıyla değişken tanımlamak zorunda kalmaktan kurtuluruz. Tablodan bir kayıt silindiğinde silinen kayıt deleted tablosunda saklanır. Update işlemi ise delete ve hemen ardından yapılmış bir insert işlemi olarak ele alınır. Bir kayıt update edildiğinde orijinal kayıt deleted tablosuna işlenir, değişen kayıt da inserted tablosunda (ve ana tabloda) saklanır. Trigger’lere dışarıdan parametre yollayamayız. Ancak, INSERTED ve DELETED tabloları sayesinde kısıtlı da olsa, son işlemden etkilenmekte olan kayıtları tespit edebiliriz.

w.

jav

ad

içerebilirler. 2. Çalıştıktan sonra bir kullanıcıya değer kümesi döndüremezler. Dolayısıyla SELECT ifadesi dahil edileceği zaman dikkatli olunmalıdır. 3. Veri bütünlüğünü, farklı tablolardan birbirine referans yapan verilerin tutarlılığını sağlamak için ve uygulamanın ihtiyacına göre bazı kuralları tanımlamak üzere kullanılabilirler. Ancak zorlayıcılarla düzenlenebilecek trigger kullanmamak daha faydalıdır. 4. İstenirse syscomments tablosunda şifrelenmiş tekilde saklanabilirler. 5. CREATE, DROP, ALTER TABLE, ALTER DATABASE, SELECT INTO gibi ifadeler trigger içinde kullanılmazlar. Örnek 47: Ödünç tablosundaki kayıtlar üstünde değişiklik yapıldığında kaç kaydı etkilediğini yazan bir trigger oluşturalım:

ww

CREATE TRIGGER tr_AffectedRows ON odunc

FOR INSERT, UPDATE,DELETE AS raiserror(‘%d kayıt üzerinde değişiklik yapılmıştır’, 0, 1, @@rowcount) RETURN

64

BYTE

Veritabanı Programlama 2

CREATE TRIGGER tr_oduncKontrol ON odunc FOR INSERT,UPDATE AS IF(SELECT COUNT(odunc.kitapNo) FROM odunc,inserted WHERE odunc.geldiMi=0 AND inserted.kitapNo=odunc.KitapNo)>1 BEGIN Print ‘dışarıdaki kitabı bir başkasına veremezsiniz..’ ROLLBACK TRANSACTION

om

Örnek 50: Ödünç tablosu üstünde hangi trigger’lerin bulunduğunu öğrenmek için gerekli TSQL komutunu yazalım: sp_helptrigger odunc

ad

END

IF(SELECT COUNT(odunc.kitapNo) FROM odunc,inserted WHERE odunc.geldiMi=0 AND inserted.kitapNo=odunc.KitapNo)>20 BEGIN Print ‘Bu kitabı 20’’den fazla defa ödünç vermişsiniz. Kütüphaneden çıkaramazsınız.’ ROLLBACK TRANSACTION END

ili. c

Örnek 48: Kitaplardan bir kısmı, ödünç verildiği ve gelmediği halde bir başkasına daha ödünç verilmiş görünebiliyor. Böyle bir hata yapılmasını kökten çözmek için bir Trigger oluşturalım:

Sonuçta Trigger ve Stored Procedure’leri karşılaştırırsak, her ikisi de önceden derlenmiş SQL ifadeleri olduğundan hemen hemen aynı hızda ve aynı külfet miktarına sahiptirler. SQL Server’ iın işlem sırası, önce View ve Stored Procedure’ leriı sonra Trigger’ları çalıştıracak şekildedir. Daha iyi performans elde etmek için mümkün olduğu kadar trigger seviyesine inilmemeye gayret edilmelidir. Eğer problemi,Zorlayıcılar seviyesainde yakalayabiliyorsak bu seviyede, stored procedure içinde yakalayabiliyorsak sp seviyesinde o da olmuyorsa, trigger seviyesinde yakalamak gerekir. Sp ile yakalanan bir durum olduğunda, trigger yüzünden, yapılan her şeyin geri alınması gerekmez. Ancak SPsp’lerin otomatik devreye girmediğini unutmamak gerekir.

jav

Bu örnekte verilen durumun içerisinden foreign key zorlayıcısı ile çıkılamayacağına dikkat edin. Şimdi odunc üstünde şöyle bir cümlecik çalıştıralım (1 nolu kitap ödünç verilmiş ve gelmemiş ise): INSERT INTO odunc(kitapNo,uyeNo, vermeTarihi,VermeSuresi,GeldiMi) VALUE S(1,1,’01.01.2003’,15,0) ->> dışarıdaki kitabı bir başkasına veremezsiniz..

w.

mesajı gelecektir. Trigger’ler, uygulamaya özgü kuralları veritabanı sisteminin bir parçası olarak tayin etmek istenildiğinde de kullanılır:

ww

Örnek 49: 20’den fazla kez ödünç alınan kitapların silinmesini önleyecek bir trigger yazalım: CREATE TRIGGER tr_oduncKontrol_20 ON odunc FOR DELETE AS

Veritabanı Programlama 2

BYTE

65