这是一个可以再进一步优化的存储过程,借鉴了网上流传的金额转换代码。
--将阿拉伯数字转为中文小数
function araNum2CnDec(p_num number) return varchar2 is
sdec varchar2(50);
i pls_integer;
len pls_integer;
sch varchar2(20) := '〇一二三四五六七八九';
scndec varchar2(100);
num pls_integer;
begin
sdec := to_char(p_num);
if instr(sdec, '.') = 0 then
return null;
else
sdec := substr(sdec, instr(sdec, '.') + 1);
len := length(sdec);
for i in 1 .. len loop
num := to_number(substr(sdec, i, 1));
scndec := scndec || substr(sch, num + 1, 1);
end loop;
return '〇点' || scndec;
end if;
end;
--将阿拉伯数字转为中文整数
function araNum2CnInt(p_num number) RETURN VARCHAR2 IS
i pls_integer;
sint varchar2(50) := ltrim(to_char(abs(p_num),
'99999999999999999999999999999990'));
len pls_integer := length(sint);
sch varchar2(20) := '〇一二三四五六七八九';
sjin varchar2(100) := '十百千万十百千亿十百千万十百千万十百千万十百千万十百千万十百千万';
scnint varchar2(100) := '';
num pls_integer;
s_num pls_integer := 0; --'0'起始位置
e_num pls_integer := 0; --'0'结束位置
begin
for i in 1 .. len loop
num := to_number(substr(sint, i, 1));
if num <> 0 then
--非'0'时处理:
if s_num = 0 then
--前面无零时处理
if i = len then
--最后一位
scnint := scnint || substr(sch, num + 1, 1);
else
scnint := scnint || substr(sch, num + 1, 1) ||
substr(sjin, len - i, 1); --前面字符非'0', 正常联接...
end if;
else
--前面有零时处理
scnint := scnint || case
when s_num < 8 and s_num >= 5 and e_num <= 5 then --前面只有一个'0'
'万'
when s_num < 12 and s_num >= 8 and e_num <= 8 then --否则(多个'0'处理)
'亿'
when s_num >= 12 and e_num < 8 then
trim(lpad(' ', ((s_num - 12) / 4 + 1) * 2, '万')) || '亿'
when s_num >= 12 and e_num >= 8 then
trim(lpad(' ', ((s_num - e_num) / 4 + 1) * 2, '万'))
end || case
when s_num - e_num >= 0 then
'〇'
end;
if i = len then
scnint := scnint || substr(sch, num + 1, 1);
else
scnint := scnint || substr(sch, num + 1, 1) ||
substr(sjin, len - i, 1);
end if;
end if;
s_num := 0; --重置为前面无零
e_num := 0;
else
--遇零时处理
if s_num = 0 then
--当s_num = 0时'0'串起始,
s_num := len - i + 1; --记录开始
e_num := s_num; --结束位置。
else
e_num := len - i + 1; --新的结束位置。
end if;
end if;
end loop;
--一直0到结尾的情况
if s_num <> 0 then
scnint := scnint || case
when s_num = len then
'〇'
when s_num < 8 and s_num >= 5 then
'万'
when s_num > 8 then
trim(lpad(' ', trunc((s_num - 9) / 4+1) * 2, '万')) || '亿'
end;
end if;
--处理"一十"开头的数字为"十"的情况
if instr(scnint, '一十') = 1 then
scnint := substr(scnint, 2);
end if;
if p_num < 0 then
scnint := '负' || scnint;
end if;
RETURN scnint;
end;
--将阿拉伯数字转为中文数字
function to_cnNumber(p_num number) return string is
begin
return araNum2CnInt(p_num) || substr(araNum2CnDec(p_num), 2);
end;