位置：文档库 > 数据库 > Oracle job + 存储过程的使用示例

Oracle job + 存储过程的使用示例

海盐留言2123 上传于 2021-05-14 22:16

《Oracle job + 存储过程的使用示例》

在Oracle数据库管理中，自动化任务调度和批量数据处理是提升系统效率的关键。Oracle Job（作业）与存储过程（Stored Procedure）的结合，能够实现定时执行复杂业务逻辑、数据清洗、报表生成等场景。本文将通过完整示例，详细讲解如何创建、管理Oracle Job并调用存储过程，同时分析常见问题与优化策略。

一、Oracle Job与存储过程基础

1.1 Oracle Job概述

Oracle Job是数据库内置的调度器，用于在指定时间或间隔自动执行PL/SQL块、存储过程或外部脚本。其核心组件包括：

DBMS_SCHEDULER：Oracle 10g后推荐的调度包，功能强大且灵活
DBMS_JOB：传统调度包（Oracle 8i引入），功能较简单

本文以DBMS_SCHEDULER为例，因其支持更复杂的调度规则（如日历表达式、依赖关系等）。

1.2 存储过程核心优势

存储过程是将业务逻辑封装在数据库中的程序单元，具有以下特点：

减少网络传输（数据在服务器端处理）
提高安全性（通过权限控制访问）
提升性能（编译后代码可重用）
便于维护（逻辑集中管理）

二、完整使用示例

2.1 示例场景

假设需要每天凌晨2点自动执行一个存储过程，该过程完成以下操作：

统计前一日订单数据
将结果插入汇总表
发送通知邮件（通过UTL_MAIL）

2.2 创建存储过程

CREATE OR REPLACE PROCEDURE proc_daily_order_report AS
  v_total_orders NUMBER;
  v_total_amount NUMBER;
  v_report_date DATE := TRUNC(SYSDATE - 1);
BEGIN
  -- 统计前一日订单
  SELECT COUNT(*), SUM(order_amount)
  INTO v_total_orders, v_total_amount
  FROM orders
  WHERE order_date = v_report_date;
  
  -- 插入汇总表
  INSERT INTO daily_order_summary
    (report_date, order_count, total_amount)
  VALUES
    (v_report_date, v_total_orders, v_total_amount);
  
  -- 发送邮件（需配置ACLS）
  BEGIN
    UTL_MAIL.SEND(
      sender => 'db_admin@example.com',
      recipients => 'manager@example.com',
      subject => '每日订单报告',
      message => '日期: ' || TO_CHAR(v_report_date, 'YYYY-MM-DD') || 
                  ' 订单数: ' || v_total_orders || 
                  ' 总金额: ' || v_total_amount
    );
  EXCEPTION
    WHEN OTHERS THEN
      DBMS_OUTPUT.PUT_LINE('邮件发送失败: ' || SQLERRM);
  END;
  
  COMMIT;
  DBMS_OUTPUT.PUT_LINE('报告生成完成');
EXCEPTION
  WHEN OTHERS THEN
    ROLLBACK;
    DBMS_OUTPUT.PUT_LINE('错误: ' || SQLERRM);
END proc_daily_order_report;
/

2.3 创建Job调度

使用DBMS_SCHEDULER创建每日执行的Job：

BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'job_daily_order_report',
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'proc_daily_order_report',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=2', -- 每天凌晨2点
    enabled         => TRUE,
    comments        => '每日订单统计报告'
  );
  
  DBMS_OUTPUT.PUT_LINE('Job创建成功');
END;
/

2.4 验证Job执行

查询Job状态和执行日志：

-- 查看所有Job
SELECT job_name, enabled, state, next_run_date 
FROM user_scheduler_jobs;

-- 查看Job运行历史
SELECT log_id, job_name, status, actual_start_date, run_duration
FROM user_scheduler_job_run_details
ORDER BY actual_start_date DESC;

-- 手动运行Job（测试用）
BEGIN
  DBMS_SCHEDULER.RUN_JOB('job_daily_order_report');
END;
/

三、高级功能与最佳实践

3.1 复杂调度设置

使用日历表达式实现更灵活的调度：

-- 每周一至周五上午9点执行
repeat_interval => 'FREQ=WEEKLY; BYDAY=MON,TUE,WED,THU,FRI; BYHOUR=9'

-- 每月最后一个工作日执行
repeat_interval => 'FREQ=MONTHLY; BYDAY=-1MON,-1TUE,-1WED,-1THU,-1FRI; BYHOUR=17'

3.2 Job依赖管理

创建链式Job（Job Chain）：

-- 创建程序
BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM (
    program_name   => 'prog_data_load',
    program_type   => 'STORED_PROCEDURE',
    program_action => 'proc_load_data',
    enabled        => TRUE
  );
  
  DBMS_SCHEDULER.CREATE_PROGRAM (
    program_name   => 'prog_data_process',
    program_type   => 'STORED_PROCEDURE',
    program_action => 'proc_process_data',
    enabled        => TRUE
  );
