1. Ana Sayfa
  2. SQL Server
  3. SQL Server – Common Table Expressions (CTE) – Rekürsif Sorgulama

SQL Server – Common Table Expressions (CTE) – Rekürsif Sorgulama

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ı https://forum.mshowto.org linkini kullanarak ulaşacağınız forum sayfamızda sorabilirsiniz.

Referanslar

www.mshowto.org

Yorum Yap

Yazar Hakkında

İstanbul Üniversitesi Bilgisayar Mühendisliği mezunudur. Kariyerine yazılım ile başlamış olup, zaman içerisinde .Net ve Dynamics Axapta üzerinde geliştirmeler yapmıştır. Son yıllarda yoğun olarak, veritabanı teknolojileri, iş zekası ve raporlama sistemleri ile ilgilenmektedir. Eroğlu Holding'de Bilgi Teknolojileri Proje Lideri olarak çalışmaktadır.

Yorum Yap

Yorumlar (1)