CONNECT_BY_ROOT は単項演算子で、列を限定することに使用でき、 現在の行から見てルートノードとみなされる列の、現在の行での対応する値を返します。
単項演算子は、単一のオペランドを操作します。 CONNECT_BY_ROOT の場合、単一のオペランドは、CONNECT_BY_ROOT キーワードに続く列名です。
次の例では、SELECT リストで CONNECT_BY_ROOT 演算子を使用する場合を示します。
SELECT [... ,] CONNECT_BY_ROOT column [, ...]
FROM table_expression ...CONNECT_BY_ROOT 演算子を使用する際は、次の規則に従ってください。
- CONNECT_BY_ROOT 演算子は、階層クエリで SELECT 文を使用している場合、SELECT リスト、WHERE 句、GROUP BY 句、HAVING 句、ORDER BY 句、および ORDER SIBLINGS BY 句で使用できます。
- CONNECT_BY_ROOT 演算子は、階層クエリの CONNECT BY 句または START WITH 句では使用できません。
- CONNECT_BY_ROOT 演算子は、列に関連する式で使用できます。 その場合、式は括弧で囲む必要があります。
次のクエリでは、CONNECT_BY_ROOT 演算子を使用して、従業員の BLAKE、CLARK、および JONES で始まるツリーに基づいた結果セットを返す方法を示しています。 結果セットには、リストされている従業員ごとに、ルートノードの従業員番号と従業員名が含まれています。
SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr,
CONNECT_BY_ROOT empno "mgr empno",
CONNECT_BY_ROOT ename "mgr ename"
FROM emp
START WITH ename IN ('BLAKE','CLARK','JONES')
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename ASC;クエリからの出力では、mgr empno や mgr ename を含む列のすべてのルートノードが、START WITH 句にリストされている従業員 BLAKE、CLARK、および JONES のうちのいずれかであることがわかります。
level | employee | empno | mgr | mgr empno | mgr ename
-------+-----------+-------+------+-----------+-----------
1 | BLAKE | 7698 | 7839 | 7698 | BLAKE
2 | ALLEN | 7499 | 7698 | 7698 | BLAKE
2 | JAMES | 7900 | 7698 | 7698 | BLAKE
2 | MARTIN | 7654 | 7698 | 7698 | BLAKE
2 | TURNER | 7844 | 7698 | 7698 | BLAKE
2 | WARD | 7521 | 7698 | 7698 | BLAKE
1 | CLARK | 7782 | 7839 | 7782 | CLARK
2 | MILLER | 7934 | 7782 | 7782 | CLARK
1 | JONES | 7566 | 7839 | 7566 | JONES
2 | FORD | 7902 | 7566 | 7566 | JONES
3 | SMITH | 7369 | 7902 | 7566 | JONES
2 | SCOTT | 7788 | 7566 | 7566 | JONES
3 | ADAMS | 7876 | 7788 | 7566 | JONES
(13 rows)次の例では、同様のクエリを示します。 このクエリでは、単一のトップレベルの従業員で始まる 1 つのツリーのみが生成されます。 トップレベルの mgr 列は null でなければなりません。
SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr,
CONNECT_BY_ROOT empno "mgr empno",
CONNECT_BY_ROOT ename "mgr ename"
FROM emp START WITH mgr IS NULL
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename ASC;次の出力では、mgr empno 列と mgr ename 列のすべてのルートノードが、 KING がこの特定のクエリのルートであることを示しています。
level | employee | empno | mgr | mgr empno | mgr ename
-------+-------------+-------+------+-----------+-----------
1 | KING | 7839 | | 7839 | KING
2 | BLAKE | 7698 | 7839 | 7839 | KING
3 | ALLEN | 7499 | 7698 | 7839 | KING
3 | JAMES | 7900 | 7698 | 7839 | KING
3 | MARTIN | 7654 | 7698 | 7839 | KING
3 | TURNER | 7844 | 7698 | 7839 | KING
3 | WARD | 7521 | 7698 | 7839 | KING
2 | CLARK | 7782 | 7839 | 7839 | KING
3 | MILLER | 7934 | 7782 | 7839 | KING
2 | JONES | 7566 | 7839 | 7839 | KING
3 | FORD | 7902 | 7566 | 7839 | KING
4 | SMITH | 7369 | 7902 | 7839 | KING
3 | SCOTT | 7788 | 7566 | 7839 | KING
4 | ADAMS | 7876 | 7788 | 7839 | KING
(14 rows)対照的に、次の例では START WITH 句を省略し、14 個のツリーが生成されます。
SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr,
CONNECT_BY_ROOT empno "mgr empno",
CONNECT_BY_ROOT ename "mgr ename"
FROM emp
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename ASC;次の例は、このクエリの出力です。 各ノードは、少なくとも 1 回は、mgr empno 列と mgr ename 列でルートノードとして表示されています。 リーフノードでさえ、自身のツリーの頂点を形成します。
level | employee | empno | mgr | mgr empno | mgr ename
-------+-------------+-------+------+-----------+-----------
1 | ADAMS | 7876 | 7788 | 7876 | ADAMS
1 | ALLEN | 7499 | 7698 | 7499 | ALLEN
1 | BLAKE | 7698 | 7839 | 7698 | BLAKE
2 | ALLEN | 7499 | 7698 | 7698 | BLAKE
2 | JAMES | 7900 | 7698 | 7698 | BLAKE
2 | MARTIN | 7654 | 7698 | 7698 | BLAKE
2 | TURNER | 7844 | 7698 | 7698 | BLAKE
2 | WARD | 7521 | 7698 | 7698 | BLAKE
1 | CLARK | 7782 | 7839 | 7782 | CLARK
2 | MILLER | 7934 | 7782 | 7782 | CLARK
1 | FORD | 7902 | 7566 | 7902 | FORD
2 | SMITH | 7369 | 7902 | 7902 | FORD
1 | JAMES | 7900 | 7698 | 7900 | JAMES
1 | JONES | 7566 | 7839 | 7566 | JONES
2 | FORD | 7902 | 7566 | 7566 | JONES
3 | SMITH | 7369 | 7902 | 7566 | JONES
2 | SCOTT | 7788 | 7566 | 7566 | JONES
3 | ADAMS | 7876 | 7788 | 7566 | JONES
1 | KING | 7839 | | 7839 | KING
2 | BLAKE | 7698 | 7839 | 7839 | KING
3 | ALLEN | 7499 | 7698 | 7839 | KING
3 | JAMES | 7900 | 7698 | 7839 | KING
3 | MARTIN | 7654 | 7698 | 7839 | KING
3 | TURNER | 7844 | 7698 | 7839 | KING
3 | WARD | 7521 | 7698 | 7839 | KING
2 | CLARK | 7782 | 7839 | 7839 | KING
3 | MILLER | 7934 | 7782 | 7839 | KING
2 | JONES | 7566 | 7839 | 7839 | KING
3 | FORD | 7902 | 7566 | 7839 | KING
4 | SMITH | 7369 | 7902 | 7839 | KING
3 | SCOTT | 7788 | 7566 | 7839 | KING
4 | ADAMS | 7876 | 7788 | 7839 | KING
1 | MARTIN | 7654 | 7698 | 7654 | MARTIN
1 | MILLER | 7934 | 7782 | 7934 | MILLER
1 | SCOTT | 7788 | 7566 | 7788 | SCOTT
2 | ADAMS | 7876 | 7788 | 7788 | SCOTT
1 | SMITH | 7369 | 7902 | 7369 | SMITH
1 | TURNER | 7844 | 7698 | 7844 | TURNER
1 | WARD | 7521 | 7698 | 7521 | WARD
(39 rows)次の例では、CONNECT_BY_ROOT の単項演算子としての効果を示します。 括弧で囲まれていない式で使用すると、CONNECT_BY_ROOT 演算子は、演算子の直後に続く語 ename にのみ影響します。 それに続く || ' manages ' || ename の連結は、CONNECT_BY_ROOT 操作の一部ではありません。 したがって、2 つ目に出現する ename は、現在の行の値になります。 最初に出現する ename は、ルートノードからの値です。
SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr,
CONNECT_BY_ROOT ename || ' manages ' || ename "top mgr/employee"
FROM emp
START WITH ename IN ('BLAKE','CLARK','JONES')
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename ASC;次の例は、このクエリの出力です。 top mgr/employee 列に対して値が生成されます。
level | employee | empno | mgr | top mgr/employee
-------+-----------+-------+------+----------------------
1 | BLAKE | 7698 | 7839 | BLAKE manages BLAKE
2 | ALLEN | 7499 | 7698 | BLAKE manages ALLEN
2 | JAMES | 7900 | 7698 | BLAKE manages JAMES
2 | MARTIN | 7654 | 7698 | BLAKE manages MARTIN
2 | TURNER | 7844 | 7698 | BLAKE manages TURNER
2 | WARD | 7521 | 7698 | BLAKE manages WARD
1 | CLARK | 7782 | 7839 | CLARK manages CLARK
2 | MILLER | 7934 | 7782 | CLARK manages MILLER
1 | JONES | 7566 | 7839 | JONES manages JONES
2 | FORD | 7902 | 7566 | JONES manages FORD
3 | SMITH | 7369 | 7902 | JONES manages SMITH
2 | SCOTT | 7788 | 7566 | JONES manages SCOTT
3 | ADAMS | 7876 | 7788 | JONES manages ADAMS
(13 rows)次の例では、括弧で囲まれた式に対して CONNECT_BY_ROOT 演算子が使用されています。
SELECT LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || ename "employee", empno, mgr,
CONNECT_BY_ROOT ('Manager ' || ename || ' is emp # ' || empno)
"top mgr/empno"
FROM emp
START WITH ename IN ('BLAKE','CLARK','JONES')
CONNECT BY PRIOR empno = mgr
ORDER SIBLINGS BY ename ASC;次の例は、このクエリの出力です。 ename と empno の両方の値は、CONNECT_BY_ROOT 演算子の影響を受けます。 top mgr/empno 列には、ルートノードから返された値が表示されています。
level | employee | empno | mgr | top mgr/empno
-------+-----------+-------+------+-----------------------------
1 | BLAKE | 7698 | 7839 | Manager BLAKE is emp # 7698
2 | ALLEN | 7499 | 7698 | Manager BLAKE is emp # 7698
2 | JAMES | 7900 | 7698 | Manager BLAKE is emp # 7698
2 | MARTIN | 7654 | 7698 | Manager BLAKE is emp # 7698
2 | TURNER | 7844 | 7698 | Manager BLAKE is emp # 7698
2 | WARD | 7521 | 7698 | Manager BLAKE is emp # 7698
1 | CLARK | 7782 | 7839 | Manager CLARK is emp # 7782
2 | MILLER | 7934 | 7782 | Manager CLARK is emp # 7782
1 | JONES | 7566 | 7839 | Manager JONES is emp # 7566
2 | FORD | 7902 | 7566 | Manager JONES is emp # 7566
3 | SMITH | 7369 | 7902 | Manager JONES is emp # 7566
2 | SCOTT | 7788 | 7566 | Manager JONES is emp # 7566
3 | ADAMS | 7876 | 7788 | Manager JONES is emp # 7566
(13 rows)