Oracle Job如何传参数？

1. 使用 DBMS_JOB (传统方式)：通过设置 nls_date_format 等会话参数来间接传递。
2. 使用 DBMS_SCHEDULER (现代推荐方式)：可以直接、灵活地为程序传递参数。

下面我将详细介绍这两种方法,并提供完整的示例。

使用 DBMS_JOB (传统方式)

DBMS_JOB 是 Oracle 早期提供的作业调度包，功能相对有限，它本身没有直接的参数传递机制,我们通过以下两种变通方法来实现：

变通方法 1：使用 NLS_DATE_FORMAT 传递简单参数

这是最经典的一种方法，你可以将要传递的参数值（通常是字符串或数字）格式化成一个日期字符串，然后在 Job 的 PL/SQL 块中，通过设置 NLS_DATE_FORMAT 来解析这个日期字符串,从而获取参数值。

示例场景：我们想创建一个 Job，每天备份一个特定的表（EMP）,表名作为参数传递。

步骤：

1. 创建一个存储过程,该接受一个表名作为参数。

   CREATE OR REPLACE PROCEDURE backup_table(p_table_name IN VARCHAR2) AS
       v_sql VARCHAR2(1000);
       v_count NUMBER;
   BEGIN
       -- 动态构建 SQL 语句
       v_sql := 'CREATE TABLE backup_' || p_table_name || '_as_of_' || TO_CHAR(SYSDATE, 'YYYYMMDDHH24MISS') || ' AS SELECT * FROM ' || p_table_name;
       -- 执行 SQL
       EXECUTE IMMEDIATE v_sql;
       -- 可选：输出备份信息
       DBMS_OUTPUT.PUT_LINE('Table ' || p_table_name || ' has been backed up.');
   EXCEPTION
       WHEN OTHERS THEN
           DBMS_OUTPUT.PUT_LINE('Error during backup: ' || SQLERRM);
   END backup_table;
   /

2. 创建 Job 并传递参数。 假设我们要备份 EMP 表，我们将 'EMP' 这个字符串格式化成 TO_DATE('EMP', 'YYYYMMDD') 这样的形式。

   VARIABLE job_number NUMBER;
   BEGIN
       -- 提交 Job
       -- 注意：我们将参数 'EMP' 放在了 what 子句的日期格式化函数中
       DBMS_JOB.SUBMIT(
           job        => :job_number,
           what       => 'BEGIN backup_table(TO_CHAR(TO_DATE(''EMP'', ''YYYYMMDD''), ''YYYYMMDD'')); END;',
           next_date  => SYSDATE,
           interval   => 'TRUNC(SYSDATE + 1)' -- 每天零点执行
       );
       COMMIT;
       DBMS_OUTPUT.PUT_LINE('Job created with number: ' || :job_number);
   END;
   /

3. 运行 Job。

   -- 运行刚刚创建的 Job
   BEGIN
       DBMS_JOB.RUN(:job_number);
   END;
   /

工作原理：

• 在 what 子句中，TO_DATE('EMP', 'YYYYMMDD') 会将字符串 'EMP' 转换为一个日期类型（虽然这个日期是无效的，但 TO_CHAR 会把它原样转回来）。
• 在 backup_table 过程内部，p_table_name 接收到的就是 'EMP'。
• 这种方法非常“取巧”，只适用于简单的、固定格式的字符串或数字,不推荐用于复杂或动态的参数。

变通方法 2：使用 UTL_FILE 读取参数文件

如果参数非常复杂，或者需要动态改变，可以将参数写入一个文本文件，Job 运行时通过 UTL_FILE 包去读取这个文件。

缺点：

• 需要在数据库服务器上配置 UTL_FILE 目录。
• 涉及文件 I/O,性能较差。
• 文件管理（创建、删除、权限）比较麻烦。

使用 DBMS_SCHEDULER (现代推荐方式)

DBMS_SCHEDULER 是 Oracle 10g 引入的、功能更强大的调度器，它原生支持为 Job 传递参数，这是 强烈推荐 的方式,它提供了三种参数类型：

1. DATE: 日期类型。
2. FILE: 文件位置类型。
3. VARCHAR2: 字符串类型。

示例场景与方法一相同：创建一个 Job，备份指定的表。

步骤：

