存储过程如下,出不来计算数据,查了一下发下Update操作没有更新,也不报错,有点急
PROCEDURE update_marketing_moneys AS
--根据佣金表里获取当月数据
CURSOR p_fycDetail_list IS
select sum(y.FYC_Money) as fyc_moneys,
info.pid,
info.profession_level,
nvl(y.intro_platform, 'cfy') as intro_platform
from is_crm_trade_audit_fyc y, v_store_userinfo info
where y.count_status = '1'
and y.confirm_grant_status = '1' --是否待发放
and y.expect_time = to_char(sysdate, 'yyyy-MM') || '-25'
and y.ins_parent_pid = info.pid
and y.owner_channel_type <> '3'
group by info.pid, info.profession_level, y.intro_platform;
BEGIN
FOR p_fyc IN p_fycDetail_list LOOP
IF p_fyc.fyc_moneys > 1000 THEN
BEGIN
--更新行销津贴
UPDATE is_crm_commission_fyc_grant g
SET g.marketing_rate = nvl((select nvl(r.rule_coefficient, '0')
from is_crm_commission_rule r
where r.rule_type = '4'
and r.rule_sub_type =
p_fyc.profession_level
and r.rule_data_begin <=
p_fyc.fyc_moneys
and r.rule_data_end >
p_fyc.fyc_moneys),
'0.15'),
g.marketing_money = nvl((select nvl(r.rule_coefficient, '0')
from is_crm_commission_rule r
where r.rule_type = '4'
and r.rule_sub_type =
p_fyc.profession_level
and r.rule_data_begin <=
p_fyc.fyc_moneys
and r.rule_data_end >
p_fyc.fyc_moneys),
'0.15') * g.fyc_moneys
where g.store_pid = p_fyc.pid
and g.intro_platform = p_fyc.intro_platform
and g.expect_time = to_char(sysdate, 'yyyy-MM') || '-25';
COMMIT;
EXCEPTION
WHEN OTHERS THEN
UPDATE is_crm_commission_fyc_grant g
SET g.marketing_rate = '0.15',
g.marketing_money = '0.15' * g.fyc_moneys
where g.store_pid = p_fyc.pid
and g.intro_platform = p_fyc.intro_platform
and g.expect_time = to_char(sysdate, 'yyyy-MM') || '-25';
END;
COMMIT;
END IF;
END LOOP;
--如果没有级别则默认为15%的fyc金额
UPDATE is_crm_commission_fyc_grant g
SET g.marketing_rate = '0.15',
g.marketing_money = '0.15' * g.fyc_moneys
where g.profession_level is null
and to_number(g.fyc_moneys) >= 1000
and g.owner_channel_type <> '3'
and g.grant_satus = '0';
END;
http://blog.csdn.net/xiaolongzaixian/article/details/50821391
版权声明:本文内容由阿里云实名注册用户自发贡献,版权归原作者所有,阿里云开发者社区不拥有其著作权,亦不承担相应法律责任。具体规则请查看《阿里云开发者社区用户服务协议》和《阿里云开发者社区知识产权保护指引》。如果您发现本社区中有涉嫌抄袭的内容,填写侵权投诉表单进行举报,一经查实,本社区将立刻删除涉嫌侵权内容。