--History表定期清理
drop procedure pHISTORYclear;
create or replace procedure pHISTORYclear
as
begin
delete from history where trunc(start_time)<trunc(sysdate)-64;
commit;
end;
/--添加定时清理任务History表定期清理,每月一号的2点执行.
declare
i number:=0;
select count(*) into i from all_jobs j where j.what='pHISTORYclear;';
if i>0 then
begin
variable jb number
dbms_job.submit(:jb,'pHISTORYclear;',sysdate,'LAST_DAY(trunc(SYSDATE))+1+2/24');
end;end if;
/这样写有什么问题么?在SQL plus里执行就是好几个错误...
drop procedure pHISTORYclear;
create or replace procedure pHISTORYclear
as
begin
delete from history where trunc(start_time)<trunc(sysdate)-64;
commit;
end;
/--添加定时清理任务History表定期清理,每月一号的2点执行.
declare
i number:=0;
select count(*) into i from all_jobs j where j.what='pHISTORYclear;';
if i>0 then
begin
variable jb number
dbms_job.submit(:jb,'pHISTORYclear;',sysdate,'LAST_DAY(trunc(SYSDATE))+1+2/24');
end;end if;
/这样写有什么问题么?在SQL plus里执行就是好几个错误...
查询数据库存在的Job:
select * from user_jobs;
select * from dba_jobs;
各位帮个忙
i number:=0;
begin
select count(*) into i from all_jobs j where j.what='pHISTORYclear;';
if i>0 then
variable jb number;
begin
dbms_job.submit(:jb,'pHISTORYclear;',sysdate,'LAST_DAY(trunc(SYSDATE))+1+2/24');
commit;
end;
end if;
end;
select * from user_jobs;
select * from all_jobs;
select * from dba_jobs;
declare
i number:=0;
jb number;
begin
select count(*) into i from all_jobs j where j.what='pHISTORYclear;';
if i>0 then
begin
dbms_job.submit(:jb,'pHISTORYclear;',sysdate,'LAST_DAY(trunc(SYSDATE))+1+2/24');
commit;
end;
end if;
end;
/
PLS-00103: Encountered the symbol "JB" when expecting one of the following:
:= . ( @ % ;
ORA-06550: line 10, column 5:
PLS-00103: Encountered the symbol "IF" when expecting one of the following:
;
是哪里的问题?为什么不能使用variable ?但是如果直接写
variable jb number
begin
dbms_job.submit(:jb,'pEIT_HISTORYclear;',sysdate,'LAST_DAY(trunc(SYSDATE))+1+2/24');
end;这样就没问题,number后面没有分号
块的declare部分是变量定义,
而后在begin ... end;之间不可再定义变量.