Hierarchical data can be queried in hierarchical order using START WITH
and CONNECT BY
.
-
START WITH
specifies the root row(s). -
CONNECT BY
, followed by a condition, specifies the hierarchical relationship between rows. At least one expression in the condition must use thePRIOR
operator, which refers to the previous row in the hierarchy (the parent).
Further Details
-
LEVEL
pseudocolumnThe depth of a row in the hierarchy.
-
CONNECT_BY_ROOT
operatorThis can be applied to any column to show the value of the same column in the root row rather than the current row.
-
SYS_CONNECT_BY_PATH(column,char)
functionReturns the path of the column from root to the current row. Each value is separated by
char
. -
CONNECT_BY_ISCYCLE
pseudocolumnCONNECT BY
will return an error if there is a ‘loop’, unless followed with the optionalNOCYCLE
parameter. In that case,CONNECT_BY_ISCYCLE
shows when the cycle has been detected and broken.