Oracle Veritabanında Hiyerarşik Sorgular

2015-10-29 21:25:21


Veritabanındaki tablolarımızda bazen hiyerarşik yapıda veriler tutarız. Mesela bir şirketin çalışanlarını bir tabloda tuttuğumuzu düşünelim. Çalışanın yöneticisi de bir çalışan olacağı için o da aynı tabloda yer alacaktır. O yöneticinin de yöneticisi olacaktır, yöneticinin yöneticisinin yöneticisi de ...  :)  Bu şekilde uzar gider. Tuttuğumuz bu hiyerarşik veriyi raporlamamız veya bir yerlerde hiyerarşik şekilde göstermemiz gerekecektir. Örneğin, bir yöneticiye bağlı tüm çalışanları veya bir çalışanın bağlı olduğu tüm yöneticiyi ve onun üzerindeki yöneticileri sorgulamamız gerekebilir. Hiyerarşinin derinliği belli olsaydı belki SELF JOIN yapan birkaç sorguyu UNION ile birleştirir sonucu elde ederdik ancak derinlik belli olmadığı için başka bir çözüme ihtiyacımız var. İşte burada hiyerarşik sorgular imdadımıza yetişiyor :) Şimdi Oracle içerisinde gelen HR şemasındaki EMPLOYEES tablosu üzerinde örneklerle devam edelim. Aşağıda HR şemasının ER diyagramı yer alıyor. Biz sadece Employees tablosu ile ilgileneceğiz.


hr


Employees tablosuna baktığımızda her çalışanın bir id'si  ( employee_id ), adı, soyadı  ve bağlı olduğu yöneticinin id'si (manager_id) var. Diğer sütunlarla şimdilik bir işimiz yok. Biz şimdi en üst yöneticiye bağlı çalışanları hiyerarşik bir şekilde listeleyelim. Yani şirketin tüm çalışanları sorgu sonucumuza gelecek ancak herkes kendi yöneticisinin altında görünecek.


SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY MANAGER_ID =PRIOR EMPLOYEE_ID;


Yukarıdaki sorguyu çalıştırdığımızda gelen sonuçların bir kısmını aşağıda görüyoruz. İlk sırada MANAGER_ID sütunu boş olan kaydı yani en üst düzey yöneticiyi görüyoruz. Onun altında ona bağlı olan Neena isimli çalışanı; onun da altında Neena isimli çalışana bağlı olan Nancy isimli çalışanı görüyoruz. Sorgumuzun sonucunda gelen kayıtlar hiyerarşik bir şekilde sıralanmış durumda.

emp


Hiyerarşik sorgularda START WITH ve CONNECT BY ... PRIOR ifadelerini kullanıyoruz.  Eğer sorgu sonucumuzu bir ağaca ( tree yapısı ) benzetirsek, ağacımızın kök düğümünü ( root node ) START WITH ifadesi ile belirtiyoruz. Örneğimizde START WITH MANAGER_ID IS NULL ifadesi ile en üst düzey yöneticiyi ifade ettik. Bunun yerine START WITH EMPLOYEE_ID=100 veya START WITH FIRST_NAME='Steven' AND LAST_NAME='King'  ifadelerini de kullanabilirdik. Yani en üst düzey yöneticiyi belirten herhangi bir ifade kullanabiliriz.  CONNECT BY...PRIOR ile ilişkiyi kuruyoruz. CONNECT BY MANAGER_ID=PRIOR EMPLOYEE_ID  ifadesi ile yukarıdaki bir çalışanın aşağıdaki bir çalışanın yöneticisi olduğunu belirttik. Sonucu ağaç yapısında düşünürsek, ağacın kök düğümünden oluşmaya başladığını ve ağaçta yukarıda yer alan çalışanın aşağısındaki düğümde yer alan çalışanın yöneticisi olduğunu belirttik. PRIOR ifadesinin EMPLOYEE_ID'nin yanında yer alması bu şekilde sonuç verdi.

Eğer seçtiğimiz bir çalışanın bağlı olduğu yöneticileri listelemek istersek nasıl bir sorgu yazmamız gerekir ? Burada PRIOR ifadesinin yeri ve START WITH ifadesi önemli oluyor. Şimdi EMPLOYEE_ID'si 109 olan Daniel isimli çalışanın yöneticilerini listeleyelim.


SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID
FROM EMPLOYEES
START WITH EMPLOYEE_ID=109
CONNECT BY PRIOR MANAGER_ID = EMPLOYEE_ID;

emp

START WITH EMPLOYEE_ID=109 ile Daniel isimli çalışandan başlamamız gerektiğini; yani ağacımızın kök düğümünün Daniel olması gerektiğini belirttik. CONNECT BY PRIOR MANAGER_ID=EMPLOYEE_ID ifadesi ile ağaçta Daniel'den sonra gelen kaydın Daniel'in yöneticisi olduğunu ifade ettik.  

İlk sorgumuza geri dönüp, sorgu sonucunda çalışanın yöneticisinin ismini de yanına yazdıralım. İlk başta SELF JOIN aklımıza geliyor ancak hiyerarşik sorgularda bunun da kolayı var :)

SELECT EMPLOYEE_ID, FIRST_NAME, LAST_NAME, MANAGER_ID,PRIOR FIRST_NAME AS MANAGER_NAME
FROM EMPLOYEES
START WITH MANAGER_ID IS NULL
CONNECT BY MANAGER_ID =PRIOR EMPLOYEE_ID;


SELECT ifadesinde PRIOR FIRST_NAME ile ağaçtaki bir önceki çalışanın yani yöneticinin FIRST_NAME değerini alabiliyoruz. Bu Oracle'ın sağladığı işe yarayacak bir kolaylık :)

Hiyerarşik sorgular ile ilgili şimdilik bahsedeceklerim bu kadar. Yakın bir zamanda hiyerarşik sorgular ile ilgili bir yazı daha yazmaya çalışacağım. Şimdilik iyi çalışmalar :)