Oracle 与 PostgreSQL 中的 IN、OUT 和 INOUT 参数

作者：阿基尔·雷迪·巴纳帕加里（Akhil Reddy Banappagari）发表于2024 年 3 月 8 日

Akhil 在 HexaCluster 担任高级开发经理。他是一位技术娴熟的开发人员，拥有 PostgreSQL 和 Oracle 方面的专业知识。Akhil 专门从事 PostgreSQL 开发以及从 Oracle 和 SQL Server 到 PostgreSQL 的数据库迁移，他成功管理了许多复杂的数据库迁移项目。Akhil 是为 PostgreSQL 编写扩展的专家。除了作为数据库开发人员的角色外，Akhil 还拥有使用 Python、C 和 Java 创建应用程序的广泛专业知识。

原文链接：

IN, OUT and INOUT parameters in Oracle vs PostgreSQL

将 Oracle 例程迁移到 PostgreSQL 时，处理 OUT 和 INOUT 参数可能很棘手。了解 Oracle 和 PostgreSQL 在管理这些参数方面的区别对于成功迁移至关重要。这些知识可以帮助您顺利地调整您的例程，确保您的代码正常运行而不会出现任何问题。在本文中，我们将探讨 Oracle 和 PostgreSQL 中的 IN、OUT 和 INOUT 参数，并了解一些重要的区别。

数据库中按值传递与按引用传递

我们已经熟悉术语“按值传递”和“按引用传递”。传递值的副本称为“按值传递”。术语“按引用传递”是指传递变量的实际内存引用。

Oracle 和 PostgreSQL 用于传递和处理例程参数的方法有所不同。我们必须理解 Oracle 和 PostgreSQL 的内部原理和区别，才能有效地编写或迁移代码。

Oracle 中的一个简单示例

让我们首先创建以下简单的过程。

CREATE OR REPLACE PROCEDURE test_pr(p_a OUT number) IS
BEGIN
p_a := 3;
END;
Output:
 
Procedure created.

现在我们将从匿名块中调用上述过程。

DECLARE
v_a number := 5;
BEGIN
   test_pr(v_a);
   DBMS_OUTPUT.PUT_LINE('The value of v_a is: ' || v_a);
END;
Output:
The value of v_a is: 3

在此示例中，我们在匿名块中声明了一个变量“v_a”，并将其作为 OUT 参数传递给 test_pr 过程，该过程将“p_a”的值赋值为 3。因此，当我们显示“v_a”的值时' 调用 test_pr 过程后，结果是 3。现在让我们看看它的内部原理。

Oracle 传递 IN、OUT、IN OUT 参数的内部原理

默认情况下，Oracle按值传递OUT和IN OUT参数，按引用传递 IN 参数。是的，你没有看错！当 OUT 或 IN OUT 参数在过程中更改时，它仅更改参数值的副本。仅当过程无错误地完成后，结果值才会复制回形式参数。

如果将集合作为 OUT 或 IN OUT 参数发送，它将按值传递。这意味着在进入过程时，完整的集合将从形式参数复制到实际参数，并在退出过程时复制回形式参数。如果集合很大，这可能会消耗大量的 CPU 和内存。NOCOPY 参数模式提示通过指示运行时引擎尝试通过引用而不是通过值传递 OUT 或 IN OUT 参数来解决此问题。

PostgreSQL处理IN、OUT、INOUT参数的方式

PostgreSQL 不支持通过引用传递参数，它仅支持按值传递。使用 OUT 或 INOUT 参数时不会传递任何引用，但返回值取自结果组合。

当从普通 SQL 调用带有 OUT 参数的 PostgreSQL 过程时会发生什么？

在普通 SQL 中，如果过程有输出参数，则当使用 CALL 语句调用时，它会将输出参数变量的最终值返回给调用者。

以下面的过程为例。

CREATE OR REPLACE PROCEDURE add_numbers(
    IN a INT,
    IN b INT,
    OUT result INT
)
LANGUAGE plpgsql
AS $$
 
BEGIN
result := a + b;
END;
$$;

现在，如果我们用普通 SQL 调用此过程，如下所示

postgres=# CALL add_numbers(3, 5, 0);

输出：
result
------
8

这里我只是为 OUT 参数传递零。我们观察到 OUT 参数的值直接作为组合返回给调用者。

当从函数、过程或 DO 块调用带有 OUT 参数的 PostgreSQL 过程时会发生什么？

当 PL/pgSQL 函数、过程或 DO 块使用CALL执行过程时，输出参数的处理方式与在普通 SQL 中使用 CALL 时不同。它的每个过程的 OUT 或 INOUT 参数都必须在 CALL 语句中提供一个变量，并且过程返回的任何内容都将分配回该变量。这是由 CALL 语句隐式完成的。要了解更多信息，请参阅此处。

相关链接：

https://www.postgresql.org/docs/current/plpgsql-control-structures.html#PLPGSQL-STATEMENTS-CALLING-PROCEDURE

考虑下面的 DO 块，它调用上面的 add_numbers 过程。

DO $$
DECLARE
result_value INT := 0;
BEGIN
    CALL add_numbers(3, 5, result_value);
RAISE NOTICE 'The result is: %', result_value;
END $$;

输出：

NOTICE: The result is: 8
DO

我们观察到打印的结果是 8，因为这里的 CALL 正在处理这个结果。

PL/pgSQL 中的 SELECT 和 PERFORM

