oracle 存储过程 编写

1、先建立一个数组[types]：

 

 

create or replace type p_index_list as varray(35) of number;

2、编写存储过程，循环插入数据： 

 

 

--call jdxxEndingProvinceStock(p_index_list(370000,370200,410000,510000),to_char(sysdate-1,'yyyy-MM-dd'));
  create or replace procedure jdxxEndingProvinceStock(p_indexs in p_index_list, stock_date IN VARCHAR2) as
  default_stock_date VARCHAR2(20);
  p_index_num        number;
begin
  default_stock_date := stock_date;
  for x in 1 .. p_indexs.count loop
    p_index_num := p_indexs(x);
    delete JDXX_ENDING_PROVINCE_STOCK
     where stock_date = to_date(default_stock_date, 'yyyy-MM-dd')
       and p_index = p_index_num;
    commit;
  
    insert into JDXX_ENDING_PROVINCE_STOCK
      select p_index_num as p_index,
             t.pd_type_name,
             to_date(default_stock_date, 'yyyy-MM-dd') as stock_date,
             sum(t.count) as stock_count,
             sum(t.count * t.cur_cost_price) / 10000 as stock_money
        from jxc_pd t
       where t.shop_id in (select t.shop_id
                             from entp_shop t
                            where t.p_index in (select t2.P_INDEX
                                                  from BASE_PROVINCE_LIST t2
                                                 where t2.del_mark = 0
                                                 start with t2.P_INDEX = p_index_num
                                                connect by prior t2.P_INDEX = t2.PAR_INDEX))
         and t.own_sys = 1
         and t.is_del = 0
       group by t.pd_type_name;
    commit;
  end loop;

end;

 3、编写定时器，定时执行语句

 

 

grant create job to nmswxt_sw;
-- 建立job
BEGIN
 dbms_scheduler.create_job(job_name => 'SCHDL_BASE_PD_CLAZZ',
                           job_type => 'STORED_PROCEDURE',
                           job_action => 'P_BASE_PD_CLAZZ',
                           start_date => to_date('10-05-2012 01:10:01', 'dd-mm-yyyy hh24:mi:ss'),
                           repeat_interval => 'sysdate+1',
                           comments => '刷新商网产品类别定时任务');
END;

-- 启用job
BEGIN
 dbms_scheduler.enable(NAME => 'SCHDL_BASE_PD_CLAZZ');
END;

-- 运行Job
BEGIN
 dbms_scheduler.run_job(job_name => 'SCHDL_BASE_PD_CLAZZ',use_current_session => FALSE);
END;

-- 停止job
BEGIN
  dbms_scheduler.disable(NAME => 'SCHDL_BASE_PD_CLAZZ');
END;

-- 删除job
BEGIN
  dbms_scheduler.drop_job(JOB_NAME => 'SCHDL_BASE_PD_CLAZZ');
END;

 

 

 

repeat_interval 参数：

     1/24 ：一小时

     1/1440 : 一分钟

     1/(3600*24) : 一秒钟

     sysdate+1 : 每天运行一次

     sysdate+1/24 : 每一小时运行一次

     sysdate+1/1440 ： 每一分钟运行一次

     sysdate+1/(3600*24) : 每一秒钟运行一次

     sysdate+7 ： 每星期运行一次

 

以上时间间隔不能保证任务(job)的下一次运行在一个特定的时间，仅仅能够指定一个任务两次运行之间的时间间隔。

特定日期或时间的例子：

trunc(sysdate+1) : 每天午夜12点

trunc(sysdate+1)+(8*60+30)/(24*60) : 每天早上8:30

trunc(last_date(sysdate)+1) : 每个月第1天的午夜12点

next_day(trunc(sysdate),"tuesday"+12/24) : 每星期二中午12点

http://xurichusheng.iteye.com/blog/1328406