Oracle PL/SQL 包详解

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

---

1. 概述

PL/SQL 包是相关对象的封装[1]：

组成：

• 规范（Specification）
• 主体（Body）

详细见：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
  );
  
  TYPE emp_tab IS TABLE OF emp_rec INDEX BY PLS_INTEGER;
  
  -- 公共变量
  g_max_salary NUMBER := 100000;
  
  -- 公共异常
  e_salary_too_high EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_salary_too_high, -20001);
  
  -- 公共游标
  CURSOR c_active_emp RETURN emp_rec;
  
  -- 公共过程
  PROCEDURE hire_emp(
    p_name IN VARCHAR2,
    p_salary IN NUMBER,
    p_dept_id IN NUMBER
  );
  
  -- 公共函数
  FUNCTION get_emp_count(p_dept_id IN NUMBER) RETURN NUMBER;
END emp_pkg;
/

2.2 主体

CREATE OR REPLACE PACKAGE BODY emp_pkg AS
  -- 私有变量
  v_total_emp NUMBER;
  
  -- 私有函数
  FUNCTION validate_salary(p_salary NUMBER) RETURN BOOLEAN IS
  BEGIN
    RETURN p_salary > 0 AND p_salary < g_max_salary;
  END;
  
  -- 游标实现
  CURSOR c_active_emp RETURN emp_rec IS
    SELECT id, name, salary FROM employees WHERE status = 'ACTIVE';
  
  -- 过程实现
  PROCEDURE hire_emp(
    p_name IN VARCHAR2,
    p_salary IN NUMBER,
    p_dept_id IN NUMBER
  ) IS
  BEGIN
    IF NOT validate_salary(p_salary) THEN
      RAISE_APPLICATION_ERROR(-20001, 'Salary invalid');
    END IF;
    
    INSERT INTO employees (id, name, salary, dept_id)
    VALUES (emp_seq.NEXTVAL, p_name, p_salary, p_dept_id);
    
    v_total_emp := v_total_emp + 1;
  END;
  
  -- 函数实现
  FUNCTION get_emp_count(p_dept_id IN 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
    SELECT COUNT(*) INTO v_total_emp FROM employees;
    DBMS_OUTPUT.PUT_LINE('Package initialized: ' || v_total_emp || ' employees');
END emp_pkg;
/

---

3. 调用

-- 调用过程
EXEC emp_pkg.hire_emp('Alice', 5000, 10);

-- 调用函数
SELECT emp_pkg.get_emp_count(10) FROM dual;

-- 使用变量
BEGIN
  DBMS_OUTPUT.PUT_LINE('Max salary: ' || emp_pkg.g_max_salary);
END;
/

-- 使用游标
DECLARE
  v_emp emp_pkg.emp_rec;
BEGIN
  OPEN emp_pkg.c_active_emp;
  LOOP
    FETCH emp_pkg.c_active_emp INTO v_emp;
    EXIT WHEN emp_pkg.c_active_emp%NOTFOUND;
    DBMS_OUTPUT.PUT_LINE(v_emp.name);
  END LOOP;
  CLOSE emp_pkg.c_active_emp;
END;
/

---

4. 重载

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(SYSDATE, 7) FROM dual;

---

5. 状态

5.1 会话级

CREATE OR REPLACE PACKAGE counter_pkg AS
  PROCEDURE increment;
  FUNCTION get_count RETURN NUMBER;
END;
/

CREATE OR REPLACE PACKAGE BODY counter_pkg AS
  v_count NUMBER := 0;  -- 会话级状态
  
  PROCEDURE increment IS
  BEGIN
    v_count := v_count + 1;
  END;
  
  FUNCTION get_count RETURN NUMBER IS
  BEGIN
    RETURN v_count;
  END;
END;
/

EXEC counter_pkg.increment;
EXEC counter_pkg.increment;
SELECT counter_pkg.get_count FROM dual;  -- 2

5.2 SERIALLY_REUSABLE

CREATE OR REPLACE PACKAGE counter_pkg AS
  PRAGMA SERIALLY_REUSABLE;
  PROCEDURE increment;
  FUNCTION get_count RETURN NUMBER;
END;
/
-- 状态仅在调用期间保持，不跨调用

---

6. 自治事务

CREATE OR REPLACE PACKAGE log_pkg AS
  PROCEDURE log_msg(p_msg VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY log_pkg AS
  PROCEDURE log_msg(p_msg VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO app_log (msg, log_time) VALUES (p_msg, SYSTIMESTAMP);
    COMMIT;
  END;
END;
/

-- 调用
BEGIN
  INSERT INTO t VALUES (1);
  log_pkg.log_msg('Inserted 1');
  ROLLBACK;  -- 主事务回滚，日志保留
END;
/

---

7. 包编译

7.1 重编译

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

7.2 依赖

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

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

---

8. 包权限

GRANT EXECUTE ON emp_pkg TO hr_app;

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

-- 同义词
CREATE SYNONYM emp_pkg FOR scott.emp_pkg;

---

9. 内置包

9.1 常用

包	用途
DBMS_OUTPUT	输出
DBMS_SQL	动态 SQL
DBMS_STATS	统计信息
DBMS_LOB	LOB 操作
DBMS_JOB / DBMS_SCHEDULER	作业
DBMS_LOCK	锁
DBMS_PIPE	管道
DBMS_ALERT	告警
DBMS_AQ	队列
UTL_FILE	文件
UTL_MAIL	邮件
UTL_HTTP	HTTP
DBMS_CRYPTO	加密
DBMS_RANDOM	随机
DBMS_METADATA	元数据

9.2 示例

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

-- DBMS_STATS
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMPLOYEES');

-- UTL_FILE
DECLARE
  f UTL_FILE.FILE_TYPE;
BEGIN
  f := UTL_FILE.FOPEN('LOG_DIR', 'test.log', 'W');
  UTL_FILE.PUT_LINE(f, 'Hello');
  UTL_FILE.FCLOSE(f);
END;
/

---

10. 包设计原则

10.1 模块化

- 相关功能
- 单一职责
- 公共/私有分离

10.2 接口

- 规范清晰
- 文档注释
- 参数合理

10.3 状态

- 谨慎使用全局
- SERIALLY_REUSABLE
- 自治事务

---

11. 性能

11.1 首次加载

- 第一次调用加载到内存
- SGA 共享

11.2 重载

- 依赖对象变更
- INVALID 状态
- 自动重编译

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

---

12. 调试

12.1 DBMS_OUTPUT

CREATE OR REPLACE PACKAGE BODY emp_pkg AS
  PROCEDURE hire_emp(...) IS
  BEGIN
    DBMS_OUTPUT.PUT_LINE('Hiring ' || p_name);
    ...
  END;
END;
/

12.2 日志

CREATE OR REPLACE PACKAGE debug_pkg AS
  PROCEDURE log(p_proc VARCHAR2, p_msg VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY debug_pkg AS
  PROCEDURE log(p_proc VARCHAR2, p_msg VARCHAR2) IS
    PRAGMA AUTONOMOUS_TRANSACTION;
  BEGIN
    INSERT INTO debug_log (proc_name, msg, log_time)
    VALUES (p_proc, p_msg, SYSTIMESTAMP);
    COMMIT;
  END;
END;
/

12.3 DBMS_DEBUG

-- 调试 API
EXEC DBMS_DEBUG.INITIALIZE;

---

13. 常见坑与排错

13.1 ORA-04063

- 包无效
- ALTER PACKAGE COMPILE

13.2 ORA-06508

- 包未找到
- 状态

13.3 状态丢失

- 包重编译
- 状态重置

---

14. 最佳实践

1. 规范/主体分离：清晰
2. 公共/私有：封装
3. 重载：灵活
4. 注释：文档
5. 自治事务：日志
6. SERIALLY_REUSABLE：状态
7. 权限管理：安全
8. 同义词：透明
9. 依赖检查：维护
10. 测试：完整

---

15. 参考资料

[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