create or replace function num2rmb(je number) return varchar2 is
result varchar2(100);
i pls_integer;
snum varchar2(20) := ltrim(replace(to_char(abs(je),
'9999999999999990.99'),
'.'));
len pls_integer := length(snum);
sch varchar2(20) := '零壹贰叄肆伍陆柒捌玖';
sjin varchar2(50) := '分角圆拾佰仟万拾佰仟亿拾佰仟万拾佰仟';
srmb varchar2(100) := '';
num Pls_Integer;
jin varchar(2);
s_num pls_integer := 0; --0起始位置
e_num pls_integer := 0; --0结束位置
begin
for i in 1 .. len loop
num := to_number(substr(snum, i, 1));
if num <> 0 then
--非‘0’时处理
if s_num = 0 then
srmb := srmb || substr(sch, num + 1, 1) ||
substr(sjin, len - i + 1, 1);
--前面字符非0时,正常连接
else
srmb := srmb || --否则
case
when s_num = e_num then --前面只有一个0
case s_num
when 7 then
'万' --只处理进位
when 11 then
'亿'
when 15 then
'万'
end
when e_num < 12 then --否则多个0处理
case
when s_num < 7 then
'' --一万以内
when s_num < 11 then
case
when e_num < 8 and s_num < 10 then
'万'
end
when s_num < 15 then
case
when e_num < 12 then
'亿'
end
else
'万亿'
end
when e_num < 16 and s_num > 14 then
'万'
end || case
when s_num > 3 and e_num < 3 then
'圆零'
when e_num = 3 then
'圆'
when e_num not in (7, 11, 15) or s_num - e_num > 2 then
'零'
end;
srmb := srmb || substr(sch, num + 1, 1) ||
substr(sjin, len - i + 1, 1);
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;
if s_num <> 0 then
--此时以0结尾
srmb := srmb || case
when s_num = len then
'零圆整' --全0串,加
when s_num = 1 or s_num = 2 then
'整' --分(1),角(2)后加
when s_num < 7 or s_num = 10 then
'圆整'
when s_num < 11 then
'万圆整'
when s_num < 15 then
'亿圆整'
else
'万亿圆整'
end;
else
srmb := srmb || '整'; --不以0结尾,加...
end if;
if je < 0 then
srmb := '负' || srmb;
end if;
return srmb;
end num2rmb;
--调用存储过程
select num2rmb(3454645435345123.45) RMB from dual;ps:不是本人自己写的,发个帖吹吹牛逼!
小数点三位,你这个存储过程就崩了~!select num2rmb(3454645435345123.4555) RMB from dual;小数点四位,你这个存储过程就溃了~!select num2rmb(3454645435345123.45555) RMB from dual;小数点五位,,,,也好....早死早超生..
CREATE OR REPLACE FUNCTION CONVERT_MONEY(x IN NUMBER DEFAULT 0)
RETURN VARCHAR2 IS
TYPE num_t IS table OF CHAR(2);
great_c num_t := num_t('零','壹','贰','叁','肆','伍','陆','柒','捌','玖');
unit_c num_t := num_t('分','角','圆','拾','佰','仟','万','拾','佰','仟','亿');
xx NUMBER;
great_list VARCHAR2(50);
BEGIN
xx := TRIM(TO_CHAR(ROUND(ABS(x),2) * 100,999999999999));
FOR i IN 1..LENGTH(xx) LOOP
great_list := great_list||great_c(TO_NUMBER(SUBSTR(xx,i,1))+1)||unit_c(LENGTH(xx)-i+1);
END LOOP;
RETURN great_list;
END;
CREATE OR REPLACE FUNCTION CONVERT_MONEY(x IN NUMBER DEFAULT 0)
RETURN VARCHAR2 IS
great_c VARCHAR2(20);
unit_c VARCHAR2(30);
xx NUMBER;
great_list VARCHAR2(50);
BEGIN
great_c := '零壹贰叁肆伍陆柒捌玖';
unit_c := '分角圆拾佰仟万拾佰仟亿';
xx := TRIM(TO_CHAR(ROUND(ABS(x),2) * 100,999999999999));
FOR i IN 1..LENGTH(xx) LOOP
great_list := great_list||substr(great_c,(TO_NUMBER(SUBSTR(xx,i,1))+1)*2-1,2)||
substr(unit_c,(LENGTH(xx)-i+1)*2-1,2);
END LOOP;
RETURN great_list;
END;