在从Oracle 迁移到 PostgreSQL时，要考虑的重要事实是它们都是 2 个不同的数据库引擎。组织在迁移过程中犯的主要错误是假设 Oracle 和 PostgreSQL 的行为是相同的。Oracle 与 PostgreSQL 之间存在差异的领域之一是使用事务控制语句。在将 PL/SQL 过程和函数从 Oracle 迁移到 PostgreSQL 中的 PL/pgSQL 时，我们需要详细了解事务控制语句的工作原理。在本文结束时，您将清楚地了解 Oracle 与 PostgreSQL - 事务控制语句。
开始和结束
让我们从一个重要的事实开始，PL/SQL 和 PL/pgSQL 中的 BEGIN 和 END 只是句法元素，与事务无关。大多数时候，我们对用于事务控制的类似名称的 SQL 命令感到困惑。在 PL/SQL 和 PL/pgSQL 中启动块的 BEGIN 与启动事务的 SQL 语句 BEGIN 不同。此处的 BEGIN/END 仅用于分组目的，但它们不会启动或结束事务。函数和触发器过程始终作为外部查询启动的事务的一部分运行。他们无法启动或提交该事务，因为没有他们必须运行的上下文。
以 PostgreSQL 中的以下 PL/pgSQL 代码为例。
CREATE OR REPLACE PROCEDURE test_proc ()
AS $$
DECLARE
BEGIN
RAISE NOTICE 'current transaction id: %', txid_current();
END;
$$
LANGUAGE PLPGSQL;
DO $$
BEGIN
RAISE NOTICE 'current transaction id: %', txid_current();
CALL test_proc ();
RAISE NOTICE 'current transaction id: %', txid_current();
END;
$$;复制
以下是上述匿名块的输出。
NOTICE: current transaction id: 17788
NOTICE: current transaction id: 17788
NOTICE: current transaction id: 17788复制
我们可以从上面的输出中得出结论，BEGIN 和 END 不会开始或结束事务。Oracle 中的 PL/SQL 也是如此。
现在，让我们看看当我们在 PostgreSQL 中使用 COMMIT 时会发生什么
在 PostgreSQL 的 PL/pgSQL 中，每当一个过程中发生 COMMIT 时，当前事务就会结束，并自动启动一个新事务。
考虑以下带有 COMMIT 的 PL/pgSQL 代码。
CREATE OR REPLACE PROCEDURE test_proc ()
AS $$
DECLARE
BEGIN
RAISE NOTICE 'current transaction id: %', txid_current();
COMMIT;
RAISE NOTICE 'current transaction id: %', txid_current();
END;
$$
LANGUAGE PLPGSQL;复制
现在，让我们调用上面创建的过程并查看事务 ID。
CALL test_proc();复制
上述命令的输出如下所示。
NOTICE: current transaction id: 1183970
NOTICE: current transaction id: 1183971复制
上面的输出表明，当发出 COMMIT 时，当前事务结束并创建新事务。
让我们看看当我们在 Oracle 中使用 COMMIT 时会发生什么
在这种情况下，Oracle 中的 PL/SQL 类似于 PostgreSQL 中的 PL/pgSQL，但是，在创建新事务的方式上仍然存在明显的差异。执行 COMMIT 时，只要遇到第一个 SQL 语句（特别是 DML 命令，如 INSERT 、 UPDATE 、 DELETE 、 SELECT ），就会结束当前事务并开始新事务。
以 Oracle 中的以下 PL/SQL 代码为例。
CREATE OR REPLACE PROCEDURE test_proc IS
BEGIN
insert into test_ts values(1);
DBMS_OUTPUT.PUT_LINE('transaction id: ' || DBMS_TRANSACTION.LOCAL_TRANSACTION_ID);
COMMIT;
-- No DML performed after the above commit.
DBMS_OUTPUT.PUT_LINE('transaction id: ' || DBMS_TRANSACTION.LOCAL_TRANSACTION_ID);
insert into test_ts values(1);
DBMS_OUTPUT.PUT_LINE('transaction id: ' || DBMS_TRANSACTION.LOCAL_TRANSACTION_ID);
END;复制
现在，让我们调用上面创建的过程并查看事务 ID。
CALL test_proc();复制
上述命令的输出如下所示。
transaction id: 10.4.6777
transaction id:
transaction id: 10.19.6776复制
我们可以从上面的输出中观察到两件事。第一个观察结果是 COMMIT 结束了事务。第二个观察是新事务仅在遇到第一个 SQL 语句时才开始，而不是立即开始。
Oracle 与 PostgreSQL 中的异常块
现在让我们考虑Oracle与PostgreSQL中的 EXCEPTION 块之间的行为差异。
PostgreSQL 中的异常块
在 PL/pgSQL 中，块中的 EXCEPTION 子句有效地创建了一个子事务，该子事务可以回滚而不影响外部事务。
考虑 PostgreSQL 中的以下 PL/pgSQL 代码。
-- I have created the following procedure
CREATE OR REPLACE PROCEDURE test_proc()
AS $$
DECLARE
BEGIN
insert into test_ts values(1);
RAISE NOTICE 'current transaction id: %', txid_current();
PERFORM 1/0;
EXCEPTION
WHEN OTHERS THEN
RAISE NOTICE 'current transaction id: %', txid_current();
END;
$$ LANGUAGE PLPGSQL;复制
在上面的块中，我正在执行插入并调用上述过程并再次执行另一个插入。这意味着，除了过程（test_proc()）执行之外的一切都会成功。因此，我们只会看到插入到 test_ts 表中的 2 条记录。
上述命令的输出和插入的行数如下所示。
DO
$$
BEGIN
insert into test_ts values(1);
RAISE NOTICE 'current transaction id: %', txid_current();
CALL test_proc();
insert into test_ts values(1);
RAISE NOTICE 'current transaction id: %', txid_current();
END;
$$;复制
在这种情况下，PL/pgSQL 块作为原子子事务执行。当一个异常被捕获时，整个块在异常块被执行之前被回滚。实际上，异常处理程序块是在子事务中运行的，这只不过是在 BEGIN 处创建一个保存点。
当抛出异常时，它会回滚到保存点。由于这个原因，在 PostgreSQL 的过程 test_proc 中进入异常块之前执行的插入被回滚。所以我们只看到在上面的输出中插入了 2 行。
当一个 EXCEPTION 子句捕捉到错误时，PL/pgSQL 函数的局部变量保持与错误之前相同，但对块内持久数据库状态的所有更改都将回滚。如果我们在 EXCEPTION 块中显式指定 ROLLBACK，则整个事务都会回滚。
Oracle中的异常块
让我们看一下Oracle PL/SQL 中异常的行为。
考虑以下类似于上述 PL/pgSQL 代码的 PL/SQL 代码。
CREATE OR REPLACE PROCEDURE test_proc IS
dummy number;
BEGIN
insert into test_ts values(1);
DBMS_OUTPUT.PUT_LINE('transaction id: ' || DBMS_TRANSACTION.LOCAL_TRANSACTION_ID);
dummy := 1/0;
EXCEPTION
WHEN OTHERS THEN
DBMS_OUTPUT.PUT_LINE('transaction id: ' || DBMS_TRANSACTION.LOCAL_TRANSACTION_ID);
END;复制
类似于我们上面考虑的 PostgreSQL 示例，Oracle 的上述过程将进入异常块，因为 1/0 将引发异常。一旦代码进入异常块，它将回滚进入 BEGIN 后执行的所有操作。
我现在已经执行了以下操作。
BEGIN
insert into test_ts values(1);
DBMS_OUTPUT.PUT_LINE('transaction id: ' || DBMS_TRANSACTION.LOCAL_TRANSACTION_ID);
test_proc();
insert into test_ts values(1);
DBMS_OUTPUT.PUT_LINE('transaction id: ' || DBMS_TRANSACTION.LOCAL_TRANSACTION_ID);
END;复制
上述命令的输出和插入的行数如下所示。
-- Output
transaction id: 8.24.3784
transaction id: 8.24.3784
transaction id: 8.24.3784
transaction id: 8.24.3784
-- Rows Inserted
SQL> select * from test_ts;
ID
----------
1
1
1复制
当您看到上述结果时，您应该看到结果为 3 条记录。所以在Oracle中通过procedure : test_proc 执行的插入，在进入异常块之前不会回滚。因此，行的输出是 3，而 PostgreSQL 中类似过程的输出是 2。
在 PL/SQL 中，异常块与事务控制无关，该块在现有事务中运行。当发生异常时，这只是关于分支到另一个代码路径。与 PL/pgSQL 不同，当 EXCEPTION 子句捕获错误时，块不会回滚。由于这个原因，在异常之前执行的插入在事务中仍然可见，并且可以提交或回滚。
函数中的提交和回滚 - Oracle vs PostgreSQL
对于 Oracle 中的 PL/SQL 中的函数，COMMIT 和 ROLLBACK 的行为方式与过程相同。但是对于 PostgreSQL 中的 PL/pgSQL 函数，您不能使用 SAVEPOINT、COMMIT 或 ROLLBACK。但是，我们仍然可以使用 PostgreSQL 函数中的 EXCEPTION 块来处理异常。PL/SQL 和 PL/pgSQL 函数中的 EXCEPTION 块的行为类似于过程。在 PL/pgSQL 函数中，当 EXCEPTION 子句捕获错误时，块被回滚，而 PL/SQL 函数 EXCEPTION 块仅用于代码分支并且块不回滚。
在 PL/SQL 和 PL/pgSQL 中，如果引发错误并且未在其中处理，则调用过程或函数的事务将中止。中止的事务无法提交，如果它们尝试提交，则 COMMIT 被视为 ROLLBACK。
结论
我们在本文中讨论了 BEGIN 和 END 只是 Oracle 中的 PL/SQL 和 PostgreSQL 中的 PL/pgSQL 中的句法元素。它们与交易无关。新事务在 PL/pgSQL 中在 COMMIT 之后自动启动，而在 PL/SQL 中，新事务仅在遇到第一个 SQL 语句时才开始。PL/pgSQL 中的 EXCEPTION 块在捕获异常时回滚块直到最后一个保存点，这与 PL/SQL 中的 EXCEPTION 块仅用于代码分支不同。最后，我们还了解到 PL/pgSQL 函数不能有 SAVEPOINT、COMMIT 或 ROLLBACK。
在开始迁移数据库之前，了解 Oracle 和 PostgreSQL 之间的区别很重要。我们之前的文章中已经发布了一些此类差异：处理尾随零、存储过程中的 OUT 参数、移植数组长度等。有关 Oracle 和 PostgreSQL 之间的更多此类差异，请订阅我们的新闻通讯。
关于作者 Akhil Reddy Banappagari
Akhil Reddy 目前在 MigOps 担任开发人员。他是一位熟练的程序员，擅长用 Java 和 Python 开发应用程序。在加入 MigOps 之前，Akhil 致力于开发 Android 和 Web 应用程序以及其他一些开发项目。怀着对设计高效算法和强大工具的热情，他加入了 MigOps 并开始开发工具来简化数据库迁移之旅。此外，Akhil 在 PL/SQL 和迁移方面的专业知识已帮助多个客户从 Oracle 迁移到 PostgreSQL。
原文标题：Oracle vs PostgreSQL – Transaction control statements
原文作者：Akhil Reddy Banappagari
原文地址：https://www.migops.com/blog/oracle-vs-postgresql-transaction-control-statements/
