存储过程 PL/SQL 和 PL/pgSQL，两者差异知多少

介绍

结构化查询语言（SQL）是管理和操作关系数据库的标准语言。它是与数据库交互的核心机制，让用户可以执行查询数据、更新记录和管理数据库结构等任务。SQL 的声明性特点使其成为检索和修改数据的理想选择，但要直接在数据库中实现复杂的业务逻辑时，它还存在局限性。

为了解决这些限制，Oracle 和 PostgreSQL 等数据库系统提供了对 SQL 的过程扩展。Oracle 的 PL/SQL 和 PostgreSQL 的 PL/pgSQL 允许开发人员在数据库中实现更高级的逻辑，包括循环、条件、错误处理和事务控制。这些过程语言增强了 SQL 的能力，使其能够编写出可以在更接近数据的位置执行的复杂例程，从而提高性能和可维护性。

作为过渡到 PostgreSQL 的 Oracle DBA，了解 PL/SQL 和 PL/pgSQL 之间的区别非常重要。本文探讨了这两种语言之间的细微差别，包括语法、功能和实用的迁移技巧，确保您可以在 PostgreSQL 环境中有效地利用 PL/pgSQL。

PL/SQL 和 PL/pgSQL 概述

Oracle 中的 PL/SQL

PL/SQL 是一种健壮的语言，用于直接在 Oracle 数据库中实现业务逻辑。它旨在通过添加过程式结构来增强 SQL 的能力，以实现错误处理、事务管理和条件逻辑。

以下是 PL/SQL 的突出之处：

• 高级的错误处理：借助 NO_DATA_FOUND 和 TOO_MANY_ROWS 等内置异常，以及定义自定义异常的功能，PL/SQL 提供了对错误管理的精细控制。

• 与 Oracle 工具集成：PL/SQL 与 SQL*Plus、Oracle Forms 和 APEX 等 Oracle 工具深度集成，从而可以轻松管理工作流。

PostgreSQL 中的 PL/pgSQL

PL/pgSQL 是 PostgreSQL 对过程式逻辑的支持。虽然它与 PL/SQL 的目标相似，但它的语法更精简，并与 PostgreSQL 的开源架构紧密结合。

PL/pgSQL 的主要特点：

• 简化的语法：PL/pgSQL 简单的语法使其易于采用，特别是对于来自其他编程语言的开发人员。

• 扩展友好：可与 PostgreSQL 扩展无缝协作，如 PostGIS 和 pg_stat_statements，允许进行高级分析、地理空间查询和性能监控。

语法和结构差异

变量声明

变量声明在结构上是相似的，但 PL/SQL 和 PL/pgSQL 之间的语法有所不同。

PL/SQL 示例：

DECLARE
    v_emp_id NUMBER := 1001;
BEGIN
    SELECT emp_name INTO v_emp_name FROM employees WHERE emp_id = v_emp_id;
END;

PL/pgSQL 示例：

DO $$
DECLARE
    v_emp_id INT := 1001;
BEGIN
    SELECT emp_name INTO v_emp_name FROM employees WHERE emp_id = v_emp_id;
END $$ LANGUAGE plpgsql;

有哪些差异点

• 在 PL/pgSQL 中，变量在函数或代码块中需要显式声明类型，以遵循 PostgreSQL 更严格的类型系统。

• Oracle 的 NUMBER 类型通常会映射到 PostgreSQL 的 NUMERIC 或 INTEGER，具体取决于精度要求。

控制结构

两种语言都支持循环和条件等控制结构。但是，使用 PL/pgSQL 的语法更简单、更像 SQL。

字符串操作：NVL 对比 COALESCE 和 DECODE 对比 CASE

PL/SQL 和 PL/pgSQL 中的字符串操作，需要适配到新的函数。有两个关键的示例，NVL 和 DECODE，它们在 PostgreSQL 中差异很大。

使用 NVL 对比 COALESCE

Oracle 中的 NVL 用于将 null 替换为指定值。在 PostgreSQL 中，这需要使用 COALESCE 来完成，它可以处理多个参数。

