云萌主云应用官方论坛

标题: 金额大写转小写SQL [打印本页]

---

作者: 骑单车的小女孩    时间: 2016-12-30 00:22
标题: 金额大写转小写SQL


金额大写转小

1. Create Or Replace Function Money2Chinese(Money In Number) Return Varchar2 Is
   
2. 
   
3.   strYuan       Varchar2(150);
   
4. 
   
5.   strYuanFen    Varchar2(152);
   
6. 
   
7.   numLenYuan    Number;
   
8. 
   
9.   numLenYuanFen Number;
   
10. 
    
11.   strRstYuan    Varchar2(600);
    
12. 
    
13.   strRstFen     Varchar2(200);
    
14. 
    
15.   strRst        Varchar2(800);
    
16. 
    
17.   Type typeTabMapping Is Table Of Varchar2(2) Index By Binary_Integer;
    
18. 
    
19.   tabNumMapping  typeTabMapping;
    
20. 
    
21.   tabUnitMapping typeTabMapping;
    
22. 
    
23.   numUnitIndex   Number;
    
24. 
    
25.   i              Number;
    
26. 
    
27.   j              Number;
    
28. 
    
29.   charCurrentNum Char(1);
    
30. 
    
31. Begin
    
32. 
    
33.   If Money Is Null Then
    
34. 
    
35.     Return Null;
    
36. 
    
37.   End If;
    
38. 
    
39.   strYuan := TO_CHAR(FLOOR(Money));
    
40. 
    
41.   If strYuan = '0' Then
    
42. 
    
43.     numLenYuan := 0;
    
44. 
    
45.     strYuanFen := lpad(TO_CHAR(FLOOR(Money * 100)), 2, '0');
    
46. 
    
47.   Else
    
48. 
    
49.     numLenYuan := length(strYuan);
    
50. 
    
51.     strYuanFen := TO_CHAR(FLOOR(Money * 100));
    
52. 
    
53.   End If;
    
54. 
    
55.   If strYuanFen = '0' Then
    
56. 
    
57.     numLenYuanFen := 0;
    
58. 
    
59.   Else
    
60. 
    
61.     numLenYuanFen := length(strYuanFen);
    
62. 
    
63.   End If;
    
64. 
    
65.   If numLenYuan = 0 Or numLenYuanFen = 0 Then
    
66. 
    
67.     strRst := '零圆整';
    
68. 
    
69.     Return strRst;
    
70. 
    
71.   End If;
    
72. 
    
73.   tabNumMapping(0) := '零';
    
74. 
    
75.   tabNumMapping(1) := '壹';
    
76. 
    
77.   tabNumMapping(2) := '贰';
    
78. 
    
79.   tabNumMapping(3) := '叁';
    
80. 
    
81.   tabNumMapping(4) := '肆';
    
82. 
    
83.   tabNumMapping(5) := '伍';
    
84. 
    
85.   tabNumMapping(6) := '陆';
    
86. 
    
87.   tabNumMapping(7) := '柒';
    
88. 
    
89.   tabNumMapping(8) := '捌';
    
90. 
    
91.   tabNumMapping(9) := '玖';
    
92. 
    
93.   tabUnitMapping(-2) := '分';
    
94. 
    
95.   tabUnitMapping(-1) := '角';
    
96. 
    
97.   tabUnitMapping(1) := '';
    
98. 
    
99.   tabUnitMapping(2) := '拾';
    
100. 
     
101.   tabUnitMapping(3) := '佰';
     
102. 
     
103.   tabUnitMapping(4) := '仟';
     
104. 
     
105.   tabUnitMapping(5) := '万';
     
106. 
     
107.   tabUnitMapping(6) := '拾';
     
108. 
     
109.   tabUnitMapping(7) := '佰';
     
110. 
     
111.   tabUnitMapping(8) := '仟';
     
112. 
     
113.   tabUnitMapping(9) := '亿';
     
114. 
     
115.   For i In 1 .. numLenYuan Loop
     
116. 
     
117.     j            := numLenYuan - i + 1;
     
118. 
     
119.     numUnitIndex := Mod(i, 8);
     
120. 
     
121.     If numUnitIndex = 0 Then
     
122. 
     
123.       numUnitIndex := 8;
     
124. 
     
125.     End If;
     
126. 
     
127.     If numUnitIndex = 1 And i > 1 Then
     
128. 
     
129.       strRstYuan := tabUnitMapping(9) || strRstYuan;
     
130. 
     
131.     End If;
     
132. 
     
133.     charCurrentNum := substr(strYuan, j, 1);
     
134. 
     
135.     If charCurrentNum <> 0 Then
     
136. 
     
137.       strRstYuan := tabNumMapping(charCurrentNum) ||
     
138. 
     
139.                     tabUnitMapping(numUnitIndex) || strRstYuan;
     
140. 
     
141.     Else
     
142. 
     
143.       If (i = 1 Or i = 5) Then
     
144. 
     
145.         If substr(strYuan, j - 3, 4) <> '0000' Then
     
146. 
     
147.           strRstYuan := tabUnitMapping(numUnitIndex) || strRstYuan;
     
148. 
     
149.         End If;
     
150. 
     
151.       Else
     
152. 
     
153.         If substr(strYuan, j + 1, 1) <> '0' Then
     
154. 
     
155.           strRstYuan := tabNumMapping(charCurrentNum) || strRstYuan;
     
156. 
     
157.         End If;
     
158. 
     
159.       End If;
     
160. 
     
161.     End If;
     
162. 
     
163.   End Loop;
     
164. 
     
165.   For i In -2 .. -1 Loop
     
166. 
     
167.     j              := numLenYuan - i;
     
168. 
     
169.     charCurrentNum := substr(strYuanFen, j, 1);
     
170. 
     
171.     If charCurrentNum <> '0' Then
     
172. 
     
173.       strRstFen := tabNumMapping(charCurrentNum) || tabUnitMapping(i) ||
     
174. 
     
175.                    strRstFen;
     
176. 
     
177.     End If;
     
178. 
     
179.   End Loop;
     
180. 
     
181.   If strRstYuan Is Not Null Then
     
182. 
     
183.     strRstYuan := strRstYuan || '圆';
     
184. 
     
185.   End If;
     
186. 
     
187.   If strRstFen Is Null Then
     
188. 
     
189.     strRstYuan := strRstYuan || '整';
     
190. 
     
191.   Elsif length(strRstFen) = 2 And substr(strRstFen, 2) = '角' Then
     
192. 
     
193.     strRstFen := strRstFen || '整';
     
194. 
     
195.   End If;
     
196. 
     
197.   strRst := strRstYuan || strRstFen;
     
198. 
     
199.   --strRst := Replace(strRst, '亿零', '亿');
     
200. 
     
201.   --strRst := Replace(strRst, '万零', '万');
     
202. 
     
203.   Return strRst;
     
204. 
     
205. End Money2Chinese;

复制代码

写SQL

---

欢迎光临 云萌主云应用官方论坛 (https://yunmengzhu.com/)

Powered by Discuz! X3.5