create
or
replace
function
Fn_GetMoneyToChina(p_money
in
number)
return
varchar2
is
/*
功能:数字金额转化为大写金额
最大支持金额 9999999999999999.99 (支持千万亿的任何金额)
小数点支持2位
*/
type myArray
is
table
of
varchar2(255);
n_str myArray := myArray(
'壹'
,
'贰'
,
'叁'
,
'肆'
,
'伍'
,
'陆'
,
'柒'
,
'捌'
,
'玖'
,
'零'
);
u_str myArray := myArray(
'分'
,
'角'
,
'圆'
,
'拾'
,
'佰'
,
'仟'
,
'万'
,
'拾'
,
'佰'
,
'仟'
,
'亿'
,
'拾'
,
'佰'
,
'仟'
,
'万'
,
'拾'
,
'佰'
,
'仟'
);
signal varchar2(2) :=
null
;
cur_digit number(1) := 0;
pre_digit number(1) := 0;
str_length number(2) := 0;
w_cash varchar2(20);
l_return varchar2(200);
begin
if p_money < 0
then
signal :=
'负'
;
end
if;
if p_money = 0
then
return
'零'
;
end
if;
w_cash := to_char(
abs
(p_money)*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
l_return
is
null
then
l_return :=
'整'
;
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
l_return := u_str(i)||l_return;
elsif pre_digit <> 0
then
l_return :=
'零'
||l_return;
end
if;
else
l_return := n_str(cur_digit)||u_str(i)||l_return;
end
if;
end
if;
pre_digit := cur_digit;
end
loop;
if instr(l_return,
'万'
,1) - instr(l_return,
'亿'
,1) = 1
then
l_return :=
replace
(l_return,
'万'
,
null
);
end
if;
l_return := signal||l_return;
return
(l_return);
exception
when
others
then
l_return :=
' '
;
return
l_return;
end
Fn_GetMoneyToChina;