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 BODY emp_pkg AS
  v_init_time TIMESTAMP;
  v_user VARCHAR2(30);
  
  PROCEDURE hire(...) IS ... BEGIN ... END;
  FUNCTION get_count(...) RETURN NUMBER IS ... BEGIN ... END;

BEGIN
  -- 首次引用时执行
  -- 一次/会话
  v_init_time := SYSTIMESTAMP;
  v_user := USER;
  
  DBMS_OUTPUT.PUT_LINE('Package initialized');
END emp_pkg;
/

2.2 用途

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

2.3 示例

CREATE OR REPLACE PACKAGE config_pkg AS
  FUNCTION get_param(p_name VARCHAR2) RETURN VARCHAR2;
END;
/

CREATE OR REPLACE PACKAGE BODY config_pkg AS
  TYPE param_tab IS TABLE OF VARCHAR2(4000) INDEX BY VARCHAR2(100);
  v_params param_tab;
  
  FUNCTION get_param(p_name VARCHAR2) RETURN VARCHAR2 IS
  BEGIN
    IF v_params.EXISTS(p_name) THEN
      RETURN v_params(p_name);
    END IF;
    RETURN NULL;
  END;

BEGIN
  -- 加载配置
  FOR rec IN (SELECT name, value FROM app_config) LOOP
    v_params(rec.name) := rec.value;
  END LOOP;
END config_pkg;
/

3. 会话状态

3.1 变量

CREATE OR REPLACE PACKAGE session_pkg AS
  v_user_id NUMBER;
  v_login_time TIMESTAMP;
  v_session_id VARCHAR2(50);
  
  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;
    v_session_id := SYS_GUID();
  END;
END;
/

3.2 持久性

- 会话级
- 跨调用保留
- 重启丢失
- RAC 各节点独立

4. SERIALLY_REUSABLE

4.1 基本

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

CREATE OR REPLACE PACKAGE BODY temp_pkg AS
  PRAGMA SERIALLY_REUSABLE;
  
  PROCEDURE increment IS
  BEGIN
    v_counter := v_counter + 1;
  END;
  
  FUNCTION get_counter RETURN NUMBER IS
  BEGIN
    RETURN v_counter;
  END;
END;
/

-- 状态不跨调用保留
EXEC temp_pkg.increment;
EXEC temp_pkg.increment;
EXEC DBMS_OUTPUT.PUT_LINE(temp_pkg.get_counter);  -- 0

4.2 优势

- 内存优化
- 大包
- 临时

4.3 限制

- 状态不保留
- 不适合缓存

5. PRAGMA

5.1 AUTONOMOUS_TRANSACTION

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

5.2 EXCEPTION_INIT

DECLARE
  e_fk EXCEPTION;
  PRAGMA EXCEPTION_INIT(e_fk, -02292);
BEGIN
  ...
EXCEPTION
  WHEN e_fk THEN ...
END;

详细见：Oracle PL/SQL 异常处理详解。

5.3 INLINE

CREATE OR REPLACE PROCEDURE caller IS
  PRAGMA INLINE(small_proc, 'YES');
BEGIN
  small_proc;
END;

6. 包状态重置

6.1 重新编译

ALTER PACKAGE emp_pkg COMPILE;
-- 重置包状态

6.2 DBMS_SESSION

EXEC DBMS_SESSION.MODIFY_PACKAGE_STATE(DBMS_SESSION.REINITIALIZE);
-- 或
EXEC DBMS_SESSION.FREE_UNUSED_USER_MEMORY;

7. 跨包状态

7.1 共享

CREATE OR REPLACE PACKAGE shared_pkg AS
  v_global_count NUMBER := 0;
END;
/

CREATE OR REPLACE PACKAGE other_pkg AS
  PROCEDURE increment;
END;
/

CREATE OR REPLACE PACKAGE BODY other_pkg AS
  PROCEDURE increment IS
  begin
    shared_pkg.v_global_count := shared_pkg.v_global_count + 1;
  END;
END;
/

7.2 RAC

- 各节点独立
- 不共享
- 同步需表/队列

8. 持久化

8.1 表

CREATE TABLE pkg_state (
  session_id VARCHAR2(50),
  pkg_name VARCHAR2(50),
  state CLOB
);

-- 保存
INSERT INTO pkg_state VALUES (..., 'EMP_PKG', ...);

-- 加载
SELECT state INTO ... FROM pkg_state WHERE ...;

8.2 Context

CREATE OR REPLACE CONTEXT app_ctx USING ctx_pkg;

CREATE OR REPLACE PACKAGE ctx_pkg AS
  PROCEDURE set(p_name VARCHAR2, p_value VARCHAR2);
END;
/

CREATE OR REPLACE PACKAGE BODY ctx_pkg AS
  PROCEDURE set(p_name VARCHAR2, p_value VARCHAR2) IS
  BEGIN
    DBMS_SESSION.SET_CONTEXT('app_ctx', p_name, p_value);
  END;
END;
/

-- 使用
SELECT SYS_CONTEXT('app_ctx', 'user_id') FROM dual;

详细见：Oracle PL/SQL 安全编程详解。

9. 全局临时表

9.1 会话级

CREATE GLOBAL TEMPORARY TABLE temp_state (
  key VARCHAR2(50),
  value VARCHAR2(4000)
) ON COMMIT PRESERVE ROWS;

-- 使用
INSERT INTO temp_state VALUES ('user_id', '100');
SELECT value INTO v_user FROM temp_state WHERE key = 'user_id';

10. 缓存

10.1 包级

CREATE OR REPLACE PACKAGE cache_pkg AS
  TYPE dept_cache IS TABLE OF departments%ROWTYPE INDEX BY PLS_INTEGER;
  v_dept_cache dept_cache;
  
  FUNCTION get_dept(p_id NUMBER) RETURN departments%ROWTYPE;
END;
/

CREATE OR REPLACE PACKAGE BODY cache_pkg AS
  FUNCTION get_dept(p_id NUMBER) RETURN departments%ROWTYPE IS
  BEGIN
    IF NOT v_dept_cache.EXISTS(p_id) THEN
      SELECT * INTO v_dept_cache(p_id) FROM departments WHERE id = p_id;
    END IF;
    RETURN v_dept_cache(p_id);
  END;
END;
/

10.2 RESULT_CACHE

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

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

11. 应用场景

11.1 会话管理

CREATE OR REPLACE PACKAGE session_mgr AS
  PROCEDURE login(p_user VARCHAR2);
  PROCEDURE logout;
  FUNCTION is_logged_in RETURN BOOLEAN;
END;
/

11.2 配置

CREATE OR REPLACE PACKAGE config_pkg AS
  FUNCTION get(p_name VARCHAR2) RETURN VARCHAR2;
END;
/

11.3 计数器

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

11.4 缓存

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

12. 性能

12.1 内存

- 包状态占 PGA
- 大集合注意
- SERIALLY_REUSABLE 优化

12.2 启动

- 首次引用初始化
- 一次/会话
- 简洁

13. 常见坑与排错

13.1 状态丢失

- 重启
- RAC
- SERIALLY_REUSABLE

13.2 内存

- 大集合
- OOM
- LIMIT

13.3 并发

- 会话独立
- RAC
- 同步

14. 最佳实践

初始化块：一次
会话状态：登录
SERIALLY_REUSABLE：临时
RESULT_CACHE：缓存
持久化：表
Context：共享
GTT：临时
重置：MODIFY_PACKAGE_STATE
监控：内存
文档：说明

15. 参考资料

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