ORACLEno1存储过程插入更新表数据

作者:网络    软件教程库   2020-05-22

create or replace procedure sp_cust_main_data_yx(instrdate in varchar2,
os_err_msg out varchar2) as

begin
--1、清空临时表数据
execute immediate ‘truncate table data_sale_day_item‘;
execute immediate ‘truncate table data_sale_day_tp‘;
execute immediate ‘truncate table data_sale_day‘;
execute immediate ‘truncate table data_sale_month_tp‘;
execute immediate ‘truncate table data_sale_month‘;

--2、跑出结果到临时表
--交易明细数据
insert into data_sale_day_item
select to_date(a.crmpostdat, ‘yyyymmdd‘) p_date,
to_date(decode(a."/bic/ztcrmc01",
‘00000000‘,
null,
‘ ‘,
null,
a."/bic/ztcrmc01"),
‘yyyymmdd‘) order_date,
a."crm_itmtyp" order_type,
a."/bic/zkunnr_l1" order_channel_l1,
d.txtsh channel_l1_name, --
a."/bic/zkunnr_l2" order_channel_l2,
e.txtsh channel_l2_name, --
a.crm_obj_id crm_order_id,
a."/bic/ztcrmc04" order_no,
a.crm_endcst cust_no,
a."/bic/ztcmc016" cust_grade,
ltrim(a."/bic/zmaterial", ‘0‘) item_code,
c.txtmd item_name,
ltrim(b."/bic/zmatdl", ‘0‘) cate_b, --大类
ltrim(b."/bic/zmatzl", ‘0‘) cate_m, --中类
ltrim(b."/bic/zmatxl", ‘0‘) cate_s, --小类
ltrim(b.matl_group, ‘0‘) cate_d, --细类
a."/bic/zeamc001" unit_no,
to_date(decode(a."/bic/zeamc011" || a."/bic/zeamc013",
‘00000000000000‘,
null,
‘ 00000‘,
null,
a."/bic/zeamc011" || a."/bic/zeamc013"),
‘yyyymmddhh24miss‘) unit_begin_time,
to_date(decode(a."/bic/zeamc012" || a."/bic/zeamc014",
‘00000000000000‘,
null,
‘ 00000‘,
null,
a."/bic/zeamc012" || a."/bic/zeamc014"),
‘yyyymmddhh24miss‘) unit_end_time,
ltrim(a."/bic/ztcmc020", ‘0‘) addr_no,
h."/bic/ztcmc017" province, --
h1.txtsh province_name, --
decode(a."/bic/zcrmd015", ‘x‘, 1, 0) is_related_sale,
decode(a."/bic/zcrmd016", ‘x‘, 1, 0) is_ivr,
a."/bic/zcrmd018" order_source,
a."/bic/zeamc027" md_code,
substr(a."/bic/zpst_tim", 1, 2) sale_period,
--总订购
decode(a.crm_prctyp, ‘za01‘, 1, ‘za02‘, 1, 0) *
a."/bic/zamk0010" tot_order_qty,
decode(a.crm_prctyp, ‘za01‘, 1, ‘za02‘, 1, 0) *
decode(a.crm_itmtyp, ‘tann‘, 0, 1) * a."/bic/zamk0011" tot_order_amt,
decode(a.crm_prctyp, ‘za01‘, 1, ‘za02‘, 1, 0) *
decode(a.crm_itmtyp, ‘tann‘, 0, 1) * a."crm_srvkb" tot_order_cost,
--净订购
decode(a.crm_prctyp, ‘za01‘, 1, ‘za02‘, 1, ‘zcr1‘, -1, 0) *
a."/bic/zamk0010" net_order_qty,
decode(a.crm_prctyp, ‘za01‘, 1, ‘za02‘, 1, ‘zcr1‘, -1, 0) *
decode(a.crm_itmtyp, ‘tann‘, 0, 1) * a."/bic/zamk0011" net_order_amt,
decode(a.crm_prctyp, ‘za01‘, 1, ‘za02‘, 1, ‘zcr1‘, -1, 0) *
decode(a.crm_itmtyp, ‘tann‘, 0, 1) * a."crm_srvkb" net_order_cost,
--有效订购
decode(a.crm_prctyp,
‘za01‘,
1,
‘za02‘,
1,
‘zcr1‘,
-1,
‘zb01‘,
-1,
0) * a."/bic/zamk0010" effect_order_qty,
decode(a.crm_prctyp,
‘za01‘,
1,
‘za02‘,
1,
‘zcr1‘,
-1,
‘zb01‘,
-1,
0) * decode(a.crm_itmtyp, ‘tann‘, 0, 1) *
a."/bic/zamk0011" effect_order_amt,
decode(a.crm_prctyp,
‘za01‘,
1,
‘za02‘,
1,
‘zcr1‘,
-1,
‘zb01‘,
-1,
0) * decode(a.crm_itmtyp, ‘tann‘, 0, 1) *
a."/bic/zcrmk009" effect_price_amt,
decode(a.crm_prctyp,
‘za01‘,
1,
‘za02‘,
1,
‘zcr1‘,
-1,
‘zb01‘,
-1,
0) * decode(a.crm_itmtyp, ‘tann‘, 0, 1) * a."crm_srvkb" effect_order_cost,
decode(a.crm_prctyp,
‘za01‘,
1,
‘za02‘,
1,
‘zcr1‘,
-1,
‘zb01‘,
-1,
0) * decode(a.crm_itmtyp, ‘tann‘, 0, 1) *
a."/bic/zcrmk006" effect_discount_amt,
--取消
decode(a.crm_prctyp, ‘zcr1‘, 1, 0) * a."/bic/zamk0010" order_cancel_qty,
decode(a.crm_prctyp, ‘zcr1‘, 1, 0) *
decode(a.crm_itmtyp, ‘tann‘, 0, 1) * a."/bic/zamk0011" order_cancel_amt,
--拒收
decode(a.crm_prctyp,
‘zb01‘,
decode(a."/bic/ztcmc008", ‘20‘, 1, 0),
0) * a."/bic/zamk0010" order_reject_qty,
decode(a.crm_prctyp,
‘zb01‘,
decode(a."/bic/ztcmc008", ‘20‘, 1, 0),
0) * decode(a.crm_itmtyp, ‘tann‘, 0, 1) *
a."/bic/zamk0011" order_reject_amt,
--退货
decode(a.crm_prctyp,
‘zb01‘,
decode(a."/bic/ztcmc008", ‘20‘, 0, 1),
0) * a."/bic/zamk0010" order_refund_qty,
decode(a.crm_prctyp,
‘zb01‘,
decode(a."/bic/ztcmc008", ‘20‘, 0, 1),
0) * decode(a.crm_itmtyp, ‘tann‘, 0, 1) *
a."/bic/zamk0011" order_refund_amt,
--拒收取消
0 order_reject_cancel_qty,
0 order_reject_cancel_amt,
--退货取消
0 order_refund_cancel_qty,
0 order_refund_cancel_amt
from itf_sap.sap_bic_aztcrd00100 a,
itf_sap.sap_bic_pzmaterial b,
itf_sap.sap_bic_tzmaterial c,
itf_sap."/bic/ohzo_mscode" d,
itf_sap."/bic/ohzo_msgb" e,
itf_sap."/bic/ohzo_pzhen" h,
itf_sap."/bic/ohzo_tsheng" h1
where a.crmpostdat between ‘20060301‘ and instrdate
and nvl(a."/bic/zcrmd001", ‘ ‘) ‘ta10‘ --剔除预订购
and nvl(a."/bic/zcrmd048", ‘ ‘) ‘tann‘ --剔除赠品
and a."/bic/zcrmd199" is null --剔除预订购
and a."/bic/zmaterial" = b."/bic/zmaterial"(+)
and a."/bic/zmaterial" = c."/bic/zmaterial"(+)
and a."/bic/zkunnr_l1" = d."/bic/zkunnr_l1"(+)
and d.langu(+) = ‘1‘
and a."/bic/zkunnr_l2" = e."/bic/zkunnr_l2"(+)
and e.langu(+) = ‘1‘
and a."/bic/ztcmc020" = h."/bic/ztcmc020"(+)
and h."/bic/ztcmc017" = h1."/bic/ztcmc017"(+)
union all
select to_date(a."/bic/ztcmc021", ‘yyyymmdd‘) 过账日期,
to_date(decode(a."/bic/ztcrmc01",
‘00000000‘,
null,
‘ ‘,
null,
a."/bic/ztcrmc01"),
‘yyyymmdd‘) crm订购日期_权责制,
a."crm_itmtyp" 项目交易类型,
a."/bic/zkunnr_l1" order_channel_l1,
d.txtsh channel_l1_name, --
a."/bic/zkunnr_l2" order_channel_l2,
e.txtsh channel_l2_name, --
a.crm_obj_id 交易编号,
a."/bic/ztcrmc04" "crm订单编号_权责制",
a.crm_endcst 会员,
a."/bic/ztcmc016" 会员等级,
ltrim(a."/bic/zmaterial", ‘0‘) item_code,
c.txtmd item_name,
ltrim(b."/bic/zmatdl", ‘0‘) cate_b, --大类
ltrim(b."/bic/zmatzl", ‘0‘) cate_m, --中类
ltrim(b."/bic/zmatxl", ‘0‘) cate_s, --小类
ltrim(b.matl_group, ‘0‘) cate_d, --细类
a."/bic/zeamc001" unit_no,
to_date(decode(a."/bic/zeamc011" || a."/bic/zeamc013",
‘00000000000000‘,
null,
‘ 00000‘,
null,
a."/bic/zeamc011" || a."/bic/zeamc013"),
‘yyyymmddhh24miss‘) unit_begin_time,
to_date(decode(a."/bic/zeamc012" || a."/bic/zeamc014",
‘00000000000000‘,
null,
‘ 00000‘,
null,
a."/bic/zeamc012" || a."/bic/zeamc014"),
‘yyyymmddhh24miss‘) unit_end_time,
ltrim(a."/bic/ztcmc020", ‘0‘) addr_no,
h."/bic/ztcmc017" province, --
h1.txtsh province_name, --
decode(a."/bic/zcrmd015", ‘x‘, 1, 0) is_related_sale,
decode(a."/bic/zcrmd016", ‘x‘, 1, 0) is_ivr,
a."/bic/zcrmd018" order_source,
a."/bic/zeamc027" md_code,
substr(a."/bic/zpst_tim", 1, 2) sale_period,
--总订购
0 总订购件数,
0 总订购金额,
0 总订购成本,
--净订购
0 净订购件数,
0 净订购金额,
0 净订购成本,
--有效订购
decode(a.crm_prctyp, ‘zb01‘, 1, 0) * a."/bic/zamk0010" 有效订购件数,
decode(a.crm_prctyp, ‘zb01‘, 1, 0) *
decode(a.crm_itmtyp, ‘tann‘, 0, 1) * a."/bic/zamk0011" 有效订购金额,
decode(a.crm_prctyp, ‘zb01‘, 1, 0) *
decode(a.crm_itmtyp, ‘tann‘, 0, 1) * a."/bic/zcrmk009" 有效售价金额,
decode(a.crm_prctyp, ‘zb01‘, 1, 0) *
decode(a.crm_itmtyp, ‘tann‘, 0, 1) * a."crm_srvkb" 有效订购成本,
decode(a.crm_itmtyp, ‘tann‘, 0, 1) * a."/bic/zcrmk006" "有效折扣金额",
--取消
0 取消订购件数,
0 取消订购金额,
--拒收
0 拒收订购件数,
0 拒收订购金额,
--退货
0 退货订购件数,
0 退货订购金额,
--拒收取消
decode(a.crm_prctyp,
‘zb01‘,
decode(a."/bic/ztcmc008", ‘20‘, 1, 0),
0) * a."/bic/zamk0010" 拒收取消订购件数,
decode(a.crm_prctyp,
‘zb01‘,
decode(a."/bic/ztcmc008", ‘20‘, 1, 0),
0) * decode(a.crm_itmtyp, ‘tann‘, 0, 1) *
a."/bic/zamk0011" 拒收取消订购金额,
--退货取消
decode(a.crm_prctyp,
‘zb01‘,
decode(a."/bic/ztcmc008", ‘20‘, 0, 1),
0) * a."/bic/zamk0010" 退货取消订购件数,
decode(a.crm_prctyp,
‘zb01‘,
decode(a."/bic/ztcmc008", ‘20‘, 0, 1),
0) * decode(a.crm_itmtyp, ‘tann‘, 0, 1) *
a."/bic/zamk0011" 退货取消订购金额
from itf_sap.sap_bic_aztcrd00100 a,
itf_sap.sap_bic_pzmaterial b,
itf_sap.sap_bic_tzmaterial c,
itf_sap."/bic/ohzo_mscode" d,
itf_sap."/bic/ohzo_msgb" e,
itf_sap."/bic/ohzo_pzhen" h,
itf_sap."/bic/ohzo_tsheng" h1
where a."/bic/ztcmc021" between ‘20060301‘ and instrdate
and nvl(a."/bic/zcrmd001", ‘ ‘) ‘ta10‘ --剔除预订购
and nvl(a."/bic/zcrmd048", ‘ ‘) ‘tann‘ --剔除赠品
and a."/bic/zcrmd199" is null --剔除预订购
and a."/bic/zmaterial" = b."/bic/zmaterial"(+)
and a."/bic/zmaterial" = c."/bic/zmaterial"(+)
and a."/bic/zkunnr_l1" = d."/bic/zkunnr_l1"(+)
and d.langu(+) = ‘1‘
and a."/bic/zkunnr_l2" = e."/bic/zkunnr_l2"(+)
and e.langu(+) = ‘1‘
and a."/bic/ztcmc020" = h."/bic/ztcmc020"(+)
and h."/bic/ztcmc017" = h1."/bic/ztcmc017"(+);
--按天统计临时表
insert into data_sale_day_tp
select to_char(t.p_date, ‘yyyymmdd‘) p_date,
t.order_channel_l1,
t.channel_l1_name,
t.order_channel_l2,
t.channel_l2_name,
t.cust_grade,
ltrim(t.cate_b, ‘0‘) cate_b,
t.province,
t.province_name,
sum(t.tot_order_qty) tot_order_qty,
sum(t.tot_order_amt) tot_order_amt,
sum(t.tot_order_cost) tot_order_cost,
sum(t.net_order_qty) net_order_qty,
sum(t.net_order_amt) net_order_amt,
sum(t.net_order_cost) net_order_cost,
sum(t.effect_order_qty) effect_order_qty,
sum(t.effect_order_amt) effect_order_amt,
sum(t.effect_price_amt) effect_price_amt,
sum(t.effect_order_cost) effect_order_cost,
sum(t.effect_discount_amt) effect_discount_amt,
sum(t.order_cancel_qty) order_cancel_qty,
sum(t.order_cancel_amt) order_cancel_amt,
sum(t.order_reject_qty) order_reject_qty,
sum(t.order_reject_amt) order_reject_amt,
sum(t.order_refund_qty) order_refund_qty,
sum(t.order_refund_amt) order_refund_amt,
sum(t.order_reject_cancel_qty) order_reject_cancel_qty,
sum(t.order_reject_cancel_amt) order_reject_cancel_amt,
sum(t.order_refund_cancel_qty) order_refund_cancel_qty,
sum(t.order_refund_cancel_amt) order_refund_cancel_amt
from data_sale_day_item t
group by to_char(t.p_date, ‘yyyymmdd‘),
t.order_channel_l1,
t.channel_l1_name,
t.order_channel_l2,
t.channel_l2_name,
t.cust_grade,
ltrim(t.cate_b, ‘0‘),
t.province,
t.province_name;
--按天统计数据表
insert into data_sale_day
select a.*,
b.tot_order_qty tot_order_qty_l1,
b.tot_order_amt tot_order_amt_l1,
b.tot_order_cost tot_order_cost_l1,
b.net_order_qty net_order_qty_l1,
b.net_order_amt net_order_amt_l1,
b.net_order_cost net_order_cost_l1,
b.effect_order_qty effect_order_qty_l1,
b.effect_order_amt effect_order_amt_l1,
b.effect_price_amt effect_price_amt_l1,
b.effect_order_cost effect_order_cost_l1,
b.effect_discount_amt effect_discount_amt_l1,
b.order_cancel_qty order_cancel_qty_l1,
b.order_cancel_amt order_cancel_amt_l1,
b.order_reject_qty order_reject_qty_l1,
b.order_reject_amt order_reject_amt_l1,
b.order_refund_qty order_refund_qty_l1,
b.order_refund_amt order_refund_amt_l1,
b.order_reject_cancel_qty order_reject_cancel_qty_l1,
b.order_reject_cancel_amt order_reject_cancel_amt_l1,
b.order_refund_cancel_qty order_refund_cancel_qty_l1,
b.order_refund_cancel_amt order_refund_cancel_amt_l1
from data_sale_day_tp a, data_sale_day_tp b
where ((substr(a.p_date, 1, 4) - 1) || substr(a.p_date, 5, 4)) =
b.p_date(+)
and a.order_channel_l1 = b.order_channel_l1(+)
and a.channel_l1_name = b.channel_l1_name(+)
and a.order_channel_l2 = b.order_channel_l2(+)
and a.channel_l2_name = b.channel_l2_name(+)
and a.cust_grade = b.cust_grade(+)
and a.cate_b = b.cate_b(+)
and a.province = b.province(+)
and a.province_name = b.province_name(+);
--按月统计临时表
insert into data_sale_month_tp
select substr(a.p_date, 1, 6) p_month,
a.order_channel_l1,
a.channel_l1_name,
a.order_channel_l2,
a.channel_l2_name,
a.cust_grade,
a.cate_b,
a.province,
a.province_name,
sum(tot_order_qty) tot_order_qty,
sum(tot_order_amt) tot_order_amt,
sum(tot_order_cost) tot_order_cost,
sum(net_order_qty) net_order_qty,
sum(net_order_amt) net_order_amt,
sum(net_order_cost) net_order_cost,
sum(effect_order_qty) effect_order_qty,
sum(effect_order_amt) effect_order_amt,
sum(effect_price_amt) effect_price_amt,
sum(effect_order_cost) effect_order_cost,
sum(effect_discount_amt) effect_discount_amt,
sum(order_cancel_qty) order_cancel_qty,
sum(order_cancel_amt) order_cancel_amt,
sum(order_reject_qty) order_reject_qty,
sum(order_reject_amt) order_reject_amt,
sum(order_refund_qty) order_refund_qty,
sum(order_refund_amt) order_refund_amt,
sum(order_reject_cancel_qty) order_reject_cancel_qty,
sum(order_reject_cancel_amt) order_reject_cancel_amt,
sum(order_refund_cancel_qty) order_refund_cancel_qty,
sum(order_refund_cancel_amt) order_refund_cancel_amt,
sum(tot_order_qty) / b.date_cnt tot_order_qty_avg,
sum(tot_order_amt) / b.date_cnt tot_order_amt_avg,
sum(tot_order_cost) / b.date_cnt tot_order_cost_avg,
sum(net_order_qty) / b.date_cnt net_order_qty_avg,
sum(net_order_amt) / b.date_cnt net_order_amt_avg,
sum(net_order_cost) / b.date_cnt net_order_cost_avg,
sum(effect_order_qty) / b.date_cnt effect_order_qty_avg,
sum(effect_order_amt) / b.date_cnt effect_order_amt_avg,
sum(effect_price_amt) / b.date_cnt effect_price_amt_avg,
sum(effect_order_cost) / b.date_cnt effect_order_cost_avg,
sum(effect_discount_amt) / b.date_cnt effect_discount_amt_avg,
sum(order_cancel_qty) / b.date_cnt order_cancel_qty_avg,
sum(order_cancel_amt) / b.date_cnt order_cancel_amt_avg,
sum(order_reject_qty) / b.date_cnt order_reject_qty_avg,
sum(order_reject_amt) / b.date_cnt order_reject_amt_avg,
sum(order_refund_qty) / b.date_cnt order_refund_qty_avg,
sum(order_refund_amt) / b.date_cnt order_refund_amt_avg,
sum(order_reject_cancel_qty) / b.date_cnt order_reject_cancel_qty_avg,
sum(order_reject_cancel_amt) / b.date_cnt order_reject_cancel_amt_avg,
sum(order_refund_cancel_qty) / b.date_cnt order_refund_cancel_qty_avg,
sum(order_refund_cancel_amt) / b.date_cnt order_refund_cancel_amt_avg
from data_sale_day_tp a,
(select substr(p_date, 1, 6) p_month,
count(distinct substr(p_date, 7, 2)) date_cnt
from data_sale_day_tp
group by substr(p_date, 1, 6)) b
where substr(a.p_date, 1, 6) = b.p_month(+)
group by substr(a.p_date, 1, 6),
a.order_channel_l1,
a.channel_l1_name,
a.order_channel_l2,
a.channel_l2_name,
a.cust_grade,
a.cate_b,
a.province,
a.province_name,
b.date_cnt;
--按月统计数据表
insert into data_sale_month
select a.*,
b.tot_order_qty tot_order_qty_l1,
b.tot_order_amt tot_order_amt_l1,
b.tot_order_cost tot_order_cost_l1,
b.net_order_qty net_order_qty_l1,
b.net_order_amt net_order_amt_l1,
b.net_order_cost net_order_cost_l1,
b.effect_order_qty effect_order_qty_l1,
b.effect_order_amt effect_order_amt_l1,
b.effect_price_amt effect_price_amt_l1,
b.effect_order_cost effect_order_cost_l1,
b.effect_discount_amt effect_discount_amt_l1,
b.order_cancel_qty order_cancel_qty_l1,
b.order_cancel_amt order_cancel_amt_l1,
b.order_reject_qty order_reject_qty_l1,
b.order_reject_amt order_reject_amt_l1,
b.order_refund_qty order_refund_qty_l1,
b.order_refund_amt order_refund_amt_l1,
b.order_reject_cancel_qty order_reject_cancel_qty_l1,
b.order_reject_cancel_amt order_reject_cancel_amt_l1,
b.order_refund_cancel_qty order_refund_cancel_qty_l1,
b.order_refund_cancel_amt order_refund_cancel_amt_l1,
b.tot_order_qty_avg tot_order_qty_avg_l1,
b.tot_order_amt_avg tot_order_amt_avg_l1,
b.tot_order_cost_avg tot_order_cost_avg_l1,
b.net_order_qty_avg net_order_qty_avg_l1,
b.net_order_amt_avg net_order_amt_avg_l1,
b.net_order_cost_avg net_order_cost_avg_l1,
b.effect_order_qty_avg effect_order_qty_avg_l1,
b.effect_order_amt_avg effect_order_amt_avg_l1,
b.effect_price_amt_avg effect_price_amt_avg_l1,
b.effect_order_cost_avg effect_order_cost_avg_l1,
b.effect_discount_amt_avg effect_discount_amt_avg_l1,
b.order_cancel_qty_avg order_cancel_qty_avg_l1,
b.order_cancel_amt_avg order_cancel_amt_avg_l1,
b.order_reject_qty_avg order_reject_qty_avg_l1,
b.order_reject_amt_avg order_reject_amt_avg_l1,
b.order_refund_qty_avg order_refund_qty_avg_l1,
b.order_refund_amt_avg order_refund_amt_avg_l1,
b.order_reject_cancel_qty_avg order_reject_cancel_qty_avg_l1,
b.order_reject_cancel_amt_avg order_reject_cancel_amt_avg_l1,
b.order_refund_cancel_qty_avg order_refund_cancel_qty_avg_l1,
b.order_refund_cancel_amt_avg order_refund_cancel_amt_avg_l1,
c.tot_order_qty tot_order_qty_l2,
c.tot_order_amt tot_order_amt_l2,
c.tot_order_cost tot_order_cost_l2,
c.net_order_qty net_order_qty_l2,
c.net_order_amt net_order_amt_l2,
c.net_order_cost net_order_cost_l2,
c.effect_order_qty effect_order_qty_l2,
c.effect_order_amt effect_order_amt_l2,
c.effect_price_amt effect_price_amt_l2,
c.effect_order_cost effect_order_cost_l2,
c.effect_discount_amt effect_discount_amt_l2,
c.order_cancel_qty order_cancel_qty_l2,
c.order_cancel_amt order_cancel_amt_l2,
c.order_reject_qty order_reject_qty_l2,
c.order_reject_amt order_reject_amt_l2,
c.order_refund_qty order_refund_qty_l2,
c.order_refund_amt order_refund_amt_l2,
c.order_reject_cancel_qty order_reject_cancel_qty_l2,
c.order_reject_cancel_amt order_reject_cancel_amt_l2,
c.order_refund_cancel_qty order_refund_cancel_qty_l2,
c.order_refund_cancel_amt order_refund_cancel_amt_l2,
c.tot_order_qty_avg tot_order_qty_avg_l2,
c.tot_order_amt_avg tot_order_amt_avg_l2,
c.tot_order_cost_avg tot_order_cost_avg_l2,
c.net_order_qty_avg net_order_qty_avg_l2,
c.net_order_amt_avg net_order_amt_avg_l2,
c.net_order_cost_avg net_order_cost_avg_l2,
c.effect_order_qty_avg effect_order_qty_avg_l2,
c.effect_order_amt_avg effect_order_amt_avg_l2,
c.effect_price_amt_avg effect_price_amt_avg_l2,
c.effect_order_cost_avg effect_order_cost_avg_l2,
c.effect_discount_amt_avg effect_discount_amt_avg_l2,
c.order_cancel_qty_avg order_cancel_qty_avg_l2,
c.order_cancel_amt_avg order_cancel_amt_avg_l2,
c.order_reject_qty_avg order_reject_qty_avg_l2,
c.order_reject_amt_avg order_reject_amt_avg_l2,
c.order_refund_qty_avg order_refund_qty_avg_l2,
c.order_refund_amt_avg order_refund_amt_avg_l2,
c.order_reject_cancel_qty_avg order_reject_cancel_qty_avg_l2,
c.order_reject_cancel_amt_avg order_reject_cancel_amt_avg_l2,
c.order_refund_cancel_qty_avg order_refund_cancel_qty_avg_l2,
c.order_refund_cancel_amt_avg order_refund_cancel_amt_avg_l2
from data_sale_month_tp a, data_sale_month_tp b, data_sale_month_tp c
where ((substr(a.p_month, 1, 4) - 1) || substr(a.p_month, 5, 2)) =
b.p_month(+)
and a.order_channel_l1 = b.order_channel_l1(+)
and a.channel_l1_name = b.channel_l1_name(+)
and a.order_channel_l2 = b.order_channel_l2(+)
and a.channel_l2_name = b.channel_l2_name(+)
and a.cust_grade = b.cust_grade(+)
and a.cate_b = b.cate_b(+)
and a.province = b.province(+)
and a.province_name = b.province_name(+)
and to_char(add_months(to_date(a.p_month, ‘yyyymm‘), -1), ‘yyyymm‘) =
c.p_month(+)
and a.order_channel_l1 = c.order_channel_l1(+)
and a.channel_l1_name = c.channel_l1_name(+)
and a.order_channel_l2 = c.order_channel_l2(+)
and a.channel_l2_name = c.channel_l2_name(+)
and a.cust_grade = c.cust_grade(+)
and a.cate_b = c.cate_b(+)
and a.province = c.province(+)
and a.province_name = c.province_name(+);

