We can use the STARTWITH... CONNECTBY... clause to implement SQL hierarchical queries, while Oracle10g adds many new pseudo columns for it. For more than a decade, OracleSQL has been able to query data based on hierarchies. For example, you can specify a starting condition and determine the content of a child row based on one or more connection conditions.
We can use the start with... connect by... clause to implement SQL hierarchical queries, while Oracle 10g adds many new pseudo columns for it. For more than a decade, Oracle SQL has been able to query data based on hierarchies. For example, you can specify a starting condition and determine the content of a child row based on one or more connection conditions.
We can use the start with... connect by... clause to implement SQL hierarchical queries, while Oracle 10g adds many new pseudo columns for it. For more than a decade, Oracle SQL has been able to query data based on hierarchies. For example, you can specify a starting condition and determine the content of a child row based on one or more connection conditions. For example, if I have a table that records some regions in the world, the table structure is as follows:
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'); |
Then we can use the start with... connect by... clause to CONNECT the parent region WITH the child region and display its level.
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, you can use the SYS_CONNECT_BY_PATH function to display the content from the parent node to the current row in the form of a "path" or a list of hierarchical elements. For example:
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 |
In Oracle 10g, there are other
