ORACLE SQL HİYERARŞİK SORGU

April 23, 2014 · by altunkan · in PL/SQL

Hiyerarşik sorgular çoğu uygulamada hayatımızı daha kolay hale getiriyor. Bu yazıda en basitinden hiyerarşik sorgu anlatım ve örnekleri olacaktır.

Hiyerarşik sorgular bir tablo veya bir sorgu içerisindeki satırları birbirlerine belli koşullar ve başlangıç noktaları baz alınarak bağlamak için kullanılır. En çok kullanılan somut örneklerinden biri yönetici-çalışan örneğidir.

Aşağıdaki ddl ve dm içeren sql scripti örneğimizde kullanacağımız tablo ve kısıtları oluşturup, örnek verilerler eklemektedir. Tablomuz çalışanların tutulduğu tablo olmakta olup calisan_id sütunu birincil anahtardır. Dikkat edilmesi gereken diğer sütun ise yonetici_id sütunudur. Bu sütun hangi çalışanın hangi çalışana bağlı olduğu bilgisini tutar.


CREATE TABLE calisan
(
   calisan_id          NUMBER,
   yonetici_id         NUMBER,
   ad                  VARCHAR2 (100),
   soyad               VARCHAR2 (100),
   dogum_tarihi        DATE,
   maas                NUMBER,
   kayit_tarihi        DATE,
   guncelleme_tarihi   DATE
);

ALTER TABLE anil.calisan
ADD CONSTRAINT calisan_pk
PRIMARY KEY
(calisan_id);

CREATE SEQUENCE anil.calisan_s
   START WITH 1
   INCREMENT BY 1
   MINVALUE 1
   NOCACHE
   NOCYCLE
   NOORDER;

INSERT INTO calisan
     VALUES (anil.calisan_s.NEXTVAL,
             NULL,
             'ANIL',
             'ALTUNKAN',
             TO_DATE ('30.12.1987', 'dd.mm.rrrr'),
             2000,
             SYSDATE,
             SYSDATE);

INSERT INTO calisan
     VALUES (anil.calisan_s.NEXTVAL,
             NULL,
             'AAA',
             'AAA',
             TO_DATE ('12.04.1988', 'dd.mm.rrrr'),
             1750,
             SYSDATE,
             SYSDATE);

INSERT INTO calisan
     VALUES (anil.calisan_s.NEXTVAL,
             NULL,
             'BBB',
             'BBB',
             TO_DATE ('11.02.1990', 'dd.mm.rrrr'),
             1750,
             SYSDATE,
             SYSDATE);

INSERT INTO calisan
     VALUES (anil.calisan_s.NEXTVAL,
             NULL,
             'CCC',
             'CCC',
             TO_DATE ('06.04.1988', 'dd.mm.rrrr'),
             1750,
             SYSDATE,
             SYSDATE);

INSERT INTO calisan
     VALUES (anil.calisan_s.NEXTVAL,
             NULL,
             'DDD',
             'DDD',
             TO_DATE ('08.09.1990', 'dd.mm.rrrr'),
             1500,
             SYSDATE,
             SYSDATE);

INSERT INTO calisan
     VALUES (anil.calisan_s.NEXTVAL,
             NULL,
             'EEE',
             'EEE',
             TO_DATE ('22.07.1989', 'dd.mm.rrrr'),
             1500,
             SYSDATE,
             SYSDATE);

INSERT INTO calisan
     VALUES (anil.calisan_s.NEXTVAL,
             NULL,
             'FFF',
             'FFF',
             TO_DATE ('13.08.1991', 'dd.mm.rrrr'),
             1500,
             SYSDATE,
             SYSDATE);

INSERT INTO calisan
     VALUES (anil.calisan_s.NEXTVAL,
             NULL,
             'GGG',
             'GGG',
             TO_DATE ('05.04.1992', 'dd.mm.rrrr'),
             1250,
             SYSDATE,
             SYSDATE);

INSERT INTO calisan
     VALUES (anil.calisan_s.NEXTVAL,
             NULL,
             'HHH',
             'HHH',
             TO_DATE ('13.01.1992', 'dd.mm.rrrr'),
             1250,
             SYSDATE,
             SYSDATE);

UPDATE calisan
   SET yonetici_id =
          (SELECT calisan_id
             FROM calisan
            WHERE ad = 'ANIL')
 WHERE ad IN ('AAA', 'BBB');

UPDATE calisan
   SET yonetici_id =
          (SELECT calisan_id
             FROM calisan
            WHERE ad = 'AAA')
 WHERE ad IN ('CCC', 'DDD');

UPDATE calisan
   SET yonetici_id =
          (SELECT calisan_id
             FROM calisan
            WHERE ad = 'BBB')
 WHERE ad IN ('EEE');

