Oracle Timer Call Stored procedure

Reprint please indicate source: http://t22011787.iteye.com/blog/1112745

1. Create a table, in order to clearly see the operation of the timer we create a table with a date field

CREATE TABLE job_table (run_time date);

2. Create a stored procedure

Create or Replace procedure Job_proc isbegin  insert INTO job_table (run_time) values (sysdate); end;

3. Create a job, and specify that it be performed one minute

Declare  job Number;begin  dbms_job.submit (Job, ' Job_proc; ', Sysdate, ' TRUNC (sysdate, ' mi ') + 1/(24*60) '); Endcommit;

4. After the creation is automatically running, we query the job table to see the job we createdSelect job,broken,what,interval,t.* from User_jobs t;

Job	Broken	What	Interval	...
81	N	Job_proc;	TRUNC (sysdate, ' mi ') + 1/(24*60)	...

Parameter introduction
Job	Unique identifier of the job, auto-generated
Broken	is running, N; run; Y: Stop
What	Stored Procedure Name
Interval	Definition of execution time

Add:
Description                      Interval parameter value
12 o'clock midnight daily             ' TRUNC (sysdate + 1) '
Daily 8:30 A.M.          ' TRUNC (sysdate + 1) + (8*60+30)/( 24*60) '
every Tuesday noon 12 o'clock          ' Next_day (TRUNC (sysdate), "' Tuesday '" + 12/24 '
12 o'clock midnight on the first day of the month     ' TRUNC (Last_day (sysdate) + 1) '
The last day of the quarter 11 o'clock ' TRUNC (add_months ( Sysdate + 2/24, 3), ' Q ') -1/24 '
every Saturday and Sunday 6:10 A.M.     ' TRUNC (LEAST (Next_day, "sysdate"), Next_day (Sysdate, "SUNDAY")) + (6X60+10)/(24x60) '
5. We'll check the table. Job_table look at the data inside

SELECT * FROM Job_table

Run_time

2011-7-1 05:21:14

2011-7-1 05:22:04

2011-7-1 05:23:04

6. Stop job, see if broken of user_jobs table becomes Y value after stopping success

Begin  Dbms_job.broken (81,true); endcommit;

7. Start the job to see if the broken of the User_jobs table becomes n value after successful startup

Begin  Dbms_job.run (Bayi); end;commit;

8. Delete job, stored procedure, table

Oracle Timer Call Stored procedure