SQL Fonctionnalité Oracle CONNECT BY avec les CTE récursifs


Exemple

La fonctionnalité CONNECT BY d'Oracle fournit de nombreuses fonctionnalités utiles et non triviales qui ne sont pas intégrées lors de l'utilisation de CTE récursifs standard SQL. Cet exemple réplique ces fonctionnalités (avec quelques ajouts par souci d'exhaustivité), en utilisant la syntaxe SQL Server. Il est très utile pour les développeurs Oracle de trouver de nombreuses fonctionnalités manquantes dans leurs requêtes hiérarchiques sur d'autres bases de données, mais il sert également à présenter ce qui peut être fait avec une requête hiérarchique en général.

  WITH tbl AS (
       SELECT id, name, parent_id
         FROM mytable)
     , tbl_hierarchy AS (
       /* Anchor */
       SELECT 1 AS "LEVEL"
            --, 1 AS CONNECT_BY_ISROOT
            --, 0 AS CONNECT_BY_ISBRANCH
            , CASE WHEN t.id IN (SELECT parent_id FROM tbl) THEN 0 ELSE 1 END AS CONNECT_BY_ISLEAF
            , 0 AS CONNECT_BY_ISCYCLE
            , '/' + CAST(t.id   AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_id
            , '/' + CAST(t.name AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_name
            , t.id AS root_id
            , t.*
         FROM tbl t
        WHERE t.parent_id IS NULL                            -- START WITH parent_id IS NULL
       UNION ALL
       /* Recursive */
       SELECT th."LEVEL" + 1 AS "LEVEL"
            --, 0 AS CONNECT_BY_ISROOT
            --, CASE WHEN t.id IN (SELECT parent_id FROM tbl) THEN 1 ELSE 0 END AS CONNECT_BY_ISBRANCH
            , CASE WHEN t.id IN (SELECT parent_id FROM tbl) THEN 0 ELSE 1 END AS CONNECT_BY_ISLEAF
            , CASE WHEN th.SYS_CONNECT_BY_PATH_id LIKE '%/' + CAST(t.id AS VARCHAR(MAX)) + '/%' THEN 1 ELSE 0 END AS CONNECT_BY_ISCYCLE
            , th.SYS_CONNECT_BY_PATH_id   + CAST(t.id   AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_id
            , th.SYS_CONNECT_BY_PATH_name + CAST(t.name AS VARCHAR(MAX)) + '/' AS SYS_CONNECT_BY_PATH_name
            , th.root_id
            , t.*
         FROM tbl t
              JOIN tbl_hierarchy th ON (th.id = t.parent_id) -- CONNECT BY PRIOR id = parent_id
        WHERE th.CONNECT_BY_ISCYCLE = 0)                     -- NOCYCLE
SELECT th.*
     --, REPLICATE(' ', (th."LEVEL" - 1) * 3) + th.name AS tbl_hierarchy
  FROM tbl_hierarchy th
       JOIN tbl CONNECT_BY_ROOT ON (CONNECT_BY_ROOT.id = th.root_id)
 ORDER BY th.SYS_CONNECT_BY_PATH_name;                       -- ORDER SIBLINGS BY name

Fonctionnalités CONNECT BY démontrées ci-dessus, avec des explications:

  • Clauses
    • CONNECT BY: Spécifie la relation qui définit la hiérarchie.
    • START WITH: Spécifie les nœuds racine.
    • ORDER SIBLINGS BY: Résultats des commandes correctement.
  • Paramètres
    • NOCYCLE: Arrête le traitement d'une branche lorsqu'une boucle est détectée. Les hiérarchies valides sont des graphes acycliques dirigés et les références circulaires violent cette construction.
  • Les opérateurs
    • PRIOR: Obtient les données du parent du noeud.
    • CONNECT_BY_ROOT: Obtient les données de la racine du noeud.
  • Pseudocolonnes
    • NIVEAU: Indique la distance entre le nœud et sa racine.
    • CONNECT_BY_ISLEAF: Indique un nœud sans enfants.
    • CONNECT_BY_ISCYCLE: Indique un noeud avec une référence circulaire.
  • Les fonctions
    • SYS_CONNECT_BY_PATH: Retourne une représentation aplatie / concaténée du chemin d'accès au noeud depuis sa racine.