PL/pgSQL 中的 SELECT 和 PERFORM 语句在用于执行带有 OUT 或 INOUT 参数的函数时，在返回结果后不处理输出参数。因此，与 PL/pgSQL CALL 命令不同，结果不会分配回我们在函数调用中使用的变量。为了将结果分配给变量，我们使用 SELECT.. INTO 语句。

SELECT 命令用于调用 PL/pgSQL 和普通 SQL 函数。但是，PL/pgSQL 中不允许没有 INTO 子句的 SELECT 查询。相反，PERFORM 用于调用 void 函数，该函数没有任何输出参数。

PostgreSQL 函数是否同时支持 OUT/INOUT 参数和 RETURN 值？

当我们有一个或多个 OUT/INOUT 参数，并且同时从 PostgreSQL 函数返回某些内容时，就会出现这种奇怪的情况。

考虑以下 Oracle 函数。

CREATE FUNCTION test_ro(x number, y OUT number)
RETURN boolean IS
BEGIN
    y := x;
RETURN true; 
END;

及其等效的 PostgreSQL 函数

CREATE OR REPLACE FUNCTION test_ro(x bigint, y OUT bigint)
RETURNS boolean AS $$
BEGIN
  y := x;
  RETURN true;
END; $$ LANGUAGE plpgsql

然而，这个函数不能在 PostgreSQL 中创建。弹出一个错误说

ERROR: function result type must be bigint because of OUT parameters
SQL state: 42P13

上面给出的 PostgreSQL 函数具有 OUT/INOUT 参数并返回一个布尔值。这些类型的函数在 PostgreSQL 中无效。在 PostgreSQL 中，OUT/INOUT 参数的值（并且只有这些值）指定具有 OUT/INOUT 参数的函数的结果。

尽管 PostgreSQL 和 Oracle 具有相似的 OUT/INOUT 参数语法，但它们的实现却存在显着差异。在 PostgreSQL 中，所有参数仅按值传递，因为 PostgreSQL 不支持按引用传递参数。

当 PostgreSQL 函数具有 OUT/INOUT 参数时，没有空间通过 RETURN 子句返回任何进一步的数据。因为在这种情况下，结果组合完全是根据 OUT/INOUT 参数构建的。这就是这里不可能返回值的原因。

如果有 OUT 或 INOUT 参数，则 RETURNS 子句是可选的。它必须与 OUT/INOUT 参数指定的结果类型匹配（如果存在）。如果有多个 OUT/INOUT 参数，则应使用 RECORD。如果只有一个 OUT/INOUT 参数，则返回类型必须与该单个 OUT/INOUT 参数匹配。

因此，上面的 PostgreSQL 函数是不正确的，因为 test_ro 函数的实际输出是一个标量 bigint 值，与指定的布尔值相反。RETURN true 也是不正确的，因为结果仅基于 OUT/INOUT 参数，并且RETURN应该为空。

那么对于此类 Oracle 功能来说，正确的迁移策略是什么？

在这种情况下，我们有一个 return 语句返回变量和 OUT/INOUT 参数，我们需要将返回变量作为 OUT 参数。这使得函数只有 RETURN 语句不返回任何内容。此外，函数的返回类型必须更改为 RECORD，而不是之前返回的数据类型（布尔值），因为我们现在返回两个 OUT 参数。

因此，函数 test_ro 从 Oracle 到 Postgres 的等效翻译如下。

CREATE OR REPLACE FUNCTION test_ro(x bigint, y OUT bigint, OUT result boolean) RETURNS record AS $$
BEGIN
  y := x;
  result := true;
  RETURN;
END; $$ LANGUAGE plpgsql

当函数返回类型为 RECORD 时，预计在 FROM 子句中调用该函数。函数调用中与 OUT 参数对应的变量可以省略。对上述函数的调用示例如下

DO $$
DECLARE
  a bigint := 5;
  b boolean := false;
BEGIN
  SELECT * INTO a, b FROM test_ro(10);
  RAISE NOTICE 'a: %, b: %', a, b;
END;

在Postgres中，当函数有OUT/INOUT变量时，RETURN语句仅用于结束执行，并不指定返回值。返回值由分配给 OUT/INOUT 参数的值确定。

结论

1. Oracle 按引用传递 IN 参数，按值传递 OUT 和 IN OUT 参数。PostgreSQL 不支持引用传递；相反，所有参数都通过值传递来传递。

2.在 PostgreSQL 中，CALL 语句在 PL/pgSQL 和普通 SQL 中的行为不同。在 PL/pgSQL 中，CALL 语句自动将通过 OUT/INOUT 参数从过程返回的值分配给 CALL 语句中指定的变量。对于函数，我们使用 SELECT.. INTO 手动将 OUT/INOUT 参数结果分配给变量。

3.由于 PostgreSQL 仅支持按值传递，并且函数只能输出一个实体，因此不支持同时具有 OUT/INOUT 参数和返回变量的函数。在这种情况下，函数的单个返回实体仅由 OUT/INOUT 参数值指定。仅 OUT/INOUT 参数的值用于提供函数的单个返回实体。这里的 RETURNS 子句是可选的，RETURN 语句只是用于终止执行，它不指定返回值。

4.如果我们想将带有返回变量和 OUT/INOUT 参数的 Oracle 函数转换为 PostgreSQL，那么我们需要将返回变量也设置为 PostgreSQL 中的 OUT 参数，并将返回数据类型更改为 RECORD 或完全删除 RETURNS 子句。希望 Ora2Pg 可以自动为您完成这项工作。