小写转大写金额存储过程[SQL SERVER]

2022-07-15 80酷酷网 80kuku.com

　　server|存储过程|大写|金额|小写原作在SQL 2000中有一些问题。修正错误并在SQL 2000中运行通过。

/********************************************************
作者：(wleii165yahoo.com)
版本：1.0
创建时间：20020227
修改时间：
功能：小写金额转换成大写
参数：n_LowerMoney 小写金额
v_TransType 种类 -- 1: directly translate, 0: read it in words
输出：大写金额
********************************************************/
CREATE PROCEDURE dbo.L2U
(
n_LowerMoney numeric(15,2),
v_TransType int,
RET VARCHAR(200) output
)
AS

Declare v_LowerStr VARCHAR(200) -- 小写金额
Declare v_UpperPart VARCHAR(200)
Declare v_UpperStr VARCHAR(200) -- 大写金额
Declare i_I int

set nocount on

select v_LowerStr = LTRIM(RTRIM(STR(n_LowerMoney,20,2))) --四舍五入为指定的精度并删除数据左右空格

select i_I = 1
select v_UpperStr = '

while ( i_I <= len(v_LowerStr))
begin
      select v_UpperPart = case substring(v_LowerStr,len(v_LowerStr) - i_I + 1,1)
                            WHEN '.' THEN '元'
                            WHEN '0' THEN '零'
                            WHEN '1' THEN '壹'
                            WHEN '2' THEN '贰'
                            WHEN '3' THEN '叁'
                            WHEN '4' THEN '肆'
                            WHEN '5' THEN '伍'
                            WHEN '6' THEN '陆'
                            WHEN '7' THEN '柒'
                            WHEN '8' THEN '捌'
                            WHEN '9' THEN '玖'
                            END
                          +
                            case i_I
                            WHEN 1 THEN '分'
                            WHEN 2 THEN '角'
                            WHEN 3 THEN '
                            WHEN 4 THEN '
                            WHEN 5 THEN '拾'
                            WHEN 6 THEN '佰'
                            WHEN 7 THEN '仟'
                            WHEN 8 THEN '万'
                            WHEN 9 THEN '拾'
                            WHEN 10 THEN '佰'
                            WHEN 11 THEN '仟'
                            WHEN 12 THEN '亿'
                            WHEN 13 THEN '拾'
                            WHEN 14 THEN '佰'
                            WHEN 15 THEN '仟'
                            WHEN 16 THEN '万'
                            ELSE '
                            END
select v_UpperStr = v_UpperPart + v_UpperStr
select i_I = i_I + 1
end

--------print '//v_UpperStr ='+v_UpperStr +'//'

if ( v_TransType=0 )
begin
select v_UpperStr = REPLACE(v_UpperStr,'零拾','零')
select v_UpperStr = REPLACE(v_UpperStr,'零佰','零')
select v_UpperStr = REPLACE(v_UpperStr,'零仟','零')
select v_UpperStr = REPLACE(v_UpperStr,'零零零','零')
select v_UpperStr = REPLACE(v_UpperStr,'零零','零')
select v_UpperStr = REPLACE(v_UpperStr,'零角零分','整')
select v_UpperStr = REPLACE(v_UpperStr,'零分','整')
select v_UpperStr = REPLACE(v_UpperStr,'零角','零')
select v_UpperStr = REPLACE(v_UpperStr,'零亿零万零元','亿元')
select v_UpperStr = REPLACE(v_UpperStr,'亿零万零元','亿元')
select v_UpperStr = REPLACE(v_UpperStr,'零亿零万','亿')
select v_UpperStr = REPLACE(v_UpperStr,'零万零元','万元')
select v_UpperStr = REPLACE(v_UpperStr,'万零元','万元')
select v_UpperStr = REPLACE(v_UpperStr,'零亿','亿')
select v_UpperStr = REPLACE(v_UpperStr,'零万','万')
select v_UpperStr = REPLACE(v_UpperStr,'零元','元')
select v_UpperStr = REPLACE(v_UpperStr,'零零','零')
end

-- 对壹元以下的金额的处理
if ( substring(v_UpperStr,1,1)='元' )
begin
select v_UpperStr = substring(v_UpperStr,2,(len(v_UpperStr) - 1))
end

if (substring(v_UpperStr,1,1)= '零')
begin
select v_UpperStr = substring(v_UpperStr,2,(len(v_UpperStr) - 1))
end

if (substring(v_UpperStr,1,1)='角')
begin
select v_UpperStr = substring(v_UpperStr,2,(len(v_UpperStr) - 1))
end

if ( substring(v_UpperStr,1,1)='分')
begin
select v_UpperStr = substring(v_UpperStr,2,(len(v_UpperStr) - 1))
end

if (substring(v_UpperStr,1,1)='整')
begin
select v_UpperStr = '零元整'
end

select ret=v_UpperStr

调用过程：

declare ret varchar(200)

exec L2U 567983.897,1,ret output

select ret