Oracle regularly calls stored procedures

 

-- Create a test table name

Create table job_table (run_date date );

-- Create a stored procedure

Create or replace procedure job_proc

Begin

Insert into job_table (run_date) values (sysdate );

End;

 

-- Create a job and run it once every minute

Declare

Job number;

Begin

Dbms_job.submit (job, 'job _ proc; ', sysdate, 'trunc (sysdate, ''mi'') + 1/(24*60 )');

End;

Commit;

 

 

-- Pause a task

 

-- Select job from user_jobs

Find the id value of the job and use the following statement to stop the job.

 

Begin

Dbms_job.broken (584, true );

End;

 

 

-- Restart a task

 

Begin

Dbms_job.execute (584 );

End;

-- Delete a task

Delete user_jobs where job = 584;

Drop procedure job_proc;

Drop table job_table;

 

After creating a job. The system automatically calls the stored procedure at a specified time.

 

 

Knowledge about time interval:

 

1. TRUNC (for dates)

 

The date value intercepted by the TRUNC function for the specified element.

 

The syntax format is as follows:

 

TRUNC (date [, fmt])

 

Where: date is a date value fmt date format, which is truncated by the specified Element format. Ignore it and it is intercepted by the latest date.

 

The usage of this function is as follows:

 

TRUNC (TO_DATE ('24-Nov-1999 12:00:00 pm '), 'dd-mon-yyyy hh: mi am') = '24-Nov-1999 am'

 

TRUNC (TO_DATE ('24-Nov-1999 08:00:00 pm ', 'dd-mon-yyyy hh: mi am'), 'hh') = '24-Nov-1999 am'

 

Trunc (sysdate, 'yyyy') -- returns the first day of the current year.

 

Trunc (sysdate, 'mm') -- returns the first day of the current month.

 

Trunc (sysdate, 'D') -- returns the first day of the current week.

 

Trunc (sysdate, 'dd') -- returns the current year, month, and day.

 

 

The unit of the number appended to the trunc function is day.

 

 

00:00 every morning: trunc (sysdate + 1)

 

Every morning at trunc (sysdate + 1) + 1/3

 

Trunc (sysdate + 1) + (8.5*60)/(24*60) at 08:30 every day)

 

Every morning at 08:30 trunc (sysdate + 1) + 8.5/24

 

Trunc (sysdate + 1) + (8*60 + 30)/(24*60) at 08:30 every day)

 

From Program Development Technology blog Xiao Li technology home