数据库基础体系 · 第 27/139 篇。文章以各产品官方稳定版本的公开语义为准；示例会明确引擎、事务与部署边界。

Oracle SQL 与 PL/SQL：数据类型、包、过程、异常和批处理

一、先区分 SQL、PL/SQL 与事务

Oracle 中常见的三层概念容易被混在一起：

• SQL：描述数据的集合运算，例如查询、插入、更新、删除和事务控制。
• PL/SQL：Oracle 的过程化扩展语言，在 SQL 基础上增加变量、条件、循环、过程、函数、包和异常处理。
• 事务：由一组逻辑相关的 SQL 操作组成的原子工作单元。事务不是 PL/SQL 过程的同义词，也不会因为过程返回就自动提交。

一条 SQL 通常由 SQL 引擎解析和执行；PL/SQL 块则由 PL/SQL 引擎解释其过程控制部分，并在需要时调用 SQL 引擎执行 SQL 语句。

例如：

UPDATE orders
   SET status = 'PAID'
 WHERE order_id = 1001;

这是一个 SQL 语句。

BEGIN
  UPDATE orders
     SET status = 'PAID'
   WHERE order_id = 1001;

  IF SQL%ROWCOUNT = 0 THEN
    RAISE_APPLICATION_ERROR(-20001, '订单不存在');
  END IF;
END;
/

这是一个匿名 PL/SQL 块，其中包含 SQL。它执行后，默认仍处于当前事务中，不能因为 PL/SQL 块结束就推断已经提交。

事务边界通常由调用者控制：

BEGIN
  UPDATE orders
     SET status = 'PAID'
   WHERE order_id = 1001;
END;
/

COMMIT;

或者出现错误时：

ROLLBACK;

在存储过程内部随意 COMMIT 会把调用者原本希望保持原子性的多个操作拆开，因此过程是否提交必须成为明确的接口约定，而不是隐含行为。

---

二、Oracle 数据类型体系

2.1 SQL 数据类型与 PL/SQL 数据类型

Oracle 有两套相关但不完全相同的类型环境：

1. SQL 数据类型：用于表列、SQL 表达式、索引、约束和 SQL 参数。
2. PL/SQL 数据类型：用于变量、常量、参数、记录和集合。

PL/SQL 可以使用许多 SQL 类型，例如：

DECLARE
  v_name   VARCHAR2(100);
  v_amount NUMBER(12, 2);
  v_time   TIMESTAMP;
BEGIN
  v_name := 'Alice';
  v_amount := 123.45;
  v_time := SYSTIMESTAMP;
END;
/

但两者并不完全等价。例如，PL/SQL 的集合类型、记录类型和传统意义上的 BOOLEAN 变量不能直接当作普通 SQL 表列使用。较新 Oracle 版本增加了 SQL 层面对 BOOLEAN 的支持，但代码仍需考虑数据库版本、客户端驱动和工具兼容性；跨版本应用通常不应把 SQL BOOLEAN 当作无条件可用的基础类型。

2.2 常用 SQL 类型

字符类型

• CHAR(n)：定长字符，值不足时通常以空格填充。
• VARCHAR2(n)：变长字符，实际保存长度通常不包含尾部填充空格。
• NCHAR、NVARCHAR2：国家字符集类型，用于需要独立国家字符集语义的场景。

示例：

CREATE TABLE user_profile (
  user_id    NUMBER PRIMARY KEY,
  user_name  VARCHAR2(100) NOT NULL,
  nickname   CHAR(10)
);

CHAR(10) 与 VARCHAR2(10) 的比较和存储语义不同。定长字符适合确实具有固定长度语义的数据，例如某些编码；普通名称、描述和业务文本通常使用 VARCHAR2。

字符串长度还涉及字符集和长度语义：

CREATE TABLE message_log (
  content VARCHAR2(200 CHAR)
);

200 CHAR 表示最多 200 个字符；如果使用字节语义，则限制与多字节字符集有关。数据库参数和列定义共同决定实际行为，不能简单用“一个汉字一定占几个字节”推断。

数值类型

NUMBER(precision, scale) 的含义是：

• precision：有效数字总位数；
• scale：小数位数，可以为负数。

例如：

amount NUMBER(12, 2)

表示最多 12 位有效数字，其中小数部分最多 2 位。1234567890.12 符合这一限制，而更大的有效数字数量可能导致精度错误。

