迁移 Oracle 到 PostgreSQL: PL/SQL 对比 PL/pgSQL

十一月 26, 2024

摘要:在本文中,您将了解过程语言的相关特性,以及 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 的架构和功能。这种方法将确保无缝过渡,同时最大限度地减少对现有逻辑的干扰。

了解更多

Oracle 到 PostgreSQL 迁移指南