如何在oracle中使用先验递归算法连接

Oracle 中通过先验递归算法进行连接


Start with...connect by previous 子句在 Oracle 中的用法 connect by 用于结构化查询。基本语法为：


select ... from tablename start with condition 1
connect by condition 2
where condition 3;
示例：< br/>select * from table
start with org_id = 'HBHqfWGWPy'
connect by previous org_id =parent_id;


简单来说就是一个树形结构存储以一个表为例，表中有两个字段：
org_id、parent_id。然后通过指示每条记录的父级是谁，就可以形成一个树结构。
使用上述语法的查询，可以获得这棵树的所有记录。
其中：
条件 1 是对根节点。当然，可以放宽条件，得到多个根节点，实际上是多棵树。
条件2是连接条件，其中PRIOR用于表示前一条记录。例如CONNECT BY PRIOR org_id =parent_id表示前一条记录的org_id是这条记录的parent_id，即这条记录的父亲是前一条记录。
条件3为过滤条件，用于过滤所有返回的记录。


简单介绍如下：
扫描树形结构表时，需要相应地访问树形结构的各个节点。一个节点只能被访问一次。询问步骤如下：
步骤一：从根节点开始；
步骤二：访问该节点；
步骤三：判断是否存在节点有未访问的子节点。如果是，则转到其最左边的未访问过的小节，执行第二步，否则执行第四步；
步骤4：如果该节点是根节点，则访问完成，否则执行步骤5；
步骤5：返回该节点的父节点，执行第三步。


简而言之：扫描整个树结构的过程，也是对树进行中序遍历的过程。


1．树结构的描述
树结构的数据存储在表中。数据之间的层次关系是父子关系，通过表中的列之间的关系来描述，例如EMP表中的EMPNO和MGR。 EMPNO代表员工的编号，MGR代表领导该员工的人的编号。即子节点的MGR值等于父节点的EMPNO值。表的每一行都有一个MGR，代表父节点（根节点除外）。通过每个节点的父节点，就可以确定整个树的结构。
使用 CONNECT BY 和 START WISELECT命令中的TH子句用于查询表中的树形结构关系。命令格式如下：
SELECT。 。 。
CONNECT BY {PRIOR列名1=列名2|列名1=PRIOR分割名2}
[STARTWITH];
其中：CONNECT BY子句表示每行数据将按层次顺序检索并规定将表中的数据连接成树形结构的关系。 PRIORY 运算符必须放置在连接关系的两列之一的前面。对于节点之间的父子关系，PRIOR运算符表示一侧是父节点，另一侧是子节点，从而确定搜索树结构的顺序是自上而下还是自下而上。在连接关系中，除了列名之外，还允许使用列表达式。 STARTWITH子句是可选的，用于标识哪个节点用作搜索树结构的根节点。如果省略该子句d，表示将所有满足查询条件的行作为根节点。
首先：不仅可以指定一个根节点，还可以指定多个根节点。
2.关于PRIOR
Prior与父节点列parentid放在一起，表示沿着父节点的方向遍历； prior是和子节点列subid放在一起的，然后往叶子节点的方向遍历。谁将parentid 和subid 列放在“=”之前并不重要。关键是谁优先。


3.定义搜索起始节点
自上而下查询树结构时，不仅可以从根节点开始，还可以定义任意节点作为起始节点，开始向下搜索。该搜索的结果是从该节点开始的结构树的分支。
4.使用LEVEL
在具有树形结构的表中，每一行数据都是树形结构中的一个节点。由于节点位于不同的层级位置，每行记录可以有一个层级号。层数根据节点与根节点之间的距离确定。无论从哪个节点开始，起始根节点的层数始终为1，根节点的子节点为2，以此类推。
5．节点和分支的修剪
查询树结构时，可以删除表中的某些行或修剪树中的分支。使用 WHERE 子句限制树结构中的单个节点。删除树中的单个节点，但不会影响其后代节点（从上到下检索时）或前驱节点（从下到上检索时）。
6．排序显示
与其他查询一样，也可以在树结构查询中使用ORDER BY子句来改变查询结果的显示顺序，而不必遵循遍历树结构的顺序




7.其他参数参考
如果有错误提示ORA-01436，报这个错误的原因是因为发生了循环，子节点和父节点的数值相同。
在不更改数据的情况下更改以下 SQL 语句，使其不会生成循环
SELECT r1.region_id,parent_id
          FROM cnl_region r1
              WHERE r1.region_id =1   START FOR r1。 Region_id = 1
CONNECT BY NOCYCLE PRIOR r1.region_id = r1.parent_id


CONNECT BY NOCYCLE可以删除递归值而不报错；
CONNECT_BY_ISCYCLE是与 结合使用 作为伪列，显示哪些是重复的；




---CONNECT_BY_ISLEAF=0 将显示下级目录；< br/>--- CONNECT_BY_ISLEAF=1 将显示下级目录；