使用 NVL 的 PL/SQL 示例：

SELECT NVL(employee_name, 'Unknown') AS emp_name
FROM employees;

使用 COALESCE 的 PL/pgSQL 示例：

SELECT COALESCE(employee_name, 'Unknown') AS emp_name
FROM employees;

为什么重要

COALESCE is more flexible because it can handle more than two arguments, returning the first non-null value. This feature allows for more comprehensive null-handling in PostgreSQL.

使用 DECODE 对比 CASE

DECODE 是 Oracle 的条件函数，通常作为简单 IF-THEN-ELSE 逻辑的快捷用法。在 PostgreSQL 中，可用 CASE 作为替代方案，它更加具有通用性。

使用 DECODE 的 PL/SQL 示例：

SELECT DECODE(department_id,
              10, 'Sales',
              20, 'HR',
              30, 'IT',
              'Other') AS department_name
FROM departments;

使用 CASE 的 PL/pgSQL 示例：

SELECT CASE department_id
         WHEN 10 THEN 'Sales'
         WHEN 20 THEN 'HR'
         WHEN 30 THEN 'IT'
         ELSE 'Other'
       END AS department_name
FROM departments;

关键要点：CASE 比 DECODE 更强大，支持复杂的表达式和嵌套条件。它在 PostgreSQL 中更适合用于高级逻辑的处理。

事务管理：COMMIT、ROLLBACK 和 SAVEPOINT

事务管理是数据库编程的一个关键方面，允许受控地执行数据操作。Oracle 中的 PL/SQL 和 PostgreSQL 中的 PL/pgSQL 都提供了事务管理功能，但在语法和行为上存在一些差异。

PL/SQL 事务管理

在 Oracle 的 PL/SQL 中，通常使用 COMMIT、ROLLBACK 和 SAVEPOINT 语句来管理事务。事务是隐式的，这意味着每个代码块执行都可以是一个事务中的一部分。

PL/SQL 中事务管理的示例：

BEGIN
    -- Start of transaction
    UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;

    -- Set a savepoint
    SAVEPOINT update_salary;

    -- Another update
    UPDATE employees SET salary = salary * 1.05 WHERE department_id = 20;

    -- Rollback to savepoint if necessary
    ROLLBACK TO update_salary;

    -- Commit the transaction
    COMMIT;
END;

在此示例中：

• SAVEPOINT 用于标记事务中可以回滚到的点。

• ROLLBACK TO 允许您撤消从保存点以来的更改，而不会影响到更早的操作。

• COMMIT 完成事务中所做的所有更改。

PL/pgSQL 事务管理

在 PostgreSQL 中，PL/pgSQL 函数和过程中的事务管理操作略有不同。虽然您可以在独立的 PL/pgSQL 代码块中使用 COMMIT 和 ROLLBACK，但它们不能直接在函数中使用，因为函数必须在单个事务的上下文中运行。但是，在支持事务控制的存储过程中，可以进行事务管理。

PL/pgSQL 中事务管理的示例：

BEGIN;

-- Start of transaction
UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;

-- Set a savepoint
SAVEPOINT update_salary;

-- Another update
UPDATE employees SET salary = salary * 1.05 WHERE department_id = 20;

-- Rollback to savepoint if needed
ROLLBACK TO update_salary;

-- Commit the transaction
COMMIT;

对于存储过程：

CREATE PROCEDURE adjust_salaries()
LANGUAGE plpgsql
AS $$
BEGIN
    -- Start of transaction
    UPDATE employees SET salary = salary * 1.1 WHERE department_id = 10;

    -- Set a savepoint
    SAVEPOINT update_salary;

    -- Another update
    UPDATE employees SET salary = salary * 1.05 WHERE department_id = 20;

    -- Rollback to savepoint if needed
    ROLLBACK TO update_salary;

    -- Commit the transaction
    COMMIT;
END;
$$;

在此示例中：

