Oracle PL/SQL 包设计与最佳实践

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

---

1. 概述

PL/SQL 包是模块化设计核心[1]：

详细见：Oracle PL/SQL 包详解。

---

2. 包结构

2.1 规范

CREATE OR REPLACE PACKAGE emp_pkg AS
  -- 类型
  TYPE emp_rec IS RECORD (
    id employees.id%TYPE,
    name employees.name%TYPE,
    salary employees.salary%TYPE
  );
  
  -- 异常
  e_emp_not_found EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_emp_not_found, -20001);
  
  -- 常量
  c_max_salary CONSTANT NUMBER := 100000;
  
  -- 游标
  CURSOR c_emp(p_dept_id NUMBER) IS
    SELECT * FROM employees WHERE dept_id = p_dept_id;
  
  -- 公共变量
  v_session_user VARCHAR2(30);
  
  -- 过程
  PROCEDURE hire_emp(
    p_name IN VARCHAR2,
    p_salary IN NUMBER,
    p_dept_id IN NUMBER
  );
  
  -- 函数
  FUNCTION get_emp_count(p_dept_id NUMBER) RETURN NUMBER;
END emp_pkg;
/

2.2 主体

CREATE OR REPLACE PACKAGE BODY emp_pkg AS
  -- 私有变量
  v_total_hired NUMBER := 0;
  
  -- 私有函数
  FUNCTION validate_salary(p_salary NUMBER) RETURN BOOLEAN IS
  BEGIN
    RETURN p_salary > 0 AND p_salary <= c_max_salary;
  END;
  
  -- 公共过程
  PROCEDURE hire_emp(
    p_name IN VARCHAR2,
    p_salary IN NUMBER,
    p_dept_id IN NUMBER
  ) IS
    v_id NUMBER;
  BEGIN
    IF NOT validate_salary(p_salary) THEN
      RAISE_APPLICATION_ERROR(-20002, 'Invalid salary');
    END IF;
    
    SELECT emp_seq.NEXTVAL INTO v_id FROM dual;
    INSERT INTO employees (id, name, salary, dept_id)
    VALUES (v_id, p_name, p_salary, p_dept_id);
    
    v_total_hired := v_total_hired + 1;
  END;
  
  -- 公共函数
  FUNCTION get_emp_count(p_dept_id NUMBER) RETURN NUMBER IS
    v_count NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_count FROM employees WHERE dept_id = p_dept_id;
    RETURN v_count;
  END;
  
  -- 初始化（可选）
BEGIN
  v_session_user := USER;
END emp_pkg;
/

---

3. 重载

CREATE OR REPLACE PACKAGE calc_pkg AS
  FUNCTION add(a NUMBER, b NUMBER) RETURN NUMBER;
  FUNCTION add(a VARCHAR2, b VARCHAR2) RETURN VARCHAR2;
  FUNCTION add(a DATE, b NUMBER) RETURN DATE;
END;
/

