Блог

Периодически таблица Oracle, называемая ED, обновляется с помощью хранимой процедуры. Источник данных представляет собой сложную инструкцию INSERT-SELECT, и ED усекается каждый раз, когда происходит обновление. Однако, прежде чем произойдет усечение, желательно знать, не будут ли какие-либо записи, существующие в ED, повторяться при обновлении, т.Е. Выбор ВСТАВКИ не будет повторно вводить определенные записи, которые в настоящее время существуют в ED.

Идея решения этой проблемы состоит в том, чтобы сначала выполнить INSERT_SELECT во временную таблицу с именем ED_TEMP . А затем сравните ED с ED_TEMP и поместите удаленные записи в другую таблицу с именем ED_MISSING .

 INSERT INTO ED_MISSING (ED_ID, ENTRY_DATE)
    SELECT DISTINCT ED_ID, ENTRY_DATE FROM ED
    WHERE ED_ID NOT IN (SELECT ED_ID FROM ED_TEMP); 
 

После этого желательно обрезать ED и повторно заполнить его с помощью ED_TEMP . Можно ли все это сделать в рамках хранимой процедуры? Или было бы лучше использовать несколько хранимых процедур?

 1. Truncate ED_TEMP.
2. Perform complex INSERT-SELECT to populate ED_TEMP.
3. Handle exception if necessary
4. Perform compare and populate ED_MISSING
5. Handle exception if necessary.
6. Truncate ED.
7. Populate ED with ED_TEMP.

Would separate COMMIT and EXCEPTION statements be necessary for each INSERT step? 
 

Можно ли все это сделать в рамках хранимой процедуры?

Конечно; вам просто нужно будет обрезать таблицы, используя execute immediate как truncate есть DDL. Также обратите внимание, что DDL неявно фиксирует, дважды — до и после фактического оператора DDL.

Или было бы лучше использовать несколько хранимых процедур?

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

Будут ли необходимы отдельные операторы ФИКСАЦИИ и ИСКЛЮЧЕНИЯ для каждого шага вставки?

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

Что касается исключений, то вы знаете лучше всех. Если есть, например, 2 select инструкции, у вас может быть один обработчик исключений для no_data_found . Но он будет обрабатывать ОБА выбора; как вы узнаете, какой из них не удался? В этом случае инкапсулируйте каждый из них в свой собственный BEGIN-EXCEPTION-END блок.

С помощью ответа Литтлфута я решил проблему следующим образом.

Создайте хранимую процедуру (ED_MAIN), которая вызывает две другие хранимые процедуры. Первая хранимая процедура (POPULATE_ED_TMP) усекает, а затем повторно заполняет временную таблицу ED_TMP. Затем вторая хранимая процедура выполняет вставку в таблицу ED_MISSING.

 BEGIN
INSERT INTO ED_MISSING(column1,...) --a primary key or unique index
SELECT column1,... FROM
(
SELECT column1,... FROM ED  -- the previous data set in the ED table
MINUS
SELECT column1,... FROM ED_TMP --the current data set
)
MINUS
SELECT column1,... FROM ED_MISSING --don't put duplicates in ED_MISSING
EXCEPTION
    WHEN OTHERS THEN
        ROLLBACK;
        RAISE_APPLICATION_ERROR(-20002, 'Insert into GATED_MISSING error - '||    SQLCODE||' - '||SQLERRM); 
END;
 

Наконец, в ED_MAIN переместите содержимое ED_TMP в ED в prep для следующего сравнения

 EXECUTE IMMEDIATE 'TRUNCATE TABLE ED'
INSERT INTO ED(column1, column2, column3, column4,...)
SELECT column1, column2, column3, column4,... FROM ED_TMP