DB / Oracle / SQL 开发

Oracle SQL 批量操作

2026.09.06

Oracle SQL 批量操作

适用版本：Oracle Database 10g / 11g / 12c / 19c / 23ai 文档版本：v1.0 / 2026-07

1. 概述

SQL 批量操作提升性能[1]：

方式：

  • INSERT ALL
  • INSERT FIRST
  • 多行 INSERT
  • MERGE
  • FORALL（PL/SQL）

2. INSERT ALL

2.1 多表插入

INSERT ALL
  INTO emp_history (id, name, hire_date) VALUES (id, name, hire_date)
  INTO emp_salary (id, salary) VALUES (id, salary)
SELECT id, name, hire_date, salary FROM employees
WHERE hire_date > DATE '2026-01-01';

2.2 条件

INSERT ALL
  WHEN salary < 5000 THEN
    INTO emp_low (id, salary) VALUES (id, salary)
  WHEN salary BETWEEN 5000 AND 10000 THEN
    INTO emp_mid (id, salary) VALUES (id, salary)
  ELSE
    INTO emp_high (id, salary) VALUES (id, salary)
SELECT id, salary FROM employees;

3. INSERT FIRST

INSERT FIRST
  WHEN salary < 5000 THEN
    INTO emp_low VALUES (id, salary)
  WHEN dept_id = 10 THEN
    INTO emp_dept10 VALUES (id, salary)
  ELSE
    INTO emp_other VALUES (id, salary)
SELECT id, salary, dept_id FROM employees;

区别：

  • INSERT ALL：所有条件都判断
  • INSERT FIRST：仅第一个匹配

4. 多行 INSERT

4.1 INSERT … SELECT

INSERT INTO emp_copy
SELECT * FROM employees WHERE dept_id = 10;

4.2 多值

INSERT ALL
  INTO t (id, name) VALUES (1, 'A')
  INTO t (id, name) VALUES (2, 'B')
  INTO t (id, name) VALUES (3, 'C')
SELECT * FROM dual;

4.3 INSERT INTO … SELECT FROM

INSERT INTO sales_2026
SELECT * FROM sales WHERE sale_date >= DATE '2026-01-01';

5. 直接路径 INSERT

5.1 APPEND Hint

INSERT /*+ APPEND */ INTO emp_copy
SELECT * FROM employees;

5.2 特性

- 直接写数据文件，绕过 Buffer Cache
- 高水位之上写入
- 速度快
- 不产生 UNDO（仅 REDO）

5.3 限制

- 提交后才能查询
- 锁表
- 空间可能浪费

6. MERGE 批量

6.1 基本

MERGE INTO target t
USING source s
ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.name = s.name
WHEN NOT MATCHED THEN
  INSERT (id, name) VALUES (s.id, s.name);

6.2 条件

MERGE INTO target t
USING source s
ON (t.id = s.id)
WHEN MATCHED THEN
  UPDATE SET t.name = s.name
  DELETE WHERE s.status = 'INACTIVE'
WHEN NOT MATCHED THEN
  INSERT (id, name) VALUES (s.id, s.name)
  WHERE s.status = 'ACTIVE';

详细见：Oracle MERGE 语句。

7. PL/SQL FORALL

7.1 基本

DECLARE
  TYPE id_array IS TABLE OF employees.id%TYPE;
  TYPE sal_array IS TABLE OF employees.salary%TYPE;
  v_ids id_array;
  v_sals sal_array;
BEGIN
  SELECT id BULK COLLECT INTO v_ids FROM employees WHERE dept_id = 10;
  
  FOR i IN 1..v_ids.COUNT LOOP
    v_sals(i) := 5000;
  END LOOP;
  
  FORALL i IN 1..v_ids.COUNT
    UPDATE employees SET salary = v_sals(i) WHERE id = v_ids(i);
END;
/

7.2 SAVE EXCEPTIONS

FORALL i IN 1..v_ids.COUNT SAVE EXCEPTIONS
  UPDATE employees SET salary = v_sals(i) WHERE id = v_ids(i);

-- 异常
EXCEPTION
  WHEN OTHERS THEN
    FOR j IN 1..SQL%BULK_EXCEPTIONS.COUNT LOOP
      DBMS_OUTPUT.PUT_LINE('Error ' || SQL%BULK_EXCEPTIONS(j).ERROR_INDEX || 
        ': ' || SQL%BULK_EXCEPTIONS(j).ERROR_CODE);
    END LOOP;

7.3 INDICES OF

FORALL i IN INDICES OF v_ids
  UPDATE employees SET salary = v_sals(i) WHERE id = v_ids(i);

详细见：Oracle BULK COLLECT 与 FORALL。

8. BULK COLLECT

8.1 基本

DECLARE
  TYPE emp_array IS TABLE OF employees%ROWTYPE;
  v_emps emp_array;
BEGIN
  SELECT * BULK COLLECT INTO v_emps FROM employees WHERE dept_id = 10;
  
  FOR i IN 1..v_emps.COUNT LOOP
    DBMS_OUTPUT.PUT_LINE(v_emps(i).name);
  END LOOP;
END;
/

8.2 LIMIT

DECLARE
  CURSOR c IS SELECT * FROM employees;
  TYPE emp_array IS TABLE OF employees%ROWTYPE;
  v_emps emp_array;
BEGIN
  OPEN c;
  LOOP
    FETCH c BULK COLLECT INTO v_emps LIMIT 1000;
    EXIT WHEN v_emps.COUNT = 0;
    
    -- 处理
    FORALL i IN 1..v_emps.COUNT
      INSERT INTO emp_copy VALUES v_emps(i);
  END LOOP;
  CLOSE c;
END;
/

9. 性能对比

方式性能场景
单行 DML慢少量
FORALL快大量
INSERT ALL快多表
MERGE快同步
APPEND最快大批量

10. 性能优化

10.1 批量大小

- FORALL LIMIT：1000-10000
- 太小：开销
- 太大：内存

10.2 直接路径

INSERT /*+ APPEND PARALLEL(t, 4) */ INTO t
SELECT * FROM source;

10.3 NOLOGGING

ALTER TABLE t NOLOGGING;
INSERT /*+ APPEND */ INTO t SELECT ...;
ALTER TABLE t LOGGING;

详细见：Oracle 数据加载工具对比。

11. 常见坑与排错

11.1 APPEND 不能查询

INSERT /*+ APPEND */ INTO t ...;
-- 必须提交
COMMIT;
SELECT * FROM t;  -- OK

11.2 FORALL 内存

- LIMIT 控制内存
- 大数据分批

11.3 MERGE 性能

-- 索引
CREATE INDEX idx_source ON source(id);

12. 最佳实践

  1. FORALL 批量：性能
  2. LIMIT 1000-10000：平衡
  3. APPEND 大批量：直接路径
  4. NOLOGGING：减少日志
  5. MERGE 同步：高效
  6. INSERT ALL：多表
  7. SAVE EXCEPTIONS：健壮
  8. BULK COLLECT：高效
  9. 测试：场景
  10. 文档化：方案

13. 参考资料

[1] Oracle Database PL/SQL Language Reference 19c, “FORALL” https://docs.oracle.com/en/database/oracle/oracle-database/19/lnpls/