Connect By Prior

CREATE TABLE TREE
(
NODE_ID NUMBER (20) NOT NULL,
DESCRIPTION VARCHAR2 (255),
MASTER_NODE_ID NUMBER (20) DEFAULT 0 NOT NULL,
ACTIVE NUMBER (1) DEFAULT 1
);

————————————

Insert into TREE
(NODE_ID, DESCRIPTION, MASTER_NODE_ID, ACTIVE)
Values
(1, ‘node 1’, 0, 1);
Insert into TREE
(NODE_ID, DESCRIPTION, MASTER_NODE_ID, ACTIVE)
Values
(2, ‘node 2’, 1, 1);
Insert into TREE
(NODE_ID, DESCRIPTION, MASTER_NODE_ID, ACTIVE)
Values
(3, ‘node 3’, 1, 1);
Insert into TREE
(NODE_ID, DESCRIPTION, MASTER_NODE_ID, ACTIVE)
Values
(4, ‘node 4’, 2, 1);
Insert into TREE
(NODE_ID, DESCRIPTION, MASTER_NODE_ID, ACTIVE)
Values
(5, ‘node 5’, 2, 1);
Insert into TREE
(NODE_ID, DESCRIPTION, MASTER_NODE_ID, ACTIVE)
Values
(6, ‘node 6’, 3, 1);
Insert into TREE
(NODE_ID, DESCRIPTION, MASTER_NODE_ID, ACTIVE)
Values
(7, ‘node 7’, 3, 1);
Insert into TREE
(NODE_ID, DESCRIPTION, MASTER_NODE_ID, ACTIVE)
Values
(8, ‘node 8’, 4, 1);
Insert into TREE
(NODE_ID, DESCRIPTION, MASTER_NODE_ID, ACTIVE)
Values
(9, ‘node 9’, 4, 1);

————————————

SELECT T.ACTIVE,
T.DESCRIPTION,
T.MASTER_NODE_ID,
T.NODE_ID
FROM tree t
WHERE T.ACTIVE = ‘1’
CONNECT BY PRIOR T.NODE_ID = T.MASTER_NODE_ID — Ana düğüm bağlı bütün düğümler olarak düşünülebilir.
START WITH T.MASTER_NODE_ID = 1 — Seçilen düğüm hariç onun altında olan bütün düğüm veya alt düğümleri getirir.

————————————

SELECT T.ACTIVE,
T.DESCRIPTION,
T.MASTER_NODE_ID,
T.NODE_ID
FROM tree t
WHERE T.ACTIVE = ‘1’
CONNECT BY PRIOR T.NODE_ID = T.MASTER_NODE_ID
START WITH T.MASTER_NODE_ID = 2
ORDER SIBLINGS BY T.DESCRIPTION — Her düğümün bağlantılarını teker teker satır haline getirir.

Örn.

25-11-2015 09-42-16

START WITH T.MASTER_NODE_ID = “B” olarak belirmiş olsaydım.

bana satır olarak D,E,H düğümlerine ait satırları getirecekti.

————————————

SELECT T.ACTIVE,
T.DESCRIPTION,
T.MASTER_NODE_ID,
T.NODE_ID,
level
FROM tree t
WHERE T.ACTIVE = ‘1’
CONNECT BY PRIOR T.NODE_ID = T.MASTER_NODE_ID
start with T.MASTER_NODE_ID = 0

*Burada yeni eklenen alan level.

  • Level düğümü derecesini belirtmektedir.

————————————–

SELECT T.ACTIVE,
T.DESCRIPTION,
T.MASTER_NODE_ID,
T.NODE_ID,
level
FROM tree t
WHERE T.ACTIVE = ‘1’
CONNECT BY PRIOR T.NODE_ID = T.MASTER_NODE_ID
start with T.MASTER_NODE_ID = 0
order by level

  • Düğümün derecesine sıralama yapılabilir.

————————————
SELECT T.ACTIVE,
T.DESCRIPTION,
T.MASTER_NODE_ID,
T.NODE_ID,
level
FROM tree t
WHERE T.ACTIVE = ‘1’
and level > 2 — level = 3
CONNECT BY PRIOR T.NODE_ID = T.MASTER_NODE_ID
start with T.MASTER_NODE_ID = 0

  • Düğüm derecesini where koşulunda kullanabilir.
Advertisements

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: