第一步:先创建存储过程

存储过程在上一篇文章有介绍几本语法

第二步:创建job 定时

下面案列是在工作中每天需要更新的一个脚本

案列:

创建存储过程:

create or replace procedure pr_goods_detail_f is
 pr_goods_detail_f varchar2(100);
 begin
 pr_goods_detail_f :=’truncate table ‘ || ‘kxdw.kx_goods_detail_f’;
 execute immediate pr_goods_detail_f;
 insert into  kxdw.kx_goods_detail_f  select * from (
(
 SELECT
  koi.order_sn order_sn,
  to_number(to_char(to_date(substr(koi.add_time, 1, 10), ‘yyyy-mm-dd’), ‘yyyymmdd’)) date_wid,
  koi.order_status order_status,
  kog.goods_sn goods_sn,
  kog.goods_price goods_price,
  kog.goods_number goods_number,
  kog.goods_price * kog.goods_number goods_amount ,
    decode(
     (koi.goods_amount ) * koi.koi.money_paid,
     0,
     0,
     round(
      (
       (kog.goods_price * kog.goods_number) / (koi.goods_amount )
      ) * koi.money_paid,
      4
     )
    ) money_paid,
    decode(
     (koi.goods_amount ) * shipping_fee,
     0,
     0,
     round(
      (
       (kog.goods_price * kog.goods_number) / (koi.goods_amount )
      ) * shipping_fee,
      4
     )
    ) shipping_fee,
   decode(
     (koi.goods_amount ) * bonus,
     0,
     0,
     ROUND(
      (
       (kog.goods_price * kog.goods_number) / (koi.goods_amount )
      ) * bonus,
      4
     )
    ) bonus,
     decode(
     (koi.goods_amount ) * surplus,
     0,
     0,
     ROUND(
      (
       (kog.goods_price * kog.goods_number) / (koi.goods_amount )
      ) * surplus,
      4
     )
    ) surplus,
     decode(
     (koi.goods_amount ) * order_amount,
     0,
     0,
     ROUND(
      (
       (kog.goods_price * kog.goods_number) / (koi.goods_amount )
      ) * order_amount,
      4
     )
    ) order_amount,
    koi.pay_id,  koi.shipping_id,koi.shipping_status,koi.pay_status,koi.user_id
  FROM
    kxnc_order_goods kog
  LEFT JOIN kxnc_order_info koi ON kog.order_id = koi.order_id
  LEFT JOIN kxnc_order_action koa ON koi.order_id = koa.order_id
  WHERE
    koi.pay_status = 2 — 已付款
  AND koi.referer <> ‘微信来源’
)
UNION
  (
    SELECT
      koi.order_sn order_sn,
      to_number(to_char(to_date(substr(koi.add_time, 1, 10), ‘yyyy-mm-dd’), ‘yyyymmdd’)) date_wid,
      koi.order_status order_status,
      kog.goods_sn goods_sn,
      kog.goods_price goods_price,
      kog.goods_number goods_number,
      kog.goods_price * kog.goods_number goods_amount ,
        decode(
     (koi.goods_amount ) * koi.money_paid,
     0,
     0,
     round(
      (
       (kog.goods_price * kog.goods_number) / (koi.goods_amount )
      ) * koi.money_paid,
      4
     )
    ) money_paid,
        decode(
     (koi.goods_amount ) * shipping_fee,
     0,
     0,
     round(
      (
       (kog.goods_price * kog.goods_number) / (koi.goods_amount )
      ) * shipping_fee,
      4
     )
    ) shipping_fee,
       decode(
     (koi.goods_amount ) * bonus,
     0,
     0,
     ROUND(
      (
       (kog.goods_price * kog.goods_number) / (koi.goods_amount )
      ) * bonus,
      4
     )
    ) bonus,
       decode(
     (koi.goods_amount ) * surplus,
     0,
     0,
     ROUND(
      (
       (kog.goods_price * kog.goods_number) / (koi.goods_amount )
      ) * surplus,
      4
     )
    ) surplus,
       decode(
     (koi.goods_amount ) * order_amount,
     0,
     0,
     ROUND(
      (
       (kog.goods_price * kog.goods_number) / (koi.goods_amount )
      ) * order_amount,
      4
     )
    ) order_amount,
    koi.pay_id,koi.shipping_id,koi.shipping_status,koi.pay_status,koi.user_id
    FROM
      kxnc_order_goods kog
    LEFT JOIN kxnc_order_info koi ON kog.order_id = koi.order_id
    WHERE
      koi.pay_status = 0
    AND koi.pay_id = 4
    AND koi.referer <> ‘微信来源’
    AND koi.order_status IN (0, 1,5)
  )
UNION
  (
    SELECT
      aa.order_sn,
      to_number(to_char(to_date(substr(bb.add_time, 1, 10), ‘yyyy-mm-dd’), ‘yyyymmdd’)) date_wid,
      bb.order_status,
     NULL ,NULL ,NULL ,NULL ,-1*aa.amount  money_paid,NULL ,NULL ,NULL ,NULL,NULL,NULL ,NULL ,NULL,NULL
    FROM
      (
        SELECT
         –add_time,
          SUBSTR (ADMIN_NOTE, – 13) AS order_sn,
          amount
        FROM
          KXNC_USER_ACCOUNT
      ) aa,
      kxnc_order_info bb,
      kxnc_order_action cc
    WHERE
      aa.order_sn = bb.order_sn
    AND cc.order_id = bb.order_id
    AND cc.order_status = 2
  ) );
   dbms_output.put_line(‘插入新纪录成功!’);
   commit;
end pr_goods_detail_f;
创建job

declare
goods_detail_job number;
begin
dbms_job.submit(goods_detail_job,’pr_goods_detail_f;’,sysdate,’sysdate+1/24′);
end;

查询job

select * from dba_jobs_running;

运行job

begin
dbms_job.run(29);
end;

select * from dba_jobs;

删除job

begin
dbms_job.remove(30);
end;