IT Ninja Turtles oracle connect by usage review, Ninja Turtles oracle

IT Ninja Turtles oracle connect by usage review, Ninja Turtles oracle

First use the emp table of scott.
The emp table has one field: empno (employee ID) and mgr (Superior manager ID)
Below are all the data in the table

1	select *from emp startwith empno=7698connect bymgr=prior empno;

The execution result is as follows:

The result is the data of empno = 7698 and the data of mgr = 7698.
It is recursive downward, that is, we traverse from empno = 7698 to find all data S (represented by S) of mgr = 7698 .), then, the empno value in S is matched to find whether there is any satisfied, mgr in (s. empno. Traverse until there is no data.

The following describes the effect in detail.

12	-- Recursive Traversalselect *from empconnect by mgr=prior empno startwith empno=7839;

The execution result is as follows:

12	-- Recursively traverse upselect * from emp connect by prior mgr=empno start with empno=7844;

The execution result is as follows:

So that no matching data exists.
The above is just a simple example.

Connect by is used in structured queries. Its basic syntax is:

1234	select ...from tablenamestartby cond1connect by cond2where cond3

Simply put, a tree structure is stored in a table. For example, a table has two fields (such as the empno and mgr fields in the emp table): empno, mgr then, by indicating who the mgr of each record is, a tree structure can be formed.

You can use the preceding syntax to query all records of the tree.
Where:
Cond1 is a restriction statement for the root node. Of course, you can relax the limitation conditions to obtain multiple root nodes. Actually, it is multiple trees.
Cond2 is the connection condition, where prior represents the previous record. For example, connect by prior id = praentid indicates that the id of the previous record is the praentid of this record, that is, the father of this record is the previous record.
Cond3 is a filter condition used to filter all returned records.
Prior and start with keywords are optional
The prior operator must be placed before one of the two columns of the connection relationship. For the parent-child relationship between nodes, the prior operator represents the parent node on one side and the child node on the other side to determine whether the order of the tree structure is top-down or bottom-up. In addition to column names, you can also use column expressions in the join relationship.
The start with clause is optional to identify the node used as the root node for searching the tree structure. If this clause is omitted, all rows that meet the query conditions are used as the root node.

Usage of oracle connect by and start

Recursive query, which is very effective when the data structure is tree and linked list.
For example, menus and organizations.

How to Implement connect by prior using qbc

Oracle Connect By usage

The select statement in oracle can use the start with... connect by prior clause to implement recursive queries. The connect by clause is used in structured queries. Its basic syntax is as follows:

Select... from <TableName>
Where <Conditional-1>
Start with <Conditional-2>
Connect by <Conditional-3>
;

<Conditional-1>: Filter condition used to filter all returned records.
<Conditional-2>: Conditions for restarting the root node in the query result.
<Conditional-3>: Connection Condition

For example:

The database table structure is as follows:

Create table t2 (
Root_id number,
Id number,
Name varchar (5 ),
Description varchar (10)
);

Insert into t2 (root_id, id, name, description) values (0, 1, 'A', 'aaa ');
Insert into t2 (root_id, id, name, description) values (1, 2, 'a1', 'aaa1 ');
Insert into t2 (root_id, id, name, description) values (1, 3, 'a2 ', 'aaa2 ');
Insert into t2 (root_id, id, name, description) values (0, 4, 'B', 'bbb ');
Insert into t2 (root_id, id, name, description) values (, 'b1 ', 'bbb1 ');
Insert into t2 (root_id, id, name, description) values (, 'b2', 'bbb ');

1. Obtain the complete tree:

Select * from t2;
Select * from t2 start with root_id = 0 connect by prior id = root_id;

2. Get the child tree:
Select * from t2 start with id = 1 connect by prior id = root_id;

Select * from t2 start with id = 4 connect by prior id = root_id;

3. If prior in connect by prior is omitted, the query will not undergo deep recursion.
For example:
Select * from t2 start with root_id = 0 connect by id = root_id;

Select * from t2 start with id = 1 connect by id = root_id;

1) prior: The position placed before and after the equal sign determines the search order during query.
When PRIOR is placed in front of a moderate number in the connect by clause, it is forcibly retrieved from the root node to the leaf node, that is, from the parent node to the rest of the full text>