Oracle10g中层次查询简介

我们可以通过START WITH . . . CONNECT BY . . .子句来实现SQL的 层次查询，而Oracle 10g 为其添加许多了新的伪列。十多年以来，Oracle SQL 具有依照层次关系进行查询的 功能。例如，你可以指定一个起始条件，然后根据一个或多个连接条件来确定孩子行的内容

　　我们可以通过START WITH . . . CONNECT BY . . .子句来实现SQL的 层次查询，而Oracle 10g 为其添加许多了新的伪列。十多年以来，Oracle SQL 具有依照层次关系进行查询的 功能。例如，你可以指定一个起始条件，然后根据一个或多个连接条件来确定孩子行的内容。举例来说，现在假设我有一个表，里面记录了世界上的某些地区，其表结构如下：

create table hier<br><br>(<br><br>parent varchar2(30),<br><br>child varchar2(30)<br><br>);<br><br>insert into hier values(null,'Asia');<br><br>insert into hier values(null,'Australia');<br><br>insert into hier values(null,'Europe');<br><br>insert into hier values(null,'North America');<br><br>insert into hier values('Asia','China');<br><br>insert into hier values('Asia','Japan');<br><br>insert into hier values('Australia','New South Wales');<br><br>insert into hier values('New South Wales','Sydney');<br><br>insert into hier values('California','Redwood Shores');<br><br>insert into hier values('Canada','Ontario');<br><br>insert into hier values('China','Beijing');<br><br>insert into hier values('England','London');<br><br>insert into hier values('Europe','United Kingdom');<br><br>insert into hier values('Japan','Osaka');<br><br>insert into hier values('Japan','Tokyo');<br><br>insert into hier values('North America','Canada');<br><br>insert into hier values('North America','USA');<br><br>insert into hier values('Ontario','Ottawa');<br><br>insert into hier values('Ontario','Toronto');<br><br>insert into hier values('USA','California');<br><br>insert into hier values('United Kingdom','England'); 

　　那么我们可以使用START WITH . . . CONNECT BY . . .从句将父级地区与孩子地区连接起来，并将其层次等级显示出来。

column child format a40<br><br>select level,lpad(' ',level*3)||child child<br><br>from hier<br><br>start with parent is null<br><br>connect by prior child = parent;<br><br>LEVEL CHILD<br><br>---------- --------------------------<br><br>1 Asia<br><br>2 China<br><br>3 Beijing<br><br>2 Japan<br><br>3 Osaka<br><br>3 Tokyo<br><br>1 Australia<br><br>2 New South Wales<br><br>3 Sydney<br><br>1 Europe<br><br>2 United Kingdom<br><br>3 England<br><br>4 London<br><br>1 North America<br><br>2 Canada<br><br>3 Ontario<br><br>4 Ottawa<br><br>4 Toronto<br><br>2 USA<br><br>3 California<br><br>4 Redwood Shores

自从Since Oracle 9i 开始，就可以通过 SYS_CONNECT_BY_PATH 函数实现将从父节点到当前行内容以“path”或者层次元素列表的形式显示出来。 如下例所示：

column path format a50<br><br>select level,sys_connect_by_path(child,'/') path<br><br>from hier<br><br>start with parent is null<br><br>connect by prior child = parent;<br><br>LEVEL PATH <br><br>-------- --------------------------------------------<br><br>1 /Asia<br><br>2 /Asia/China<br><br>3 /Asia/China/Beijing<br><br>2 /Asia/Japan<br><br>3 /Asia/Japan/Osaka<br><br>3 /Asia/Japan/Tokyo<br><br>1 /Australia<br><br>2 /Australia/New South Wales<br><br>3 /Australia/New South Wales/Sydney<br><br>1 /Europe<br><br>2 /Europe/United Kingdom<br><br>3 /Europe/United Kingdom/England<br><br>4 /Europe/United Kingdom/England/London<br><br>1 /North America<br><br>2 /North America/Canada<br><br>3 /North America/Canada/Ontario<br><br>4 /North America/Canada/Ontario/Ottawa<br><br>4 /North America/Canada/Ontario/Toronto<br><br>2 /North America/USA<br><br>3 /North America/USA/California<br><br>4 /North America/USA/California/Redwood Shores 

在 Oracle 10g 中，还有其他更多关于层次查询的新特性 。例如，有的时候用户更关心的是每个层次分支中等级最低的内容。那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如果是叶子就会在伪列中显示“1”，如果不是叶子而是一个分支（例如当前内容是其他行的父亲）就显示“0”。下给出了一个关于这个函数使用的例子：

select connect_by_isleaf,sys_connect_by_path(child,'/') path<br><br>from hier<br><br>start with parent is null<br><br>connect by prior child = parent;<br><br>CONNECT_BY_ISLEAF PATH<br><br>---------------------------------- 

0 /Asia<br><br>0 /Asia/China<br><br>1 /Asia/China/Beijing<br><br>0 /Asia/Japan<br><br>1 /Asia/Japan/Osaka<br><br>1 /Asia/Japan/Tokyo<br><br>0 /Australia<br><br>0 /Australia/New South Wales<br><br>1 /Australia/New South Wales/Sydney<br><br>0 /Europe<br><br>0 /Europe/United Kingdom<br><br>0 /Europe/United Kingdom/England<br><br>1 /Europe/United Kingdom/England/London<br><br>0 /North America<br><br>0 /North America/Canada<br><br>0 /North America/Canada/Ontario<br><br>1 /North America/Canada/Ontario/Ottawa<br><br>1 /North America/Canada/Ontario/Toronto<br><br>0 /North America/USA<br><br>0 /North America/USA/California<br><br>1 /North America/USA/California/Redwood Shores