`

ORACLE CONNECT BY 学习

阅读更多

语法格式:

 

select [level], column, expr... from table
  [where condition]
  start with condition
  connect by [prior column1= column2 |column1 = prior column2];

 层次查询是通过start with和connect by子句标识的:

 

1.其中level关键字是可选的,表示等级,1表示root,2表示root的child,其他相同的规则。

2.From之后可以是table,view但是只能是一个table。

3.Where条件限制了查询返回的行,但是不影响层次关系,属于将节点截断,但是这个被截断的节点的下层child不受影响。

4.Start with是表示开始节点,对于一个真实的层次关系,必须要有这个子句,但是不是必须的。

5.connect by prior是指定父子关系,其中prior的位置不一定要在connect by之后,对于一个真实的层次关系,这也是必须的。

对于from是视图的,那么这个view不能包含join。

层次查询限制

1.层次查询from 之后如果是table,只能是一个table,不能有join。

2.from之后如果是view,则view不能是带join的。

3.使用order by子句,order子句是在等级层次做完之后开始的,所以对于层次查询来说没有什么意义,除非特别关注level,获得某行在层次中的深度,但是这两种都会破坏层次。见增强特性中的使用siblings排序。

4.在start with中表达式可以有子查询,但是connect by中不能有子查询。

层次查询的增强特性

1、SYS_CONNECT_BY_PATH

Oracle 9i提供了sys_connect_by_path(column,char),其中column是字符型或能自动转换成字符型的列名。它的主要目的就是将父节点到当前节点的”path”按照指定的模式展现出现。这个函数只能使用在层次查询中。

下面的是oracle10g新增特性

2、 CONNECT_BY_ISLEAF

    在oracle9i的时候,查找指定root下的叶子节点,是很复杂的,oracle10g引入了一个新的函数,connect_by_isleaf,如果行的值为0表示不是叶子节点,1表示是叶子节点。

3、CONNECT_BY_ISCYCLE和NOCYCLE关键字

    如果从root节点开始找其子孙,找到一行,结果发生和祖先互为子孙的情况,则发生循环,oracle会报ORA-01436: CONNECT BY loop in user data,在9i中只能将发生死循环的不加入到树中或删除,在10g中可以用nocycle关键字加在connect by之后,避免循环的参加查询操作。并且通过connect_by_iscycle得到哪个节点发生循环。0表示未发生循环,1表示发生了循环。

4、CONNECT_BY_ROOT

    Oracle10g新增connect_by_root,用在列名之前表示此行的根节点的相同列名的值。

5、使用SIBLINGS关键字排序

    对于层次查询如果用order by排序,比如order by last_name则是先做完层次获得level,然后按last_name排序,这样破坏了层次,比如特别关注某行的深度,按level排序,也是会破坏层次的。

     在oracle10g中,增加了siblings关键字的排序。

语法order  siblings  by <expre>

它会保护层次,并且在每个等级中按expre排序。 

案例:

--测试表A B C D 4层数据
create table DATAS(
ID NUMBER,
DATES DATE,
IDNEXT NUMBER
)
--存在B、C情况
insert into datas values(1,to_date('2015-01-01','yyyy-mm-dd'),2);
insert into datas values(2,to_date('2015-01-11','yyyy-mm-dd'),3);
insert into datas values(3,to_date('2015-01-14','yyyy-mm-dd'),4);
insert into datas values(4,to_date('2015-01-19','yyyy-mm-dd'),null);
--不存在B、C情况
insert into datas values(11,to_date('2015-02-01','yyyy-mm-dd'),14);
insert into datas values(14,to_date('2015-02-11','yyyy-mm-dd'),null);

--查询语句
SELECT ID,DATES,
CONNECT_BY_ROOT ID "ROOT",
CONNECT_BY_ISLEAF "ISLEAF",
LEVEL,
SYS_CONNECT_BY_PATH(ID, '-') "PATH"
FROM DATAS
START WITH IDNEXT IS NULL
CONNECT BY PRIOR ID = IDNEXT ORDER SIBLINGS BY ID;

         ID   DATES          ROOT    ISLEAF   LEVEL   PATH

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

         4  1/19/2015            4           0           1          -4

         3  1/14/2015            4           0           2          -4-3

         2  1/11/2015            4           0           3          -4-3-2

         1  1/1/2015              4           1           4          -4-3-2-1

        14  2/11/2015          14          0           1          -14

        11  2/1/2015            14          1           2          -14-11

分享到:
评论

相关推荐

    oracle connect by level 应用

    Oracle的`CONNECT BY LEVEL`是数据库查询中用于构建层次结构数据的一种强大工具,尤其是在处理具有层级关系的数据时,如组织架构、产品分类等。本文将深入探讨`CONNECT BY`子句以及`LEVEL`伪列的使用,以帮助你更好...

    oracle Connect By使用

    Connect By 可以列出上下级关系 构造序列 求排列组合 逆转求出下上级的关系路径

    Oracle的Connect By使用示例

    ### Oracle的Connect By使用示例 #### 一、引言 在关系数据库系统中,进行树状或层次结构数据查询时经常会遇到复杂性问题。Oracle数据库通过提供`START WITH...CONNECT BY`语句来简化这类查询操作。此功能最早出现...

    Oracle中connect by...start with...的使用

    本文章详细介绍了Oracle中connect by...start with...的用法。

    ORACLE查询树型关系(connect_by_prior_start_with)

    ORACLE 查询树型关系(connect_by_prior_start_with) Oracle 查询树型关系是指使用 START WITH 和 CONNECT BY 子句来实现 SQL 的层次查询。从 Oracle 9i 开始,可以通过 SYS_CONNECT_BY_PATH 函数实现将父节点到...

    connect_by_prior_递归算法

    ### Oracle中的Connect By Prior递归算法详解 #### 一、Connect By Prior 子句概述 在Oracle数据库中,`Connect By Prior`子句是一种非常有用的工具,尤其在处理具有层次结构的数据时。它允许用户以一种简洁的方式...

    oracle connect by 和 分析函数总结.doc

    Oracle的`CONNECT BY`和分析函数是数据库查询中的两种强大工具,主要用于处理复杂的数据结构和进行高级数据分析。本文将深入探讨这两种技术。 ### Oracle `CONNECT BY` 用法总结 `CONNECT BY` 是Oracle SQL中用于...

    Oracle start with.connect by prior子句实现递归查询

    ### Oracle Start With.Connect By Prior 子句实现递归查询 #### 概述 在Oracle数据库中,`Start With.Connect By Prior`子句是执行递归查询的一种强大工具,主要用于处理层次结构数据。这类数据通常存在于组织...

    浅谈Oracle下connect by原理.pdf

    文章中给出了一个名为`t_test_connectby`的数据表例子,其中包含父子关系的节点数据。通过该表,我们可以更直观地了解Connect By的执行过程。 - 不指定起始条件时,使用`CONNECT BY`: ```sql SELECT * FROM t_test...

    connect by的使用探索

    "Connect By的使用探索" Connect By是一种递归查询语句,用于实现树形结构的查询和遍历。它是 Oracle 数据库中的一种特色语句,能够高效地查询树形结构的数据。 Connect By的基本语法 Connect By语句的基本语法...

    plsql连接64位oracle报错instantclient或没有connect by 选项问题

    此问题通常是由于 PL/SQL Developer 的配置不当或者使用的 Oracle 客户端版本较低,不支持 `Connect By` 功能。 #### 三、解决步骤 本文将详细介绍如何解决以上问题,并提供了一键解决方法,即通过下载并正确配置 ...

    oracle中connect-by-prior用法,实战解决日期分解问题.docx

    Oracle数据库中的`CONNECT BY PRIOR`是一个强大的查询构造器,用于处理树形数据结构,尤其在组织层级、部门结构或者员工管理系统中非常常见。这个功能允许我们从一个或多个根节点开始,按照指定的规则遍历整个树结构...

    oracle数据库startwith用法

    ### Oracle数据库中的START WITH 和 CONNECT BY 用法详解 在Oracle数据库中,处理层次结构数据时,`START WITH` 和 `CONNECT BY` 是非常有用的两个关键字。这些关键字可以帮助我们在查询时构建出树形或者层级结构的...

    connect_by_path和connect_by_root比较总结

    通过实例比较了 SYS_CONNECT_BY_PATH 和 CONNECT_BY_ROOT 的异同,和返回树形的数据结构

    connect by的使用

    在Oracle数据库中,`CONNECT BY` 是一个非常重要的SQL语法,用于构建层次查询,它能够帮助我们处理具有层级关系的数据,比如组织结构、产品分类、树形菜单等。`CONNECT BY` 查询允许从一个表中抽取数据,并按照指定...

    oracle学习心得总结

    ### Oracle学习心得总结 在IT领域中,Oracle数据库因其强大的功能和广泛的适用性而备受青睐。对于初学者而言,掌握Oracle的基本操作是非常重要的一步。本文将根据提供的文档内容,总结和扩展Oracle数据库的基础知识...

    oracle11g学习笔记

    在学习Oracle 11g的过程中,了解并掌握其基本概念、安装、用户管理、权限和角色、数据对象以及管理工具的使用至关重要。 首先,安装过程虽然在此略过,但在实际操作中,Oracle 11g的安装包括下载安装包、配置环境...

    Oracle_start_with_connect_by_prior_用法

    ### Oracle中的START WITH CONNECT BY PRIOR 用法详解 #### 一、概念介绍 在Oracle数据库中,`START WITH` 和 `CONNECT BY PRIOR` 是两个非常强大的特性,主要用于处理具有层级结构的数据。这两个特性可以帮助我们...

    ORACLE CONNECT MSSQL USING ODBC / DSN

    CONNECT TO "username" IDENTIFIED BY "password" USING 'ODBC:MYSQLSERVERDSN';` 在上面的语句中,我们创建了一个名为“mysqlsrv”的数据库链接,使用 username 和 password 连接 SQL Server 数据库,使用 ODBC ...

    Oracle学习笔记 Oracle学习笔记

    根据提供的信息,我们可以总结出以下Oracle数据库学习的关键知识点: ### Oracle 学习笔记概览 在Oracle学习过程中,掌握基础的命令与权限管理是至关重要的。以下内容将围绕Oracle数据库的基础操作、用户管理以及...

Global site tag (gtag.js) - Google Analytics