如何在目标中标识附加到目标Oracle表(随后已从其数据源中删除)的数据记录？

Question

定期通过存储过程更新名为ED的Oracle表。数据源是一个复杂的INSERT-SELECT语句，每次更新发生时都会截断ED。但是，在进行截断之前，希望知道在更新时是否会重新出现ED中存在的任何记录，即INSERT-SELECT不会重新引入某些当前存在于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? 

Answer 1

在Littlefoot回答的帮助下，我以以下方式解决了这个问题。

创建一个调用另外两个存储过程的存储过程(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中，为下一次比较做准备。

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

Answer 2

所有这些都能在存储过程中完成吗？

当然，您只需使用execute immediate截断表，因为truncate是DDL。另外，请注意DDL隐式提交了两次-在实际DDL语句之前和之后。

还是多个存储过程更好？

无法判断；如果整个进程完成了一个事务，则将其保持在相同的过程中。或者，如果它真的很复杂，如果它被分割成较小的部分，那么维护它就更容易了。有些人说，理想的过程尺寸是一个屏幕高度(这样你就可以同时看到一切)。不过，我不认为这总是可能的。

对于每个插入步骤，

是否需要单独的提交语句和异常语句？

这取决于:提交结束了一个事务，因此--如果事后出了问题，就没有回滚。因此，如果您所做的一切都被认为是相同的事务，那么提交一次--在结束时。但是，如果您使用大量数据进行操作，回滚段可能无法全部吞并，因此您可能需要在此过程中提交。

作为例外，你知道最好的。如果有两个select语句，您可以为no_data_found设置一个异常处理程序。但是，它将处理两个选择；您如何知道哪一个失败了？在这种情况下，将它们封装到自己的BEGIN-EXCEPTION-END块中。