DB / Oracle / SQL 开发

Oracle PL/SQL 包（Package）设计

2026.09.06

Oracle PL/SQL 包（Package）设计

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

1. 概述

PL/SQL 包（Package） 是相关对象（过程/函数/类型/变量）的集合[1]：

优势：

  • 模块化
  • 封装
  • 性能（首次加载后驻留）
  • 重载
  • 状态保持

2. 包结构

2.1 包规范（Specification）

CREATE OR REPLACE PACKAGE emp_pkg AS
  -- 公共类型
  TYPE emp_record IS RECORD (
    id NUMBER,
    name VARCHAR2(100),
    salary NUMBER
  );
  
  -- 公共变量
  g_max_salary CONSTANT NUMBER := 100000;
  g_dept_id NUMBER;
  
  -- 公共异常
  e_invalid_salary EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_invalid_salary, -20001);
  
  -- 公共游标
  CURSOR c_emp RETURN emp_record;
  
  -- 公共过程
  PROCEDURE update_salary(
    p_emp_id NUMBER,
    p_salary NUMBER
  );
  
  -- 公共函数
  FUNCTION get_total_salary(
    p_dept_id NUMBER
  ) RETURN NUMBER;
  
END emp_pkg;
/

2.2 包体（Body）

CREATE OR REPLACE PACKAGE BODY emp_pkg AS
  -- 私有变量
  v_count NUMBER;
  
  -- 私有函数
  FUNCTION validate_salary(p_salary NUMBER) RETURN BOOLEAN IS
  BEGIN
    RETURN p_salary > 0 AND p_salary <= g_max_salary;
  END;
  
  -- 游标实现
  CURSOR c_emp RETURN emp_record IS
    SELECT employee_id, last_name, salary FROM employees;
  
  -- 过程实现
  PROCEDURE update_salary(
    p_emp_id NUMBER,
    p_salary NUMBER
  ) AS
  BEGIN
    IF NOT validate_salary(p_salary) THEN
      RAISE e_invalid_salary;
    END IF;
    
    UPDATE employees 
    SET salary = p_salary 
    WHERE employee_id = p_emp_id;
  END;
  
  -- 函数实现
  FUNCTION get_total_salary(
    p_dept_id NUMBER
  ) RETURN NUMBER AS
    v_total NUMBER;
  BEGIN
    SELECT SUM(salary) INTO v_total
    FROM employees WHERE dept_id = p_dept_id;
    RETURN v_total;
  END;
  
BEGIN
  -- 初始化部分（首次调用执行）
  v_count := 0;
  g_dept_id := 10;
END emp_pkg;
/

3. 重载（Overloading）

3.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 calc_pkg;
/

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

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

4. 包状态

4.1 会话级状态

CREATE OR REPLACE PACKAGE state_pkg AS
  v_counter NUMBER := 0;
  PROCEDURE increment;
  PROCEDURE reset;
END state_pkg;
/

CREATE OR REPLACE PACKAGE BODY state_pkg AS
  PROCEDURE increment IS
  BEGIN
    v_counter := v_counter + 1;
  END;
  
  PROCEDURE reset IS
  BEGIN
    v_counter := 0;
  END;
END state_pkg;
/

-- 会话 1
EXEC state_pkg.increment;
EXEC state_pkg.increment;
-- v_counter = 2

-- 会话 2（独立）
EXEC state_pkg.increment;
-- v_counter = 1

4.2 PRAGMA SERIALLY_REUSABLE

-- 状态不保持（每次调用重置）
CREATE OR REPLACE PACKAGE temp_pkg AS
  PRAGMA SERIALLY_REUSABLE;
  v_counter NUMBER := 0;
END temp_pkg;
/

5. 包初始化

5.1 初始化部分

CREATE OR REPLACE PACKAGE BODY init_pkg AS
  ...
BEGIN
  -- 首次调用时执行
  DBMS_OUTPUT.PUT_LINE('Package initialized');
  v_count := 0;
  SELECT COUNT(*) INTO v_count FROM employees;
EXCEPTION
  WHEN OTHERS THEN
    NULL;
END init_pkg;
/

6. 包依赖

6.1 包依赖图

emp_pkg → employees 表
       → dept_pkg
              → departments 表

6.2 重编译

-- 编译包
ALTER PACKAGE emp_pkg COMPILE;
ALTER PACKAGE emp_pkg COMPILE SPECIFICATION;
ALTER PACKAGE emp_pkg COMPILE BODY;

6.3 依赖管理

-- 查看依赖
SELECT name, type, referenced_name, referenced_type
FROM user_dependencies
WHERE name = 'EMP_PKG';

7. 包权限

7.1 授予执行权限

GRANT EXECUTE ON emp_pkg TO hr_user;
GRANT EXECUTE ON emp_pkg TO PUBLIC;

7.2 同义词

CREATE PUBLIC SYNONYM emp_pkg FOR scott.emp_pkg;

8. 包设计原则

8.1 高内聚

  • 相关功能放一起
  • 单一职责
  • 清晰命名

8.2 低耦合

  • 最小化公共接口
  • 私有实现细节
  • 接口稳定

8.3 命名规范

包名：emp_pkg
过程：update_salary
函数：get_total_salary
变量：v_count / g_dept_id
常量：c_max
异常：e_invalid

9. 常用包

9.1 DBMS_OUTPUT

EXEC DBMS_OUTPUT.ENABLE;
EXEC DBMS_OUTPUT.PUT_LINE('Hello');

9.2 DBMS_SQL

-- 动态 SQL

9.3 DBMS_LOB

-- LOB 操作

9.4 DBMS_STATS

-- 统计信息
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMPLOYEES');

9.5 UTL_FILE

-- 文件操作

10. 常见坑与排错

10.1 ORA-06508: 包不存在

-- 1. 检查包是否存在
SELECT * FROM user_objects WHERE object_type='PACKAGE';

-- 2. 编译
ALTER PACKAGE emp_pkg COMPILE;

10.2 ORA-04068: 包状态丢弃

-- 包被重编译后会话状态失效
-- 重新调用即可

10.3 状态丢失

原因：包被重编译或数据库重启。

修复：

-- 使用 PRAGMA SERIALLY_REUSABLE（如适合）
-- 或持久化状态

10.4 编译错误

-- 查看错误
SHOW ERRORS PACKAGE emp_pkg;
SHOW ERRORS PACKAGE BODY emp_pkg;

-- 或
SELECT * FROM user_errors WHERE name='EMP_PKG';

11. 最佳实践

  1. 业务逻辑封装到包：模块化
  2. 规范/体分离：接口清晰
  3. 公共/私有分明：封装
  4. 合理重载：灵活
  5. 状态谨慎使用：可能丢失
  6. 包初始化轻量：避免启动慢
  7. 依赖最小化：降低耦合
  8. 异常处理：健壮性
  9. 定期重编译：避免失效
  10. 文档完整：易维护

12. 参考资料

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