金额计算应避免先把数据转为二进制浮点数。Oracle 的 NUMBER 采用十进制精度语义，更适合金额、数量等需要十进制精确性的值。BINARY_FLOAT 和 BINARY_DOUBLE 是二进制浮点类型，适合科学计算或明确接受浮点误差的场景。

日期和时间

常见类型包括：

• DATE：包含日期以及时、分、秒，但不包含时区。
• TIMESTAMP：比 DATE 支持更高的小数秒精度。
• TIMESTAMP WITH TIME ZONE：保存时区信息。
• TIMESTAMP WITH LOCAL TIME ZONE：数据库内部按规范化方式保存，查询时按会话时区显示。

例如：

SELECT
  SYSDATE,
  SYSTIMESTAMP,
  CURRENT_TIMESTAMP
FROM dual;

这些函数的时区和数据类型语义不同：

• SYSDATE 返回数据库服务器操作系统时间，类型为 DATE；
• SYSTIMESTAMP 返回数据库主机系统时间戳，并带时区信息；
• CURRENT_TIMESTAMP 使用当前会话时区。

如果业务数据表示“发生时的全球时间”，应明确采用 UTC 或带时区的时间类型；如果只保存 DATE，之后通常无法可靠恢复原始时区。

大对象

• CLOB：字符大对象；
• NCLOB：国家字符集大对象；
• BLOB：二进制大对象；
• BFILE：数据库外部文件的只读定位器，不等同于数据库内部保存的文件内容。

大对象处理通常不能简单当作普通 VARCHAR2 使用。PL/SQL 提供 DBMS_LOB 等 API 用于分段读写。把大文本无条件拼接为一个巨大的 VARCHAR2，可能产生 ORA-06502 或占用过多 PGA 内存。

行地址和标识类型

• ROWID 表示行在堆表中的物理地址；
• UROWID 可表示更广泛的行地址形式；
• RAW 用于二进制字节；
• XMLType 等专用类型用于特定数据模型。

ROWID 可以提高某些已定位行的访问效率，但它不是业务主键。表迁移、分区变化、导入导出或其他物理操作可能改变行地址，因此不能把 ROWID 作为外部系统长期保存的业务标识。

2.3 PL/SQL 的变量、常量、记录和集合

%TYPE 与 %ROWTYPE

直接复制列类型容易造成程序与表结构漂移。PL/SQL 可以让变量引用表列的类型：

DECLARE
  v_user_name  user_profile.user_name%TYPE;
  v_user       user_profile%ROWTYPE;
BEGIN
  SELECT *
    INTO v_user
    FROM user_profile
   WHERE user_id = 1;

  v_user_name := v_user.user_name;
END;
/

%TYPE 继承列的类型属性；%ROWTYPE 表示一行记录结构。它们减少了硬编码长度和精度，但并不自动解决业务语义问题。例如，某列改为允许 NULL 后，程序仍可能在逻辑上要求非空。

记录类型

DECLARE
  TYPE t_order IS RECORD (
    order_id  orders.order_id%TYPE,
    status    orders.status%TYPE
  );

  v_order t_order;
BEGIN
  v_order.order_id := 1001;
  v_order.status := 'READY';
END;
/

记录是 PL/SQL 内存中的复合值，不能直接作为普通 SQL 表值使用。需要将字段拆开传给 SQL，或者定义 SQL 可见的对象/集合类型。

集合类型

PL/SQL 常用三类集合：

1. 关联数组 INDEX BY：主要用于 PL/SQL 内存中的键值访问。
2. 嵌套表：可以稀疏，某些情况下可以作为 SQL 集合使用。
3. 变长数组 VARRAY：有最大长度限制，元素按顺序组织。

例如：

DECLARE
  TYPE t_id_list IS TABLE OF orders.order_id%TYPE;
  l_ids t_id_list := t_id_list(1001, 1002, 1003);
BEGIN
  FOR i IN 1 .. l_ids.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(l_ids(i));
  END LOOP;
END;
/

集合下标、集合是否稀疏、是否可在 SQL 中使用，是 BULK COLLECT 和 FORALL 正确性的基础。对可能为空的集合直接使用 1 .. collection.COUNT，在某些边界情况下会产生不符合预期的循环行为；批处理代码应先判断集合是否为空。

2.4 隐式转换、精度和 NULL

Oracle 可能在比较、赋值或函数调用时进行隐式类型转换：

SELECT *
  FROM orders
 WHERE order_id = '1001';

如果 order_id 是数值列，Oracle 可能将字符串转换为数字；如果列上存在索引，隐式转换还可能改变谓词形式，影响索引使用和执行计划。更危险的是，字符串内容不合法时会在运行时抛出 ORA-01722: invalid number。

