PolarDB:CONNECT BY 句

次のコードブロックは、CONNECT BY句の一般的な構文を示しています。

CONNECT BY { PRIOR parent_expr = child_expr |
 child_expr = PRIOR parent_expr }

parent_exprは、候補親行に対して評価されます。 FROM句によって返される行に対して、parent_expr = child_expr条件がTRUEと評価された場合、この行は親行の子行と見なされます。

次のオプションの句は、CONNECT BY句と組み合わせて指定できます。

START WITH start_expression

FROM句によって返される行に対してstart_expressionがTRUEと評価された場合、この行は階層のルートノードになります。

ORDER SIBLINGS BY expression [ ASC | DESC ] [, ...]

階層の兄弟行は、結果セット内の式によって順序付けられます。

テストデータの作成

employeesという名前のテーブルを作成し、テストデータを挿入します。 次のサンプルコードを参照してください。

-- Create the employees table.
CREATE TABLE employees (
  employee_id NUMBER(11) NOT NULL,
  last_name VARCHAR2(16) NOT NULL,
  manager_id NUMBER(11),
  department_id NUMBER(4) NOT NULL,
  CONSTRAINT pk_employees PRIMARY KEY (employee_id)
);

-- Insert test data.
INSERT INTO employees (employee_id, last_name, manager_id, department_id) VALUES
(100, 'King', NULL, 80),  -- Employee 100 is the manager of department 80.
(101, 'Blake', 100, 80),  -- Employee 101 is a subordinate of employee 100.
(102, 'Clark', 100, 80),  -- Employee 102 is a subordinate of employee 100.
(103, 'Miller', 101, 80), -- Employee 103 is a subordinate of employee 101.
(200, 'Kochhar', NULL, 110),  -- Employee 200 is the manager of department 110.
(201, 'Higgins', 200, 110),  -- Employee 201 is a subordinate of employee 200.
(202, 'Gietz', 200, 110),  -- Employee 202 is a subordinate of employee 200.
(203, 'Sciarra', 201, 110); -- Employee 203 is a subordinate of employee 201.

CONNECT_BY_ISLEAF

CONNECT_BY_ISLEAFは、現在の行がリーフノードであるかどうかを判断します。 現在の行がリーフノードの場合、1またはtrueが返されます。 それ以外の場合、0またはfalseが返されます。

CONNECT_BY_ISLEAFが使用されるSELECTステートメントの例を次に示します。

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 employee_id = 100
   CONNECT BY PRIOR employee_id = manager_id AND LEVEL <= 4;

返された結果の例を次に示します。

 Employee | IsLeaf | level |        Path        
----------+--------+-------+--------------------
 King     |      0 |     1 | /King
 Blake    |      0 |     2 | /King/Blake
 Miller   |      1 |     3 | /King/Blake/Miller
 Clark    |      1 |     2 | /King/Clark
(4 rows)

CONNECT_BY_ROOT

CONNECT_BY_ROOTは単項演算子であり、階層クエリでのみ有効です。 この演算子を使用して列を修飾すると、ルート行のデータを使用して列の値が返されます。 この演算子は、階層内の直接親行とすべての先祖行を返します。 これにより、階層クエリのCONNECT BY [PRIOR] 条件の機能が拡張されます。

次のコードは、CONNECT_BY_ROOTが使用されるSELECTステートメントの例を示しています。

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           
----------+---------+---------+--------------------------
 Gietz    | Kochhar |       1 | /Kochhar/Gietz
 Higgins  | Kochhar |       1 | /Kochhar/Higgins
 Sciarra  | Kochhar |       2 | /Kochhar/Higgins/Sciarra
 Sciarra  | Higgins |       1 | /Higgins/Sciarra
(4 rows)