Saklı Yordamlar

Saklı Yordamlar içerisindeki kodlar düzenlendiği için SQL komutlarından daha hızlı yürütülür. Şimdi nasıl çalıştığına hep beraber bakalım.

Saklı Yordamlar (Stored Procedure)

T-SQL (sql server da yazdığımız komutların tamamına verilen addır) programlama dili veri tabanı ile program arasındaki ilk basamaktır. Hazırladığımız programda veritabanına işlem yaptırmanın iki yöntemi vardır.

1. İşlem için gerekli komutlar programdan gönderilir

2. İşlemler saklı yordamlarda saklanır. Programdan saklı yordam çağrılır

Saklı Yordamların üstünlükleri

• Yordam bir kez oluşturulduktan sonra defalarca çağrılabilir. Yaptığınız programın kodlarıyla oynamadan sql server da saklı yordam üzerinde değişiklik yapabilirsiniz. Bu işleme modülerlik adı verilir.
• Eğer yapılacak işlemler fazla T-Sql kodu içeriyorsa ve/veya bu işlemler tekrar tekrar yapılacaksa saklı yordam yığın işlemlerinden (go ile ayrılan işlem blokları) çok daha hızlı çalışır. Saklı yordam ilk kez çalıştırıldığında yazım denetimi yapılır ve hız için en iyi seviyeye getirilir. Saklı yordam derlendikten sonra sonraki kullanımlar için hafızada saklanır. Yani her kullanım için yazım denetimi ve hız için en iyi seviyeye getirme ayarı yapılmaz.
• Ağ trafiğini azaltır.
  Bir döngü içerisinde yüzlerce satırın herbirinde baştan sona işlem/işlemler yapılacaksa ağın trafik yoğunluğu artar. Bu durumda döngü işlemleri saklı yordam içerisinde yapılarak ağ trafiği azaltılır.
• Güvenlik
  Saklı Yordamı çalıştırabilmek için kullanıcının izne ihtiyacı vardır. İzne sahip değilse doğrudan saklı yordamı çalıştıramazlar.

Parametre

Saklı yordam içerisindeki kodlar başka programlardan gönderilen parametrelere göre çalıştırılabilir.

Saklı Yordamlar fonksiyonlar gibi değer döndürebilir, bu döndürülecek değer işlemi parametreye OUTPUT yazısı eklenerek belirtilir.

Saklı yordamlar 2100 tane parametreye sahip olabilir

Parametre; parametre adı, veri türü, yönü (output veya input), ve varsayılan değer özelliklerine sahiptir.

Parametresiz bir örnek

Create proc ilkonspor
as
set nocount on
set rowcount 11
select baslik,id from haber order by id desc
set nocount off
set rowcount 0

1. satırda ilkonspor yordamı oluşturuluyor. Yordam oluşturulurken create procedure veya create proc kullanılır.
2. satırda as ile yordam başlangıcı belirtiliyor
3. satırda nocount özelliğine atama yapılıyor
4. satırda rowcount özelliğine atama yapılıyor
5. satırda t-sql cümleciği yazılıyor
6. ve 7. satırlarda atama yapılan özellikler eski hallerine döndürülüyor

Parametreli bir örnek

create procedure ilkhaber
@hangisi int
as
select * from haber where id=@hangisi

exec ilkhaber 300 --Bir yordamı çalıştırmak için exec veya execute komutu kullanılır
yordam çalıştırıldığında 300 nolu haber ekrana getirilir

saklı yordamlar; view ler ve fonksiyonlar gibi sql cümleciklerine tablo adı olarak yazılamaz. select * from ilkonspor şeklinde yazım olmaz

Yordam parametresine varsayılan değer verme

Yordam çalıştırılırken yordamdaki parametre sayısı kadar değer gönderilmelidir. Aksi takdirde yordamda ki parametreye değer gönderilmediği için yordam çalışmaz. Bazı parametrelere değer gönderilmediği zaman dahi yordamın çalışması istenebilir.
Aşağıdaki örnekte ilkhaber yordamına herhangi bir parametre gönderilmediği zaman 116 sayısı parametreye değer olacaktır