应显式转换并控制格式：

WHERE order_id = TO_NUMBER(:order_id)

日期也不应依赖会话的 NLS_DATE_FORMAT：

WHERE created_at >= TO_DATE(:date_text, 'YYYY-MM-DD')

NULL 不是空字符串、0 或普通的“未知对象”。在 Oracle 中，空字符串在字符类型语境下通常按 NULL 处理，因此下面的判断不会成立：

IF v_name = NULL THEN
  ...
END IF;

正确写法是：

IF v_name IS NULL THEN
  ...
END IF;

SQL 使用三值逻辑：条件结果可能为 TRUE、FALSE 或 UNKNOWN。例如：

SELECT *
  FROM orders
 WHERE status <> 'PAID';

当 status 为 NULL 时，status <> 'PAID' 的结果是 UNKNOWN，该行不会被选中。要包含空值，必须显式写出：

WHERE status <> 'PAID'
   OR status IS NULL

---

三、过程、函数与参数模式

3.1 过程和函数

**过程（procedure）**执行一组操作，可以通过 OUT 或 IN OUT 参数返回结果；**函数（function）**必须返回一个值，可以在 PL/SQL 表达式中使用，也可以在满足 SQL 调用限制时被 SQL 调用。

CREATE OR REPLACE PROCEDURE get_order_status (
  p_order_id IN  orders.order_id%TYPE,
  p_status   OUT orders.status%TYPE
) AS
BEGIN
  SELECT status
    INTO p_status
    FROM orders
   WHERE order_id = p_order_id;
END;
/

调用：

VARIABLE v_status VARCHAR2(30);

EXEC get_order_status(1001, :v_status);

PRINT v_status;

SELECT INTO 必须满足以下条件：

• 查询恰好返回一行：赋值成功；
• 返回零行：抛出 NO_DATA_FOUND；
• 返回多行：抛出 TOO_MANY_ROWS。

因此，SELECT INTO 不是“可能返回一行”的查询语法，而是“程序断言查询结果恰好一行”的语法。

函数示例：

CREATE OR REPLACE FUNCTION order_total (
  p_order_id IN orders.order_id%TYPE
) RETURN NUMBER
IS
  l_total NUMBER;
BEGIN
  SELECT NVL(SUM(quantity * unit_price), 0)
    INTO l_total
    FROM order_item
   WHERE order_id = p_order_id;

  RETURN l_total;
END;
/

如果在 SQL 中调用 PL/SQL 函数，函数必须遵守 SQL 调用环境的限制。例如，函数不能随意对同一查询涉及的表进行会改变其读一致性的 DML；否则可能出现 ORA-04091: table ... is mutating 或其他运行时错误。函数中执行提交也不是正常的 SQL 函数设计方式。

3.2 参数模式

• IN：只读输入参数，默认模式；
• OUT：输出参数，过程返回时向调用者传值；
• IN OUT：既作为输入，也可以被过程修改后返回。

CREATE OR REPLACE PROCEDURE normalize_name (
  p_name IN OUT VARCHAR2
) AS
BEGIN
  p_name := UPPER(TRIM(p_name));
END;
/

参数类型应优先使用 %TYPE，使接口随数据库列类型变化：

p_order_id IN orders.order_id%TYPE

但包或过程的公开接口不应盲目暴露内部表结构。若表结构频繁变化，或者接口属于跨系统稳定契约，可以使用独立的领域类型或明确的 SQL 类型。

---

四、包：组织接口、实现和会话状态

4.1 包的结构

**包（package）**由包规范（specification）和包体（body）组成：

• 包规范声明外部可见的过程、函数、类型、常量和变量；
• 包体实现规范中的子程序，也可以定义仅供包内部使用的私有成员。

CREATE OR REPLACE PACKAGE order_api AS
  PROCEDURE mark_paid (
    p_order_id IN orders.order_id%TYPE
  );

  FUNCTION get_total (
    p_order_id IN orders.order_id%TYPE
  ) RETURN NUMBER;
END order_api;
/

包体：

