Bu makalede, SQL
Server 2005 ile gelen T-SQL ifadelerinden CTE(Common Table Expression)yi ele
alacağız. Ayrıca makalede view ve türetilmiş tablo gibi terimlere de değineceğiz.
Bu çerçevede özellikle hiyerarşik veriler için sık sık ihtiyaç duyulan rekürsif
sorguların basitleştirilmesini ele alacağız.
Giriş
CTE(Common Table
Expressionlar) SQL-99 standardı çerçevesinde Microsoft T-SQLe girmiş bir
kalıptır. Bu kalıp, özellikle rekürsif sorguları basitleştirmek için etkin
bir yoldur. Genel itibari ile Viewlere ve türetilmiş tablo(Derived Table)
lara çok benzeyen bu kalıp, daha çok rekürsif(kendine atıfta bulunan ) sorguları
ifade etmeyi kolaylaştırıcı bir etkiye sahiptir
Öncelikle, view
ve türetilmiş tablo(derived table) kavramlarını birer örnek ile ele alalım:
View, bir sorgudan
ibaret olan veritabanı öğesi olup, tablo gibi davranır. Veritabanında kapladığı
alan bir SELECT… ifadesinden ibarettir.
Basit bir View
şu şekilde oluşturululabilir:
Örnek 1:
CREATE
VIEW reyon(a,b)
AS
SELECT categoryID, categoryNAme FROM tbCategory
|
Türetilmiş tablolar,
alt sorgularla çalışmanın özel bir halidir ve bir sorguda, doğrudan tablo
adı vermek yerine, başka bir sorgunun türettiği tabloyu tekrar sorgulamak
şeklinde özetleyebiliriz. Bu durumda türetilmiş tabloyu, kaydedilmemiş
view gibi düşünebiliriz.
Örnek 2:
SELECT
reyon .*
FROM (
SELECT categoryID , categoryName
FROM tbCategory )
reyon ( a , b )
WHERE reyon.a ]] 100
|
Sorgusunda, reyon
için türetilmiş tablo denir. Burada, sütun adlarını değiştirdik. (MS Access
ile çalışıyorsanız, sütun isimlerinin değiştirilmesine müsaade etmez.)
CTE
Genel Tablo İfadeleri(Common
Table Expressions) bir tabloyu ifadeye bağlı tanımlama ve arkasından bu tabloya
atıfta bulunacak bir sorgu şeklinde tanımlanabilir. Genel kullanımı şu şekildedir:
WITH CTEismi
( sütun-listesi )
AS ( CTE tanimi )
SELECT|INERT|UPDATE|DELETE… FROM… CTEismi
|
Az önceki örneği,
CTE ile tekrarlayacak olursak:
Örnek-3:
WITH reyonCTE
( categoryID , categoryName )
AS
(
SELECT categoryID , categoryName FROM tbCategory
)
SELECT reyonCTE . categoryID , reyonCTE . categoryName
FROM reyonCTE WHERE reyonCTE . categoryID ]] 100
|
Bir önceki Türetilmiş
Tablo ile bu örneğimiz arasındaki tek fark, aynı işin CTE ile yapılması.
Ancak bu tür işlemler için, elbette Türetilmiş Tablo, CTEye göre daha
sade bir hal almakta. Bu tür durumlar için CTE kullanmak avantaj yerine karmaşıklık
getirmekte.
Birden fazla CTE
aynı sorgu içerisinde kullanılabilir. Bu durum, birden fazla Türetilmiş tablo
ile çalışmaya benzer.
Örnek-4:
WITH CTEilkOnReyon
(categoryID, categoryName)
AS
(
SELECT TOP 10 categoryID, categoryName
FROM tbCategory
ORDER BY categoryID
),
CTEsonOnReyon(categoryID, categoryName)
AS
(
SELECT TOP 10 categoryID, categoryName
FROM tbCategory ORDER BY
categoryID DESC
)
SELECT * FROM CTEsonOnReyon, CTEIlkOnReyon
|
Bu sorgu, herhangi
iki türetilmiş tabloda olduğu gibi, iki CTE ifadesinin kartezyen çarpımını
üretecektir(Biraz saçma ama basit bir örnek).
DİKKAT:
Birden fazla CTE bir sorguda kullanılacaksa, Bir defa WITH deyimi geçtiğine
ve aralarına virgül konularak ayrıdığına dikkat edin.
Yinelemelilik(Recursion)
Türetilmiş tablo
kavramından sonra, rekürsif ifade ne demektir biraz da bunun üstünde duralım.
Rekürsif ifade, belli bir duruma kadar, sürekli olarak kendisine atıfta bulunan
bir sorgu, veri yapısı, fonksiyon veya kod parçası için kullanılan genel bir
ifadedir. Bunun için en iyi örnek, faktöriyel hesabı yapan bir fonksiyon
olabilir. Bu türden bir C# metodunu şu şekilde kodlayabiliriz:
Burada, dikkat
edilirse, bir biriktirme ve bir başlangıç ayarlama olmak üzere, rekürsif
işlem iki kısım halinde tanımlanmaktadır.
Örnek 5:
public
class FaktoriyelHesapla
{
public int sonuc = 0;
public FaktoriyelHesapla(int sayi)
{
// başlangıç ayarlama
sonuc=FaktoriyeliniAl(sayi);
}
private int FaktoriyeliniAl(int sayi)
{
if(sayi [[ 0 || sayi == 0 || sayi == 1)
return 1;
else
{
// biriktirme
return sayi * FaktoriyeliniAl(sayi-1);
}
}
}
|
İPUCU:
Sorguda görüldüğü gibi Rekürsiflik gereksinimi yok ise Sorguyu türetilmiş
tablolar kullanarak kurgulamak daha rahat ve okunabilir bir yöntemdir.
İlişkisel Ortamda
Hiyerarşik Veriyi Modellemek
İlişkisel veritabanında
hiyerarşik veri tutmak genellikle problemlidir. Aslında ilişkisel veriyi saklamak
bu işin en problemsiz ve en kolay kısmıdır. Şekil.1de bir hiyerarşik verinin
ağaç yapısı şeklinde gösterimini görebilirsiniz.
Şekil.1: Hiyerarşik
verilerin ağaç şeklinde gösterimi
Bu türden bir
hiyerarşik veriyi tutumak için şu şekilde bir ilişkisel tasarım yapılabilir:
(a)  (b)
Şekil 2: a) Hiyerarşik
veri saklamak için tasarlanmış bir kendisi ile ilişkili tablo. b)Bu tablo
içerisinde yer alan hiyerarşik verileri içeren bir sorgunun sonucu
Buraya kadar her
şey çok pratik. Peki dışarıdan bir kategori kodu geldiğinde bu kategori
ve bütün altındaki kategorilere ait ürünleri nasıl bulabiliriz? İşte bu
soruya cevap vermek daha önce karşılaşmadınız ise biraz vaktinizi alabilir.
Bunun için yapılabileceklerden biri, çoklu ifade ile tablo döndüren bir
fonksiyona, bir kategori kodu gönderildiğinde, bu kategori ve alt kategorilerinin
listesini tablo olarak döndürtmektir. Bunun için akla gelen ilk çözümlerden
birini aşağıda bulabilirsiniz:
SQL Server 2000de
Rekürsif Sorgu için Örnek Fonksiyon
Örnek-6:
CREATE
FUNCTION FN$CHILDCATSFINDER(
@categoryID
BIGINT
)
RETURNS @subCatIDs TABLE(categoryID BIGINT, passed BIT)
AS
BEGIN
DECLARE @sonucAdet TINYINT
INSERT INTO @subCatIDs
VALUES(@categoryID,1)
INSERT INTO @subCatIDs
SELECT categoryID,0
FROM tbCategory
WHERE parentCategoryID = @categoryID
SET @sonucAdet = @@ROWCOUNT
WHILE (@sonucAdet != 0)
BEGIN
INSERT
INTO @subCatIDs SELECT categoryID,0
FROM
tbCategory WHERE parentCategoryID IN
(SELECT
categoryID FROM @subCatIDs WHERE passed=0 )
SET
@sonucAdet = @@ROWCOUNT
UPDATE
@subCatIDs
SET
passed=1
END
RETURN
END
|
Burada yapılan
işlemleri özet olarak ele alalım.
Fonksiyonumuz şöyle
bir tablo değişken kullanmakta:
categoryID :BIGINT
passed :BIT
Bu alanlardan categoryIDde
kategorinin kodu, passed sütununda da alt kategorilerinin tabloya eklenip eklenmediğini
kontrol etmek için ekledik. Bir kategorinin alt kategorileri tabloya eklendiği
anda, bu alanı 1e eşitliyoruz.
Bunun dışında
yapılanları bir cümle ile özetleyelim: Son sorgudan sıfır kayıt etkileninceye
kadar, tablodaki her bir satıra ait alt satırlar tabloya eklenmekte.
Bu fonksiyon oluşturulduktan
sonra, bir reyon kodu geldiğinde, bu reyon ve bütün alt reyonlarında yer
alan ürünlerin listesini şu şekilde bulabiliriz:
SELECT *
FROM …. WHERE categoryID IN(SELECT categoryID FROM dbo.FN$CHILDCATSFINDER(1)
|
Görüldüğü
gibi, rekürsif bir sorguyu, bilindik yöntemlerle gerçekleştirmeye çalışmak
oldukça zahmetli bir işlem halini almaktadır. İşte bu türden bir sorun
CTE kullanımı için oldukça iyi bir sorundur. Şimdi problemi bir de bu yönü
ile inceleyeceğiz.
Hiyerarşik Sorgu
için CTEnin Rekürsif Kullanımı
Bir CTE kendine
referans içerdiğinde rekürsif olur. Bu durum için genel bir kalıp şu şekilde
verilebilir:
WITH
CTEIsim ( sutun listesi )
(
baslangic sorgusu
UNION ALL
CTEIsmini çağıran sorgu (biriktirme)
)
SELECT CTEye referans
|
Örnek-7:
Kategori ağacını hiyerarşik ve rekürsif sorgulamamız gerekiyorsa:
WITH
CatCTE(categoryID, categoryName)
AS
(
SELECT categoryID, categoryName FROM tbCategory
WHERE categoryID=1
UNION ALL
SELECT C.categoryID, categoryName
FROM tbCategory C JOIN CatCTE as CTE ON C.parentCategoryID
= CTE.categoryID
)
SELECT * FROM CatCTE
|
Bu tür bir yapıda,
kendini çağırma işlemi, ikinci SELECT boş sonuç seçtiğinde sona erer.
Aksi halde rekürsifliğin sonucu olarak sürekli kendisini çağırmaya devam
etmesi beklenir. Ancak performansın kırılmaması için SQL Server 2005in default
özelliği olarak, bir CTEnin en fazla 100 defa kendini çağırmasına müsaade
edilir. Bu sayıyı, artırmak veya azaltmak mümkündür.
Örnek -8:
En fazla 4 seviyeye kadar kendini çağıran bir CTE
WITH
CatCTE(categoryID, categoryName)
AS
(
SELECT categoryID, categoryName FROM tbCategory
WHERE categoryID=1
UNION ALL
SELECT C.categoryID, categoryName
FROM tbCategory C JOIN CatCTE as CTE
ON C.parentCategoryID = CTE.categoryID
)
SELECT * FROM CatCTE OPTION(MAXRECURSION 4)
|
DİKKAT
OPTION(MAXRECURSION
0) ile tanımlı bir SELECT cümlesi için bir tekrar çağırma sayısı kısıtlaması
yoktur. Bu tür bir ifade, ikinci SELECT ifadesi sonuç döndürmez oluncaya
kadar kendini çağırma devam eder.
Değerlendirme
ve Sonuç
CTE (veya GTI)
özellikle rekürsif sorgular veya hiyerarşik veriler sorgulamak için oldukça
avantajlı ve kullanımı kolay bir yapı olarak karşımıza çıkmakta. Özellikle
Tablo döndüren kullanıcı tanımlı fonksiyonlar(UDF) ile birlikte kullanımı
bir çok hiyerarşik sorun için çözüm olacaktır.
Makale:
SQL Server 2005 : Recursive(Öz-yinelemeli) Sorgular ve CTE ADO.NET ve SQL Yaşar Gözüdeli
|