2022年数据库递归查询参考 .pdf

《2022年数据库递归查询参考 .pdf》由会员分享，可在线阅读，更多相关《2022年数据库递归查询参考 .pdf（7页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、Oracle的递归查询收集的几条在oracle 中通过 connect by prior 来实现递归查询Start with.Connect By子句递归查询一般用于一个表维护树形结构的应用。创建示例表：CREATE TABLE TBL_TEST(ID NUMBER,NAME VARCH AR2(100 BYTE),PID NUMBER DEFAULT 0);插入测试数据：INSERT INTO TBL_TEST(ID,NAME,PID)VALUES(1,10,0);INSERT INTO TBL_TEST(ID,NAME,PID)VALUES(2,11,1);INSERT INTO TBL_

2、TEST(ID,NAME,PID)VALUES(3,20,0);INSERT INTO TBL_TEST(ID,NAME,PID)VALUES(4,12,1);INSERT INTO TBL_TEST(ID,NAME,PID)VALUES(5,121,2);从 Root 往树末梢递归select*from TBL_TEST start with id=1 connect by prior id=pid从末梢往树ROOT 递归select*from TBL_TEST start with id=5 connect by prior pid=id=有一张表t 字段：parent child 两个字

3、段的关系是父子关系写一个 sql 语句，查询出指定父下面的所有的子比如a b a c a e b b1 b b2 名师资料总结-精品资料欢迎下载-名师精心整理-第 1 页，共 7 页 -c c1 e e1 e e3 d d1 指定 parent=a，选出a b a c a e b b1 b b2 c c1 e e1 e e3 SQL 语句：select parent,child from test start with parent=a connect by prior child=parent=connect by是结构化查询中用到的，其基本语法是：select.from tablename

4、 start by cond1 connect by cond2 where cond3;简单说来是将一个树状结构存储在一张表里，比如一个表中存在两个字段:id,parentid那么通过表示每一条记录的parent 是谁，就可以形成一个树状结构。用上述语法的查询可以取得这棵树的所有记录。其中 COND1是根结点的限定语句，当然可以放宽限定条件，以取得多个根结点，实际就是多棵树。COND2 是连接条件，其中用PRIOR 表示上一条记录，比如CONNECT BY PRIOR ID=PRAENTID就是说上一条记录的ID 是本条记录的PRAENTID，即本记录的父亲是上一条记录。COND3 是过滤条

5、件，用于对返回的所有记录进行过滤。PRIOR 和 START W ITH 关键字是可选项PRIORY运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系，PRIOR 运算符在一侧表示父节点，在另一侧表示子节点，从而确定查找树结构是的顺序是自顶向下还是自底向上。在连接关系中，除了可以使用列名外，还允许使用列表达式。START WITH子句为可选项，用来标识哪个节点作为查找树型结构的根节点。若该子句被省略，则表示所有满足查询条件的行作为根节点。完整的例子如SELECT PID,ID,NAME FROM T_WF_ENG_WFKIND START WITH PID=0 CONNECT

