Передача имени таблицы каждый раз динамически
В приведенном ниже запросе j.table_name является родительской таблицей и содержит некоторую зависимую таблицу.
1 STORE_ORDER_JSON_DATA
2 ECOMM_SHIPDOCS_MQ_DATA
Я передаю имя родительской таблицы динамически, а имя зависимой таблицы задается напрямую.
Но я хочу добавить имена зависимых таблиц и их столбцы, которые используется в условии, где (js.data_Load_id,js.status,js.date_loaded также необходимо передавать динамически), поскольку зависимые таблицы будут каждый раз изменяться на основе родительской таблицы. Как мы можем этого добиться? Теперь я указал зависимые таблицы и их столбцы непосредственно в пункте выхода.
V_SQL := 'DELETE FROM ' || j.table_name || ' ord ' ||---------------main 1
' WHERE ' || j.purge_condition_1 || ' = ''' || j.purge_indicator_1 || '''' ||
' AND trunc(' || j.purge_date || ') <= trunc(sysdate) - ' || j.threshold_days ||
' AND EXISTS (SELECT 1 FROM STORE_ORDER_JSON_DATA js ' ||--
' WHERE js.data_Load_id = ord.data_load_id ' ||
' AND js.status = ''P'' ' ||
' AND trunc(js.date_loaded) <= trunc(sysdate) - 10) ' ||
' AND EXISTS (SELECT 1 FROM ECOMM_SHIPDOCS_MQ_DATA mq ' ||--
' WHERE mq.data_Load_id = ord.data_load_id ' ||
' AND mq.status = ''P'' ' ||
' AND trunc(mq.date_loaded) <= trunc(sysdate) - 10)';
Ответы 2
... потому что зависимые таблицы будут каждый раз меняться на основе родительской таблицы.
Ненавижу это говорить, но динамический SQL — это зло. Вам это действительно нужно? Почему? Не могли бы вы достичь цели, создав, например,
- представление (как ОБЪЕДИНЕНИЕ ВСЕХ нескольких запросов, каждый из которых использует свою собственную комбинацию таблиц) или
- функция, которая возвращает рефкурсор
или что-то еще?
Если нет, то рассмотрите возможность создания дополнительной таблицы, которая будет содержать пары таблиц «основная-подробная» и столбцов, которые необходимо объединить.
id master detail mcol1 dcol1 mcol2 dcol2
-- ------ ------ ------ ------ -------- ----------
1 dept emp deptno deptno
2 emp bonus empno empno hiredate bonus_date
а затем составить динамический SQL, запросив эту таблицу на основе идентификатора, чтобы вы знали, какие столбцы необходимо соединить. При необходимости расширите его другими парами mcol (основной столбец) и dcol (столбец сведений). Или, если вы считаете, что это лучший вариант, нормализуйте его так, чтобы предыдущая таблица содержала только идентификатор, основную и подробную таблицу, а ее подробная таблица содержала пары столбцов:
id mcol dcol
-- -------- ----------
1 deptno deptno
2 empno empno
2 hiredate bonus_date
У кого-то другого могут быть другие, другие, лучшие идеи, так что ждите его мнения.
Поскольку каждая родительская таблица будет иметь разные зависимые таблицы и потенциально (или даже предположительно) разные вещи, которые нужно проверять каждый раз, попытка создать динамический оператор, который всегда будет работать, будет болезненной и трудной в обслуживании.
Если у вас нет большого количества возможных родительских таблиц (а, возможно, даже если они у вас есть), я бы рассмотрел возможность полного избавления от динамического оператора и создания отдельного статического оператора для каждой таблицы, что-то вроде:
if j.table_name = 'PARENT_TABLE_1' -- with real name, of course, and maybe case insensitive
and j.purge_condition_1 = 'PARENT_COL_1' -- again, real column name
then
DELETE FROM parent_table_1 ord
WHERE parent_column_1 = j.purge_indicator_1
AND trunc(j.purge_date) <= trunc(sysdate) - j.threshold_days
AND EXISTS (
SELECT 1 FROM STORE_ORDER_JSON_DATA js
WHERE js.data_Load_id = ord.data_load_id
AND js.status = 'P'
AND trunc(js.date_loaded) <= trunc(sysdate) - 10)
AND EXISTS (
SELECT 1 FROM ECOMM_SHIPDOCS_MQ_DATA mq
WHERE mq.data_Load_id = ord.data_load_id
AND mq.status = 'P'
AND trunc(mq.date_loaded) <= trunc(sysdate) - 10);
else if j.table_name = 'PARENT_TABLE_1'
and j.purge_condition_1 = 'PARENT_COL_2'
then
DELETE FROM parent_table_1 ord
WHERE parent_column_2 = j.purge_indicator_1
...
-- etc.
Возможно, это будет много статического кода, но поскольку он статический, он будет проверяться во время компиляции, поэтому вы избежите ошибок во время выполнения в динамическом операторе, внедрения SQL (которым вы сейчас рискуете из-за внедрения значений) и проблем NLS ( которые у вас есть в результате встраивания дат, преобразованных в строки).
Легче поддерживать в некоторых отношениях, хотя, по общему признанию, сложнее в других, поскольку новая родительская таблица требует изменения кода для ее обработки, а не настройки, как это позволяет подход Литтлфута, - если все структуры достаточно похожи, чтобы это работало.