CREATE OR REPLACE PACKAGE BODY order_api AS

  PROCEDURE mark_paid (
    p_order_id IN orders.order_id%TYPE
  ) AS
  BEGIN
    UPDATE orders
       SET status = 'PAID'
     WHERE order_id = p_order_id;

    IF SQL%ROWCOUNT = 0 THEN
      RAISE_APPLICATION_ERROR(-20001, '订单不存在');
    END IF;
  END mark_paid;

  FUNCTION get_total (
    p_order_id IN orders.order_id%TYPE
  ) RETURN NUMBER
  AS
    l_total NUMBER;
  BEGIN
    SELECT NVL(SUM(quantity * unit_price), 0)
      INTO l_total
      FROM order_item
     WHERE order_id = p_order_id;

    RETURN l_total;
  END get_total;

END order_api;
/

调用：

BEGIN
  order_api.mark_paid(1001);

  DBMS_OUTPUT.PUT_LINE(
    TO_CHAR(order_api.get_total(1001))
  );
END;
/

包的主要价值不是“把几个过程放在同一个文件”，而是提供：

1. 接口与实现分离：调用方只依赖规范；
2. 命名空间：通过 order_api.mark_paid 区分同名操作；
3. 封装私有实现：未出现在规范中的成员对包外不可见；
4. 复用类型和常量：多个过程共享同一组声明；
5. 会话级状态：包变量在同一数据库会话中可被多个调用共享。

4.2 包的依赖和状态

重新编译包规范，通常会使依赖它的对象失效；重新编译仅修改包体，影响范围通常小于修改规范。应用部署时必须检查 USER_OBJECTS 或 DBA_OBJECTS 中对象的 STATUS：

SELECT object_name, object_type, status
  FROM user_objects
 WHERE object_name = 'ORDER_API';

包变量具有会话状态。例如：

CREATE OR REPLACE PACKAGE request_context AS
  g_user_id NUMBER;
END;
/

BEGIN
  request_context.g_user_id := 100;
END;
/

同一会话后续调用可以读取 g_user_id，但另一个数据库会话看不到它。连接池会复用物理数据库会话，因此如果应用没有在每次借用连接时重置包状态，就可能把上一个请求的上下文带入下一个请求。这是包状态与无状态 Web 请求之间的典型边界。

4.3 AUTHID DEFINER 与 AUTHID CURRENT_USER

存储过程、函数和包涉及权限检查时，需要区分运行权限：

• AUTHID DEFINER：通常称为定义者权限，使用拥有者权限执行；
• AUTHID CURRENT_USER：调用者权限，使用当前用户权限执行。

定义者权限适合封装受控的数据访问接口，但也扩大了代码被授予的权限影响范围。调用者权限更接近“按调用者权限执行”，但调用方必须拥有所需对象权限。

权限问题常表现为：

ORA-00942: table or view does not exist
ORA-01031: insufficient privileges

对象通过角色获得的权限与存储对象编译、运行时权限语义并不完全相同。部署脚本应明确使用直接授予的对象权限，而不能只在交互式会话中验证“角色能访问”。

---

五、异常：错误如何产生、传播和处理

5.1 异常是控制流的一部分

PL/SQL 异常可以来自：

1. Oracle 运行时自动抛出；
2. 程序显式 RAISE；
3. RAISE_APPLICATION_ERROR 抛出业务错误。

基本结构：

BEGIN
  -- 正常处理
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    -- 零行
  WHEN TOO_MANY_ROWS THEN
    -- 多行
  WHEN OTHERS THEN
    -- 其他异常
END;
/

常见预定义异常包括：

• NO_DATA_FOUND：SELECT INTO 没有返回行；
• TOO_MANY_ROWS：SELECT INTO 返回多行；
• DUP_VAL_ON_INDEX：违反唯一约束；
• VALUE_ERROR：转换、精度或赋值错误；
• INVALID_NUMBER：字符串转换为数字失败；
• ZERO_DIVIDE：除数为零；
• NO_DATA_NEEDED：某些可提前结束的管道式处理场景。

异常发生后，当前语句通常失败；如果当前块没有匹配处理器，异常向外层块传播。异常处理器执行完毕后，控制流不会自动回到出错语句继续执行，而是离开当前块，进入外层后续流程。

5.2 自定义异常

DECLARE
  e_invalid_status EXCEPTION;
BEGIN
  IF :new_status NOT IN ('READY', 'PAID', 'CANCELLED') THEN
    RAISE e_invalid_status;
  END IF;
EXCEPTION
  WHEN e_invalid_status THEN
    RAISE_APPLICATION_ERROR(-20002, '非法订单状态');
END;
/

RAISE 适合重新抛出已声明的异常；RAISE_APPLICATION_ERROR 用于返回应用可识别的错误号，错误号范围通常为 -20000 到 -20999：

