ORACLE SQL 金额从小写到大写的转换

ORACLE 专栏收录该内容
5 篇文章 0 订阅

create or replace function F_upper_money(p_num in number default null)
return nvarchar2 is
/*Ver:1.0
将金额数字(单位元)转换为大写(采用从低至高算法)
数字整数部分不得超过16位,可以是负数。
测试用例:
SET HEAD OFF
SET FEED OFF
select '无参数时='||f_upper_money() from dual;
select 'null='||f_upper_money(null) from dual;
select '0='||f_upper_money(0) from dual;
select '0.01='||f_upper_money(0.01) from dual;
select '0.126='||f_upper_money(0.126) from dual;
select '01.234='||f_upper_money(01.234) from dual;
select '10='||f_upper_money(10) from dual;
select '100.1='||f_upper_money(100.1) from dual;
select '100.01='||f_upper_money(100.01) from dual;
select '10000='||f_upper_money(10000) from dual;
select '10012.12='||f_upper_money(10012.12) from dual;
select '20000020.01='||f_upper_money(20000020.01) from dual;
select '3040506708.901='||f_upper_money(3040506708.901) from dual;
select '40005006078.001='||f_upper_money(40005006078.001) from dual;
select '-123456789.98='||f_upper_money(-123456789.98) from dual;
-------
select '123456789123456789.89='||f_upper_money(123456789123456789.89) from dual;

*/
Result nvarchar2(100);--返回字符串
num_round nvarchar2(100) :=to_char(abs(round(p_num,2)));--转换数字为小数点后2位的字符(正数)
num_left nvarchar2(100);--小数点左边的数字
num_right nvarchar2(2);--小数点右边的数字
str1 nchar(10) :='零壹贰参肆伍陆柒捌玖';--数字大写
str2 nchar(16) :='元拾佰仟万拾佰仟亿拾佰仟万拾佰仟';--数字位数(从低至高)
num_pre number(1):=1;--前一位上的数字
num_current number(1);--当前位上的数字
num_count number:=0;--当前数字位数

begin
if p_num is null then return null;end if;--转换数字为null时返回null

select to_char(
nvl(substr(to_char(num_round),1,
decode(instr(to_char(num_round),'.'),0,
length(num_round),instr(to_char(num_round),'.')-1)),
0)) into num_left from dual;--取得小数点左边的数字
select substr(to_char(num_round),
decode(instr(to_char(num_round),'.'),0,
length(num_round)+1,instr(to_char(num_round),'.')+1),2)
into num_right from dual;--取得小数点右边的数字

if length(num_left)>16 then return '**********'; end if;--数字整数部分超过16位时

--采用从低至高的算法,先处理小数点右边的数字
if length(num_right)=2 then
if to_number(substr(num_right,1,1))=0 then
result:='零'||substr(str1,to_number(substr(num_right,2,1))+1,1)||'分';
else
result:=substr(str1,to_number(substr(num_right,1,1))+1,1)||'角'||
substr(str1,to_number(substr(num_right,2,1))+1,1)||'分';
end if;
elsif length(num_right)=1 then
result:=substr(str1,to_number(substr(num_right,1,1))+1,1)||'角整';
else
result :='整';
end if;
--再处理小数点左边的数字
for i in reverse 1..length(num_left) loop --(从低至高)
num_count:=num_count+1;--当前数字位数
num_current:=to_number(substr(num_left,i,1));--当前位上的数字
if num_current>0 then --当前位上数字不为0按正常处理
result:=substr(str1,num_current+1,1)||substr(str2,num_count,1)||result;
else --当前位上数字为0时
if mod(num_count-1,4)=0 then --当前位是元、万或亿时
result:=substr(str2,num_count,1)||result;
num_pre:=0;--元、万,亿前不准加零
end if;
if num_pre>0 or length(num_left)=1 then --上一位数字不为0或只有个位时
result:=substr(str1,num_current+1,1)||result;
end if;
end if;
num_pre:=num_current;
end loop;

if p_num<0 then --转换数字是负数时
result:='负'||result;
end if;

return Result;

exception
when others then
raise_application_error(-20001,'数字转换大写出现错误!'||sqlerrm);
end ;

--下面是一个存储过程,主要是看他的结构

