SQL’de Stored Procedure Kullanımı

Bu yazımızda SQL’de Stored Procedure yani saklı yordamın ne olduğunu, sorgu güvenliği ve performansı açısından bizlere neler kazandırdığını ele alacağız.

Stored Procedure

Prosedürler, veri tabanında performans, sorgu yükünü azaltma ve sorgu güvenliği açısından oldukça fayda sağlar. Bazen karşımıza “Stored Procedure” yani saklı yordam olarak da karşımıza çıkabilir. Yapısı programlama da kullandığımız metot yapısına benzer. Uzun ve sık kullandığımız SQL sorgularımızı tek kelimelik yapılara indirir. Bu sayede bahsettiğimiz avantajlara erişmiş oluruz. Prosedür oluştururken genellikle DDL (Data Definition Language) yani veri tanımlama dilinden faydalanırız. Yaratma işlemini Create, güncelleme işlemini Alter, silme işlemlerimiz için de Drop komutumuzu kullanırız.

AVANTAJLARI

• Dışarıdan parametre alabilir.
• Sorgu tekrarını önler.
• Sorgu performansını yükseltir.
• Sorgu güvenliğini artırır.

Prosedür Kullanımı

Prosedür oluşturmak için iki seçeneğimiz var. Birincisi sol taraftan bulunan Stored Procedures isimli klasörümüze sağ tıklayıp New diyerek. İkinci seçeneğimiz ise sorgu ekranımız üzerinden T-SQL ile oluşturmak. Biz Stored Procedure yapısını daha iyi anlamak için T-SQL ile ilerleyeceğiz.

-- Prosedür Syntax(Söz dizimi);
CREATE PROCEDURE TABLE
AS
BEGIN 
SORGULAR
END

İlk olarak aşağıdaki gibi tablolarımızı oluşturup ilgili kayıtları girelim.

Tahsilat tablomuzu oluşturalım.

CREATE TABLE tbl_nkthsl
(
tahsilat_id int primary key identity,
tarih date,
cari_id int,
ad nvarchar(20),
acıklama nvarchar(100),
tahsil_edilen_fyt smallmoney
)

Tahsilat tablomuza ilgili kayıtları ekleyelim.

INSERT INTO tbl_nkthsl (tarih, cari_id, ad, acıklama, tahsil_edilen_fyt)
VALUES(GETDATE(),1,'mert','Mert Ticaret A.Ş. için tahsil edilen tutar',5800)

INSERT INTO tbl_nkthsl (tarih, cari_id, ad, acıklama, tahsil_edilen_fyt)
VALUES(GETDATE (),2,'mert','Ahmet Ticaret A.Ş. için tahsil edilen tutar',8400)

INSERT INTO tbl_nkthsl (tarih, cari_id, ad, acıklama, tahsil_edilen_fyt)
VALUES(GETDATE (),3,'mert','Yusuf Ticaret A.Ş. için tahsil edilen tutar',9500)

INSERT INTO tbl_nkthsl (tarih, cari_id, ad, acıklama, tahsil_edilen_fyt)
VALUES(GETDATE (),4,'mert','Selin Ticaret A.Ş. için tahsil edilen tutar',6300)

INSERT INTO tbl_nkthsl (tarih, cari_id, ad, acıklama, tahsil_edilen_fyt)
VALUES(GETDATE (),5,'mert','Elif Ticaret A.Ş. için tahsil edilen tutar',5500)

Nakliye gideri tablomuzu oluşturalım.

CREATE TABLE tbl_nklyegdr
(
gider_id int primary key identity,
sefer_id int,
plaka char(8),
tarih date,
fiyat smallmoney,
acıklama nvarchar(100),
personel_id int 
)

Nakliye gideri tablomuza ilgili kayıtları ekleyelim.

INSERT INTO tbl_nklyegdr (sefer_id, plaka, tarih, fiyat, acıklama, personel_id)
VALUES(1,'40 BB 80',GETDATE (),5400,'x ürünü için gönderi gideri',1)

INSERT INTO tbl_nklyegdr (sefer_id, plaka, tarih, fiyat, acıklama, personel_id)
VALUES(2,'06 AB 79',GETDATE(),9800,'X ürünü için gönderi gideri',2)

INSERT INTO tbl_nklyegdr (sefer_id, plaka, tarih, fiyat, acıklama, personel_id)
VALUES(3,'38 DD 90',GETDATE(),1000,'X ürünü için gönderi gideri',3)

INSERT INTO tbl_nklyegdr (sefer_id, plaka, tarih, fiyat, acıklama, personel_id)
VALUES(4,'35 ED 50',GETDATE(),7000,'X ürünü için gönderi gideri',4)

INSERT INTO tbl_nklyegdr (sefer_id, plaka, tarih, fiyat, acıklama, personel_id)
VALUES(5,'34 EF 40',GETDATE(),6500,'X ürünü için gönderi gideri',5)

Gönderici alıcı tablomuzu oluşturalım.

CREATE TABLE tbl_gndrc_alc
(
gonderici_id int primary key identity,
gonderici_ad nvarchar(20),
alıcı_ad nvarchar(20),
malzeme_ad nvarchar(30),
cins nvarchar(20),
birim nvarchar(20),
miktar tinyint,
fiyat smallmoney,
acıklama nvarchar(100),
malzeme_id int
)

Gönderici alıcı tablomuza ilgili kayıtları ekleyelim.

INSERT INTO tbl_gndrc_alc (gonderici_ad, alıcı_ad, malzeme_ad, cins, birim, miktar, fiyat, acıklama, malzeme_id)
VALUES('Mert Ticaret A.Ş.','burak','ABC Bisküvi','Bisküvi','20',40,400,'Mert ticaret tarafından burak bey e gönderilen ürün',1)

