ヒエラルキークエリの疑似列は、ヒエラルキークエリでのみ有効です。クエリ内で階層関係を定義するには、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