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;
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;
通过查看第一级的组件,我们的用户现在能够确定他们是否要进一步向下查看。当有更多的数据要查看时,可以通过将组件名实现为 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 <= 2
START WITH assembly_id = 100
CONNECT BY parent_assembly = PRIOR assembly_id;