Oracle实现金额小写转大写函数

今天工作的时候遇到了，然后上网百度了一下，发现了有个大佬写了一个很牛的函数，在此记录下来。

原文：http://www.itpub.net/thread-240281-1-1.html

函数：

create or replace function F_upper_money(p_num in number default null)
return nvarchar2 is
/*Ver:1.0 Created By xsb on 2003-8-18 For:
        将金额数字(单位元)转换为大写(采用从低至高算法)
        数字整数部分不得超过16位,可以是负数。
Ver：1.1 Modified By xsb on 2003-8-20 For:个位数处理也放在For循环中。        
Ver：1.2 Modified By xsb on 2003-8-22 For:分后不带整字。
Ver：1.3 Modified By xsb on 2003-8-28 For:完善测试用例。
测试用例:
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 ;

调用效果如下：

-- 无参数，结果为：null=
select "null="||f_upper_money(null) total from dual;

-- 0=零元整
select "0="||f_upper_money(0) total from dual;

-- 0.05=零元零伍分
select "0.05="||f_upper_money(0.05) total from dual;

-- 0.155=零元壹角陆分
select "0.155="||f_upper_money(0.155) total from dual;

-- 01.125=壹元壹角参分
select "01.125="||f_upper_money(01.125) total from dual;

-- 10=壹拾元整
select "10="||f_upper_money(10) total from dual;

-- 100.5=壹佰元伍角整
select "100.5="||f_upper_money(100.5) total from dual;

-- 100.05=壹佰元零伍分
select "100.05="||f_upper_money(100.05) from dual;

-- 10000=壹万元整
select "10000="||f_upper_money(10000) from dual;

-- 10025.52=壹万零贰拾伍元伍角贰分
select "10025.52="||f_upper_money(10025.52) from dual;

-- 50000020.05=伍仟万零贰拾元零伍分
select "50000020.05="||f_upper_money(50000020.05) from dual;

-- 5040302105.501=伍拾亿肆仟零参拾万贰仟壹佰零伍元伍角整
select "5040302105.501="||f_upper_money(5040302105.501) from dual;

-- 50002001075.001=伍佰亿零贰佰万壹仟零柒拾伍元整
select "50002001075.001="||f_upper_money(50002001075.001) from dual;

-- -123456789.15=负壹亿贰仟参佰肆拾伍万陆仟柒佰捌拾玖元壹角伍分
select "-123456789.15="||f_upper_money(-123456789.15) from dual;

-- 123456789123456789.89=**********
select "123456789123456789.89="||f_upper_money(123456789123456789.89) from dual;