Stored Procedure Etiketine sahip yazılar.
May
13
2011

SQL PROCEDURE MACERASI -9

Buraya kadar her şey belli bir standart çerçevesinde gelişti, parametrik değişken geldi şartı sağlıyorsa X işlemi sağlamıyorsa Y işlemi gerçekleştirildi.

 

Ama biz bu kadar uzun sorgu cümleleri ile uğraşmak yerine DİNAMİK sorgular üzerinde çalışmış olsaydık bu sefer de PROSEDÜRLERİMİZİ kısaltabilecektik.

 

Ama Nasıl?

 

 

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

— =============================================

— Author:        Burak SEKERCIOGLU

— Create date: 12/05/2011

— Description:   SP TEST-9

— =============================================

 

CREATE PROCEDURE [dbo].[sp_TEST] (@tip int,@tip2 int)

 

AS

declare @sorgu nvarchar(max)

declare @odaca nvarchar(50)

declare @terkin nvarchar(50)

declare @sqlsorgu nvarchar(max)

declare @sor nvarchar(max)

declare @sor2 nvarchar(max)

declare @sqlsorgu2 nvarchar(max)

declare @sorgu2 nvarchar(max)

BEGIN

 

 

SET NOCOUNT ON;

 

 

if @tip=1

begin

set @sorgu='(indx.sanayi = 1)’

set @sqlsorgu=convert(nvarchar(max),@sorgu)

end

 

if @tip=2

begin

set @sorgu='(indx.ithalat = 1)’

set @sqlsorgu=convert(nvarchar(max),@sorgu)

end

 

if @tip=3

begin

set @sorgu='(indx.ihracat = 1)’

set @sqlsorgu=convert(nvarchar(max),@sorgu)

end

 

if @tip2=1

begin

set @sorgu2='(indx.sanayi = 1)’

set @sqlsorgu2=convert(nvarchar(max),@sorgu2)

end

 

if @tip2=2

begin

set @sorgu2='(indx.ithalat = 1)’

set @sqlsorgu2=convert(nvarchar(max),@sorgu2)

end

 

if @tip2=3

begin

set @sorgu2='(indx.ihracat = 1)’

set @sqlsorgu2=convert(nvarchar(max),@sorgu2)

end

 

 

set @odaca=’TASFİYE’

set @terkin=’TERK’

 

set @sor=’ and (durumtanimi<>@odaca) and (durumtanimi<>@terkin) ORDER BY kurum.grupkodu’

set @sor2=convert(nvarchar(max),@sor)

 

set @sorgu=’ SELECT     TOP (100) PERCENT  kurum.web, kurum.eposta, kurum.unvan, kurum.sicil, kurum.grupkodu,kurum.durum, kurum_indx.istihdam, kurum_indx.mark,kurum_indx.urun,sektor.sektor,kurum_indx.adres FROM         indx LEFT OUTER JOIN sektor ON indx.sektor = sektor.id RIGHT OUTER JOIN kurum ON indx.kno = kurum.kno WHERE’

if @tip is not null and @tip is null

begin

set @sorgu=@sorgu+  @sqlsorgu + @sor2

end

 

if @tip is not null and @tip is not null

begin

set @sorgu=@sorgu+  @sqlsorgu + ‘ OR ‘ + @sqlsorgu2 + @sor2

end

 

exec(@sorgu)

END

 

 

Yukarıdaki kodlardan da görebileceğiniz gibi her seferinde sorgumuzu uzun uzun yazmak yerine durumlara bağlı olarak @sorgu ve @sorgu2 ye değer atadık ve bu değerleri de @sqlsorgu ve @sqlsorgu2 değişkenlerine nvarchar(max) yaparak atadık.

 

Sonrasında ise @sorgu değişkeninde cümle yapılarını değişken şartlarına göre birleştirerek

exec(@sorgu)

ile çalıştırılmasını sağladık.

 

İşte böyle… Elimden geldiğince sizlere STORED PROCEDURE macerasında rehber olabilecek örnek yapıları anlatmaya çalıştım.

 

Bu konuda önemli olan şey, hayal gücünüzün sınırlarını genişletmek ve hayal gücünüz genişlerken de komut kombinasyonlarını korkmadan kullanabilmektir.

 

May
13
2011

SQL PROCEDURE MACERASI -8

