Tuesday, March 5, 2013

How to use Connect by oracle


first create table


create table node(
em varchar(100),
ma varchar(100)
)


insert into node (em, ma) values(upper('a'), null)
insert into node (em, ma) values(upper('b'), upper('a'))
insert into node (em, ma) values(upper('c'), upper('a'))
insert into node (em, ma) values(upper('d'), upper('c'))
insert into node (em, ma) values(upper('e'), upper('c'))


commit;

select * from node




select level, lpad(' ', 4 * (level-1)) || em 사원, ma 관리자, connect_by_isleaf isleaf
from node
start with ma is null
connect by prior em = ma

select connect_by_root em 루트사원, sys_connect_by_path(em, '/') 경로, em, ma
from node
start with ma is null
connect by prior em = ma




No comments:

Post a Comment