我们可以通过START WITCNECT BY子句来实现SQL的 层次查询而Oracle g 为其添加许多了新的伪列
我们可以通过START WITH CONNECT BY 子句来实现SQL的 层次查询而Oracle g 为其添加许多了新的伪列十多年以来Oracle SQL 具有依照层次关系进行查询的 功能例如你可以指定一个起始条件然后根据一个或多个连接条件来确定孩子行的内容举例来说现在假设我有一个表里面记录了世界上的某些地区其表结构如下
create table hier
(
parent varchar()
child varchar()
);
insert into hier values(nullAsia);
insert into hier values(nullAustralia);
insert into hier values(nullEurope);
insert into hier values(nullNorth America);
insert into hier values(AsiaChina);
insert into hier values(AsiaJapan);
insert into hier values(AustraliaNew South Wales);
insert into hier values(New South WalesSydney);
insert into hier values(CaliforniaRedwood Shores);
insert into hier values(CanadaOntario);
insert into hier values(ChinaBeijing);
insert into hier values(EnglandLondon);
insert into hier values(EuropeUnited Kingdom);
insert into hier values(JapanOsaka);
insert into hier values(JapanTokyo);
insert into hier values(North AmericaCanada);
insert into hier values(North AmericaUSA);
insert into hier values(OntarioOttawa);
insert into hier values(OntarioToronto);
insert into hier values(USACalifornia);
insert into hier values(United KingdomEngland);
那么我们可以使用START WITH CONNECT BY 从句将父级地区与孩子地区连接起来并将其层次等级显示出来
column child format a
select levellpad( level*)||child child
from hier
start with parent is null
connect by prior child = parent;
LEVEL CHILD
Asia
China
Beijing
Japan
Osaka
Tokyo
Australia
New South Wales
Sydney
Europe
United Kingdom
England
London
North America
Canada
Ontario
Ottawa
Toronto
USA
California
Redwood Shores
自从Since Oracle i 开始就可以通过 SYS_CONNECT_BY_PATH 函数实现将从父节点到当前行内容以path或者层次元素列表的形式显示出来 如下例所示
column path format a
select levelsys_connect_by_path(child/) path
from hier
start with parent is null
connect by prior child = parent;
LEVEL PATH
/Asia
/Asia/China
/Asia/China/Beijing
/Asia/Japan
/Asia/Japan/Osaka
/Asia/Japan/Tokyo
/Australia
/Australia/New South Wales
/Australia/New South Wales/Sydney
/Europe
/Europe/United Kingdom
/Europe/United Kingdom/England
/Europe/United Kingdom/England/London
/North America
/North America/Canada
/North America/Canada/Ontario
/North America/Canada/Ontario/Ottawa
/North America/Canada/Ontario/Toronto
/North America/USA
/North America/USA/California
/North America/USA/California/Redwood Shores
在 Oracle
g 中
还有其他更多关于层次查询的新特性
例如
有的时候用户更关心的是每个层次分支中等级最低的内容
那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子
如果是叶子就会在伪列中显示
如果不是叶子而是一个分支（例如当前内容是其他行的父亲）就显示
下给出了一个关于这个函数使用的例子
select connect_by_isleafsys_connect_by_path(child/) path
from hier
start with parent is null
connect by prior child = parent;
CONNECT_BY_ISLEAF PATH
/Asia
/Asia/China
/Asia/China/Beijing
/Asia/Japan
/Asia/Japan/Osaka
/Asia/Japan/Tokyo
/Australia
/Australia/New South Wales
/Australia/New South Wales/Sydney
/Europe
/Europe/United Kingdom
/Europe/United Kingdom/England
/Europe/United Kingdom/England/London
/North America
/North America/Canada
/North America/Canada/Ontario
/North America/Canada/Ontario/Ottawa
/North America/Canada/Ontario/Toronto
/North America/USA
/North America/USA/California
/North America/USA/California/Redwood Shores
自从Since Oracle
i 开始
就可以通过 SYS_CONNECT_BY_PATH 函数实现将从父节点到当前行内容以
path
或者层次元素列表的形式显示出来
如下例所示
column path format a
select levelsys_connect_by_path(child/) path
from hier
start with parent is null
connect by prior child = parent;
LEVEL PATH
/Asia
/Asia/China
/Asia/China/Beijing
/Asia/Japan
/Asia/Japan/Osaka
/Asia/Japan/Tokyo
/Australia
/Australia/New South Wales
/Australia/New South Wales/Sydney
/Europe
/Europe/United Kingdom
/Europe/United Kingdom/England
/Europe/United Kingdom/England/London
/North America
/North America/Canada
/North America/Canada/Ontario
/North America/Canada/Ontario/Ottawa
/North America/Canada/Ontario/Toronto
/North America/USA
/North America/USA/California
/North America/USA/California/Redwood Shores
在 Oracle g 中还有其他更多关于层次查询的新特性 例如有的时候用户更关心的是每个层次分支中等级最低的内容那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子如果是叶子就会在伪列中显示如果不是叶子而是一个分支（例如当前内容是其他行的父亲）就显示下给出了一个关于这个函数使用的例子
select connect_by_isleafsys_connect_by_path(child/) path
from hier
start with parent is null
connect by prior child = parent;
CONNECT_BY_ISLEAF PATH
/Asia
/Asia/China
/Asia/China/Beijing
/Asia/Japan
/Asia/Japan/Osaka
/Asia/Japan/Tokyo
/Australia
/Australia/New South Wales
/Australia/New South Wales/Sydney
/Europe
/Europe/United Kingdom
/Europe/United Kingdom/England
/Europe/United Kingdom/England/London
/North America
/North America/Canada
/North America/Canada/Ontario
/North America/Canada/Ontario/Ottawa
/North America/Canada/Ontario/Toronto
/North America/USA
/North America/USA/California
/North America/USA/California/Redwood Shores
在Oracle g 中还有一个新操作——CONNECT_BY_ROOT 它用在列名之前用于返回当前层的根节点如下面的例子我可以显示出层次结构表中当前行数据所对应的最高等级节点的内容
select connect_by_root childsys_connect_by_path(child/) path
from hier
start with parent is null
connect by prior child = parent;
CONNECT_BY_ROOT PATH
Asia /Asia
Asia /Asia/China
Asia /Asia/China/Beijing
Asia /Asia/Japan
Asia /Asia/Japan/Osaka
Asia /Asia/Japan/Tokyo
Australia /Australia
Australia /Australia/New South Wales
Australia /Australia/New South Wales/Sydney
Europe /Europe
Europe /Europe/United Kingdom
Europe /Europe/United Kingdom/England
Europe /Europe/United Kingdom/England/London
North America /North America
North America /North America/Canada
North America /North America/Canada/Ontario
North America /North America/Canada/Ontario/Ottawa
North America /North America/Canada/Ontario/Toronto
North America /North America/USA
North America /North America/USA/California
North America /North America/USA/California/Redwood Shores
在Oracle g 之前的版本中如果在你的树中出现了环状循环（如一个孩子节点引用一个父亲节点）Oracle 就会报出一个错误提示 ORA: CONNECT BY loop in user data如果不删掉对父亲的引用就无法执行查询操作而在 Oracle g 中只要指定NOCYCLE就可以进行任意的查询操作与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE 如果在当前行中引用了某个父亲节点的内容并在树中出现了循环那么该行的伪列中就会显示否则就显示如下例所示
create table hier
(
parent number
child number
);
insert into hier values(null);
insert into hier values();
insert into hier values();
insert into hier values();
select connect_by_iscyclesys_connect_by_path(child/) path
from hier
start with parent is null
connect by nocycle prior child = parent;
CONNECT_BY_ISCYCLE PATH
/
//
///