• 显式事务可以直接在存储过程中进行管理。

• 保存点可以根据需要设置和回滚，类似于 PL/SQL。

• 虽然函数在单个事务中运行，但 PostgreSQL 中的存储过程允许使用 COMMIT 和 ROLLBACK 等事务控制命令。

主要区别

• 函数与存储过程：在 PostgreSQL 中，事务管理命令只能在存储过程中使用，而不能在函数中使用，而 PL/SQL 允许在这两种上下文中使用这些命令。但是请注意，PostgreSQL 中的函数会自动在单个事务上下文中运行。

• 隐式事务：Oracle 的 PL/SQL 在代码块中隐式处理事务，而 PostgreSQL 的 PL/pgSQL 在使用存储过程时需要显式管理事务。

• SAVEPOINT 行为：保存点的行为在两个系统中基本相似，提供了一种在不影响整个代码块的情况下部分撤消事务的方法。

PL/pgSQL 中事务管理的最佳实践

• 使用存储过程进行复杂事务控制：如果您需要通过提交或回滚来管理事务，请考虑在 PostgreSQL 中将函数重构为存储过程。

• 最小化事务持续时间：保持事务简短，以避免锁定问题并提高性能。

• 谨慎处理异常：使用 EXCEPTION 块捕获和处理错误，确保事务在需要时正确回滚。

高级的错误处理和事务

PL/SQL 允许使用内置异常和自定义异常进行更复杂的异常处理。PL/pgSQL 提供了类似的功能，但需要用不同的方法。

PL/SQL 示例：

EXCEPTION
    WHEN NO_DATA_FOUND THEN
        DBMS_OUTPUT.PUT_LINE('No record found.');

PL/pgSQL 示例：

EXCEPTION
    WHEN OTHERS THEN
        RAISE EXCEPTION 'Error occurred.';

DBA 注意事项：在 PostgreSQL 中，使用 RAISE 处理错误，并使用 DEBUG、NOTICE、INFO 和 WARNING 等消息级别，来自定义错误消息。

迁移建议：PL/pgSQL 的最佳实践

深思熟虑地重构代码

不要尝试直接 1 对 1 的迁移。请调整您的代码逻辑，以适配 PostgreSQL 的架构，这可能会涉及将大型的包对象分解为较小的函数。

了解类型映射

数据类型的差异可能会影响到性能和功能。请确保将 Oracle 的数据类型（如 VARCHAR2 和 NUMBER）正确映射到 PostgreSQL 上的等效数据类型（如 VARCHAR 和 NUMERIC）。

利用 PostgreSQL 的优势

利用好特定于 PostgreSQL 的功能，如原生 JSON 支持、外部数据包装器，以及 pgcrypto 等扩展，来实现加密和安全性。

要避免的常见陷阱

• 预设直接的匹配项：并非所有 PL/SQL 函数在 PL/pgSQL 中都有直接的匹配项。请调整你的方法，并准备好重写某些逻辑。

• 过度依赖 Oracle 包：PostgreSQL 不支持包；可用使用模式来组织相关函数（尽管模式并不支持与 Oracle 包相同的封装和模块化）。

• 数据类型的混淆：请注意数据类型，尤其是数字、时间戳和字符串。

PL/pgSQL 的应用场景

实时分析

借助函数和触发器，PL/pgSQL 非常适合直接在数据库中构建实时分析的解决方案。

ETL 和数据转换

PL/pgSQL 可以管理复杂的 ETL 任务，使其成为数据聚合、清理和转换的理想选择，这对于报告生成和数据分析非常重要。

基于触发器的工作流

PL/pgSQL 的触发器机制允许轻松实现复杂的工作流程，例如审计和日志记录。

结论

PL/SQL 和 PL/pgSQL 有明显的区别，但都有基本的过程概念。当您从 Oracle 迁移到 PostgreSQL 时，请专注适配到 PostgreSQL 的架构和功能。这种方法将确保无缝过渡，同时最大限度地减少对现有逻辑的干扰。