Shulou(Shulou.com)06/01 Report--

At present, there are generally two ways to set job scheduling for scheduled tasks in oracle database, which are dbms_scheduler to create job scheduling and dbms_job to create job scheduling. Dbms_scheduler created job scheduling only after 10g, and Oracle has provided more powerful features and more flexible mechanisms / management to replace dbms_job. These two ways are described below.

1. Dbms_scheduler creates a job schedule.

-- query select * from dba_scheduler_jobs;-- to create job begin dbms_scheduler.create_job (job_name = > 'job_myjob',-- job name job_type = >' STORED_PROCEDURE',--job type job_action = > 'proc_myproc',-- stored procedure name start_date = > sysdate,-- start execution time repeat_interval = >' FREQ=DAILY;BYHOUR=9;BYMINUTE=30 BYSECOND=0',-next execution time, per day, 09:30:00 every day, execute the stored procedure proc_myproc comments = > 'Test JOB',-- comment auto_drop= > false-whether to delete automatically after job is disabled); end;-- runs begindbms_scheduler.run_job (' job_myjob'); end;-- enables begindbms_scheduler.enable ('job_myjob'); end;-- disables begindbms_scheduler.disable (' job_myjob'); end -- except begin dbms_scheduler.drop_job (job_name = > 'job_myjob',force = > TRUE); end

2.dbms_job creates a job schedule.

-- query select * from dba_jobs;select * from all_jobs;select * from user_jobs;select * from dba_jobs_running;-- to create a jobdeclare job_id number -- declare an out variable begin-- execute this stored procedure proc_myproc at 09:30:00 every day, and output a job_id variable whose value is the ID number dbms_job.submit of the job (job_id,-- parameter is the output parameter, the binary_ineger returned by the submit () procedure, which is used to uniquely identify a job. Generally define a variable to receive, you can go to the user_jobs view to query the job value. 'proc_myproc;',-- the parameter is the block of PL/SQL code to be executed, the name of the stored procedure, and so on. Sysdate,-- the parameter indicates when the work will be run. 'TRUNC (SYSDATE+1) + (9: 60: 30) / (24: 60)'-the parameter when the work will be reperformed. Print out the ID number of job dbms_output.put_line (job_id); run jobbegin on end;-- this 7 is job_id, please change it to your own corresponding job number dbms_job.run (7); end;-- enables jobbegin dbms_job.broken (7); end;-- disables jobbegin dbms_job.broken (7); end -- Delete a jobbegin dbms_job.remove (7); end

Summary: it is recommended that you use dbms_scheduler to create job scheduling. Job scheduling can regularly execute PL/SQL code blocks, stored procedures, and so on.

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.