有关家族树的用法解释?

select lpad(' ',4*(level-1)) ¦ &brvbarename name,empno,mgr from emp start with mgr=7788
connect by prior mgr=empno
能详细解释一下关于家族树方面的信息吗?最好能把这条语句分段解释,prior是用来干什么的?
---------------------------------------------------------------

http://www.adp-gmbh.ch/ora/sql/connect_by.html
---------------------------------------------------------------

就是上下级的信息放在一张表里,但是有一个字段来关联上下级的信息.
---------------------------------------------------------------

connect by prior mgr=empno

表示empno的上级是mgr

level是伪列,表示深度
---------------------------------------------------------------

prior代表等于上一行的字段
prior mgr=empno
empno等于上一行的mgr

START WITH specifies the root row(s) of the hierarchy.
CONNECT BY specifies the relationship between parent rows and child rows of the hierarchy. In a hierarchical query, one expression in condition must be qualified with the PRIOR operator to refer to the parent row. For example,
... PRIOR expr = expr
or
... expr = PRIOR expr

If the CONNECT BY condition is compound, then only one condition requires the PRIOR operator. For example:

CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id

In addition, the CONNECT BY condition cannot contain a subquery.

PRIOR is a unary operator and has the same precedence as the unary + and - arithmetic operators. It evaluates the immediately following expression for the parent row of the current row in a hierarchical query.

PRIOR is most commonly used when comparing column values with the equality operator. (The PRIOR keyword can be on either side of the operator.) PRIOR causes Oracle to use the value of the parent row in the column. Operators other than the equal sign (=) are theoretically possible in CONNECT BY clauses. However, the conditions created by these other operators can result in an infinite loop through the possible combinations. In this case Oracle detects the loop at run time and returns an error.

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

http://download-west.oracle.com/docs/cd/B10501_01/server.920/a96540/queries4a.htm#2053937

Published At
Categories with 数据库类
Tagged with
comments powered by Disqus