Oracleのstart with connect byの使い方

30544 ワード

転載先:http://blog.csdn.net/jiushuai/article/details/8231093
ORACLE--Connect By、Level、Start Withの使用(Hierarchical query-階層クエリー)
Syntax 1
CONNECT BY [NOCYCLE] START WITH
Syntax 2
START WITH  CONNECT BY [NOCYCLE]
参考サイト:http://asktom.oracle.com/pls/asktom/f?p=100:11:0::::P11_QUESTION_ID:489772591421
  http://psoug.org/reference/connectby.html
  http://www.oradev.com/connect_by.jsp
http://philip.greenspun.com/sql/trees.html 従業員番号7369のリーダーを検索します.

1 SELECT LEVEL,E.* FROM EMP E CONNECT BY PRIOR E.MGR = E.EMPNO  START WITH E.EMPNO = 7876
2 ORDER BY LEVEL DESC

"start with"-- this identifies all LEVEL=1 nodes in the tree
"connect by"-- describes how to walk from the parent nodes above to their children and their childrens children.
Easiest to use an example on emp. If we start with "where mgr is NULL", we generate the set of employees that have no mgr (they are the top of the tree). If we
CONNECT BY PRIOR EMPNO =/* current */MGR
that will take all of the PRIOR records (the start with at first) and find all records such that the MGR column equals their EMPNO (find all the records of people managed by the people we started with).
WITH文を使用したクエリー結果の最適化:最適化レベル

 1 WITH A AS
 2  (SELECT MAX(LEVEL) + 1 LVL
 3     FROM EMP E
 4   CONNECT BY PRIOR E.MGR = E.EMPNO
 5    START WITH E.EMPNO = 7876
 6    ORDER BY LEVEL DESC)
 7 SELECT A.LVL      1,
 8        LEVEL     ,
 9        A.LVL - LEVEL      ,
10        E.* 　FROM A,
11        EMP E CONNECT BY PRIOR E.MGR = E.EMPNO START WITH E.EMPNO = 7876 ORDER BY LEVEL DESC

従業員番号7839のすべての部下を検索します(7839はking):

1 SELECT LEVEL   , E.*
2   FROM EMP E
3 CONNECT BY PRIOR E.EMPNO = E.MGR
4  START WITH E.EMPNO = 7839

--階層全体を構築

1 select lpad(' ',level*2,' ')||ename ename, empno, mgr
2     from emp
3     START WITH MGR IS NULL
4     CONNECT BY PRIOR EMPNO = MGR

So, KING is the start with set then JONES BLAKE and CLARK fall under him. Each of them becomes the PRIOR record in turn and their trees are expanded.
Connect Byとlevelを組み合わせて仮想行を構築するには、次の手順に従います.

1 SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 5

rownumを使用して、同様の機能を実現します.

1 SELECT LEVEL FROM DUAL CONNECT BY LEVEL < 5

-----------------------------------------------------------
UNIOALLを使用して、2つのノードのツリーを構築します.
次のように表示されます.

 1 CREATE OR REPLACE VIEW TREE_VIEW AS
 2 SELECT
 3  '1' AS rootnodeid,
 4  'xxxx      ' AS treename,
 5  '-1'  AS parent_id
 6 FROM dual
 7 UNION
 8 SELECT
 9   to_char(d.deptno),
10   d.dname || '_' ||d.loc,
11   '1' AS parent_id
12  FROM dept d;

クエリ文:

1 SELECT T.*, LEVEL
2   FROM TREE_VIEW T
3  START WITH T.PARENT_ID = '-1'
4 CONNECT BY PRIOR T.ROOTNODEID = T.PARENT_ID

------以下は更新内容です.
1、まず全部でいくつの等級があるかを確認します.

1 SELECT COUNT(LEVEL)
2   FROM EMP E
3 CONNECT BY PRIOR E.EMPNO = E.MGR
4  START WITH E.MGR IS NULL;

2、各等級の人数を確認する.主にLEVELによるGROUP BY

1 SELECT COUNT(LEVEL)
2   FROM EMP E
3 CONNECT BY PRIOR E.EMPNO = E.MGR
4  START WITH E.MGR IS NULL
5  GROUP BY LEVEL;

