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;
- Oracle函数
- oracle 函数
- Oracle函数
- Oracle函数
- oracle函数
- ORACLE函数
- Oracle函数
- oracle 函数
- oracle 函数
- oracle 函数
- ORACLE函数
- oracle函数
- oracle函数
- oracle函数
- oracle函数
- oracle 函数
- oracle函数
- Oracle函数
- How to Perform System Boot and Shutdown Procedures for Solaris 10, Part B
- 费米问题的启示
- 在ASP.NET中重写URL
- Tips - Web UI 资源索引
- 好东西大家分享: 软件设计的三个维度
- oracle 函数
- 基本数据类型之二:指针
- 瑜伽断食法——From《瑜伽祖本》(手敲版)
- linux 查看 系统 端口占用情况
- Oracle中的外连接语句
- 终于加入组织
- How to Perform System Boot and Shutdown Procedures for Solaris 10, Part C
- 如何在页面上获取鼠标事件。
- 不可多得的Javascript(AJAX)开发工具 - Aptana