create or replace procedure ptr_jsfood_rep_crop_pterm
(outnum  out   integer)
is
/***********************************************************************
目的:
统计报表(江苏省食品生产加工企业生产条件汇总表)
XXX  2005/06/15     1.0       创建         
Co.ltd
***********************************************************************/
  var_crop_scope      varchar2(50);
  var_crop_character  varchar2(50);
 
 var_sate_sign       number;     --执行标准(国标)
 var_row_sign       number;    --执行标准(行标)
 var_corp_sign      number;     --执行标准(企标)
 var_none_sign      number;     --执行标准(无标)
 var_pq_fine           number;  --产品质量 (好)
 var_pq_preferably     number;  --产品质量 (较好)
 var_pq_sort           number;  --产品质量 (较差)
 var_pq_errand         number;  --产品质量 (差)
 var_pe_fine           number;  --生产设备(好)
 var_pe_preferably     number;  --生产设备(较好)
 var_pe_sort           number;  --生产设备(较差)
 var_pe_errand         number;  --生产设备(差)
 var_MATERIAL_since_check   number;     --原材料把关(自检)
 var_MATERIAL_consign       number;     --原材料把关(委托)
 var_MATERIAL_prove         number;     --原材料把关(证明)
 var_MATERIAL_n_check   number;     --原材料把关(不把关)
 var_lc_since_check    number;     --出厂检验方式(自检)
 var_lc_p_scheck       number;     --出厂检验方式(部分自检)
 var_lc_consign        number;     --出厂检验方式(委托)
 var_mea_affirm1         number;    --计量确认(体系)
 var_mea_affirm2         number;     --计量确认(保证)
 var_mea_affirm3         number;    --计量确认(合格)
 var_iso9000        number;     --体系认证(ISO9000)
 var_HACCP          number;     --体系认证(HACCP)
 var_perennial      number;     --生产情况(常年)
 var_season         number;     --生产情况(季节)
 var_stop_prod      number;      --生产情况(停产)
 
 CURSOR CURJSFOOD_stocktmp IS
 select crop_scope,crop_character from tmp_rep_crop_pterm;
 begin
 execute immediate'truncate table tmp_rep_crop_pterm';
 execute immediate'truncate table rep_crop_pterm';
 insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('大型企业','国有');
 insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('大型企业','集体');
 insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('大型企业','股份');
 insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('大型企业','私营');
 insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('大型企业','三资');
 insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('大型企业','合计');
 insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('中型企业','国有');
 insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('中型企业','集体');
 insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('中型企业','股份');
 insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('中型企业','私营');
 insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('中型企业','三资');
 insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('中型企业','合计');
 insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('小型企业','国有');
 insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('小型企业','集体');
 insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('小型企业','股份');
 insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('小型企业','私营');
 insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('小型企业','三资');
 insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('小型企业','合计');
 insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('小作坊','小作坊');
 insert into tmp_rep_crop_pterm(crop_scope,crop_character) values('总计','总计');
 open CURJSFOOD_stocktmp ;
 while true loop
  fetch CURJSFOOD_stocktmp into var_crop_scope,var_crop_character;
 exit when  CURJSFOOD_stocktmp%notfound;
 --执行标准(国标)
 select nvl(sum(decode(prod_level,'国标',1,0)),0) into var_sate_sign from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
 --执行标准(行标)
  select nvl(sum(decode(prod_level,'行标',1,0)),0) into var_row_sign from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
 --执行标准(企标)
 select nvl(sum(decode(prod_level,'企标',1,0)),0) into var_corp_sign from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
 --执行标准(无标)
  select nvl(sum(decode(prod_level,'无标',1,0)),0) into var_none_sign from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
 --产品质量(好)
  select nvl(sum(decode(prod_evalu,'好',1,0)),0) into var_pq_fine from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--产品质量 (较好)
 select nvl(sum(decode(prod_evalu,'较好',1,0)),0) into var_pq_preferably from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--产品质量(较差)
 select nvl(sum(decode(prod_evalu,'较差',1,0)),0) into  var_pq_sort  from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--产品质量(差)
  select nvl(sum(decode(prod_evalu,'差',1,0)),0) into  var_pq_errand  from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--生产设备(好)
  select nvl(sum(decode(EQUIP_EVALU,'好',1,0)),0) into   var_pe_fine  from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--生产设备(较好)
    select nvl(sum(decode(EQUIP_EVALU,'较好',1,0)),0) into  var_pe_preferably  from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--生产设备(较差)
   select nvl(sum(decode(EQUIP_EVALU,'较差',1,0)),0) into  var_pe_sort  from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--生产设备(差)
     select nvl(sum(decode(EQUIP_EVALU,'差',1,0)),0) into  var_pe_errand  from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--原材料把关(自检)
  select nvl(sum(decode(material_gate,'自检',1,0)),0) into  var_MATERIAL_since_check  from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--原材料把关(委托)
  select nvl(sum(decode(material_gate,'委托',1,0)),0) into  var_MATERIAL_consign   from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--原材料把关(证明)
    select nvl(sum(decode(material_gate,'证明',1,0)),0) into var_MATERIAL_prove  from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--原材料把关(不把关)
   select nvl(sum(decode(material_gate,'不把关',1,0)),0) into var_MATERIAL_n_check  from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--出厂检验方式(自检)
   select nvl(sum(decode(b.factory_check_type,'自检',1,0)),0) into var_lc_since_check  from t_prod_info b,t_corp t ,dic_reg_type d where b.corp_code=t.corp_code and t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--出厂检验方式(部分自检)
   select nvl(sum(decode(b.factory_check_type,'部分自检',1,0)),0) into var_lc_p_scheck  from t_prod_info b,t_corp t ,dic_reg_type d where b.corp_code=t.corp_code and t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--出厂检验方式(委托)
   select nvl(sum(decode(b.factory_check_type,'委托',1,0)),0) into var_lc_consign  from t_prod_info b,t_corp t ,dic_reg_type d where b.corp_code=t.corp_code and t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--计量确认(体系)
  select nvl(sum(decode(measure_affirm,'体系',1,0)),0) into var_mea_affirm1  from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
 --计量确认(保证)
 select nvl(sum(decode(measure_affirm,'保证',1,0)),0) into var_mea_affirm2  from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
 --计量确认(合格)
  select nvl(sum(decode(measure_affirm,'合格',1,0)),0) into var_mea_affirm3 from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
 --体系认证(ISO9000)
   select nvl(sum(decode(SYS_AUTH,'ISO9000',1,0)),0) into var_iso9000 from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--体系认证(HACCP)
  select nvl(sum(decode(SYS_AUTH,'HACCP',1,0)),0) into var_HACCP from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--生产情况(常年)
   select nvl(sum(decode(PROD_CYCLE,'常年',1,0)),0) into var_perennial from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--生产情况(季节) 
   select nvl(sum(decode(PROD_CYCLE,'季节',1,0)),0) into var_season from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
