迁移 Oracle 到 PostgreSQL: 匿名代码块

七月 12, 2023

摘要:Oracle PL/SQL 是 SQL 的过程式扩展。PL/SQL 的程序结构将代码划分为由以下关键字区分的块:DECLAREBEGINEXCEPTIONEND。未命名的 PL/SQL 代码块(未作为过程、函数或包存储在数据库中的代码)称为匿名块。

Oracle 用法

匿名块作为 Oracle PL/SQL 的基本单元,包含以下代码部分:

  • 声明性部分(可选)— 包含变量(名称、数据类型和初始值)。
  • 可执行部分(必填) — 包含可执行语句(每个块结构必须至少包含一个可执行的 PL/SQL 语句)。
  • 异常处理部分(可选)— 包含用于处理代码中的异常或错误的元素。

例子

Oracle 匿名块的简单结构。

SET SERVEROUTPUT ON;
BEGIN
  DBMS_OUTPUT.PUT_LINE('hello world');
END;
/
hello world
PL/SQL procedure successfully completed.

Oracle PL/SQL 匿名块可以包含高级代码元素,例如函数、游标、动态 SQL 和条件逻辑。以下匿名块使用了游标、条件逻辑和异常处理。

SET SERVEROUTPUT ON;
DECLARE
  v_sal_chk        NUMBER;
  v_emp_work_years NUMBER;
  v_sql_cmd        VARCHAR2(2000);
BEGIN
  FOR v IN (SELECT EMPLOYEE_ID, FIRST_NAME || ' ' || LAST_NAME AS
    EMP_NAME, HIRE_DATE, SALARY FROM EMPLOYEES)
  LOOP
    v_emp_work_years := EXTRACT(YEAR FROM SYSDATE) - EXTRACT (YEAR FROM v.hire_date);
    IF v_emp_work_years >= 10 and v.salary <= 6000 then
      DBMS_OUTPUT.PUT_LINE('Consider a Bonus for: ' || v.emp_name);
    END IF;
  END LOOP;
EXCEPTION WHEN OTHERS THEN
  DBMS_OUTPUT.PUT_LINE('CODE ERR: ' || sqlerrm);
END;
/

前面的示例根据表EMPLOYEES的列HIRE_DATE计算每个雇员的工作年数。如果员工工作了十年或更长时间,工资为 6000 美元或更低,系统会打印消息“考虑奖金:<员工姓名>”。

有关详细信息,请参阅 Oracle 文档中的 PL/SQL 概述

PostgreSQL 用法

PostgreSQL 支持类似于 Oracle 匿名块的功能。在 PostgreSQL 中,您可以使用 PL/pgSQL 的DO语句将未存储在数据库中的 PL/pgSQL 代码作为独立代码段运行。

PL/pgSQL 是 ANSI SQL 的 PostgreSQL 扩展,具有许多与 Oracle PL/SQL 相似的元素。PostgreSQL 的DO语法使用与 Oracle 匿名块类似的代码结构:

  • 声明性部分(可选)。
  • 可执行文件部分(必需)。
  • 异常处理部分(可选)。

例子

PostgreSQL DO 简单的结构。

SET CLIENT_MIN_MESSAGES = 'debug';
-- Equivalent To Oracle SET SERVEROUTPUT ON

DO $$
BEGIN
  RAISE DEBUG USING MESSAGE := 'hello world';
END $$;
DEBUG: hello world
DO

PostgreSQL PL/pgSQL DO语句支持使用高级代码元素,如函数、游标、动态 SQL 和条件逻辑。

以下示例是从上一节中介绍的 Oracle “员工奖金” PL/SQL 匿名块示例转换而来的更复杂的 PL/pgSQL DO 代码结构:

DO $$
DECLARE
  v_sal_chk DOUBLE PRECISION;
  v_emp_work_years DOUBLE PRECISION;
  v_sql_cmd CHARACTER VARYING(2000);
  v RECORD;
BEGIN
  FOR v IN
  SELECT employee_id, CONCAT_WS('', first_name, ' ', last_name) AS emp_name, hire_date, salary FROM employees
  LOOP
    v_emp_work_years := EXTRACT (YEAR FROM now()) - EXTRACT (YEAR FROM v.hire_date);
    IF v_emp_work_years >= 10 AND v.salary <= 6000 THEN
      RAISE DEBUG USING MESSAGE := CONCAT_WS('', 'Consider a Salary Raise for: ',v.emp_name);
    END IF;
  END LOOP;
EXCEPTION
  WHEN others THEN
    RAISE DEBUG USING MESSAGE := CONCAT_WS('', 'CODE ERR: ',SQLERRM);
END $$;

有关详细信息,请参阅 PostgreSQL 文档中的 DO