create procedure ilkhaber
@hangisi int=116
as
select * from haber where id=@hangisi

exec ilkhaber 300
yordam çalıştırıldığında 300 nolu haber ekrana getirilir
---------------------------------------------
exec ilkhaber
yordam çalıştırıldığında parametre değeri gönderilmediği için 116 nolu haber ekrana getirilir

Yordama Joker Parametre Geçmek

%, _, [] ve [^] karakterleri joker karakterlerdir. Eğer yordama geçilecek parametre like sözcüğünde kullanılacaksa gönderilen parametre değeri joker karakter içerebilir. Ayrıca Parametreye varsayılan değer verilirken joker karakterler kullanılabilir.

create proc yazarlar
@yazadi nvarchar(30)='A%'
as
select * from yazar where adi like @yazadi

exec yazarlar
parametre belirtilmediği için varsayılan değer ataması yapılır. İsmi A ile başlayan yazarlar listelenir.

exec yazarlar 'M%'
parametrede joker karakter kullanılmıştır. Parametre kullanıldığı için varsayılan değer ataması (='A%') yapılmayacaktır. İsmi M ile başlayan yazarlar listelenecektir.

Drop Procedure
var olan yordamı silmek için kullanılır.
drop procedure ilkonspor

Alter Procedure
var olan yordam üzerinde değişiklik yapmak için kullanılır

alter proc ilkonspor
as
set nocount on
set rowcount 8
select baslik, kisaozet, id from haber order by id desc
set nocount off
set rowcount 0

Uyarılar

• Bir saklı saklı yordamın boyutu 128 MB ı geçmemelidir.
• Geçici saklı yordamlar hariç saklı yordamlar sadece üzerinde çalışılan veritabanı üzerinde oluşturulur. Geçici saklı yordamlar tempdb veritabanının içine oluşturulurlar.
• Yordam parametreleri null değeri kabul ederler. Parametreye karşılık gelecek bir değer yoksa parametre null ifade içerir. Bu parametre kullanılarak saklı yordam içerisinde tablo için alan oluşturulacaksa sql server hata döndürür. Bir tablonun alan adı null olamaz. Bu durumdan kurtulmak için oluşturulan parametrelere varsayılan değer verilmelidir.
  örnek
  create procedure ilkhaber
  @hangisi int=116
  as
  select * from haber where id=@hangisi
• exec ilkhaber 300
  yordam çalıştırıldığında 300 nolu haber ekrana getirilir
  ---------------------------------------------
  exec ilkhaber
  yordam çalıştırıldığında parametre değeri gönderilmediği için 116 nolu haber ekrana getirilir
• -----------------------------------------------
  declare @num int
  exec ilkhaber @num
  bu iki satır işaretlenip beraber çalıştırılırsa ilkhaber yordamına @num değişkeni içeriği yollanır. @num değişkeni tanımlanmış fakat değer atanmamıştır, null dur. ilkhaber yordamına @num değişkeni gönderildiği için yordamdaki parametreye varsayılan değer yani 116 değeri atanmaz, @num değişkeninin içeriği atanır. Sql server parametrelerdeki varsayılan değerlerin null olup olmadığına: yordam çalıştırılırken yordamdaki parametrelere karşılık gelen değer ya da değişken var mı? buna bakar varsa varsayılan değeri atamaz. Peki değişken gönderiliyor fakat değişken null değer ile geliyor. Bu durumdada sql server varsayılan değeri atamaz. Çünkü parametreye karşılık geliyor. Bu sorunun çözümü için yordam içerisinde gelen değerin null olup olmadığı sınanır.
  ------------------------------------------------------
  Örnek
  create procedure ilkhaber
  @hangisi int=116
  as
  if @hangisi is null
  set @hangisi=200
  select * from haber where id=@hangisi
  ------------------------------------------------
  Yordam çalıştırıldığında parametre gelmiyor ise 116 nolu haber
  parametre geliyor, fakat gelen parametre null ise 200 nolu haber ekrana getirilir,
  parametre geliyor ve gelen değer null değil ise gelen değerdeki haber ekrana getirilir.