RAISE_APPLICATION_ERROR(-20003, '订单已经取消，不能支付');

也可以把 Oracle 错误号映射为有名字的异常：

DECLARE
  e_deadlock EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_deadlock, -60);
BEGIN
  NULL;
EXCEPTION
  WHEN e_deadlock THEN
    -- 处理 ORA-00060
    RAISE;
END;
/

5.3 WHEN OTHERS 的正确边界

下面的写法会吞掉错误：

EXCEPTION
  WHEN OTHERS THEN
    NULL;

调用方会误以为操作成功，实际数据可能只完成了一部分。

至少应记录上下文并重新抛出：

EXCEPTION
  WHEN OTHERS THEN
    DBMS_OUTPUT.PUT_LINE(
      SQLCODE || ': ' || SQLERRM
    );
    RAISE;
END;
/

生产日志应记录业务主键、批次号、执行阶段和错误堆栈。SQLERRM 只提供当前错误文本；需要完整调用栈时可使用 DBMS_UTILITY.FORMAT_ERROR_STACK 和 DBMS_UTILITY.FORMAT_ERROR_BACKTRACE。

异常处理不能替代事务处理。捕获异常后是否回滚，要看事务边界：

BEGIN
  order_api.mark_paid(1001);
  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

这里由调用者决定失败时回滚整个调用单元。若过程内部已经提交，外层 ROLLBACK 无法撤销已经提交的部分。

5.4 保存点与自治事务

保存点只影响当前事务：

SAVEPOINT before_detail;

BEGIN
  INSERT INTO order_item (...);
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK TO before_detail;
    RAISE;
END;

ROLLBACK TO 会撤销保存点之后的修改，但不会结束整个事务，也不会撤销保存点之前的修改。

PRAGMA AUTONOMOUS_TRANSACTION 创建独立事务，常被用于错误日志：

CREATE OR REPLACE PROCEDURE write_error_log (
  p_message IN VARCHAR2
) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_log(message, log_time)
  VALUES (p_message, SYSTIMESTAMP);

  COMMIT;
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    RAISE;
END;
/

自治事务必须独立提交或回滚。它可以在主事务回滚后保留日志，但如果自治事务访问主事务尚未提交且被锁住的数据，可能发生阻塞或死锁。因此日志表应尽量只写入独立的错误信息，不要依赖主事务中尚未提交的业务数据。

---

六、从逐行处理到批处理

6.1 游标和逐行处理

显式游标把查询结果逐行交给 PL/SQL：

DECLARE
  CURSOR c_orders IS
    SELECT order_id
      FROM orders
     WHERE status = 'READY';

BEGIN
  FOR r IN c_orders LOOP
    UPDATE orders
       SET status = 'PROCESSING'
     WHERE order_id = r.order_id;
  END LOOP;
END;
/

这种写法清晰，但每次循环都可能发生一次 PL/SQL 到 SQL 引擎的切换。数据量大时，切换成本、网络往返或内部调用次数会成为瓶颈。能用单条集合 SQL 表达的逻辑，应优先写成一条 SQL：

UPDATE orders
   SET status = 'PROCESSING'
 WHERE status = 'READY';

只有在每行需要不同的过程化逻辑、调用外部例程、复杂异常分类或无法用集合 SQL 表达时，才需要逐行处理。

6.2 BULK COLLECT：批量取数

BULK COLLECT 将多行一次性取入集合：

DECLARE
  TYPE t_id_list IS TABLE OF orders.order_id%TYPE;
  l_ids t_id_list;
BEGIN
  SELECT order_id
    BULK COLLECT INTO l_ids
    FROM orders
   WHERE status = 'READY';

  DBMS_OUTPUT.PUT_LINE('读取行数: ' || l_ids.COUNT);
END;
/

它减少了逐行取数的切换，但会把结果放入 PGA。若结果集很大，会导致会话内存压力。因此通常使用 LIMIT 分批读取。

6.3 FORALL：批量执行 DML

FORALL 不是普通循环，它把集合中的元素用于批量 DML：

DECLARE
  TYPE t_id_list IS TABLE OF orders.order_id%TYPE;
  l_ids t_id_list := t_id_list(1001, 1002, 1003);
BEGIN
  FORALL i IN 1 .. l_ids.COUNT
    UPDATE orders
       SET status = 'PROCESSING'
     WHERE order_id = l_ids(i);

  DBMS_OUTPUT.PUT_LINE('影响行数: ' || SQL%ROWCOUNT);
END;
/

