Oracle periodically invokes stored procedures (easy to understand)

--Create a test table name
CREATE TABLE job_table (run_date date);
--Create a stored procedure
Create or replace procedure Job_proc as
Begin
Insert into job_table (run_date) values (sysdate);
End

--Create a job and specify one minute execution
Declare
Job number;
Begin
Dbms_job.submit (Job, ' Job_proc; ', Sysdate, ' TRUNC (sysdate, ' mi ') +1/(24*60) ');
End
Commit

--Suspend task

--select Job from User_jobs
Locate the Job ID value, and then use the following statement to stop the task.

Begin
Dbms_job.broken (584,true);
End

--Restart Task

Begin
Dbms_job.run (584);
End
--Delete Task
Delete User_jobs where job=584;
drop procedure Job_proc;
drop table job_table;

After the job is created. The system automatically invokes the stored procedure at a specified time.

Relevant knowledge about time intervals:

1.TRUNC (for dates)

The date value that the Trunc function truncates for the specified element.

Its specific syntax format is as follows:

TRUNC (Date[,fmt])

Where: Date A Date value FMT the date format, which is truncated by the specified element format. Ignoring it is truncated by the nearest date.

The following is the use of this function:

TRUNC (To_date (' 24-nov-1999 08:00 pm '), ' dd-mon-yyyy hh:mi am ') = ' 24-nov-1999 12:00:00 am '

TRUNC (To_date (' 24-nov-1999 08:37 pm ', ' dd-mon-yyyy hh:mi am '), ' hh ') = ' 24-nov-1999 08:00:00 am '

Trunc (sysdate, ' yyyy ')--return to the first day of the year.

Trunc (sysdate, ' mm ')--return to the first day of the month.

Trunc (sysdate, ' d ')--Returns the first day of the current week.

Trunc (sysdate, ' DD ')--return to current month day

The unit of the number that is appended to the TRUNC function is the day.

Daily 0:0 A.M.: trunc (sysdate+1)

Every morning at 8 o ' trunc (sysdate+1) +1/3

Daily 8:30 A.M. trunc (sysdate+1) + (8.5*60)/(24*60)

Every day 8:30 A.M. trunc (sysdate+1) +8.5/24

Daily 8:30 A.M. trunc (sysdate+1) + (8*60+30)/(24*60)