3、Oracle 10 gは簡単な接続を提供しています.by_isleaf=1,
0は非リーフノードを表します

1 SELECT LEVEL AS   , CONNECT_BY_ISLEAF AS        , E.*
2   FROM EMP E
3 CONNECT BY PRIOR E.EMPNO = E.MGR
4  START WITH E.MGR IS NULL

4、SYS_CONNECT_BY_PATH
Oracle 9 iはsys_を提供しています.connect_by_path(column,char)で、columnは文字型または自動回転可能である
文字型のカラム名に変更します.その主な目的は、親ノードから現在のノードへの「path」を指定したモードで表示することです.この関数は階層クエリーでのみ使用できます.

1 SELECT LEVEL AS   ,
2        CONNECT_BY_ISLEAF AS        ,
3        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>')
4   FROM EMP E
5 CONNECT BY PRIOR E.EMPNO = E.MGR
6  START WITH E.MGR IS NULL;

Oracle 的 start with connect by 用法_第10张图片

5、枝とノードをトリムする:

Oracle 的 start with connect by 用法_第11张图片

フィルタ番号は7566のデータ(トリミングノード)で、このノードをカットすることを指していますが、ツリー構造を破壊していません.サブノードは正常に表示されます.

1 SELECT LEVEL AS   ,
2        CONNECT_BY_ISLEAF AS        ,
3        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>'),
4        E.*
5   FROM EMP E
6 WHERE e.empno != 7566
7 CONNECT BY PRIOR E.EMPNO = E.MGR
8  START WITH E.MGR IS NULL;

Oracle 的 start with connect by 用法_第12张图片

カット番号は7698のノードとそのサブノードです.

1 SELECT LEVEL AS   ,
2        CONNECT_BY_ISLEAF AS        ,
3        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>'),
4        E.*
5   FROM EMP E
6 CONNECT BY PRIOR E.EMPNO = E.MGR
7        AND E.EMPNO != 7698
8  START WITH E.MGR IS NULL;

Oracle 的 start with connect by 用法_第13张图片

6、CONNECT_BY_ROOTの使用、oracle 10 g新規接続_by_rootは、行のルートノードの同じ列名を列名の前に表す値です.

1 SELECT LEVEL AS   ,
2        CONNECT_BY_ISLEAF AS        ,
3        CONNECT_BY_ROOT ENAME,
4        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>'),
5        E.*
6   FROM EMP E
7 CONNECT BY PRIOR E.EMPNO = E.MGR
8  START WITH E.MGR IS NULL;

Oracle 的 start with connect by 用法_第14张图片

階層クエリーでorder byでソートする場合、order by last_などnameは階層を完了してlevelを取得し、last_を押します.nameソートは、階層を破壊します.たとえば、行の深さに特に注目し、levelを押します. ソートは、階層を破壊します.oracle 10 gではsiblingsキーワードのソートが追加されました.
構文:order siblings by
階層が保護され、各レベルでexpreでソートされます.

1 SELECT LEVEL AS   ,
2        CONNECT_BY_ISLEAF AS        ,
3        LPAD(' ', LEVEL * 2 - 1) || SYS_CONNECT_BY_PATH(ENAME, '=>'),
4        E.*
5   FROM EMP E
6 CONNECT BY PRIOR E.EMPNO = E.MGR 
7  START WITH E.MGR IS NULL
8  ORDER SIBLINGS BY  E.ENAME;

Oracle 的 start with connect by 用法_第15张图片

connect_by_iscycle(ループが存在し、1を返します.そうでなければ0を返します)
The CONNECT_BY_ISCYCLE pseudocolumn returns 1 if the current row has a child which is also its ancestor. Otherwise it returns 0. You can specify CONNECT_BY_ISCYCLE only if you have specified the NOCYCLE parameter of the CONNECT BY clause. NOCYCLE enables Oracle to return the results of a query that would otherwise fail because of a CONNECT BY loop in the data.

Struts 2カスタムラベルに動的値を渡すにはどうすればいいですか?

JBM 3ソースファイルの解読FieldInstantiation実装集合Field