DB / Oracle / SQL 开发

Oracle PL/SQL 内置包大全

2026.09.06

Oracle PL/SQL 内置包大全

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

1. 概述

Oracle 提供丰富的 PL/SQL 内置包[1]：

2. DBMS_OUTPUT

BEGIN
  DBMS_OUTPUT.ENABLE;
  DBMS_OUTPUT.PUT_LINE('Hello');
  DBMS_OUTPUT.PUT('No newline');
  DBMS_OUTPUT.NEW_LINE;
  DBMS_OUTPUT.PUT_LINE('Value: ' || 100);
END;
/

SET SERVEROUTPUT ON SIZE UNLIMITED;

3. DBMS_SQL

DECLARE
  v_cur INTEGER;
  v_count INTEGER;
BEGIN
  v_cur := DBMS_SQL.OPEN_CURSOR;
  DBMS_SQL.PARSE(v_cur, 'SELECT COUNT(*) FROM employees', DBMS_SQL.NATIVE);
  DBMS_SQL.DEFINE_COLUMN(v_cur, 1, v_count);
  v_count := DBMS_SQL.EXECUTE(v_cur);
  IF DBMS_SQL.FETCH_ROWS(v_cur) > 0 THEN
    DBMS_SQL.COLUMN_VALUE(v_cur, 1, v_count);
  END IF;
  DBMS_SQL.CLOSE_CURSOR(v_cur);
END;
/

详细见：Oracle PL/SQL 动态 SQL 详解。

4. DBMS_LOB

DECLARE
  v_clob CLOB;
BEGIN
  DBMS_LOB.CREATETEMPORARY(v_clob, TRUE);
  DBMS_LOB.WRITEAPPEND(v_clob, 5, 'Hello');
  DBMS_LOB.APPEND(v_clob, ' World');
  
  DBMS_OUTPUT.PUT_LINE('Length: ' || DBMS_LOB.GETLENGTH(v_clob));
  DBMS_OUTPUT.PUT_LINE('Substr: ' || DBMS_LOB.SUBSTR(v_clob, 5, 1));
  
  DBMS_LOB.FREETEMPORARY(v_clob);
END;
/

详细见：Oracle 数据类型详解。

5. UTL_FILE

DECLARE
  v_file UTL_FILE.FILE_TYPE;
  v_line VARCHAR2(4000);
BEGIN
  -- 目录
  -- CREATE DIRECTORY data_dir AS '/tmp';
  
  v_file := UTL_FILE.FOPEN('DATA_DIR', 'test.txt', 'R');
  
  LOOP
    UTL_FILE.GET_LINE(v_file, v_line);
    DBMS_OUTPUT.PUT_LINE(v_line);
  END LOOP;
EXCEPTION
  WHEN NO_DATA_FOUND THEN
    UTL_FILE.FCLOSE(v_file);
END;
/

-- 写
DECLARE
  v_file UTL_FILE.FILE_TYPE;
BEGIN
  v_file := UTL_FILE.FOPEN('DATA_DIR', 'output.txt', 'W');
  UTL_FILE.PUT_LINE(v_file, 'Hello World');
  UTL_FILE.FCLOSE(v_file);
END;
/

6. DBMS_STATS

-- 表统计
EXEC DBMS_STATS.GATHER_TABLE_STATS('SCOTT', 'EMPLOYEES', cascade => TRUE);

-- Schema
EXEC DBMS_STATS.GATHER_SCHEMA_STATS('SCOTT');

-- 系统
EXEC DBMS_STATS.GATHER_SYSTEM_STATS;

-- 数据库
EXEC DBMS_STATS.GATHER_DATABASE_STATS;

-- 设置
EXEC DBMS_STATS.SET_TABLE_PREFS('SCOTT', 'EMPLOYEES', 'ESTIMATE_PERCENT', '100');

详细见：Oracle 直方图与统计信息。

7. DBMS_SCHEDULER

-- 创建作业
BEGIN
  DBMS_SCHEDULER.CREATE_JOB(
    job_name => 'daily_backup',
    job_type => 'PLSQL_BLOCK',
    job_action => 'BEGIN backup_db; END;',
    start_date => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2',
    enabled => TRUE
  );
END;
/

-- 程序
EXEC DBMS_SCHEDULER.CREATE_PROGRAM(...);

-- 调度
EXEC DBMS_SCHEDULER.CREATE_SCHEDULE(...);

-- 运行
EXEC DBMS_SCHEDULER.RUN_JOB('daily_backup');

-- 禁用/启用
EXEC DBMS_SCHEDULER.DISABLE('daily_backup');
EXEC DBMS_SCHEDULER.ENABLE('daily_backup');

-- 删除
EXEC DBMS_SCHEDULER.DROP_JOB('daily_backup');

8. DBMS_JOB（旧）

DECLARE
  v_job NUMBER;
BEGIN
  DBMS_JOB.SUBMIT(
    job => v_job,
    what => 'BEGIN backup_db; END;',
    next_date => SYSDATE,
    interval => 'SYSDATE + 1'
  );
  COMMIT;
END;
/

