oracle 函数

最新推荐文章于 2025-08-10 14:45:00 发布

原创最新推荐文章于 2025-08-10 14:45:00 发布 · 1k 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#oracle #integer #insert #sms #exception #function

本文介绍了一个用于计算Oracle数据库中代理商酬金的函数MXL_GET_SMSUNIT_ACCOUNT及一个更新代理商月酬金的过程MXL_UPDATE_CBM_REWARD_ACCOUNT。通过这两个自定义函数和过程，可以实现根据不同业务类型和区域自动计算并更新代理商的酬金。

摘要生成于 C知道，由 DeepSeek-R1 满血版支持，前往体验 >

我自己写的Oracle函数:

CREATE OR REPLACE Function MXL_GET_SMSUNIT_ACCOUNT(BusinessCode In varchar2,areacode In Integer,SmsUnitMoney In Integer) Return Integer Is retMoney Integer;
-- BusinessCode: 代理商酬金类型: 短信营业厅代理商酬金:SMS_UNIT_MONEY 手机营业厅代理商酬金
-- areacode: 区域编码：城区:700 乡镇:701 村庄:702
-- SmsUnitMoney 代理商收费金额: 代理商在月统计中实际收费金额
type_cur Sys_Refcursor;
emp_rec cbm_rewardrule%Rowtype;
Begin
   -- dbms_output.put_line('select * from cbm_rewardrule where cbusiness_code= '''|| BusinessCode || ''' And carea_code= ' || areacode || ' Order By end_value Desc');
    retMoney:=0;
    Open type_cur For 'select * from cbm_rewardrule where cbusiness_code= '''|| BusinessCode || ''' And carea_code= ' || areacode || ' Order By end_value Desc';
    Loop
        fetch type_cur into emp_rec; exit When type_cur%notfound;
      Begin
   --     dbms_output.put_line(emp_rec.end_value);
          If emp_rec.end_value Is Null Then             --判断酬金规则当中没有最高额限制的一项。
             Begin
               If SmsUnitMoney>emp_rec.begin_value Then          --如果用户的收费总额大于系统最高额的限制。计算该范围内的金额
                 retMoney:=(SmsUnitMoney- emp_rec.begin_value)*emp_rec.nrewardrull;
               End If;
              End;
        End If;

       If SmsUnitMoney>=emp_rec.begin_value Then        --判断代理商收费金额大于收费规则中开始值的
         Begin
            If SmsUnitMoney>emp_rec.end_value Then          --判断代理商收费金额大于结束值时：规则结束值-开始值 X 酬金比例
                 retMoney := retMoney+((emp_rec.end_value-emp_rec.begin_value)*emp_rec.nrewardrull);
              Elsif SmsUnitMoney<=emp_rec.end_value Then   --判断代理商收费金额小于结束值是：收费金额-开始值 X 酬金比例
                retMoney := retMoney+((SmsUnitMoney-emp_rec.begin_value)*emp_rec.nrewardrull);
           End If;
         End;
       End If;
      End;
      --Case When Then

     -- dbms_output.put_line();
    End Loop;


    Return retMoney;
End;

下面是我第二个：

create or replace procedure MXL_UPDATE_CBM_REWARD_ACCOUNT(rewardmonth in varchar2,businesscode in varchar2,userid in varchar2 ,retnum out integer ) as
--统计代理商月收费酬金
-- rewardmonth. 统计代理商酬金的帐期
-- businesscode: 业务类型短信营业厅统计:SMS_UNIT_MONEY 手机营业厅: MONILE_UNIT_MONEY
sys_cur Sys_Refcursor;
changesum cbm_charge_sum%rowtype;
reward cbm_reward%rowtype;
money integer;

begin

     retnum:=0;
          select count(*) into retnum from cbm_charge_sum where cperiod=rewardmonth and cchannel_type=businesscode and cfiled1=1;
     IF retnum>0 Then
         open sys_cur for select * from cbm_charge_sum where cperiod=rewardmonth and cchannel_type=businesscode and cfiled1=1;
         loop
             fetch sys_cur into changesum; exit When sys_cur %notfound;

                    --取得代理商所在的区域：市区、城区、乡镇、村庄
                    --select * from cbm_unti where cnote3=changesum.cunit_id
                   money:=MXL_GET_SMSUNIT_ACCOUNT(businesscode,700,changesum.n_real_fee);

                   select * into reward from cbm_reward where CUNIT_CODE=changesum.cunit_code;

                      if SQL%notfound then--判断是否已经存在代理商酬金信息
                        begin
                            insert into cbm_reward_account (nserial, cunit_code, cuser_id, cchannel_type,
                            creward_type, nreward_bef, nreward, nreward_af, cperiod, dtdate_cal, cuserid_cal,
                             baudit, cuserid_audit, dtdate_audit, ncancel_serial, nbcancel_fee, cccancel_userid,
                              dtcancel, ctitle, cnote, cfiled1, cfiled2, cfiled3 )
                            values(SEQ_CBM_REWARD_ACCOUNT.Nextval, changesum.cunit_code,
                            changesum.cuser_id,changesum.cperiod,'',0,money,money,changesum.cperiod,sysdate,userid,
                            0,'','','',0,'','','','','','','');
                        end ;
                      else
                        begin
                            insert into cbm_reward_account (nserial, cunit_code, cuser_id, cchannel_type,
                            creward_type, nreward_bef, nreward, nreward_af, cperiod, dtdate_cal, cuserid_cal,
                             baudit, cuserid_audit, dtdate_audit, ncancel_serial, nbcancel_fee, cccancel_userid,
                              dtcancel, ctitle, cnote, cfiled1, cfiled2, cfiled3 )
                            values(SEQ_CBM_REWARD_ACCOUNT.Nextval, changesum.cunit_code,
                            changesum.cuser_id,changesum.cperiod,'',reward.nreward_cur,money,reward.nreward_cur+ money,changesum.cperiod,sysdate,userid,
                            0,'','','',0,'','','','','','','');
                        end;
                    end if;

         end loop;
     End if;
     commit;
     exception
     when no_data_found then
          dbms_output.put_line('dddddddddddddddd');

end;