END;
/

-- 创建链
BEGIN
  DBMS_SCHEDULER.CREATE_CHAIN (
    chain_name => 'chain_data_pipeline',
    rule_set_name => NULL,
    evaluation_interval => NULL,
    comments => '数据加载与处理链'
  );
  
  -- 添加步骤
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
    chain_name => 'chain_data_pipeline',
    step_name  => 'step_load',
    program_name => 'prog_data_load'
  );
  
  DBMS_SCHEDULER.DEFINE_CHAIN_STEP (
    chain_name => 'chain_data_pipeline',
    step_name  => 'step_process',
    program_name => 'prog_data_process'
  );
  
  -- 定义规则（顺序执行）
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
    chain_name => 'chain_data_pipeline',
    condition => 'TRUE',
    action => 'START step_load',
    rule_name => 'rule_start',
    comments => '启动加载步骤'
  );
  
  DBMS_SCHEDULER.DEFINE_CHAIN_RULE (
    chain_name => 'chain_data_pipeline',
    condition => 'step_load COMPLETED',
    action => 'START step_process',
    rule_name => 'rule_process',
    comments => '加载完成后处理'
  );
  
  DBMS_SCHEDULER.ENABLE('chain_data_pipeline');
END;
/

-- 创建链Job
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => 'job_run_data_pipeline',
    job_type        => 'CHAIN',
    job_action      => 'chain_data_pipeline',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=DAILY; BYHOUR=1',
    enabled         => TRUE
  );
END;
/

3.3 错误处理与通知

配置Job失败时发送警报：

-- 创建通知程序
BEGIN
  DBMS_SCHEDULER.CREATE_PROGRAM (
    program_name   => 'prog_send_alert',
    program_type   => 'PLSQL_BLOCK',
    program_action => '
      BEGIN
        UTL_MAIL.SEND(
          sender => ''db_alert@example.com'',
          recipients => ''dba_team@example.com'',
          subject => ''Job失败警报: '' || :job_name,
          message => ''Job '' || :job_name || '' 执行失败于 '' || 
                     TO_CHAR(SYSTIMESTAMP, ''YYYY-MM-DD HH24:MI:SS'') || 
                     '' 错误: '' || :error_message
        );
      END;',
    enabled        => TRUE
  );
END;
/

-- 创建带错误处理的Job
DECLARE
  v_job_name VARCHAR2(30) := 'job_with_alert';
BEGIN
  DBMS_SCHEDULER.CREATE_JOB (
    job_name        => v_job_name,
    job_type        => 'STORED_PROCEDURE',
    job_action      => 'proc_that_may_fail',
    start_date      => SYSTIMESTAMP,
    repeat_interval => 'FREQ=HOURLY; INTERVAL=1',
    enabled         => FALSE -- 先禁用
  );
  
  -- 创建事件队列
  DBMS_AQADM.CREATE_QUEUE_TABLE(
    queue_table        => 'job_error_qt',
    queue_payload_type => 'SYS.AQ$_JMS_TEXT_MESSAGE',
    storage_clause     => 'TABLESPACE users'
  );
  
  DBMS_AQADM.CREATE_QUEUE(
    queue_name  => 'job_error_q',
    queue_table => 'job_error_qt'
  );
  
  DBMS_AQADM.START_QUEUE(
    queue_name => 'job_error_q'
  );
  
  -- 配置Job事件订阅
  DBMS_SCHEDULER.ADD_JOB_EVENT_SUBSCRIPTION(
    job_name        => v_job_name,
    event_condition => 'q"$job_name = :job_name AND q"$status = ''FAILED''',
    queue_name      => 'job_error_q',
    subscriber      => SYS.AQ$_AGENT('job_alert_agent', 'localhost:1521:ORCL')
  );
  
  -- 启用Job
  DBMS_SCHEDULER.ENABLE(v_job_name);
END;
/

四、常见问题与解决方案

4.1 Job未按时执行

原因：

数据库调度器未启动
系统资源不足导致Job挂起
时间设置错误（如时区问题）

解决方案：

-- 检查调度器状态
SELECT state FROM dba_scheduler_jobs WHERE job_name = 'YOUR_JOB';

-- 启动调度器（需DBA权限）
BEGIN
  DBMS_SCHEDULER.SET_SCHEDULER_ATTRIBUTE(
    attribute => 'scheduler_disabled',
    value     => FALSE
  );
END;
/

4.2 存储过程权限不足

错误示例：

ORA-06512: 在"SYS.DBMS_SCHEDULER", line XXXX
ORA-06512: 在"YOUR_SCHEMA.YOUR_PROCEDURE", line XXXX
ORA-27486: 缺少执行存储过程的权限

解决方案：

-- 授予执行权限
GRANT EXECUTE ON your_schema.your_procedure TO scheduler_owner;

-- 或授予整个模式
GRANT EXECUTE ANY PROCEDURE TO scheduler_owner;