EXEC DBMS_JOB.RUN(1);
EXEC DBMS_JOB.BROKEN(1, TRUE);
EXEC DBMS_JOB.REMOVE(1);

9. DBMS_LOCK

DECLARE
  v_lockhandle VARCHAR2(128);
  v_lockid NUMBER;
BEGIN
  DBMS_LOCK.ALLOCATE_UNIQUE('my_lock', v_lockhandle);
  v_lockid := DBMS_LOCK.REQUEST(
    id => DBMS_LOCK.ALLOCATE_UNIQUE('my_lock', v_lockhandle),
    lockmode => DBMS_LOCK.X_MODE,
    timeout => 10
  );
  
  -- 临界区
  
  DBMS_LOCK.RELEASE(v_lockid);
END;
/

10. DBMS_ALERT

-- 注册
EXEC DBMS_ALERT.REGISTER('my_alert');

-- 发送
EXEC DBMS_ALERT.SIGNAL('my_alert', 'message');

-- 等待
DECLARE
  v_msg VARCHAR2(4000);
  v_status INTEGER;
BEGIN
  DBMS_ALERT.WAITONE('my_alert', v_msg, v_status);
  DBMS_OUTPUT.PUT_LINE('Got: ' || v_msg);
END;
/

-- 注销
EXEC DBMS_ALERT.REMOVE('my_alert');

11. DBMS_PIPE

-- 发送
DECLARE
  v_status INTEGER;
BEGIN
  DBMS_PIPE.PACK_MESSAGE('Hello');
  v_status := DBMS_PIPE.SEND_MESSAGE('my_pipe');
END;
/

-- 接收
DECLARE
  v_msg VARCHAR2(4000);
  v_status INTEGER;
BEGIN
  v_status := DBMS_PIPE.RECEIVE_MESSAGE('my_pipe');
  DBMS_PIPE.UNPACK_MESSAGE(v_msg);
  DBMS_OUTPUT.PUT_LINE('Got: ' || v_msg);
END;
/

12. DBMS_CRYPTO

-- 加密
DECLARE
  v_input VARCHAR2(100) := 'secret';
  v_enc RAW(2000);
  v_key RAW(32) := UTL_I18N.STRING_TO_RAW('mykey', 'AL32UTF8');
BEGIN
  v_enc := DBMS_CRYPTO.ENCRYPT(
    src => UTL_I18N.STRING_TO_RAW(v_input, 'AL32UTF8'),
    typ => DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5,
    key => v_key
  );
  
  -- 解密
  v_input := UTL_I18N.RAW_TO_STRING(
    DBMS_CRYPTO.DECRYPT(
      src => v_enc,
      typ => DBMS_CRYPTO.ENCRYPT_AES256 + DBMS_CRYPTO.CHAIN_CBC + DBMS_CRYPTO.PAD_PKCS5,
      key => v_key
    ),
    'AL32UTF8'
  );
END;
/

-- HASH
SELECT DBMS_CRYPTO.HASH(UTL_RAW.CAST_TO_RAW('abc'), 2) FROM dual;

13. DBMS_RANDOM

-- 随机数
SELECT DBMS_RANDOM.VALUE FROM dual;
SELECT DBMS_RANDOM.VALUE(1, 100) FROM dual;
SELECT DBMS_RANDOM.NORMAL FROM dual;

-- 随机字符串
SELECT DBMS_RANDOM.STRING('U', 10) FROM dual;  -- 大写
SELECT DBMS_RANDOM.STRING('L', 10) FROM dual;  -- 小写
SELECT DBMS_RANDOM.STRING('A', 10) FROM dual;  -- 字母
SELECT DBMS_RANDOM.STRING('X', 10) FROM dual;  -- 大写+数字
SELECT DBMS_RANDOM.STRING('P', 10) FROM dual;  -- 可打印

-- 随机数据
INSERT INTO t SELECT DBMS_RANDOM.VALUE(1, 1000) FROM dual CONNECT BY LEVEL <= 1000;

14. DBMS_XPLAN

-- 执行计划
EXPLAIN PLAN FOR SELECT ...;
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY));

-- 游标
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_CURSOR('&sql_id'));

-- AWR
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_AWR('&sql_id'));

-- SQL Plan Baseline
SELECT * FROM TABLE(DBMS_XPLAN.DISPLAY_SQL_PLAN_BASELINE(...));

详细见：Oracle 执行计划详解。

15. DBMS_SQLTUNE

-- 创建任务
DECLARE
  v_task VARCHAR2(30);
BEGIN
  v_task := DBMS_SQLTUNE.CREATE_TUNING_TASK(sql_id => '&sql_id');
  DBMS_SQLTUNE.EXECUTE_TUNING_TASK(v_task);
END;
/

-- 报告
SELECT DBMS_SQLTUNE.REPORT_TUNING_TASK('TASK_NAME') FROM dual;

-- 接受 Profile
EXEC DBMS_SQLTUNE.ACCEPT_SQL_PROFILE(...);

详细见：Oracle SQL 调优顾问。

16. DBMS_SPM

