Заполнение поля иерархической таблицы из этой иерархической таблицы

@rogee · Регистрация: 16.02.2018

Студворк — интернет-сервис помощи студентам

Привет всем, врываюсь в Oracle. Такая задача, если следующая таблица

SQL
1
2
3
4
5
6
7
CREATE TABLE rost_task3_irhy
(
       id_f NUMBER(10) --код
       ,id_par NUMBER (10) --код родителя
       ,name_v varchar2(100) --название
       ,full_name VARCHAR(1000) --полное название
)

В ней есть вот такие записи

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
INSERT ALL
       INTO rost_task3_irhy VALUES (1 ,NULL ,'Департамент розничных технологий')
       INTO rost_task3_irhy VALUES (2 ,NULL ,'Департамент продаж ГМ')
       INTO rost_task3_irhy VALUES (3 ,NULL ,'Департамент продаж ММ')
       INTO rost_task3_irhy VALUES (4 ,NULL ,'Департамент продаж формата Аптеки')
       INTO rost_task3_irhy VALUES (5 ,NULL ,'Департамент продаж дрогери')
       INTO rost_task3_irhy VALUES (6 ,1 ,'Управление внедрения технологий')
       INTO rost_task3_irhy VALUES (7 ,1 ,'Управление разработки и автоматизации')
       INTO rost_task3_irhy VALUES (8 ,1 ,'Управление бизнес-аналитики')
       INTO rost_task3_irhy VALUES (9 ,6 ,'Руководитель управления внедрения технологий')
       INTO rost_task3_irhy VALUES (10 ,6 ,'Руководитель стратегических проектов')
       INTO rost_task3_irhy VALUES (11 ,6 ,'Руководитель проекта')
       INTO rost_task3_irhy VALUES (12 ,9 ,'Администратор проекта')
       INTO rost_task3_irhy VALUES (13 ,9 ,'Старший менеджер')
       INTO rost_task3_irhy VALUES (14 ,13 ,'Менеджер')
       INTO rost_task3_irhy VALUES (15 ,7 ,'Руководитель управления разработки и автоматизации')
       INTO rost_task3_irhy VALUES (16 ,15 ,'Старший менеджер')
       INTO rost_task3_irhy VALUES (17 ,16 ,'Менеджер')
       INTO rost_task3_irhy VALUES (18 ,8 ,'Руководитель управления бизнес-анализа')
       INTO rost_task3_irhy VALUES (19 ,18 ,'Ведущий аналитик')
       INTO rost_task3_irhy VALUES (20 ,19 ,'Аналитик бизнес-процессов')
       INTO rost_task3_irhy VALUES (21 ,20 ,'Методист')
       INTO rost_task3_irhy VALUES (22 ,2 ,'Сектор экономического анализа СООГМ')
       INTO rost_task3_irhy VALUES (23 ,2 ,'Сектор анализа ассортимента СП ГМ/МС')
       INTO rost_task3_irhy VALUES (24 ,2 ,'Сектор производственного учета')
       INTO rost_task3_irhy VALUES (25 ,22 ,'Руководитель сектора')
       INTO rost_task3_irhy VALUES (26 ,25 ,'Ведущий специалист')
       INTO rost_task3_irhy VALUES (27 ,26 ,'Менеджер')
       INTO rost_task3_irhy VALUES (28 ,27 ,'Специалист')
       INTO rost_task3_irhy VALUES (29 ,24 ,'Старший специалист')
       INTO rost_task3_irhy VALUES (30 ,29 ,'Специалист')
       INTO rost_task3_irhy VALUES (31 ,23 ,'Руководитель сектора')
       INTO rost_task3_irhy VALUES (32 ,31 ,'Аналитик')
       INTO rost_task3_irhy VALUES (33 ,5 ,'Отдел методологии управления продажами')
       INTO rost_task3_irhy VALUES (34 ,5 ,'Отдел по работе с потерями МК')
       INTO rost_task3_irhy VALUES (35 ,34 ,'Начальник отдела')
       INTO rost_task3_irhy VALUES (36 ,35 ,'Ведущий аналитик направления')
       INTO rost_task3_irhy VALUES (37 ,35 ,'Ведущий специалист направления')
       INTO rost_task3_irhy VALUES (38 ,37 ,'Специалист направления')
       INTO rost_task3_irhy VALUES (39 ,36 ,'Аналитик направления')
       INTO rost_task3_irhy VALUES (40 ,33 ,'Начальник отдела методологии управления')
       INTO rost_task3_irhy VALUES (41 ,40 ,'Ведущий специалист')
       INTO rost_task3_irhy VALUES (42 ,41 ,'Специалист направления')
SELECT * FROM dual;