Elimizde 3 farklı tablomuz olsun ve bu tablolardan 1 tane kayıt silinmek istendiğinde diğer 2 tablodaki ilişkili alanlarda silinsin ve yapılan işlemler bir log tablosunda kayıt altına alınsın. Eğer silinmek istenen kayıt 1. Tabloda yoksa hata kodu hatakod=0 olsun eğer kayıtlar başarılı olarak silinmiş ise de hatakod=1 olarak programa gelsin.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:        Burak SEKERCIOGLU
— Create date: 12/05/2011
— Description:   SP TEST-8
— =============================================
Create proc [sp_test] (
@kullanici nvarchar(max),
@domain nvarchar(max),
@org_id int
)
AS
declare @hatakod int
begin /*basla*/
if not @org_id=0
begin
IF EXISTS (select id from etkinlik WHERE id=@org_id)
begin
DELETE FROM ajanda WHERE  org_id=@org_id
DELETE FROM etkinlik WHERE id=@org_id
DELETE FROM materyal WHERE org_id=@org_id
execute sp_log @kullanici,@domain, ‘Etkinlik Silindi’
set @hatakod=0 /* Etkinlik Yok */
return @hatakod
end
else
begin
set @hatakod=1 /* Etkinlik Silindi*/
return @hatakod
end
end
/* Son */
end

Nasıl oldukça kolay oluyor değil mi?

Peki dikkatinizi bir şey çekti mi? Mevcut prosedürümüz içinden farklı bir prosedüre atıfta bulunduk ve mevcut prosedürün

parametreleri ile

execute sp_log @kullanici,@domain, ‘Etkinlik Silindi’

Diğer prosedürü de çalıştırdık. Bu sayede uzun uzadıya prosedürler yazmak yerine ortak kullanımdaki prosedür ile işlem loglarını sisteme kaydettik.

Devamı SQL Procedure Macerası -9

May
13
2011

SQL PROCEDURE MACERASI -7

Peki bir soru daha soralım. MSSQL üzerinden veri sorgularken (ASP, .Net) sayfa sayfa veri almamızı sağlayan. Pagesize,

.AbsolutePage gibi fonksyionlar Stored Procedure üzerinde kullanılabilir mi?

Bunu acı bir deneyim ile programım hata verdiğinde öğrenmiştim. Prosedür Server üzerinde çalışıyordu ama program kodlarında

sayfalama kısmına geldiğinde hata veriyordu. Sonradan öğrendim ki bu fonksiyonlar ile Stored Procedure kullanılamıyormuş.

O zaman bu işlem için farklı bir metod izlenmeliydi. Ama nasıl?

İşte kodlar;

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: Burak SEKERCIOGLU
— Create date: 12/05/2011
— Description: SP TEST-7
— =============================================
CREATE PROCEDURE [sp_test] (@tip int, @maxkayit int, @sayfa int)
AS
— =============================================
— Sayfalama Fonksiyonu için tanımlar yapılıyor
— =============================================
declare @kucukAralik int
declare @buyukAralik int
set @kucukAralik = (@sayfa-1) * @maxkayit
set @buyukAralik= (@sayfa*@maxkayit)
— =============================================
BEGIN
SET NOCOUNT ON;
if @tip=1
begin
SELECT * FROM (
SELECT row_number() over (order by kno) as rowid,
webadresi,
epostaadresi,
kurumunvani,
sicilno,
grupkodu,
durumbilgisi,
istihdam,
marka,
urunler,
sektor,
adres
FROM
kurumex LEFT OUTER JOIN sektorler ON kurumex.sektor = sektor.id
RIGHT OUTER JOIN kurumlar ON kurumex.kno = kurum.kno
WHERE (kurumex.sanayi = 1) ) AS sonuclar
WHERE sonuclar.rowid between @kucukAralik and @buyukAralik
end
if @tip=2
begin
SELECT * FROM (
SELECT row_number() over (order by kurumno) as rowid,
webadresi,
epostaadresi,
kurumunvani,
sicilno,
grupkodu,
durumbilgisi,
istihdam,
marka,
urunler,
sektor,
adres
FROM
kurumex LEFT OUTER JOIN sektorler ON kurumex.sektor = sektor.id
RIGHT OUTER JOIN kurumlar ON kurumex.kno = kurum.kno
WHERE (kurumex.ithalat = 1) ) AS sonuclar
WHERE sonuclar.rowid between @kucukAralik and @buyukAralik
end
if @tip=3
begin
SELECT * FROM (
SELECT row_number() over (order by kno) as rowid,
webadresi,
epostaadresi,
kurumunvani,
sicilno,
grupkodu,
durumbilgisi,
istihdam,
marka,
urunler,
sektor,
adres
FROM
kurumex LEFT OUTER JOIN sektorler ON kurumex.sektor = sektor.id
RIGHT OUTER JOIN kurumlar ON kurumex.kno = kurum.kno
WHERE (kurumex.ihracat = 1) ) AS sonuclar
WHERE sonuclar.rowid between @kucukAralik and @buyukAralik
end
END

