SQL Oracle - SUBQUERY для выборочного извлечения данных из *разного* столбца, в зависимости от условий таблицы

Чтобы сделать его универсальным, вам нужно будет использовать динамический SQL. Самый простой способ — это простая функция, которая возвращает значение для данной таблицы, строки и столбца.

create or replace function current_value(in_table_name in varchar2,
                                         in_row_column in varchar2, 
                                         in_row_id in integer,
                                         in_select_column in varchar2)
  return varchar2
as
  var_result varchar2(4000);
begin
  execute immediate 'select max("'||in_select_column||'") 
                       from "'||in_table_name||'" 
                      where "'||in_row_column||'" = :id' 
               into var_result 
           using in in_row_id;

  return var_result;
end;

Это, конечно, преобразует любой столбец в строку, поэтому, если вам нужна дата или число, либо повторно преобразуйте их при выводе, либо создайте другие функции или даже упакованные перегрузки для обработки других типов данных. Если все столбцы, которые вы отслеживаете, являются числовыми, замените varchar2 на number, в противном случае использование varchar2 безопаснее всего, поскольку к нему можно привести большинство типов данных. Он также явно не будет обрабатывать LOB и ожидает, что идентификатор строки будет числовым. Какая бы сложность вам ни потребовалась, она должна быть встроена, но это дает вам базовую основу.

Кроме того, чтобы иметь хоть какую-то надежду на достойную производительность, вам необходимо убедиться, что столбец, идентифицирующий строку (ID), проиндексирован в каждой таблице. Если это ПК, то так и должно быть. Затем вы просто запрашиваете таблицу аудита и вызываете функцию для каждой строки:

SELECT
    sourceid AS ID,
    id AS EVENT_ID
    key AS COLUMN,
    oldvalue AS PREVIOUS_ENTRY,
    current_value('MYTABLE','ID',id,key) current_value
FROM AUDITTABLE  

Вы можете использовать прием XML для генерации и запуска динамического SQL как части простого запроса без собственной функции PL/SQL.

Если вы добавите вызов

DBMS_XMLGEN.GETXMLTYPE ('select "' || key || '" from table1 where id = ' || sourceid)

на ваш запрос он даст вам текущее значение; но как часть XML-документа. Таким образом, вы можете использовать XMLQuery или XMLTable для его извлечения; это использует этот расширенный запрос как CTE:

WITH cte AS (
    SELECT
        sourceid AS ID,
        id AS EVENT_ID,
        key AS COLUMN_NAME,
        oldvalue AS PREVIOUS_ENTRY,
        DBMS_XMLGEN.GETXMLTYPE (
            'select "' || key || '" from table1 where id = ' || sourceid) AS XML
    FROM
        AUDITTABLE
)
SELECT
    ID,
    EVENT_ID,
    COLUMN_NAME,
    PREVIOUS_ENTRY,
    CURRENT_VALUE
FROM
    cte
CROSS APPLY XMLTABLE (
    '/ROWSET/ROW'
    PASSING cte.XML
    COLUMNS CURRENT_VALUE number PATH '.'
)

с результатом в виде числа, что приемлемо для этих двух значений, но не в целом; вы можете изменить тип данных с числа на vachar2 при вызове XMLTable. А если текущего значения нет, вы можете использовать OUTER APPLY вместо CROSS APPLY.

Или, более непосредственно, с помощью XMLQuery:

SELECT
    sourceid AS ID,
    id AS EVENT_ID,
    key AS COLUMN_NAME,
    oldvalue AS PREVIOUS_ENTRY,
    XMLQUERY ('/ROWSET/ROW/*/text()'
        PASSING DBMS_XMLGEN.GETXMLTYPE (
            'select "' || key || '" from table1 where id = ' || sourceid)
        RETURNING CONTENT) AS CURRENT_VALUE
FROM
    AUDITTABLE

который возвращает значение в виде строки. Вы можете преобразовать это, но если оно должно быть универсальным, строка, вероятно, будет вашим единственным вариантом, поскольку current_value не может иметь более одного типа данных. Это потенциально может вызвать проблемы с датами и т. д. Это, конечно, справедливо и для выражения case, но, по крайней мере, тогда вы сможете применить определенное форматирование для каждого ключа/столбца.

рабочий пример

Функциональный подход PaulW может масштабироваться и работать лучше - не в последнюю очередь потому, что он позволяет предоставлять идентификатор (надеюсь, PK) в качестве переменной привязки, что сокращает объем жесткого анализа (вероятно, значительного).

Однако выражение Case может быть более практичным, чем любой из подходов, после его создания; и вы можете сгенерировать его из словаря данных, если не хотите вводить все это. Предположительно, таблица аудита заполняется из триггера, что может привести к обратной проблеме, поэтому вы сможете увидеть, как она там решается, включая то, как генерируются и поддерживаются триггеры.

Не все столбцы таблицы имеют один и тот же тип данных, тип данных столбцов может быть изменен в течение срока службы приложения и т. д. Поэтому, если вы хотите (предыдущая_запись, текущее_значение), они могут быть только строковым представлением данных. Другим способом хранения этих данных аудита может быть полезная нагрузка JSON или XML, поэтому у вас будет только 1 таблица аудита (audit_pk, Meta_pk, полезная нагрузка) для всей схемы и 1 таблица метаданных аудита (meta_pk, имя таблицы, версия, мета_деск). , первый из которых ссылается на второй, Meta_desc содержит полезную нагрузку JSON или XML, описывающую метаданные таблицы для конкретной версии ее определения, метаданные, которые вы можете генерировать из user_tab_columns каждый раз, когда вы вносите изменения в метаданные таблицы. И у вас будет только 1 строка на модификацию исходной строки вместо 1 строки на каждый измененный столбец, и в конечном итоге можно будет иметь полиморфную табличную функцию, возвращающую изменения (таблицы, версии) в виде набора строк (исходный pk, date_modif , col1_old_value, col1_new_value, ..., colN_old_value, colN_new_value), при этом каждый столбец возвращается в исходном типе данных, PTF выполняет обратное преобразование из строки в исходный тип данных благодаря метаданным... (именно здесь все CASE для разных типов данных будут скрыты...) и по-прежнему иметь возможность возвращать тройки (имя_столбца, старое, новое), используя JSON_TABLE или XMLTABLE в сохраненной полезной нагрузке. Или иметь 1 VIEW для каждой (таблицы, версии) для выполнения работы PTF, поэтому вместо 1 AUDITTABLE для каждой ТАБЛИЦЫ у вас будет 1 AUDITVIEW для каждой ТАБЛИЦЫ... (должна быть возможность генерировать код VIEW на основе user_tab_columns слишком).