分享
 
 
 

Oracle Database 10g 中新特性

王朝oracle·作者佚名  2008-05-31
窄屏简体版  字體: |||超大  

这篇文章具体说明了 Oracle Database 10g 中三个新的 CONNECT BY 特性。这些示例使用的表和数据与我之前的文章中所用的表和数据相同。您可能希望读那篇文章来重温一下 CONNECT BY 查询的工作方式。

问题的起源

假如您阅读了我先前的文章,则您已经了解了如何在层次查询中使用 PRIOR 操作符来从一个父行返回列数据。能够返回到根行有时是很方便的。Oracle Database 10g 中新增的 CONNECT_BY_ROOT 操作符使您能够从一个层次结构中的任何位置引用根行的值。

CONNECT_BY_ROOT 的一个用途是用来识别所有包含了一个给定部分的产品。设想您为一个制造公司工作。您发现 1019 号部件有缺陷,消费品安全委员会命令您收回已售出的所有包含了该部件的产品。您的第一个任务是确定您担心的是哪些产品。您可以通过发出以下查询来开始,作为第一步工作:

SELECT assembly_id, assembly_name

FROM bill_of_materials

WHERE part_number = 1019

START WITH parent_assembly IS NULL

CONNECT BY parent_assembly = PRIOR assembly_id;

ASSEMBLY_ID ASSEMBLY_NAME

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

141 Lock

144 Lock

这些结果看起来似乎是合理的,但它们存在一个问题。1019 号部件是一个锁,但事实上它被用在两个锁组件中。然而,那些锁组件随后被用在左边和右边的门组件内,门组件随后又被用在一个车身组件中,车身组件最后被用来制造一辆汽车,而我们售出的和要关心的是汽车。您不想要 1019 号部件的直接父组件;您想要 1019 号部件最终的父亲。幸运的是,您的数据库刚刚升级到 Oracle Database 10g,因此您可以利用新的 CONNECT_BY_ROOT 操作符:

SELECT DISTINCT CONNECT_BY_ROOT assembly_id,

CONNECT_BY_ROOT assembly_name

FROM bill_of_materials

WHERE part_number = 1019

START WITH parent_assembly IS NULL

CONNECT BY parent_assembly = PRIOR assembly_id;

CONNECT_BY_ROOTASSEMBLY_ID CONNECT_BY_ROOTASSEMBLY

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

100 Automobile

该查询大部分和以前相同。只有两点区别:使用了DISTINCT;在 SELECT 列表中的每一个列名前面出现了 CONNECT_BY_ROOT。CONNECT_BY_ROOT 操作符输出我们关心的那个部件的最终父组件 ID 和名称。DISTINCT 要害字防止一个产品在多个组件中包含了相同部件时被多次列出。因此,结果是您的汽车在它的右边和左边的门里都包含了锁。

寻根究底

层次数据经常是深层嵌套的。考虑这样一个问题:为机械师提供一辆汽车中的组件和部件的一个嵌套列表。汽车包含大量的部件。机械师很少希望马上了解所有组件和部件的详情。那样的一个列表不仅将使人不知所措,而且当用户只需要该数据的一部分时,从数据库检索那样一个组件和部件的完整列表以及跨网络传递那样的信息也是非常低效的。相反,您可能选择一开始仅提供顶层的组件,然后让用户从那里开始深入到层次结构内部。例如,您可能一开始提供给我们的用户下列查询的结果:

SELECT ASSEMBLY_ID,

RPAD(' ', 2*(LEVEL-1)) assembly_name assembly_name,

quantity

FROM bill_of_materials

WHERE LEVEL

START WITH assembly_id = 100

CONNECT BY parent_assembly = PRIOR assembly_id;

ASSEMBLY_ID ASSEMBLY_NAME QUANTITY

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

100 Automobile

110 Combustion Engine 1

120 Body1

130 Interior1

通过查看第一级的组件,我们的用户现在能够确定他们是否要进一步向下查看。当有更多的数据要查看时,可以通过将组件名实现为 Web 链接来实现向下查看,或者您可以像 Windows 应用中常见的那样实现一个树状控制。且慢!您怎么知道什么时候一个组件会有更多的数据?什么时候向下查看是可能的?当用户试图从该层次结构的底部向下查看时,您可以让他们试着向下查看到任何组件中,然后给他们一条“没有更多数据”的消息,但这是一种生硬的解决办法,无疑将使他们感到灰心。最好能够提前了解向下查看是否可能。Oracle Database 10g 使我们能够通过 CONNECT_BY_ISLEAF 虚拟列来达到这一目的。

您可以使用以下查询来开始:

SELECT ASSEMBLY_ID,

RPAD(' ', 2*(LEVEL-1)) assembly_name assembly_name,

quantity, CONNECT_BY_ISLEAF

FROM bill_of_materials

WHERE LEVEL

START WITH assembly_id = 100

CONNECT BY parent_assembly = PRIOR assembly_id;

ASSEMBLY_ID ASSEMBLY_NAME QUANTITY CONNECT_BY_ISLEAF

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

100 Automobile 0

110 Combustion Engine1 0

120 Body 1 0

130 Interior 1 0

CONNECT_BY_ISLEAF 返回的零指示在该列表中显示的组件中没有一个是叶节点。换句话说,向下查看到它们的任何一个中都是无效的。假定用户向下查看到 Combustion Engine 中。那么您可以发出以下查询来获取组成发动机的子组件:

SELECT ASSEMBLY_ID,