Bu şekilde yaptığımızda neler oluyor, inceleyelim.

CREATE PROCEDURE [sp_test] (@tip int, @maxkayit int, @sayfa int)
AS
— =============================================
— Sayfalama Fonksiyonu için tanımlar yapılıyor
— =============================================
declare @kucukAralik int
declare @buyukAralik int
set @kucukAralik = (@sayfa-1) * @maxkayit
set @buyukAralik= (@sayfa*@maxkayit)
— =============================================

Daha önceden de anlattığımız gibi dışarıdan parametrik @tip, @maxkayit ve @sayfa verilerini alıyoruz.

Sonrasında ise bu verileri kullanarak gösterilmek istenen kayıt satır aralıklarını DECLARE ile belirtilen değişkenlere SET ile

tanımlıyoruz.

Şöyle ki;

Exec sp_test 1,10,2

ile sp_test prosedürüne değişkenlerimizi gönderdiğimizde sistem
@tip = 1
@maxkayit=10
@sayfa=2

Değerlerini alıyor. Sonrasında ise

set @kucukAralik = (@sayfa-1) * @maxkayit
set @buyukAralik= (@sayfa*@maxkayit)[/box]

Satırlarındaki tanımlara göre

@kucukAralik= (2-1) * 10
@buyukAralik=(2*10)

Oluyor ve recordset çıktımızın 10 ile 20. Kayıtlar arasında gösterilmesi sağlanıyor.

Peki kayıtların satır numaralarını neye göre belirliyoruz?

[box]
SELECT distinct(kurumno),* FROM (
SELECT row_number() over (order by kno) as rowid,
[/box]
Yukarıda göreceğiniz gibi iç içe 2 adet select yapısı kullanıyoruz ve en içteki kısımda da row_number() over (order by kno) as

rowid

ile listelenen kayıtların sıra numaralarını rowid kolonunda gösteriyoruz.

Buraya kadar herşey normal ama neden içiçe sorgu yarattık? diyenleriniz olabilir. Bunun sebebi de sorgumuzun satır aralığını

gösterirken Where rowid … şeklindeki alias ifadeleri kullanamayız ama içiçe yaratılan sorgulardan oluşan

ifadelerde bunu

[box]
) AS sonuclar
WHERE sonuclar.rowid between @kucukAralik and @buyukAralik
[/box]

Şeklinde bir sonuclar dizisine aktarıp sonra bu diziye ait sonuclar.rowid kolonları kullanılarak istenen sonuçlara ulaşabiliyoruz.

Nasıl, gittikçe Prosedürler hakkında daha keyifli bilgilere sahip oluyoruz değil mi? Daha bitmedi

Şimdi de şöyle düşünelim;

Devamı SQL Procedure Macerası -8

May
13
2011

SQL PROCEDURE MACERASI -6

Şu ana kadar anlatılanlarda hep DCL (Data Control Language) ile bir şeyleri kontrol edip, ilgili veritabanı üzerinde çeşitli işlemler yaptık.

Bu yapılan işlemler sonucunda da olumsuz durum oluşmuş ise hata mesajı döndürdük.

Peki ya tek adımlık işlemler yerine bir takım recordset yapıları üzerinden veri okumaya yönelik çalışmak isteseydik?

Aslına bakarsanız bu konu, önceden anlattıklarımızdan çok daha basit bir yapıya sahiptir ve her şey sizin HAYAL GÜCÜNÜZE bağlıdır.

SQL konusuna ilk başladığım günlerde daha VIEW nedir? Bilmezken. Yazmış olduğum programlarda uzun uzun SELECT cümleciklerini

sıralardım.

Sonrasında VIEW yaratmayı ve yaratılan sorguların yükünün sunucu tarafına bindirilmesini gördüm. Evet, sorgularım hızlanmıştı.