CREATE OR REPLACE PACKAGE BODY calc_pkg AS
  FUNCTION add(a NUMBER, b NUMBER) RETURN NUMBER IS
  BEGIN
    RETURN a + b;
  END;
  
  FUNCTION add(a VARCHAR2, b VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    RETURN a || b;
  END;
  
  FUNCTION add(a DATE, b NUMBER) RETURN DATE IS
  BEGIN
    RETURN a + b;
  END;
END;
/

---

4. 状态管理

4.1 会话状态

CREATE OR REPLACE PACKAGE session_pkg AS
  v_user_id NUMBER;
  v_login_time TIMESTAMP;
  
  PROCEDURE init(p_user_id NUMBER);
END;
/

CREATE OR REPLACE PACKAGE BODY session_pkg AS
  PROCEDURE init(p_user_id NUMBER) IS
  BEGIN
    v_user_id := p_user_id;
    v_login_time := SYSTIMESTAMP;
  END;
END;
/

4.2 SERIALLY_REUSABLE

CREATE OR REPLACE PACKAGE temp_pkg AS
  PRAGMA SERIALLY_REUSABLE;
  v_counter NUMBER := 0;
  
  PROCEDURE increment;
END;
/

-- 状态不跨调用保留

---

5. 包变量持久化

5.1 限制

- 会话级
- 重启丢失
- RAC 各节点独立

5.2 替代

- 表
- Global Temporary
- Context

---

6. PRAGMA

6.1 AUTONOMOUS_TRANSACTION

PROCEDURE log_msg(p_msg VARCHAR2) IS
  PRAGMA AUTONOMOUS_TRANSACTION;
BEGIN
  INSERT INTO log VALUES (p_msg, SYSTIMESTAMP);
  COMMIT;
END;

6.2 SERIALLY_REUSABLE

PRAGMA SERIALLY_REUSABLE;
-- 内存优化

---

7. 初始化块

CREATE OR REPLACE PACKAGE BODY pkg AS
  ...
BEGIN
  -- 首次引用时执行
  -- 一次/会话
  v_init_time := SYSDATE;
END pkg;
/

---

8. RESULT CACHE

8.1 函数

CREATE OR REPLACE PACKAGE lookup_pkg AS
  FUNCTION get_dept_name(p_id NUMBER) RETURN VARCHAR2
    RESULT_CACHE RELIES_ON (departments);
END;
/

CREATE OR REPLACE PACKAGE BODY lookup_pkg AS
  FUNCTION get_dept_name(p_id NUMBER) RETURN VARCHAR2
    RESULT_CACHE RELIES_ON (departments)
  IS
    v_name VARCHAR2(100);
  BEGIN
    SELECT dept_name INTO v_name FROM departments WHERE id = p_id;
    RETURN v_name;
  END;
END;
/

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

---

9. ACCESSIBLE BY（12c+）

CREATE OR REPLACE PACKAGE emp_pkg 
  ACCESSIBLE BY (PROCEDURE hr_proc, FUNCTION hr_fn) 
AS
  ...
END;
-- 仅指定单元可访问

---

10. 包权限

10.1 执行

GRANT EXECUTE ON emp_pkg TO hr_role;

10.2 AUTHID

CREATE OR REPLACE PACKAGE emp_pkg 
  AUTHID DEFINER        -- 默认
AS ...

CREATE OR REPLACE PACKAGE emp_pkg 
  AUTHID CURRENT_USER   -- 调用者
AS ...

详细见：Oracle 存储过程与函数详解。

---

11. 编译

11.1 重新编译

ALTER PACKAGE emp_pkg COMPILE;
ALTER PACKAGE emp_pkg COMPILE SPECIFICATION;
ALTER PACKAGE emp_pkg COMPILE BODY;

11.2 错误

SHOW ERRORS PACKAGE emp_pkg;
SHOW ERRORS PACKAGE BODY emp_pkg;

SELECT name, type, line, position, text 
FROM user_errors 
WHERE name = 'EMP_PKG';

---

12. 查看

12.1 源代码

SELECT name, type, line, text 
FROM user_source 
WHERE name = 'EMP_PKG' 
ORDER BY line;

12.2 对象

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

12.3 依赖

SELECT name, type, referenced_name, referenced_type
FROM user_dependencies
WHERE name = 'EMP_PKG';

---

13. 包设计原则

13.1 高内聚

- 相关功能
- 业务模块
- 一致

13.2 低耦合

- 最小依赖
- 接口清晰
- 独立

13.3 接口稳定

- 规范少改
- 主体可变
- 兼容

13.4 命名

- pkg_xxx
- 业务前缀
- 清晰

---

14. 常见包

14.1 DBMS_OUTPUT

DBMS_OUTPUT.PUT_LINE(...);
DBMS_OUTPUT.ENABLE;

14.2 DBMS_SQL

-- 动态 SQL

详细见：Oracle PL/SQL 动态 SQL 详解。

14.3 UTL_FILE

-- 文件操作

14.4 DBMS_LOB

-- LOB 操作

14.5 DBMS_STATS

-- 统计

14.6 DBMS_SCHEDULER

-- 调度

---

15. 常见坑与排错

15.1 状态丢失

- 重启
- RAC
- SERIALLY_REUSABLE

15.2 失效

- 依赖变化
- 重新编译
- INVALID

15.3 循环依赖

- A 依赖 B
- B 依赖 A
- 重构

---

16. 最佳实践

1. 模块化：高内聚
2. 接口稳定：规范
3. 重载：灵活
4. 私有：隐藏
5. 常量：定义
6. 异常：集中
7. RESULT_CACHE：性能
8. AUTHID：权限
9. 初始化：默认
10. 文档化：注释

---

17. 参考资料

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