Shulou(Shulou.com)05/31 Report--

This article mainly introduces "oracle how to write and view delete scheduled tasks". In daily operation, I believe many people have doubts about how to write and view delete scheduled tasks in oracle. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "how to write and view delete scheduled tasks in oracle". Next, please follow the editor to study!

Declare

Jobno number

Begin dbms_job.submit (

Jobno,-- timer ID, the system automatically obtains

'PRC_INSERT;',-- the name of the procedure executed by what

Sysdate,--next_date, the time when the timer starts to execute, which means immediate execution

'sysdate + 15/1440'--interval, which sets the frequency of timer execution, so that it is executed every 15 minutes

);

Commit

End

The first parameter here is the task number, which is automatically assigned by the system. You can also use isubmit to specify manually

The second parameter is the task procedure that needs to be executed. If the code is long, you can write it into a stored procedure and call it in it, such as' pro_test;' (pro_test assumes a stored procedure name).

The third parameter is the time that the automated task is executed for the first time, using sysdate if it is needed to execute immediately

The last parameter, the system specifies the next execution time based on the value of the parameter.

Declare

Jobno

Number

Begin

Dbms_job.remove (45)

Commit

End

Exec dbms_job.remove (83);-- delete a timer

-- scheduled task deletion can also be used with the following

Begin

Dbms_job.remove (70);-- id found in user_jobs

Commit

--: job can replace the value of dba_jobs.job with the value of 1198

End

Exec dbms_job.run (84);-- run a timer

Exec DBMS_JOB.BROKEN (83SYS.DIUTIL.INTIUTIL.INTERTIOL toggle BOOL (1));-stop a timer

Exec DBMS_JOB.INTERVAL (84, 'sysdate + 60 amp 1440');-- change the execution frequency of a timer to be executed every other hour

Select * from user_jobs;-- View scheduled tasks

Select * from dba_jobs_running;-- to view scheduling tasks that are being executed

Select * from dba_jobs;-- to view the completed scheduling tasks

The parameter description of the timer:

The myjob parameter is the binary_ineger returned by the Submit () procedure. This value is used to uniquely identify a job

The what parameter is the block of PL/SQL code to be executed, which refers to a stored procedure. Note the semicolon after the name.

The next_date parameter indicates when the work will be run. You can write Job without specifying this value.

The interval parameter when the work will be reperformed.

For the settings of interval, refer to the following examples:

1. Execute every minute

Interval = > TRUNC (sysdate,'mi') + 1 / (24060)

2. Execute regularly every day

For example: execution at 2: 00 a. M. every day

Interval = > TRUNC (sysdate) + 1 + 2 / (24)

3. Execution on a weekly basis

For example: every Monday at 2: 00 a. M.

Interval = > TRUNC (next_day (sysdate,2)) + 2 Universe 24-Monday, the second day of the week

4. Execute regularly every month

For example: execution at 2: 00 a. M. on the 1st of every month

Interval = > TRUNC (LAST_DAY (SYSDATE)) + 1x 2max 24

5. regular execution on a quarterly basis

For example, it is executed at 2 am on the first day of every quarter.

Interval = > TRUNC (ADD_MONTHS (SYSDATE,3),'Q') + 2max 24

6. Regular execution every half a year

For example: 2: 00 a.m. on July 1 and January 1 every year

Interval = > ADD_MONTHS (trunc (sysdate,'yyyy'), 6) + 2max 24

7. Regular execution every year

For example, it will be executed at 2: 00 a.m. on January 1 every year.

Interval = > ADD_MONTHS (trunc (sysdate,'yyyy'), 12) + 2max 24

A complete example, from creating stored procedures to scheduled tasks:

Create or replace procedure userdate_to_message

As

Begin

Insert into MESSAGE (MSG_ID,MSG_TITLE,MSG_CONTENT,MSG_FLAG,MSG_DATE,USER_NAME)

Select t. "ID", t.TITLEZOT. TITLEPER0PRANCHAR (to_date (t.DATARADATA DATAGRAPHING MMGYYYY'), 'yyyyMMdd'), t.USERID from USERDATE t

Where 1yyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyyy

And

(

(

To_char (to_date (t.timejinghe HH24VR mi'), 'HH24:mi') = to_char (sysdate+5/1440,'HH24:mi') and t.time! =' all day'

)

Or

(

To_char (sysdate,'HH24:mi') = '00virtual 00' and t.timekeeper' all day'

)

)

Commit

End

Declare

Job1 number;--job1 is a scheduled task name. It is optional and needs to be consistent with the job1 in the submit below.

Begin

Dbms_job.submit (job1, 'userdate_to_message;', sysdate,' sysdate+1/24*60')

Insert a record every 1 minute. Userdate_to_message is the name of the stored procedure.

End

At this point, the study on "how to write and view delete scheduled tasks in oracle" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.