И в ласт поле - полное название, иерархия по сути, заполняется вот так

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
BEGIN
  FOR loop_count IN 1 .. 42
  loop
    UPDATE rost_task3_irhy
       SET full_name =
           (SELECT sys_connect_by_path(name_v, '->') AS path
              FROM rost_task3_irhy
             WHERE id_f = loop_count
             START WITH id_par IS NULL
            CONNECT BY prior id_f = id_par)
     WHERE id_f = loop_count;
  END loop;
END;

Задача, чтобы при обновлении, удалении, добавлении обновлялось 4 поле, вся иерархия.

Уперся в составной триггер, пока просто на обновление, но он вызывает рекурсию бесконечную, не могу понять как сделать его правильным

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
CREATE OR REPLACE TRIGGER rost_task3_irhy_trg
  FOR UPDATE ON rost_task3_irhy
  compound TRIGGER  bup BOOLEAN;
 
  BEFORE each ROW IS
  BEGIN
    IF updating
    THEN
      bup := TRUE;
    END IF;
  END BEFORE each ROW;
 
  after statement IS
  BEGIN
    IF bup = TRUE
    THEN
    BEGIN
        FOR loop_count IN 1 .. 42
        loop
          UPDATE rost_task3_irhy
             SET full_name =
                 (SELECT sys_connect_by_path(name_v, '->') AS path
                    FROM rost_task3_irhy
                   WHERE id_f = loop_count
                   START WITH id_par IS NULL
                  CONNECT BY prior id_f = id_par)
           WHERE id_f = loop_count;
        END loop;
      END;
    END IF;
  END after statement;
END;

Добавлено через 28 минут
Вот так можно посмотреть таблицу

SQL
1
2
3
4
SELECT level, id_f, id_par, lpad(' ', 15*level)||name_v AS Tree, full_name
FROM rost_task3_irhy
START WITH id_par IS NULL
CONNECT BY prior id_f = id_par

@rogee · 19.07.2019, 16:56 **[ТС]**

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
CREATE OR REPLACE TRIGGER rost_task3_irhy_trg
  FOR UPDATE OR INSERT OF id_f, id_par, name_v ON rost_task3_irhy
  compound TRIGGER
--переменные
  v_bup       NUMBER;
  v_f_id      NUMBER;
  v_par_id    NUMBER;
  v_new_name  varchar2(1000);
  v_old_name  varchar2(1000);
  v_full_name varchar2(1000);
  TYPE v_tbl_upd IS record(
     f_id   NUMBER
    ,f_par  NUMBER
    ,f_name VARCHAR(200));
  TYPE v_tbl_change IS TABLE OF v_tbl_upd;
  --до обновления
  BEFORE each ROW IS
  BEGIN
    IF inserting
    THEN
      v_bup      := 1;
      v_f_id     := :NEW.id_f;
      v_par_id   := :NEW.id_par;
      v_new_name := :NEW.name_v;
      v_old_name := :OLD.name_v;
    elsif updating
    THEN
      v_bup      := 2;
      v_f_id     := :NEW.id_f;
      v_par_id   := :NEW.id_par;
      v_new_name := :NEW.name_v;
      v_old_name := :OLD.name_v;
      
      v_tbl_upd(v_tbl_upd.f_id) := :OLD.id_f;
      v_tbl_upd(v_tbl_upd.f_par) := :OLD.id_par;
      v_tbl_upd(v_tbl_upd.f_name) := :OLD.name_v;
                
    elsif deleting
    THEN
      v_bup      := 3;
      v_old_name := :OLD.name_v;
    END IF;
  END BEFORE each ROW;
  --добавление
  after statement IS
  BEGIN
    IF v_bup = 1
    THEN
      BEGIN
        UPDATE rost_task3_irhy
           SET full_name =
               (SELECT substr(sys_connect_by_path(name_v, '->'), 3) AS path
                  FROM rost_task3_irhy
                 WHERE id_f = v_f_id
                 START WITH id_par IS NULL
                CONNECT BY prior id_f = id_par)
         WHERE id_f = v_f_id;
      END;
    END IF; 
    --обновление
    IF v_bup = 2 /*and
                  v_new_name != v_old_name*/
    THEN
      BEGIN
        MERGE INTO rost_task3_irhy fir
        USING (SELECT id_f
                     ,id_par
                     ,name_v
                     ,substr(sys_connect_by_path(name_v, '->'), 3) AS full_name
                 FROM rost_task3_irhy
                WHERE id_par IN (SELECT id_par
                                   FROM rost_task3_irhy
                                  START WITH id_par IS NULL
                                 CONNECT BY prior id_f = id_par)
                START WITH id_par IS NULL
               CONNECT BY prior id_f = id_par) sec
        ON (fir.id_f = sec.id_f)
        WHEN matched THEN
          UPDATE
             SET fir.id_par    = sec.id_par
                ,fir.name_v    = sec.name_v
                ,fir.full_name = sec.full_name;
      END;   
    END IF;
  END after statement;
