Oracle Job Timing Tasks

-- Create a table

CreateTableJobtest (IDVARCHAR2( -)PrimaryKey,NAMEVARCHAR2( -), age Number(3) )

-- create a stored procedure

Create or Replace procedure jobtestprocedure

isbegin

Insert into jobtestvalues(to_char (sysdate, ' yyyy-mm-dd hh:mm:ss '),' Figo ',();

commit;

end;

-- Create a timed task

declare myjobnumber;

begin

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

end;

-- View Scheduled Tasks

-- View timers: to DBA Role Login Execution

Select * from Dba_jobs;

-- or the current user

Select * from user_jobs;

-- start a scheduled task

Begin

Dbms_job.run (3);

End  ;

-- Delete Scheduled Tasks

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 + 60/1440 '); -- Change the execution frequency of a timer to execute every hour

-- check the datasheet to see if the task has been executed

Select Count (1) from jobtest;

Parameter description of the timer:

The Myjob parameter is the Binary_ineger returned by the submit () procedure. This value is used to uniquely identify a job;

What parameter is the PL/SQL code block that will be executed, here refers to a stored procedure, notice the semicolon after the name;

The Next_date parameter indicates when the job will run. You can not specify this value when writing a job;

Interval parameter when this work will be re-executed.

For interval settings, refer to the following examples:

1. Execute Every Minute

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

2, daily scheduled execution

Example: Daily 2 o'clock in the morning execution

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

3. Regular weekly execution

For example: Every Monday 2 o'clock in the morning execution

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

4. Regular monthly execution

For example: 1st 2 o'clock in the morning every month to execute

Interval =>trunc (Last_day (sysdate)) +1+2/24

5, quarterly scheduled execution

For example, the first day of each quarter is 2 o'clock in the morning execution

Interval = TRUNC (Add_months (sysdate,3), ' Q ') + 2/24

6, every half-yearly scheduled execution

For example: Every July 1 and January 1 2 o'clock in the morning

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

7. Scheduled execution every year

For example: January 1 2 o'clock in the morning every year to execute

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