5. Адаптация новых сотрудников к особенностям организации и новому рабочему месту, когда их знакомят.
6. Обучение как вновь прибывших работников для приобретения ими эффективных навыков работы, так и опытных сотрудников предприятия с целью повышения их квалификации и переквалификации.
7. Мотивация, когда определяются методы и средства воздействия на персонал, с помощью которых сотрудников побуждают к результативному труду.
8. Оценка трудовой деятельности, включающая текущий контроль результатов труда, проведение различных аттестационных мероприятий и доведение их результатов до сотрудников.
9. Формирование кадрового резерва и продвижение сотрудников в организации, когда решаются вопросы их повышения, перевода в другие подразделения или на другие участки работы, понижения в должности и увольнения.
Работа с деревьями в Oracle
Просмотров: 18344
Рекомендовано для:
Oracle Database 8i
Oracle Database 9i R1
Oracle Database 9i R2
Oracle Database 10g R1
Oracle Database 10g R2
Oracle Database 11g R1
Эта статья посвящена работе с деревьями в Oracle. В большинстве современных СУБД нет встроенных средств для работы с иерархическими структурами, для построения дерева на основе таблицы приходится писать громоздкие процедуры, или разносить данные по нескольким таблицам.
Oracle предлагает достаточно средств, для удовлетворения практически любых желаний при работе с иерархиями. В это статье я попытаюсь на примерах показать, как это применять, и какие результаты достигаются.
Классическим примером дерева является иерархия сотрудников на предприятии. Для демонстрации работы с деревьями создадим таблицу и заполним ее данными:
CREATE TABLE EMPL (ID INTEGER PRIMARY KEY, NAME VARCHAR(50), PARENT_ID REFERENCES EMPL); Добавим данные в таблицу:
INSERT INTO EMPL VALUES (1, 'Директор', NULL);INSERT INTO EMPL VALUES (2, 'Заместитель по экономике', 1);INSERT INTO EMPL VALUES (3, 'Заместитель по ИТ', 1);INSERT INTO EMPL VALUES (4, 'Программист', 3);INSERT INTO EMPL VALUES (5, 'Программист-стажер', 4);INSERT INTO EMPL VALUES (6, 'Главный бухгалтер', 1);INSERT INTO EMPL VALUES (7, 'Бухгалтер 1', 6);INSERT INTO EMPL VALUES (8, 'Бухгалтер 2', 6); Проверяем:
SQL> SELECT * FROM EMPL; ID NAME PARENT_ID---------- -------------------------------------------------- ---------- 1 Директор 2 Заместитель по экономике 1 3 Заместитель по ИТ 1 4 Программист 3 5 Программист-стажер 4 6 Главный бухгалтер 1 7 Бухгалтер 1 6 8 Бухгалтер 2 68 rows selected. Значения столбца PARENT_ID, реально указывают на другие строки в таблице EMPL. Для отображения получившийся иерархии имея в распоряжении стандартный SQL и любой язык программирования, такой как C++, Delphi или C# придется написать достаточно громоздкий код. Отобрать сначала узлы верхнего уровня, далее в зависимости от выбранного узла запрашивать подчиненные записи и т.д.
В распоряжение пользователя, Oracle предоставляет предложение языка PL/SQL - CONNECT BY. Оно позволяет строить иерархию одним запросом, просто и изящно:
SELECT NAME, ID, PARENT_IDFROM EMPLCONNECT BY PRIOR ID = PARENT_ID;NAME ID PARENT_ID-------------------------------------------------- ---------- ----------Заместитель по экономике 2 1Заместитель по ИТ 3 1Программист 4 3Программист-стажер 5 4Главный бухгалтер 6 1Бухгалтер 1 7 6Бухгалтер 2 8 6Программист 4 3Программист-стажер 5 4Программист-стажер 5 4Бухгалтер 1 7 6Бухгалтер 2 8 6Директор 1Заместитель по экономике 2 1Заместитель по ИТ 3 1Программист 4 3Программист-стажер 5 4Главный бухгалтер 6 1Бухгалтер 1 7 6Бухгалтер 2 8 620 rows selected. Полученный результат кажется не совсем понятным, но если внимательно посмотреть, то видно, что выстроены все возможные деревья и поддеревья. Теперь добавим в запрос конструкцию START WITH:
SELECT NAME, ID, PARENT_IDFROM EMPLCONNECT BY PRIOR ID = PARENT_IDSTART WITH ID IN (SELECT ID FROM EMPL WHERE PARENT_ID IS NULL);NAME ID PARENT_ID-------------------------------------------------- ---------- ----------Директор 1Заместитель по экономике 2 1Заместитель по ИТ 3 1Программист 4 3Программист-стажер 5 4Главный бухгалтер 6 1Бухгалтер 1 7 6Бухгалтер 2 8 68 rows selected. Обратите внимание, что в предложении START WITH использован вложенный запрос для определения кто стоит на самом верху. Обычно, в поле PARENT_ID для узлов, используют NULL или -1. Естественно, что их может быть один и более. Сама конструкция START WIDTH указывает, откуда начинать строить дерево.
Теперь, наведем немного порядок, упорядочим записи, и покажем кто находится на каком уровне иерархии. Для этого, Oracle предоставляет псевдоколонку LEVEL. Она может быть использована только в том случае, если в запросе присутствует CONNECT BY. Для упрощения укажем ID =1:
SELECT NAME, ID, PARENT_ID, LEVELFROM EMPLCONNECT BY PRIOR ID = PARENT_IDSTART WITH ID = 1;NAME ID PARENT_ID LEVEL---------------------------------------- ---------- ---------- ----------Директор 1 1Заместитель по экономике 2 1 2Заместитель по ИТ 3 1 2Программист 4 3 3Программист-стажер 5 4 4Главный бухгалтер 6 1 2Бухгалтер 1 7 6 3Бухгалтер 2 8 6 38 rows selected. Колонка LEVEL может быть использована для отметки записи. Используем оператор конкатенации (||)для добавления пробелов в начале каждой строки:
SELECT LPAD(' ', (LEVEL - 1) * 2) || NAME AS H_NAME, ID, PARENT_ID, LEVELFROM EMPLCONNECT BY PRIOR ID = PARENT_IDSTART WITH ID = 1;H_NAME ID PARENT_ID LEVEL----------------------------------- ---------- ---------- ----------Директор 1 1 Заместитель по экономике 2 1 2 Заместитель по ИТ 3 1 2 Программист 4 3 3 Программист-стажер 5 4 4 Главный бухгалтер 6 1 2 Бухгалтер 1 7 6 3 Бухгалтер 2 8 6 38 rows selected. Для ограничения вывода можно использовать стандартное условие WHERE. Уберем из вывода сотрудников, у которых уровень меньше, либо равен 3:
SELECT LPAD(' ', (LEVEL - 1) * 2) || NAME AS H_NAME, ID, PARENT_ID, LEVELFROM EMPLWHERE LEVEL <=3CONNECT BY PRIOR ID = PARENT_IDSTART WITH ID = 1;H_NAME ID PARENT_ID LEVEL----------------------------------- ---------- ---------- ----------Директор 1 1 Заместитель по экономике 2 1 2 Заместитель по ИТ 3 1 2 Программист 4 3 3 Главный бухгалтер 6 1 2 Бухгалтер 1 7 6 3 Бухгалтер 2 8 6 37 rows selected. Если вы хотите произвести сортировку, то стоит учитывать, ORDER BY работает не совсем так, как в случае с простыми данными, без иерархии. Продемонстрируем это:
SELECT LPAD(' ', (LEVEL - 1) * 2) || NAME AS H_NAME, ID, PARENT_ID, LEVELFROM EMPLCONNECT BY PRIOR ID = PARENT_IDSTART WITH ID = 1ORDER BY LEVEL, NAME;H_NAME ID PARENT_ID LEVEL----------------------------------- ---------- ---------- ----------Директор 1 1 Главный бухгалтер 6 1 2 Заместитель по ИТ 3 1 2 Заместитель по экономике 2 1 2 Бухгалтер 1 7 6 3 Бухгалтер 2 8 6 3 Программист 4 3 3 Программист-стажер 5 4 48 rows selected. Как видно, сортировка прошла по колонке LEVEL, и затем уже по имени, но замете, что самое важное, иерархия сохранена, и внутри каждого уровня иерархии уже идет сортировка по имени. А что же будет, если из условия сортировки убрать поле LEVEL?
SELECT LPAD(' ', (LEVEL - 1) * 2) || NAME AS H_NAME, ID, PARENT_ID, LEVELFROM EMPLCONNECT BY PRIOR ID = PARENT_IDSTART WITH ID = 1ORDER BY NAME;H_NAME ID PARENT_ID LEVEL----------------------------------- ---------- ---------- ---------- Бухгалтер 1 7 6 3 Бухгалтер 2 8 6 3 Главный бухгалтер 6 1 2Директор 1 1 Заместитель по ИТ 3 1 2 Заместитель по экономике 2 1 2 Программист 4 3 3 Программист-стажер 5 4 48 rows selected. Как видно вся иерархия поломалась. Чтобы указать Oracle, что сортировать надо только в пределах одного уровня иерархии, поможет маленькая добавка в виде оператора SIBLINGS. Достаточно изменить условие сортировки на ORDER SIBLINGS BY <поле> – и все встанет на свои места.
SELECT LPAD(' ', (LEVEL - 1) * 2) || NAME AS H_NAME, ID, PARENT_ID, LEVELFROM EMPLCONNECT BY PRIOR ID = PARENT_IDSTART WITH ID = 1ORDER SIBLINGS BY NAME;H_NAME ID PARENT_ID LEVEL----------------------------------- ---------- ---------- ----------Директор 1 1 Главный бухгалтер 6 1 2 Бухгалтер 1 7 6 3 Бухгалтер 2 8 6 3 Заместитель по ИТ 3 1 2 Программист 4 3 3 Программист-стажер 5 4 4 Заместитель по экономике 2 1 28 rows selected. Еще одна очень полезная функция - SYS_CONNECT_BY_PATH().Она принимает два параметра через запятую: название колонки и строку с символом-разделителем. Для иллюстрации ее работы выполним такой запрос:
SELECT SYS_CONNECT_BY_PATH(NAME, '/') AS PATHFROM EMPLWHERE ID=5START WITH PARENT_ID IS NULLCONNECT BY PRIOR ID = PARENT_ID;PATH-------------------------------------------------/Директор/Заместитель по ИТ/Программист/Программист-стажер В результате получаем путь от большого босса до стажера. Сейчас это выглядит как путь в файловом менеджере, но эта функция может быть вам очень полезна, к тому же разделитель можно сменить.
Топаем дальше. Псевдоколонка CONNECT_BY_ISLEAF. Ее можно использовать так же, как LEVEL. В этой колонке напротив каждой строки проставляется 0 или 1. Если есть потомки – то 0. Если потомков нет, такой узел в дереве называется «листом», тогда и значение в поле CONNECT_BY_ISLEAF будет равно 1.
Помните такую конструкцию PRIOR, которая позволяла ссылаться на родительскую запись? Так вот, есть такой оператор, CONNECT_BY_ROOT, который ссылается на корень дерева. Для демонстрации работы выполним:
SELECT ID, NAME, PARENT_ID, LEVEL, CONNECT_BY_ISLEAF AS ISLEAF, PRIOR NAME AS PARENT, CONNECT_BY_ROOT NAME AS ROOTFROM EMPLSTART WITH PARENT_ID IS NULLCONNECT BY PRIOR ID = PARENT_IDORDER SIBLINGS BY NAME; Если при построении дерева вы получаете ошибку, о том, что найдена петля (цикл), то это означает – дерево неверно спроектировано. На такой случай, есть NOCYCLE. Это позволит вам избежать бесконечных циклов. Для иллюстрации работы, выполним:
UPDATE EMPL SET PARENT_ID=5 WHERE ID=5;COMMIT; Теперь, программист-стажер подчиняется сам себе. Выполняем:
SELECT LPAD(' ', (LEVEL - 1) * 2) || NAME AS H_NAME, ID, PARENT_ID, LEVELFROM EMPLCONNECT BY PRIOR ID = PARENT_IDSTART WITH ID = 1ORDER BY LEVEL, NAME; H_NAME ID PARENT_ID LEVEL------------------------------ ---------- ---------- ----------Директор 1 1 Главный бухгалтер 6 1 2 Заместитель по ИТ 3 1 2 Заместитель по экономике 2 1 2 Бухгалтер 1 7 6 3 Бухгалтер 2 8 6 3 Программист 4 3 37 rows selected. И видим, что стажера нет, он выпал из дерева. Oracle не видит пути, и не включает элемент в иерархию. Попробуем заставить его начать со стажера. Для этого немного поменяем условия запроса:
SELECT LPAD(' ', (LEVEL - 1) * 2) || NAME AS H_NAME, ID, PARENT_ID, LEVELFROM EMPLCONNECT BY PRIOR ID = PARENT_IDSTART WITH ID = 5ORDER BY LEVEL, NAME;FROM EMPL *error at line 6:ORA-01436: CONNECT BY loop in user data Что бы избежать таких неприятных ситуаций, изменим запрос, что бы он выглядел так:
SELECT LPAD(' ', (LEVEL - 1) * 2) || NAME AS H_NAME, ID, PARENT_ID, LEVELFROM EMPLCONNECT BY NOCYCLE PRIOR ID = PARENT_IDSTART WITH ID = 5ORDER BY LEVEL, NAME; H_NAME ID PARENT_ID LEVEL------------------------------ ---------- ---------- ----------Программист-стажер JOIN не работает с CONNECT BY
Например, построим отчет в котором укажем сотрудника и его непосредственного начальника:
SELECT LPAD(' ', (LEVEL - 1) * 2) || A.NAME AS Н_NAME, B.NAME AS BOSS_NAME FROM EMPL A, EMPL BWHERE A.PARENT_ID = B.ID(+)CONNECT BY PRIOR A.ID = A.PARENT_IDSTART WITH A.ID = 1; На старых версиях Oracle, можно получить сообщение об ошибке:
ERROR at line 4: ORA-01437: cannot have join with CONNECT BY
Обойти эту проблему можно создав представление:
CREATE OR REPLACE VIEW V_EMPL ASSELECT LPAD(' ', (LEVEL - 1) * 2) || NAME AS H_NAME, ID, PARENT_ID, LEVEL AS THE_LEVELFROM EMPLCONNECT BY PRIOR ID = PARENT_IDSTART WITH ID = 1; Колонку LEVEL переименовали, чтобы представление не заканчивалось на зарезервированное слово.
SQL> SELECT * FROM V_EMPL; Сейчас можно выполнить JOIN
SELECT A.H_NAME, B.NAME AS BOSS_NAMEFROM V_EMPL A, EMPL BWHERE A.PARENT_ID = A.ID(+); Если обратите внимание, то увидите что выполнено OUTER JOIN, потому что в списке нет большого босса.
Подзапросы, списки и CONNECT BY
Вместо VIEW и JOIN можно использовать вложенные запросы в списке выборки:
SELECT LPAD(' ', (LEVEL - 1) * 2) || NAME AS H_NAME, (SELECT NAME FROM EMPL B WHERE B. ID = A.PARENT_ID) AS BOSS_NAMEFROM EMPL ACONNECT BY PRIOR ID = PARENT_IDSTART WITH ID = 1;H_NAME BOSS_NAME----------------------------------- -----------------------------------Директор Заместитель по экономике Директор Заместитель по ИТ Директор Программист Заместитель по ИТ Программист-стажер Программист Главный бухгалтер Директор Бухгалтер 1 Главный бухгалтер Бухгалтер 2 Главный бухгалтер8 rows selected. Производительность
Для увеличения производительности, вам потребуется создать индексы на таблицу, которые позволят Oracle быстрее получать ответ на вопрос, «кто является детьми некого родителя Х?»:
CREATE INDEX EMPL_IDX1 ON EMPL (ID, PARENT_ID);CREATE INDEX EMPL_IDX2 ON EMPL (PARENT_ID, ID); Надеюсь эта небольшая статья поможет вам в работе с деревьями. Удачи.
БИОХИМИЯ ТКАНЕЙ ЗУБА В составе зуба выделяют минерализованные и неминерализованные ткани...
Типология суицида. Феномен суицида (самоубийство или попытка самоубийства) чаще всего связывается с представлением о психологическом кризисе личности...