6、BY PRIOR ID=PID以上主要是针对上层对下层的顺向递归查询而使用start with.connect by prior.这种方式,但有时在需求需要的时候,可能会需要由下层向上层的逆向递归查询,此是语句就有所变化:例如要实现select*from table where id in(0,01,0101,0203,0304);现在想把 0304 的上一级 03 给递归出名师资料总结-精品资料欢迎下载-名师精心整理-第 2 页，共 7 页 -来,0203 的上一级 02 给递归出来,而01现在已经是存在的,最高层为 0.而这张 table不仅仅这些数据,但我 现 在 只 需 要(0,01,

7、0101,0203,0304,02,03)这 些 数 据,此 时 语 句 可 以 这 样 写SELECT PID,ID,NAME FROM V_WF_WFKIND_TREE WHERE ID IN(SELECT DISTINCT(ID)ID FROM V_WF_WFKIND_TREE CONNECT BYPRIOR PID=ID START WITH ID IN(0,01,0101,0203,0304);其中 START W ITH ID IN里面的值也可以替换SELECT 子查询语句.注意由上层向下层递归与下层向上层递归的区别在于START W ITH.CONNECT BY PRIOR.的先

8、后顺序以及ID=PID 和 PID=ID 的微小变化!=connect by prior start with 经常会被用到一个表中存在递归关系的时候。比如我们经常会将一个比较复杂的目录树存储到一个表中。或者将一些部门存储到一个表中，而这些部门互相有隶属关系。这个时候你就会用到connect by prior start with。典型的使用方法就是：select*from table connect by prior cur_id=parent_id start with cur_id=?例如：a b1 0 2 1 3 1 4 2 5 3如果想查找 a=2 及其下面的所有数据，则：selec

9、t*from table connect by prior a=b start with a=2 a b 2 1 4 2这些只是基础，皮毛。其实只要你灵活的构造查询语句。可以得出意想不到的结果。比如生成树每一个路径。但是这些记录组成的树必须正常才可以。如果有互为父子的情况，就会出现循环错误！select*from tb_cus_area_cde-子取父select*from tb_cus_area_cde a CONNECT BY PRIOR a.c_snr_area=a.c_area_cde START WITH a.c_area_cde=1040101-父取子select*from tb_

10、cus_area_cde a CONNECT BY PRIOR a.c_area_cde=a.c_snr_area START WITH a.c_snr_area is null注意：在用这个函数的时候，statement 的参数要用ResultSet.TYPE_SCROLL_INSENSITIVE 而不能用ResultSet.TYPE_SCROLL_SENSITIVE,在这里再把这两个之间的区别讲讲：1.TYPE_FORWORD_ONLY,只可向前滚动；名师资料总结-精品资料欢迎下载-名师精心整理-第 3 页，共 7 页 -2.TYPE_SCROLL_INSENSITIVE,双向滚动，但不及

11、时更新，就是如果数据库里的数据修改过，并不在 ResultSet 中反应出来。3.TYPE_SCROLL_SENSITIVE，双向滚动，并及时跟踪数据库的更新,以便更改 ResultSet 中的数据=10g 树形查询特性CONNECT_BY_ISCYCLE在10g 中 Oracle 提供了新的伪列：CONNECT_BY _ISCYCLE，通过这个伪列，可以判断是否在树形查询的过程中构成了循环，这个伪列只是在CONNECT BY NOCYCLE 方式下有效。这一篇描述一下解决问题的思路。CONNECT_BY _ISCYCLE的 实 现 和 前 面 两 篇 文 章 中CONNECT_BY _ROO

12、T和CONNECT_BY _ISLEAF的实现完全不同。因为要实现 CONNECT_BY _ISCYCLE，就必须先实现CONNECT BY NOCY CLE，而在 9i 中是没有方法实现这个功能的。也就是说，首先要实现自己的树形查询的功能，而仅这第一点，就是一个异常困难的问题，何况后面还要实现NOCYCLE，最后再加上一个ISCYCLE 的判断。所以总的来说，这个功能的实现比前面两个功能要复杂得多。由于树形查询的LEVEL 是不固定的，所以采用链接的方式实现，基本上是不现实的。换句话说，用纯SQL 的方式来实现树形查询的功能基本上不可行。而为了解决这个功能，只能通过PL/SQL 配合 SQL

13、 来实现。仍然是首先构造一个例子：SQL CREATE TABLE T_TREE(ID NUMBER,FATHER_ID NUMBER,NAME VARCHAR2(30);表已创建。SQL INSERT INTO T_TREE VALUES(1,0,A);已创建 1 行。SQL INSERT INTO T_TREE VALUES(2,1,BC);已创建 1 行。SQL INSERT INTO T_TREE VALUES(3,1,DE);已创建 1 行。SQL INSERT INTO T_TREE VALUES(4,1,FG);已创建 1 行。SQL INSERT INTO T_TREE VAL

14、UES(5,2,HIJ);已创建 1 行。SQL INSERT INTO T_TREE VALUES(6,4,KLM);已创建 1 行。SQL INSERT INTO T_TREE VALUES(7,6,NOPQ);已创建 1 行。SQL INSERT INTO T_TREE VALUES(0,0,ROOT);已创建 1 行。SQL INSERT INTO T_TREE VALUES(4,7,FG);已创建 1 行。SQL COMMIT;提交完成。SQL SELECT*FROM T_TREE;名师资料总结-精品资料欢迎下载-名师精心整理-第 4 页，共 7 页 -ID FATHER_ID NA

15、ME-1 0 A 2 1 BC 3 1 DE 4 1 FG 5 2 H IJ 6 4 KLM 7 6 NOPQ 0 0 ROOT 4 7 FG 已选择 9行。上面构造了两种树形查询循环的情况，一种是当前记录的自循环，另一种是树形查询的某个子节点是当前节点的祖先节点，从而构成了循环。在这个例子中，记录ID 为0和 ID 为4且 FATHER_ID等于7的两条记录分别构成了上述的两种循环的情况。下面就来看看CONNECT_BY_ISCYCLE和 CONNECT BY NOCYCLE 的功能：SQL SELECT*2 FROM T_TREE 3 START W ITH ID=0 4 CONNECT

16、BY PRIOR ID=FATHER_ID;ERROR:ORA-01436:用户数据中的CONNECT BY循环未选定行SQL SELECT*2 FROM T_TREE 3 START W ITH ID=1 4 CONNECT BY PRIOR ID=FATHER_ID;ERROR:ORA-01436:用户数据中的CONNECT BY循环未选定行这就是不使用CONNECT BY NOCYCLE的情况，查询会报错，指出树形查询中出现循环，在10g中可以使用CONNECT BY NOCYCLE的方式来避免错误的产生：SQL SELECT*2 FROM T_TREE 3 START W ITH ID

17、=0 4 CONNECT BY NOCYCLE PRIOR ID=FATHER_ID;ID FATHER_ID NAME-0 0 ROOT 1 0 A 2 1 BC 5 2 H IJ 3 1 DE 名师资料总结-精品资料欢迎下载-名师精心整理-第 5 页，共 7 页 -4 1 FG 6 4 KLM 7 6 NOPQ 已选择 8行。使用 CONNECT BY NOCY CLE，Oracle 自动避免循环的产生，将不产生循环的数据查询出来，下面看看 CONNECT_BY _ISCYCLE 的功能：SQL SELECT ID,2 FATHER_ID,3 NAME,4 CONNECT_BY_ISCYC

18、LE CYCLED 5 FROM T_TREE 6 START W ITH ID=0 7 CONNECT BY NOCYCLE PRIOR ID=FATHER_ID;ID FATHER_ID NAME CYCLED-0 0 ROOT 1 1 0 A 0 2 1 BC 0 5 2 H IJ 0 3 1 DE 0 4 1 FG 0 6 4 KLM 0 7 6 NOPQ 1 已选择 8行。可以看到，CONNECT_BY_ISCYCLE伪列指出循环在树形查询中发生的位置。为了实现 CONNECT_BY _ISCYCLE 就必须先实现CONNECT BY NOCYCLE 方式，而这在9i 中是没有现成的

19、办法的，所以这里尝试使用PL/SQL 来自己实现树形查询的功能。SQL CREATE OR REPLACE FUNCTION F_FIND_CHILD(P_VALUE VARCH AR2)RETURN VARCH AR2 AS 2 V_STR VARCH AR2(32767):=/|P_VALUE;3 4 PROCEDURE P_GET_CHILD_STR(P_FATHER IN VARCH AR2,P_STR IN OUT VARCH AR2)AS 5 BEGIN 6 FOR I IN(SELECT ID FROM T_TREE WHERE FATHER_ID=P_FATHER ANDFAT

20、HER_ID!=ID)LOOP 7 IF INSTR(P_STR|/,/|I.ID|/)=0 THEN 8 P_STR:=P_STR|/|I.ID;9 P_GET_CHILD_STR(I.ID,P_STR);10 END IF;11 END LOOP;12 END;名师资料总结-精品资料欢迎下载-名师精心整理-第 6 页，共 7 页 -13 BEGIN 14 P_GET_CHILD_STR(P_VALUE,V_STR);15 RETURN V_STR;16 END;17/函数已创建。构造一个函数，在函数中递归调用过程来实现树形查询的功能。下面看看调用这个函数的结果：SQL SELECT F_F

21、IND_CHILD(0)FROM DUAL;F_FIND_CHILD(0)-/0/1/2/5/3/4/6/7 SQL SELECT F_FIND_CHILD(2)FROM DUAL;F_FIND_CHILD(2)-/2/5 SQL SELECT F_FIND_CHILD(4)FROM DUAL;F_FIND_CHILD(4)-/4/6/7虽然 目 前存 在 的问 题还 有 很多，但 是 已经 基本 上 实现 了 一个 最简 单 的 NOCYCLE的SYS_CONNECT_BY _PATH 的功能。有了这个函数作为基础，就可以逐步的实现最终的目标了。名师资料总结-精品资料欢迎下载-名师精心整理-第 7 页，共 7 页 -