第一步：先创建存储过程
存储过程在上一篇文章有介绍几本语法
第二步：创建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;