commit;

exception
when others then
os_err_msg := to_char(‘调用失败,请检查! ‘ || sqlerrm);
rollback;
return;

end sp_cust_main_data_yx;

oracle no1 存储过程插入更新表数据

原文地址:http://www.cnblogs.com/fafawaxing/p/4481296.html

软件教程库 该篇文章地址:https://www.itjcku.com/9999/1091569.html

阅读全部内容


Tags:存储过程进程插入更新

返回首页



推荐内容

win使用telnet到ubuntu下vim显示中文为乱码的解决方法~

1.几个路径: ubuntu: /etc/default/locale 相当于 centos:/etc/sysconfi ...

mediawiki的安装与配置

apache的配置: 1. 开启php module 查看mods-enabled/php5.load 是否存在,不存在 ...

C#随机数

c#中有个random类可以非常方便的产生一个随机数,但是在使用中你会发现这个类并不是特别好用,偶尔会一直提供同一 ...

shell-入门

dos2unix start.sh 在windows系统下编写的shell脚本 在liunx下进行一次转译,否则会爆出 ...

团队开发——软件需求分析报告(HelloWorld团队)

一.项目名称超级迷宫二.设计背景随着生活节奏加快,游戏更新速度的加快,游戏大同小异缺少新颖度,同时为了满足多游戏的结合, ...

