3

Stored Procedure Sql Server

Herkese merhaba,

Bugünkü yazımda sizlere veritabanı’nda olması gereken en önemli özelliği yani stored procedure özelliğini anlatacağım. Store procedure dilimizde saklı alt yordam veya saklı işlem grubu olarakta ifade edilmektedir. Stored procedure’lerin en önemli özellikleri veritabanı içinde saklanmalarıdır. Derlenmesi için başlangıçta çalışır ve daha sonraki kullanımlarda derlenmez. Bu da bize artı zaman kazandırır ve etkili bir performans sunar. Stored procedure’ler C,C#, Java ya da başka programlama dillerindeki fonksiyonlar gibi parametreler içermektedir. Bir özelliği daha ise; oluşturulacak bir prosedürün içinde declare, set, if ,try catch gibi deyimlerin de kullanılmasıdır. Eğer bir veritabanı ile uğraşıyorsanız kesinlikle işlerinizi stored procedure ile halletmenizi öneriyorum.

Stored procedurelerin kullanıldığı başlıca alanlar:

  • Tablodan veri çekme

  • Tablodan veri silme

  • Tabloya veri ekleme

  • Tablodaki veriyi güncelleme

Örnekler üzerinde Stored Procedure mantığı ve işlevi daha iyi oturacaktır.

İlk olarak alt yordamın söz dizim (syntax) şekli 2 türlü yazılabilir

CREATE PROCEDURE [Procedure İsmi]
(
 -- girilecek parametre değerleri buraya yazılıcak
)
WITH  { RECOMPILE | ENCRYPTION }
AS
BEGIN
 -- BEGIN END Kullanılması zorunlu degildir.
END
CREATE PROC [Procedure İsmi]
(
--girilecek parametre değerleri buraya yazılıcak
)
AS

With ile beraberinde yazılan ifadeler { RECOMPILE | ENCRYPTION }

  1. RECOMPILE ifadesi Stored procedure her çalıştırmada (execute) yeniden derlenecek anlamına gelir.

  2. ENCRYPTION ifadesi yazılan kodun şifrelenmesidir. Şifrelenmiş kodu sadece o prosedürü yaratan ve system admin olan görebilir.

Alt yordamın söz diziminide öğrendikten sonra sık kullanılan veri ekleme prosedürünü yazalım :

Örneğin: Şirkete yeni bir çalışan alındığında, bu çalışanı eklemek istiyoruz.

CREATE PROC SP_CalisanEkle
(
@CalisanID nchar(5),  (1)
@CalisanIsim nvarchar(40),
@CalisanSoyisim nvarchar(40),
@CalisanTelefon nvarchar(10),
@CalisanAdres nvarchar(100),
@CalisanNotlari nvarchar(100),
@CalisanDugumTarihi DATETIME
)
WITH ENCRYPTION
AS
IF EXISTS(SELECT * FROM dbo.CalisanlarinTablosu WHERE  CalisanID=@CalisanID) (2)
BEGIN (3)
PRINT 'Sistemde id numarası mevcuttur!'
END
ELSE
BEGIN
INSERT INTO dbo.CalisanlarinTablosu VALUES (@CalisanID,@CalisanIsim,
@CalisanSoyisim,@CalisanTelefon,@CalisanAdres,@CalisanNotlari,
@CalisanDogumTarihi)
END
  1. Parametleri oluştururken başına @ İşareti koyulur ve bitişik olarak hangi işleve yarıyacaksa onun hatırlatacak isim girilir daha sonra veri tipi yazılarak bir sonraki parametreye geçilir.

  2. if exist() komutunu ve begin end işlevlerini görebilmek için oluşturulmuştur. IF EXIST(), exists() bölümünde oluşturulan şart içindeki değer sağlanırsa true dönmektedir ve ilk begin end bloğuna girip sistemde aynı çalışan numarasına ait başkasının olduğunu söyler. Eğer sistemde aynı id numarasına ait bir çalışan bulunmadıysa, yeni bir çalışan else bloğuna girerek ekleniyor. Eğer tek bir satırdan oluşuyorsa sorgunuz Begin End bloğunu yazmak zorunda değilsiniz.
    3.BEGIN END bloğu programlama dillerindeki süslü parantezlerdir (scope) { }.

Stored Procedureleri çalıştırmak için oluştutulan method adının önüne EXEC ya da EXECUTE komutları yazılabilir:

EXEC SP_CalisanEkle 'OSM99','Osman','Oztürk','05541231212','134 sokak daire:5 Mecidiyekoy / Istanbul','Isinde basarılıdır','1980-02-02 '

EXECUTE SP_CalisanEkle 'OSM99','Osman','Oztürk','05541231212','134 sokak daire:5 Mecidiyekoy / Istanbul','Isinde basarılıdır','1980-02-02'

Alt yordamı çalıştırmak için bir seçeneğiniz daha var; o da exec ya da execute komutları yazmadan sadece prosedürünüzün adını yazıp gereken parametreleri doldurmaktır.

SP_CalisanEkle 'OSM99','Osman','Oztürk','05541231212','134 sokak daire:5 Mecidiyekoy / Istanbul','Isinde basarılıdır','1980-02-02'

Stored Procedure’lerde önceden hazırlanmış olan alt yordamın üzerinde değişiklikler de yapılabilir. Oluşturduğumuz SP_CalisanEkle prosedürüne ek olarak çalışanların hangi programlama dillerini bildiklerini de ekleyelim, ayrıca eğer yeni çalışanın yaşı 30 yaşından büyükse, firmadaki projelerin denetimine katılacakların listesine de (tabloya) eklensin. Proje denetmen tablomuz 4 özellikli bir tablodur. Sonuç olarak burada istenen ek programlama dili bilgisi, bizim esas tablomuzda da bu değişikliğin yapılmış olduğunu gösterir ve buna göre tekrar alt yordamın düzeltilmesi istenir. Değişiklikleri kaydetmek için ALTER komutunu kullanıyoruz.

