ヒエラルキークエリの疑似列は、ヒエラルキークエリにおいてのみ有効です。クエリ内で階層関係を定義するには、必ずCONNECT BY句を使用する必要があります。本記事では、CONNECT_BY_ISCYCLE疑似列、CONNECT_BY_ISLEAF疑似列、LEVEL疑似列を含む、ヒエラルキークエリ疑似列の3種類のタイプの使用方法と説明を主に紹介します。
CONNECT_BY_ISCYCLE疑似列
CONNECT_BY_ISCYCLE疑似列は、サイクルがどの行から始まるかをマークするために使用されます。
現在の行の子ノードが同時にその祖先ノードの一つである場合、CONNECT_BY_ISCYCLEは1を返し、そうでない場合は0を返します。
CONNECT_BY_ISCYCLEはCONNECT BY句のNOCYCLEと組み合わせて使用する必要があります。そうでない場合、ツリー構造の結果にサイクルが存在するため、クエリ結果にエラーが報告されます。
CONNECT_BY_ISLEAF疑似列
CONNECT_BY_ISLEAF疑似列は、階層構造の葉ノードをマークするために使用されます。
現在の行がCONNECT BY条件で定義されたツリーの葉ノードである場合、CONNECT_BY_ISLEAFは1を返し、そうでない場合は0を返します。
LEVEL疑似列
LEVEL疑似列は、ノードの階層をマークするために使用されます。
階層構造では、ルートが第1層目、ルートの子ノードが第2層目、以下同様です。例えば、ルートノードのLEVEL値は1を返し、ルートノードの子ノードのLEVEL値は2を返し、以下同様です。
4層の逆ツリー構造を例にとると、Root Rowは逆ツリー内で最も高い行であり、LEVEL値は通常1です。Child RowはRoot Row以外の任意の行であり、LEVEL値は通常2、3、または4です。Parent RowはChild Rowを持つ任意の行（Root Rowを除く）であり、LEVEL値は通常2または3です。Leaf Rowは子ノードを持たない任意の行であり、LEVEL値は通常4です。
階層クエリの例
CREATE TABLE tbl1(col1 INT, col2 INT, col3 INT);
INSERT INTO tbl1 VALUES(1, 0, -1);
INSERT INTO tbl1 VALUES(2, 1, -2);
INSERT INTO tbl1 VALUES(4, 2, -4);
INSERT INTO tbl1 VALUES(5, 2, -5);
INSERT INTO tbl1 VALUES(3, 1, -3);
INSERT INTO tbl1 VALUES(6, 3, -6);
INSERT INTO tbl1 VALUES(7, 3, -7);
obclient> SELECT col1, col2, LEVEL, CONNECT_BY_ISLEAF, CONNECT_BY_ISCYCLE,
CONNECT_BY_ROOT col1,CONNECT_BY_ROOT col2 FROM tbl1 START WITH col1 = 1
CONNECT BY NOCYCLE PRIOR col1 = col2;
+------+------+-------+-------------------+--------------------+---------------------+---------------------+
| COL1 | COL2 | LEVEL | CONNECT_BY_ISLEAF | CONNECT_BY_ISCYCLE | CONNECT_BY_ROOTCOL1 | CONNECT_BY_ROOTCOL2 |
+------+------+-------+-------------------+--------------------+---------------------+---------------------+
| 1 | 0 | 1 | 0 | 0 | 1 | 0 |
| 2 | 1 | 2 | 0 | 0 | 1 | 0 |
| 4 | 2 | 3 | 1 | 0 | 1 | 0 |
| 5 | 2 | 3 | 1 | 0 | 1 | 0 |
| 3 | 1 | 2 | 0 | 0 | 1 | 0 |
| 6 | 3 | 3 | 1 | 0 | 1 | 0 |
| 7 | 3 | 3 | 1 | 0 | 1 | 0 |
+------+------+-------+-------------------+--------------------+---------------------+---------------------+
7 rows in set
obclient> SELECT col1, col2, LEVEL, CONNECT_BY_ISLEAF, CONNECT_BY_ISCYCLE,
CONNECT_BY_ROOT (col1 + col2) FROM tbl1 START WITH col1 = 1
CONNECT BY NOCYCLE PRIOR col1 = col2;
+------+------+-------+-------------------+--------------------+----------------------------+
| COL1 | COL2 | LEVEL | CONNECT_BY_ISLEAF | CONNECT_BY_ISCYCLE | CONNECT_BY_ROOT(COL1+COL2) |
+------+------+-------+-------------------+--------------------+----------------------------+
| 1 | 0 | 1 | 0 | 0 | 1 |
| 2 | 1 | 2 | 0 | 0 | 1 |
| 4 | 2 | 3 | 1 | 0 | 1 |
| 5 | 2 | 3 | 1 | 0 | 1 |
| 3 | 1 | 2 | 0 | 0 | 1 |
| 6 | 3 | 3 | 1 | 0 | 1 |
| 7 | 3 | 3 | 1 | 0 | 1 |
+------+------+-------+-------------------+--------------------+----------------------------+
7 rows in set
obclient> SELECT CONNECT_BY_ROOT col1, TO_NUMBER(CONNECT_BY_ROOT col1),
TO_CHAR(CONNECT_BY_ROOT col1), CONNECT_BY_ROOT(col1 + col2),
TO_NUMBER(CONNECT_BY_ROOT (col1 + col2)), TO_CHAR(CONNECT_BY_ROOT (col1 + col2))
FROM tbl1 START WITH col1 = 1 CONNECT BY NOCYCLE PRIOR col1 = col2;
+---------------------+--------------------------------+------------------------------+----------------------------+---------------------------------------+-------------------------------------+
| CONNECT_BY_ROOTCOL1 | TO_NUMBER(CONNECT_BY_ROOTCOL1) | TO_CHAR(CONNECT_BY_ROOTCOL1) | CONNECT_BY_ROOT(COL1+COL2) | TO_NUMBER(CONNECT_BY_ROOT(COL1+COL2)) | TO_CHAR(CONNECT_BY_ROOT(COL1+COL2)) |
+---------------------+--------------------------------+------------------------------+----------------------------+---------------------------------------+-------------------------------------+
| 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 1 | 1 | 1 |
| 1 | 1 | 1 | 1 | 1 | 1 |
+---------------------+--------------------------------+------------------------------+----------------------------+---------------------------------------+-------------------------------------+
7 rows in set