Java开发网 - [en][转贴]如何用CONNECT BY查询等级数据

Java开发网

您没有登录

» Java开发网 » Database/JDBC/SQL/JDO/Hibernate » Oracle

打印话题 寄给朋友 订阅主题

作者

[en][转贴]如何用CONNECT BY查询等级数据 [精华]

itsyh

发贴: 0
积分: 0

于 2002-08-16 21:44

Querying Hierarchical Data with CONNECT BY

Business intelligence systems need to perform complex queries efficiently, and some of these queries must access hierarchical data. For example, an employee table may hold an organization's hierarchy information by listing each employee's name and manager. How can we generate an organization chart based on the hierarchical data? Oracle9i can readily perform the necessary hierarchical query with the CONNECT BY clause. The CONNECT BY clause specifies the relationship between parent rows and child rows in the hierarchy and the starting point of the hierarchy.
Here is a simple example of a hierarchical query:

SELECT employee_name, manager_name, LEVEL
FROM employees
START WITH employee_name = 'King'
CONNECT BY PRIOR employee_id = manager_id;

This query returns rows from the table employees in a hierarchical order starting with the employee whose name is 'King.' The LEVEL keyword is used to show the level of the hierarchy. A partial set of results is shown below:

EMPLOYEE_NAME MANAGER_NAME LEVEL
King 1
Greenberg King 2
Faviet Greenberg 3
Chen Greenberg 3
Sciarra Greenberg 3
Urman Greenberg 3
Popp Greenberg 3

In Oracle9i, significant improvements have been made to the CONNECT BY feature.

In Oracle8i, the CONNECT BY clause could not be used in a query that contained a join. In Oracle9i, this restriction has been removed. The following is an example of a query that can be used in Oracle9i.
SELECT employee_name, manager_name, dept_name
FROM employee, dept
WHERE employee.deptno = dept.deptno
START WITH employee_name = 'KING'
CONNECT BY PRIOR employee_id = manager_id;

This query returns all employees, their manager name and their department name starting with the employee 'KING.'

The CONNECT BY clause can be used with all objects in Oracle9i. That is, it can be used with views, external tables, etc. In Oracle8i, CONNECT BY could be used only with tables.
A new keyword, 'SIBLINGS,' can be used to order all child rows of a given parent by some criteria. The query below gives an example of SIBLINGS:
SELECT employee_name, manager_name, dept_name
FROM employee, dept
WHERE employee.deptno = dept.deptno
START WITH employee_name = 'KING'
CONNECT BY PRIOR employee_id = manager_id
ORDER SIBLINGS BY hire_date;

This query orders all employees reporting to a common manager by their hire date.

The new function SYS_CONNECT_BY_PATH can be used to describe the path to any row in the hierarchy. This function takes 2 parameters. The first is the column for which we need the path. The second is the list separator to use when generating the path. Here is a brief example:
SELECT employee_name,
SYS_CONNECT_BY_PATH(employee_name, '/') "PATH"
FROM employee
START WITH employee_name = 'KING'
CONNECT BY PRIOR employee_id = manager_id;

This query returns the employee name and the management chain for each employee starting with the employee 'KING.' The results, using the same values shown in the first example, would look like:

EMPLOYEE_NAME PATH
King /King
Greenberg /King/Greenberg
Faviet /King/Greenberg/Faviet
Chen /King/Greenberg/Chen
Sciarra /King/Greenberg/Sciarra
Urman /King/Greenberg/Urman
Popp /King/Greenberg/Popp

The CONNECT BY enhancements in Oracle9i enable quicker performance, greater flexibility and more productive development when querying hierarchical data. These enhancements are just one aspect of the many Oracle9i features which support complex business intelligence queries.

More Info Oracle9i SQL Reference Release 1 (9.0.1) Chapter 7 - SQL Queries and Other SQL Statements - Hierarchical Queries

Oracle9i Daily Features
Archives




原文连接：
http://otn.oracle.com/products/oracle9i/daily/jun18.html

九佰 edited on 2003-07-25 17:40

作者	回复： [en][转贴]如何用CONNECT BY查询等级数据 [Re:itsyh]
C 发贴: 0 积分: 0	于 2002-08-17 02:18 帮你改了一下标题，Oracle 9i新特性这个标题太笼统了一点。

已读帖子

新的帖子

被删除的帖子