oracle create tasks (to)

Posted on 2009-06-29 filed under: Oracle (RSS) 32 views

rn
rn
http://www.cnblogs.com/tohen/archive/2008/11/17/1335186.html

rn
First, create a PLSQL table:

rn
create table HWQY.TEST (CARNO VARCHAR2 (30), CARINFOID NUMBER)
rn

rn

rn
Second, create a PLSQL stored procedure:

rn
create or replace procedure pro_test
AS
carinfo_id number;
BEGIN
select s_CarInfoID.nextval into carinfo_id from dual;
insert into test (test.carno, test.carinfoid) values (carinfo_id,'123 ');
commit;
end pro_test;
rn

rn

rn
Third, in the SQL command window to start the task:
In SQL> execute:

rn
VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT (: jobno, 'pro_test;', SYSDATE, 'sysdate +1/24/12');
commit;
end;
/
rn

rn

rn
Fourth, the case of tracking tasks (see task queue):

rn
SQL> select job, next_date, next_sec, failures, broken from user_jobs;

JOB NEXT_DATE NEXT_SEC FAILURES BROKEN
---------- ----------- ---------------- ---------- --- ---
1 2008-2-22? 01:00:00 0 N
rn

That has a role to exist.
Implementation of select * from test t see the results of tasks regularly. As can be seen from time to time is normal for the implementation of the mandate of the.

rn

rn
Fifth, stop the timer has started tasks:

rn
Before the implementation of select job, next_date, next_sec, failures, broken from user_jobs; to view its regular task of the job.
In the SQL> statement in the implementation of the following has been launched to stop a timing tasks:

rn
begin
dbms_job.remove (1);
commit;
end;
/
rn

That the cessation of the task of job 1.
After the implementation is shown below: PL / SQL procedure successfully completed

rn

rn
6, view the process of a few:

rn
show parameter job_queue_processes;
- Must be> 0, otherwise, run the following modifications:
alter system set job_queue_processes = 5;
rn

rn

rn
Seven, and then create a task (once every 5 minutes):

rn
variable jobno number;
begin
dbms_job.submit (: jobno, 'pro_test;', sysdate, 'sysdate +1/24/12');
commit;
end;
/
rn

rn
The establishment of a regular mission, in PLSQL view JOB, it is similar to the sql statement is as follows:

rn
begin
sys.dbms_job.submit (job =>: jobno,
what => 'pro_test;',
next_date => to_date ('21-02-2008 17:37:26 ',' dd-mm-yyyy hh24: mi: ss'),
interval => 'sysdate +1/24/12');
commit;
end;
/
rn

rn
Therefore, the task of creating a complete format is:

rn
variable jobno number;
begin
sys.dbms_job.submit (job =>: jobno,
what => 'pro_test;',
next_date => to_date ('21-02-2008 17:37:26 ',' dd-mm-yyyy hh24: mi: ss'),
interval => 'sysdate +1/24/12');
commit;
end;
/
- The system will automatically assign a task No. jobno.
rn

rn

rn
8, the implementation of the results:

rn
select job, next_date, next_sec, failures, broken from user_jobs;

JOB NEXT_DATE NEXT_SEC FAILURES BROKEN
1 1 2008-2-22 AM 01:00:00 01:00:00 0 N
2 2 2008-2-21 PM 05:42:45 17:42:45 0 N
3 3 2008-2-21 PM 05:42:45 17:42:45 0 N

rn
rn rn rn rnrn

rnrn