首页 » ORACLE » Script: sql 金额数字转换大写(1to壹)

Script: sql 金额数字转换大写(1to壹)

在打印的单位中涉及金额的地方,常规都是有中文大写,避免修改,目前有个需求正好需要,网上有一篇不错,在这转了,对不住原创,地址找不到了

1001.23¥ 转成 壹仟零壹元贰角叁分

create or replace function MoneyToChina(p_money in number) return varchar2 is
   type myArray is table of varchar2(255);
    n_str myArray := myArray('壹','贰','叁','肆','伍','陆','柒','捌','玖','零');
    u_str myArray := myArray('分','角','圆','拾','佰','仟','万','拾','佰','仟','亿','拾','佰','仟');  
    signal varchar2(2) := null; 
    cur_digit    number(1) := 0;
    pre_digit    number(1) := 0;
    str_length number(2) := 0;        
    w_cash   varchar2(20);
    l_return     varchar2(200);
begin
   if p_money < 0 then
       signal := '负';
   end if;
   w_cash := to_char(abs(p_money)*100);
   str_length := length(w_cash);

   for i in 1..str_length loop
       cur_digit := to_number(substr(w_cash,str_length - i + 1,1));
       if i = 3 and l_return is null then
           l_return := '整';          
       end if ;      
       if i > 2 or cur_digit <> 0 then
           if cur_digit = 0 then
              if i = 3 or i = 7 or i = 11 then
                  l_return := u_str(i)||l_return;
              elsif pre_digit <> 0 then   
                  l_return := '零'||l_return;
              end if;
           else
              l_return := n_str(cur_digit)||u_str(i)||l_return;   
           end if;
       end if;
       pre_digit := cur_digit;
   end loop;  
   if instr(l_return,'万',1) - instr(l_return,'亿',1) = 1 then
       l_return := replace(l_return,'万',null);
   end if; 
   l_return := signal||l_return;
   return(l_return);
end MoneyToChina;

打赏

对不起,这篇文章暂时关闭评论。