Simple usage database study notes ---connect by

Recommended for you: Get network issues from WhatsUp Gold. Not end users.
Simple use of connect by
Technology of QQ communication group: JavaDream: 251572072
Tutorial download, online communication: a dream of the IT community: www.credream.com
------------------------------------------------------------
1.
One, first of all from the query sequence is a classic example of the number:
select level from dual connect by level <= 10


select level from dual connect by 1 = 1
The query sequence can be obtained. The N 1 (but up to 100 lines)

We analyze the working principle, level<=10 is used to control the number of circulating, namely to repeat many times a table scan the contents of the dual. The first scan the result set of the level is 1, the second scan result set of the level is 2, and so on. Can describe in words is not easy to understand, we adopted the following tests show:

with x as
( select 'aa' chr from dual
union all
select 'bb' chr from dual)
select level ,chr,lpad( ' ' ,( level - 1 )* 5 , '-' )||chr other from x connect by level <= 3

LEVEL CHR OTHER
1 aa aa
2 aa ---- aa
3 aa --------- aa
3 bb --------- bb
2 bb ---- bb
3 aa --------- aa
3 bb --------- bb
1 bb bb
2 aa ---- aa
3 aa --------- aa
3 bb --------- bb
2 bb ---- bb
3 aa --------- aa
3 bb --------- bb
The visible is a tree structure of all level, when scanning the object is a dual, a level is generated only a record.

Two, how to solve the from dual show only 100 lines of the problem:
selectlevel from dual connectbylevel <=300
Show only 100 lines, but they just said that 9i display problems, the solution is as follows:
select * from (level from dual connectbylevel <=300)
You can display 300 lines! In order to solve the problem of unable to display multiple lines

Application examples are as follows:
select to_date( 2008 || '0101' , 'yyyymmdd' ) + rownum - 1 rq,
to_char(to_date( 2008 || '0101' , 'yyyymmdd' ) + rownum - 1 , 'day' ) day
from ( select rownum from dual
connect by rownum <= to_date( 2008 || '1231' , 'yyyymmdd' ) - to_date( 2008 || '0101' , 'yyyymmdd' )+ 1 );
Effect: list all the date and day of the week, can be used to query the working day




Three, the start with connect by to explain the usage...:
Build the following table:
ID NAME PID
1 10 0
2 11 1
3 20 0
4 12 1
5 121 2
code example1:

select TBL_TEST.*, level from TBL_TEST
Start with pid= 1 - connect by wrote back
connect by prior pid = id
ID NAME PID LEVEL
2 11 1 1
1 10 0 2
4 12 1 1
1 10 0 2
code example2:

select TBL_TEST.*, level from TBL_TEST
Start with id = 5, connect by wrote back
connect by prior pid = id
ID NAME PID LEVEL
5 121 2 1
2 11 1 2
1 10 0 3


Explain:
1, Start start with pid=1 query =====> level 21111; 1,
2, According to PID = ID, id=1 query sentence, 11002 =====> Level 2,
3, According to PID = ID, id=0 query sentence, not the query to after the end of the branch,
Note: prior PID = ID saying that PID is the parent node of the ID, through the PID query ID

Four, explain the function of sys_connect_by_path:
The sys_connect_by_path function main role is to put all the child nodes of a parent node is distinguished by a character, in a display;
select level ,sys_connect_by_path( id , '/' ) from TBL_TEST
start with pid= 1
connect by prior pid = id ;

select level ,sys_connect_by_path(pid, '/' ) from TBL_TEST
start with pid= 1
connect by prior pid = id ;

The difference between the operating results can be compared to the two section of code with code example1 results, can understand this function.

Or look at the specific example of the use of sys_connect_by_path




==============================================================================================

Some data of the Internet, from yangtingkun

Start with connect by SQL... Looking forward to new solutions to the problem of drawing

