Oracle PL/SQL 单元测试详解

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

---

1. 概述

PL/SQL 单元测试保证代码质量[1]：

详细见：Oracle PL/SQL 单元测试。

---

2. 手工测试

2.1 基本

BEGIN
  -- 准备
  -- 执行
  -- 验证
  -- 清理
END;
/

2.2 示例

DECLARE
  v_count NUMBER;
  v_emp_id NUMBER;
BEGIN
  -- 准备
  DELETE FROM employees WHERE email = 'test@test.com';
  
  -- 执行
  sp_hire_emp('Test', 5000, 10, v_emp_id);
  
  -- 验证
  SELECT COUNT(*) INTO v_count FROM employees WHERE id = v_emp_id;
  IF v_count != 1 THEN
    DBMS_OUTPUT.PUT_LINE('FAIL: Expected 1, got ' || v_count);
  ELSE
    DBMS_OUTPUT.PUT_LINE('PASS');
  END IF;
  
  -- 清理
  DELETE FROM employees WHERE id = v_emp_id;
  COMMIT;
END;
/

---

3. utPLSQL

3.1 安装

-- 安装 utPLSQL
-- https://utplsql.org/

3.2 注解

CREATE OR REPLACE PACKAGE test_emp_pkg IS
  --%suite(Employee tests)
  --%suitepath(all.emp)
  
  --%beforeeach
  PROCEDURE setup;
  
  --%aftereach
  PROCEDURE teardown;
  
  --%test(Hire employee)
  PROCEDURE test_hire_emp;
  
  --%test(Get count)
  PROCEDURE test_get_count;
END;
/

CREATE OR REPLACE PACKAGE BODY test_emp_pkg IS
  PROCEDURE setup IS
  BEGIN
    DELETE FROM employees WHERE email LIKE 'test%';
    COMMIT;
  END;
  
  PROCEDURE teardown IS
  BEGIN
    DELETE FROM employees WHERE email LIKE 'test%';
    COMMIT;
  END;
  
  PROCEDURE test_hire_emp IS
    v_id NUMBER;
    v_count NUMBER;
  BEGIN
    sp_hire_emp('Test', 5000, 10, v_id);
    
    SELECT COUNT(*) INTO v_count FROM employees WHERE id = v_id;
    ut.expect(v_count).to_equal(1);
    
    SELECT COUNT(*) INTO v_count FROM employees WHERE id = v_id AND salary = 5000;
    ut.expect(v_count).to_equal(1);
  END;
  
  PROCEDURE test_get_count IS
    v_count NUMBER;
  BEGIN
    v_count := fn_get_emp_count(10);
    ut.expect(v_count).to_be_greater_than(0);
  END;
END;
/

3.3 运行

BEGIN
  ut.run();
END;
/

-- 或命令行
-- utplsql run scott/tiger

3.4 断言

ut.expect(actual).to_equal(expected);
ut.expect(actual).to_be_null();
ut.expect(actual).to_be_not_null();
ut.expect(actual).to_be_true();
ut.expect(actual).to_be_greater_than(n);
ut.expect(actual).to_be_less_than(n);
ut.expect(actual).to_contain(expected);
ut.expect(actual).to_match(pattern);

---

4. SQL Developer 单元测试

4.1 创建

- SQL Developer → Tools → Unit Test
- 创建测试
- 选择对象
- 定义断言

4.2 执行

- 运行测试
- 查看结果
- 报告

---

5. 测试组织

5.1 测试套件

--%suite(Employee Management)
--%suitepath(all.hr)

--%test
PROCEDURE test_hire;
PROCEDURE test_fire;
PROCEDURE test_promote;

5.2 Setup / Teardown

--%beforeeach
PROCEDURE setup;

--%aftereach
PROCEDURE teardown;

--%beforeall
PROCEDURE setup_all;

--%afterall
PROCEDURE teardown_all;

---

6. 测试场景

6.1 正向

PROCEDURE test_hire_emp IS
  v_id NUMBER;
BEGIN
  sp_hire_emp('Alice', 5000, 10, v_id);
  ut.expect(v_id).to_be_greater_than(0);
END;

6.2 负向

