Oracle hierarchical SQL 查询应用.docVIP

基本用法 在Oracle中，有个很方便的特性，层次关系子句。其语法如下: hierarchical_query_clause::=[START WITH condition] CONNECT BY condition 利用start with … connect by … 可以实现父子关系的查询，这是层次关系查询的基本用法。 如下例：(SQL1.1) -- SQL 1.1 -- begin 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);end;那么我们可以使用START WITH . . . CONNECT BY . . . 子句将父级地区与孩子地区连接起来，并将其层次等级显示出来。 -- SQL 1.2 --select level,child from hier start with parent is null connect by prior child = parent;LEVEL CHILD---------- --------------------------1 Asia2 China3 Beijing2 Japan3 Osaka3 Tokyo1 Australia2 New South Wales3 Sydney1 Europe2 United Kingdom3 England4 London1 North America2 Canada3 Ontario4 Ottawa4 Toronto2 USA3 California4 Redwood Shores 自从Oracle 9i 开始，就可以通过 SYS_CONNECT_BY_PATH 函数实现将从父节点到当前行内容以“path”或者层次元素列表的形式显示出来。 如下例所示：(SQL1.3)-- SQL 1.3 --select level,sys_connect_by_path(child,/) path from hier start with parent is null connect by prior child = parent;LEVEL PATH -------- --------------------------------------------1 /Asia2 /Asia/China3 /Asia/China/Beijing2 /Asia/Japan3 /Asia/Japan/Osaka3 /Asia/Japa