oracle表关联层级树查询

select level
,lpad(' ', (level - 1) * 20, ' ') || t.fk_table as node_name --节点名称（带缩进）
,t.fk_name
,t.fk_table
,t.pk_name
,t.pk_table
from (select null as fk_name
,uta.table_name as fk_table
,null as pk_name
,null as pk_table
from user_tables uta
where not exists (select 1
from user_constraints uc
where uc.constraint_type = 'R'
and uc.table_name = uta.table_name)
union all
select a.constraint_name as fk_name
,a.table_name as fk_table
,b.constraint_name as pk_name
,b.table_name as pk_table
from user_constraints a
,user_constraints b
where a.constraint_type = 'R'
and b.constraint_type = 'P'
and a.r_constraint_name = b.constraint_name) t
start with t.pk_table is null
connect by prior t.fk_table = t.pk_table
--
;

从父节点查询子节点
start with t.pk_table is null
connect by prior t.fk_table = t.pk_table

从子节点查询父节点
start with t.fk_table = 'ASSIGN'
connect by prior t.pk_table = t.fk_table


--alias as child and parent

select level
,lpad(' ', (level - 1) * 20, ' ') || t.child_table as node_name --节点名称（带缩进）
-- ,t.node_name
,t.child_table
-- ,t.pk_name
,t.parent_table
from (select null as node_name
,uta.table_name as child_table
,null as pk_name
,null as parent_table
from user_tables uta
where not exists (select 1
from user_constraints uc
where uc.constraint_type = 'R'
and uc.table_name = uta.table_name)
union all
select a.constraint_name as node_name
,a.table_name as child_table
,b.constraint_name as pk_name
,b.table_name as parent_table
from user_constraints a
,user_constraints b
where a.constraint_type = 'R'
and b.constraint_type = 'P'
and a.r_constraint_name = b.constraint_name) t
start with t.parent_table is null
connect by prior t.child_table = t.parent_table
--
;