/*******************************************************************************
By START WITH. CONNECT BY. To realize the SQL hierarchical query clause.
Since the Oracle 9i began, through the SYS_CONNECT_BY_PATH function will be the parent node to the current line to "path" or level of element of a list is displayed in the form of.
Since Oracle 10g, there are other more about the new characteristics of hierarchical query. For example, sometimes the user is more concerned with the minimum grade level in each branch content.
Then you can judge the current line is not leaves using pseudo column function CONNECT_BY_ISLEAF. If the leaves will be displayed on the pseudo column"1",
If the leaves are not but a branch (such as the current content is other's father) show"0".
In Oracle previous versions of 10G, if the ring cycle appeared in your tree (such as a child node references a father node),
Oracle will report an error: "ORA-01436: CONNECT BY loop in user data". If you do not delete the reference to father cannot execute the query operation.
In Oracle 10g, as long as the designated "NOCYCLE" can be arbitrary queries. There is also a pseudo columns associated with the keyword-CONNECT_BY_ISCYCLE,
If the current line is referenced in a father node content and in the tree appeared in a cycle, then the pseudo column in display "1", or display"0".
The start with .. connect by clause can be used to select data that has a hierarchical relationship
(usually some sort of parent->child, boss->employee or thing->parts).
It is also being used when an sql execution plan is explained.
syntax:
select ... [start with initial-condition] connect by [nocycle] recurse-condition
level
With level it is possible to show the level in the hierarchical relation of all the data.
--oracle 9i
sys_connect_by_path
With sys_connect_by_path it is possible to show the entire path from the top level down to the 'actual' child.
--oracle 10g
connect_by_root
connect_by_root is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
connect_by_is_leaf
connect_by_isleaf is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
connect_by_iscycle
connect_by_is_cycle is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
Processing mechanism of --start with... Connect by... '
How must a start with ... connect by select statement be read and interpreted?
If Oracle encounters such an SQL statement, it proceeds as described in the following pseude code.
for rec in (select * from some_table) loop
if FULLFILLS_START_WITH_CONDITION(rec) then
RECURSE(rec, rec.child);
end if;
end loop;
procedure RECURSE (rec in MATCHES_SELECT_STMT, new_parent IN field_type) is
begin
APPEND_RESULT_LIST(rec);
for rec_recurse in (select * from some_table) loop
if FULLFILLS_CONNECT_BY_CONDITION(rec_recurse.child, new_parent) then
RECURSE(rec_recurse,rec_recurse.child);
end if;
end loop;
end procedure RECURSE;
created by zhouwf0726 2006.
*******************************************************************************/
- create table test, increase the test data
create table test(superid varchar2(20),id varchar2(20));
insert into test values('0','1');
insert into test values('0','2');
insert into test values('1','11');
insert into test values('1','12');
insert into test values('2','21');
insert into test values('2','22');
insert into test values('11','111');
insert into test values('11','112');
insert into test values('12','121');
insert into test values('12','122');
insert into test values('21','211');
insert into test values('21','212');
insert into test values('22','221');
insert into test values('22','222');
commit;
Hierarchical query example --
select level||'Layer',lpad(' ',level*5)||id id
from test
start with superid = '0' connect by prior id=superid;
select level||'Layer',connect_by_isleaf,lpad(' ',level*5)||id id
from test
start with superid = '0' connect by prior id=superid;
- gives two before " the database string group added four " in the example to understand start with... Connect by...
- function: to achieve group by superid, using ID; " " link up
-- to achieve: the following two examples are constructed by 2 pseudo columns to achieve connect by connection.
/*------method one------*/
select superid,ltrim(max(sys_connect_by_path(id,';')),';') from(
select superid,id,row_number() over(partition by superid order by superid) id1,
row_number() over(order by superid) + dense_rank() over(order by superid) id2
from test
)
start with id1=1 connect by prior id2 = id2 -1
group by superid order by superid;
/*------method two------*/
select distinct superid,ltrim(first_value(id) over(partition by superid order by l desc),';')
from(
select superid,level l,sys_connect_by_path(id,';') id
from(
select superid,id,superid||rownum parent_rn,superid||to_char(rownum-1) rn
from test
)
connect by prior parent_rn = rn
);
The following example: add up the numbers of an integer on each bit, through this example we re understanding of connect by.
create or replace function f_digit_add(innum integer) return number
is
outnum integer;
begin
if innum<0 then
return 0;
end if;
select sum(nm) into outnum from(
select substr(innum,rownum,1) nm from dual connect by rownum<length(innum)
);
return outnum;
end f_digit_add;
/
select f_digit_add(123456) from dual;

