is_zero := TRUE; z_count := 0; i := 0; WHILE i < len LOOP i := i + 1; n := substr(c_money, i, 1);
IF n = '0' THEN
IF len - i = 10 OR len - i = 6 OR len - i = 2 OR len = i THEN IF is_zero THEN b_string := substr(b_string, 1, length(b_string) - 1); is_zero := FALSE; END IF; case when len - i = 10 then b_string := b_string || '亿'; when len - i = 6 then b_string := b_string || '万'; when len - i = 2 then b_string := b_string || '圆'; when len = i then b_string := b_string || '整'; end case;
z_count := 0; ELSE IF z_count = 0 THEN b_string := b_string || '零'; is_zero := TRUE; END IF; z_count := z_count + 1;
END IF; ELSE b_string := b_string || substr(n_string, to_number(n), 1) || substr(m_string, len - i + 1, 1); z_count := 0; is_zero := FALSE; END IF; END LOOP;
b_string := l_sign || b_string ;
RETURN substr(b_string, 1, 30); END rmb_trans; --------------------------------------------------------------------- 调试: var v1 varchar2(80) exec :v1:=rmb_trans(455000045.34235) 注: round(n,m)四舍五入,如果省略m,则四舍五入到整数, 如果m是负数,则四舍五入到小数点前m位, 如果m是正数,则四舍五入到小数点前n位. select ltrim('morning','m'),ltrim('morning','or') from dual; 显示oring 显示morning ---作用 :去掉左边和右边的空格。 substr(c_money, i, 1) 取得子串,i是起始位置,1是子串长度,当i为负数,表示从尾数开始。
return varchar2
-- CopyRight Toshiba Shenzhen Han Fangquan 2002.04.10
as
type myArray is table of varchar2(255);
l_str myArray := myArray( '',
' Thousand ', ' Million ',
' Billion ', ' Trillion ',
' Quadrillion ', ' Quintillion ',
' Sextillion ', ' Septillion ',
' Octillion ', ' Nonillion ',
' Decillion ', ' Undecillion ',
' Duodecillion ' );
l_num varchar2(50) default trunc( p_number );
h_number number;
l_return varchar2(4000);
begin
h_number := round(p_number*100)/100;
for i in 1 .. l_str.count
loop
exit when l_num is null;if ( substr(l_num, length(l_num)-2, 3) <> 0 )
then
l_return := to_char(
to_date(
substr(l_num, length(l_num)-2, 3),
'J' ),
'Jsp' ) || l_str(i) || l_return;
end if;
l_num := substr( l_num, 1, length(l_num)-3 );
end loop;
-- add Dollars string. Han Fangquan
-- Han Fangquan modified begin
if l_return is null then l_return := 'Zero' ;
end if;
-- p_number >1 then add Dollars else Add Dollar.
if trunc( h_number )>1
then
l_return := l_return || ' Dollars ';
else
l_return := l_return || ' Dollar ';
end if;if to_char( h_number ) like '%.%'
then
l_num := substr(h_number,instr(h_number,'.')+1);
if length(l_num)=1
then
l_num := l_num || '0';
end if;
if l_num > 0
then
if l_num > 1
then
l_return := l_return ||'And '||l_num||' Cents ';
else l_return := l_return ||'And '||l_num||' Cent ';
end if;
end if;
end if;
-- end (Han Fangquan modified )
/*
-- beginning of section added to include decimal places:
if to_char( h_number ) like '%.%'
then
l_num := substr( h_number, instr( h_number, '.' )+1 );
if l_num > 0
then
l_return := l_return || ' point';
for i in 1 .. length (l_num)
loop
exit when l_num is null;
if substr( l_num, 1, 1 ) = '0'
then
l_return := l_return || ' zero';
else
l_return := l_return
|| ' '
|| to_char(
to_date(
substr( l_num, 1, 1),
'j' ),
'jsp' );
end if;
l_num := substr( l_num, 2 );
end loop;
end if;
end if;
*/-- end of section added to include decimal places
-- Han if the length fo l_trun less than 100 ,i will add **
-- if length(l_return) < 100
-- then
-- l_return := l_return ||
-- substr
-- length(l_return)-1) ;
-- end if;
l_return := l_return||'*************';
return l_return;
end tcos_spell_number;
FUNCTION rmb_trans(s_money NUMBER) RETURN VARCHAR2
IS
c_money VARCHAR2(12);
m_string VARCHAR2(60) := '分角圆拾佰仟万拾佰仟亿拾';
n_string VARCHAR2(40) := '壹贰叁肆伍陆柒捌玖';
b_string VARCHAR2(80);
n CHAR;
len NUMBER(3);
i NUMBER(3);
tmp NUMBER(12);
is_zero BOOLEAN;
z_count NUMBER(3);
l_money NUMBER;
l_sign VARCHAR2(10);
BEGIN
l_money := abs(s_money);
IF s_money < 0 THEN
l_sign := '负' ;
ELSE
l_sign := '';
END IF;
tmp := round(l_money, 2) * 100;
c_money := rtrim(ltrim(to_char(tmp, '999999999999')));
len := length(c_money);
is_zero := TRUE;
z_count := 0;
i := 0;
WHILE i < len LOOP
i := i + 1;
n := substr(c_money, i, 1);
IF n = '0' THEN
IF len - i = 10 OR len - i = 6 OR len - i = 2 OR len = i THEN
IF is_zero THEN
b_string := substr(b_string, 1, length(b_string) - 1);
is_zero := FALSE;
END IF;
case
when len - i = 10 then
b_string := b_string || '亿';
when len - i = 6 then
b_string := b_string || '万';
when len - i = 2 then
b_string := b_string || '圆';
when len = i then
b_string := b_string || '整';
end case;
z_count := 0; ELSE
IF z_count = 0 THEN
b_string := b_string || '零';
is_zero := TRUE;
END IF; z_count := z_count + 1;
END IF; ELSE
b_string := b_string || substr(n_string, to_number(n), 1) ||
substr(m_string, len - i + 1, 1);
z_count := 0;
is_zero := FALSE;
END IF;
END LOOP;
b_string := l_sign || b_string ;
RETURN substr(b_string, 1, 30);
END rmb_trans;
---------------------------------------------------------------------
调试:
var v1 varchar2(80)
exec :v1:=rmb_trans(455000045.34235)
注:
round(n,m)四舍五入,如果省略m,则四舍五入到整数,
如果m是负数,则四舍五入到小数点前m位,
如果m是正数,则四舍五入到小数点前n位.
select ltrim('morning','m'),ltrim('morning','or') from dual;
显示oring 显示morning
---作用 :去掉左边和右边的空格。
substr(c_money, i, 1) 取得子串,i是起始位置,1是子串长度,当i为负数,表示从尾数开始。