Oracle PL/SQL 包设计与开发

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

1. 概述

包是 PL/SQL 模块化单元[1]：

组成：

包规范（Spec）：声明
包体（Body）：实现

2. 包规范

CREATE OR REPLACE PACKAGE emp_pkg AS
  -- 公共类型
  TYPE emp_rec IS RECORD (
    id employees.id%TYPE,
    name employees.name%TYPE,
    salary employees.salary%TYPE
  );
  
  -- 公共常量
  c_min_salary CONSTANT NUMBER := 5000;
  
  -- 公共变量
  g_dept_id NUMBER;
  
  -- 公共异常
  e_low_salary EXCEPTION;
  
  -- 公共游标
  CURSOR c_emp RETURN emp_rec;
  
  -- 公共过程
  PROCEDURE hire_emp(p_name VARCHAR2, p_salary NUMBER);
  PROCEDURE fire_emp(p_id NUMBER);
  
  -- 公共函数
  FUNCTION get_emp(p_id NUMBER) RETURN emp_rec;
  FUNCTION get_count RETURN NUMBER;
END emp_pkg;
/

3. 包体

CREATE OR REPLACE PACKAGE BODY emp_pkg AS
  -- 私有变量
  v_total_hired NUMBER := 0;
  
  -- 私有函数
  FUNCTION is_valid_salary(p_salary NUMBER) RETURN BOOLEAN IS
  BEGIN
    RETURN p_salary >= c_min_salary;
  END;
  
  -- 公共游标实现
  CURSOR c_emp RETURN emp_rec IS
    SELECT id, name, salary FROM employees;
  
  -- 公共过程实现
  PROCEDURE hire_emp(p_name VARCHAR2, p_salary NUMBER) IS
    v_id NUMBER;
  BEGIN
    IF NOT is_valid_salary(p_salary) THEN
      RAISE e_low_salary;
    END IF;
    
    INSERT INTO employees (id, name, salary) 
    VALUES (emp_seq.NEXTVAL, p_name, p_salary)
    RETURNING id INTO v_id;
    
    v_total_hired := v_total_hired + 1;
  END;
  
  PROCEDURE fire_emp(p_id NUMBER) IS
  BEGIN
    DELETE FROM employees WHERE id = p_id;
    IF SQL%ROWCOUNT = 0 THEN
      RAISE_APPLICATION_ERROR(-20001, 'Employee not found');
    END IF;
  END;
  
  -- 公共函数实现
  FUNCTION get_emp(p_id NUMBER) RETURN emp_rec IS
    v_rec emp_rec;
  BEGIN
    SELECT id, name, salary INTO v_rec
    FROM employees WHERE id = p_id;
    RETURN v_rec;
  EXCEPTION
    WHEN NO_DATA_FOUND THEN
      RETURN NULL;
  END;
  
  FUNCTION get_count RETURN NUMBER IS
    v_count NUMBER;
  BEGIN
    SELECT COUNT(*) INTO v_count FROM employees;
    RETURN v_count;
  END;
  
  -- 初始化块（可选）
BEGIN
  SELECT dept_id INTO g_dept_id FROM ... WHERE ...;
END emp_pkg;
/

4. 包调用

4.1 过程

EXEC emp_pkg.hire_emp('Alice', 8000);
EXEC emp_pkg.fire_emp(100);

4.2 函数

DECLARE
  v_rec emp_pkg.emp_rec;
BEGIN
  v_rec := emp_pkg.get_emp(100);
  DBMS_OUTPUT.PUT_LINE(v_rec.name);
END;
/

SELECT emp_pkg.get_count FROM dual;

4.3 公共变量

BEGIN
  emp_pkg.g_dept_id := 10;
END;
/

5. 重载

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

6. 包状态

6.1 持久状态

- 公共变量在会话内持久
- UGA 存储
- 会话结束清理

6.2 重置

EXEC DBMS_SESSION.MODIFY_PACKAGE_STATE(DBMS_SESSION.REINITIALIZE);

6.3 SERIALLY_REUSABLE

CREATE OR REPLACE PACKAGE emp_pkg IS
  PRAGMA SERIALLY_REUSABLE;
  ...
END;
/

-- 状态不持久，每次调用后释放

7. 包编译

7.1 编译

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

7.2 错误

SHOW ERRORS PACKAGE emp_pkg;
SHOW ERRORS PACKAGE BODY emp_pkg;

SELECT * FROM user_errors WHERE name = 'EMP_PKG';

8. 包依赖

8.1 查看

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

8.2 失效

- 依赖对象修改
- 包自动失效
- 下次调用重新编译

9. 包权限

GRANT EXECUTE ON emp_pkg TO scott;
GRANT EXECUTE ON emp_pkg TO public;

-- 调用
EXEC scott.emp_pkg.hire_emp(...);

10. 包信息

SELECT object_name, object_type, status 
FROM user_objects 
WHERE object_type LIKE 'PACKAGE%';

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

11. AUTHID

11.1 DEFINER（默认）

CREATE OR REPLACE PACKAGE emp_pkg AUTHID DEFINER AS
  ...
END;
/
-- 以包所有者权限执行

11.2 CURRENT_USER

CREATE OR REPLACE PACKAGE emp_pkg AUTHID CURRENT_USER AS
  ...
END;
/
-- 以调用者权限执行

12. 包设计原则

12.1 模块化

- 相关功能聚集
- 单一职责
- 清晰接口

12.2 封装

- 私有变量/过程
- 公共接口稳定
- 实现可变

12.3 命名

- 包名：模块名_pkg
- 过程：动词_名词
- 函数：get_/is_/has_

13. 常用包

13.1 DBMS_OUTPUT

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

13.2 DBMS_STATS

DBMS_STATS.GATHER_TABLE_STATS(...);

13.3 DBMS_SCHEDULER

DBMS_SCHEDULER.CREATE_JOB(...);

13.4 UTL_FILE

UTL_FILE.FOPEN(...);
UTL_FILE.PUT_LINE(...);

13.5 UTL_MAIL

UTL_MAIL.SEND(...);

14. 常见坑与排错

14.1 状态丢失

- 包重编译
- 公共变量重置
- 注意依赖

14.2 ORA-04068

- 包被修改
- 调用方需重新连接

14.3 循环依赖

- 包 A 依赖包 B
- 包 B 依赖包 A
- 重构分离

15. 最佳实践

规范 + 体分离：清晰
公共/私有分离：封装
重载便利：多态
避免大量公共变量：状态
SERIALLY_REUSABLE：内存
AUTHID 选择：权限
异常处理：完整
权限授予：控制
文档化：接口
测试：质量

16. 参考资料

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