/**********************************************************************************
***********************************************************************************
Below is a directed graph problem solving SQL, mentioned in the case of error
select * from fares connect by prior arrive = depart start with depart = 'LHR';
ERROR:
ORA-01436: CONNECT BY loop in user data
In the above Oracle10g can be solved by using the nocycle parameter connect by. Interested friends of a SQL directed graph problem!
***********************************************************************************
**********************************************************************************/
A common problem in "advanced computer science can be described under the map to" category. A directed graph is a set of nodes connected by a set of finite vector and edge.
For example, a node may be thought of as a "city", and each vector can be thought of as a "route two city.".
There are many papers about algorithms and how to solve every possible path traversal problem and search for the shortest path and minimum cost path problem.
These algorithms are mostly procedural, or use recursion to solve. However, SQL declarative language allows to solve complex directed graph problems easier,
And does not require a lot of code.
Let us take between two city route as an example, create a table to save some hypothetical data:
create table airports
(
code char(3) constraint airports_pk primary key,
description varchar2(200)
);
insert into airports values ('LHR','London Heathrow, UK');
insert into airports values ('JFK','New York-Kennedy, USA');
insert into airports values ('GRU','Sao Paulo, Brazil');
create table fares
(
depart char(3),
arrive char(3),
price number,
constraint fares_pk primary key (depart,arrive),
constraint fares_depart_fk foreign key (depart) references airports,
constraint fares_arrive_fk foreign key (arrive) references airports
);
insert into fares values('LHR','JFK',700);
insert into fares values('JFK','GRU',600);
insert into fares values('LHR','GRU',1500);
insert into fares values('GRU','LHR',1600);
Cannot use CONNECT BY syntax to solve the problem of how to get from London to St Paul, because in the picture data to produce a ring (flew back from St Paul):
select * from fares connect by prior arrive = depart start with depart = 'LHR';
ERROR:
ORA-01436: CONNECT BY loop in user data
To solve the problem of directed graph, we need to create a temporary table to store all possible paths between two nodes. We must be careful not to copy the path has been treated,
But in this case, the same place we don't want to go back to the beginning of the path. I also want to track the number required to reach a destination range, and describe the walking route.
Temporary tables are created using the following script:
create global temporary table faretemp
(
depart char(3),
arrive char(3),
hops integer,
route varchar2(30),
price number,
constraint faretemp_pk primary key (depart,arrive)
);
A simple view can be slightly simplified using this example code. The view can be based on a single voyage in the fares table is calculated from a path in the faretemp table.
Arrive at a range of data:
create or replace view nexthop
as
select src.depart,
dst.arrive,
src.hops+1 hops,
src.route||','||dst.arrive route,
src.price + dst.price price
from faretemp src,fares dst
where src.arrive = dst.depart
and dst.arrive != src.depart;
/
show errors;
This algorithm is very simple. First of all, to fill the faretemp table using fares data in the table, as the initial flight. Then, to get all the data we just inserted,
Use them to build all possible two voyage (two-hop) path. This process is repeated, until between two nodes created a new path.
Circulation process will be among the nodes of all possible paths are described after the withdrawal. If we only a start condition of interest,
Then we can constrain the first so as to reduce the amount of data loading. Below is the found path code:
truncate table faretemp;
begin
-- initial connections
insert into faretemp
select depart,arrive,1,depart||','||arrive,price from fares;
while sql%rowcount > 0 loop
insert into faretemp
select depart,arrive,hops,route,price from nexthop
where (depart,arrive)
not in (select depart,arrive from faretemp);
end loop;
end;
/
show errors;
select * from faretemp order by depart,arrive;
You can view the output in table A.
Previous data have a small problem. Data is the point between the shortest path (voyage number set minimum). However, from London to St Paul flight is not the cheapest one.
In order to solve the problem of the cost of the cheapest, need to make an improvement in our cycle, when finding a cheaper route on a voyage when using this route instead of the original route.
The modified code is as follows:
truncate table faretemp;
declare
l_count integer;
begin
-- initial connections
insert into faretemp
select depart,arrive,1,depart||','||arrive,price from fares;
l_count := sql%rowcount;
while l_count > 0 loop
update faretemp
set (hops,route,price) =
(select hops,route,price from nexthop
where depart = faretemp.depart
and arrive = faretemp.arrive)
where (depart,arrive) in
(select depart,arrive from nexthop
where price <faretemp.price);
l_count := sql%rowcount;
insert into faretemp
select depart,arrive,hops,route,price from nexthop
where (depart,arrive)
not in (select depart,arrive from faretemp);
l_count := l_count + sql%rowcount;
end loop;
end;
/
show errors;
select * from faretemp order by depart,arrive;
May see the output in table B.
LHR, JFK, GRU algorithm for finding route cheaper than the LHR, GRU line, so the former can replace the latter. Cycle will be no cheaper costs,
And no other possible routes to exit.
------------------------------------------------------------------------------------------
The SYS_CONNECT_BY_PATH function
Since Since Oracle 9i began, it can be from a parent node to the current content with "path" or hierarchical list of elements form displayed by SYS_CONNECT_BY_PATH function. As shown in the following 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
CONNECT_BY_ISLEAF pseudo columns
In Oracle 10g, there are other more about the new characteristics of hierarchical query. For example, sometimes the user is more concerned with the minimum grade level in each branch content. Then you can judge the current line is not leaves using pseudo column function CONNECT_BY_ISLEAF. If the leaves will in the pseudo column shows "1", if not the leaves but a branch (such as the current content is other's father) will show "0". Gives a about this function uses the example of:
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
CONNECT_BY_ROOT pseudo columns
There is a new operation in the Oracle 10g -- CONNECT_BY_ROOT. It is used in the column is used before a root node returns the current layer. As shown in the examples below, I can show the hierarchical structure of the current row in the data table corresponding to the highest level of the contents of a node.
select connect_by_root ,sys_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
CONNECT_BY_ISCYCLE pseudo columns
In Oracle previous versions of 10G, if the ring cycle appeared in your tree (such as a child node references a father node), Oracle will report an error: " ORA-01436: CONNECT BY loop in user data". If you do not delete the reference to father cannot execute the query operation. In Oracle 10g, as long as the designated "NOCYCLE" can be arbitrary queries. Associated with the keyword and a pseudo column -- CONNECT_BY_ISCYCLE, if the current line is referenced in a father node content and in the tree appeared in a cycle, then the pseudo column in display "1", or display"0". As shown in the following example:
create table hier2
(
parent number,
child number
);
insert into hier2 values(null,1);
insert into hier2 values(1,2);
insert into hier2 values(2,3);
insert into hier2 values(3,1);
select connect_by_iscycle,sys_connect_by_path(child,"/") path
from hier2
start with parent is null
connect by nocycle prior child = parent;
CONNECT_BY_ISCYCLE PATH
------------------ -------
0 /1
0 /1/2
1 /1/2/3
----------------------------------------------
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download

Posted by Jerry at December 01, 2013 - 11:28 PM