1 CREATE OR REPLACE FUNCTION num2rmb(Pi_MONEY NVARCHAR2) RETURN NVARCHAR2 IS
2 -- PURPOSE :返回人民币大写方式
3 v_NUM NUMBER;
4 I NUMBER;
5 J NUMBER;
6 K NUMBER;
7 L NUMBER;
8 FS1 NVARCHAR2(500);
9 FS2 NVARCHAR2(500);
10 FS3 NVARCHAR2(500);
11 S NVARCHAR2(500);
12 S1 NVARCHAR2(500);
13 S2 NVARCHAR2(500);
14 ZERO NUMBER;
15 POS NUMBER;
16 H NUMBER;
17 T NUMBER(5,4);
18 v_money NUMBER;
19 BEGIN
20 v_money := round(Pi_MONEY,2);
21 IF NVL(v_money,0)=0
22 OR INSTR(v_money,'.')>=18 THEN
23 RETURN '无';
24 END IF;
25 ZERO :=0;
26 POS :=0;
27 FS1 :='零壹贰叁肆伍陆柒捌玖';
28 FS2 :='仟佰拾万仟佰拾元';
29 FS3 :='仟佰拾万仟佰拾亿';
30 H := TRUNC(v_money);---整数部分
31 T := v_money - H;---小数部分
32 IF T =0 THEN
33 ---形成字符串
34 S := LPAD(TO_CHAR(H),16,'0')||'.0000';
35 ELSE
36 S := LPAD(TO_CHAR(H),16,'0')|| RPAD(TO_CHAR(T),5,'0');
37 END IF;
38 S1 := SUBSTR(S,1,8)||'.';
39 --整理亿位数字
40 FOR I IN 1..8 LOOP
41 IF I >= POS THEN
42 ---else skip 0
43 L := TO_NUMBER(SUBSTR(S1, I,1));
44 IF L >0 THEN
45 --add fs1 and fs2 char
46 S2 := S2 || SUBSTR(FS1, L +1,1)|| SUBSTR(FS3, I,1);
47 ZERO :=1;--有整数
48 ELSE
49 IF I =4 THEN
50 S2 := S2 ||'万';
51 END IF;---插入万字，因为从此出分隔一次循环
52 POS := I +1;
53 FOR J IN I +1..9 LOOP
54 IF J =4
55 AND SUBSTR(S1, J,1)='0'
56 AND S2 IS NOT NULL
57 AND ZERO =1 THEN
58 S2 := S2 ||'万';
59 END IF;
60 IF SUBSTR(S1, J,1)='0' THEN
61 POS := POS +1;
62 END IF;
63 EXIT WHEN SUBSTR(S1, J,1)!='0';--POS >=9;
64 END LOOP;
65 IF POS =9
66 AND SUBSTR(S1,1,8)>=1 THEN
67 S2 := S2 ||'亿';--无个亿位,直接加亿
68 ELSE
69 IF ZERO =1 THEN
70 S2 := S2 ||'零';
71 END IF;--不到个亿位,补中间零 eg 10101
72 END IF;
73 END IF;
74 END IF;
75 END LOOP;
76 ZERO :=0;
77 POS :=0;
78 --整理亿位之后的数据
79 S1 := SUBSTR(S,9);
80 FOR I IN 1..8 LOOP
81 IF I =1
82 AND TO_NUMBER(SUBSTR(S1, I,1))=0
83 AND S2 IS NOT NULL THEN
84 S2 := S2 ||'零';
85 END IF;
86 IF I >= POS THEN
87 ---else skip 0
88 L := TO_NUMBER(SUBSTR(S1, I,1));
89 IF L >0 THEN
90 --add fs1 and fs2 char
91 S2 := S2 || SUBSTR(FS1, L +1,1)|| SUBSTR(FS2, I,1);
92 ZERO :=1;--有整数
93 ELSE
94 IF I =4 THEN
95 S2 := S2 ||'万';
96 END IF;---插入万字，因为从此出分隔一次循环
97 POS := I +1;
98 FOR J IN I +1..9 LOOP
99 IF J =4
100 AND SUBSTR(S1, J,1)='0'
101 AND S2 IS NOT NULL
102 AND ZERO =1 THEN
103 S2 := S2 ||'万';
104 END IF;
105 IF SUBSTR(S1, J,1)='0' THEN
106 POS := POS +1;
107 END IF;
108 EXIT WHEN SUBSTR(S1, J,1)!='0';
109 END LOOP;
110 IF POS =9
111 AND v_money >=1 THEN
112 S2 := S2 ||'元';--无个位,直接加元
113 ELSE
114 IF ZERO =1 THEN
115 S2 := S2 ||'零';
116 END IF;--不到个位,补中间零 eg 10101.00
117 END IF;
118 END IF;
119 END IF;
120 END LOOP;
121 IF SUBSTR(S1,10,4)='0000' THEN
122 S2 := S2 ||'整';
123 ELSE
124 L := TO_NUMBER(SUBSTR(S1,10,1));
125 IF L >0 THEN
126 S2 := S2 || SUBSTR(FS1, L *1+1,1)||'角';
127 ELSE
128 IF v_money >0.1 THEN
129 S2 := S2 ||'零';
130 END IF;--去掉0.01的前导零
131 END IF;
132 L := TO_NUMBER(SUBSTR(S1,11,1));
133 IF L >0 THEN
134 S2 := S2 || SUBSTR(FS1, L *1+1,1)||'分';
135 END IF;
136 END IF;
137 RETURN S2;
138 END num2rmb;
