17,377
社区成员
发帖
与我相关
我的任务
分享请教一个SQL把数字金额转为大写金额的方法或者解决办法
网上找了都是大堆大堆的牛掰代码，自己用又报错不知道错在哪里，提示方法无效！
在线等...已经找了两个小时，头疼
在线等...已经找了两个小时，头疼
...全文
请发表友善的回复…
发表回复
莫欺少年穷 2013-09-24
- 打赏
- 举报
5楼有正确做法，我的做法如下，也是正确的：
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(2367893456.123) From dual;
贰拾叁亿陆仟柒佰捌拾玖万叁仟肆佰伍拾陆圆壹角贰分
u010412956 2013-09-11
- 打赏
- 举报
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;
居然有人说长得比我帅 2013-09-11
- 打赏
- 举报
来蹭分
无敌小二傻 2013-09-11
- 打赏
- 举报
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;
Leon_He2014 2013-09-11
- 打赏
- 举报
怎么解决的呢
莫欺少年穷 2013-09-11
- 打赏
- 举报
解决了，回帖一个可以结贴了