PROCEDURE test_hire_emp_invalid_salary IS
  v_id NUMBER;
BEGIN
  BEGIN
    sp_hire_emp('Alice', -100, 10, v_id);
    ut.fail('Should have raised exception');
  EXCEPTION
    WHEN OTHERS THEN
      ut.expect(SQLCODE).to_equal(-20002);
  END;
END;

6.3 边界

PROCEDURE test_hire_emp_max_salary IS
  v_id NUMBER;
BEGIN
  -- 边界值
  sp_hire_emp('Alice', 100000, 10, v_id);
  ut.expect(v_id).to_be_greater_than(0);
  
  -- 超过边界
  BEGIN
    sp_hire_emp('Bob', 100001, 10, v_id);
    ut.fail('Should have raised');
  EXCEPTION
    WHEN OTHERS THEN
      ut.expect(SQLCODE).to_equal(-20002);
  END;
END;

6.4 NULL

PROCEDURE test_hire_emp_null_name IS
  v_id NUMBER;
BEGIN
  BEGIN
    sp_hire_emp(NULL, 5000, 10, v_id);
    ut.fail('Should have raised');
  EXCEPTION
    WHEN OTHERS THEN
      ut.expect(SQLCODE).to_equal(-20001);
  END;
END;

---

7. 数据准备

7.1 测试数据

PROCEDURE setup IS
BEGIN
  INSERT INTO departments (id, name) VALUES (99, 'Test');
  INSERT INTO employees (id, name, salary, dept_id) VALUES (1001, 'Test1', 5000, 99);
  INSERT INTO employees (id, name, salary, dept_id) VALUES (1002, 'Test2', 6000, 99);
  COMMIT;
END;

7.2 清理

PROCEDURE teardown IS
BEGIN
  DELETE FROM employees WHERE dept_id = 99;
  DELETE FROM departments WHERE id = 99;
  COMMIT;
END;

7.3 事务

-- 测试在事务中
PROCEDURE test_... IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  ...
  ROLLBACK;
END;

---

8. Mock

8.1 替换

-- 测试中替换依赖
-- utPLSQL mock
ut.expect(...).to_be_called();

8.2 Stub

-- 包重定义
CREATE OR REPLACE PACKAGE BODY mock_pkg AS
  FUNCTION get_time RETURN DATE IS
  BEGIN
    RETURN DATE '2025-01-01';
  END;
END;
/

---

9. 覆盖率

9.1 utPLSQL

BEGIN
  ut.run(ut_coverage_html_reporter());
END;
/

-- HTML 报告

9.2 DBMS_PROFILER

EXEC DBMS_PROFILER.START_PROFILER('test');
-- 执行测试
EXEC DBMS_PROFILER.STOP_PROFILER;

SELECT unit_name, line, total_occur 
FROM plsql_profiler_data ...

---

10. CI/CD 集成

10.1 命令行

utplsql run scott/tiger@db -f=html -o=report.html
utplsql run scott/tiger@db -f=junit -o=report.xml

10.2 Maven / Gradle

- 集成 utPLSQL
- 测试阶段
- 报告

10.3 Jenkins

- 触发测试
- 收集报告
- 失败通知

---

11. 测试最佳实践

11.1 AAA

- Arrange（准备）
- Act（执行）
- Assert（验证）

11.2 独立

- 测试独立
- 不依赖顺序
- 清理

11.3 命名

- test_xxx
- 描述性
- 场景

11.4 覆盖

- 正向
- 负向
- 边界
- 异常

---

12. 常见坑与排错

12.1 数据污染

- 清理
- 事务回滚
- 隔离

12.2 依赖

- Mock
- Stub
- 顺序

12.3 性能

- 测试慢
- 数据量
- 并行

---

13. 测试金字塔

- 单元测试：多
- 集成测试：中
- 端到端：少

---

14. 最佳实践

1. utPLSQL：现代
2. AAA 模式：清晰
3. 覆盖：完整
4. 独立：隔离
5. 清理：避免污染
6. 命名：描述性
7. Mock：依赖
8. CI/CD：自动化
9. 报告：可视化
10. 持续：维护

---

15. 参考资料

[1] utPLSQL Documentation https://utplsql.org/