ALTER PROC SP_CalisanEkle
(
@CalisanID nchar(5),
@CalisanIsim nvarchar(40),
@CalisanSoyisim nvarchar(40),
@CalisanTelefon nvarchar(10),
@CalisanAdres nvarchar(100),
@CalisanNotlari nvarchar(100),
@CalisanDugumTarihi DATETIME,
@CalisanProgDilleri nvarchar(100)
)
WITH ENCRYPTION
AS
IF EXISTS(SELECT * FROM dbo.CalisanlarinTablosu WHERE  CalisanID=@CalisanID)
BEGIN
PRINT 'Sistemde id numarası mevcuttur!'
END
ELSE
BEGIN
IF (30 <= DATEPART(yyyy,GETDATE())-DATEPART(yyyy,@CalisanDogumTarihi))
BEGIN
INSERT INTO dbo.ProDenetmenTablosu VALUES
(@CalisanID,@CalisanIsim,@CalisanSoyisim,@CalisanTelefon)
INSERT INTO dbo.CalisanlarinTablosu VALUES (@CalisanID,@CalisanIsim,
@CalisanSoyisim,@CalisanTelefon,@CalisanAdres,@CalisanNotlari,
@CalisanDogumTarihi,@CalisanProgDilleri)
END
ELSE
BEGIN
INSERT INTO dbo.CalisanlarinTablosu VALUES (@CalisanID,@CalisanIsim,
@CalisanSoyisim,@CalisanTelefon,@CalisanAdres,@CalisanNotlari,
@CalisanDogumTarihi,@CalisanProgDilleri)
END
END

Son olarak, oluşturulan stored procedure yapısını silmek için DROP komutu kullanılır. Böylelikle sistemdeki prosedürü ortadan kaldırabilirsiniz. Şunu unutmamak gerekir ki oluşturulan stored procedurelerin encryption özelliği varsa sadece sistem admin ve o procedürü yapan kişi silebilir.

DROP PROC SP_CalisanEkle

BONUS

Çalışanlar isimli tablo kolonları ad, soyad ,bölüm ve maaş olsun. Aşağıdaki 2 farklı soruya 2 farklı SP oluşturalım:

CREATE TABLE dbo.Calisanlar
(
ad nvarchar(40),
soyad nvarchar(40),
bolum nvarchar(10),
maas decimal(5,2)
);

Örnek olarak eklediğim 3 kişi:

INSERT INTO dbo.Calisanlar VALUES ("ali","ali","yazilim",123)
INSERT INTO dbo.Calisanlar VALUES ("ahmet","ahmet","yazilim",321)
INSERT INTO dbo.Calisanlar VALUES ("ayse","ayse","cto",555)
1> select * from dbo.Calisanlar
2> go
ad                                       soyad                                    bolum      maas
---------------------------------------- ---------------------------------------- ---------- -------
ali                                      ali                                      yazilim    123.00
ahmet                                    ahmet                                    yazilim    321.00
ayse                                     ayse                                     cto        555.00

1. Soru

Çalışan tüm çalışanların maaşlarının toplamını döndüren sp:

CREATE PROC SP_MAAS_TOTAL
( @bolum nvarchar(10) )
WITH ENCRYPTION
AS
BEGIN
Select SUM(maas) as Total_Maas FROM dbo.Calisanlar where bolum=@bolum (1)
END
  1. Filtreden dönen çalışanlar üzerindeki maaşların hepsini SUM(KOLON_ISMI) metodunu kullanarak ekrana yansıtılır.

Etkilenen çalışanların tüm maaşlarının toplamı ekrana yansıyacaktır:

EXEC SP_MAAS_TOTAL "yazilim"

2. Soru

Bölüm bilgisinden çalışan personellerin maaşını %20 zam yapan prosedür yapımı.

CREATE PROC SP_UPT_MAAS
( @bolum nvarchar(10) )
WITH ENCRYPTION
AS
BEGIN
UPDATE dbo.Calisanlar SET maas = maas * 1.20 WHERE bolum=@bolum (1)
END
  1. Filtreden dönecek olan çalışanlar üzerinden UPDATE komutunu kullanarak maaşları %20 arttırabiliriz.

Su şekilde çalıştırıldığında ekrana kaç çalışanın etkinlendiği yazacaktır. Diğer işlemler arkada gerçekleşecek.

EXEC SP_UPT_MAAS "cto"

Bir sonraki yazıda görüşmek üzere.

Hakan Özler

MongoDB and Docker Enthusiast, JCP Contributor. Java and Certified MongoDB developer. Computer Engineer, MSc.

3 Comments

  1. Biz stored procedüre kullanarak örneğin 1 olanları2,2 olanları 3 yapan kodu nasıl yazarız?

  2. Merhaba procedure lerde LIKE komutunu nasıl kullanabilirim. Northwind veritabanını kullanıyorum. Şöyle birşey yapmak istiyorum. (Ürün ismi c ile başlayan ürünleri listeleyen procedure ( ürün ismi parametre olarak alınacak )

    Yaptığım kod şu şekilde.

    CREATE PROCEDURE sp_UrunIsmiC
    @pName nvarchar (50)
    AS
    SELECT * FROM Products WHERE ProductName LIKE ‘c%’ + @pName +’c%’

    Ama exec sp_UrunIsmiC ‘c%’

    Bu şekilde çalıştıramadım. VIEW ile yapılıyor ama procedure leri daha iyi öğrenmek içn böyle birşey yapmaya çalışıyorum

Bir Cevap Yazın