Demek ki bu VIEW dedikleri güzel bir şeydi 🙂

Ama zaman içersinde benim sorgular gittikçe büyüdü, tablolarımdaki veriler 100 binlerce satır halini almaya başladı ve hep daha

hızlısını istedikçe VIEW konusu da bana yetmemeye başladı.

Çünkü VIEW üzerinden bir veriyi alabilmek için öncelikle tüm ilgili verileri kapsayacak şekilde bir recordset alanı oluşturulmalı

ve yaratılan bu recordset üzerinden de WHERE cümlecikleri ile istenilen şartlar seçilmeliydi.

Gözden kaçan ise VIEW ilk yaratıldığında ya da çağrıldığında veritabanı üzerinde ilgili ne varsa önce çantasına dolduruyor,

ardından da sizden ilgili şartlar ile bu çantadakileri seçmenizi istiyordu.

Durum böyle olunca da program ciddi ölçüde yavaşlıyordu.

Daha sonra bu işi Stored Procedure ile nasıl yaparım? Konusuna geldim. Prosedür mantık olarak dışarıdan gelen parametreler

doğrultusunda bir sorgu yapacak ve çıktısını programa ulaştıracaktı.

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:        Burak SEKERCIOGLU
— Create date: 12/05/2011
— Description:   SP TEST-6
— =============================================
CREATE PROCEDURE [sp_test] (@tip int)
AS
BEGIN
SET NOCOUNT ON;
if @tip=1
begin
SELECT     TOP (100) PERCENT
webadresi,
epostaadresi,
kurumunvani,
sicilno,
grupkodu,
durumbilgisi,
istihdam,
marka,
urunler,
sektor,
adres
FROM
kurumex LEFT OUTER JOIN sektorler ON kurumex.sektor = sektor.id
RIGHT OUTER JOIN kurumlar ON kurumex.kno = kurum.kno
WHERE     (kurumex.sanayi = 1)
end
if @tip=2
begin
SELECT     TOP (100) PERCENT
webadresi,
epostaadresi,
kurumunvani,
sicilno,
grupkodu,
durumbilgisi,
istihdam,
marka,
urunler,
sektor,
adres
FROM
kurumex LEFT OUTER JOIN sektorler ON kurumex.sektor = sektor.id
RIGHT OUTER JOIN kurumlar ON kurumex.kno = kurum.kno
WHERE     (kurumex.ithalat = 1)
end
if @tip=3
begin
SELECT     TOP (100) PERCENT
webadresi,
epostaadresi,
kurumunvani,
sicilno,
grupkodu,
durumbilgisi,
istihdam,
marka,
urunler,
sektor,
adres
FROM
kurumex LEFT OUTER JOIN sektorler ON kurumex.sektor = sektor.id
RIGHT OUTER JOIN kurumlar ON kurumex.kno = kurum.kno
WHERE     (kurumex.ihracat = 1)
end
END
Yukarıdaki kodlardan da görebileceğiniz gibi prosedürümüze dışarıdan sadece @tip bilgisi gönderiliyor ve o da bu veriye göre

Sanayici, İthalatçı, İhracatçı ayrımı yaparak sadece ilgili verileri listeden çekiyor.

Diğer türlü ya ayrı ayrı view tasarımı yapılacaktı ya da tek view üzerinden WHERE ile sorgulama yapılacaktı.

Devamı SQL Procedure Macerası – 7

May
13
2011

SQL PROCEDURE MACERASI -5

Standart düzenlerde bunlar için ayrı ayrı bağlantı nesneleri yaratılır, tablonun bir tanesine kayıt yapılır sonra o kayıt tablodan

tekrar okunur ve alınan ID bilgisi diğer bir tabloyu çapraz olarak bağlamak üzere farklı bir alana kayıt edilir…

Sözel olarak özet geçmek istediğimde bile kafam karışıyor J programsal kısmını düşünmek bile istemiyorum. Haydi, bu kadar uzun

kodu yazdım diyelim o zaman da programın performansı ne kadar düşer tahmini bile zor J

