Oracle 原子存储过程

Question

我正在编写 Oracle 中的存储过程，该过程将刷新包含非规范化数据的表。程序概要如下:

CREATE OR REPLACE PROCEDURE loadDenormalizedTable IS
BEGIN

DELETE FROM denormalizedTable;

INSERT INTO
    denormalizedTable
    (
        data
    )
SELECT DISTINCT
    data
FROM
    normalizedTables;

END;
/

我希望所有这些都发生在一个事务中，以便表中始终有数据。现在删除运行并且表是空的几分钟直到插入完成。在没有任何停机时间的情况下处理这种类型的表刷新的最佳方法是什么？

Answer 1

默认情况下，过程将作为 session 拥有的更大事务的一部分执行。如documentation提到:

Note: A transaction can span multiple blocks, and a block can contain multiple transactions.

使用概述的代码，在调用过程后提交之前，没有其他 session 将看到您的删除或插入。例如，如果您只是从 SQL*Plus 提示符执行它:

SQL> exec loadDenormalizedTable;

PL/SQL procedure successfully completed.

SQL>

...然后任何其他查看该表的人仍然会看到旧数据，即使删除和插入都已完成。 (任何其他试图执行该过程，或者在 denormalizedTable 中插入或删除数据的人，都会被阻止，但大概您只是希望其他人查询它)。一旦您发出 commit那么每个人都会看到同样的事情。

获得您描述的行为的唯一方法是在过程中手动结束事务:

CREATE OR REPLACE PROCEDURE loadDenormalizedTable IS
BEGIN

DELETE FROM denormalizedTable;

COMMIT WORK; -- makes the delete visible elsewhere

INSERT INTO
    denormalizedTable
    (
        data
    )
SELECT DISTINCT
    data
FROM
    normalizedTables;    
END;
/

你不需要 commit在程序的中间，你会或应该想要这样做是非常罕见的，因为它打破了原子性。

有可能你正在做一些你没有意识到的隐式提交；也许调用另一个执行自己提交的过程或函数(不这样做的原因之一 - 它可能会产生意想不到的副作用!)，或者可能是一个 DDL 语句 - 它总是会在幕后进行隐式提交，但是你无论如何都必须使用动态 SQL 来做到这一点。

另一种可能性是您实际上并没有做 delete ，但您正在执行 truncate .正如 in the documentation 暗示的那样，这将立即对其他人可见，无需明确提交。 .不过，这也与您提供的大纲有很大不同。