UPDATE calisan
   SET yonetici_id =
          (SELECT calisan_id
             FROM calisan
            WHERE ad = 'CCC')
 WHERE ad IN ('FFF');

UPDATE calisan
   SET yonetici_id =
          (SELECT calisan_id
             FROM calisan
            WHERE ad = 'BBB')
 WHERE ad IN ('GGG', 'HHH');

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

CREATE TABLE calisan

(

calisan_id NUMBER,

yonetici_id NUMBER,

ad VARCHAR2 (100),

soyad VARCHAR2 (100),

dogum_tarihi DATE,

maas NUMBER,

kayit_tarihi DATE,

guncelleme_tarihi DATE

);

ALTER TABLE anil.calisan

ADD CONSTRAINT calisan_pk

PRIMARY KEY

(calisan_id);

CREATE SEQUENCE anil.calisan_s

START WITH 1

INCREMENT BY 1

MINVALUE 1

NOCACHE

NOCYCLE

NOORDER;

INSERT INTO calisan

VALUES (anil.calisan_s.NEXTVAL,

NULL,

'ANIL',

'ALTUNKAN',

TO_DATE ('30.12.1987', 'dd.mm.rrrr'),

2000,

SYSDATE,

SYSDATE);

INSERT INTO calisan

VALUES (anil.calisan_s.NEXTVAL,

NULL,

'AAA',

TO_DATE ('12.04.1988', 'dd.mm.rrrr'),

1750,

SYSDATE,

SYSDATE);

INSERT INTO calisan

VALUES (anil.calisan_s.NEXTVAL,

NULL,

'BBB',

TO_DATE ('11.02.1990', 'dd.mm.rrrr'),

1750,

SYSDATE,

SYSDATE);

INSERT INTO calisan

VALUES (anil.calisan_s.NEXTVAL,

NULL,

'CCC',

TO_DATE ('06.04.1988', 'dd.mm.rrrr'),

1750,

SYSDATE,

SYSDATE);

INSERT INTO calisan

VALUES (anil.calisan_s.NEXTVAL,

NULL,

'DDD',

TO_DATE ('08.09.1990', 'dd.mm.rrrr'),

1500,

SYSDATE,

SYSDATE);

INSERT INTO calisan

VALUES (anil.calisan_s.NEXTVAL,

NULL,

'EEE',

TO_DATE ('22.07.1989', 'dd.mm.rrrr'),

1500,

SYSDATE,

SYSDATE);

INSERT INTO calisan

VALUES (anil.calisan_s.NEXTVAL,

NULL,

'FFF',

TO_DATE ('13.08.1991', 'dd.mm.rrrr'),

1500,

SYSDATE,

SYSDATE);

INSERT INTO calisan

VALUES (anil.calisan_s.NEXTVAL,

NULL,

'GGG',

TO_DATE ('05.04.1992', 'dd.mm.rrrr'),

1250,

SYSDATE,

SYSDATE);

INSERT INTO calisan

VALUES (anil.calisan_s.NEXTVAL,

NULL,

'HHH',

TO_DATE ('13.01.1992', 'dd.mm.rrrr'),

1250,

SYSDATE,

SYSDATE);

UPDATE calisan

SET yonetici_id =

(SELECT calisan_id

FROM calisan

WHERE ad = 'ANIL')

WHERE ad IN ('AAA', 'BBB');

UPDATE calisan

SET yonetici_id =

(SELECT calisan_id

FROM calisan

WHERE ad = 'AAA')

WHERE ad IN ('CCC', 'DDD');

UPDATE calisan

SET yonetici_id =

(SELECT calisan_id

FROM calisan

WHERE ad = 'BBB')

WHERE ad IN ('EEE');

UPDATE calisan

SET yonetici_id =

(SELECT calisan_id

FROM calisan

WHERE ad = 'CCC')

WHERE ad IN ('FFF');

UPDATE calisan

SET yonetici_id =

(SELECT calisan_id

FROM calisan

WHERE ad = 'BBB')

WHERE ad IN ('GGG', 'HHH');

Aşağıdaki resimde tablomuza eklediğimiz verinin hiyerarşik göstergesi yer almaktadır.

Çalışan Hiyerarşi

Oracle’ın bize sağladığı analitik fonksiyonlar sayesinde normal sql içerisinde bu hiyerarşiyi özetleme imkanımız mevcuttur. Bu bağlamda aşağıdaki sql keyword, fonksiyon ve kavramlarını anlamak yararlı olacaktır.

start with: Hiyerarşik sorgularda belli bir başlangıç noktası belirlenmelidir. Bu gidiş yönüne göre en alt veya en üst olabilirken, orta bir noktadan da başlamak mümkündür. Bu keyword koşul belirtilerek de kullanılabilir.

