CREATE OR REPLACE FUNCTION money_to_chinese (
money IN VARCHAR2)
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 (
money);
IF 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
= 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;
IF len - i
= 6
THEN
b_string := b_string
|| '万';
END IF;
IF len - i
= 2
THEN
b_string := b_string
|| '圆';
END IF;
IF len = i
THEN
b_string := b_string
|| '整';
END IF;
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 b_string;
EXCEPTION
WHEN OTHERS
THEN
RETURN (SQLERRM);
END;
/使用方法
SELECT money_to_chinese(ssje) as dx FROM ywdjb WHERE djh='02x1995052119950140'
转载于:https://www.cnblogs.com/siyunianhua/p/3359223.html
