`
txf2004
  • 浏览: 7041427 次
  • 性别: Icon_minigender_1
  • 来自: 上海
社区版块
存档分类
最新评论

Oracle 10G 新特性——增强的CONNECT BY子句

阅读更多
为了更好的查询一个树状结构的表,在OraclePL/SQL中提供乐一个诱人的特性——CONNECT BY子句。它大大的方便了我们查找树状表:遍历一棵树、寻找某个分支……,但还是存在一些不足。在Oracle <chmetcnv unitname="g" sourcevalue="10" hasspace="False" negative="False" numbertype="1" tcsc="0" w:st="on">10G</chmetcnv>,就对这个特性做了增强。下面就举例说明一下:

CONNECT_BY_ROOT

一张表,有多颗子树(根节点为0),现在我想知道每个节点属于哪个子树。举例:铃音目录结构下有多个大分类:中外名曲、流行经典、浪漫舞曲……,每个大类下面又有多个子类,子类下面还可以细分。那现在想要知道每个子类分属哪个大类,或者要统计每个大类下面有多少个子类。

看下面的例子,DIRINDEX分别为1、2、3的就是大分类,其他编号的都是子类或孙子类:

select dirindex, fatherindex, RPAD(' ', 2*(LEVEL-1)) || dirname from t_tonedirlib

start with fatherindex = 0

connect by fatherindex = prior dirindex

DIRINDEX FATHERINDEX DIRNAME

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

1 0 中文经典

52 1 kkkkkkk

70 52 222

58 52 sixx

59 52 seven

69 52 uiouoooo

55 52 four

7 1 流行风云

8 1 影视金曲

1111 8 aaa

1112 8 bbb

1113 8 ccc

9 1 古典音乐

81 1 小熊之家

104 81 龙珠

105 81 snoppy

101 81 叮当1

102 81 龙猫

103 81 叮当2

2 0 热门流行

31 2 有奖活动

32 2 相约香格里拉

50 2 新浪彩铃

3 0 老歌回放

333 3 老电影

335 3 怀旧金曲

26 rows selected

如何统计1、2、3三个大类下有哪些子类,有多少个子类?在9i及以前要做这样的统计十分麻烦。现在10G提供了一个新特性:CONNECT_BY_ROOT,他的作用就是使结果不是当前的节点ID,而满足查询条件下的根节点的ID。以上面为例,我们需要得到以上结果只需要执行以下语句就可以搞定了:

select CONNECT_BY_ROOT dirindex, fatherindex, RPAD(' ', 2*(LEVEL-1)) || dirname from t_tonedirlib

start with fatherindex = 0

connect by fatherindex = prior dirindex

CONNECT_BY_ROOTDIRINDEX FATHERINDEX RPAD('',2*(LEVEL-1))||DIRNAME

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

1 0 中文经典

1 1 kkkkkkk

1 52 222

1 52 sixx

1 52 seven

1 52 uiouoooo

1 52 four

1 1 流行风云

1 1 影视金曲

1 8 aaa

1 8 bbb

1 8 ccc

1 1 古典音乐

1 1 小熊之家

1 81 龙珠

1 81 snoppy

1 81 叮当1

1 81 龙猫

1 81 叮当2

2 0 热门流行

2 2 有奖活动

2 2 相约香格里拉

2 2 新浪彩铃

3 0 老歌回放

3 3 老电影

3 3 怀旧金曲

26 rows selected

查出来的结果中,CONNECT_BY_ROOTDIRINDEX就是各个子类(孙子类)所属的大类编号,如果需要统计,就只要执行以下语句马上可以统计出来了:

select rootindex, count('X') from

(select CONNECT_BY_ROOT dirindex as rootindex

from t_tonedirlib

start with fatherindex = 0

connect by fatherindex = prior dirindex) a

group by a.rootindex

ROOTINDEX COUNT('X')

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

1 19

2 4

3 3

3 rows selected

CONNECT_BY_ISLEAF

经常有DBA因为要查找树状表中的叶子节点而苦恼。大部分DBA为了解决这个问题就给表增加了一个字段来描述这个节点是否为叶子节点。但这样做有很大的弊端:需要通代码逻辑来保证这个字段的正确性。

Oracle 10G中提供了一个新特性——CONNECT_BY_ISLEAF——来解决这个问题了。简单点说,这个属性结果表明当前节点在满足条件的查询结果中是否为叶子节点, 0不是,1是:

select CONNECT_BY_ISLEAF, dirindex, fatherindex, RPAD(' ', 2*(LEVEL-1)) || dirname

from t_tonedirlib

start with fatherindex = 0

connect by fatherindex = prior dirindex

CONNECT_BY_ISLEAF DIRINDEX FATHERINDEX RPAD(' ',2*(LEVEL-1))||dirname

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

0 1 0 中文经典

0 52 1 kkkkkkk

1 70 52 222

1 58 52 sixx

1 59 52 seven

1 69 52 uiouoooo

1 55 52 four

1 7 1 流行风云

0 8 1 影视金曲

1 1111 8 aaa

1 1112 8 bbb

1 1113 8 ccc

1 9 1 古典音乐

0 81 1 小熊之家

1 104 81 龙珠

1 105 81 snoppy

1 101 81 叮当1

1 102 81 龙猫

1 103 81 叮当2

0 2 0 热门流行

1 31 2 有奖活动

1 32 2 相约香格里拉

1 50 2 新浪彩铃

0 3 0 老歌回放

1 333 3 老电影

1 335 3 怀旧金曲

26 rows selected

一看结果,清晰明了!

CONNECT_BY_ISCYCLE

我们的树状属性一般都是在一条记录中记录一个当前节点的ID和这个节点的父ID来实现。但是,一旦数据中出现了循环记录,如两个节点互为对方父节点,系统就会报ORA-01436错误:

insert into t_tonedirlib(dirindex, fatherindex, dirname, status) values (666, 667, '123', 5);

1 row inserted

insert into t_tonedirlib(dirindex, fatherindex, dirname, status) values (667, 666, '456', 5);

1 row inserted

select dirindex, fatherindex, RPAD(' ', 2*(LEVEL-1)) || dirname from t_tonedirlib

start with fatherindex = 666

connect by fatherindex = prior dirindex

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

<chmetcnv unitname="g" sourcevalue="10" hasspace="False" negative="False" numbertype="1" tcsc="0" w:st="on">10G</chmetcnv>中,可以通过加上NOCYCLE关键字避免报错。并且通过CONNECT_BY_ISCYCLE属性就知道哪些节点产生了循环:

select CONNECT_BY_ISCYCLE, dirindex, fatherindex, RPAD(' ', 2*(LEVEL-1)) || dirname

from t_tonedirlib

start with fatherindex = 666

connect by NOCYCLE fatherindex = prior dirindex

CONNECT_BY_ISCYCLE DIRINDEX FATHERINDEX RPAD(' ',2*(LEVEL-1))||dirname

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

0 667 666 456

1 666 667 123

2 rows selected

以上就是在<chmetcnv unitname="g" sourcevalue="10" hasspace="False" negative="False" numbertype="1" tcsc="0" w:st="on"><span lang="EN-US">10G</span></chmetcnv>中增强的CONNECT BY了。当然对于这些增强特性的

分享到:
评论

相关推荐

    oracle语法.rar

    Oracle提供了一个特殊的查询构造——CONNECT BY,它允许查询数据时自动生成层次结构。通过指定START WITH子句来定义递归的起点,然后使用CONNECT BY子句定义如何从一个层级过渡到下一个层级。例如,查询员工及其下属...

    Oracle SQL高级编程(资深Oracle专家力作,OakTable团队推荐)--随书源代码

    Karen Morton及其团队在本书中提供了专业的方案:先掌握语言特性,再学习Oracle为提升语言效率而加入的支持特性,进而将两者综合考虑并在工作中加以应用。作者通过总结各自多年的软件开发和教学培训经验,与大家...

    Oracle树结构查询(图)

    Oracle提供了CONNECT BY子句来实现这一点。它允许我们定义一个起始点(通常是顶层节点),然后根据特定的连接条件逐层向下遍历。例如,我们可以用以下SQL语句查询员工的完整上下级关系: ```sql SELECT level, ...

    Oracle中的树状查询(递归查询)

    递归查询基于`CONNECT BY`子句,它允许我们定义一个起始点并根据特定条件进行递归。`PRIOR`关键字用于指定当前行与父行的关系。通过这些工具,Oracle可以构建出一个层次结构,展示出数据的树状关系。 2. **基本...

    oracle递归查询的例子

    2. **CONNECT BY PRIOR 子句**:用于定义递归关系,即如何从上一级记录推导出下一级记录。 3. **LEVEL 关键字**:用于获取当前记录的层级编号,这对于控制递归深度非常有用。 #### 三、示例表的创建与数据插入 首先...

    oracle ebs开发文档

    这部分介绍了如何使用 CONNECT BY 和 START WITH 子句来进行层次结构的查询,这对于组织结构图或产品分类树等数据模型非常适用。 #### 五、PL/SQL 进阶 **5.1 PL/SQL 过程调用表示法** 这部分介绍了如何在 PL/SQL ...

    oracle学习文档 笔记 全面 深刻 详细 通俗易懂 doc word格式 清晰 连接字符串

    2. oracle11G自带一个卸载批处理\app\Administrator\product\11.2.0\dbhome_1\deinstall\deinstall.bat 3. 运行该批处理程序将自动完成oracle卸载工作,最后手动删除\app文件夹(可能需要重启才能删除) 4. 运行...

    各种oracle指南

    ### Oracle从入门到精通——全面解析Oracle数据库的关键知识点 #### SQL基础:理解Oracle数据库的核心语言 **1.1 基本概念** Oracle数据库作为业界领先的数据库管理系统之一,其核心功能在于通过SQL(Structured ...

    oracle知识点大汇总

    在Oracle数据库系统中,存在两个预定义的用户——`sys`和`system`,它们在数据库安装过程中自动生成。 - **sys用户**:作为超级用户,拥有最高的权限级别,包括`sysdba`角色,这赋予其创建数据库的能力。默认情况下...

    Oracle数据库实验报告

    ### Oracle数据库实验报告知识点梳理 #### 一、准备工作:SQL*Plus创建公共用户C##scott - **目的**:创建一个公共用户`C##scott`,以便多个用户可以共享该账号登录Oracle数据库。 - **步骤**: - 打开SQL*Plus...

    韩顺平玩转oracle学习笔记

    - 复杂查询可以使用`where`, `group by`, `having`, `order by`等子句。 - 多表查询使用`join`关键字连接多个表。 - 子查询使用`in`, `exists`, `any`, `all`等关键字进行子查询。 - 分页查询使用`rownum`或`fetch ...

    程序员的SQL金典.rar

     11.9.1 Oracle中的CONNECT BY子句  11.9.2 Oracle中的SYS_CONNECT_BY_PATH()函数  11.9.3 My SQL Server和DB2中递归查询 附录A 常用数据库系统的安装和使用  A.1 DB2的安装和使用  A.2 MySQL的安装和使用  A....

    MS SQL Server树形结构表遍历的循环算法.pdf

    例如,在Oracle中,可以利用CONNECT BY子句实现树结构的递归查询,而在IBM DB2中,递归查询的SQL语句由初始查询、递归查询和最终查询三部分组成。但是,MS SQL Server在当时尚未内置递归查询功能,这意味着在MS SQL ...

Global site tag (gtag.js) - Google Analytics