connect by: Sorgu içerisinde hangi hiyerarşi ilişkisini belirleyen eşitliktir. Yine start with gibi bu keyword de koşul belirtilerek kullanılabilir.

prior: connect by yaparken eşitliğin iki tarafında da kullanılabilinir. Prior hiyerarşinin yönüne göre her zaman gidilen tarafa doğru olmalıdır.

sys_connect_by_path: Bu fonksiyon select içerisinde yer alan sütunlar gibi kullanılır. İki parametre almaktadır.

connect_by_root: Bu fonksiyon da select içerisinde yer alan sütunlar gibi kullanılır. Bu fonksiyonu kullanırken başlangıç noktası belirtilmez zira kendisi bütün başlangıç noktalarını baz alarak sorguyu getirir.

level: Hiyerarşinin seviyesini çeker. Sütun gibi kullanılır.

Bu kavramları anladıktan sonra aşağıdaki SQL örneklerine geçebiliriz.

Aşağıdaki örnekte alt seviyede yer alan 7 numaralı ID’ye sahip çalışan başlangıç noktası olarak belirlenmiştir. Yani hiyerarşide gidiş yönümüz yukarı doğrudur, dolayısıyla prior keyword’ünün connect by içerisinde yönetici tarafında yer alması gerekir.


SELECT calisan_id,
           yonetici_id,
           ad,
           soyad,
           SUBSTR (SYS_CONNECT_BY_PATH (ad || ' ' || soyad, '->'), 3),
           LEVEL
      FROM calisan
START WITH calisan_id = 7
CONNECT BY PRIOR yonetici_id = calisan_id;

SELECT calisan_id,

yonetici_id,

ad,

soyad,

SUBSTR (SYS_CONNECT_BY_PATH (ad || ' ' || soyad, '->'), 3),

LEVEL

FROM calisan

START WITH calisan_id = 7

CONNECT BY PRIOR yonetici_id = calisan_id;

Sonuç:

Şimdi başlangıç noktamızı en tepeye yani yöneticisi olmayan kişiye 1 numaralı ID’ye sahip çalışana çekiyoruz. Fakat bu sefer connect by yaparken prior’ı yönümüz aşağı doğru olduğu için eşitliğin çalışan tarafına kaydırıyoruz.


    SELECT calisan_id,
           yonetici_id,
           ad,
           soyad,
           SUBSTR (SYS_CONNECT_BY_PATH (ad || ' ' || soyad, '->'), 3),
           LEVEL
      FROM calisan
START WITH calisan_id = 1
CONNECT BY yonetici_id = PRIOR calisan_id;

SELECT calisan_id,

yonetici_id,

ad,

soyad,

SUBSTR (SYS_CONNECT_BY_PATH (ad || ' ' || soyad, '->'), 3),

LEVEL

FROM calisan

START WITH calisan_id = 1

CONNECT BY yonetici_id = PRIOR calisan_id;

Sonuç:

Son olarak connect_by_root örneği için aşağıdaki SQL çalıştırılır. Dikkat edilmesi gereken nokta başlangıç noktası yani start with belirtilmemiştir.


    SELECT calisan_id,
           yonetici_id,
           ad,
           soyad,
           SUBSTR (SYS_CONNECT_BY_PATH (ad || ' ' || soyad, '->'), 3),
           LEVEL,
           CONNECT_BY_ROOT (ad)
      FROM calisan
CONNECT BY yonetici_id = PRIOR calisan_id;

SELECT calisan_id,

yonetici_id,

ad,

soyad,

SUBSTR (SYS_CONNECT_BY_PATH (ad || ' ' || soyad, '->'), 3),

LEVEL,

CONNECT_BY_ROOT (ad)

FROM calisan

CONNECT BY yonetici_id = PRIOR calisan_id;

Sonuç:

Tags: hiyerarşik sorgu, pl/sql, sql

6 Responses

sartuk · April 29, 2014 at 07:25:59 · →

Eline sağlık.
cahit · June 30, 2014 at 12:49:35 · →

Eline sağlık.
cihat · August 21, 2015 at 13:04:28 · →

Eline sağlık.
cigdem · April 29, 2016 at 07:03:35 · →

Teşekkürler. Elinize sağlık.
1. altunkan · April 29, 2016 at 11:20:27 · →
  
  Teşekkürler..
Uğur Sinan · June 20, 2019 at 06:30:14 · →

SUBSTR (SYS_CONNECT_BY_PATH (ad || ‘ ‘ || soyad, ‘->’), 3),
bunu hala anlayamadım.

connect by prior dediğinde önceki satırdaki veriyle bağlıyor heralde. Ben bu level mantığını neden anlayamıyorum çözemedim.

ORACLE SQL HİYERARŞİK SORGU

6 Responses

Leave a Reply Click here to cancel reply.

Visitors

Subscribe to Blog via Email