create or replace procedure panyh(v_cusid in varchar2, v_time in varchar2) as
v_con integer;
begin
delete from F_ORDERINFO_DETAIL_PAN t
where substr(t.order_date, 0, 4) = v_time
and customer_id = v_cusid;
commit;
insert into F_ORDERINFO_DETAIL_PAN
select t.*, 'Y'
from f_orderinfo_detail t
where substr(t.order_date, 0, 4) = v_time
AND T.CUSTOMER_ID = v_cusid;
commit;
update F_ORDERINFO_DETAIL_PAN set is_charge_flag='N' where order_type not in ('JBCX');
update F_ORDERINFO_DETAIL_PAN set is_charge_flag='N' where order_status not in ('查到');
for c1 in (select * from F_ORDERINFO_DETAIL_PAN t where substr(t.order_date,0,4)=v_time and t.order_type='JBCX' and t.order_status='查到' order by foi_orderno ) loop
select count(*)
into v_con
from F_ORDERINFO_DETAIL_PAN t
where t.order_date = c1.order_date
AND T.CUSTOMER_ID = v_cusid
and t.order_type = 'JBCX'
and t.order_status = '查到'
and t.foi_orderno=c1.foi_orderno
and t.foi_key in
(select foi_key
from F_ORDERINFO_DETAIL_PAN t
where to_date(foi_ordertime,'yyyy-mm-dd hh24:mi:ss') >=to_date(c1.foi_ordertime,'yyyy-mm-dd hh24:mi:ss')-30
and foi_ordertime
and foi_orderno not in (c1.foi_orderno)
and t.is_charge_flag='Y'
and t.order_type='JBCX'
and t.order_status='查到');
if v_con >0
then
update F_ORDERINFO_DETAIL_PAN t set t.is_charge_flag='N' where t.foi_orderno=c1.foi_orderno;
commit;
end if;
end loop;
end;
#######################
下面这个是我弟写的
create or replace procedure panyh5(v_cusid in varchar2, v_time in varchar2) as
v_count NUMBER;
begin
delete from F_ORDERINFO_DETAIL_PAN t
where substr(t.order_date, 0, 4) = v_time
and customer_id = v_cusid;
commit;
insert into F_ORDERINFO_DETAIL_PAN
select t.*, 'N'
from f_orderinfo_detail t
where substr(t.order_date, 0, 4) = v_time
AND T.CUSTOMER_ID = v_cusid;
commit;
update F_ORDERINFO_DETAIL_PAN a set is_charge_flag='Y'
where substr(order_date,0,4)=v_time and a.rowid in (
select min(b.rowid) from F_ORDERINFO_DETAIL_PAN b where b.foi_key=a.foi_key and substr(order_date,0,4)=v_time );
v_count := v_count + 1;
IF v_count >= 1000 THEN
commit;
end if;
commit;
end;
转播
