Merhabalar, bu yazıda Common Table Expressions(CTE) ve Recursive Queries kullanımını inceleyeceğiz.
CTE kullanımında unutulmaması gerekenler;
- CTE kullanımıyla üzerinde çalışacağımız veri setini küçülterek gereksiz yüklerden kurtulmuş olmak, okunabilirlik.
- Rekürsif sorgularda, aynı tablo üzerindeki tekrarlı joinler için ( hiyerarşik verileri sorgulamak gibi)
- Kendi indexleri yoktur. İçeriğindeki tabloların indexlerini kullanırlar.
- Sadece çalıştığı query bloğunda geçerlidir.
CTE syntax ı
WITH CTE_ISMI [ kolon1,kolon2,kolon3…. ]
AS
( CTE Kaynak Sorgusu )
Şeklindedir ve
SELECT kolon1,kolon2… FROM CTE_ISMI şeklinde sorgulanabilir.
Örnek olarak;
WITH cteDeneme (Unvan,Cinsiyet)
AS
(
SELECT [JobTitle],[Gender] FROM [HumanResources].[Employee]
)
SELECT * FROM cteDeneme
Resim-1
Bu basit örnekle CTE syntaxını gördükten sonra yazımızın bir diğer önemli noktası olan Rekürsif sorgulara bakalım.
Rekürsif CTE örneği için en uygun yapılardan biri Yönetici-Çalışan hiyerarşisidir. Bunun için Adventurework2008 R2 içerisindeki [dbo].[uspGetManagerEmployees] Stored Procedure (sp) ünü inceleyelim.
Prosedür Scripti ;
ALTER PROCEDURE [dbo].[uspGetManagerEmployees]
@BusinessEntityID [int]
AS
BEGIN
SET NOCOUNT ON;
WITH [EMP_cte]([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [RecursionLevel])
AS (
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], 0
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
WHERE e.[BusinessEntityID] = @BusinessEntityID
UNION ALL
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName],[RecursionLevel] + 1
FROM [HumanResources].[Employee] e
INNER JOIN [EMP_cte] ON e.[OrganizationNode].GetAncestor(1) = [EMP_cte].[OrganizationNode]
INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
)
SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[OrganizationNode].ToString() as [OrganizationNode], p.[FirstName] AS ‘ManagerFirstName’, p.[LastName] AS ‘ManagerLastName’,
[EMP_cte].[BusinessEntityID], [EMP_cte].[FirstName], [EMP_cte].[LastName
FROM [EMP_cte]
INNER JOIN [HumanResources].[Employee] e ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
ORDER BY [RecursionLevel], [EMP_cte].[OrganizationNode].ToString()
OPTION (MAXRECURSION 25)
END;
Bu sp bir yöneticiye bağlı tüm çalışanları (direkt ve dolaylı bağlı olan tüm çalışanları) listelemek için kullanılmaktadır. CTE içerisinde rekürsif bir sorgu örneğidir.
Adım adım inceleyelim;
ALTER PROCEDURE [dbo].[uspGetManagerEmployees]
@BusinessEntityID [int]
AS
BEGIN
Sp, integer @BusinessEntityID parametresi alıyor.
SET NOCOUNT ON;
SQL’de çalıştırdığımız işlem sonucunda dönen, etkilenen satır sayısı sonucunu kapatmaya yarar. (Resim-2 deki satır sayısını görmek istemiyoruz)
Resim-2
Buraya kadar standart bir sp de görebileceğimiz kodlarla geldik, CTE kısmı şimdi başlıyor.
WITH [EMP_cte]([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [RecursionLevel])
AS (
CTE WITH ile başlar, daha sonra CTE ismi (EMP_cte) gelir, bunlardan sonra da CTE kümemizin elemanlarını yazarız.(Alanlar)
CTE yi bir view gibi düşünürsek ([BusinessEntityID], [OrganizationNode], [FirstName], [LastName], [RecursionLevel] alanlarını view select satırında çektiğimiz tablo alanlarına benzetebiliriz.
Şimdi CTE nin kaynağı olacak sorgumuzu yazacağız. Burada CTE genel mantığını anlatırken, daha iyi anlaşılması açısından incelediğimiz sp nin satırlarını da yorumlayalım.
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName], 0
FROM [HumanResources].[Employee] e
INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
WHERE e.[BusinessEntityID] = @BusinessEntityID
UNION ALL
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName],[RecursionLevel] + 1
FROM [HumanResources].[Employee] e
INNER JOIN [EMP_cte] ON e.[OrganizationNode].GetAncestor(1) = [EMP_cte].[OrganizationNode]
INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
Amacımız bir yöneticiye direkt ya da dolaylı bağlı tüm çalışanların listesini çıkarmak,Union bloğunun ilk kısmında bu tepe yöneticimize ulaşıyoruz. Örneğe [BusinessEntityID] si 16 olan David Bradley ile devam edelim.(Resim-3)
Resim-3
Şimdi Union bloğunun ikinci kısmına devam edelim. İşin rengi değişmeye başlayacak J
Kod içerisindeki GetAncestor(1) metodunun dikkatinizi çekmiştir muhtemelen, bu method mevcut satırın atasını getirir.( OrganizationNode alanı hierarchyid tipinde !)
Bu bilgi ile ikinci bloğu tekrar okuyacak olursak
SELECT e.[BusinessEntityID], e.[OrganizationNode], p.[FirstName], p.[LastName],[RecursionLevel] + 1
FROM [HumanResources].[Employee] e
INNER JOIN [EMP_cte] ON e.[OrganizationNode].GetAncestor(1) = [EMP_cte].[OrganizationNode]
INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
CTE bloğu içerisinde CTE nin kendisinin çağrıldığına dikkat ediniz !(Rekürsif yapı)
CTE ye BusinessEntityID 16 verdiğimiz için
[Employee].[OrganizationNode].GetAncestor(1) = [EMP_cte].[OrganizationNode]
Satırını
Atası = 16 olanlar şeklinde yorumlayabiliriz.
CTE kaynak kısmını bitirdikten sonra;
SELECT [EMP_cte].[RecursionLevel], [EMP_cte].[OrganizationNode].ToString() as [OrganizationNode], p.[FirstName] AS ‘ManagerFirstName’, p.[LastName] AS ‘ManagerLastName’,
[EMP_cte].[BusinessEntityID], [EMP_cte].[FirstName], [EMP_cte].[LastName
FROM [EMP_cte]
INNER JOIN [HumanResources].[Employee] e ON [EMP_cte].[OrganizationNode].GetAncestor(1) = e.[OrganizationNode]
INNER JOIN [Person].[Person] p ON p.[BusinessEntityID] = e.[BusinessEntityID]
ORDER BY [RecursionLevel], [EMP_cte].[OrganizationNode].ToString()
OPTION (MAXRECURSION 25)
Sorgusuyla oluşturulan CTE sorgulanmaktadır. Burada görsel olarak yönetici ve çalışanı yan yana gösterebilmek için CTE den gelen yöneticinin çalışanları sorgusunun GetAncestor(1) ile atasına giderek yönetici bilgilerini buluyoruz.
Daha iyi anlaşılması açısından ;
Select * from [EMP_cte] kısmını çalıştırsaydık;
Resim-4
Sonucunu elde edecektik.
Bunu Employee ve Person ile birleştirerek sorguladığımızda;
Resim-5
Bilgisini elde etmiş oluyoruz.
Son olarak sorgunun en altında gördüğümüz OPTION (MAXRECURSION 25) kodu, özellikle rekürsif sorgularda ihmal etmememiz gereken gereksiz döngü ihtimalinin önüne geçmek için bir önlem. Sorguyu en fazla 25 kez iç içe çalışması için kısıtlamış oluyoruz. Bu değeri 0 and 32,767 arasında ayarlayabiliriz.0 sonsuz anlamına gelir, bu opsiyonu belirtmememiz durumunda Server seviyesinde default değer olan 100 e kadar devam eder.
Basic CTE kullanımını ve Rekürsif sorgulama yapısını örnekler üzerinden incelemiş olduk.
Bu konuyla ilgili sorularınızı alt kısımda bulunan yorumlar alanını kullanarak sorabilirsiniz.
Referanslar
Yorumlar (1)