create or replace function comm.F_upper_money(p_num in number default null)
return nvarchar2 is
Result nvarchar2(100); --返回字符串
num_round nvarchar2(100) := to_char(abs(round(p_num, 2))); --转换数字为小数点后2位的字符(正数)
num_left nvarchar2(100); --小数点左边的数字
num_right nvarchar2(2); --小数点右边的数字
str1 nchar(10) := '零壹贰叁肆伍陆柒捌玖'; --数字大写
str2 nchar(16) := '元拾佰仟万拾佰仟亿拾佰仟万拾佰仟'; --数字位数(从低至高)
num_pre number(1) := 1; --前一位上的数字
num_current number(1); --当前位上的数字
num_count number := 0; --当前数字位数
begin
if p_num is null then
return null;
end if; --转换数字为null时返回null
select to_char(nvl(substr(to_char(num_round),
1,
decode(instr(to_char(num_round), '.'),
0,
length(num_round),
instr(to_char(num_round), '.') - 1)),
0))
into num_left
from dual; --取得小数点左边的数字
select substr(to_char(num_round),
decode(instr(to_char(num_round), '.'),
0,
length(num_round) + 1,
instr(to_char(num_round), '.') + 1),
2)
into num_right
from dual; --取得小数点右边的数字
if length(num_left) > 16 then
return '**********';
end if; --数字整数部分超过16位时
--采用从低至高的算法，先处理小数点右边的数字
if length(num_right) = 2 then
if to_number(substr(num_right, 1, 1)) = 0 then
result := '零' ||
substr(str1, to_number(substr(num_right, 2, 1)) + 1, 1) || '分';
else
result := substr(str1, to_number(substr(num_right, 1, 1)) + 1, 1) || '角' ||
substr(str1, to_number(substr(num_right, 2, 1)) + 1, 1) || '分';
end if;
elsif length(num_right) = 1 then
result := substr(str1, to_number(substr(num_right, 1, 1)) + 1, 1) || '角整';
else
result := '整';
end if;
--再处理小数点左边的数字
for i in reverse 1 .. length(num_left) loop
--(从低至高)
num_count := num_count + 1; --当前数字位数
num_current := to_number(substr(num_left, i, 1)); --当前位上的数字
if num_current > 0 then
--当前位上数字不为0按正常处理
result := substr(str1, num_current + 1, 1) ||
substr(str2, num_count, 1) || result;
else
--当前位上数字为0时
if mod(num_count - 1, 4) = 0 then
--当前位是元、万或亿时
result := substr(str2, num_count, 1) || result;
num_pre := 0; --元、万,亿前不准加零
end if;
if num_pre > 0 or length(num_left) = 1 then
--上一位数字不为0或只有个位时
result := substr(str1, num_current + 1, 1) || result;
end if;
end if;
num_pre := num_current;
end loop;
if p_num < 0 then
--转换数字是负数时
result := '负' || result;
end if;
return Result;
exception
when others then
raise_application_error(-20001, '数字转换大写出现错误！' || sqlerrm);
end;
--结束END--
本文标题: plsql小写金额转大写金额函数
本文链接: https://www.lsjlt.com/news/243004.html(转载时请注明来源链接)
有问题或投稿请发送至:
邮箱/279061341@qq.com QQ/279061341
0