我们可以通过START WITH . . . CONNECT BY . . .子句来实现SQL的 层次查询,而Oracle 10g 为其添加许多了新的伪列。十多年以来,Oracle SQL 具有依照层次关系进行查询的功能。例如,你可以指定一个起始条件,然后根据一个或多个连接条件来确定孩子行的内容。举例来说,现在假设我有一个表,里面记录了世界上的某些地区,其表结构如下:
create table hier ( parent varchar2(30), child varchar2(30) ); insert into hier values(null,''Asia''); insert into hier values(null,''Australia''); insert into hier values(null,''Europe''); insert into hier values(null,''North America''); insert into hier values(''Asia'',''China''); insert into hier values(''Asia'',''Japan''); insert into hier values(''Australia'',''New South Wales''); insert into hier values(''New South Wales'',''Sydney''); insert into hier values(''California'',''Redwood Shores''); insert into hier values(''Canada'',''Ontario''); insert into hier values(''China'',''Beijing''); insert into hier values(''England'',''London''); insert into hier values(''Europe'',''United Kingdom''); insert into hier values(''Japan'',''Osaka''); insert into hier values(''Japan'',''Tokyo''); insert into hier values(''North America'',''Canada''); insert into hier values(''North America'',''USA''); insert into hier values(''Ontario'',''Ottawa''); insert into hier values(''Ontario'',''Toronto''); insert into hier values(''USA'',''California''); insert into hier values(''United Kingdom'',''England''); |
那么我们可以使用START WITH . . . CONNECT BY . . .从句将父级地区与孩子地区连接起来,并将其层次等级显示出来。
column child format a40 select level,lpad('' '',level*3)||child child from hier start with parent is null connect by prior child = parent; LEVEL CHILD ---------- -------------------------- 1 Asia 2 China 3 Beijing 2 Japan 3 Osaka 3 Tokyo 1 Australia 2 New South Wales 3 Sydney 1 Europe 2 United Kingdom 3 England 4 London 1 North America 2 Canada 3 Ontario 4 Ottawa 4 Toronto 2 USA 3 California 4 Redwood Shores |
自从Since Oracle 9i 开始,就可以通过 SYS_CONNECT_BY_PATH 函数实现将从父节点到当前行内容以“path”或者层次元素列表的形式显示出来。 如下例所示:
column path format a50 select level,sys_connect_by_path(child,''/'') path from hier start with parent is null connect by prior child = parent; LEVEL PATH -------- -------------------------------------------- 1 /Asia 2 /Asia/China 3 /Asia/China/Beijing 2 /Asia/Japan 3 /Asia/Japan/Osaka 3 /Asia/Japan/Tokyo 1 /Australia 2 /Australia/New South Wales 3 /Australia/New South Wales/Sydney 1 /Europe 2 /Europe/United Kingdom 3 /Europe/United Kingdom/England 4 /Europe/United Kingdom/England/London 1 /North America 2 /North America/Canada 3 /North America/Canada/Ontario 4 /North America/Canada/Ontario/Ottawa 4 /North America/Canada/Ontario/Toronto 2 /North America/USA 3 /North America/USA/California 4 /North America/USA/California/Redwood Shores |
select connect_by_isleaf,sys_connect_by_path(child,''/'') path from hier start with parent is null connect by prior child = parent; CONNECT_BY_ISLEAF PATH ---------------------------------- 0 /Asia 0 /Asia/China 1 /Asia/China/Beijing 0 /Asia/Japan 1 /Asia/Japan/Osaka 1 /Asia/Japan/Tokyo 0 /Australia 0 /Australia/New South Wales 1 /Australia/New South Wales/Sydney 0 /Europe 0 /Europe/United Kingdom 0 /Europe/United Kingdom/England 1 /Europe/United Kingdom/England/London 0 /North America 0 /North America/Canada 0 /North America/Canada/Ontario 1 /North America/Canada/Ontario/Ottawa 1 /North America/Canada/Ontario/Toronto 0 /North America/USA 0 /North America/USA/California 1 /North America/USA/California/Redwood Shores |