本帖最后由 yangrongyan2008 于 2014-11-10 10:27 编辑
请教各位大侠！
CREATE OR REPLACE PROCEDURE "PROC_POINT_ACTIVITY_11" is
cursor c2 is
select o.customer_idmycustomerid,
o.order_id myorderid,
o.customer_card_id mycardid
from t_to_order_info o
union
select o.customer_idmycustomerid,
o.order_id myorderid,
o.customer_card_id mycardid
from t_ho_order_info o;
begin
---插入信息之前 先判断t_c_activity_customer 表里有没有这个会员
for r2 in c2 loop
insert into t_c_activity_customer values (r2.mycustomerid);
---生成积分明细
insert into t_c_point_record2014
values
(seq_t_c_point_record.nextval,
null,
1,
7,
r2.mycustomerid,
null,
1000,
'手机客户端营销活动',
2,
r2.myorderid,
sysdate,
1,
r2.mycardid);
--更新会员表积分记录 加1000
update t_c_customer c
set c.gc_points_total= c.gc_points_total + 1000,
c.gc_points_usable = c.gc_points_usable + 1000
where c.customer_id = r2.mycustomerid
and not exists (select CUSTOMER_ID
from t_c_activity_customer
where customer_id = r2.mycustomerid); -- --------愿意是想 排除 更新过的会员，实现会员即使预定多次 也只送一次积分。目前这里有问题 会员下几个订单 ，积分就翻倍增加
commit;
end loop;
end;
|
