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 列表、WHERE 子句、GROUP BY 子句、HAVING 子句、ORDER BY 子句和 ORDER SIBLINGS BY 子句中使用，只要 SELECT 命令用于分层查询。
- CONNECT_BY_ROOT 运算符不能在分层查询的 CONNECTBY 子句或 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)下面是一个类似的查询，但只生成一个以单个顶层级别员工开头的树，其中 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 子句，从而生成十四个树。
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;下面是该查询的输出。每个节点作为根节点至少在 mgr empno 和 mgrename 列下出现一次，因为甚至叶节点也构成其自己树的顶层。
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 运算的一部分，因此，第二次出现的 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)