- 浏览: 772786 次
- 性别:
- 来自: 北京
文章分类
- 全部博客 (208)
- Java (77)
- JavaScript (16)
- UML (1)
- Spring (24)
- Hibernate (11)
- J2EE部署 (18)
- 操作系统 (13)
- struts (11)
- jsp (3)
- J2EE (34)
- 数据库 (22)
- tomcat (4)
- apache (2)
- MyEclipse (13)
- Linux (14)
- Ext (6)
- Weblogic (2)
- 数据库 Oracle 空表导出 (1)
- Oracle (3)
- 编码 乱码 (1)
- 多线程 (5)
- jQuery (2)
- Apache Mina (1)
- ibatis (6)
- abator (1)
- svn (1)
- jvm (1)
- ERwin (2)
- mysql (2)
- ant (1)
- memcache (1)
- dubbo (1)
- PowerDesigner (1)
最新评论
-
di1984HIT:
Shallow heap & Retained heap -
tinguo002:
非常感谢 , 太棒了。
Spring注解方式,异常 'sessionFactory' or 'hibernateTemplate' is required的解决方法 -
白天看黑夜:
Apache Mina Server 2.0 中文参考手册(带 ...
Apache Mina – 简单的客户端/服务端应用示例 -
wumingxingzhe:
好文
Shallow heap & Retained heap -
di1984HIT:
学习了!!
工作流(Workflow)和BPM的不同
通过START WITH . . . CONNECT BY . . .子句来实现SQL的层次查询.
自从Oracle 9i开始,可以通过 SYS_CONNECT_BY_PATH 函数实现将父节点到当前行内容以“path”或者层次元素列表的形式显示出来。
自从Oracle 10g 中,还有其他更多关于层次查询的新特性 。例如,有的时候用户更关心的是每个层次分支中等级最低的内容。
那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如果是叶子就会在伪列中显示“1”,
如果不是叶子而是一个分支(例如当前内容是其他行的父亲)就显示“0”。
在Oracle 10g 之前的版本中,如果在你的树中出现了环状循环(如一个孩子节点引用一个父亲节点),
Oracle 就会报出一个错误提示:“ ORA-01436: CONNECT BY loop in user data”。如果不删掉对父亲的引用就无法执行查询操作。
而在 Oracle 10g 中,只要指定“NOCYCLE”就可以进行任意的查询操作。与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE,
如果在当前行中引用了某个父亲节点的内容并在树中出现了循环,那么该行的伪列中就会显示“1”,否则就显示“0”。
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).
It is also being used when an sql execution plan is explained.
syntax:
select ... [start with initial-condition] connect by [nocycle] recurse-condition
level
With level it is possible to show the level in the hierarchical relation of all the data.
--oracle 9i
sys_connect_by_path
With sys_connect_by_path it is possible to show the entire path from the top level down to the 'actual' child.
--oracle 10g
connect_by_root
connect_by_root is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
connect_by_is_leaf
connect_by_isleaf is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
connect_by_iscycle
connect_by_is_cycle is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
--start with ... connect by ... 的处理机制
How must a start with ... connect by select statement be read and interpreted?
If Oracle encounters such an SQL statement, it proceeds as described in the following pseude code.
for rec in (select * from some_table) loop
if FULLFILLS_START_WITH_CONDITION(rec) then
RECURSE(rec, rec.child);
end if;
end loop;
procedure RECURSE (rec in MATCHES_SELECT_STMT, new_parent IN field_type) is
begin
APPEND_RESULT_LIST(rec);
for rec_recurse in (select * from some_table) loop
if FULLFILLS_CONNECT_BY_CONDITION(rec_recurse.child, new_parent) then
RECURSE(rec_recurse,rec_recurse.child);
end if;
end loop;
end procedure RECURSE;
created by zhouwf0726 2006.
*******************************************************************************/
--创建测试表,增加测试数据
/**********************************************************************************
***********************************************************************************
下面是关于SQL解决有向图问题,在这个例子中作者提到的错误
select * from fares connect by prior arrive = depart start with depart = 'LHR';
ERROR:
ORA-01436: CONNECT BY loop in user data
在oracle10g以上版本可以利用connect by的nocycle参数来解。有兴趣的朋友研究用一条sql实现有向图问题!
***********************************************************************************
**********************************************************************************/
一个常见的高级计算机科学问题可以在“有向图”的范畴之下描述。有向图是由一组向量和边所连接的一组有限的节点。
例如,一个节点可以想象为一座“城市”,而每个向量可以想象为两座城市间的一个“航线”。
有很多算法和论文讲到如何解决每种可能路线的遍历问题以及寻找最短路径或者最小代价路径的问题。
这些算法中大部分都是过程化的,或者是使用递归方面来解决的。然而 SQL 的声明性语言使得解决复杂的有向图问题更加容易,
而且不需要很多代码。
让我们以两座城市之间的航线为例子,创建一个表保存一些假想数据:
不能使用CONNECT BY 语法来解决如何从伦敦到圣保罗,因为在图中有数据产生一个环(从圣保罗飞回):
ERROR:
ORA-01436: CONNECT BY loop in user data
要解决有向图问题,我们需要创建一个临时表来保存两个节点之间所有可能的路径。我们必须注意不复制已经处理过的路径,
而且在这种情况下,我们不想路径走回开始处的同一个地点。我还希望跟踪到达目的地所需航程的数目,以及所走路线的描述。
临时表使用以下脚本创建:
一个简单的视图可以在稍微简化这个例子中使用的代码。视图可以根据 fares 表中的单个航程计算从 faretemp 表中的一个路径
到达一下一个航程的数据:
这个算法相当简单。首先,使用 fares 表中的数据填充 faretemp 表,作为初始的航程。然后,取到我们刚才插入的所有数据,
使用它们建立所有可能的二航程(two-hop)路径。重复这一过程,直至在两个节点之间创建了新路径。
循环过程将在节点间所有可能的路径都被描述之后退出。如果我们只对某个开始条件感兴趣,
那么我们还可以限制第一次的插入从而减少装载数据的量。下面是发现路径的代码:
可以在表 A 中查看输出。
前面的数据有一个小问题。数据是点之间最短路径(最小航程数)的集合。然而,从伦敦到圣保罗的航程却不是最便宜的一个。
要解决最便宜的费用问题,需要对我们的循环做一个改进,当在一个航程中发现一个更便宜的路线时使用这个路线代替原来的路线。
修改后的代码如下:
可能在表 B中查看输出。
算法发现LHR、JFK、GRU 路线比 LHR、GRU 路线便宜,所以用前者代替了后者。循环将在没有更便宜的费用,
并且没有其它可能路线时退出。
自从Oracle 9i开始,可以通过 SYS_CONNECT_BY_PATH 函数实现将父节点到当前行内容以“path”或者层次元素列表的形式显示出来。
自从Oracle 10g 中,还有其他更多关于层次查询的新特性 。例如,有的时候用户更关心的是每个层次分支中等级最低的内容。
那么你就可以利用伪列函数CONNECT_BY_ISLEAF来判断当前行是不是叶子。如果是叶子就会在伪列中显示“1”,
如果不是叶子而是一个分支(例如当前内容是其他行的父亲)就显示“0”。
在Oracle 10g 之前的版本中,如果在你的树中出现了环状循环(如一个孩子节点引用一个父亲节点),
Oracle 就会报出一个错误提示:“ ORA-01436: CONNECT BY loop in user data”。如果不删掉对父亲的引用就无法执行查询操作。
而在 Oracle 10g 中,只要指定“NOCYCLE”就可以进行任意的查询操作。与这个关键字相关的还有一个伪列——CONNECT_BY_ISCYCLE,
如果在当前行中引用了某个父亲节点的内容并在树中出现了循环,那么该行的伪列中就会显示“1”,否则就显示“0”。
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).
It is also being used when an sql execution plan is explained.
syntax:
select ... [start with initial-condition] connect by [nocycle] recurse-condition
level
With level it is possible to show the level in the hierarchical relation of all the data.
--oracle 9i
sys_connect_by_path
With sys_connect_by_path it is possible to show the entire path from the top level down to the 'actual' child.
--oracle 10g
connect_by_root
connect_by_root is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
connect_by_is_leaf
connect_by_isleaf is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
connect_by_iscycle
connect_by_is_cycle is a new operator that comes with Oracle 10g and enhances the ability to perform hierarchical queries.
--start with ... connect by ... 的处理机制
How must a start with ... connect by select statement be read and interpreted?
If Oracle encounters such an SQL statement, it proceeds as described in the following pseude code.
for rec in (select * from some_table) loop
if FULLFILLS_START_WITH_CONDITION(rec) then
RECURSE(rec, rec.child);
end if;
end loop;
procedure RECURSE (rec in MATCHES_SELECT_STMT, new_parent IN field_type) is
begin
APPEND_RESULT_LIST(rec);
for rec_recurse in (select * from some_table) loop
if FULLFILLS_CONNECT_BY_CONDITION(rec_recurse.child, new_parent) then
RECURSE(rec_recurse,rec_recurse.child);
end if;
end loop;
end procedure RECURSE;
created by zhouwf0726 2006.
*******************************************************************************/
--创建测试表,增加测试数据
create table test(superid varchar2(20),id varchar2(20)); insert into test values('0','1'); insert into test values('0','2'); insert into test values('1','11'); insert into test values('1','12'); insert into test values('2','21'); insert into test values('2','22'); insert into test values('11','111'); insert into test values('11','112'); insert into test values('12','121'); insert into test values('12','122'); insert into test values('21','211'); insert into test values('21','212'); insert into test values('22','221'); insert into test values('22','222'); commit; --层次查询示例 select level||'层',lpad(' ',level*5)||id id from test start with superid = '0' connect by prior id=superid; select level||'层',connect_by_isleaf,lpad(' ',level*5)||id id from test start with superid = '0' connect by prior id=superid; --给出两个以前在"数据库字符串分组相加之四"中的例子来理解start with ... connect by ... --功能:实现按照superid分组,把id用";"连接起来 --实现:以下两个例子都是通过构造2个伪列来实现connect by连接的。 /*------method one------*/ select superid,ltrim(max(sys_connect_by_path(id,';')),';') from( select superid,id,row_number() over(partition by superid order by superid) id1, row_number() over(order by superid) + dense_rank() over(order by superid) id2 from test ) start with id1=1 connect by prior id2 = id2 -1 group by superid order by superid; /*------method two------*/ select distinct superid,ltrim(first_value(id) over(partition by superid order by l desc),';') from( select superid,level l,sys_connect_by_path(id,';') id from( select superid,id,superid||rownum parent_rn,superid||to_char(rownum-1) rn from test ) connect by prior parent_rn = rn ); --下面的例子实现把一个整数的各个位上的数字相加,通过这个例子我们再次理解connect by. create or replace function f_digit_add(innum integer) return number is outnum integer; begin if innum<0 then return 0; end if; select sum(nm) into outnum from( select substr(innum,rownum,1) nm from dual connect by rownum<length(innum) ); return outnum; end f_digit_add; / select f_digit_add(123456) from dual;
/**********************************************************************************
***********************************************************************************
下面是关于SQL解决有向图问题,在这个例子中作者提到的错误
select * from fares connect by prior arrive = depart start with depart = 'LHR';
ERROR:
ORA-01436: CONNECT BY loop in user data
在oracle10g以上版本可以利用connect by的nocycle参数来解。有兴趣的朋友研究用一条sql实现有向图问题!
***********************************************************************************
**********************************************************************************/
一个常见的高级计算机科学问题可以在“有向图”的范畴之下描述。有向图是由一组向量和边所连接的一组有限的节点。
例如,一个节点可以想象为一座“城市”,而每个向量可以想象为两座城市间的一个“航线”。
有很多算法和论文讲到如何解决每种可能路线的遍历问题以及寻找最短路径或者最小代价路径的问题。
这些算法中大部分都是过程化的,或者是使用递归方面来解决的。然而 SQL 的声明性语言使得解决复杂的有向图问题更加容易,
而且不需要很多代码。
让我们以两座城市之间的航线为例子,创建一个表保存一些假想数据:
create table airports ( code char(3) constraint airports_pk primary key, description varchar2(200) ); insert into airports values ('LHR','London Heathrow, UK'); insert into airports values ('JFK','New York-Kennedy, USA'); insert into airports values ('GRU','Sao Paulo, Brazil'); create table fares ( depart char(3), arrive char(3), price number, constraint fares_pk primary key (depart,arrive), constraint fares_depart_fk foreign key (depart) references airports, constraint fares_arrive_fk foreign key (arrive) references airports ); insert into fares values('LHR','JFK',700); insert into fares values('JFK','GRU',600); insert into fares values('LHR','GRU',1500); insert into fares values('GRU','LHR',1600);
不能使用CONNECT BY 语法来解决如何从伦敦到圣保罗,因为在图中有数据产生一个环(从圣保罗飞回):
select * from fares connect by prior arrive = depart start with depart = 'LHR';
ERROR:
ORA-01436: CONNECT BY loop in user data
要解决有向图问题,我们需要创建一个临时表来保存两个节点之间所有可能的路径。我们必须注意不复制已经处理过的路径,
而且在这种情况下,我们不想路径走回开始处的同一个地点。我还希望跟踪到达目的地所需航程的数目,以及所走路线的描述。
临时表使用以下脚本创建:
create global temporary table faretemp ( depart char(3), arrive char(3), hops integer, route varchar2(30), price number, constraint faretemp_pk primary key (depart,arrive) );
一个简单的视图可以在稍微简化这个例子中使用的代码。视图可以根据 fares 表中的单个航程计算从 faretemp 表中的一个路径
到达一下一个航程的数据:
create or replace view nexthop as select src.depart, dst.arrive, src.hops+1 hops, src.route||','||dst.arrive route, src.price + dst.price price from faretemp src,fares dst where src.arrive = dst.depart and dst.arrive != src.depart; / show errors;
这个算法相当简单。首先,使用 fares 表中的数据填充 faretemp 表,作为初始的航程。然后,取到我们刚才插入的所有数据,
使用它们建立所有可能的二航程(two-hop)路径。重复这一过程,直至在两个节点之间创建了新路径。
循环过程将在节点间所有可能的路径都被描述之后退出。如果我们只对某个开始条件感兴趣,
那么我们还可以限制第一次的插入从而减少装载数据的量。下面是发现路径的代码:
truncate table faretemp; begin -- initial connections insert into faretemp select depart,arrive,1,depart||','||arrive,price from fares; while sql%rowcount > 0 loop insert into faretemp select depart,arrive,hops,route,price from nexthop where (depart,arrive) not in (select depart,arrive from faretemp); end loop; end; / show errors; select * from faretemp order by depart,arrive;
可以在表 A 中查看输出。
前面的数据有一个小问题。数据是点之间最短路径(最小航程数)的集合。然而,从伦敦到圣保罗的航程却不是最便宜的一个。
要解决最便宜的费用问题,需要对我们的循环做一个改进,当在一个航程中发现一个更便宜的路线时使用这个路线代替原来的路线。
修改后的代码如下:
truncate table faretemp; declare l_count integer; begin -- initial connections insert into faretemp select depart,arrive,1,depart||','||arrive,price from fares; l_count := sql%rowcount; while l_count > 0 loop update faretemp set (hops,route,price) = (select hops,route,price from nexthop where depart = faretemp.depart and arrive = faretemp.arrive) where (depart,arrive) in (select depart,arrive from nexthop where price < faretemp.price); l_count := sql%rowcount; insert into faretemp select depart,arrive,hops,route,price from nexthop where (depart,arrive) not in (select depart,arrive from faretemp); l_count := l_count + sql%rowcount; end loop; end; / show errors; select * from faretemp order by depart,arrive;
可能在表 B中查看输出。
算法发现LHR、JFK、GRU 路线比 LHR、GRU 路线便宜,所以用前者代替了后者。循环将在没有更便宜的费用,
并且没有其它可能路线时退出。
发表评论
-
erwin连接mysql数据库建表
2012-09-09 00:45 6161Erwin是不支持直接连接mysql的,但是可以通过odbc实 ... -
ibatis级联查询
2012-08-12 21:05 3738ibatis 级联查询的配置: <?xml versio ... -
ERwin模型导入oracle数据库
2012-05-19 15:09 16091.选择物理模型视图Physical: 2.选择Too ... -
什么是两阶段提交协议
2012-05-08 16:58 1067两阶段提交协议 实现分布式事务的关键就是两阶段提交协议。在此 ... -
Abator —— IBatis 代码生成工具
2012-04-03 18:31 19351、在eclipse安装abator插件http://ibat ... -
使用iBatis中报 java.sql.SQLException: 无效的列类型异常
2011-12-15 14:46 2244<!--Content表 插入应的 ... -
如何在Eclipse中安装和使用ibatis插件Abator
2011-12-01 21:26 49761、获得abator: http://ibatis. ... -
PowerDesigner生成数据库建表sql脚本
2011-11-06 09:30 3532一、生成sql server 2000或o ... -
MySQL没有boolean类型,由tinyint(1)替代
2011-04-01 00:34 4850MySQL没有boolean类型。这也是比较奇怪的现象。例: ... -
oracle查看锁表进程及解锁
2010-11-05 15:13 2552查看锁表进程SQL语句1: select sess.sid, ... -
oracle 10g正则表达式 REGEXP_LIKE 用法
2010-10-29 13:42 2155ORACLE中的支持正则表达式的函数主要有下面四个: 1,RE ... -
oracle中的exists 和not exists 用法及查询效率
2010-10-29 13:36 1797有两个简单例子,以说明 “exists”和“in”的效率问题 ... -
创建DBLINK
2010-10-11 16:23 1685创建DBLINK 有两种方法 一: 动态DB LINK ... -
不drop表的情况下进行数据移植(Oracle)
2010-09-09 13:59 1175项目组在开发过程中需要经常在多个数据库中进行数据移植操作,包括 ... -
IMP和EXP命令(Oracle数据导入导出)
2010-09-08 15:04 1565Oracle数据导入导出imp/exp就相当于orac ... -
Oracle递归树select...start with... connect by ...prior 理解
2010-07-26 11:06 2371Oracle树查询的最重要的 ... -
Oracle session
2010-07-08 00:28 23981.如何查看session级的等待事件? 当我们对数据库的性 ... -
常见数据库字段类型与java.sql.Types的对应
2010-06-10 17:53 2230Oracle与java.sql.Types的对应 Oracl ... -
优化sql语句,提升程序执行效率
2009-09-16 14:57 1257在学习SQL时看到的一片很好的文章,特贴出来和大家一起分享! ... -
关于事务的隔离级别
2009-09-08 11:26 1116事务的提出主要是为了 ...
相关推荐
#### `Start With.Connect By Prior`语法详解 `Start With.Connect By Prior`的基本语法结构如下: ```sql SELECT * FROM START WITH <start_condition> CONNECT BY PRIOR <parent_column> = ; ``` 其中: - `...
### Oracle中的START WITH CONNECT BY PRIOR 用法详解 #### 一、概念介绍 在Oracle数据库中,`START WITH` 和 `CONNECT BY PRIOR` 是两个非常强大的特性,主要用于处理具有层级结构的数据。这两个特性可以帮助我们...
### Connect By Prior 数据库树的应用 #### 概述 在关系型数据库中,尤其是在 Oracle 数据库环境下,构建和查询层次结构数据是一项常见的需求。通过使用 `Connect By Prior` 存储过程,我们可以有效地处理这类问题...
### Oracle数据库中的START WITH 和 CONNECT BY 用法详解 在Oracle数据库中,处理层次结构数据时,`START WITH` 和 `CONNECT BY` 是非常有用的两个关键字。这些关键字可以帮助我们在查询时构建出树形或者层级结构的...
在Oracle数据库中,递归查询可以通过`START WITH CONNECT BY PRIOR`语句实现。此语句允许用户按照树状结构来检索数据。 ##### 1. `START WITH CONNECT BY PRIOR`用法详解 **基本语法**: ```sql SELECT * FROM ...
### Oracle中的Connect By Prior递归算法详解 #### 一、Connect By Prior 子句概述 在Oracle数据库中,`Connect By Prior`子句是一种非常有用的工具,尤其在处理具有层次结构的数据时。它允许用户以一种简洁的方式...
Oracle数据库通过提供`START WITH...CONNECT BY`语句来简化这类查询操作。此功能最早出现在Oracle 8.1.6版本,并一直沿用至今,成为处理递归查询的强大工具之一。 #### 二、Connect By与Start With详解 1. **...
在Oracle中,要查询树形结构的数据,可以使用`START WITH`和`CONNECT BY PRIOR`语句。`START WITH`用于指定查询的起始节点,而`CONNECT BY PRIOR`则定义了节点之间的父子关系。 - **`START WITH`**:指定查询的根...
在Oracle中,递归查询主要通过`START WITH`和`CONNECT BY PRIOR`子句实现。 ### 二、递归查询的基本语法 递归查询的基本语法如下: ```sql SELECT column_name(s) FROM table_name START WITH condition CONNECT ...
CONNECT BY PRIOR`语句可以从根节点开始遍历整个树形结构。例如,从编号为7839的员工开始查询: ```sql SELECT empno, mgr, ename, job FROM emp START WITH empno = 7839 CONNECT BY PRIOR empno = mgr; ``` 这里`...
在Oracle数据库中,递归查询主要依赖于`START WITH`和`CONNECT BY PRIOR`两个关键语法。本文将详细介绍这些概念以及具体的使用方法。 #### 二、创建示例表 为了更好地理解Oracle中的递归查询,我们首先需要创建...
在Oracle中,实现树状查询的核心在于使用`CONNECT BY`子句与`START WITH`子句。这两个子句共同作用,可以按照定义的层级关系遍历表中的记录,并按照树状结构进行排序和展示。 #### 1. `START WITH`子句 `START ...
`LEVEL`伪列表示从根节点开始的层级数,`START WITH MGR IS NULL`确保从最高级别的管理者开始查询,而`CONNECT BY PRIOR EMPNO = MGR`定义了父子关系。 #### 四、结果解析 查询结果展示了公司内部的管理层次,每一...
通过合理地定义`START WITH`和`CONNECT BY PRIOR`子句,可以方便地检索出任何复杂的层级结构数据。此外,结合`ORDER SIBLINGS BY`子句,还可以对查询结果进行排序,以满足不同场景的需求。希望本文能帮助读者更好地...
### Oracle高级语句详解 #### 一、With临时表 **功能概述:** `WITH` 子句在SQL中提供了一种定义临时结果集的方法,这些结果集可以在后续的查询中重复使用,从而提高查询效率。`WITH` 临时表在复杂的查询中尤其...
Oracle 数据库在处理树形数据结构时,提供了一种强大的查询方法,即 `SELECT...START WITH...CONNECT BY...PRIOR` 语法。这种查询模式允许我们以递归方式遍历和检索具有层级关系的数据,例如组织结构、产品分类或者...
在Oracle中,递归查询通常使用`START WITH`和`CONNECT BY PRIOR`这两个关键字来实现。`START WITH`用于指定递归的起始条件,而`CONNECT BY PRIOR`则定义了层级之间的关系。 例如,假设我们有一个表格`test`,包含...
2. **CONNECT BY PRIOR 子句**:用于定义递归关系,即如何从上一级记录推导出下一级记录。 3. **LEVEL 关键字**:用于获取当前记录的层级编号,这对于控制递归深度非常有用。 #### 三、示例表的创建与数据插入 首先...