집 >데이터 베이스 >MySQL 튜토리얼 >금액을 대문자에서 소문자로 변환 SQL
금액을 대문자에서 소문자로 변환 SQL

大家讲道理원래의: 2016-11-12 10:50:391580검색
Create Or Replace Function Money2Chinese(Money In Number) Return Varchar2 Is
 
  strYuan       Varchar2(150);
 
  strYuanFen    Varchar2(152);
 
  numLenYuan    Number;
 
  numLenYuanFen Number;
 
  strRstYuan    Varchar2(600);
 
  strRstFen     Varchar2(200);
 
  strRst        Varchar2(800);
 
  Type typeTabMapping Is Table Of Varchar2(2) Index By Binary_Integer;
 
  tabNumMapping  typeTabMapping;
 
  tabUnitMapping typeTabMapping;
 
  numUnitIndex   Number;
 
  i              Number;
 
  j              Number;
 
  charCurrentNum Char(1);
 
Begin
 
  If Money Is Null Then
 
    Return Null;
 
  End If;
 
  strYuan := TO_CHAR(FLOOR(Money));
 
  If strYuan = &#39;0&#39; Then
 
    numLenYuan := 0;
 
    strYuanFen := lpad(TO_CHAR(FLOOR(Money * 100)), 2, &#39;0&#39;);
 
  Else
 
    numLenYuan := length(strYuan);
 
    strYuanFen := TO_CHAR(FLOOR(Money * 100));
 
  End If;
 
  If strYuanFen = &#39;0&#39; Then
 
    numLenYuanFen := 0;
 
  Else
 
    numLenYuanFen := length(strYuanFen);
 
  End If;
 
  If numLenYuan = 0 Or numLenYuanFen = 0 Then
 
    strRst := &#39;零圆整&#39;;
 
    Return strRst;
 
  End If;
 
  tabNumMapping(0) := &#39;零&#39;;
 
  tabNumMapping(1) := &#39;壹&#39;;
 
  tabNumMapping(2) := &#39;贰&#39;;
 
  tabNumMapping(3) := &#39;叁&#39;;
 
  tabNumMapping(4) := &#39;肆&#39;;
 
  tabNumMapping(5) := &#39;伍&#39;;
 
  tabNumMapping(6) := &#39;陆&#39;;
 
  tabNumMapping(7) := &#39;柒&#39;;
 
  tabNumMapping(8) := &#39;捌&#39;;
 
  tabNumMapping(9) := &#39;玖&#39;;
 
  tabUnitMapping(-2) := &#39;分&#39;;
 
  tabUnitMapping(-1) := &#39;角&#39;;
 
  tabUnitMapping(1) := &#39;&#39;;
 
  tabUnitMapping(2) := &#39;拾&#39;;
 
  tabUnitMapping(3) := &#39;佰&#39;;
 
  tabUnitMapping(4) := &#39;仟&#39;;
 
  tabUnitMapping(5) := &#39;万&#39;;
 
  tabUnitMapping(6) := &#39;拾&#39;;
 
  tabUnitMapping(7) := &#39;佰&#39;;
 
  tabUnitMapping(8) := &#39;仟&#39;;
 
  tabUnitMapping(9) := &#39;亿&#39;;
 
  For i In 1 .. numLenYuan Loop
 
    j            := numLenYuan - i + 1;
 
    numUnitIndex := Mod(i, 8);
 
    If numUnitIndex = 0 Then
 
      numUnitIndex := 8;
 
    End If;
 
    If numUnitIndex = 1 And i > 1 Then
 
      strRstYuan := tabUnitMapping(9) || strRstYuan;
 
    End If;
 
    charCurrentNum := substr(strYuan, j, 1);
 
    If charCurrentNum <> 0 Then
 
      strRstYuan := tabNumMapping(charCurrentNum) ||
 
                    tabUnitMapping(numUnitIndex) || strRstYuan;
 
    Else
 
      If (i = 1 Or i = 5) Then
 
        If substr(strYuan, j - 3, 4) <> &#39;0000&#39; Then
 
          strRstYuan := tabUnitMapping(numUnitIndex) || strRstYuan;
 
        End If;
 
      Else
 
        If substr(strYuan, j + 1, 1) <> &#39;0&#39; Then
 
          strRstYuan := tabNumMapping(charCurrentNum) || strRstYuan;
 
        End If;
 
      End If;
 
    End If;
 
  End Loop;
 
  For i In -2 .. -1 Loop
 
    j              := numLenYuan - i;
 
    charCurrentNum := substr(strYuanFen, j, 1);
 
    If charCurrentNum <> &#39;0&#39; Then
 
      strRstFen := tabNumMapping(charCurrentNum) || tabUnitMapping(i) ||
 
                   strRstFen;
 
    End If;
 
  End Loop;
 
  If strRstYuan Is Not Null Then
 
    strRstYuan := strRstYuan || &#39;圆&#39;;
 
  End If;
 
  If strRstFen Is Null Then
 
    strRstYuan := strRstYuan || &#39;整&#39;;
 
  Elsif length(strRstFen) = 2 And substr(strRstFen, 2) = &#39;角&#39; Then
 
    strRstFen := strRstFen || &#39;整&#39;;
 
  End If;
 
  strRst := strRstYuan || strRstFen;
 
  --strRst := Replace(strRst, &#39;亿零&#39;, &#39;亿&#39;);
 
  --strRst := Replace(strRst, &#39;万零&#39;, &#39;万&#39;);
 
  Return strRst;
 
End Money2Chinese;
성명：
이전 기사：회계 연도 테이블 구성 제약다음 기사：회계 연도 테이블 구성 제약
금액을 대문자에서 소문자로 변환 SQL

관련 기사
