`

Oracle “CONNECT BY” 使用

 
阅读更多
 select lpad(' ',3*(level-1))||parentmenuid a,
lpad(' ',3*(level-1))||menuid,
lpad(' ',3*(level-1))||menu_name from t_pub_menu where is_show=1
start  with menuid=20000
 connect by parentmenuid = prior menuid
 
 

Oracle “CONNECT BY” 使用

 

    Oracle “CONNECT BY”是层次查询子句,一般用于树状或者层次结果集的查询。其语法是:

1
2
[ START WITH condition ]
CONNECT BY [ NOCYCLE ] condition

The start with .. connect by clause can be used to select data that has a hierarchical relationship (usually some sort of parent->child (boss->employee or thing->parts).

 

说明: 
1. START WITH:告诉系统以哪个节点作为根结点开始查找并构造结果集,该节点即为返回记录中的最高节点。 
2. 当分层查询中存在上下层互为父子节点的情况时,会返回ORA-01436错误。此时,需要在connect by后面加上NOCYCLE关键字。同时,可用connect_by_iscycle伪列定位出存在互为父子循环的具体节点。 connect_by_iscycle必须要跟关键字NOCYCLE结合起来使用

 

    接下来,用一些示例来说明“CONNECT BY”的用法。

 

[例1]

创建一个部门表,这个表有三个字段,分别对应部门ID,部门名称,以及上级部门ID

1
2
3
4
5
6
7
8
-- Create table
create table DEP
(
  DEPID      number(10) not null,
  DEPNAME    varchar2(256),
  UPPERDEPID number(10)
)
;

初始化一些数据

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (0, '总经办', null);
1 row inserted
 
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (1, '开发部', 0);
1 row inserted
 
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (2, '测试部', 0);
1 row inserted
 
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (3, 'Sever开发部', 1);
1 row inserted
 
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (4, 'Client开发部', 1);
1 row inserted
 
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (5, 'TA测试部', 2);
1 row inserted
 
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (6, '项目测试部', 2);
1 row inserted
 
SQL> commit;
Commit complete
1
2
3
4
5
6
7
8
9
10
11
12
13
SQL> SELECT * FROM DEP;
 
      DEPID DEPNAME                                                                           UPPERDEPID
----------- -------------------------------------------------------------------------------- -----------
          0 General Deparment                                                               
          1 Development                                                                                0
          2 QA                                                                                         0
          3 Server Development                                                                         1
          4 Client Development                                                                         1
          5 TA                                                                                         2
          6 Porject QA                                                                                 2
 
7 rows selected

现在我要根据“CONNECT BY”来实现树状查询结果

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
SQL> SELECT RPAD( ' ', 2*(LEVEL-1), '-' ) || DEPNAME "DEPNAME",
CONNECT_BY_ROOT DEPNAME "ROOT",
CONNECT_BY_ISLEAF "ISLEAF",
LEVEL ,
SYS_CONNECT_BY_PATH(DEPNAME, '/') "PATH"
FROM DEP
START WITH UPPERDEPID IS NULL
CONNECT BY PRIOR DEPID = UPPERDEPID;
 
DEPNAME                        ROOT                    ISLEAF      LEVEL PATH
------------------------------ ------------------- ---------- ---------- --------------------------------------------------------------------------------
General Deparment              General Deparment            0          1 /General Deparment
 -Development                  General Deparment            0          2 /General Deparment/Development
 ---Server Development         General Deparment            1          3 /General Deparment/Development/Server Development
 ---Client Development         General Deparment            1          3 /General Deparment/Development/Client Development
 -QA                           General Deparment            0          2 /General Deparment/QA
 ---TA                         General Deparment            1          3 /General Deparment/QA/TA
 ---Porject QA                 General Deparment            1          3 /General Deparment/QA/Porject QA
                                                    
7 rows selected

说明: 
1. CONNECT_BY_ROOT 返回当前节点的最顶端节点 
2. CONNECT_BY_ISLEAF 判断是否为叶子节点,如果这个节点下面有子节点,则不为叶子节点 
3. LEVEL 伪列表示节点深度 
4. SYS_CONNECT_BY_PATH函数显示详细路径,并用“/”分隔

[例2]

通过CONNECT BY生成序列

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
SQL> SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 10;
 
    ROWNUM
----------
         1
         2
         3
         4
         5
         6
         7
         8
         9
        10
 
10 rows selected

[例3]

通过CONNECT BY用于十六进度转换为十进制

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
CREATE OR REPLACE FUNCTION f_hex_to_dec(p_str IN VARCHAR2) RETURN VARCHAR2 IS
    ----------------------------------------------------------------------------------------------------------------------
    -- 对象名称: f_hex_to_dec
    -- 对象描述: 十六进制转换十进制
    -- 输入参数: p_str 十六进制字符串
    -- 返回结果: 十进制字符串
    -- 测试用例: SELECT f_hex_to_dec('78A') FROM dual;
    ----------------------------------------------------------------------------------------------------------------------
    v_return  VARCHAR2(4000);
  BEGIN
    SELECT SUM(DATA) INTO v_return
      FROM (SELECT (CASE upper(substr(p_str, rownum, 1))
                     WHEN 'A' THEN '10'
                     WHEN 'B' THEN '11'
                     WHEN 'C' THEN '12'
                     WHEN 'D' THEN '13'
                     WHEN 'E' THEN '14'
                     WHEN 'F' THEN '15'
                     ELSE substr(p_str, rownum, 1)
                   END) * power(16, length(p_str) - rownum) DATA
              FROM dual
            CONNECT BY rownum <= length(p_str));
    RETURN v_return;
  EXCEPTION
    WHEN OTHERS THEN
      RETURN NULL;
  END;

说明:

1. CONNECT BY rownum <= length(p_str))对输入的字符串进行逐个遍历

2. 通过CASE语句,来解析十六进制中的A-F对应的10进制值 
分享到:
评论

相关推荐

    oracle Connect By使用

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

    Oracle的Connect By使用示例

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

    oracle connect by level 应用

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

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

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

    ORACLE查询树型关系(connect_by_prior_start_with)

    Oracle 查询树型关系是指使用 START WITH 和 CONNECT BY 子句来实现 SQL 的层次查询。从 Oracle 9i 开始,可以通过 SYS_CONNECT_BY_PATH 函数实现将父节点到当前行内容以“path”或者层次元素列表的形式显示出来。 ...

    connect by的使用探索

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

    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的使用

    在Oracle数据库中,`CONNECT BY` 是一个非常重要的SQL语法,用于构建层次查询,它能够帮助我们处理具有层级关系的数据,比如组织结构、产品分类、树形菜单等。`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` 的使用方法以及应用场景。这两个关键字对于处理具有层级结构的数据非常有用。此外,我们还讨论了如何利用 `LEVEL` 关键字来显示节点所在的...

    connect_by_path和connect_by_root比较总结

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

    Oracle_start_with_connect_by_prior_用法

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

    树状数据库表:Oracle中start with...connect by prior子句用法

    `START WITH...CONNECT BY PRIOR`是Oracle SQL中的一个特性,用于处理具有层级关系的数据。这个子句允许我们遍历和查询具有父子关系的数据,例如部门和其下属子部门,或者员工和他们的上级经理。 1. **START WITH...

    Oracle递归树形结构查询功能

    `sys_connect_by_path`函数与`CONNECT BY`配合使用,可以追踪节点在整个树中的路径。例如,它可以将每个节点的路径作为字符串返回,便于理解和分析树结构。 在实际应用中,例如组织结构的展示,我们可以创建一个...

    connect by prior数据库树的应用

    通过使用 `Connect By Prior` 存储过程,我们可以有效地处理这类问题。本文将详细介绍如何利用 `Connect By Prior` 来构建和查询层次结构数据,并通过具体的例子来解释其工作原理。 #### 核心知识点详解 ### 1. `...

Global site tag (gtag.js) - Google Analytics