好不容易翻出以前写的东西:Create or Replace Function SAY_CNY(p_cash number)
return varchar2 IS
Type typArray Is VArray(20) of varchar2(2);
unit typArray; --中文单位
c_digit typArray; --中文数字
w_cash varchar2(15); --字符类型的金额
signal varchar2(2) := null; --正负符号
cur_digit number(1) := 0; --当前数字
pre_digit number(1) := 0; --前一位数字
str_length number(2):= 0; --数字长度
result varchar2(255):= null; --返回结果
BEGIN
--初始化中文单位
unit := typArray('分','角','元','拾','佰','仟','万',
'拾','佰','仟','亿','拾','佰','仟');
--初始化中文数字
c_digit := typArray('壹','贰','叁','肆','伍',
'陆','柒','捌','玖','零');
If p_cash < 0 Then
signal := '负';
End If;
w_cash := to_char(Abs(p_cash) * 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 result is null Then --在元位如果以后部分为空的话,加上整
result := '整';
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
result := Unit(i) || result;
ElsIf pre_digit <> 0 Then
result := '零' || result;
End If;
Else
result := c_digit(cur_digit) || Unit(i) || result;
End If;
End If;
pre_digit := cur_digit;
END Loop; result := signal || result; return result;
END SAY_CNY;
return varchar2 IS
Type typArray Is VArray(20) of varchar2(2);
unit typArray; --中文单位
c_digit typArray; --中文数字
w_cash varchar2(15); --字符类型的金额
signal varchar2(2) := null; --正负符号
cur_digit number(1) := 0; --当前数字
pre_digit number(1) := 0; --前一位数字
str_length number(2):= 0; --数字长度
result varchar2(255):= null; --返回结果
BEGIN
--初始化中文单位
unit := typArray('分','角','元','拾','佰','仟','万',
'拾','佰','仟','亿','拾','佰','仟');
--初始化中文数字
c_digit := typArray('壹','贰','叁','肆','伍',
'陆','柒','捌','玖','零');
If p_cash < 0 Then
signal := '负';
End If;
w_cash := to_char(Abs(p_cash) * 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 result is null Then --在元位如果以后部分为空的话,加上整
result := '整';
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
result := Unit(i) || result;
ElsIf pre_digit <> 0 Then
result := '零' || result;
End If;
Else
result := c_digit(cur_digit) || Unit(i) || result;
End If;
End If;
pre_digit := cur_digit;
END Loop; result := signal || result; return result;
END SAY_CNY;
-- 将金额转换为大写汉字
--
FUNCTION N_To_C(n_money NUMBER) RETURN VARCHAR2 IS
c_dx1 CONSTANT VARCHAR2(40) := '壹贰叁肆伍陆柒捌玖零';
c_dx2 CONSTANT VARCHAR2(40) := '分角元拾佰仟万拾佰仟亿';
v_ret VARCHAR2(100);
v_money100 VARCHAR2(20);
n_length100 NUMBER(8);
i_money INTEGER;
bz INTEGER;
j INTEGER;
BEGIN
IF n_money is not null THEN
IF n_money = 0 THEN
RETURN('零元');
END IF;
v_money100 := To_Char( n_money*100 );
n_length100 := Length( v_money100 );
v_ret := '';
bz := 0;
j := 1;
FOR i IN REVERSE 1..n_length100 LOOP
i_money := To_Number(Substr(v_money100,i,1));
IF i_money = 0 THEN
IF bz = 0 AND j = 1 THEN
bz := 1 ;
v_ret := '整';
ELSIF bz = 0 AND j <> 1 then
bz := 1 ;
v_ret := '零'||v_ret;
END IF;
IF bz = 0 AND j > 11 THEN
v_ret := '拾' || v_Ret ;
END IF ;
IF j = 3 THEN
v_ret := '元'||v_ret;
ELSIF j = 7 THEN
v_ret := '万'||v_ret;
ELSIF j = 11 THEN
v_ret := '亿'||v_ret;
END IF;
ELSE
IF j = 12 THEN
v_ret := '拾' || v_ret ;
ELSIF j = 13 THEN
v_ret := '佰' || v_ret ;
ELSIF j = 14 THEN
v_ret := '仟' || v_ret ;
END IF ;
v_ret := Substr(c_dx1,i_money,1)||Substr(c_dx2,j,1)||v_ret;
bz := 0;
END IF;
j := j + 1;
END LOOP;
ELSE
v_ret := null;
END IF;
RETURN v_ret;
END N_To_C;
--
'转换大写的金额
Dim strNumber(10) As String
Dim strDigit(-3 To 7) As String
Dim strCurrency As String
Dim strReturn As String
Dim nIndex As Integer
Dim nDecimalIndex As Integer '小数点
strNumber(0) = "零"
strNumber(1) = "壹"
strNumber(2) = "贰"
strNumber(3) = "叁"
strNumber(4) = "肆"
strNumber(5) = "伍"
strNumber(6) = "陆"
strNumber(7) = "柒"
strNumber(8) = "捌"
strNumber(9) = "玖"
strDigit(-3) = "分"
strDigit(-2) = "角"
strDigit(-1) = "整"
strDigit(0) = "圆"
strDigit(1) = "拾"
strDigit(2) = "佰"
strDigit(3) = "仟"
strDigit(4) = "万"
strDigit(5) = "拾"
strDigit(6) = "佰"
strDigit(7) = "仟"
If dCurrency > 99999999.99 Then
getCapitalCurrency = CStr(dCurrency)
Exit Function
End If
strCurrency = Format(dCurrency, "0.00")
nDecimalIndex = InStr(strCurrency, ".")
If nDecimalIndex = 0 Then
nDecimalIndex = Len(strCurrency) + 1
End If
For nIndex = 1 To Len(strCurrency)
If nIndex <> nDecimalIndex Then
'非小数位处理
If strReturn <> "" Then
If Mid(strReturn, Len(strReturn) - 1, 1) = strNumber(0) And _
nIndex <= nDecimalIndex Then '是否已有零
If CInt(Mid(strCurrency, nIndex, 1)) = 0 Then '是否还是零
strReturn = Left(strReturn, Len(strReturn) - 2)
Else
strReturn = Left(strReturn, Len(strReturn) - 1)
End If
End If
End If
'前面是零,去除
If strReturn = strNumber(0) Then
strReturn = ""
End If
strReturn = strReturn + strNumber(CInt(Mid(strCurrency, nIndex, 1))) + strDigit(nDecimalIndex - nIndex - 1)
If Right(strReturn, 2) = strNumber(0) + strDigit(4) Then '零万处理
strReturn = Left(strReturn, Len(strReturn) - 2) + strDigit(4)
If strNumber(CInt(Mid(strCurrency, nIndex + 1, 1))) <> strNumber(0) Then
strReturn = strReturn + strNumber(0) '如后面跟非零仟
End If
End If
Else
'小数位 元
If strReturn <> "" Then
If Mid(strReturn, Len(strReturn) - 1, 1) = strNumber(0) Then
strReturn = Left(strReturn, Len(strReturn) - 2) + strDigit(0)
Else
strReturn = strReturn
End If
Else
strReturn = strNumber(0) + strDigit(0)
End If
End If
Next
If nIndex = nDecimalIndex Then '没有小数位
If Mid(strReturn, Len(strReturn) - 1, 1) = strNumber(0) Then
strReturn = Left(strReturn, Len(strReturn) - 2) + strDigit(0) + _
strNumber(0) + strDigit(-2) + strNumber(0) + strDigit(-3)
End If
End If
getCapitalCurrency = strReturn
End Function
我试了一下你的程序:say_cny(10) = 壹拾元整
呵呵,好像平常不这么说吧~~
select say_cny(1800005023.04) from dual;
SAY_CNY(1800005023.04)
------------------------------------------------------
壹拾捌亿万伍仟零贰拾叁元肆分select n_to_c(1800005023.04) from dual;
N_TO_C(1800005023.04)
-------------------------------------------------------
壹拾捌亿万零伍仟零贰拾叁元零肆分
//说明:本函数实现将付款金额由小写转换为大写。
//只对0 -- 99,999,999.99即亿元以下的数据有效。
String LowToUpp(String RMB)
{
const String cuppa = "零壹贰叁肆伍陆柒捌玖";
const String cuppb = "整分角圆拾佰仟万亿";
int i,p;
String upp = "";
String ma,oma="1";
String RMB1,RMB2;
int len1;
bool zheng = false;
int len = RMB.Length();
p = RMB.Pos('.');
RMB1 = RMB.SubString(1,p - 1);
RMB2 = RMB.SubString(p + 1,len - p);
len1 = RMB1.Length();
//对小数点后的数据进行处理
if ((RMB2.SubString(1,1) == "0") && (RMB2.SubString(2,1) == "0"))
zheng = true;
else
for (i = 0; i <2 ; i ++)
{
ma = RMB2.SubString(2 - i,1);
if ((len1 == 1) && (RMB1.SubString(1,1) == 0))
{
if (ma != "0")
upp = cuppa.SubString(2 * StrToInt(ma) + 1,2)
+ cuppb.SubString(2 * i + 3,2) + upp;
}
else
if (ma == "0")
{
if (i == 1)
upp = cuppa.SubString(2 * StrToInt(ma) + 1,2) + upp;
}
else
upp = cuppa.SubString(2 * StrToInt(ma) + 1,2)
+ cuppb.SubString(2 * i + 3,2) + upp;;
}
if ((len1 == 1) && (RMB1.SubString(1,1) == 0)) //如果只有小数位,返回结果
return upp; for (i = 0; i < len1; i ++)//对小数点前的数据进行处理
{
ma = RMB1.SubString( len1 - i,1);
if (i == 0)//元位
{
if (ma != "0")
{
upp = cuppa.SubString(2 * StrToInt(ma) + 1,2)
+ cuppb.SubString(2 * i + 7,2) + upp;
oma = "1";
}
else
{
upp = cuppb.SubString(2 * i +7,2) + upp;
oma = ma;
}
}
else
{
if (ma != "0")
{
if ( i == 4)//万元位
upp = cuppa.SubString(2 * StrToInt(ma) + 1, 2) +
cuppb.SubString(2 * i + 7, 2) + upp;
if (i > 4)
upp = cuppa.SubString(2 * StrToInt(ma) + 1, 2) +
cuppb.SubString(2 * i - 1, 2) + upp;
if ( i < 4)
upp = cuppa.SubString(2 * StrToInt(ma) + 1, 2) +
cuppb.SubString(2 * i + 7, 2) + upp;
oma = "1";
}
else
{
if ( i == 4)//万元位
upp = cuppb.SubString(2 * i + 7, 2) + upp;
else
if (ma != oma)
upp = cuppa.SubString(1, 2) + upp;
oma = ma;
}
}
}
if (zheng)
upp = upp + cuppb.SubString(1,2);//对没有小数位的加'整'
return upp;
}