--------------------------------------------------------------------
--创建一个存储过程、用来给oracle作业调用
--------------------------------------------------------------------
create or replace procedure PLAN_AUTO_FACTTIME_PROC
(time_plan_lastDay in varchar2,fact_cost_am in number,fact_cost_pm in number) is
i number;
previous_day varchar2(50);
begin
i:=1;
loop
i:=i+1;
select to_date(time_plan_lastDay,'yyyy-mm-dd')-i into previous_day from dual;
ddl...;
if i>6 then
exit;
end if;
end loop;
end;
-----------------------------------
-----在命令窗口下执行正确的oracle作业创建及启动
SQL> variable jobno number ;
SQL> begin
2 dbms_job.submit(job => :jobno,
3 what => 'PLAN_AUTO_FACTTIME_PROC(''2012-4-8'',210,270);',
4 next_date => sysdate,
5 interval => 'sysdate+1/1440');//调用周期设置
6 commit;
7 end;
8 /
----- 启动
begin
dbms_job.run(:jobno);
end;
-----删除
begin
dbms_job.remove(:jobno);
end;
-----停止
begin
dbms_job.broken(:jobno);
end;
---------------------------------
--创建一个存储过程、用来给oracle作业调用
--------------------------------------------------------------------
create or replace procedure PLAN_AUTO_FACTTIME_PROC
(time_plan_lastDay in varchar2,fact_cost_am in number,fact_cost_pm in number) is
i number;
previous_day varchar2(50);
begin
i:=1;
loop
i:=i+1;
select to_date(time_plan_lastDay,'yyyy-mm-dd')-i into previous_day from dual;
ddl...;
if i>6 then
exit;
end if;
end loop;
end;
-----------------------------------
-----在命令窗口下执行正确的oracle作业创建及启动
SQL> variable jobno number ;
SQL> begin
2 dbms_job.submit(job => :jobno,
3 what => 'PLAN_AUTO_FACTTIME_PROC(''2012-4-8'',210,270);',
4 next_date => sysdate,
5 interval => 'sysdate+1/1440');//调用周期设置
6 commit;
7 end;
8 /
----- 启动
begin
dbms_job.run(:jobno);
end;
-----删除
begin
dbms_job.remove(:jobno);
end;
-----停止
begin
dbms_job.broken(:jobno);
end;
---------------------------------