ORACLE小写金额转大写金额

转自：http://www.2cto.com/database/201307/226045.html

ORACLE小写金额转大写金额

 

?

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103

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 = '0' Then     numLenYuan := 0;     strYuanFen := lpad(TO_CHAR(FLOOR(Money * 100)), 2, '0');   Else     numLenYuan := length(strYuan);     strYuanFen := TO_CHAR(FLOOR(Money * 100));   End If;   If strYuanFen = '0' Then     numLenYuanFen := 0;   Else     numLenYuanFen := length(strYuanFen);   End If;   If numLenYuan = 0 Or numLenYuanFen = 0 Then     strRst := '零圆整';     Return strRst;   End If;   tabNumMapping(0) := '零';   tabNumMapping(1) := '壹';   tabNumMapping(2) := '贰';   tabNumMapping(3) := '叁';   tabNumMapping(4) := '肆';   tabNumMapping(5) := '伍';   tabNumMapping(6) := '陆';   tabNumMapping(7) := '柒';   tabNumMapping(8) := '捌';   tabNumMapping(9) := '玖';   tabUnitMapping(-2) := '分';   tabUnitMapping(-1) := '角';   tabUnitMapping(1) := '';   tabUnitMapping(2) := '拾';   tabUnitMapping(3) := '佰';   tabUnitMapping(4) := '仟';   tabUnitMapping(5) := '万';   tabUnitMapping(6) := '拾';   tabUnitMapping(7) := '佰';   tabUnitMapping(8) := '仟';   tabUnitMapping(9) := '亿';   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) <> '0000' Then           strRstYuan := tabUnitMapping(numUnitIndex) || strRstYuan;         End If;       Else         If substr(strYuan, j + 1, 1) <> '0' 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 <> '0' Then       strRstFen := tabNumMapping(charCurrentNum) || tabUnitMapping(i) ||                    strRstFen;     End If;   End Loop;   If strRstYuan Is Not Null Then     strRstYuan := strRstYuan || '圆';   End If;   If strRstFen Is Null Then     strRstYuan := strRstYuan || '整';   Elsif length(strRstFen) = 2 And substr(strRstFen, 2) = '角' Then     strRstFen := strRstFen || '整';   End If;   strRst := strRstYuan || strRstFen;   --strRst := Replace(strRst, '亿零', '亿');   --strRst := Replace(strRst, '万零', '万');   Return strRst; End Money2Chinese;

 

 

测试SQL如下：

 

?

1	Select Money2Chinese(0932402934024.213) From dual;

 

 

结果：

 

玖仟叁佰贰拾肆亿零贰佰玖拾叁万肆仟零贰拾肆圆贰角壹分

 

测试通过