END;
/

@rogee · 25.07.2019, 10:01 **[ТС]**

решение

SQL
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
CREATE OR REPLACE TRIGGER rost_task3_irhy_trg
  FOR UPDATE OR INSERT OF id_f, id_par, name_v ON rost_task3_irhy
  compound TRIGGER
--переменные
  TYPE to_calculate_type IS TABLE OF NUMBER;
  TYPE v_tbl_upd IS record(
     to_calculate_arr NUMBER
    ,path_type        VARCHAR(1000));
  TYPE v_tbl_change IS TABLE OF v_tbl_upd;
 
  tab_change       v_tbl_change;
  update_v to_calculate_type := to_calculate_type();
  --очистка record  
  BEFORE statement IS
  BEGIN
    update_v := to_calculate_type();
  END BEFORE statement;
 
  after each ROW IS
  BEGIN
    IF updating OR inserting
    THEN
      dbms_output.put_line('b');
      update_v.extend;
      update_v(update_v.last) := :NEW.id_f;
    END IF;
  END after each ROW;
 
  after statement IS
  BEGIN
    FOR i IN 1 .. update_v.count
    loop
      SELECT id_f
            ,calc_path(rti.id_f, rti.id_par, rti.name_v)
        bulk collect
        INTO tab_change
        FROM rost_task3_irhy rti
      CONNECT BY prior rti.id_f = rti.id_par
       START WITH rti.id_f = update_v(i);
    END loop;
    BEGIN
      forall i IN 1 .. tab_change.count
        UPDATE rost_task3_irhy rti
           SET rti.full_name = tab_change(i).path_type
         WHERE rti.id_f = tab_change(i).to_calculate_arr;
    END;
  END after statement;
END;

Новые блоги и статьи Все статьи Все блоги /
SDL3 для Web (WebAssembly): Работа со звуком через SDL3_mixer 8Observer8 08.02.2026 Содержание блога Пошагово создадим проект для загрузки звукового файла и воспроизведения звука с помощью библиотеки SDL3_mixer. Звук будет воспроизводиться по клику мышки по холсту на Desktop и по. . .	SDL3 для Web (WebAssembly): Основы отладки веб-приложений на SDL3 по USB и Wi-Fi, запущенных в браузере мобильных устройств 8Observer8 07.02.2026 Содержание блога Браузер Chrome имеет средства для отладки мобильных веб-приложений по USB. В этой пошаговой инструкции ограничимся работой с консолью. Вывод в консоль - это часть процесса. . .	SDL3 для Web (WebAssembly): Обработчик клика мыши в браузере ПК и касания экрана в браузере на мобильном устройстве 8Observer8 02.02.2026 Содержание блога Для начала пошагово создадим рабочий пример для подготовки к экспериментам в браузере ПК и в браузере мобильного устройства. Потом напишем обработчик клика мыши и обработчик. . .	Философия технологии iceja 01.02.2026 На мой взгляд у человека в технических проектах остается роль генерального директора. Все остальное нейронки делают уже лучше человека. Они не могут нести предпринимательские риски, не могут. . .
SDL3 для Web (WebAssembly): Вывод текста со шрифтом TTF с помощью SDL3_ttf 8Observer8 01.02.2026 Содержание блога В этой пошаговой инструкции создадим с нуля веб-приложение, которое выводит текст в окне браузера. Запустим на Android на локальном сервере. Загрузим Release на бесплатный. . .	SDL3 для Web (WebAssembly): Сборка C/C++ проекта из консоли 8Observer8 30.01.2026 Содержание блога Если вы откроете примеры для начинающих на официальном репозитории SDL3 в папке: examples, то вы увидите, что все примеры используют следующие четыре обязательные функции, а. . .	SDL3 для Web (WebAssembly): Установка Emscripten SDK (emsdk) и CMake для сборки C и C++ приложений в Wasm 8Observer8 30.01.2026 Содержание блога Для того чтобы скачать Emscripten SDK (emsdk) необходимо сначало скачать и уставить Git: Install for Windows. Следуйте стандартной процедуре установки Git через установщик. . . .	SDL3 для Android: Подключение Box2D v3, физика и отрисовка коллайдеров 8Observer8 29.01.2026 Содержание блога Box2D - это библиотека для 2D физики для анимаций и игр. С её помощью можно определять были ли коллизии между конкретными объектами. Версия v3 была полностью переписана на Си, в. . .