Oracle PL/SQL 最佳实践

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

1. 概述

PL/SQL 开发最佳实践[1]：

方面：

编码规范
性能优化
异常处理
安全
可维护性

2. 命名规范

2.1 前缀

类型	前缀	示例
过程	p_	p_hire_emp
函数	f_	f_get_count
包	pkg_	pkg_emp
变量	v_	v_count
常量	c_	c_min_sal
参数	p_	p_id
类型	t_	t_emp_rec
异常	e_	e_invalid
游标	c_	c_emp

2.2 命名

有意义
动词 + 名词
一致

3. 变量声明

3.1 %TYPE

v_name employees.name%TYPE;
v_salary employees.salary%TYPE;

3.2 %ROWTYPE

v_emp employees%ROWTYPE;

3.3 初始化

v_count NUMBER := 0;
v_name VARCHAR2(100) := NULL;

3.4 常量

c_max_salary CONSTANT NUMBER := 100000;

4. SQL 集成

4.1 减少 SQL 调用

-- 慢
FOR rec IN (SELECT * FROM t1) LOOP
  SELECT ... INTO ... FROM t2 WHERE ...;
END LOOP;

-- 快：JOIN
SELECT ... FROM t1, t2 WHERE t1.x = t2.x;

4.2 BULK COLLECT + FORALL

DECLARE
  TYPE id_array IS TABLE OF NUMBER;
  v_ids id_array;
BEGIN
  SELECT id BULK COLLECT INTO v_ids FROM employees WHERE ...;
  
  FORALL i IN 1..v_ids.COUNT
    UPDATE employees SET ... WHERE id = v_ids(i);
END;
/

详细见：Oracle BULK COLLECT 与 FORALL。

4.3 绑定变量

-- 推荐
EXECUTE IMMEDIATE 'SELECT * FROM t WHERE id = :id' USING v_id;

-- 避免
EXECUTE IMMEDIATE 'SELECT * FROM t WHERE id = ' || v_id;

5. 异常处理

5.1 具体

EXCEPTION
  WHEN NO_DATA_FOUND THEN ...
  WHEN TOO_MANY_ROWS THEN ...
  WHEN OTHERS THEN
    log_error(...);
    RAISE;
END;

5.2 RAISE_APPLICATION_ERROR

RAISE_APPLICATION_ERROR(-20001, 'Custom error: ' || SQLERRM, TRUE);

5.3 自治事务日志

CREATE OR REPLACE PROCEDURE log_error(p_msg VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO error_log (...) VALUES (...);
  COMMIT;
END;
/

详细见：Oracle PL/SQL 异常处理。

6. 性能优化

6.1 BULK

- BULK COLLECT
- FORALL
- LIMIT 1000-10000

6.2 Native Compilation

ALTER PROCEDURE my_proc COMPILE NATIVE;

6.3 Result Cache

CREATE OR REPLACE FUNCTION get_name(p_id NUMBER) 
RETURN VARCHAR2 RESULT_CACHE IS ...

详细见：Oracle PL/SQL 性能优化。

6.4 PLS_INTEGER

v_count PLS_INTEGER := 0;
-- 比 NUMBER 快

7. 安全

7.1 绑定变量

- 防 SQL 注入
- 性能

7.2 DBMS_ASSERT

v_sql := 'SELECT * FROM ' || DBMS_ASSERT.QUALIFIED_SQL_NAME(p_table);

7.3 权限

GRANT EXECUTE ON my_pkg TO app_user;
-- 最小权限

7.4 加密

DBMS_CRYPTO.ENCRYPT(...);

详细见：Oracle TDE 透明数据加密。

8. 模块化

8.1 包

CREATE OR REPLACE PACKAGE emp_pkg AS
  -- 接口
END;
/

CREATE OR REPLACE PACKAGE BODY emp_pkg AS
  -- 实现
END;
/

详细见：Oracle PL/SQL 包设计。

8.2 单一职责

- 一个过程/函数一个功能
- 小而专
- 可复用

9. 注释

9.1 头部

/**
 * Procedure: hire_employee
 * Purpose: 雇佣新员工
 * Author: Alice
 * Date: 2026-07-21
 * Params:
 *   p_name - 员工姓名
 *   p_salary - 薪资
 * Returns: 员工 ID
 * Throws: e_low_salary - 薪资过低
 */

9.2 关键

-- 重要业务规则
IF salary < c_min THEN ...

-- 性能优化
-- 使用 BULK COLLECT 减少 SQL 切换

10. 代码格式

10.1 缩进

IF condition THEN
    statement1;
    statement2;
END IF;

10.2 大小写

-- 关键字大写
SELECT * FROM employees WHERE id = 100;

-- 标识符小写
v_count NUMBER;

11. 测试

11.1 单元测试

-- DBMS_UT
-- utPLSQL

11.2 性能测试

SET TIMING ON;
EXEC my_proc;

11.3 边界

- NULL 输入
- 大数据
- 异常场景

12. 版本控制

12.1 提取

# DBMS_METADATA
SELECT DBMS_METADATA.GET_DDL('PACKAGE', 'EMP_PKG') FROM dual;

12.2 脚本

- 01_create_pkg.sql
- 02_alter_pkg.sql
- 03_drop_pkg.sql

13. 监控

13.1 编译错误

SHOW ERRORS;
SELECT * FROM user_errors WHERE name = 'MY_PKG';

13.2 失效对象

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

13.3 性能

SELECT sql_id, elapsed_time, plsql_exec_time
FROM v$sql
WHERE sql_text LIKE '%my_proc%';

14. 常见坑与排错

14.1 WHEN OTHERS THEN NULL

- 吞掉异常
- 难调试
- 记录并 RAISE

14.2 隐式转换

- 性能差
- 索引失效
- 显式类型

14.3 无限循环

- EXIT 条件
- 超时保护
- 监控

15. 最佳实践

命名规范：一致
%TYPE/%ROWTYPE：解耦
BULK + FORALL：性能
绑定变量：安全 + 性能
具体异常：清晰
包模块化：组织
注释完整：维护
测试覆盖：质量
版本控制：协作
监控告警：运维

16. 参考资料

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