O zaman bu tarz bir kodu STORED PROCEDURE ile yazmak istediğimizde bakın neler oluyor.
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author: Burak SEKERCIOGLU
— Create date: 12/05/2011
— Description: SP TEST-5
— =============================================
CREATE PROCEDURE sp_test (@kod1 int, @kod2 int)
AS
Declare @tablo1ID int
BEGIN
SET NOCOUNT ON;
if @kod1=1
begin
if not exist (select * from test_tablomuz Where alan1=@kod1 and alan2=@kod2)
begin
Insert into test_tablomuz (alan1,alan2) values (@kod1,@kod2)
set @tablo1ID=SCOPE_IDENTITY()
Insert into ikincitablo (tablo1id,alan1) values (@tablo1ID,@kod1)
End
Else
Begin
Print ‘TEST_TABLOMUZ tablosunda aynı kayıt var’
End
end
if @kod1=2
begin
if not exist (select * from test_tablomuz2 Where alan1=@kod1 and alan2=@kod2)
begin
Insert into test_tablomuz2 (alan1,alan2) values (@kod1,@kod2)
end
Else
Begin
Print ‘TEST_TABLOMUZ2 tablosunda aynı kayıt var’
End
end
END
GO

Yukarıdaki prosedürümüzde, öncekilere ek olarak

set @tablo1ID=SCOPE_IDENTITY()
Insert into ikincitablo (tablo1id,alan1) values (@tablo1ID,@kod1)

Satırları ile @tablo1ID değişkeninin tanımlandığı Declare @tablo1ID int Satırı eklendi.
Bu kodlarda ne anlatmak istedik, bakalım;

Declare @tablo1ID int
İle prosedürümüz içersinde @tablo1ID isimli bir değişkene INTEGER veri atayacağımızı söyledik.
Insert into test_tablomuz (alan1,alan2) values (@kod1,@kod2)
Test_tablomuz üzerine @kod1 ve @kod2 değişkenlerine atanan parametrik verileri kaydettik
set @tablo1ID=SCOPE_IDENTITY()
SCOPE_IDENTITY () fonksyionu ile Test_tablomuz üzerine kayıt ettiğimiz son verinin PRIMARY KEY (ID) değerini aldık ve

@tablo1ID değişkenine yükledik.
Insert into ikincitablo (tablo1id,alan1) values (@tablo1ID,@kod1)

Tanımlanan @tablo1ID değişkeni ikincitablo üzerinde ilgili kolona eklendi.

Umarım şu ana kadar anlaşılmayan bir şey yoktur. 🙂

Devamı SQL Procedure Macerası -6

May
13
2011

SQL PROCEDURE MACERASI -4

Peki… Ya biz bu sistemi SQL Server üzerinde çalıştırmak isteseydik? Orada geri dönen @hatakod yerine görsel olarak bir hata mesajı

görüntülememiz daha güzel olmaz mıydı?

Haydi, yazalım o zaman 🙂

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
— =============================================
— Author:        Burak SEKERCIOGLU
— Create date: 12/05/2011
— Description:   SP TEST-4
— =============================================
CREATE PROCEDURE sp_test (@kod1 int, @kod2 int)
AS
BEGIN
SET NOCOUNT ON;
if @kod1=1
begin
if not exist (select * from test_tablomuz Where alan1=@kod1 and alan2=@kod2)
begin
Insert into test_tablomuz (alan1,alan2) values (@kod1,@kod2)
End
Else
Begin
<strong>Print ‘TEST_TABLOMUZ tablosunda aynı kayıt var’</strong>
End
end
if @kod1=2
begin
if not exist (select * from test_tablomuz2 Where alan1=@kod1 and alan2=@kod2)
begin
Insert into test_tablomuz2 (alan1,alan2) values (@kod1,@kod2)
end
Else
Begin
<strong>Print ‘TEST_TABLOMUZ2 tablosunda aynı kayıt var’</strong>
End
end
END
GO

Gördüğünüz gibi PRINT fonksiyonu ile prosedür sonucumuzu ekrana yazdırabildik.

Stored Procedure sadece bu kadar mı? Elbette hayır, program yazarken sizin de başınıza gelmiştir. İçiçe döngüsel yapılar… Birinden alınan kayıt verisinin diğer bir tabloya ID verisi olarak kayıt edilmesi vs. vs.

Devamı Stored Procedure Macerası -5

TeamViewer ile İnternet üzerinden Uzaktan Erişim ve Destek Sekercioglu.eu Uzaktan Yardım
Ammyy Uzaktan Yardım
WinRAR
WEBMAIL Google PageRank Checker
Twitterda Takip Edin! Twitterda Takip Edin!
Twitter

Üye Paneli

Son Yazılar

Kategoriler

Son Yorumlar