作者：材女貝蒂_673_576 | 来源：互联网 | 2014-07-13 17:52
----------------------------------------------------------------------创建一个存储过程、用来给oracle作业调用--------------------------------------------------------------------
--------------------------------------------------------------------
--创建一个存储过程、用来给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 www.2cto.com
i:=1;
loop
i:=i+1;
select to_date(time_plan_lastDay,&＃39;yyyy-mm-dd&＃39;)-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 => &＃39;PLAN_AUTO_FACTTIME_PROC(&＃39;&＃39;2012-4-8&＃39;&＃39;,210,270);&＃39;,
4 next_date => sysdate,
5 interval => &＃39;sysdate+1/1440&＃39;);//调用周期设置
6 commit;
7 end;
8 /
www.2cto.com
----- 启动
begin
dbms_job.run(:jobno);
end;
-----删除
begin
dbms_job.remove(:jobno);
end;
-----停止
begin
dbms_job.broken(:jobno);
end;
---------------------------------
作者 柳暗花明
京公网安备 11010802041100号 | 京ICP备19059560号-4 | PHP1.CN 第一PHP社区 版权所有