INSERT INTO tbl_gndrc_alc (gonderici_ad, alıcı_ad, malzeme_ad, cins, birim, miktar, fiyat, acıklama, malzeme_id)
VALUES('Selin Ticaret A.Ş.','volkan','ABC Kraker','Kraker','30',50,600,'Selin ticaret tarafından volkan bey e gönderilen ürün',2)

INSERT INTO tbl_gndrc_alc (gonderici_ad, alıcı_ad, malzeme_ad, cins, birim, miktar, fiyat, acıklama, malzeme_id)
VALUES('Ahmet Ticaret A.Ş.','mert','ABC İçecek','İçecek','35',45,800,'Ahmet ticaret tarafından mert bey e gönderilen ürün',3)

INSERT INTO tbl_gndrc_alc (gonderici_ad, alıcı_ad, malzeme_ad, cins, birim, miktar, fiyat, acıklama, malzeme_id)
VALUES('Yusuf Ticaret A.Ş.','elif','AAA Cips','Cips','40',95,890,'Yusuf ticaret tarafından elif hanıma gönderilen ürün',4)

INSERT INTO tbl_gndrc_alc (gonderici_ad, alıcı_ad, malzeme_ad, cins, birim, miktar, fiyat, acıklama, malzeme_id)
VALUES('Elif Ticaret A.Ş.','sena','AAA Çikolata','Çikolata','39',65,444,'Elif ticaret tarafından sena hanıma gönderilen ürün',5)

INSERT INTO tbl_gndrc_alc (gonderici_ad, alıcı_ad, malzeme_ad, cins, birim, miktar, fiyat, acıklama, malzeme_id)
VALUES('Elif Ticaret A.Ş.','sena','AAA Çikolata','Çikolata','39',65,444,'Elif ticaret tarafından sena hanıma gönderilen ürün',5)

Örnekler

1. İşletmemizde sürekli tahsilat işlemleri yapıyoruz. Bunun içinde defalarca aynı sorguyu yazmak yerine yazdığım bu procedure aracılığı ile hem sorgu tekrarından kurtulmuş olacağım hem de performansımı artırmış olacağım.

CREATE PROCEDURE pr_thslgncl
(
@tahsilat_id INT,
@tarih date,
@cari_id INT,
@ad NVARCHAR(20),
@acıklama NVARCHAR(50),
@tahsil_edilen_fyt smallmoney
)
AS 
BEGIN
UPDATE tbl_nkthsl 
SET 
tarih =@tarih ,
cari_id =@cari_id ,
ad =@ad ,
acıklama =@acıklama ,
tahsil_edilen_fyt =@tahsil_edilen_fyt 
WHERE tahsilat_id=@tahsilat_id ;
END;

Create procedure ifadesi ile procedure oluşturmaya başlıyorum ardından ilgili sütunlarımı oluşturuyorum. BEGIN /AND bloğu içerisinde de Where ifadesi ile şartımı koyuyorum eğer tahsilat_id değeri benim göndereceğim tahsilat_id değerine eşitse diğer sütunları benim göndereceğim değerler ile doldur.

Execute ile çalıştırdığımızda procedure yaratılmış olacak. Şimdi aşağıda yazacağım sorgu ile bir tahsilatımızı güncelleyelim.

EXEC pr_thslgncl 3,'2022 -06- 06',3,'hatice','Mert Ticaret A.Ş. için tahsil edilen tutar',59002.

2. Yeni bir nakliye gideri oluşmuş olabilir bunun için oluşturduğum prosedür.

CREATE PROCEDURE pr_nklygdreklm
(
@sefer_id INT,
@plaka CHAR(8),
@tarih DATE,
@fiyat SMALLMONEY,
@aciklama NVARCHAR(100),
@personel_id INT
)
AS
BEGIN 
INSERT INTO tbl_nklyegdr (sefer_id, plaka, tarih, fiyat, acıklama, personel_id)
VALUES(@sefer_id ,@plaka ,@tarih ,@fiyat ,@aciklama ,@personel_id)
END;

Execute ile çalıştırdığımızda procedure yaratılmış olacak. Şimdi aşağıda yazacağım sorgu ile yeni bir nakliye giderini kolayca ekleyelim.

EXEC pr_nklygdreklm 6,'40-CC 14','2022-12-11',4800,'A ürünü için gönderi',5

3. Gönderici/alıcı tabloma yanlış kayıt girilmiş olabilir ya da göndericileri ve alıcıları silmek için oluşturduğum prosedür

CREATE PROCEDURE pr_gndrcalcbul
(
@gonderici_id INT,
@gonderici_ad NVARCHAR(20),
@alıcı_ad NVARCHAR(20),
@malzeme_ad NVARCHAR(30),
@cins NVARCHAR (30),
@birim NVARCHAR(20),
@miktar TINYINT,
@fiyat SMALLMONEY,
@acıklama NVARCHAR(100),
@malzeme_id INT
)
AS
BEGIN
DELETE FROM tbl_gndrc_alc 
WHERE gonderici_id =@gonderici_id 
END;

Execute ile çalıştırdığımızda procedure yaratılmış olacak. Şimdi aşağıda yazacağım sorgu ile gönderici_id değeri 7 olan kaydını çeşitli sebeplerle silmek istiyorum.

EXEC pr_gndrcalcbul 7

Bu yazımızda, Prosedürlerin SQL Server da neden kullanıldığını, prosedürler aracılığı ile sorgu kirliliğini artırmadan ve performansımızı düşürmeden nasıl uygulanabilir olduğunu öğrendik.