Oracle Start with keyword

Oracle Start with keyword preface

Designed to document a variety of issues encountered in Oracle usage. I also hope to help people who have the same problems as me.

Start with (tree query)

Problem Description:

In the database, there is a more common design pattern, hierarchical design patterns, specific to the Oracle table, the field features are as follows:

ID, DSC, PID;

Three fields, respectively, the ID of the current identity (primary key), the description of the current identity of the DSC, PID of its parent ID, the more typical example is the state, province, the city of this hierarchical structure;

Provinces belong to the state, so the PID is the national ID, and so on;

create table DEMO (    ID varchar2(10) primary key,    DSC varchar2(100),    PID varchar2(10))--插入几条数据Insert Into DEMO values (‘00001‘, ‘中国‘, ‘-1‘);Insert Into DEMO values (‘00011‘, ‘陕西‘, ‘00001‘);Insert Into DEMO values (‘00012‘, ‘贵州‘, ‘00001‘);Insert Into DEMO values (‘00013‘, ‘河南‘, ‘00001‘);Insert Into DEMO values (‘00111‘, ‘西安‘, ‘00011‘);Insert Into DEMO values (‘00112‘, ‘咸阳‘, ‘00011‘);Insert Into DEMO values (‘00113‘, ‘延安‘, ‘00011‘);

This is a simple tree structure, I generally set the root node PID is 1;

Start with:

Reference Link: http://blog.csdn.net/weiwenhp/article/details/8218091

The basic syntax is as follows:

SELECT ... FROM    + 表名WHERE              + 条件3START WITH         + 条件1CONNECT BY PRIOR   + 条件2--示例Select * From DEMOStart With ID = ‘00001‘Connect By Prior ID = PID

Condition 1: Indicates the node from which to start the lookup, that is, the data to be queried through condition 1, as the starting node (parameter) for subsequent queries.

Of course, you can relax the qualification, such as ID in (' 00001 ', ' 00011 ') to get multiple root nodes, which is more than one tree; In connection relationships, column expressions are allowed in addition to the use of columns.

If you omit the start with

By default, all the data in the tree that satisfies the query criteria is traversed from start to finish, one root at a time, and then the other node information in the tree is traversed.

Condition 2: Is a connection condition in which the previous record is represented with PRIOR, for example, connect by PRIOR ID = pid, meaning that the ID of the previous record is the PID of this record, that is, the father of this record is the previous record. The Connect by clause shows that each row of data is retrieved in a hierarchical order and that the data in the table is linked to a tree-structured relationship.

Prior on one side of the parent node, from the bottom up, on the side of the child node represents from the top down query;

Condition 3: Can not be used in Connect by, the condition of the judgment, equivalent to in the final query results list, then the conditional filtering; Not delete nodes and sub-nodes;

--自底向上Select * From DEMOStart With ID = ‘00113‘Connect By  Prior PID = ID--结果00113   延安  0001100011   陕西  0000100001   中国  -1--自上向下Select * From DEMOStart With ID = ‘00001‘--用 Start Wiht PID = ‘-1‘ 结果不变Connect By  Prior ID = PID--结果00001   中国  -100011   陕西  0000100111   西安  0001100112   咸阳  0001100113   延安  0001100012   贵州  0000100013   河南  00001--Where 删除Select ID, PID, DSCFrom DEMOWHERE ID <> ‘00011‘Start With ID = ‘00001‘Connect By Prior ID =  PID--结果00001   -1      中国00111   00011   西安00112   00011   咸阳00113   00011   延安00012   00001   贵州00013   00001   河南

Here are a few keyword special points:

nocycle keyword, sometimes the data itself is unreasonable will lead to the problem of the cycle, such as the above ID ' 00001 ' recorded ' PID ' also changed to ' 00001 ', there will be a cyclic problem, which is, need to use the nocycle can eliminate the cycle;

Connect by nocycle Prior ID = PID.

Connect_by_isleaf indicates whether the current node is a leaf node

Level represents the hierarchy of the current node, where the hierarchy refers to the beginning of the node from start with query, which is currently at the first level

Select ID, PID, DSC,connect_by_isleaf isLeaf,LEVELFrom DEMOConnect By nocycle Prior ID = PIDStart With ID = ‘00001‘;--结果ID      PID     DSC  isLeaf     LEVEL00001   00001   中国  0           000011   00001   陕西  0           100111   00011   西安  1           200112   00011   咸阳  1           200113   00011   延安  1           200012   00001   贵州  1           100013   00001   河南  1           1

One point to note here is that if you're using a bottom-up approach, the level hierarchy is also bottom-up, such as the 00113 levels 1 00011, and 2, 00001.

Another point: If in the query statement Select ID, PID, DSC, Connect_by_isleaf isleaf, LEVEL-1 level This query way, in the where judging condition, only need to judge level = 1, you can To take out the child nodes of the current query node (because level is also pseudo-column, the need to use a subquery method);

Siblings keyword: It will protect the hierarchy and sort by expre in each level.

Select ID, PID, DSC,connect_by_isleaf,LEVELFrom DEMOStart With ID = ‘00001‘Connect By nocycle Prior ID =  PIDORDER By DSC--结果, 仅贴出部分数据(层级结构被破坏了)00012   00001   贵州  1   200013   00001   河南  1   200011   00001   陕西  0   200111   00011   西安  1   300112   00011   咸阳  1   300113   00011   延安  1   300001   -1      中国  0   1--ORDER SIBLINGS  By DSCSelect ID, PID, DSC,connect_by_isleaf,LEVELFrom DEMOStart With ID = ‘00001‘Connect By nocycle Prior ID =  PIDORDER SIBLINGS  By DSC--结果(Level 层级不变)00001   -1      中国  0   100012   00001   贵州  1   200013   00001   河南  1   200011   00001   陕西  0   200111   00011   西安  1   300112   00011   咸阳  1   300113   00011   延安  1   3

Connect_by_iscycle: There is a loop, will return 1, otherwise return 0

Oracle Start with keyword