oracle数据库树状或者层次结果集的查询
来源:互联网 发布:linux虚拟机kvm怎么用 编辑:程序博客网 时间:2024/06/07 03:59
Oracle “CONNECT BY”是层次查询子句,一般用于树状或者层次结果集的查询。其语法是:
[ START WITH condition ]CONNECT BY [ NOCYCLE ] conditionThe 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
-- Create tablecreate table DEP( DEPID number(10) not null, DEPNAME varchar2(256), UPPERDEPID number(10));
初始化一些数据
SQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (0, '总经办', null);1 row insertedSQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (1, '开发部', 0);1 row insertedSQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (2, '测试部', 0);1 row insertedSQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (3, 'Sever开发部', 1);1 row insertedSQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (4, 'Client开发部', 1);1 row insertedSQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (5, 'TA测试部', 2);1 row insertedSQL> INSERT INTO DEP(DEPID, DEPNAME, UPPERDEPID) VALUES (6, '项目测试部', 2);1 row insertedSQL> commit;Commit completeSQL> 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”来实现树状查询结果
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-Development General Deparment 0 2---Server Development General Deparment 1 3---Client Development General Deparment 1 3-QA General Deparment 0 2---TA General Deparment 1 3---Porject QA General Deparment 1 37 rows selected说明: 1. CONNECT_BY_ROOT 返回当前节点的最顶端节点 2. CONNECT_BY_ISLEAF 判断是否为叶子节点,如果这个节点下面有子节点,则不为叶子节点 3. LEVEL 伪列表示节点深度 4. SYS_CONNECT_BY_PATH函数显示详细路径,并用“/”分隔[例2]通过CONNECT BY生成序列
SQL> SELECT ROWNUM FROM DUAL CONNECT BY ROWNUM <= 10;
ROWNUM 1 2 3 4 5 6 7 8 9 1010 rows selected[例3]通过CONNECT BY用于十六进度转换为十进制
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;
“`
说明:
CONNECT BY rownum <= length(p_str))对输入的字符串进行逐个遍历
通过CASE语句,来解析十六进制中的A-F对应的10进制值
- oracle数据库树状或者层次结果集的查询
- Oracle “CONNECT BY” 使用Oracle “CONNECT BY”是层次查询子句,一般用于树状或者层次结果集的查询。其语法是:
- 【Oracle数据库】层次查询
- Oracle数据库用模糊查询、或者= 怎么得不到汉字的搜索结果?
- oracle的层次查询
- oracle的层次查询
- oracle:数据库查询返回结果集的默认值
- oracle层次查询的陷阱
- ORACLE数据库中将clob查询结果展开
- oracle的查询结果合并
- Oracle层次查询的基本用法
- 数据库查询获取结果集
- ResultSet 结果集查询数据库
- 数据库查询不到结果集
- oracle层次查询
- Oracle 层次查询
- ORACLE层次化查询
- oracle 层次化查询
- OpenCV 2.4.9 +VS2013 开发环境配置
- 分布式集群系统下的高可用session解决方案
- 初学Spring Boot遇到Whitelabel Error Page的解决办法
- iOS-格式化金额,三位一逗号
- 关于实行和防止SSL剥离
- oracle数据库树状或者层次结果集的查询
- 关于文本分类方面的
- poj 3311 Hie with the Pie (floyd+状态压缩dp~)
- 第十一周项目一
- 第十一周项目4---利用遍历思想求解图问题(5)
- 定义一个图形类及其子类(三角形类和矩形类),分别计算其面积和周长。
- nrm --npm镜像管理工具
- C# Socket简单例子(服务器与多个客户端通信)
- RT1050(地上最强Cortex-M7) uclinux初体验