Иерархические запросы в ORACLE Печать E-mail
Рейтинг: / 11
ХудшаяЛучшая 

Иерархические запросы в ORACLE

в Oracle присутствует специфический функционал для работы с данными в виде иерархии

-- connect by ключевой оператор который используется для работы и иерархией в oralce

-- данными где присутствует id и parent_id - ид родителя

Запрос перечисление

-- level является перечислением - цикл по датам

-- таким образом, для перечисления интервала дат

-- следует воспользоваться данным запросом

  select level nnnn 

       from dual

     connect by level <= 10   

      NNNN

      1

      2

      3

      4

      5

      6

      7

      8

      9

      10

 

    

Запрос перечисление по датам

-- простое перечисление

-- специфическая переменная level отражает уровень иерархии ,который в данном запросе условен

 select trunc(sysdate-10-1+level) daten 

       from dual

     connect by level <= (sysdate-(sysdate-10))

      DATEN

    -----

      28.09.2012

      29.09.2012

      30.09.2012

      01.10.2012

      02.10.2012

      03.10.2012

      04.10.2012

      05.10.2012

      06.10.2012

      07.10.2012

 

    

Иерархический запрос

-- level яляется перечислением

-- пример иерархического запроса

-- специфическая переменная level отражает уровень иерархии в запросе

-- оператор connect BY отображает условие иерархии в запросе

-- в данном

WITH T AS

(

    SELECT 1 ID, NULL parent_id, 'AAAA' lname FROM dual UNION ALL

    SELECT 2 ID, 1    parent_id, 'BBBB' lname FROM dual UNION ALL

    SELECT 3 ID, 1    parent_id, 'CCCC' lname FROM dual UNION ALL

    SELECT 5 ID, 3    parent_id, 'DDDD' lname FROM dual UNION ALL

    SELECT 4 ID, NULL parent_id, 'FFFF' lname FROM dual

)

SELECT T.*,level,rpad(' ',(level-1)*4)||lname fstr

  FROM T

 connect BY PRIOR ID = parent_id

 

ID    PARENT_ID   LNAME LEVEL FSTR

--- ----- --- ----- ---- -----

      2 1  BBBB   1     BBBB

      3     1     CCCC  1     CCCC

      5     3     DDDD  2         DDDD

      5     3     DDDD  1     DDDD

      1           AAAA  1     AAAA

      2     1     BBBB  2         BBBB

      3     1     CCCC  2         CCCC

      5     3     DDDD  3             DDDD

      4           FFFF  1     FFFF

 

 start WITH

-- пример иерархии , когда выбираем ветку начала иерархии

-- опреатор  start WITH id = 1 отбражает условие выбора начальной ветки иерархии

WITH T AS

(

    SELECT 1 ID, NULL parent_id, 'AAAA' lname FROM dual UNION ALL

    SELECT 2 ID, 1    parent_id, 'BBBB' lname FROM dual UNION ALL

    SELECT 3 ID, 1    parent_id, 'CCCC' lname FROM dual UNION ALL

    SELECT 5 ID, 3    parent_id, 'DDDD' lname FROM dual UNION ALL

    SELECT 4 ID, NULL parent_id, 'FFFF' lname FROM dual

)

SELECT T.*,level,rpad(' ',(level-1)*4)||lname fstr

  FROM T

 start WITH id = 1

 connect BY PRIOR ID = parent_id

 

      ID    PARENT_ID   LNAME LEVEL FSTR

      1           AAAA  1     AAAA

      2     1     BBBB  2         BBBB

      3     1     CCCC  2         CCCC

      5     3     DDDD  3             DDDD

 

 

 

 пример иерархии , c сортировкой

-- как видим оператор order by не совсем соответствует нашим требованиям

WITH T AS

(

    SELECT 1 ID, NULL parent_id, 'AAAA' lname FROM dual UNION ALL

    SELECT 2 ID, 1    parent_id, 'BBBB' lname FROM dual UNION ALL

    SELECT 3 ID, 1    parent_id, 'CCCC' lname FROM dual UNION ALL

    SELECT 5 ID, 3    parent_id, 'DDDD' lname FROM dual UNION ALL

    SELECT 4 ID, NULL parent_id, 'FFFF' lname FROM dual

)

SELECT T.*,level,rpad(' ',(level-1)*4)||lname fstr

  FROM T

 connect BY PRIOR ID = parent_id

 ORDER SIBLINGS  by lname

ORDER SIBLINGS

--коректнее было бы использовать оперетор ORDER SIBLINGS

ID    PARENT_ID   LNAME LEVEL FSTR

      2     1     BBBB  1     BBBB

      3     1     CCCC  1     CCCC

      5     3     DDDD  2         DDDD

      5     3     DDDD  1     DDDD

      1           AAAA  1     AAAA

      2     1     BBBB  2         BBBB

      3     1     CCCC  2         CCCC

      5     3     DDDD  3             DDDD

      4           FFFF  1     FFFF

 
След. »