开发者社区> 问答> 正文

ORACLE存储过程update不了也不报错,请大神帮帮忙 ?报错

存储过程如下,出不来计算数据,查了一下发下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;

展开
收起
爱吃鱼的程序员 2020-06-23 14:19:13 814 0
1 条回答
写回答
取消 提交回答
  • https://developer.aliyun.com/profile/5yerqm5bn5yqg?spm=a2c6h.12873639.0.0.6eae304abcjaIB

    http://blog.csdn.net/xiaolongzaixian/article/details/50821391

    2020-06-23 14:19:29
    赞同 展开评论 打赏
问答排行榜
最热
最新

相关电子书

更多
PostgresChina2018_樊文凯_ORACLE数据库和应用异构迁移最佳实践 立即下载
PostgresChina2018_王帅_从Oracle到PostgreSQL的数据迁移 立即下载
Oracle云上最佳实践 立即下载

相关镜像