其逻辑相当于对每个集合元素执行一次 DML，但 PL/SQL 与 SQL 引擎之间的切换次数显著减少。FORALL 只能用于一条 DML 语句，不能在其中放入普通的 PL/SQL 语句或复杂分支。

注意：

• SQL%ROWCOUNT 表示本次 FORALL 的累计影响行数；
• SQL%BULK_ROWCOUNT(i) 表示第 i 个集合元素对应的 DML 影响行数；
• 如果 DML 违反约束，默认可能使整个 FORALL 语句失败；
• SAVE EXCEPTIONS 可以让其他元素继续执行，并在结束时集中报告错误。

6.4 一个可运行的批处理过程

以下示例假设已经存在表：

CREATE TABLE orders (
  order_id NUMBER PRIMARY KEY,
  status   VARCHAR2(20) NOT NULL,
  updated_at TIMESTAMP
);

包规范：

CREATE OR REPLACE PACKAGE order_batch_api AS
  PROCEDURE mark_ready_as_processing (
    p_limit   IN  PLS_INTEGER,
    p_changed OUT PLS_INTEGER
  );
END order_batch_api;
/

包体：

CREATE OR REPLACE PACKAGE BODY order_batch_api AS

  PROCEDURE mark_ready_as_processing (
    p_limit   IN  PLS_INTEGER,
    p_changed OUT PLS_INTEGER
  ) AS
    CURSOR c_orders IS
      SELECT order_id
        FROM orders
       WHERE status = 'READY'
       ORDER BY order_id;

    TYPE t_id_list IS TABLE OF orders.order_id%TYPE;
    l_ids     t_id_list;
    l_changed PLS_INTEGER := 0;
  BEGIN
    IF p_limit IS NULL OR p_limit <= 0 THEN
      RAISE_APPLICATION_ERROR(-20010, 'p_limit 必须大于 0');
    END IF;

    OPEN c_orders;

    LOOP
      FETCH c_orders
        BULK COLLECT INTO l_ids
        LIMIT p_limit;

      EXIT WHEN l_ids.COUNT = 0;

      FORALL i IN 1 .. l_ids.COUNT
        UPDATE orders
           SET status     = 'PROCESSING',
               updated_at = SYSTIMESTAMP
         WHERE order_id = l_ids(i)
           AND status = 'READY';

      l_changed := l_changed + SQL%ROWCOUNT;
    END LOOP;

    CLOSE c_orders;
    p_changed := l_changed;
  EXCEPTION
    WHEN OTHERS THEN
      IF c_orders%ISOPEN THEN
        CLOSE c_orders;
      END IF;
      RAISE;
  END mark_ready_as_processing;

END order_batch_api;
/

调用方式：

VARIABLE v_changed NUMBER;

BEGIN
  order_batch_api.mark_ready_as_processing(
    p_limit   => 500,
    p_changed => :v_changed
  );
END;
/

PRINT v_changed;
COMMIT;

每一步的含义如下：

1. 游标按 order_id 读取 READY 行；
2. FETCH ... BULK COLLECT ... LIMIT 500 每次最多把 500 个主键读入 PGA；
3. FORALL 使用这些主键批量执行更新；
4. status = 'READY' 是保护条件，避免已经被其他事务修改的行被无条件覆盖；
5. 过程只统计和返回结果，不提交；
6. 调用者确认成功后提交，发生异常则可以回滚。

这个实现仍有并发边界：普通游标读取的数据在后续更新前，可能被其他会话先处理。保护条件能避免错误覆盖，但两个会话可能都读取同一批主键，其中一个更新成功，另一个更新为 0 行。如果要求严格的“领取任务”语义，应设计行锁、状态转换和重试策略，而不能仅依靠 BULK COLLECT。

---

七、批处理异常与部分成功

7.1 SAVE EXCEPTIONS

需要让一批记录尽量继续执行，同时保留失败项时，可以使用：

DECLARE
  TYPE t_id_list IS TABLE OF orders.order_id%TYPE;
  l_ids t_id_list := t_id_list(1001, 1002, 1003);

  e_bulk_errors EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_bulk_errors, -24381);
BEGIN
  FORALL i IN 1 .. l_ids.COUNT SAVE EXCEPTIONS
    UPDATE orders
       SET status = 'PROCESSING'
     WHERE order_id = l_ids(i);

EXCEPTION
  WHEN e_bulk_errors THEN
    FOR i IN 1 .. SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE(
        '集合下标=' || SQL%BULK_EXCEPTIONS(i).ERROR_INDEX ||
        ', 错误码=' || SQL%BULK_EXCEPTIONS(i).ERROR_CODE
      );
    END LOOP;

    RAISE;
