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

It is believed that many inexperienced people don't know what to do about how to execute stored procedure tasks regularly in Oralce. Therefore, this paper summarizes the causes and solutions of the problem. Through this article, I hope you can solve this problem.

How to set the task of Oralce to execute stored procedure regularly

[1. Create a test table]

Create table getSysDate (test date)

[2. Create stored procedures to be executed on a regular basis]

Create or replace procedure insertSysDate as

Begin

Insert into getSysDate values (sysdate)

End

[3. Create a JOB, that is, create a scheduled task process to be executed]

Variable job1 number

Begin

Dbms_job.submit (: job1,'insertSysdate;',sysdate,'sysdate+1/1440')

End

How to set the task of Oralce to execute stored procedure regularly

[IV. Start JOB, that is, start and run the scheduled task process]

Begin

Dbms_job.run (: job1)

End

[5. Check the running effect]

Select to_char (test,'yyyy/mm/dd hh34:mi:ss') from getSysDate

The results are as follows:

2011-08-03 10:53:11

2011-08-03 11:05:51

[supplementary notes on some key parameters]:

DBMS_JOB.SUBMIT (: jobno,//job number

'The process to be performed by your_procedure;',//

Trunc (sysdate) + 1 Charpy 24 pound / next execution time

'trunc (sysdate) + 1 beat 24 minutes 1 pound / interval at each time

)

Delete job:dbms_job.remove (jobno)

Modify the action to be performed: job:dbms_job.what (jobno,what)

Modify the next execution time: dbms_job.next_date (job,next_date)

Modification interval: dbms_job.interval (job,interval)

Stop job:dbms.broken (job,broken,nextdate)

Start job:dbms_job.run (jobno)

Modify the value of job_queue_processes: (make sure it is not 0 or JOB will not run automatically)

You can view its value through select * from vault parameter;

Or check the following directly with show parameter job_queue_processes;:

NAME TYPE VALUE

-

Job_queue_processes integer 10

Method 1 oracle startup pfile='C:\ oracle\ ora90\ database\ initorcl.ora'

/ / this method modifies the job_queue_processes parameter of the initorcl.ora file, and then restarts the database

Method 2 minute alter system set job_queue_processes=10

/ / this method does not take effect by restarting the database, but only after the system automatically modifies the init.ora file.

After reading the above, have you mastered the method of how to execute stored procedure tasks regularly in Oralce? If you want to learn more skills or want to know more about it, you are welcome to follow the industry information channel, thank you for reading!

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.