试试这个：
WITH brumba(le_vel,root,node,id_name,root_distance) AS (
SELECT 1 as le_vel, id_name as root, null as node, id_name, to_number(null) as root_distance
FROM MY_TREE WHERE parent_id IS NULL
UNION ALL
SELECT b.le_vel + 1, b.root,
CASE WHEN 1 < (
SELECT count(*) FROM MY_TREE t1 WHERE t1.parent_id = t.parent_id
)
THEN t.parent_id ELSE b.node
END,
t.id_name, coalesce(b.root_distance,0)+t.parent_distance
FROM MY_TREE t
JOIN brumba b ON b.id_name = t.parent_id
)
SELECT * FROM brumba
无需使用PL / sql进行“第二种方法”-上面的sql将立即计算所有根节点（parent_id列中为空）的结果。 只需添加一个前缀或者INSERT INTO tablename(col1,col2, ... colN) ...还是CREATE TABLE name AS ...以上面的查询。 上面的演示包含后一个选项的示例CREATE TABLE xxx AS query
602392714
清零编程群