1. 确保存储过程已创建（与方法一相同）。

   CREATE OR REPLACE PROCEDURE backup_table(p_table_name IN VARCHAR2) AS
       -- ... (代码同上)
   END backup_table;
   /

2. 创建 Job 并直接传递参数。 我们使用 DBMS_SCHEDULER.CREATE_JOB，并通过 parameter_values 参数来传递值。

   BEGIN
       DBMS_SCHEDULER.CREATE_JOB (
           job_name        => 'backup_emp_job_scheduler', -- Job 的唯一名称
           job_type        => 'PLSQL_BLOCK', -- 作业类型为 PL/SQL 块
           job_action      => 'backup_table(:p_table_name)', -- 要执行的 PL/SQL 代码
           start_date      => SYSDATE, -- 立即开始
           repeat_interval => 'FREQ=DAILY; BYHOUR=2', -- 每天凌晨2点执行
           enabled         => TRUE, -- 创建后立即启用
           comments        => 'A job to backup a specific table using parameters'
       );
       -- 添加参数
       DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
           job_name        => 'backup_emp_job_scheduler',
           argument_name   => 'p_table_name', -- 必须与 job_action 中的占位符名称一致
           argument_value  => 'EMP' -- 要传递的实际值
       );
       COMMIT;
       DBMS_OUTPUT.PUT_LINE('Job "backup_emp_job_scheduler" created successfully.');
   END;
   /

关键点解释：

• job_action: 这里不是直接写 BEGIN ... END;，而是直接写 backup_table(:p_table_name)。p_table_name 是一个占位符。
• SET_JOB_ARGUMENT_VALUE: 这个过程用于为占位符赋值。
  • job_name: 要设置参数的 Job 名称。
  • argument_name: 占位符的名称，必须与 job_action 中 后面的名字完全一致（不区分大小写）。
  • argument_value: 你想传递的实际值。

3. 运行 Job。

   BEGIN
       DBMS_SCHEDULER.RUN_JOB('backup_emp_job_scheduler');
   END;
   /

4. 查看 Job 状态和日志。

   -- 查看所有 Job
   SELECT job_name, state, last_start_date, next_run_date 
   FROM user_scheduler_jobs;
   -- 查看运行日志（非常有用！）
   SELECT job_name, status, actual_start_date, log_id
   FROM user_scheduler_job_run_details
   WHERE job_name = 'backup_emp_job_scheduler';

传递多个参数

DBMS_SCHEDULER 轻松支持多个参数，只需在 job_action 中定义多个占位符，然后多次调用 SET_JOB_ARGUMENT_VALUE 即可。

示例：修改 backup_table 过程,增加一个备份描述参数。

-- 1. 修改存储过程
CREATE OR REPLACE PROCEDURE backup_table(
    p_table_name IN VARCHAR2, 
    p_description IN VARCHAR2
) AS
    -- ... (代码略，可以使用 p_description)
BEGIN
    DBMS_OUTPUT.PUT_LINE('Backing up table: ' || p_table_name || ' for reason: ' || p_description);
    -- ... 备份逻辑 ...
END backup_table;
/
-- 2. 创建带多个参数的 Job
BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'backup_emp_job_multi_param',
        job_type        => 'PLSQL_BLOCK',
        job_action      => 'backup_table(:p_table_name, :p_description)',
        start_date      => SYSDATE,
        repeat_interval => 'FREQ=DAILY',
        enabled         => TRUE
    );
    -- 设置第一个参数
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
        job_name        => 'backup_emp_job_multi_param',
        argument_name   => 'p_table_name',
        argument_value  => 'DEPT'
    );
    -- 设置第二个参数
    DBMS_SCHEDULER.SET_JOB_ARGUMENT_VALUE(
        job_name        => 'backup_emp_job_multi_param',
        argument_name   => 'p_description',
        argument_value  => 'Monthly department report'
    );
    COMMIT;
END;
/

总结与对比

对于任何新的 Oracle 开发工作，请务必使用 DBMS_SCHEDULER，它设计更现代，功能更完善，特别是其原生的参数传递机制，使得代码更清晰、更健壮、更易于维护。DBMS_JOB 应被视为遗留技术，仅在维护无法迁移到 DBMS_SCHEDULER 的旧系统时使用。