Create Or Replace Function Money2Chinese
(Money In Number) Return Varchar2 Is
strYuan Varchar2(150);
strYuanFen Varchar2(152);
numLenYuan Number;
numLenYuanFen Number;
strRstYuan Varchar2(600);
strRstFen Varchar2(200);
strRst Varchar2(800);
Type typeTabMapping Is Table Of Varchar2(2) Index By Binary_Integer;
tabNumMapping typeTabMapping;
tabUnitMapping typeTabMapping;
numUnitIndex Number;
i Number;
j Number;
charCurrentNum Char(1);
Begin
If Money Is Null Then
Return Null;
End If;
strYuan := TO_CHAR(FLOOR(Money));
If strYuan = '0' Then
numLenYuan := 0;
strYuanFen := lpad(TO_CHAR(FLOOR(Money * 100)), 2, '0');
Else
numLenYuan := length(strYuan);
strYuanFen := TO_CHAR(FLOOR(Money * 100));
End If;
If strYuanFen = '0' Then
numLenYuanFen := 0;
Else
numLenYuanFen := length(strYuanFen);
End If;
If numLenYuan = 0 Or numLenYuanFen = 0 Then
strRst := '';
Return strRst;
End If;
tabNumMapping(0) := '零';
tabNumMapping(1) := '壹';
tabNumMapping(2) := '贰';
tabNumMapping(3) := '叁';
tabNumMapping(4) := '肆';
tabNumMapping(5) := '伍';
tabNumMapping(6) := '陆';
tabNumMapping(7) := '柒';
tabNumMapping(8) := '捌';
tabNumMapping(9) := '玖';
tabUnitMapping(-2) := '分';
tabUnitMapping(-1) := '角';
tabUnitMapping(1) := '';
tabUnitMapping(2) := '拾';
tabUnitMapping(3) := '佰';
tabUnitMapping(4) := '仟';
tabUnitMapping(5) := '万';
tabUnitMapping(6) := '拾';
tabUnitMapping(7) := '佰';
tabUnitMapping(8) := '仟';
tabUnitMapping(9) := '亿';
For i In 1 .. numLenYuan Loop
j := numLenYuan - i + 1;
numUnitIndex := Mod(i, 8);
If numUnitIndex = 0 Then
numUnitIndex := 8;
End If;
If numUnitIndex = 1 And i > 1 Then
strRstYuan := tabUnitMapping(9) || strRstYuan;
End If;
charCurrentNum := substr(strYuan, j, 1);
If charCurrentNum <> 0 Then
strRstYuan := tabNumMapping(charCurrentNum) ||
tabUnitMapping(numUnitIndex) || strRstYuan;
Else
If (i = 1 Or i = 5) Then
If substr(strYuan, j - 3, 4) <> '0000' Then
strRstYuan := tabUnitMapping(numUnitIndex) || strRstYuan;
End If;
Else
If substr(strYuan, j + 1, 1) <> '0' Then
strRstYuan := tabNumMapping(charCurrentNum) || strRstYuan;
End If;
End If;
End If;
End Loop;
For i In -2 .. -1 Loop
j := numLenYuan - i;
charCurrentNum := substr(strYuanFen, j, 1);
If charCurrentNum <> '0' Then
strRstFen := tabNumMapping(charCurrentNum) || tabUnitMapping(i) ||
strRstFen;
End If;
End Loop;
If strRstYuan Is Not Null Then
strRstYuan := strRstYuan || '';
End If;
If strRstFen Is Null Then
strRstYuan := strRstYuan || '';
Elsif length(strRstFen) = 2 And substr(strRstFen, 2) = '' Then
strRstFen := strRstFen || '';
End If;
strRst := strRstYuan || strRstFen;
--strRst := Replace(strRst, '亿零', '亿');
--strRst := Replace(strRst, '万零', '万');
Return strRst;
End Money2Chinese; select Money2Chinese(15) from dual帮修改一下看看
(Money In Number) Return Varchar2 Is
strYuan Varchar2(150);
strYuanFen Varchar2(152);
numLenYuan Number;
numLenYuanFen Number;
strRstYuan Varchar2(600);
strRstFen Varchar2(200);
strRst Varchar2(800);
Type typeTabMapping Is Table Of Varchar2(2) Index By Binary_Integer;
tabNumMapping typeTabMapping;
tabUnitMapping typeTabMapping;
numUnitIndex Number;
i Number;
j Number;
charCurrentNum Char(1);
Begin
If Money Is Null Then
Return Null;
End If;
strYuan := TO_CHAR(FLOOR(Money));
If strYuan = '0' Then
numLenYuan := 0;
strYuanFen := lpad(TO_CHAR(FLOOR(Money * 100)), 2, '0');
Else
numLenYuan := length(strYuan);
strYuanFen := TO_CHAR(FLOOR(Money * 100));
End If;
If strYuanFen = '0' Then
numLenYuanFen := 0;
Else
numLenYuanFen := length(strYuanFen);
End If;
If numLenYuan = 0 Or numLenYuanFen = 0 Then
strRst := '';
Return strRst;
End If;
tabNumMapping(0) := '零';
tabNumMapping(1) := '壹';
tabNumMapping(2) := '贰';
tabNumMapping(3) := '叁';
tabNumMapping(4) := '肆';
tabNumMapping(5) := '伍';
tabNumMapping(6) := '陆';
tabNumMapping(7) := '柒';
tabNumMapping(8) := '捌';
tabNumMapping(9) := '玖';
tabUnitMapping(-2) := '分';
tabUnitMapping(-1) := '角';
tabUnitMapping(1) := '';
tabUnitMapping(2) := '拾';
tabUnitMapping(3) := '佰';
tabUnitMapping(4) := '仟';
tabUnitMapping(5) := '万';
tabUnitMapping(6) := '拾';
tabUnitMapping(7) := '佰';
tabUnitMapping(8) := '仟';
tabUnitMapping(9) := '亿';
For i In 1 .. numLenYuan Loop
j := numLenYuan - i + 1;
numUnitIndex := Mod(i, 8);
If numUnitIndex = 0 Then
numUnitIndex := 8;
End If;
If numUnitIndex = 1 And i > 1 Then
strRstYuan := tabUnitMapping(9) || strRstYuan;
End If;
charCurrentNum := substr(strYuan, j, 1);
If charCurrentNum <> 0 Then
strRstYuan := tabNumMapping(charCurrentNum) ||
tabUnitMapping(numUnitIndex) || strRstYuan;
Else
If (i = 1 Or i = 5) Then
If substr(strYuan, j - 3, 4) <> '0000' Then
strRstYuan := tabUnitMapping(numUnitIndex) || strRstYuan;
End If;
Else
If substr(strYuan, j + 1, 1) <> '0' Then
strRstYuan := tabNumMapping(charCurrentNum) || strRstYuan;
End If;
End If;
End If;
End Loop;
For i In -2 .. -1 Loop
j := numLenYuan - i;
charCurrentNum := substr(strYuanFen, j, 1);
If charCurrentNum <> '0' Then
strRstFen := tabNumMapping(charCurrentNum) || tabUnitMapping(i) ||
strRstFen;
End If;
End Loop;
If strRstYuan Is Not Null Then
strRstYuan := strRstYuan || '';
End If;
If strRstFen Is Null Then
strRstYuan := strRstYuan || '';
Elsif length(strRstFen) = 2 And substr(strRstFen, 2) = '' Then
strRstFen := strRstFen || '';
End If;
strRst := strRstYuan || strRstFen;
--strRst := Replace(strRst, '亿零', '亿');
--strRst := Replace(strRst, '万零', '万');
Return strRst;
End Money2Chinese; select Money2Chinese(15) from dual帮修改一下看看
解决方案 »
- oracle 中truncate 和delete的区别
- linux下imp导入多个文件时出现syntax error near unexpected token '('
- 动态sql问题
- 欢迎加入oracle讨论群,绝对有效,群号1617315,不用验证,加入就行
- 如何让客户端的Oracle中的异常,以中文的方式显示
- 请各位oracle高手给优化一下查询sql
- 请教复制记录的触发器??
- oracle decode
- 字段varchar到nvarchar时,怎么将数据后面的空格去掉?
- 用DELPHI6.0如何存取ORACLE数据库中的 字段类型为BLOB的图象数据????
- 显示写法游标问题
- 求一个oracle存储过程谢谢
Oracle有处理函数,不用你从新定义
CREATE OR REPLACE FUNCTION money_to_chinese (money IN VARCHAR2)
RETURN VARCHAR2
IS
c_money VARCHAR2 (12);
m_string VARCHAR2 (60) := '分角圆拾佰仟万拾佰仟亿';
n_string VARCHAR2 (40) := '壹贰叁肆伍陆柒捌玖';
b_string VARCHAR2 (80);
n CHAR;
len NUMBER (3);
i NUMBER (3);
tmp NUMBER (12);
is_zero BOOLEAN;
z_count NUMBER (3);
l_money NUMBER;
l_sign VARCHAR2 (10);
BEGIN
l_money := ABS (money); IF money < 0
THEN
l_sign := '负';
ELSE
l_sign := '';
END IF; tmp := ROUND (l_money, 2) * 100;
c_money := RTRIM (LTRIM (TO_CHAR (tmp, '999999999999')));
len := LENGTH (c_money);
is_zero := TRUE;
z_count := 0;
i := 0; WHILE i < len
LOOP
i := i + 1;
n := SUBSTR (c_money,
i,
1
); IF n = '0'
THEN
IF len - i = 6 OR len - i = 2 OR len = i
THEN
IF is_zero
THEN
b_string := SUBSTR (b_string,
1,
LENGTH (b_string) - 1
);
is_zero := FALSE;
END IF; IF len - i = 6
THEN
b_string := b_string || '万';
END IF; IF len - i = 2
THEN
b_string := b_string || '圆';
END IF; IF len = i
THEN
b_string := b_string || '整';
END IF; z_count := 0;
ELSE
IF z_count = 0
THEN
b_string := b_string || '零';
is_zero := TRUE;
END IF; z_count := z_count + 1;
END IF;
ELSE
b_string :=
b_string
|| SUBSTR (n_string,
TO_NUMBER (n),
1
)
|| SUBSTR (m_string,
len - i + 1,
1
);
z_count := 0;
is_zero := FALSE;
END IF;
END LOOP; b_string := l_sign || b_string;
RETURN b_string;
EXCEPTION
WHEN OTHERS
THEN
RETURN (SQLERRM);
END;
一楼针对了所有的未知情况
如果一列里像 15,100,这样的数据有几百上千条,16,17,20,199 都有呢
难不成decode了所有的情况?