CREATE OR REPLACE FUNCTION num2rmb(Pi_MONEY NVARCHAR2) RETURN NVARCHAR2 IS
-- PURPOSE :返回人民币大写方式
v_NUM NUMBER;
I NUMBER;
J NUMBER;
K NUMBER;
L NUMBER;
FS1 NVARCHAR2(500);
FS2 NVARCHAR2(500);
FS3 NVARCHAR2(500);
S NVARCHAR2(500);
S1 NVARCHAR2(500);
S2 NVARCHAR2(500);
ZERO NUMBER;
POS NUMBER;
H NUMBER;
T NUMBER(5,4);
v_money NUMBER;
BEGIN
v_money := round(Pi_MONEY,2);
IF NVL(v_money,0)=0
OR INSTR(v_money,'.')>=18 THEN
RETURN '无';
END IF;
ZERO :=0;
POS :=0;
FS1 :='零壹贰叁肆伍陆柒捌玖';
FS2 :='仟佰拾万仟佰拾元';
FS3 :='仟佰拾万仟佰拾亿';
H := TRUNC(v_money);---整数部分
T := v_money - H;---小数部分
IF T =0 THEN
---形成字符串
S := LPAD(TO_CHAR(H),16,'')||'.0000';
ELSE
S := LPAD(TO_CHAR(H),16,'')|| RPAD(TO_CHAR(T),5,'');
END IF;
S1 := SUBSTR(S,1,8)||'.';
--整理亿位数字
FOR I IN 1..8 LOOP
IF I >= POS THEN
---else skip 0
L := TO_NUMBER(SUBSTR(S1, I,1));
IF L >0 THEN
--add fs1 and fs2 char
S2 := S2 || SUBSTR(FS1, L +1,1)|| SUBSTR(FS3, I,1);
ZERO :=1;--有整数
ELSE
IF I =4 THEN
S2 := S2 ||'万';
END IF;---插入万字，因为从此出分隔一次循环
POS := I +1;
FOR J IN I +1..9 LOOP
IF J =4
AND SUBSTR(S1, J,1)=''
AND S2 IS NOT NULL
AND ZERO =1 THEN
S2 := S2 ||'万';
END IF;
IF SUBSTR(S1, J,1)='' THEN
POS := POS +1;
END IF;
EXIT WHEN SUBSTR(S1, J,1)!='';--POS >=9;
END LOOP;
IF POS =9
AND SUBSTR(S1,1,8)>=1 THEN
S2 := S2 ||'亿';--无个亿位,直接加亿
ELSE
IF ZERO =1 THEN
S2 := S2 ||'零';
END IF;--不到个亿位,补中间零 eg 10101
END IF;
END IF;
END IF;
END LOOP;
ZERO :=0;
POS :=0;
--整理亿位之后的数据
S1 := SUBSTR(S,9);
FOR I IN 1..8 LOOP
IF I =1
AND TO_NUMBER(SUBSTR(S1, I,1))=0
AND S2 IS NOT NULL THEN
S2 := S2 ||'零';
END IF;
IF I >= POS THEN
---else skip 0
L := TO_NUMBER(SUBSTR(S1, I,1));
IF L >0 THEN
--add fs1 and fs2 char
S2 := S2 || SUBSTR(FS1, L +1,1)|| SUBSTR(FS2, I,1);
ZERO :=1;--有整数
ELSE
IF I =4 THEN
S2 := S2 ||'万';
END IF;---插入万字，因为从此出分隔一次循环
POS := I +1;
FOR J IN I +1..9 LOOP
IF J =4
AND SUBSTR(S1, J,1)=''
AND S2 IS NOT NULL
AND ZERO =1 THEN
S2 := S2 ||'万';
END IF;
IF SUBSTR(S1, J,1)='' THEN
POS := POS +1;
END IF;
EXIT WHEN SUBSTR(S1, J,1)!='';
END LOOP;
IF POS =9
AND v_money >=1 THEN
S2 := S2 ||'元';--无个位,直接加元
ELSE
IF ZERO =1 THEN
S2 := S2 ||'零';
END IF;--不到个位,补中间零 eg 10101.00
END IF;
END IF;
END IF;
END LOOP;
IF SUBSTR(S1,10,4)='' THEN
S2 := S2 ||'整';
ELSE
L := TO_NUMBER(SUBSTR(S1,10,1));
IF L >0 THEN
S2 := S2 || SUBSTR(FS1, L *1+1,1)||'角';
ELSE
IF v_money >0.1 THEN
S2 := S2 ||'零';
END IF;--去掉0.01的前导零
END IF;
L := TO_NUMBER(SUBSTR(S1,11,1));
IF L >0 THEN
S2 := S2 || SUBSTR(FS1, L *1+1,1)||'分';
END IF;
END IF;
RETURN S2;
END num2rmb;