求知 文章 文库 Lib 视频 iProcess 课程 角色 咨询 工具 讲座 Modeler   Code  
会员   
 
  
每天15篇文章
不仅获得谋生技能
更可以追随信仰
 
 
     
   
分享到
Oracle的递归
 

作者:T240178168,发布于2012-9-18

 

Oracle的递归查询

收集的几条在oracle中通过connect by prior来实现递归查询

Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。

创建示例表:

CREATE TABLE TBL_TEST

(

ID NUMBER,

NAME VARCHAR2(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_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

两个字段的关系是父子关系

写一个sql语句,查询出指定父下面的所有的子

比如

a b

a c

a e

b b1

b b2

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 start by cond1

connect by cond2

where cond3;

简单说来是将一个树状结构存储在一张表里,比如一个表中存在两个字段:

id,parentid那么通过表示每一条记录的parent是谁,就可以形成一个树状结构。

用上述语法的查询可以取得这棵树的所有记录。

其中COND1是根结点的限定语句,当然可以放宽限定条件,以取得多个根结点,实际就是多棵树。

COND2是连接条件,其中用PRIOR表示上一条记录,比如 CONNECT BY PRIOR ID=PRAENTID就是说上一条记录的ID是本条记录的PRAENTID,即本记录的父亲是上一条记录。

COND3是过滤条件,用于对返回的所有记录进行过滤。

PRIOR和START WITH关键字是可选项

PRIORY运算符必须放置在连接关系的两列中某一个的前面。对于节点间的父子关系,PRIOR 运算符在一侧表示父节点,在另一侧表示子节点,从而确定查找树结构是的顺序是自顶向下还是 自底向上。在连接关系中,除了可以使用列名外,还允许使用列表达式。START WITH 子句为 可选项,用来标识哪个节点作为查找树型结构的根节点。若该子句被省略,则表示所有满足查询 条件的行作为根节点。

完整的例子如SELECT PID,ID,NAME FROM T_WF_ENG_WFKIND START WITH PID =0 CONNECT BY PRIOR ID = PID

以上主要是针对上层对下层的顺向递归查询而使用start with ... connect by prior ...这种方式,但有时在需求需要的时候,可能会需要由下层向上层的逆向递归查询,此是语句就有所变化:例如要实现 select * from table where id in ('0','01','0101','0203','0304') ;现在想把0304的上一级03给递归出来,0203的上一级02给递归出来,而01现在已经是存在的,最高层为0.而这张table不仅仅这些数据,但我现在只需要('0','01','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 BY PRIOR PID = ID START WITH ID IN ('0','01','0101','0203','0304') );

其中START WITH ID IN里面的值也可以替换SELECT 子查询语句.

注意由上层向下层递归与下层向上层递归的区别在于START WITH...CONNECT BY PRIOR...的先后顺序以及 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 b

1 0

2 1

3 1

4 2

5 3

如果想查找a=2及其下面的所有数据,则:

select * 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_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,只可向前滚动;

2.TYPE_SCROLL_INSENSITIVE,双向滚动,但不及时更新,就是如果数据库里的数据修改过,并不在ResultSet中反应出来。

3.TYPE_SCROLL_SENSITIVE,双向滚动,并及时跟踪数据库的更新,以便更改ResultSet中的数据

======================================

10g树形查询特性CONNECT_BY_ISCYCLE

在10g中Oracle提供了新的伪列:CONNECT_BY_ISCYCLE,通过这个伪列,可以判断是否在树形查询的过程中构成了循环,这个伪列只是在CONNECT BY NOCYCLE方式下有效。

这一篇描述一下解决问题的思路。

CONNECT_BY_ISCYCLE的实现和前面两篇文章中CONNECT_BY_ROOT和CONNECT_BY_ISLEAF的实现完全不同。

因为要实现CONNECT_BY_ISCYCLE,就必须先实现CONNECT BY NOCYCLE,而在9i中是没有方法实现这个功能的。

也就是说,首先要实现自己的树形查询的功能,而仅这第一点,就是一个异常困难的问题,何况后面还要实现NOCYCLE,最后再加上一个ISCYCLE的判断。

所以总的来说,这个功能的实现比前面两个功能要复杂得多。由于树形查询的LEVEL是不固定的,所以采用链接的方式实现,基本上是不现实的。换句话说,用纯SQL的方式来实现树形查询的功能基本上不可行。而为了解决这个功能,只能通过PL/SQL配合SQL来实现。

仍然是首先构造一个例子:

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 VALUES (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;

ID FATHER_ID NAME

---------- ---------- ------------------------------

1 0 A

2 1 BC

3 1 DE

4 1 FG

5 2 HIJ

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 WITH ID = 0

4 CONNECT BY PRIOR ID = FATHER_ID;

ERROR:

ORA-01436: 用户数据中的 CONNECT BY 循环

未选定行

SQL> SELECT *

2 FROM T_TREE

3 START WITH 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 WITH ID = 0

4 CONNECT BY NOCYCLE PRIOR ID = FATHER_ID;

ID FATHER_ID NAME

---------- ---------- ------------------------------

0 0 ROOT

1 0 A

2 1 BC

5 2 HIJ

3 1 DE

4 1 FG

6 4 KLM

7 6 NOPQ

已选择8行。

使用CONNECT BY NOCYCLE,Oracle自动避免循环的产生,将不产生循环的数据查询出来,下面看看CONNECT_BY_ISCYCLE的功能:

SQL> SELECT ID,

2 FATHER_ID,

3 NAME,

4 CONNECT_BY_ISCYCLE CYCLED

5 FROM T_TREE

6 START WITH 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 HIJ 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中是没有现成的办法的,所以这里尝试使用PL/SQL来自己实现树形查询的功能。

SQL> CREATE OR REPLACE FUNCTION F_FIND_CHILD(P_VALUE VARCHAR2) RETURN VARCHAR2 AS

2 V_STR VARCHAR2(32767) := '/' || P_VALUE;

3

4 PROCEDURE P_GET_CHILD_STR (P_FATHER IN VARCHAR2, P_STR IN OUT VARCHAR2) AS

5 BEGIN

6 FOR I IN (SELECT ID FROM T_TREE WHERE FATHER_ID = P_FATHER AND FATHER_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;

13 BEGIN

14 P_GET_CHILD_STR(P_VALUE, V_STR);

15 RETURN V_STR;

16 END;

17 /

函数已创建。

构造一个函数,在函数中递归调用过程来实现树形查询的功能。

下面看看调用这个函数的结果: 

SQL> SELECT F_FIND_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的功能。

有了这个函数作为基础,就可以逐步的实现最终的目标了。


相关文章 相关文档 相关视频



我们该如何设计数据库
数据库设计经验谈
数据库设计过程
数据库编程总结
数据库性能调优技巧
数据库性能调整
数据库性能优化讲座
数据库系统性能调优系列
高性能数据库设计与优化
高级数据库架构师
数据仓库和数据挖掘技术
Hadoop原理、部署与性能调优

 
分享到
 
 
     


MySQL索引背后的数据结构
MySQL性能调优与架构设计
SQL Server数据库备份与恢复
让数据库飞起来 10大DB2优化
oracle的临时表空间写满磁盘
数据库的跨平台设计
更多...   


并发、大容量、高性能数据库
高级数据库架构设计师
Hadoop原理与实践
Oracle 数据仓库
数据仓库和数据挖掘
Oracle数据库开发与管理


GE 区块链技术与实现培训
航天科工某子公司 Nodejs高级应用开发
中盛益华 卓越管理者必须具备的五项能力
某信息技术公司 Python培训
某博彩IT系统厂商 易用性测试与评估
中国邮储银行 测试成熟度模型集成(TMMI)
中物院 产品经理与产品管理
更多...   
 
 
 
 
 
每天2个文档/视频
扫描微信二维码订阅
订阅技术月刊
获得每月300个技术资源
 
 

关于我们 | 联系我们 | 京ICP备10020922号 京公海网安备110108001071号