我的java天地

Oracle用Start with...Connect By子句递归查询

Start with...Connect By子句递归查询一般用于一个表维护树形结构的应用。
创建示例表:
CREATE TABLE TBL_TEST
(
   ID     NUMBER,
   NAME   VARCHAR2(100 BYTE),
   PID    NUMBER                                   DEFAULT 0
);
插入测试数据:
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('1','10','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('2','11','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('3','20','0');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('4','12','1');
INSERT INTO TBL_TEST(ID,NAME,PID) VALUES('5','121','2');
从Root往树末梢递归
select * from TBL_TEST
start with id=1
connect by prior id = pid
从末梢往树ROOT递归
select * from TBL_TEST
start with id=5
connect by prior pid = id
找到更全面的资料
Oracle Connect By Function
Version 10.2
Basic Syntax Elements START WITH <condition>
CONNECT BY
[NOCYCLE]
<condition>
CONNECT BY PRIOR
A condition that identifies the relationship between parent rows and child rows of the hierarchy CONNECT BY <child_value> = <parent_value>
conn hr/hr

SELECT employee_id, last_name, manager_id
FROM employees
CONNECT BY PRIOR employee_id = manager_id;
START WITH
Specifies a condition that identifies the row(s) to be used as the root(s) of a hierarchical query START WITH (column_name) = <value>
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
SIBLINGS BY preserves any ordering specified in the hierarchical query clause and then applies the order_by_clause to the siblings of the hierarchy ORDER SIBLINGS BY (column_name)
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;
CONNECT_BY_ROOT
CONNECT_BY_ROOT is a unary operator that is valid only in hierarchical queries. When you qualify a column with this operator, Oracle returns the column value using data from the root row.

Cannot be specified with the START WITH or   CONNECT BY condition.
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:
col emp format a20
col mgr format a20
set linesize 120

SELECT "Name", SUM(salary) "Total_Salary"
FROM (
   SELECT CONNECT_BY_ROOT last_name "Name", salary
   FROM employees
   WHERE department_id = 110
   CONNECT BY PRIOR employee_id = manager_id)
GROUP BY "Name";

-- Thanks Colin for the correction
CONNECT_BY_ISCYCLE Pseudocolumn
The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0
UPDATE employees SET manager_id = 145
WHERE employee_id = 100;

SELECT last_name, 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

SELECT last_name, 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;
CONNECT_BY_ISLEAF Pseudocolumn
The CONNECT_BY_ISLEAF pseudocolumn returns 1 if the current row is a leaf of the tree defined by the CONNECT BY condition. Otherwise it returns 0. This information indicates whether a given row can be further expanded to show more of the hierarchy.
SELECT last_name "Employee", CONNECT_BY_ISLEAF "IsLeaf",
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;
LEVEL Pseudocolumn
For each row returned by a hierarchical query, the LEVEL pseudocolumn returns 1 for a root row, 2 for a child of a root, and so on
SELECT employee_id, last_name, manager_id, LEVEL
FROM employees
CONNECT BY PRIOR employee_id = manager_id;

SELECT LPAD(' ',2*(LEVEL-1)) || last_name ORG_CHART,
employee_id, manager_id, job_id
FROM employees
START WITH job_id = 'AD_VP'
CONNECT BY PRIOR employee_id = manager_id;

SYS_CONNECT_BY_PATH
Returns the path of a column value from root to node, with column values separated by char for each row returned by CONNECT BY condition
SYS_CONNECT_BY_PATH(<column>, <char>)
See CONNECT_BY_ISCYCLE demo
Function Demo
Use A Function To Receive The Current Node and Search for Parents of the Current Node
CREATE OR REPLACE FUNCTION permissions_sub_tree_root (
the_id IN NUMBER,
the_level IN NUMBER)
RETURN NUMBER IS

sub_tree_root NUMBER(10);

BEGIN
   SELECT id
   INTO sub_tree_root
   FROM hierarchy
   WHERE level = the_level
  -- Connect 'upwards', i.e. find the parent
   CONNECT BY PRIOR PARENT = id
   START WITH ID = the_id;

   RETURN sub_tree_root;
END permissions_sub_tree_root;
/

SELECT id, name, username
FROM (
   SELECT ID, PARENT, NAME,
   permissions_sub_tree_root
(id, LEVEL) ROOT
   FROM hierarchy
   CONNECT BY PRIOR id = PARENT) HIERARCHY, permissions
WHERE ROOT = hierarchy_id;
GROUP BY Demo
Group By Demo with CONNECT_BY_ROOT and
CONNECT_BY_PRIOR
SELECT name, SUM(salary) "Total_Salary"
FROM (
   SELECT CONNECT_BY_ROOT last_name "Name", salary
   FROM employees
   WHERE department_id = 110
  CONNECT BY PRIOR employee_id = manager_id)
GROUP BY name;
Demos
Indenting col lname format a30

SELECT LPAD(' ', level*2, ' ') || last_name LNAME, d.department_id
FROM employees e, departments d
WHERE e.department_id = d.department_id
START WITH employee_id = 100
CONNECT BY PRIOR e.employee_id = e.manager_id;
Hierarchical Query with IN In a [NOT] IN condition in a WHERE clause, if the right-hand side of the condition is a subquery, you cannot use LEVEL on the left-hand side of the condition. However, you can specify LEVEL in a subquery of the FROM clause to achieve the same result. For example, the following statement is not valid:
SELECT employee_id, last_name FROM employees
WHERE (employee_id, LEVEL)
IN (SELECT employee_id, 2 FROM employees)
START WITH employee_id = 2
CONNECT BY PRIOR employee_id = manager_id;
But the following statement is valid because it encapsulates the query containing the LEVEL information in the FROM clause:
SELECT v.employee_id, v.last_name, v.lev
FROM (
   SELECT employee_id, last_name, LEVEL lev
   FROM employees v
   START WITH employee_id = 100
   CONNECT BY PRIOR employee_id = manager_id) v
WHERE (v.employee_id, v.lev) IN (
   SELECT employee_id, 2 FROM employees);

posted on 2009-09-28 17:33 tobyxiong 阅读(2105) 评论(0)  编辑  收藏 所属分类: DATABASES


只有注册用户登录后才能发表评论。


网站导航:
 
<2009年9月>
303112345
6789101112
13141516171819
20212223242526
27282930123
45678910

导航

统计

常用链接

留言簿(3)

随笔分类(144)

随笔档案(157)

相册

最新随笔

搜索

积分与排名

最新评论

阅读排行榜

评论排行榜