END;
/

ERROR_INDEX 是集合下标，不一定是业务主键；必须用它回查 l_ids(ERROR_INDEX) 才能定位业务记录。

SAVE EXCEPTIONS 的关键语义是：语句执行期间允许部分元素成功，执行结束后以异常形式报告失败元素。它不会自动回滚已成功的元素。是否保留这些成功结果，取决于后续是否提交或回滚。

因此要先决定批处理的原子性：

• 整批原子：任意一条失败就回滚整批；
• 逐项容错：成功项提交，失败项记录并稍后重试；
• 分块原子：每 500 或 1000 条构成一个事务单元。

这三种模式没有统一的“最佳”答案。选择取决于业务是否允许部分完成、重试是否幂等以及锁持有时间。

7.2 LOG ERRORS 的不同用途

Oracle 的 DML 错误日志功能可以将部分行错误记录到错误表，而不一定让整条 DML 因单行错误立即失败。它适合数据装载、清洗和可接受部分失败的场景。

其典型流程是：

BEGIN
  DBMS_ERRLOG.CREATE_ERROR_LOG(
    dml_table_name => 'ORDERS'
  );
END;
/

之后在 DML 中使用 LOG ERRORS。错误日志表中的错误码、错误消息和相关行信息可供后续分析。

它与 SAVE EXCEPTIONS 的区别在于：

• SAVE EXCEPTIONS 是 PL/SQL FORALL 的批量异常收集机制；
• LOG ERRORS 是 SQL DML 的错误记录机制；
• 两者都可能产生部分成功，不能因为错误被记录就认为事务已经安全完成；
• 错误日志表本身也需要纳入运维和清理策略。

---

八、批处理的事务、锁和故障路径

8.1 提交频率不是单纯的性能参数

一次性提交大量数据：

• 原子性强；
• 失败时回滚范围大；
• 持有锁的时间可能较长；
• 产生的撤销和重做压力可能较大。

频繁提交：

• 单次事务较小；
• 锁释放更快；
• 失败后容易从小批次继续；
• 但会削弱整批原子性；
• 如果没有稳定的断点和幂等条件，重试可能重复处理。

一个可重试批处理通常需要：

1. 稳定的业务主键；
2. 明确的状态转换，例如 READY -> PROCESSING -> DONE；
3. 失败状态或错误表；
4. 可识别的批次号；
5. 重跑时不会重复产生副作用的设计。

“先更新状态再调用外部系统”尤其需要谨慎：数据库事务无法自动回滚已经发出的 HTTP、消息或文件操作。这属于数据库事务与外部副作用之间的边界，通常需要消息表、事务性发件箱或补偿机制协调。

8.2 并发领取任务

如果多个会话并行处理任务，常见目标是：

• 同一行只被一个工作者领取；
• 一个工作者失败后任务可以重试；
• 工作者之间不长时间互相等待。

可以考虑 SELECT ... FOR UPDATE SKIP LOCKED，但它必须与事务边界一起设计。被锁定的行只能在事务结束时释放；如果在打开的 FOR UPDATE 游标中间提交，可能导致游标失效或出现提取顺序错误。因此，不能机械地把“加锁读取”和“每批提交”拼接在一起，而应设计明确的领取事务：

1. 短事务锁定并把任务从 READY 改为 PROCESSING；
2. 提交领取结果；
3. 在后续事务中处理 PROCESSING 任务；
4. 成功改为 DONE，失败改为可重试状态。

这样把“领取”和“实际处理”拆开，才能处理工作者崩溃后的超时回收问题。

---

九、与 Oracle 架构和 SQL 调优的关系

9.1 PGA 与批量集合

BULK COLLECT 的集合主要位于执行会话的 PGA。LIMIT 越大，不一定越快：

• 太小：批次切换次数增加；
• 太大：单会话 PGA 占用增加，多个并发会话可能共同造成内存压力。

LIMIT 应通过实际数据宽度、并发数、执行时间和 PGA 监控验证，而不是套用固定数字。

9.2 执行计划和批处理 DML

FORALL 减少的是 PL/SQL 与 SQL 引擎之间的切换，不会自动让单条 DML 使用更好的执行计划。更新语句是否能高效定位行，仍取决于：

• 谓词是否有合适索引；
• 列上是否发生隐式类型转换；
• 表和索引统计信息是否及时；
• 优化器估算的基数是否合理；
• 是否存在锁竞争和热点块。

