# 数据转换为金额大写

CREATE OR REPLACE PACKAGE NUMTOAMOUNT IS
FUNCTION SAY_MONEY(P_NUM NUMBER) RETURN VARCHAR2;
FUNCTION SAY_PERMILLION(P_NUM NUMBER) RETURN VARCHAR2;
FUNCTION SAY_CHINESE(P_NUM NUMBER) RETURN VARCHAR2;
END ;

CREATE OR REPLACE PACKAGE BODY NUMTOAMOUNT IS

-- 0到9十个数字转换为大写
FUNCTION SAY_MONEY(P_NUM NUMBER) RETURN VARCHAR2 IS
W_STR VARCHAR2(400);
BEGIN
SELECT TRANSLATE(P_NUM, '0123456789', '零壹贰叁肆伍陆柒捌玖') INTO W_STR FROM dual;
RETURN W_STR;
EXCEPTION WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;

-- 按每四位来转换
FUNCTION SAY_PERMILLION(P_NUM NUMBER) RETURN VARCHAR2 IS
W_STR VARCHAR2(400);
W_CTN NUMBER;
W_NUM NUMBER;
BEGIN
W_STR := '';
W_NUM := P_NUM;

W_CTN := TRUNC(W_NUM / 1000);
W_NUM := W_NUM MOD 1000;
IF W_CTN > 0 THEN
W_STR := W_STR || SAY_MONEY(W_CTN) || '仟';
END IF;

W_CTN := TRUNC(W_NUM / 100);
W_NUM := W_NUM MOD 100;
IF W_CTN > 0 THEN
W_STR := W_STR || SAY_MONEY(W_CTN) || '佰';
END IF;
-- 仟位非空，且佰位为0，把零取出
IF (W_STR IS NOT NULL AND W_CTN = 0)  THEN
W_STR := W_STR || SAY_MONEY(0);
END IF;

W_CTN := TRUNC(W_NUM / 10);
W_NUM := W_NUM MOD 10;
IF W_CTN > 0 THEN
W_STR := W_STR || SAY_MONEY(W_CTN) || '拾';
END IF;
-- 拾位之前非空，佰位非0，拾位为0，且个位不为0，把零取出
IF (W_STR IS NOT NULL AND W_CTN = 0 AND INSTR(W_STR, '零') != 3 AND W_NUM != 0)  THEN
W_STR := W_STR || SAY_MONEY(0);
END IF;

W_CTN := TRUNC(W_NUM / 1);
W_NUM := W_NUM MOD 1;
IF W_CTN > 0 THEN
W_STR := W_STR || SAY_MONEY(W_CTN);
END IF;
-- 仟位非零，佰位、拾位和个位为0，把佰位的零去掉(这种情况，佰位的零会取出，而拾位和个位的不会取出，因此要把多余的零去掉)
IF (W_CTN = 0 AND INSTR(W_STR,'仟') > 0 AND INSTR(W_STR,'零') = 3 AND INSTR(W_STR,'拾') != 5) THEN
W_STR := REPLACE(W_STR, '零', '');
END IF;

RETURN W_STR;
EXCEPTION WHEN NO_DATA_FOUND THEN
RETURN NULL;
END;

-- 数字转换为金额大写
FUNCTION SAY_CHINESE(P_NUM NUMBER) RETURN VARCHAR2 IS
W_CTN NUMBER;
W_NUM NUMBER;
W_STR VARCHAR2(2000);
BEGIN
W_STR := '';
W_NUM := P_NUM;
IF W_NUM > 999999999999 OR W_NUM < 0 THEN
RETURN NULL;
ELSIF W_NUM = 0 THEN
RETURN '零圆';
END IF;

-- 读取亿位
W_CTN := TRUNC(W_NUM / 100000000);
W_NUM := W_NUM MOD 100000000;
IF W_CTN > 0 THEN
W_STR := SAY_PERMILLION(W_CTN) || '亿';
END IF;

-- 读取万位
W_CTN := TRUNC(W_NUM / 10000);
W_NUM := W_NUM MOD 10000;
IF W_CTN > 0 THEN
-- 亿位非空，而仟万位为0，在读万的数据之前要加个零
IF (W_STR IS NOT NULL AND TRUNC(W_CTN / 1000) = 0 ) THEN
W_STR := W_STR || '零' || SAY_PERMILLION(W_CTN) || '万';
ELSE
W_STR := W_STR || SAY_PERMILLION(W_CTN) || '万' ;
END IF;
END IF;

-- 读取个位
W_CTN := TRUNC(W_NUM);
W_NUM := W_NUM MOD 1;
IF W_CTN > 0 THEN
-- 万位之前非空，而仟位为0，在读仟位的数据之前要加个零
IF (W_STR IS NOT NULL AND TRUNC(W_CTN / 1000) = 0 ) THEN
-- 数值为整数，在圆之后加个整字
IF W_NUM =0 THEN
W_STR := W_STR || '零' || SAY_PERMILLION(W_CTN) || '圆整';
ELSE
W_STR := W_STR || '零' || SAY_PERMILLION(W_CTN) || '圆';
END IF;
ELSE
IF W_NUM =0 THEN
W_STR := W_STR || SAY_PERMILLION(W_CTN) || '圆整';
ELSE
W_STR := W_STR || SAY_PERMILLION(W_CTN) || '圆';
END IF;
END IF;

END IF;

-- 读取小数
IF W_NUM > 0 THEN
-- 读取角
W_CTN := TRUNC(W_NUM*10);
W_NUM := (W_NUM*10) MOD 1;
IF W_CTN > 0 THEN
W_STR := W_STR || SAY_PERMILLION(W_CTN) || '角';
END IF;

-- 读取分
W_CTN := TRUNC(W_NUM*10);
W_NUM := (W_NUM*10) MOD 1;
IF W_CTN > 0 THEN
W_STR := W_STR || SAY_PERMILLION(W_CTN) || '分';
END IF;
END IF;
RETURN LTRIM(W_STR);
END;

END;

• 博文量
97
• 访问量
182568