4.3 Job重复执行

原因：

Job被意外启用多次
前一次执行未完成导致重叠

解决方案：

-- 设置最大运行实例数
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => 'your_job_name',
    attribute => 'max_runs',
    value     => 1
  );
END;
/

-- 或使用Job类控制
BEGIN
  DBMS_SCHEDULER.CREATE_JOB_CLASS(
    job_class_name  => 'single_run_class',
    resource_plan   => NULL,
    service         => NULL,
    logging_level   => DBMS_SCHEDULER.LOGGING_OFF,
    comments        => '确保每次只有一个实例运行'
  );
  
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => 'your_job_name',
    attribute => 'job_class_name',
    value     => 'single_run_class'
  );
END;
/

五、性能优化建议

1. 批量处理：在存储过程中使用BULK COLLECT和FORALL减少上下文切换

CREATE OR REPLACE PROCEDURE proc_bulk_update AS
  TYPE id_tab IS TABLE OF NUMBER;
  TYPE amount_tab IS TABLE OF NUMBER;
  v_ids id_tab;
  v_amounts amount_tab;
BEGIN
  -- 批量获取数据
  SELECT order_id, order_amount
  BULK COLLECT INTO v_ids, v_amounts
  FROM orders
  WHERE status = 'PENDING'
  AND ROWNUM

2. 并行处理：对大数据量任务使用DBMS_PARALLEL_EXECUTE

-- 创建任务分区
BEGIN
  DBMS_PARALLEL_EXECUTE.CREATE_TASK('task_parallel_update');
  
  DBMS_PARALLEL_EXECUTE.CREATE_CHUNKS_BY_ROWID(
    task_name   => 'task_parallel_update',
    table_owner => 'your_schema',
    table_name  => 'large_table',
    by_row      => TRUE,
    chunk_size  => 10000
  );
  
  -- 定义处理过程
  DBMS_PARALLEL_EXECUTE.RUN_TASK(
    task_name      => 'task_parallel_update',
    sql_stmt       => 'UPDATE /*+ PARALLEL(t 4) */ your_schema.large_table t
                       SET column1 = :value
                       WHERE rowid BETWEEN :start_id AND :end_id',
    language_flag  => DBMS_SQL.NATIVE,
    parallel_level => 4
  );
END;
/

3. 资源管理：通过资源计划限制Job资源使用

-- 创建资源计划
BEGIN
  DBMS_RESOURCE_MANAGER.CREATE_PENDING_AREA();
  
  DBMS_RESOURCE_MANAGER.CREATE_PLAN(
    plan    => 'plan_job_control',
    comment => '控制Job资源使用'
  );
  
  DBMS_RESOURCE_MANAGER.CREATE_PLAN_DIRECTIVE(
    plan                  => 'plan_job_control',
    group_or_subplan      => 'job_group',
    comment               => '限制Job资源',
    mgmt_p1               => 50, -- CPU份额
    utilization_limit     => 70  -- 最大使用率
  );
  
  DBMS_RESOURCE_MANAGER.VALIDATE_PENDING_AREA();
  DBMS_RESOURCE_MANAGER.SUBMIT_PENDING_AREA();
END;
/

-- 将Job关联到资源计划
BEGIN
  DBMS_SCHEDULER.SET_ATTRIBUTE(
    name      => 'your_job_name',
    attribute => 'resource_plan',
    value     => 'plan_job_control'
  );
END;
/

六、总结

Oracle Job与存储过程的结合为数据库自动化提供了强大工具。通过合理设计存储过程逻辑、配置灵活的调度规则，并实施有效的错误处理和性能优化，可以构建高可靠性的自动化系统。实际应用中需注意：

始终在开发环境充分测试Job逻辑
监控Job执行历史，建立基线性能指标
对关键Job实施冗余设计和故障恢复机制
定期审查和清理不再需要的Job

关键词：Oracle Job、存储过程、DBMS_SCHEDULER、自动化调度、批量处理、错误处理、性能优化

简介：本文详细介绍了Oracle数据库中Job与存储过程的结合使用方法，包含完整示例代码和场景说明。内容涵盖基础概念、创建与调度Job、高级功能（如链式Job、错误通知）、常见问题解决方案以及性能优化策略，适合DBA和开发人员参考。

立即下载

数据库相关

Oracle job + 存储过程 的使用示例

一、Oracle Job与存储过程基础

1.1 Oracle Job概述

1.2 存储过程核心优势

二、完整使用示例

2.1 示例场景

2.2 创建存储过程

2.3 创建Job调度

2.4 验证Job执行

三、高级功能与最佳实践

3.1 复杂调度设置

3.2 Job依赖管理

3.3 错误处理与通知

四、常见问题与解决方案

4.1 Job未按时执行

4.2 存储过程权限不足

4.3 Job重复执行

五、性能优化建议

六、总结

Oracle job + 存储过程的使用示例