这是我以前写的,你此基础下改吧Create Or Replace Procedure Auto_Cljs_Job (v_timestr In Varchar2,v_jobno Out Varchar2,v_msg Out Varchar2) Is/****************************************************************************** PURPOSE: 自动潮流计算Job的建立与更改 REVISIONS: Ver Date Author Description --------- ---------- --------------- ------------------------------------ 1.0 2003-10-17 Fred Zhang 1. Created this procedure. ******************************************************************************/ jobno Integer; jobid Integer; Begin Select job Into jobid From all_jobs Where what='AUTO_CLJS;'; dbms_job.Change(jobid,'AUTO_CLJS;',To_Date(To_Char(Sysdate,'yyyy-mm-dd')||' '||V_timestr,'yyyy-mm-dd hh24:mi:ss'),'trunc(sysdate,''mi'')+1'); Commit; v_jobno:=jobid; v_msg:='恭喜你!自动计算设定已经成功更改!'; Exception When No_Data_Found Then dbms_job.submit(jobid,'AUTO_CLJS;',To_Date(To_Char(Sysdate,'yyyy-mm-dd')||' '||V_timestr,'yyyy-mm-dd hh24:mi:ss'),'trunc(sysdate,''mi'')+1'); Commit; v_jobno:=jobid; v_msg:='恭喜你!自动计算已经成功设定!'; End Auto_Cljs_Job;CREATE OR REPLACE PROCEDURE Auto_Cljs_Job_Start_Stop (result OUT VARCHAR2) IS tmpVar NUMBER; /****************************************************************************** PURPOSE: 自动潮流计算Job的启动与停止 REVISIONS: Ver Date Author Description --------- ---------- --------------- ------------------------------------ 1.0 2003-10-18 Fred Zhang 1. Created this procedure. ******************************************************************************/ jobid INTEGER; job_status VARCHAR2(1); BEGIN SELECT job,broken INTO jobid,job_status FROM all_jobs WHERE what='AUTO_CLJS;'; IF job_status='N' THEN dbms_job.broken(jobid,TRUE); COMMIT; result:='恭喜你!自动计算设定已经停止!'; ELSE dbms_job.broken(jobid,FALSE); COMMIT; result:='恭喜你!自动计算设定已经开始!'; END IF; EXCEPTION WHEN NO_DATA_FOUND THEN result:='对不起,你还没有设定此Job!'; WHEN OTHERS THEN NULL; END Auto_Cljs_Job_Start_Stop; /
一个简单例子: 创建测试表 SQL> create table a(a date);表已创建。创建一个自定义过程 SQL> create or replace procedure test as 2 begin 3 insert into a values(sysdate); 4 end; 5 /过程已创建。创建JOB SQL> variable job1 number; SQL> SQL> begin 2 dbms_job.submit(:job1,'test;',sysdate,'sysdate+1/1440'); --每天1440分钟,即一分钟运行test过程一次 3 end; 4 /PL/SQL 过程已成功完成。运行JOB SQL> begin 2 dbms_job.run(:job1); 3 end; 4 /PL/SQL 过程已成功完成。SQL> select to_char(a,'yyyy/mm/dd hh24:mi:ss') 时间 from a;时间 ------------------- 2001/01/07 23:51:21 2001/01/07 23:52:22 2001/01/07 23:53:24删除JOB SQL> begin 2 dbms_job.remove(:job1); 3 end; 4 /PL/SQL 过程已成功完成。
PURPOSE: 自动潮流计算Job的建立与更改 REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2003-10-17 Fred Zhang 1. Created this procedure.
******************************************************************************/
jobno Integer;
jobid Integer;
Begin
Select job Into jobid From all_jobs Where what='AUTO_CLJS;';
dbms_job.Change(jobid,'AUTO_CLJS;',To_Date(To_Char(Sysdate,'yyyy-mm-dd')||' '||V_timestr,'yyyy-mm-dd hh24:mi:ss'),'trunc(sysdate,''mi'')+1');
Commit;
v_jobno:=jobid;
v_msg:='恭喜你!自动计算设定已经成功更改!';
Exception When No_Data_Found Then
dbms_job.submit(jobid,'AUTO_CLJS;',To_Date(To_Char(Sysdate,'yyyy-mm-dd')||' '||V_timestr,'yyyy-mm-dd hh24:mi:ss'),'trunc(sysdate,''mi'')+1');
Commit;
v_jobno:=jobid;
v_msg:='恭喜你!自动计算已经成功设定!'; End Auto_Cljs_Job;CREATE OR REPLACE PROCEDURE Auto_Cljs_Job_Start_Stop (result OUT VARCHAR2) IS
tmpVar NUMBER;
/******************************************************************************
PURPOSE: 自动潮流计算Job的启动与停止 REVISIONS:
Ver Date Author Description
--------- ---------- --------------- ------------------------------------
1.0 2003-10-18 Fred Zhang 1. Created this procedure.
******************************************************************************/
jobid INTEGER;
job_status VARCHAR2(1);
BEGIN
SELECT job,broken INTO jobid,job_status FROM all_jobs WHERE what='AUTO_CLJS;';
IF job_status='N' THEN
dbms_job.broken(jobid,TRUE);
COMMIT;
result:='恭喜你!自动计算设定已经停止!';
ELSE
dbms_job.broken(jobid,FALSE);
COMMIT;
result:='恭喜你!自动计算设定已经开始!';
END IF;
EXCEPTION
WHEN NO_DATA_FOUND THEN
result:='对不起,你还没有设定此Job!';
WHEN OTHERS THEN
NULL;
END Auto_Cljs_Job_Start_Stop;
/
SQL> create table a(a date);表已创建。创建一个自定义过程
SQL> create or replace procedure test as
2 begin
3 insert into a values(sysdate);
4 end;
5 /过程已创建。创建JOB
SQL> variable job1 number;
SQL>
SQL> begin
2 dbms_job.submit(:job1,'test;',sysdate,'sysdate+1/1440'); --每天1440分钟,即一分钟运行test过程一次
3 end;
4 /PL/SQL 过程已成功完成。运行JOB
SQL> begin
2 dbms_job.run(:job1);
3 end;
4 /PL/SQL 过程已成功完成。SQL> select to_char(a,'yyyy/mm/dd hh24:mi:ss') 时间 from a;时间
-------------------
2001/01/07 23:51:21
2001/01/07 23:52:22
2001/01/07 23:53:24删除JOB
SQL> begin
2 dbms_job.remove(:job1);
3 end;
4 /PL/SQL 过程已成功完成。
假设有一个存储过程p_test,每20分钟执行一次
解答:
1、把init<sid>.ora中如下两个参数打开
JOB_QUEUE_INTERVAL=60
JOB_QUEUE_PROCESSES=4
job_queue_keep_connections=true 然后重启一个库,如果原来已经打开了则不用这步了
2、示例,以下由sqlplus 来执行,具体参照一下相关的文档
VARIABLE jobno number;
BEGIN
DBMS_JOB.SUBMIT(:jobno,
'p_test;'
SYSDATE,'SYSDATE + 1/72');
commit;
END;DBMS_JOB.SUBMIT(:jobno,//job号
'your_procedure;',//要执行的过程
trunc(sysdate)+1/24,//下次执行时间
'trunc(sysdate)+1/24+1'//每次间隔时间
);
删除job:dbms_job.remove(jobno);
修改job:dbms_job.what(jobno,what);
修改下次执行时间:dbms_job.next_date(job,next_date);
修改间隔时间:dbms_job.interval(job,interval);
停止job:dbms.broken(job,broken,nextdate);
启动job:dbms_job.run(jobno);
例子:
VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,
'Procdemo;',
SYSDATE, 'SYSDATE + 1/720');
commit;
end;
/
我做了JOB ,可以执行,但是不自动执行,怎么回事?>--------------------
1.确认job_queue_processes的值n>0,如果不大于0请改为大于0
2.确认数据库不在restricted mode下restricted mode下不能执行job
3.确认你的JOB涉及的对象你是否有权限