Oracle PL/SQL 包重载与封装详解

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

1. 概述

PL/SQL 包重载与封装是面向对象特性[1]：

2. 重载

2.1 基本

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;
/

-- 调用
SELECT calc_pkg.add(1, 2) FROM dual;
SELECT calc_pkg.add('Hello ', 'World') FROM dual;
SELECT calc_pkg.add(DATE '2025-01-01', 7) FROM dual;

2.2 规则

- 参数数量不同
- 参数类型不同
- 返回类型不区分
- 同一包内

2.3 示例

CREATE OR REPLACE PACKAGE emp_pkg AS
  -- 重载
  PROCEDURE hire(p_name VARCHAR2, p_salary NUMBER);
  PROCEDURE hire(p_name VARCHAR2, p_salary NUMBER, p_dept_id NUMBER);
  PROCEDURE hire(p_name VARCHAR2, p_salary NUMBER, p_dept_id NUMBER, p_mgr_id NUMBER);
  
  FUNCTION get_count RETURN NUMBER;
  FUNCTION get_count(p_dept_id NUMBER) RETURN NUMBER;
  FUNCTION get_count(p_dept_id NUMBER, p_status VARCHAR2) RETURN NUMBER;
END;
/

3. 封装

3.1 私有

CREATE OR REPLACE PACKAGE emp_pkg AS
  -- 公共
  PROCEDURE hire_emp(p_name VARCHAR2, p_salary NUMBER);
  FUNCTION get_count(p_dept_id NUMBER) RETURN NUMBER;
END;
/

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 <= 100000;
  END;
  
  -- 私有过程
  PROCEDURE log_hire(p_name VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO hire_log (name, hire_time) VALUES (p_name, SYSTIMESTAMP);
    COMMIT;
  END;
  
  -- 公共
  PROCEDURE hire_emp(p_name VARCHAR2, p_salary NUMBER) IS
  BEGIN
    IF NOT validate_salary(p_salary) THEN
      RAISE_APPLICATION_ERROR(-20001, 'Invalid salary');
    END IF;
    
    INSERT INTO employees (name, salary) VALUES (p_name, p_salary);
    log_hire(p_name);
    v_total_hired := v_total_hired + 1;
  END;
  
  FUNCTION get_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;
END;
/

3.2 优势

- 信息隐藏
- 实现细节隐藏
- 接口稳定
- 维护性

4. 状态管理

4.1 会话状态

CREATE OR REPLACE PACKAGE session_pkg AS
  v_user_id NUMBER;
  v_login_time TIMESTAMP;
  
  PROCEDURE init(p_user_id NUMBER);
  FUNCTION get_user_id RETURN 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;
  
  FUNCTION get_user_id RETURN NUMBER IS
  BEGIN
    RETURN v_user_id;
  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 初始化块

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

5.2 用途

- 默认值
- 加载配置
- 验证
- 一次性

6. 包依赖

6.1 依赖

CREATE OR REPLACE PACKAGE pkg_a AS
  FUNCTION get_data RETURN VARCHAR2;
END;
/

CREATE OR REPLACE PACKAGE pkg_b AS
  PROCEDURE process;
END;
/

CREATE OR REPLACE PACKAGE BODY pkg_b AS
  PROCEDURE process IS
    v_data VARCHAR2(100);
  BEGIN
    v_data := pkg_a.get_data;  -- 依赖
    ...
  END;
END;
/

6.2 循环

- A 依赖 B
- B 依赖 A
- 重构避免

7. ACCESSIBLE BY（12c+）

7.1 限制访问

CREATE OR REPLACE PACKAGE emp_pkg
  ACCESSIBLE BY (PROCEDURE hr_proc, FUNCTION hr_fn, PACKAGE hr_pkg)
AS
  ...
END;
/

7.2 单元访问

CREATE OR REPLACE PACKAGE emp_pkg AS
  PROCEDURE public_proc;
  
  PROCEDURE private_proc
    ACCESSIBLE BY (PROCEDURE hr_proc);
END;
/

8. PRAGMA

8.1 AUTONOMOUS_TRANSACTION

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

8.2 SERIALLY_REUSABLE

PRAGMA SERIALLY_REUSABLE;

9. 编译

9.1 重新编译

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

9.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';

10. 权限

10.1 执行

GRANT EXECUTE ON emp_pkg TO hr_role;

10.2 AUTHID

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

-- CURRENT_USER
CREATE OR REPLACE PACKAGE emp_pkg AUTHID CURRENT_USER AS ...

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

11. 查看源码

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

12. 设计原则

12.1 高内聚

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

12.2 低耦合

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

12.3 接口稳定

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

12.4 单一职责

- 一个包一个职责
- 不臃肿
- 清晰

13. 应用场景

13.1 业务模块

CREATE OR REPLACE PACKAGE hr_pkg AS
  PROCEDURE hire_emp(...);
  PROCEDURE fire_emp(...);
  PROCEDURE promote_emp(...);
  FUNCTION get_emp(...) RETURN ...;
END;
/

13.2 工具包

CREATE OR REPLACE PACKAGE util_pkg AS
  FUNCTION format_date(...) RETURN ...;
  FUNCTION validate_email(...) RETURN ...;
  FUNCTION generate_id(...) RETURN ...;
END;
/

13.3 数据访问层

CREATE OR REPLACE PACKAGE emp_dao AS
  PROCEDURE insert_emp(...);
  PROCEDURE update_emp(...);
  PROCEDURE delete_emp(...);
  FUNCTION select_emp(...) RETURN SYS_REFCURSOR;
END;
/

14. 常见坑与排错

14.1 状态丢失

- 重启
- RAC
- SERIALLY_REUSABLE

14.2 失效

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

14.3 循环依赖

- 重构
- 接口

14.4 重载冲突

- 参数相同
- 区分

15. 最佳实践

模块化：高内聚
封装：隐藏
重载：灵活
接口稳定：规范
单一职责：清晰
状态管理：会话
AUTHID：权限
ACCESSIBLE BY：限制
PRAGMA：特性
测试：验证

16. 参考资料

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