OracleJOB scheduled task

-- Create a table

Createtable JOBTEST (IDVARCHAR2 (50) primarykey, NAMEVARCHAR2 (20), age number (3 ))

-- Create a stored procedure

Createorreplaceprocedure jobtestprocedure

Isbegin

Insertinto jobtest values (to_char (sysdate, 'yyyy-MM-dd hh: mm: ss'), 'figo', 28 );

Commit;

End;

-- Create a scheduled task

Declare myjob number;

Begin

Dbms_job.submit (myjob, 'jobtestprocedure; ', sysdate, 'sysdate + 1/24/60/60 ');-- Run once every 5 seconds

End;

-- View scheduled tasks

-- View the Timer: Log On As A dba and execute it.

Select * from dba_jobs;

-- Or the current user

Select * from user_jobs;

-- Start a scheduled task

Begin

Dbms_job.run (3 );

End;

-- Delete a scheduled task

Begin

Dbms_job.remove (3 );

End;

Commit;

Exec dbms_job.remove (3 );-- Delete a timer

Exec dbms_job.run (3 );-- Run a timer

Exec DBMS_JOB.BROKEN (3, SYS. DIUTIL. INT_TO_BOOL (1 ));-- Stop a timer

Exec DBMS_JOB.INTERVAL (3, 'sysdate + 100 ');-- Change the execution frequency of a timer to every hour.

-- View the data table to see if the task has been executed

Select count (1) from jobtest;

Description of timer parameters:

The myjob parameter is the binary_ineger returned by the Submit () process. This value uniquely identifies a job;

What parameter is the PL/SQL code block to be executed. Here it refers to a stored procedure. Pay attention to the semicolon following the name;

The next_date parameter specifies when the job will be run. You can leave this value unspecified when writing a Job;

The interval parameter indicates when the job will be re-executed.

For more information about interval Settings, see the following examples:

1. Execution per minute

Interval => TRUNC (sysdate, 'mi') + 1/(24*60)

2. daily scheduled execution

Example: Execute at every day

Interval => TRUNC (sysdate) + 1 + 2/(24)

3. weekly scheduled execution

Example: Execute at every Monday

Interval => TRUNC (next_day (sysdate, 2) + 2/24 -- Monday, the second day of a week

4. scheduled monthly execution

For example, the task is executed at on the first day of every month.

Interval => TRUNC (LAST_DAY (SYSDATE) + 1 + 2/24

5. Periodical execution on a quarterly basis

For example, the statement is executed at on the first day of each quarter.

Interval => TRUNC (ADD_MONTHS (SYSDATE, 3), 'q') + 2/24

6. scheduled execution every six months

For example, at a.m. on January 1, July 1 and January 1, January 1

Interval => ADD_MONTHS (trunc (sysdate, 'yyyy'), 6) + 2/24

7. Regular annual execution

For example, it is executed at on January 1, January 1 every year.

Interval => ADD_MONTHS (trunc (sysdate, 'yyyy'), 12) + 2/24