= ============ ==================================
示例：
SQL> select * from emp;< br/>

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO
----- ---------- --------- ----- ----------- --------- -------- - ------< br/> 7369 史密斯职员 7902 1980/12/17 800.00 20
7499 艾伦推销员 7698 1981/2/20 1600.00 300.00 30
7521 沃德 7698 1981/2/22 1250.00 500.00 30
7566 琼斯经理 7839 1981/4/2 2975.00 20
7654 马丁销售员 7698 1981/9/28 1250.00 1400.00 30
7698 布莱克经理7839 1981 /5/1 2850.00 30< br/> 7782 克拉克经理 7839 1981/6/9 2450.00 10
7839 王总裁 1981/11/17 5000.00 10
7844 特纳业务员 7698 1981/9/8 1500.00 0.0 0 30
7900 JAMES CLERK 7698 1981/12/3 950.00   30
7902 福特分析师 7566 1981/12/3 3000.00 20
7934 MILLER CLERK 7782 1982/1/23 13 00.00         10
7788 斯科特 分析师 7566 1987 /4/19   3000.00           20
7876 ADAMS CLERK 7788 1987/5/23 1100.00 20
已选择 14 行


SQL> col 路径格式 a35
SQL> col level_id 格式 a15
SQL> select e.mgr,e.deptno,e.empno,e.ename,e.job,level,SYS_CONNECT_BY_PATH(job,'/') 路径,lpad(' ',level*2)||empno level_id
2 来自 emp e
3 以 mgr 开头为空
  4 按之前的 empno =mgr 连接
5 按级别排序;


MGR DEPTNO EMPNO ENAME 作业级别路径 LEVEL_ID
----- --- --- ----- --------- - --------- ---------- ------------- ----------------- ----- ---------------
            10  7839 KING       总统          1 /总统                            7839< br/> 7839 20 7566 琼斯经理 2 /总统/男士艾格7566
7839 30 7698 BLAKE 经理​2/总裁/经理 7698
7839 10 7782 Clark 经理 2/总裁/经理 7782
7566 20 7902 福特分析师 3/总裁/分析师 7902
7698 30 75 75 21 病房推销员 3 /总裁/经理/推销员 7521
7698 30 7900 JAMES CLERK 3 /总裁/经理/职员 7900
7782 10 7934 MILLER CLERK 3 /总裁/经理/职员 7934
7698 30 7499 艾伦 推销员 3 /总裁/经理/推销员 7499
7566 20 7788 斯科特 分析师 3 /总裁/经理/分析师 7788
7698 30 7654 MARTIN 推销员 3 /总裁/经理/推销员 7654
7698 30 7844 特纳 推销员 3 /总裁/经理/推销员 7844
7788 20 7876 ADAMS 职员 4 /总裁/经理/分析师/职员 7876
7902 20 7369 SMITH CLERK 4 /PRESIDENT/MANAGER/ANALYST/CLERK 7369
已选择 14 行




SQL> select e.mgr, e.deptno,e.empno,e.ename,e.job,level
2 来自 emp e
3 以 mgr 开头为空
4 通过 empno = 先前的 mgr 连接
5 按级别排序；


MGR DEPTNO EMPNO ENAME JOB LEVEL
----- ------ ----- ------- --- --------- ----------
10 7839 KING PRESIDENT 1


SQL>
< br/>




--存在重复值时报错
WITH
T
AS
(
选择
'JOHN' 员工，
'JACK' 经理
FROM

DUAL
UNION ALL
SELECT
' JACK' 员工，
'JOHN' 经理
来自
DUAL
)
选择
SYS_CONNECT_BY_PATH (EMPLOYEE,'/') 作为路径，
经理
来自
T
CONNECT BY PRIOR EMPLOYEE = MANAGER;


--使用CONNECT BY CYCLE后可以正常显示
WITH
T
AS
(
选择
“JOHN”员工，
 “JACK”经理
FROM
DUAL
UNION ALL
从
DUAL
中选择
'JACK'员工，
'JOHN'经理

SELECT
SYS_CONNECT_BY_PATH(EMPLOYEE,'/ ') 作为路径，
MANAGER
FROM
T
CONNECT BY NOCYCLE PRIOR EMPLOYEE = Manager;




路径管理器
------------------------ -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- -------------------------------------------------- --------------- ------------------------
/约翰p; br/>AS
(
选择
“JOHN”员工，
“JACK”经理
FROM
DUAL
UNION ALL
SELECT
“JACK”员工，
“JOHN”经理
FROM
DUAL
)
SELECT
SYS_CONNECT_BY_PATH (EMPLOYEE,'/' ）作为路径，
MANAGER，
CONNECT_BY_ISCYCLE
FROM
T
CONNECT BY
NOCYCLE
PRIOR EMPLOYEE = MANAGER;
- ----------- ------- ------------------
/约翰                                                                                                                                                                                                致约翰/杰克·约翰 1
/杰克·约翰 0
/jack/约翰·杰克1

以上就是《oracle中如何使用connect byprior递归算法》一文的全部内容，感谢您的阅读！希望分享的内容对大家有所帮助。了解更多相关知识，欢迎关注业内人士y信息频道！