--生产情况(停产) 
   select nvl(sum(decode(PROD_CYCLE,'停产',1,0)),0) into var_stop_prod from t_corp t,dic_reg_type d where t.corp_scope=var_crop_scope
  and t.reg_type=d.reg_type and d.reg_kind=var_crop_character;
 
 update  tmp_rep_crop_pterm set
 sate_sign=var_sate_sign,row_sign=var_row_sign,corp_sign=var_corp_sign,none_sign=var_none_sign,
 pq_fine=var_pq_fine,pq_preferably=var_pq_preferably,pq_sort=var_pq_sort,pq_errand=var_pq_errand,
 pe_fine=var_pe_fine,pe_preferably=var_pe_preferably,pe_sort=var_pe_sort,pe_errand=var_pe_errand,
 MATERIAL_since_check=var_MATERIAL_since_check,MATERIAL_consign=var_MATERIAL_consign,
 MATERIAL_prove=var_MATERIAL_prove,MATERIAL_n_check=var_MATERIAL_n_check,
 lc_since_check=var_lc_since_check,lc_p_scheck=var_lc_p_scheck,lc_consign=var_lc_consign,
 mea_affirm1=var_mea_affirm1,mea_affirm2=var_mea_affirm2,mea_affirm3=var_mea_affirm3,
 iso9000=var_iso9000,HACCP=var_HACCP,perennial=var_perennial,season=var_season, stop_prod=var_stop_prod
 where crop_scope=var_crop_scope and crop_character=var_crop_character; 
 
 end loop;
  close CURJSFOOD_stocktmp;
 
  insert into rep_crop_pterm select * from tmp_rep_crop_pterm;
  commit;
  outnum:=1;
  exception
   when others then
    outnum:=0;
       rollback;
 
end ptr_jsfood_rep_crop_pterm;

  • 0
    点赞
  • 1
    评论
  • 0
    收藏
  • 一键三连
    一键三连
  • 扫一扫,分享海报

©️2021 CSDN 皮肤主题: 大白 设计师:CSDN官方博客 返回首页
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、C币套餐、付费专栏及课程。

余额充值