求大侠帮忙写个ORACLE的存储过程,外加一个定时任务每天0点调用它。
发布网友
发布时间:2022-04-10 05:21
我来回答
共1个回答
热心网友
时间:2022-04-10 06:51
create or replace procere p_MAKE_REPORT_DATA_TMP as
begin
insert into MAKE_REPORT_DATA_TMP
(STATE_DATE,
AREANO,
AREANAME,
CARDTYPE,
CARDTYPENAME,
CHARGETYPE,
CREATE_SEQ,
CARD_ATTR,
CARD_RANGE,
PRINT_SORT,
CARD_FACE_VALUE,
CARD_ACTUAL_VALUE,
CREATE_DATE,
ACTIVE_DATE,
PRE_STATE,
CUR_STATE,
CARD_COUNT,
CHARGE_SUM,
VALUE_SUM)
select trunc(t.state_date),
a.full_area_code areano,
a.areaname areaname, --地区
t.card_type cardtype,
p.type_name cardtypename, --卡类型
t.charge_type chargetype,
t.create_seq,
t.card_attribute,
t.card_range_type,
a.print_sort,
c.charge card_face_value, --卡面额额
c.value card_value, --卡实际金额
trunc(t.create_date) create_date, --创卡时间
trunc(t.active_date) active_date, --激活时间
t.pre_state pre_state, --前状态
t.state cur_state, --当前状态
count(t.card_id), --卡数
NVL(SUM(c.charge), 0), --面值总额
--NVL(SUM(c.value),0) --实际值总额
NVL(SUM(decode(nvl(p.business_type, 0), 1, c.charge, c.value)),
0) --实际值总额
from view_card_acct_data t,
card_type p,
card_charge_type c,
card_area a,
card_attribute b,
card_range_type d
where a.areano = t.card_area
and p.card_type = t.card_type
and c.charge_type = t.charge_type
and b.attribute_id = t.card_attribute
and d.range_type = t.card_range_type
and p.card_type <> '0'
group by trunc(t.state_date),
a.full_area_code,
a.areaname,
t.card_type,
p.type_name,
t.charge_type,
t.create_seq,
t.card_attribute,
t.card_range_type,
a.print_sort,
c.charge,
c.value,
trunc(t.create_date),
trunc(t.active_date),
t.pre_state,
t.state;
commit;
exception
when others then
rollback;
end p_MAKE_REPORT_DATA_TMP;
--- 创建定时任务JOB
declare
v_jobid number;
begin
sys.dbms_job.submit(job => v_jobid,
what => 'p_MAKE_REPORT_DATA_TMP;',
next_date => trunc(sysdate+1),
interval => 'sysdate+1');
commit;
end;