[转]Oracle中将数字金额转化为大写

[转]Oracle中将数字⾦额转化为⼤写今天写了⼀个将数字⾦额转化为⼤写的函数。

功能：将任意长度的数字⾦额转化为⼤写。

最低位为圆或⾓时，后⾯加“整”；为“分”时不加“整”。

说明：⼩数点后保留两位。

超过两位的部分被截断。

代码：
Create Or Replace Function Money2Chinese(Money In Number) Return Varchar2Is
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 =0Or numLenYuanFen =0Then
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 In1 .. numLenYuan Loop
j := numLenYuan - i +1;
numUnitIndex := Mod(i, 8);
If numUnitIndex =0Then
numUnitIndex :=8;
End If;
If numUnitIndex =1And i >1Then
strRstYuan := tabUnitMapping(9) || strRstYuan;
End If;
charCurrentNum := substr(strYuan, j, 1);
If charCurrentNum <>0Then
strRstYuan := tabNumMapping(charCurrentNum) ||
tabUnitMapping(numUnitIndex) || strRstYuan;
Else
If (i =1Or 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) =2And substr(strRstFen, 2) ='⾓'Then
strRstFen := strRstFen ||'整';
End If;
strRst := strRstYuan || strRstFen;
--strRst := Replace(strRst, '亿零', '亿');
--strRst := Replace(strRst, '万零', '万');
Return strRst;
End Money2Chinese;
测试SQL如下：
Select Money2Chinese(0932402934024.213) From dual;结果：
玖仟叁佰贰拾肆亿零贰佰玖拾叁万肆仟零贰拾肆圆贰⾓壹分测试通过
原⽂地址：。