RPAD(' ', 2*(LEVEL-1)) assembly_name assembly_name,

quantity, CONNECT_BY_ISLEAF

FROM bill_of_materials

WHERE LEVEL = 2

START WITH assembly_id = 110

CONNECT BY parent_assembly = PRIOR assembly_id;

ASSEMBLY_ID ASSEMBLY_NAME QUANTITY CONNECT_BY_ISLEAF

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

111 Piston 6 1

112 Air Filter 1 1

113 Spark Plug 6 1

114 Block1 1

115 Starter System 1 0

该查询和以前几乎是相同的。Combustion Engine 的 START WITH 组件 ID 值变为 110,该查询非凡请求 LEVEL = 2。在这个节点上,您不需要 LEVEL = 1,因为那将再次返回 Combustion Engine 的行,而您已经得到那一行了。

这次,您看到 CONNECT_BY_ISLEAF 有两个值。Piston、Air Filter、Spark Plug 和 Block 的值为 1 指示那些组件是叶节点,且其下没有发现更多的组件。了解到这一点,您就可以调整我们的显示内容,这样用户就知道不用徒劳地在那些组件上向下钻取。另一方面,Starter System 的 CONNECT_BY_ISLEAF 值为 0,这指示仍然存在要检索的子组件。

跳出循环

只要您利用层次数据进行工作,您就可能碰到一个循环的层次。比如说,有人可能将一辆汽车的父组件设为一个火花塞:

UPDATE bill_of_materials

SET parent_assembly = 113

WHERE assembly_id=100;

尝试在该组件树中查询 "Automobile" 现在就将失败:

SELECT RPAD(' ', 2*(LEVEL-1)) assembly_name assembly_name,

quantity

FROM bill_of_materials

START WITH assembly_id = 100

CONNECT BY parent_assembly = PRIOR assembly_id;

ERROR:

ORA-01436:CONNECT BY loop in user data

当您获得像这样的一条错误消息时,您可以使用 CONNECT_BY_ISCYCLE 虚拟列来确定引起问题的行的位置。要做到这一点,您还必须添加 NOCYCLE 要害字到 CONNECT BY 子句中,防止数据库进入层次结构中的任何循环:

SELECT RPAD(' ', 2*(LEVEL-1)) assembly_name assembly_name,

quantity, CONNECT_BY_ISCYCLE

FROM bill_of_materials

START WITH assembly_id = 100

CONNECT BY NOCYCLE parent_assembly = PRIOR assembly_id;

ASSEMBLY_NAMEQUANTITY CONNECT_BY_ISCYCLE

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

Automobile 0

Combustion Engine 10

Piston60

Air Filter10

Spark Plug61

Block 10

注重,CONNECT_BY_ISCYCLE 为 "Spark Plug" 行返回一个 1。

当您使用 NOCYCLE 时,数据库通过层次跟踪它的路径,不断检查确保它不会进入循环。在完成从 "Automobile" 到 "Combustion Engine" 再到 "Spark Plug" 的遍历后,数据库发现 "Spark Plug" 的孩子是 "Automobile",该行所处的路径通向 "Spark Plug"。这样的一行代表一个循环。NOCYCLE 防止数据库进入循环,CONNECT_BY_ISCYCLE 返回一个 1 来指示出现循环的行

 
 
 
免责声明:本文为网络用户发布,其观点仅代表作者个人观点,与本站无关,本站仅提供信息存储服务。文中陈述内容未经本站证实,其真实性、完整性、及时性本站不作任何保证或承诺,请读者仅作参考,并请自行核实相关内容。
2023年上半年GDP全球前十五强
 百态   2023-10-24
美众议院议长启动对拜登的弹劾调查
 百态   2023-09-13
上海、济南、武汉等多地出现不明坠落物
 探索   2023-09-06
印度或要将国名改为“巴拉特”
 百态   2023-09-06
男子为女友送行,买票不登机被捕
 百态   2023-08-20
手机地震预警功能怎么开?
 干货   2023-08-06
女子4年卖2套房花700多万做美容:不但没变美脸,面部还出现变形
 百态   2023-08-04
住户一楼被水淹 还冲来8头猪
 百态   2023-07-31
女子体内爬出大量瓜子状活虫
 百态   2023-07-25
地球连续35年收到神秘规律性信号,网友:不要回答!
 探索   2023-07-21
全球镓价格本周大涨27%
 探索   2023-07-09
钱都流向了那些不缺钱的人,苦都留给了能吃苦的人
 探索   2023-07-02
倩女手游刀客魅者强控制(强混乱强眩晕强睡眠)和对应控制抗性的关系
 百态   2020-08-20
美国5月9日最新疫情:美国确诊人数突破131万
 百态   2020-05-09
荷兰政府宣布将集体辞职
 干货   2020-04-30
倩女幽魂手游师徒任务情义春秋猜成语答案逍遥观:鹏程万里
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案神机营:射石饮羽
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案昆仑山:拔刀相助
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案天工阁:鬼斧神工
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案丝路古道:单枪匹马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:与虎谋皮
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:李代桃僵
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案镇郊荒野:指鹿为马
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:小鸟依人
 干货   2019-11-12
倩女幽魂手游师徒任务情义春秋猜成语答案金陵:千金买邻
 干货   2019-11-12
 
推荐阅读
 
 
 
>>返回首頁<<
 
靜靜地坐在廢墟上,四周的荒凉一望無際,忽然覺得,淒涼也很美
© 2005- 王朝網路 版權所有