oralce 层次查询

我们可以通过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

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

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