How to set up oracle scheduled tasks to be executed regularly within a certain period of time

To set up tasks to be executed regularly within a specified time period in Oracle, you need to create triggers and jobs: 1. Use triggers to specify the execution time period (start and end dates) and tasks to be executed; 2. Use a job to specify the trigger's name, start execution date, and recurrence frequency.

How to set tasks to be executed regularly within a specified time period in Oracle

In the Oracle database, you can Use the DBMS_JOB package to create and manage scheduled tasks. To set a task to be executed regularly within a certain period of time, please follow the steps below:

1. Create a trigger

Use the following statement to create a trigger, This trigger will execute the specified PL/SQL block within the specified time period:

<code class="sql">CREATE OR REPLACE TRIGGER trigger_name
BEFORE/AFTER INSERT/UPDATE/DELETE ON table_name
FOR EACH ROW
BEGIN
  IF (SYSDATE BETWEEN start_date AND end_date) THEN
    -- 在此执行要执行的任务
  END IF;
END;</code>

where:

• trigger_name is the name of the trigger.
• start_date is the date and time when the task starts execution.
• end_date is the date and time when the task ends execution.

2. Create a job

Use the following statement to create a job that will call the trigger:

<code class="sql">BEGIN
  DBMS_JOB.SUBMIT(
    job => job_name,
    next_date => start_date,
    interval => 'freq=(seconds=60, minutes=0, hours=0, days=0, weeks=0, months=0, years=0)',
    what => 'BEGIN ' || trigger_name || '; END;'
  );
END;</code>

Where:

• job_name is the name of the job.
• start_date is the date and time when the job starts executing.
• interval Specifies how often the job should be executed, in this case, every 60 seconds.
• what Specifies the PL/SQL block to execute.

Example

To create a task that executes every hour between 9am and 5pm every day, you can use the following code:

<code class="sql">CREATE OR REPLACE TRIGGER my_trigger
BEFORE INSERT/UPDATE/DELETE ON my_table
FOR EACH ROW
BEGIN
  IF (SYSDATE BETWEEN TO_DATE('2023-03-08 09:00:00', 'YYYY-MM-DD HH24:MI:SS') AND TO_DATE('2023-03-08 17:00:00', 'YYYY-MM-DD HH24:MI:SS')) THEN
    -- 在此执行要执行的任务
  END IF;
END;

BEGIN
  DBMS_JOB.SUBMIT(
    job => my_job,
    next_date => TO_DATE('2023-03-08 09:00:00', 'YYYY-MM-DD HH24:MI:SS'),
    interval => 'freq=(seconds=0, minutes=60, hours=0, days=0, weeks=0, months=0, years=0)',
    what => 'BEGIN my_trigger; END;'
  );
END;</code>

The above is the detailed content of How to set up oracle scheduled tasks to be executed regularly within a certain period of time. For more information, please follow other related articles on the PHP Chinese website!