oracle 函数

来源:互联网 发布:matlab优化工具箱 编辑:程序博客网 时间:2024/03/29 16:35

我自己写的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;

 

原创粉丝点击