//创建一个过程 create or replace procedure pro as tsql varchar2(100); begin delete from table1; commit; insert into table1 select sysdate,table2.column1,0 from table2; commit; end; / //创建一个job VARIABLE jobno number; begin DBMS_JOB.SUBMIT(:jobno, 'Pro;', SYSDATE, 'trunc(SYSDATE) + 25/24');//每天1:00执行 commit; end; / print jobno;//查看job号
sqlplus user/passwd@service @d:\aa.bat
2.编辑d:\aa.bat如下
sqlplus user/passwd@servicedelete table1;commit;insert into table1 select sysdate,table2.column1,0 from table2;commit;quit
3.把bb.bat添加到计划任务里,按设定的时间执行即可
create or replace procedure pro as
tsql varchar2(100);
begin
delete from table1;
commit;
insert into table1 select sysdate,table2.column1,0 from table2;
commit;
end;
/
//创建一个job
VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,
'Pro;',
SYSDATE, 'trunc(SYSDATE) + 25/24');//每天1:00执行
commit;
end;
/
print jobno;//查看job号
也可以用dbms_job来完成.就像上面例子
主要是设置注释的项目VARIABLE jobno number;
begin
DBMS_JOB.SUBMIT(:jobno,
'Pro;', //执行的存储过程名称
SYSDATE,
'trunc(SYSDATE) + 25/24');//每天1:00执行
commit;
end;
/
print jobno;//查看job号