Рекурсивный запрос для табличных зависимостей не повторяется не так много, как хотелось бы
У меня была идея, что я мог бы написать запрос, чтобы найти все таблицы-потомки корневой таблицы, основанные на внешних ключах.
Запрос выглядит так:
select level, lpad(' ', 2 * (level - 1)) || uc.table_name as "TABLE", uc.constraint_name, uc.r_constraint_name
from all_constraints uc
where uc.constraint_type in ('R', 'P')
start with uc.table_name = 'ROOT_TAB'
connect by nocycle prior uc.constraint_name = uc.r_constraint_name
order by level asc;
Результаты, которые я получаю, выглядят так:
1 ROOT_TAB XPKROOTTAB
1 ROOT_TAB R_20 XPKPART_TAB
2 CHILD_TAB_1 R_40 XPKROOTTAB
2 CHILD_TAB_2 R_115 XPKROOTTAB
2 CHILD_TAB_3 R_50 XPKROOTTABЭтот результат - все дочерние таблицы ROOT_TAB, но запрос не рекурсирует детям CHILD_TAB_1, CHILD_TAB_2, или же CHILD_TAB_3,
Рекурсивные запросы являются новыми для меня, поэтому я предполагаю, что что-то упустил в connect by пункт, но я рисую пробел здесь. Действительно ли возможно получить полную иерархию ROOT_TAB в одном запросе, или мне лучше обернуть запрос в рекурсивную процедуру?
3 ответа
Вы хотите что-то вроде этого:
select t.table_name, level,lpad(' ', 2 * (level - 1))||t.table_name
from user_tables t
join user_constraints c1
on (t.table_name = c1.table_name
and c1.constraint_type in ('U', 'P'))
left join user_constraints c2
on (t.table_name = c2.table_name
and c2.constraint_type='R')
start with t.table_name = 'ROOT_TAB'
connect by prior c1.constraint_name = c2.r_constraint_name
Проблема с исходным запросом заключается в том, что uc.constraint_name для дочерней таблицы является именем внешнего ключа. Это хорошо для подключения первого дочернего элемента к корневой таблице, но это не то, что вам нужно для подключения дочерних элементов второго уровня к первому. Вот почему вам нужно объединиться с ограничениями дважды - один раз, чтобы получить первичный ключ таблицы, один раз, чтобы получить внешние ключи.
Кроме того, если вы собираетесь запрашивать представления all_*, а не представления user_*, вы, как правило, хотите присоединить их к имени table_name AND, а не просто table_name. Если в нескольких схемах есть таблицы с одинаковым именем, объединение только с именем table_name даст неверные результаты.
Для случая с несколькими схемами и несколькими корневыми таблицами попробуйте что-то вроде:
WITH constraining_tables AS (SELECT owner, constraint_name, table_name
FROM all_constraints
WHERE owner LIKE 'ZZZ%' AND constraint_type IN ('U', 'P')),
constrained_tables AS (SELECT owner, constraint_name, table_name, r_owner, r_constraint_name
FROM all_constraints
WHERE owner LIKE 'ZZZ%' AND constraint_type = 'R'),
root_tables AS (SELECT owner, table_name FROM constraining_tables
MINUS
SELECT owner, table_name FROM constrained_tables)
SELECT c1.owner || '.' || c1.table_name, LEVEL, LPAD (' ', 2 * (LEVEL - 1)) || c1.owner || '.' || c1.table_name
FROM constraining_tables c1
LEFT JOIN
constrained_tables c2
ON c1.owner = c2.owner AND c1.table_name = c2.table_name
START WITH c1.owner || '.' || c1.table_name IN (SELECT owner || '.' || table_name FROM root_tables)
CONNECT BY PRIOR c1.constraint_name = c2.r_constraint_name
После глубокого глубокого изучения я сделал свою собственную версию, которая обрабатывает все таблицы и получает максимальный уровень таблицы в иерархии (она считывает все схемы, учитывая также таблицы без родительско-дочерних отношений, которые будут на уровне 1 вместе с корнем из них). Если у вас есть доступ, используйте таблицы dba_ вместо all_.
WITH hier AS (
SELECT child_table owner_table_name
, LEVEL lvl
, LPAD (' ', 4 * (LEVEL - 1)) || child_table indented_child_table
, sys_connect_by_path( child_table, '|' ) tree
FROM (
/*----------------------------------------------------------------------*/
/* Retrieve all tables. Set them as the Child column, and set their */
/* Parent Column to NULL. This is the root list (first iteration) */
/*----------------------------------------------------------------------*/
SELECT NULL parent_table
, a.owner || '.' || a.table_name child_table
FROM all_tables a
UNION
/*----------------------------------------------------------------------*/
/* List of all possible Parent-Child relations. This table is used as */
/* a link list, to link the current iteration with the next one, from */
/* root to last child (last child is what we are interested to find). */
/*----------------------------------------------------------------------*/
SELECT p.owner || '.' || p.table_name parent_table
, c.owner || '.' || c.table_name child_table
FROM all_constraints p, all_constraints c
WHERE p.owner || '.' || p.constraint_name = c.r_owner || '.' || c.r_constraint_name
AND (p.constraint_type = 'P' OR p.constraint_type = 'U')
AND c.constraint_type = 'R'
)
START WITH parent_table IS NULL
/*----------------------------------------------------------------------*/
/* NOCYCLE prevents infinite loops (i.e. self referencing table constr) */
/*----------------------------------------------------------------------*/
CONNECT BY NOCYCLE PRIOR child_table = parent_table
)
SELECT *
FROM hier
WHERE (owner_table_name, lvl) IN ( SELECT owner_table_name
, MAX(lvl)
FROM hier
GROUP BY owner_table_name
);
Изменить: есть "вид" проблема с этим запросом при поиске бесконечных циклов.
Если у нас есть это дерево:
b --> c --> d
b <-- c
он назначит lvl 2 для c как: b --> c и лвл 2 до б как: c --> b
для д, он обнаружит b --> c --> dтак что назначит 3 лвл
Итак, как вы можете видеть, проблема находится внутри цикла, значения извне всегда будут иметь максимальный правильный уровень lvl.