oracle connect by 官方文档翻译
来源:互联网 发布:美国女键盘手走红网络 编辑:程序博客网 时间:2024/06/05 07:03
http://docs.oracle.com/cd/B19306_01/server.102/b14200/queries003.htm,官方文档(英文版)
If a table contains hierarchical data, then you can select rows in a hierarchical order using the hierarchical query clause:
hierarchical_query_clause::=
Description of the illustration hierarchical_query_clause.gif
START
WITH
specifies the root row(s) of the hierarchy.
CONNECT
BY
specifies the relationship between parent rows and child rows of the hierarchy.
The
NOCYCLE
parameter instructs Oracle Database to return rows from a query even if aCONNECT
BY
LOOP
exists in the data. Use this parameter along with theCONNECT_BY_ISCYCLE
pseudocolumn to see which rows contain the loop. Please refer to CONNECT_BY_ISCYCLE Pseudocolumn for more information.In a hierarchical query, one expression in
condition
must be qualified with thePRIOR
operator to refer to the parent row. For example,... PRIOR expr = expror... expr = PRIOR expr
If the
CONNECT
BY
condition
is compound, then only one condition requires thePRIOR
operator, although you can have multiplePRIOR
conditions. For example:CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...CONNECT BY PRIOR employee_id = manager_id and PRIOR account_mgr_id = customer_id ...
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. (ThePRIOR
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 inCONNECT
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.
Both the CONNECT
BY
condition and the PRIOR
expression can take the form of an uncorrelated subquery. However, the PRIOR
expression cannot refer to a sequence. That is, CURRVAL
and NEXTVAL
are not valid PRIOR
expressions.
You can further refine a hierarchical query by using the CONNECT_BY_ROOT
operator to qualify a column in the select list. This operator extends the functionality of the CONNECT
BY
[PRIOR
] condition of hierarchical queries by returning not only the immediate parent row but all ancestor rows in the hierarchy.
See Also:
CONNECT_BY_ROOT for more information about this operator and "Hierarchical Query Examples"Oracle processes hierarchical queries as follows:
A join, if present, is evaluated first, whether the join is specified in the
FROM
clause or withWHERE
clause predicates.The
CONNECT
BY
condition is evaluated.Any remaining
WHERE
clause predicates are evaluated.
Oracle then uses the information from these evaluations to form the hierarchy using the following steps:
Oracle selects the root row(s) of the hierarchy--those rows that satisfy the
START
WITH
condition.Oracle selects the child rows of each root row. Each child row must satisfy the condition of the
CONNECT
BY
condition with respect to one of the root rows.Oracle selects successive generations of child rows. Oracle first selects the children of the rows returned in step 2, and then the children of those children, and so on. Oracle always selects children by evaluating the
CONNECT
BY
condition with respect to a current parent row.If the query contains a
WHERE
clause without a join, then Oracle eliminates all rows from the hierarchy that do not satisfy the condition of theWHERE
clause. Oracle evaluates this condition for each row individually, rather than removing all the children of a row that does not satisfy the condition.Oracle returns the rows in the order shown in Figure 9-1. In the diagram, children appear below their parents. For an explanation of hierarchical trees, see Figure 3-1, "Hierarchical Tree".
Figure 9-1 Hierarchical Queries
Description of "Figure 9-1 Hierarchical Queries"
To find the children of a parent row, Oracle evaluates the PRIOR
expression of the CONNECT
BY
condition for the parent row and the other expression for each row in the table. Rows for which the condition is true are the children of the parent. The CONNECT
BY
condition can contain other conditions to further filter the rows selected by the query. The CONNECT
BY
condition cannot contain a subquery.
If the CONNECT
BY
condition results in a loop in the hierarchy, then Oracle returns an error. A loop occurs if one row is both the parent (or grandparent or direct ancestor) and a child (or a grandchild or a direct descendent) of another row.
Note:
In a hierarchical query, do not specify eitherORDER
BY
or GROUP
BY
, as they will destroy the hierarchical order of the CONNECT
BY
results. If you want to order rows of siblings of the same parent, then use the ORDER
SIBLINGS
BY
clause. See order_by_clause.Hierarchical Query Examples
CONNECT BY Example The following hierarchical query uses the CONNECT
BY
clause to define the relationship between employees and managers:
SELECT employee_id, last_name, manager_id FROM employees CONNECT BY PRIOR employee_id = manager_id;EMPLOYEE_ID LAST_NAME MANAGER_ID----------- ------------------------- ---------- 101 Kochhar 100 108 Greenberg 101 109 Faviet 108 110 Chen 108 111 Sciarra 108 112 Urman 108 113 Popp 108 200 Whalen 101...
LEVEL Example The next example is similar to the preceding example, but uses the LEVEL
pseudocolumn to show parent and child rows:
SELECT employee_id, last_name, manager_id, LEVEL FROM employees CONNECT BY PRIOR employee_id = manager_id;EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL----------- ------------------------- ---------- ---------- 101 Kochhar 100 1 108 Greenberg 101 2 109 Faviet 108 3 110 Chen 108 3 111 Sciarra 108 3 112 Urman 108 3 113 Popp 108 3...
START WITH Examples The next example adds a START
WITH
clause to specify a root row for the hierarchy and an ORDER
BY
clause using the SIBLINGS
keyword to preserve ordering within the hierarchy:
SELECT last_name, employee_id, manager_id, LEVEL FROM employees START WITH employee_id = 100 CONNECT BY PRIOR employee_id = manager_id ORDER SIBLINGS BY last_name;LAST_NAME EMPLOYEE_ID MANAGER_ID LEVEL------------------------- ----------- ---------- ----------King 100 1Cambrault 148 100 2Bates 172 148 3Bloom 169 148 3Fox 170 148 3Kumar 173 148 3Ozer 168 148 3Smith 171 148 3De Haan 102 100 2Hunold 103 102 3Austin 105 103 4Ernst 104 103 4Lorentz 107 103 4Pataballa 106 103 4Errazuriz 147 100 2Ande 166 147 3Banda 167 147 3...
In the hr.employees
table, the employee Steven King is the head of the company and has no manager. Among his employees is John Russell, who is the manager of department 80. If we update the employees
table to set Russell as King's manager, we will create a loop in the data:
UPDATE employees SET manager_id = 145 WHERE employee_id = 100;SELECT last_name "Employee", LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM employees WHERE level <= 3 AND department_id = 80 START WITH last_name = 'King' CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4; 2 3 4 5 6 7 ERROR:ORA-01436: CONNECT BY loop in user data
The NOCYCLE
parameter in the CONNECT
BY
condition causes Oracle to return the rows in spite of the loop. The CONNECT_BY_ISCYCLE
pseudocolumn shows you which rows contain the cycle:
SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle", LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM employees WHERE level <= 3 AND department_id = 80 START WITH last_name = 'King' CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4;Employee Cycle LEVEL Path------------------------- ------ ------ -------------------------Russell 1 2 /King/RussellTucker 0 3 /King/Russell/TuckerBernstein 0 3 /King/Russell/BernsteinHall 0 3 /King/Russell/HallOlsen 0 3 /King/Russell/OlsenCambrault 0 3 /King/Russell/CambraultTuvault 0 3 /King/Russell/TuvaultPartners 0 2 /King/PartnersKing 0 3 /King/Partners/KingSully 0 3 /King/Partners/SullyMcEwen 0 3 /King/Partners/McEwen...
CONNECT_BY_ROOT Examples The following example returns the last name of each employee in department 110, each manager above that employee in the hierarchy, the number of levels between manager and employee, and the path between the two:
SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager", LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM employees WHERE LEVEL > 1 and department_id = 110 CONNECT BY PRIOR employee_id = manager_id;Employee Manager Pathlen Path--------------- ------------ ---------- -----------------------------------Higgins Kochhar 1 /Kochhar/HigginsGietz Kochhar 2 /Kochhar/Higgins/GietzGietz Higgins 1 /Higgins/GietzHiggins King 2 /King/Kochhar/HigginsGietz King 3 /King/Kochhar/Higgins/Gietz
The following example uses a GROUP
BY
clause to return the total salary of each employee in department 110 and all employees below that employee in the hierarchy:
SELECT name, SUM(salary) "Total_Salary" FROM ( SELECT CONNECT_BY_ROOT last_name as name, Salary FROM employees WHERE department_id = 110 CONNECT BY PRIOR employee_id = manager_id) GROUP BY name;NAME Total_Salary------------------------- ------------Gietz 8300Higgins 20300King 20300Kochhar 20300
START WITH:指定层次的根行(次)。
CONNECT BY:指定父行和子行的层级之间的关系。
- 该NOCYCLE参数指示返回Oracle数据库中通过CONNECT BY LOOP查询行。使用随CONNECT_BY_ISCYCLE伪列,看看哪些行包含的循环。请参考CONNECT_BY_ISCYCLE伪列获取更多信息。
- 在一个分层的查询,表达式的条件:必须使用PRIOR运算符来、匹配父行。例如,
... PRIOR expr = expror... expr = PRIOR expr
- 如果CONNECT BY条件的复合条件,但是只有一个条件能够使用PRIOR,
,虽然可以有多个PRIOR。例如:
CONNECT BY last_name != 'King' AND PRIOR employee_id = manager_id ...CONNECT BY PRIOR employee_id = manager_id and PRIOR account_mgr_id = customer_id ...
- PRIOR是一元运算符,并且具有相同的优先级为一元 + 和 - 运算符。它评估了紧随其后的表达式当前行的分层查询父行。
- 与相等运算符比较列值时,事先是最常用的。 (现有关键字可以是在操作者的任一侧。)PRIOR导致Oracle使用父行的值的列。运营商不是等号(=)等在理论上是可能的CONNECT BY子句。然而,由这些其它运营商创造了条件可能会导致通过可能的组合,无限循环。在这种情况下的Oracle检测回路,在运行时间,并返回一个错误。
两者中的CONNECT BY条件和先表达可以采取不相关子查询的形式。然而,现有的表达不能引用序列。也就是说,CURRVAL和NEXTVAL不是之前有效表达。
您可以通过使用CONNECT_BY_ROOT 操作有资格在选择列表中的列进一步细化分层查询。此操作通过返回,不仅直接父行,但在层次结构中的所有祖先行延伸层次查询的CONNECT BY[上一页]状态的功能。
另请参见:
CONNECT_BY_ROOT有关此操作符和“分层查询示例”的详细信息
Oracle进程分层查询,如下所示:
一个连接,如果存在的话,首先评估是否加入在FROM子句或WHERE子句,谓词中指定。
在CONNECT BY条件进行评估。
任何剩余的WHERE子句谓词进行评估。
甲骨文然后使用这些评价的信息通过以下步骤而形成的层次结构:
甲骨文选择层次的根行(S) - 满足与状态的开始的那些行。
甲骨文选择子行每一根一行。每个子行必须满足CONNECT的条件BY条件相对于根行之一。
甲骨文选择连续几代的子行。 Oracle首先选择在步骤2中返回的行的孩子,那么这些孩子的孩子,等等。甲骨文总是通过评估CONNECT BY条件相对于当前父行选择孩子。
如果查询包含没有加入WHERE子句,则Oracle消除了等级不符合WHERE子句条件的所有行。甲骨文评估该条件为每一行单独的,而不是除去行不满足该条件的所有的孩子。
Oracle返回在图9-1所示的顺序行。在该图中,儿童出现父母下面。对于层次树的说明,请参阅图3-1,“层次树”。
图9-1层次查询
图9-1说明如下
“图9-1分层查询”的说明
为了找到一个父行的孩子,甲骨文评估CONNECT事先表达条件父行及其他表达式表中的每一行。排在其条件为真都是父母的孩子。在CONNECT BY条件可以包含其他的条件,以进一步筛选查询所选择的行。在CONNECT BY条件不能包含子查询。
如果在CONNECT BY条件导致在层次结构中的循环,那么Oracle将返回一个错误。如果一行是另一行的两个父(或祖父母或直接祖先)和一个孩子(或孙子或直系后裔)出现一个循环。
注意:
在一个分层查询,也可以不指定ORDER BY或GROUP BY,因为它们会破坏CONNECT的等级秩序BY结果。如果您想订购的行同父母的兄弟姐妹,然后使用ORDER SIBLINGS BY子句。见order_by_clause。
分层查询的例子
CONNECT BY示例下面的分层查询使用CONNECT BY子句定义员工和管理者之间的关系:
SELECT employee_id, last_name, manager_id FROM employees CONNECT BY PRIOR employee_id = manager_id;EMPLOYEE_ID LAST_NAME MANAGER_ID----------- ------------------------- ---------- 101 Kochhar 100 108 Greenberg 101 109 Faviet 108 110 Chen 108 111 Sciarra 108 112 Urman 108 113 Popp 108 200 Whalen 101...
级别实例下面的例子是类似上面的例子,但使用
LEVEL
伪列显示父和子行: SELECT employee_id, last_name, manager_id, LEVEL FROM employees CONNECT BY PRIOR employee_id = manager_id;EMPLOYEE_ID LAST_NAME MANAGER_ID LEVEL----------- ------------------------- ---------- ---------- 101 Kochhar 100 1 108 Greenberg 101 2 109 Faviet 108 3 110 Chen 108 3 111 Sciarra 108 3 112 Urman 108 3 113 Popp 108 3...
START WITH例子下面的例子增加一个START WITH子句来指定一个根一行的层次结构和ORDER BY子句使用姐弟俩关键字保留层次结构中排序:
SELECT last_name, employee_id, manager_id, LEVEL FROM employees START WITH employee_id = 100 CONNECT BY PRIOR employee_id = manager_id ORDER SIBLINGS BY last_name;LAST_NAME EMPLOYEE_ID MANAGER_ID LEVEL------------------------- ----------- ---------- ----------King 100 1Cambrault 148 100 2Bates 172 148 3Bloom 169 148 3Fox 170 148 3Kumar 173 148 3Ozer 168 148 3Smith 171 148 3De Haan 102 100 2Hunold 103 102 3Austin 105 103 4Ernst 104 103 4Lorentz 107 103 4Pataballa 106 103 4Errazuriz 147 100 2Ande 166 147 3Banda 167 147 3...
在HR.EMPLOYEES表,员工史蒂芬国王是该公司的负责人,也没有经理。在他的员工是约翰·罗素,谁是部门80如果我们更新了员工表设置罗素作为国王的经理,我们将创建的数据循环的经理:
UPDATE employees SET manager_id = 145 WHERE employee_id = 100;SELECT last_name "Employee", LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM employees WHERE level <= 3 AND department_id = 80 START WITH last_name = 'King' CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4; 2 3 4 5 6 7 ERROR:ORA-01436: CONNECT BY loop in user data
在连接NOCYCLE参数BY条件导致的Oracle返回的行,尽管循环。该CONNECT_BY_ISCYCLE伪显示哪些行包含循环:
SELECT last_name "Employee", CONNECT_BY_ISCYCLE "Cycle", LEVEL, SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM employees WHERE level <= 3 AND department_id = 80 START WITH last_name = 'King' CONNECT BY NOCYCLE PRIOR employee_id = manager_id AND LEVEL <= 4;Employee Cycle LEVEL Path------------------------- ------ ------ -------------------------Russell 1 2 /King/RussellTucker 0 3 /King/Russell/TuckerBernstein 0 3 /King/Russell/BernsteinHall 0 3 /King/Russell/HallOlsen 0 3 /King/Russell/OlsenCambrault 0 3 /King/Russell/CambraultTuvault 0 3 /King/Russell/TuvaultPartners 0 2 /King/PartnersKing 0 3 /King/Partners/KingSully 0 3 /King/Partners/SullyMcEwen 0 3 /King/Partners/McEwen...
CONNECT_BY_ROOT示例下面的示例返回110部每位员工的姓名,该员工在层次结构上的每个经理,经理和员工之间的级别数,以及两者之间的路径:
SELECT last_name "Employee", CONNECT_BY_ROOT last_name "Manager", LEVEL-1 "Pathlen", SYS_CONNECT_BY_PATH(last_name, '/') "Path" FROM employees WHERE LEVEL > 1 and department_id = 110 CONNECT BY PRIOR employee_id = manager_id;Employee Manager Pathlen Path--------------- ------------ ---------- -----------------------------------Higgins Kochhar 1 /Kochhar/HigginsGietz Kochhar 2 /Kochhar/Higgins/GietzGietz Higgins 1 /Higgins/GietzHiggins King 2 /King/Kochhar/HigginsGietz King 3 /King/Kochhar/Higgins/Gietz
下面的示例使用GROUP BY子句返回的110部每位员工,而员工在层次结构下的所有雇员的工资总额:
SELECT name, SUM(salary) "Total_Salary" FROM ( SELECT CONNECT_BY_ROOT last_name as name, Salary FROM employees WHERE department_id = 110 CONNECT BY PRIOR employee_id = manager_id) GROUP BY name;NAME Total_Salary------------------------- ------------Gietz 8300Higgins 20300King 20300Kochhar 20300
- oracle connect by 官方文档翻译
- Oracle官方OCI(Oracle Call Interface)文档翻译
- JNI 官方文档翻译
- FullCalendar 官方文档翻译
- monkeyrunner官方文档翻译
- FullCalendar 官方文档翻译
- FullCalendar 官方文档翻译
- NSLayoutContraint 官方文档翻译
- NSPredicate官方文档翻译
- FullCalendar 官方文档翻译
- MifareUltralight官方文档翻译
- ViewPager官方文档翻译
- NSPredicate官方文档翻译
- hbase 官方文档翻译
- Andriod官方文档翻译
- MHA官方文档翻译
- Fragment官方文档翻译
- 翻译AsyncTask官方文档
- HDU 3579 Hello Kiki 中国剩余定理 不互质情况
- SecureCRT 使用技巧
- hadoop-hive-hive多个实例
- STL之双端队列
- 关于readonly的理解——学习笔记
- oracle connect by 官方文档翻译
- 4.4.2.1 创建随机颜色的画笔
- 软件测试流程
- android中各个度量单位的解释,分享给大家
- C#中,将一个DataTable转为List
- linux sed
- 深入理解SELinux/SEAndroid
- 行为型模式--状态模式
- shell实例浅谈之六文件特定行打印的多种方法