FUNCTION convert_quantity(p_digit IN NUMBER) RETURN VARCHAR2 IS
l_left_str VARCHAR2(100); ---左边字符串
l_right_str VARCHAR2(100); ---右边字符串
l_unit_str VARCHAR2(100); ---位
l_number_str VARCHAR2(100);
l_vt VARCHAR2(1000);
l_vn NUMBER;
l_left_length NUMBER; ---左边字符长度
i NUMBER;
n NUMBER;
m NUMBER;
l_new_digit NUMBER;
l_speech VARCHAR2(50);
l_amount_format VARCHAR2(50);
BEGIN
l_left_str := NULL;
l_right_str := NULL;
l_unit_str := '拾佰仟万拾佰仟亿拾佰仟';
l_number_str := '壹贰叁肆伍陆柒捌玖';
m := 1;
l_new_digit := to_char(p_digit);
i := 1;
l_vt := NULL;
IF p_digit = 0 THEN
RETURN '零';
END IF;
IF instr(l_new_digit, '.') = 0 THEN
l_left_str := l_new_digit;
l_right_str := NULL;
ELSIF instr(l_new_digit, '.') = 1 THEN
l_speech := '零';
l_right_str := substr(l_new_digit, instr(l_new_digit, '.') + 1, length(l_new_digit) - instr(l_new_digit, '.'));
ELSE
l_left_str := substr(l_new_digit, 1, instr(l_new_digit, '.') - 1);
l_right_str := substr(l_new_digit, instr(l_new_digit, '.') + 1, length(l_new_digit) - instr(l_new_digit, '.'));
END IF;
IF l_left_str IS NOT NULL THEN
l_left_length := length(l_left_str);
IF l_left_length <= 8 THEN
n := l_left_length;
WHILE i <= l_left_length - 1 LOOP
l_vn := to_number(substr(l_left_str, i, 1));
IF l_vn >= 1 THEN
IF m = 0 THEN
l_vt := l_vt || '零';
END IF;
l_vt := l_vt || substr(l_number_str, l_vn, 1) || substr(l_unit_str, n - 1, 1);
m := 1;
ELSE
IF n = 5 THEN
l_vt := l_vt || '万';
m := 1;
ELSE
m := 0;
END IF;
END IF; --end 零
i := i + 1;
n := n - 1;
END LOOP;
l_vn := to_number(substr(l_left_str, l_left_length, 1));
IF l_vn > 0 THEN
IF m = 0 THEN
l_vt := l_vt || '零';
END IF;
l_vt := l_vt || substr(l_number_str, l_vn, 1);
END IF;
IF to_number(substr(l_left_str, 1, 1)) = 0 OR l_left_length IS NULL THEN
l_vt := l_vt || '零';
END IF;
l_speech := l_speech || l_vt;
ELSE
RETURN '数量过大,不允许数量过亿';
END IF;
END IF;
l_vt := NULL;
IF length(l_right_str) >= 1 THEN
l_vt := '点';
i := 1;
WHILE i <= length(l_right_str) LOOP
l_vn := to_number(substr(l_right_str, i, 1));
IF l_vn = 0 THEN
l_vt := l_vt || '零';
ELSE
l_vt := l_vt || substr(l_number_str, l_vn, 1);
END IF;
i := i + 1;
END LOOP;
END IF;
l_speech := l_speech || l_vt;
RETURN l_speech;
END;
oracle 数量大写函数
最新推荐文章于 2025-08-07 21:09:17 发布