MSSQl事务的使用

注册了博客这么久没还好好写过文章呢?昨天看了我同学的博客,写的很好,每次用到以前的知识我就会去看看她写的博客,现在 ...

NSDictionary转化为NSData

之前自己写了nsdictionary转化为nsdata的一种方法如下 nsmutabledata *data = ...

nginx关键字

反向代理: proxy_pass: 负载均衡: upstream 负载均衡,有四种方式:     a/ 轮询 ...

Java设计模式之简单工厂模式

设计模式之简单工厂举例: l简单工厂模式概述 又叫静态工厂方法模式,它定义一个具体的工厂类负责创建一些类的实例 l优点 ...

解题报告之POJ3686TheWindy's

解题报告 之 poj3686 the windy‘s description the windy‘s i ...

【ThinkingInJava】18、关于java中的闭包与回调

/** * 书本:《thinking in java》 * 功能:关于java中的闭包与回调,这里回调区别于c++,c+ ...

TreasureHunting (hdu3468二分匹配+bfs最短路径)

treasure hunting time limit: 4000/2000 ms (java/others)mem ...

【ThinkingInJava】19、控制框架的实现

/** * 书本:《thinking in java》 * 功能:控制框架的实现 * 文件:event.java * 时 ...

【ThinkingInJava】20、控制框架的使用(初始化系统使用)

/** * 书本:《thinking in java》 * 功能:控制框架的实现,1、控制框架的完整实现是由单个的类创建 ...

最小汉密尔顿回路问题状态压缩dp

给定n个顶点做成的图,要求从顶点0出发经过所有点一次然后回到0点的一条权#20540;之和最小的一条路的权#20540; ...

POJ1966.CableTVNetwork——无向图的点连通度

http://poj.org/problemid=1966 题目描述: 有线电视网络中,中继器的连接是双向的。如果网 ...

Oracle备忘录1

数据库管理员:安装升级oracle数据库建库,表空间,表,视图,索引。。。制定并实施备份和修复计划数据库权限管理,调优, ...

FileStream文件流

使用文件流拷贝一个较大的多媒体文件: public static void copyfile(string soucr ...

C语言BFS(5)___TT与魔法师(swustoj2464)

description tt生活在一个充满魔法的国度,为了便于管理,国王请魔法师在一些重要的城市之间造出 ...

进程类Process与多线程Thread

进程类(process)的基本操作: //通过进程类查询系统所有进程 process[] pr ...

Xml解析方式之Pull解析器的使用

xml有多种解析的方式,这篇文章只介绍用pull解析器来解析xml文件,接下来我会说明使用pull解析器来读取xml文件 ...

enum,EnumMap,EnumSet

enum基本使用 : package com.enumtest; enum shrubbery { gr ...

Hibernate乱码问题解决

乱码问题其实归根接地就是两端的字符集不统一。 解决思路也有两种: 1. 修改两端字符集统一。 2. 通过代码进行转 ...

eclipse集成struts2.3.20

需要强调的是,这里介绍的是在eclipse工具下集成struts2.3.20而不是myeclipse添加对struts2 ...

ubi文件系统制作,还是"-c"选项的问题

以下是分析记录: --------------------------------------------------- ...

如何把事情做到最好读书笔记1

开篇语: 每个人生来都具备足够的潜力,每个人都能做到别人#30524;中难以企及的事情。请永远保持初学之心,勇敢面对 ...

如何把事情做到最好读书笔记2

第二章 认清自己:你属于哪种类型的人 你必须足够了解你自己,下面有三种类型的人、 (1)浅尝辄止者 浅尝辄止者对一切 ...

如何把事情做到最好读书笔记3

第三章 一份耕耘才能一份收获 当你决定踏上精益求精之路时,你会突然发现周围的一切都与你所追求的#26684;#2668 ...

如何把事情做到最好读书笔记4

第四章 热爱平台期 从小,我们接受的教育就是好好学习,这样才能上好的大学,上好的大学才能找到好工作,有好工作才能有钱买 ...

习题10-21二项式系数UVa1649

1.题目描述:点击打开链接 2.解题思路:本题利用枚举#43;二分解决。问题的关键是选对枚举对象,因为要找c(n,k)= ...


本网站部分内容来自互联网,版权归原作者所有,文章内容仅代表原作者个人观点。如有侵权请联系我们删除 电子邮件 itjcku@foxmail.com