Connect by prior start with statement detailed

Through start with ... CONNECT by ... clause to implement the SQL hierarchy query.
Since Oracle 9i, the parent node can be displayed in the form of "PATH" or a list of hierarchical elements through the Sys_connect_by_path function implementation.

Since Oracle 10g, there are a number of other new features for hierarchical queries. For example, there are times when users are more concerned with the lowest level of content in each hierarchy branch.
Then you can use the pseudo-column function connect_by_isleaf to determine whether the current row is a leaf. If it is a leaf, it will display "1" in the pseudo column,
"0" is displayed if it is not a leaf but a branch (for example, if the current content is the father of another row).

In previous versions of Oracle 10g, if a circular loop occurs in your tree (such as a child node referencing a parent node),
Oracle will quote an error message: "Ora-01436:connect by loop in user data." The query operation cannot be performed without deleting a reference to the father.
In Oracle 10g, arbitrary query operations can be performed as long as the "Nocycle" is specified. There is also a pseudo-column--connect_by_iscycle associated with this keyword,
If the contents of a parent node are referenced in the current row and a loop occurs in the tree, "1" is displayed in the pseudo column of the row, or "0" is displayed.

The start with ... Connect BY clause can is 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 a SQL execution plan is explained.

Syntax
Select ... [Start with initial-condition] Connect by [nocycle] Recurse-condition

Level
With level it's possible to show the level in the hierarchical relation of the all data.

--oracle 9i
Sys_connect_by_path
With Sys_connect_by_path it are 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 this comes with Oracle 10g and enhances the ability to perform hierarchical.
Connect_by_is_leaf
Connect_by_isleaf is a new operator this comes with Oracle 10g and enhances the ability to perform hierarchical.
Connect_by_iscycle
Connect_by_is_cycle is a new operator this comes with Oracle 10g and enhances the ability to perform hierarchical.

--start with ... connect by ... The processing mechanism
How must a-start with ... Connect by SELECT statement is read and interpreted?
If Oracle encounters such a SQL statement, it proceeds as described in the following 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 test tables, increase test data

CREATE TABLE Test (Superid varchar2 (), 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;

--Give two examples previously in the "Add four of database strings" to understand the start with ... connect by ...
--function: implementation according to Superid group, the ID with ";" Link up
--Implementation: The following two examples are constructed with 2 pseudo columns to implement the 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 enables you to add the numbers on each bit of an integer, and we understand the connect by again.

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;


/**********************************************************************************
***********************************************************************************
The following is a problem with the SQL solution to the graph, and in this case the author mentions the error
SELECT * from fares connect by prior arrive = Depart start with depart = ' LHR ';
ERROR:
Ora-01436:connect by loop in user data
The above version of oracle10g can be solved by using the nocycle parameter of connect by. Interested friends study using a SQL to achieve a graph problem!
***********************************************************************************
**********************************************************************************/

A common advanced computer science problem can be described in the context of a "direction map". A graph is a limited set of nodes connected by a set of vectors and edges.
For example, a node can be imagined as a "city", and each vector can be imagined as a "route" between two cities.
There are many algorithms and papers on how to solve each possible route traversal problem and find the shortest path or the minimum cost path.
Most of these algorithms are procedural, or are solved using recursion. However, the declarative language of SQL makes it easier to solve complex problem-solving problems,
And you don't need a lot of code.

Let's take the route between two cities as an example, create a table to hold 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);

You cannot use the Connect by syntax to resolve how to get from London to Sao Paulo because there is data in the diagram that produces a loop (fly back from Sao Paulo):

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 with the graph, we need to create a temporary table to hold all the possible paths between the two nodes. We must be careful not to replicate the paths that have been processed,
And in this case, we don't want the path to go back to the same place at the beginning. I would also like to track the number of voyages to the destination and the description of the route taken.

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 simplify the code used in this example slightly. A view can compute a path from the Faretemp table based on a single voyage in the Fares table
To arrive at a voyage 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 quite simple. First, use the data in the fares table to populate the Faretemp table as the initial voyage. And then, taking all the data we just inserted,
Use them to establish all possible two voyage (Two-hop) paths. Repeat this process until a new path is created between the two nodes.
The loop process exits after all possible paths between the nodes are described. If we are only interested in a certain starting condition,
Then we can also limit the number of loading data by limiting the first insertion. Here is the code for the discovery path:

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.

There is a small problem with the previous data. Data is a collection of the shortest path (minimum number of voyages) between points. However, the voyage from London to São Paulo was not the cheapest.

To solve the cheapest cost problem, we need to improve our cycle by using this route instead of the original route when discovering a cheaper route on a voyage.
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;

You may see the output in table B.

The algorithm found that LHR, JFK, GRU route is cheaper than LHR, GRU route, so replace the latter with the former. The loop will be at no cheaper cost,
And there is no other possible route to exit.