-- 加载
DECLARE
  pls PLS_INTEGER;
BEGIN
  pls := DBMS_SPM.LOAD_PLANS_FROM_CURSOR_CACHE(sql_id => '...');
END;
/

-- 查看
SELECT * FROM dba_sql_plan_baselines;

-- 固定
EXEC DBMS_SPM.ALTER_SQL_PLAN_BASELINE(...);

详细见：Oracle SQL Plan Baseline 基线。

17. DBMS_MVIEW

-- 刷新
EXEC DBMS_MVIEW.REFRESH('mv_emp');
EXEC DBMS_MVIEW.REFRESH('mv_emp', 'C');  -- Complete
EXEC DBMS_MVIEW.REFRESH('mv_emp', 'F');  -- Fast
EXEC DBMS_MVIEW.REFRESH_ALL_MVIEWS;

-- 能力
EXEC DBMS_MVIEW.EXPLAIN_MVIEW('mv_emp');
SELECT * FROM mv_capabilities_table;

详细见：Oracle 视图与物化视图详解。

18. DBMS_REDEFINITION

-- 在线重定义
BEGIN
  DBMS_REDEFINITION.CAN_REDEF_TABLE('SCOTT', 'EMPLOYEES');
  DBMS_REDEFINITION.START_REDEF_TABLE('SCOTT', 'EMPLOYEES', 'EMPLOYEES_NEW');
  DBMS_REDEFINITION.FINISH_REDEF_TABLE('SCOTT', 'EMPLOYEES', 'EMPLOYEES_NEW');
END;
/

详细见：Oracle 在线重定义详解。

19. DBMS_METADATA

-- DDL
SELECT DBMS_METADATA.GET_DDL('TABLE', 'EMPLOYEES', 'SCOTT') FROM dual;
SELECT DBMS_METADATA.GET_DDL('INDEX', 'IDX_EMP_NAME') FROM dual;
SELECT DBMS_METADATA.GET_DDL('PROCEDURE', 'MY_PROC') FROM dual;

-- 依赖
SELECT referenced_name, referenced_type FROM user_dependencies WHERE name = 'MY_PROC';

20. UTL_MAIL

-- 配置
ALTER SYSTEM SET smtp_out_server = 'smtp.example.com' SCOPE=SPFILE;

-- 发送
BEGIN
  UTL_MAIL.SEND(
    sender => '[email protected]',
    recipients => '[email protected]',
    subject => 'Alert',
    message => 'Database alert'
  );
END;
/

21. UTL_HTTP

-- HTTP 请求
DECLARE
  v_req UTL_HTTP.REQ;
  v_resp UTL_HTTP.RESP;
  v_text VARCHAR2(4000);
BEGIN
  v_req := UTL_HTTP.BEGIN_REQUEST('http://example.com/api');
  v_resp := UTL_HTTP.GET_RESPONSE(v_req);
  
  LOOP
    UTL_HTTP.READ_LINE(v_resp, v_text);
    DBMS_OUTPUT.PUT_LINE(v_text);
  END LOOP;
  
  UTL_HTTP.END_RESPONSE(v_resp);
EXCEPTION
  WHEN UTL_HTTP.END_OF_BODY THEN
    UTL_HTTP.END_RESPONSE(v_resp);
END;
/

22. DBMS_AQ

-- 队列
EXEC DBMS_AQADM.CREATE_QUEUE_TABLE('qt_msg', 'msg_type');
EXEC DBMS_AQADM.CREATE_QUEUE('q_msg', 'qt_msg');
EXEC DBMS_AQADM.START_QUEUE('q_msg');

-- 入队
DECLARE
  v_opt DBMS_AQ.ENQUEUE_OPTIONS_T;
  v_prop DBMS_AQ.MESSAGE_PROPERTIES_T;
  v_msgid RAW(16);
  v_msg msg_type := msg_type('hello');
BEGIN
  DBMS_AQ.ENQUEUE('q_msg', v_opt, v_prop, v_msg, v_msgid);
  COMMIT;
END;
/

-- 出队
DECLARE
  v_opt DBMS_AQ.DEQUEUE_OPTIONS_T;
  v_prop DBMS_AQ.MESSAGE_PROPERTIES_T;
  v_msgid RAW(16);
  v_msg msg_type;
BEGIN
  DBMS_AQ.DEQUEUE('q_msg', v_opt, v_prop, v_msg, v_msgid);
  DBMS_OUTPUT.PUT_LINE(v_msg.text);
  COMMIT;
END;
/

23. 最佳实践

  1. DBMS_OUTPUT：调试
  2. DBMS_SQL：动态
  3. DBMS_LOB：大对象
  4. UTL_FILE：文件
  5. DBMS_STATS：统计
  6. DBMS_SCHEDULER：调度
  7. DBMS_CRYPTO：加密
  8. DBMS_XPLAN：计划
  9. DBMS_SQLTUNE：调优
  10. DBMS_METADATA：DDL

24. 参考资料

[1] Oracle Database PL/SQL Packages and Types Reference 19c https://docs.oracle.com/en/database/oracle/oracle-database/19/arpls/