Create a job in Oracle (note that you must execute the job in the PLSQL command window)

 1. Create a stored procedure

Create Or Replace Procedure My_procedure
Is
-- The cursor is used here.
Cursor Cur Is
Select * From Users Where Length (name) > 10 ;
Begin
For User In Cur
Loop
Insert Into Users_temp Values ( User . ID, User . Passwd );
End Loop;
End ;

 

Ii. Create a job

Variable job_num Number ;
Begin
Dbms_job.submit (: job_num, ' My_procedure; ' , Sysdate, ' Sysdate + 1 ' );
Commit ;
End ;

 

Iii. Run a job

Begin
Dbms_job.run (: job_num );
End ;

 

4. query jobs

Select Priv_user, job, what From User_jobs;

 

5. delete a job 

Begin
Dbms_job.remove (: job_num );
End ;

 

Vi. Time

Sysdate + 1 / 1440 -- Run Once every 1 minute
Sysdate + 5 / 1440 -- Run once every 5 minutes
Trunc (sysdate + 1 ) + 2 / 24 -- Run the command at next day.
' Sysdate + 7 ' -- 7 days after the last execution
' Sysdate + 1/48 ' -- Executed every 30 minutes
' Next_day (trunc (sysdate ), '' Monday '' ) + 15/24 ' -- Three o'clock P.M. every Monday
' Next_day (add_months (trunc (sysdate, '' Q '' ), 3 ), '' Thursday '' ) ' -- The first Thursday of each quarter

 

 

The last item can be referenced as follows:

'Trunc (sysdate + 1) 'At midnight every day )'

Every morning 08:30 'trunc (sysdate + 1) + (8*60 + 30)/(24*60 )'

Next _ day (trunc (sysdate), ''tuesday'') + 12/24 at every Tuesday'

'Trunc (last_day (sysdate) + 1) 'at midnight on the first day of each month )'

'Trunc (add_months (sysdate + 2/24, 3), 'q')-100' at on the last day of each quarter'

'Trunc (least (next_day (sysdate, ''saturday "), next_day (sysdate," Sunday ") + (6*60 + 10) /(24*60 )'

Here, li_jobno is its ID. You can stop this task by using this ID. The last thing you want to say is not to execute it multiple times. You can manage it in it and find that it is already running and will not submit it.

Run 'sysdate + 1' every day'

Run 'sysdate + 1/24 'every hour'

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

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

Run 'sysdate + 7' once every week'

No longer run the task and delete it null

Trunc (last_day (to_date (extract (year from sysdate) | '12' | '01', 'yyyy-mm-dd') + 1 at each year)