例如：

UPDATE orders
   SET status = 'PROCESSING'
 WHERE order_id = :id
   AND status = 'READY';

order_id 为主键时，通常可以快速定位目标行；但如果批处理只按低选择性的 status 更新大量行，索引是否有益要结合数据分布、更新比例和执行计划判断。可以使用：

EXPLAIN PLAN FOR
UPDATE orders
   SET status = 'PROCESSING'
 WHERE order_id = 1001
   AND status = 'READY';

SELECT *
  FROM TABLE(DBMS_XPLAN.DISPLAY);

EXPLAIN PLAN 是估算计划，不一定等于实际执行计划。生产诊断应结合实际游标信息、执行统计、等待事件和锁信息分析。Hint 只能表达明确的优化器指导，不能用来掩盖统计信息错误、类型不匹配或数据模型问题。

9.3 SQL 与 PL/SQL 的边界选择

可以按以下因果关系判断实现方式：

• 能用一条集合 SQL 完成：优先集合 SQL；
• 需要逐行但不需要复杂过程逻辑：考虑游标循环；
• 需要逐行逻辑且数据量较大：考虑 BULK COLLECT + FORALL；
• 需要稳定公开接口和内部封装：使用包；
• 需要给调用者返回单一计算值：使用函数；
• 需要执行命令或返回多个结果：使用过程；
• 需要区分可恢复错误与不可恢复错误：定义明确异常和状态；
• 需要跨多个调用保持状态：谨慎使用包变量，并明确会话边界。

批量化不是把所有代码都改成 FORALL。如果核心操作本来只影响一行，或者 DML 逻辑很复杂、每行都需要独立异常决策，强行批量化会降低可读性和可诊断性。

---

十、常见错误及诊断方法

ORA-01403: no data found

通常来自 SELECT INTO 无结果，也可能来自显式使用 NO_DATA_FOUND 的逻辑。应先判断“无数据是否是正常分支”。如果正常，不一定需要把它当作系统错误；可以使用聚合、COUNT 或先做存在性判断，但要注意额外查询带来的并发窗口。

ORA-01422: exact fetch returns more than requested number of rows

说明程序把多行结果当作单行。解决方法不是简单捕获异常，而是重新确认业务唯一性：

• 增加正确谓词；
• 为业务唯一条件建立唯一约束；
• 改用游标或集合查询；
• 不要使用没有确定排序的“任意第一行”。

ORA-06502: PL/SQL: numeric or value error

常见原因包括：

• 字符串超过变量长度；
• 数值精度不足；
• 非法类型赋值；
• 大对象被错误地转换为普通字符串。

诊断时应检查变量声明、隐式转换、字符长度语义和实际数据，而不是只扩大变量长度。

ORA-04091: table is mutating

通常发生在行级触发器中查询或修改正在被修改的同一表。问题本质是：单行触发器执行期间，表的集合状态尚未形成稳定的可见语义。常见重构方式是使用复合触发器、语句级处理，或把业务逻辑移到显式过程和批处理流程中。

批处理“成功但数据不对”

重点检查：

1. 过程内部是否意外 COMMIT；
2. 调用方是否真的提交；
3. 是否发生了部分成功；
4. FORALL 使用的集合是否为空、稀疏或下标错误；
5. 更新条件是否遗漏状态保护；
6. 多会话是否重复领取同一任务；
7. 连接池是否复用了旧的包变量；
8. 字符串、数字和日期是否发生隐式转换。

数据库中的过程、异常和批处理不是孤立语法点。数据类型决定值如何保存和比较，过程决定调用边界，包决定接口与会话状态，异常决定失败如何传播，批处理决定引擎切换、内存、锁和事务如何共同变化。只有把这些边界同时纳入设计，PL/SQL 代码才不仅“能够运行”，还能够在并发、失败和重试条件下保持可解释性。

---

系列导航与关联阅读

• 系列入口：数据库完整学习路线：从关系模型、事务索引到分布式与向量检索
• 上一篇：Oracle 数据库架构：Instance、SGA、PGA、数据文件与后台进程
• 下一篇：Oracle 事务与 Undo：读一致性、SCN、锁、Redo 和恢复机制
• 延伸：Oracle 索引与优化器：统计信息、执行计划、Hint 和 SQL 调优

官方资料

• Oracle Database Documentation
• Oracle Database Concepts

本文依据数据库官方文档重新梳理；正文、示例与生产检查清单由 WR BLOG 编写。