把阿拉伯数字转换为大写

create or replace function Get_Cap_Arabic_num(S_Num in number)

--  传入阿拉伯数字返回大写的阿拉伯数字
--  2006-09-14 
--  版本号2006.9.14.1

  return varchar2 As
  V_String    varchar2(64);  --函数返回值
  v_Length    Number(2);  -- 字符串长度
  V_Loopcount Number(2);  --  循环次数
  V_Char      Varchar2(2); --  阿拉伯数字大写字符
  V_TempStr   Varchar2(64); --  临时字符串
  V_DPoint    Number(2) ; --  小数点'.'在字符串中的位置
  V_DChar     Varchar2(4)  ;  -- 根据小数点位置以及该数字字符在字符串中的位置确定其计量单位
  V_YWPoint   Number(2) ;  --在字符串中'亿'、'万'最后一个字符的位置
Begin
  If ABS(S_Num) >= 0 Then
    V_String    := to_Char(s_num);
    V_Length    := Length(V_String);
    V_Loopcount := 1;
    V_DPoint := INSTR(V_String,'.') ;
   
    While V_Loopcount <= V_Length Loop
      V_Char      := Substr(V_String, V_Loopcount, 1);
     
      -- 把阿拉伯数字转换为大写
      V_Char      := Case When(V_char = '0') Then '零'
                          When(V_char = '1') Then '壹'
                          When(V_char = '2') Then '贰'
                          When(V_char = '3') Then '叁'
                          When(V_char = '4') Then '肆'
                          When(V_char = '5') Then '伍'
                          When(V_char = '6') Then '陆'
                          When(V_char = '7') Then '柒'
                          When(V_char = '8') Then '捌'
                          When(V_char = '9') Then '玖'
                          When(V_Char = '.') Then '点'
                          Else Null
                     End;
                    
      -- 根据所在的位置加上相应的计量单位       
      V_DChar := Case When (V_DPoint - V_Loopcount  = 1) Then null
                      When (V_DPoint - V_Loopcount  = 2) Then '拾' 
                      When (V_DPoint - V_Loopcount  = 3) Then '佰' 
                      When (V_DPoint - V_Loopcount  = 4) Then '仟' 
                      When (V_DPoint - V_Loopcount  = 5) Then '万' 
                      When (V_DPoint - V_Loopcount  = 6) Then '拾万' 
                      When (V_DPoint - V_Loopcount  = 7) Then '佰万' 
                      When (V_DPoint - V_Loopcount  = 8) Then '仟万' 
                      When (V_DPoint - V_Loopcount  = 9) Then '亿' 
                      When (V_DPoint - V_Loopcount  = 10) Then '拾亿' 
                      When (V_DPoint - V_Loopcount  = 11) Then '佰亿' 
                      When (V_DPoint - V_Loopcount  = 12) Then '仟亿' 
                      Else
                        Null
                      end ;
                     
      --  若为零，则不加计量单位，否则加上计量单位。
      If  V_Char = '零' Then
        V_TempStr   := V_TempStr || V_Char;
      Else
        V_TempStr   := V_TempStr || V_Char||V_DChar;
      End If;
     
      -- 把连续的多个'零'只留下一个
      V_TempStr := REPLACE(V_TempStr,'零零','零') ;
     
      --  若有多个'亿'则只留下最后一个 ；
      V_YWPoint := Instr(V_TempStr,'亿',-1,1) ;   
      If V_YWPoint > 1 Then
        V_TempStr := Replace(Substr(V_TempStr,1,V_YWPoint-1),'亿','') || Substr(V_TempStr,V_YWPoint,Length(V_TempStr)) ;
      end If ;

      --  若有多个'万'则只留下最后一个 ；
      V_YWPoint := Instr(V_TempStr,'万',-1,1) ;   
      If V_YWPoint > 1 Then
        V_TempStr := Replace(Substr(V_TempStr,1,V_YWPoint-1),'万','') || Substr(V_TempStr,V_YWPoint,Length(V_TempStr)) ;
      end If ;     
     
      V_Loopcount := V_Loopcount + 1;
    End Loop;
    V_String := V_TempStr;
  Else
    Dbms_Output.put_line('参数[S_Num]不能为空！');
    V_String := '传入的数字为非法';
  End IF;
 
  Return(V_String);
 
End;