• 
  CREATE RULE (artık kullanılmıyor yerine check kısıtlaması geldi) , CREATE PROCEDURE, CREATE DEFAULT (varsayılan değer kısıtlamasının kod ile yapılması halinde kullanılan komut parçacığı) , CREATE TRIGGER (ileride trigger ları göreceğiz )ve CREATE VIEW komutları saklı yordam içerisinde kullanılamaz.

Saklı Yordam Çeşitleri

1.Kullanıcı Tanımlı Saklı Yordamlar
T-SQL: Yukarı anlattığımız saklı yordam türüdür
CLR: Net mimarisinin kalbi olarak nitelendirilir. Net Framework mimarisindeki bir sınıfa ait yerel ya da genel sınıf özelliklerini içerir. Sql server da olmayan diziler, kolleksiyon sınıfları clr sayesinde kullanılır.
jljkl

2.Genişletilmiş Saklı Yordamlar (Extended Stored Procedures )
Herhangi bir programlama dilinde (Visual Basic, C gibi) oluşturulan harici yordamlardır. Önünde xp_ öneki bulunur. Genişletilmiş yordamlar DLL dosyalarıdır, bu dosyalar dinamic olarak Sql Server'a yüklenir. Bu yordamlar SQL Server Extenden Stored Procedure API kullanılarak programlanırlar ve doğrudan SQL Server in bulunduğu adres boşluğunda yer alırlar (Ram üzerindeki).
CLR bu yordamdan daha güvenilirdir. Genişletilmiş saklı yordamlar SQL Server in önümüzdeki zamanda çıkacak sürümlerinde bulunmayacaktır. Dolayısıyla bu yordamın kullanılmasından sakınılmalıdır. Bunun yerine CLR kullanılmalıdır.

3.Sistem Saklı Yordamları
SQL server da ki yönetim kademesindeki bir çok işlem özel bir yordam olan sistem saklı yordamları sayesinde yapılır. Sistem saklı yordamlarının önünde sp_ öneki bulunur. Örneğin sys.sp_changedbowner bir sistem saklı yordamıdır. Sistem saklı yordamları Resource veritabanında saklanırlar. İzin işlemleri olan Grant, Deny ve Revoke komutları sistem saklı yordamlarına uygulanabilirler. Sistem saklı yordamları listesi için tıklayınız.

Resource veritabanı

Resource veritabanı salt-okunurdur. SQL serverdaki tüm sistem nesnelerini içerir. Sistem nesneleri (sys.objects gibi ) fiziksel olarak Resource veritabanında saklanırlar. Fakat bunlar her veritabanının sys şemasında görünürler. Resource veritabanları kullanıcı verileri veya kullanıcı metadata ları saklamazlar.

Resource veritabanı SQL Server in yeni veriyonlarıyla güncellenir. 2005 ten önceki versiyonlarda Resource veritabanını güncellemek için, önce sistem objeleri silinir sonra oluşturulurdu. Şimdi ise Resource veritabanını kopyalayıp yapıştırmak yeterlidir.

Resource veritabanı sabit diskte

SQL Server Resource veritabanını yedekleyemez. Yedekleme işlemi ilgili klasör açılıp mssqlsystemresource adındaki mdf ve ldf dosyaları kopyalanıp başka bir klasöre yapıştırılmasıyla gerçekleştirilir. Yedek Yükleme işlemide aynı şekilde yapılır.

Resource veritabanı düzenlemeleri sadece Microsoft firması yetkilileri tarafından yapılır. Kullanıcının yapacağı iş sadece Resource veritabanı ile master veritabanını aynı klasörde bulundurmaktır. Resource veritabanının ID numarası her zaman 32767 dir. Resource veritabanı ile ilgili önemli diğer özellikler ise: Sürüm numarası ve en son güncelleme zamanıdır.
Sürüm numarası
SELECT SERVERPROPERTY('ResourceVersion')
ile öğrenilir.
En son güncelleme zamanı ise
SELECT SERVERPROPERTY('ResourceLastUpdateDateTime')
komutu ile öğrenilir.

sistem nesnesi ile ilgili sql komutunu görmek için
SELECT OBJECT_DEFINITION(OBJECT_ID('sys.objects'))
komutu kullanılır.