Oracle:job use

Oracle's job, in fact, is a database built-in timing task, similar to the timer function in the code. The following are the use procedures:

Here we simulate a scenario where the stored procedure is called periodically p_test_job insert data into table Test_job_log

Table structure:

1 Create TableTest_job_log2 (3rec_id Number  not NULL,4 occr_time DATE5 );6 Alter TableTest_job_log7   Add constraintPk_test_jobPrimary Key(rec_id);

Sequence:

1 Create sequence Seq_test_job_log 2 1 3 99999999 4  with 1 5  by 1 6 ten;

Stored procedures:

1 Create or Replace procedure  is 2 begin 3   Insert  into Values (seq_test_job_log.nextval,sysdate); 4   Commit ; 5 End P_test_job;

The above is just the preparatory work, the following is the focus: (The following script is all PL/SQL developer Environment)

1. Create Job

 1  declare  2  job_id number   3   4  sys.dbms_job.submit (job_id,  " p_test_ JOB;   ", Sysdate, "   sysdate+1/1440  ); --  run now, then run once every minute  5  sys.dbms_output.put_line (job_id); --  output job Id  6  end ; 

Each job is created with a unique number, and in the PL/SQL Output panel, you can see the job ID value of the dbms_output.put_line outputs.

2, check the job running situation

1 Select *  from -- DBA Authority required 2 Select *  from -- DBA Authority required 3 Select *  from all_jobs;   4 Select *  from User_jobs;

3. Delete Job

begin   Dbms_job.remove (108--108 is a specific job ID that can be queried by the SELECT * from User_jobs to get the end ;

4. Manually start the job

1 begin 2   Dbms_job.run (109); -- run the specified job 3 End;

Finally, give a few small examples of job creation:

A, stored procedure call with parameters

 1  declare  2  job_id number   3   4  sys.dbms_job.submit (job_id,  " p_job_ CKG (sysdate,sysdate-30);   ", Sysdate, "   Trunc (sysdate+1) + (4*60)/(24*60)   "); --  run  4:00 every day. 5  sys.dbms_output.put_line (job_id); --  output job Id  6  end ; 

This specifies that the incoming parameter of P_JOB_CKG is Sysdate and sysdate-30, and if it is a string parameter, it is required to add two single quotes, similar to ' p_xxx (' parameter value '); '

B, example of an expression for a specified time

Run Once a day
' Sysdate + 1 '

Run once per hour
' Sysdate + 1/24 '

Run every 10 minutes
' Sysdate + 10/(60*24) '

Run once every 30 seconds
' Sysdate + 30/(60*24*60) '

Run every other week
' Sysdate + 7 '

Run once on the last day of each month
' TRUNC (Last_day (Add_months (sysdate,1)) + 23/24 '

January 1 zero per year
' TRUNC (Last_day (To_date (EXTRACT (year from sysdate) | | 12 ' | | ' (' Yyyy-mm-dd ')) +1) '

12 O'Clock midnight every day.
' 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 every month
' TRUNC (Last_day (sysdate) + 1) '

23 points on the last day of each month
' TRUNC (Last_day (sysdate)) + 23/24 '

The last day of every 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, "SATURDAY"), Next_day (Sysdate, "SUNDAY")) + (6*60+10)/(24*60) '

Oracle:job use