本文的目的是介绍除Oracle以外的RDBMS中的CONNECTBY或分层查询的详细情况，特别关注oracle除了某些字段其他数据都查询的相关信息。我们将通过专业的研究、有关数据的分析等多种方式，为您
本文的目的是介绍除Oracle以外的RDBMS中的CONNECT BY或分层查询的详细情况，特别关注oracle 除了某些字段其他数据都查询的相关信息。我们将通过专业的研究、有关数据的分析等多种方式，为您呈现一个全面的了解除Oracle以外的RDBMS中的CONNECT BY或分层查询的机会，同时也不会遗漏关于#ORACLE 每日一点#Oracle CONNECT BY 使用、jboss6.x – 无法将oracle.jdbc.driver.T4CConnection强制转换为oracle.jdbc.OracleConnection、Oracle connect by prior查询用法、Oracle Connect By Prior（递归查询）简洁实例的知识。
本文目录一览：- 除Oracle以外的RDBMS中的CONNECT BY或分层查询（oracle 除了某些字段其他数据都查询）
- #ORACLE 每日一点#Oracle CONNECT BY 使用
- jboss6.x – 无法将oracle.jdbc.driver.T4CConnection强制转换为oracle.jdbc.OracleConnection
- Oracle connect by prior查询用法
- Oracle Connect By Prior（递归查询）简洁实例
除Oracle以外的RDBMS中的CONNECT BY或分层查询（oracle 除了某些字段其他数据都查询）
Oracle附带了一个非常方便的功能。您可以使用以下子句创建分层查询（递归行为）：
CONNECT BY [NOCYCLE] {condition [AND condition...]} [START WITH condition]
如此处记录：
http://download.oracle.com/docs/cd/B28359_01/server.111/b28286/queries003.htm
我想知道，是否还存在其他支持等效或相似语法的RDBMS？还是可以使用常规SQL对这种递归行为进行一般性的模拟？
我想能够模拟的一个很好的例子是（摘自Oracle文档）：
SELECT LPAD(' ',2 * (LEVEL-1)) || last_name org_chart,employee_id,manager_id,job_id
FROM employees
START WITH job_id = 'AD_VP'
CONNECT BY PRIOR employee_id = manager_id;
导致：
ORG_CHART EMPLOYEE_ID MANAGER_ID JOB_ID
------------------ ----------- ---------- ----------
Kochhar 101 100 AD_VP
Greenberg 108 101 FI_MGR
Faviet 109 108 FI_ACCOUNT
Chen 110 108 FI_ACCOUNT
Sciarra 111 108 FI_ACCOUNT
Urman 112 108 FI_ACCOUNT
Popp 113 108 FI_ACCOUNT
Whalen 200 101 AD_ASST
Mavris 203 101 HR_REP
Baer 204 101 PR_REP
Higgins 205 101 AC_MGR
Gietz 206 205 AC_ACCOUNT
De Haan 102 100 AD_VP
Hunold 103 102 IT_PROG
Ernst 104 103 IT_PROG
Austin 105 103 IT_PROG
Pataballa 106 103 IT_PROG
Lorentz 107 103 IT_PROG
该LEVEL用它来实现伪列和压痕对我来说并不那么重要
#ORACLE 每日一点#Oracle CONNECT BY 使用
oracle “connect by”是层次查询子句，一般用于树状或者层次结果集的查询。其语法是： [ START WITHcondition ]CONNECT BY [ NOCYCLE ] condition The start with .. connect by clause can be used to select data that has a hierarchical relationship (
oracle “connect by”是层次查询子句，一般用于树状或者层次结果集的查询。其语法是：
[ START WITHcondition ] 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结合起来使用
例1
创建一个部门表，这个表有三个字段，分别对应部门ID，部门名称，以及上级部门ID
-- Create table create table DEP ( DEPID number(10)notnull, DEPNAME varchar2(256), UPPERDEPID number(10) ) ;
初始化一些数据
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
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 rowsselected
现在我要根据“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 LEVELPATH
------------------------------ ------------------- ---------- ---------- --------------------------------------------------------------------------------
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 rowsselected
1. CONNECT_BY_ROOT 返回当前节点的最顶端节点
2. CONNECT_BY_ISLEAF 判断是否为叶子节点，如果这个节点下面有子节点，则不为叶子节点
3. LEVEL 伪列表示节点深度
4. SYS_CONNECT_BY_PATH函数显示详细路径，并用“/”分隔
例2
通过CONNECT BY生成序列
SQL> SELEC TROWNUM FROM DUAL CONNECT BY ROWNUM <br><p><strong><span>例3</span></strong></p>
<p>通过CONNECT BY用于十六进度转换为十进制</p>
<p>
</p><p>
</p><p></p><pre>CREATE OR REPLACE FUNCTION f_hex_to_dec(p_strINVARCHAR2)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 (CASEupper(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 <br><blockquote>
说明：
1. CONNECT BY rownum </blockquote>
<br><br><p><br></p>
jboss6.x – 无法将oracle.jdbc.driver.T4CConnection强制转换为oracle.jdbc.OracleConnection
WrappedConnectionJDK6 wrapped = (WrappedConnectionJDK6) dbStrategy.getConnection();
Connection underlyingConn = wrapped.getUnderlyingConnection();
OracleConnection oracleConn = (OracleConnection)underlyingConn;
最后一行给出错误 –
> ERROR > [org.apache.catalina.core.ContainerBase.[jboss.web].[default-host].[/web].[resteasy-servlet]] > (http-/0.0.0.0:8080-1) Servlet.service() for servlet resteasy-servlet > threw exception: org.jboss.resteasy.spi.UnhandledException: > java.lang.classCastException: oracle.jdbc.driver.T4CConnection cannot > be cast to oracle.jdbc.OracleConnection
恕我直言你有2个驱动程序实现,一个在app服务器上,一个在你的项目依赖项中,必须有一个类加载问题,因为检索到的驱动程序实现由共享类加载器加载,你试图将它转换为webApp加载的类类加载器.
您可以确保您的Web应用程序依赖项与服务器提供的实现相同,或者在打包时仅从Web应用程序中排除依赖项.
如果你正在使用maven,只需将范围设置为提供.
Oracle connect by prior查询用法
参考地址：https://blog.csdn.net/qq_33656602/article/details/77750576
用法示例：
SELECT t.* FROM Score_Model t WHERE t.id = 552 OR t.pid = 552
SELECT t.* FROM Score_Model t START WITH t.id = 552 CONNECT BY PRIOR t.id = t.pid
SELECT t.* FROM Score_Model t START WITH t.id = 553 CONNECT BY t.id = PRIOR t.pid
SELECT t.* FROM Score_Model t START WITH t.id = 554 CONNECT BY t.id = PRIOR t.pid
Oracle Connect By Prior（递归查询）简洁实例
oracle中的select语句可以用START WITH...CONNECT BY PRIOR子句实现递归查询，connect by 是结构化查询中用到的，其基本语法是：
select ... from <TableName>
where <Conditional-1>
start with <Conditional-2>
connect by <Conditional-3>
;
<Conditional-1>：过滤条件，用于对返回的所有记录进行过滤。
<Conditional-2>：查询结果重起始根结点的限定条件。
<Conditional-3>：连接条件
例如：
数据库表结构如下：
create table t2(
root_id number,
id number,
name varchar(5),
description varchar(10)
);
insert into t2(root_id,id,name,description) values(0,1,'a','aaa');
insert into t2(root_id,description) values(1,2,'a1','aaa1');
insert into t2(root_id,3,'a2','aaa2');
insert into t2(root_id,4,'b','bbb');
insert into t2(root_id,description) values(4,5,'b1','bbb1');
insert into t2(root_id,6,'b2','bbb2');
如图：
1.获取完整树：
select * from t2;
select * from t2 start with root_id = 0 connect by prior id = root_id;
2.获取特定子树：
select * from t2 start with id = 1 connect by prior id = root_id;
select * from t2 start with id = 4 connect by prior id = root_id;
3.如果connect by prior中的prior被省略，则查询将不进行深层递归。
如：
select * from t2 start with root_id = 0 connect by id = root_id;
select * from t2 start with id = 1 connect by id = root_id;
3、可以向下或者向上查找
关于除Oracle以外的RDBMS中的CONNECT BY或分层查询和oracle 除了某些字段其他数据都查询的介绍现已完结，谢谢您的耐心阅读，如果想了解更多关于#ORACLE 每日一点#Oracle CONNECT BY 使用、jboss6.x – 无法将oracle.jdbc.driver.T4CConnection强制转换为oracle.jdbc.OracleConnection、Oracle connect by prior查询用法、Oracle Connect By Prior（递归查询）简洁实例的相关知识，请在本站寻找。
本文标签：
