/*------Oracle数据库------*/
在存储过程中，将数据中的金额小写转大写的函数。
create or replace function F_upper_money(p_num in number default null)
return nvarchar2 is
/*Ver:1.0 Created By xsb on -8-18 For:
将金额数字(单位元)转换为大写(采用从低至高算法)
数字整数部分不得超过16位,可以是负数。
Ver：1.1 Modified By xsb on -8-20 For:个位数处理也放在For循环中。
Ver：1.2 Modified By xsb on -8-22 For:分后不带整字。
Ver：1.3 Modified By xsb on -8-28 For:完善测试用例。
测试用例:
SET HEAD OFF
SET FEED OFF
select '无参数时='||f_upper_money() from dual;
select 'null='||f_upper_money(null) from dual;
select '0='||f_upper_money(0) from dual;
select '0.01='||f_upper_money(0.01) from dual;
select '0.126='||f_upper_money(0.126) from dual;
select '01.234='||f_upper_money(01.234) from dual;
select '10='||f_upper_money(10) from dual;
select '100.1='||f_upper_money(100.1) from dual;
select '100.01='||f_upper_money(100.01) from dual;
select '10000='||f_upper_money(10000) from dual;
select '10012.12='||f_upper_money(10012.12) from dual;
select '20000020.01='||f_upper_money(20000020.01) from dual;
select '3040506708.901='||f_upper_money(3040506708.901) from dual;
select '40005006078.001='||f_upper_money(40005006078.001) from dual;
select '-123456789.98='||f_upper_money(-123456789.98) from dual;
select '123456789123456789.89='||f_upper_money(123456789123456789.89) from dual;
*/
Result nvarchar2(100);--返回字符串
num_round nvarchar2(100) :=to_char(abs(round(p_num,2)));--转换数字为小数点后2位的字符(正数)
num_left nvarchar2(100);--小数点左边的数字
num_right nvarchar2(2);--小数点右边的数字
str1 nchar(10) :='零壹贰参肆伍陆柒捌玖';--数字大写
str2 nchar(16) :='元拾佰仟万拾佰仟亿拾佰仟万拾佰仟';--数字位数(从低至高)
num_pre number(1):=1;--前一位上的数字
num_current number(1);--当前位上的数字
num_count number:=0;--当前数字位数
begin
if p_num is null then return null;end if;--转换数字为null时返回null
select to_char(
nvl(substr(to_char(num_round),1,
decode(instr(to_char(num_round),'.'),0,
length(num_round),instr(to_char(num_round),'.')-1)),
0)) into num_left from dual;--取得小数点左边的数字
select substr(to_char(num_round),
decode(instr(to_char(num_round),'.'),0,
length(num_round)+1,instr(to_char(num_round),'.')+1),2)
into num_right from dual;--取得小数点右边的数字
if length(num_left)>16 then return '**********'; end if;--数字整数部分超过16位时
--采用从低至高的算法，先处理小数点右边的数字
if length(num_right)=2 then
if to_number(substr(num_right,1,1))=0 then
result:='零'||substr(str1,to_number(substr(num_right,2,1))+1,1)||'分';
else
result:=substr(str1,to_number(substr(num_right,1,1))+1,1)||'角'||
substr(str1,to_number(substr(num_right,2,1))+1,1)||'分';
end if;
elsif length(num_right)=1 then
result:=substr(str1,to_number(substr(num_right,1,1))+1,1)||'角整';
else
result :='整';
end if;
--再处理小数点左边的数字
for i in reverse 1..length(num_left) loop --(从低至高)
num_count:=num_count+1;--当前数字位数
num_current:=to_number(substr(num_left,i,1));--当前位上的数字
if num_current>0 then --当前位上数字不为0按正常处理
result:=substr(str1,num_current+1,1)||substr(str2,num_count,1)||result;
else --当前位上数字为0时
if mod(num_count-1,4)=0 then --当前位是元、万或亿时
result:=substr(str2,num_count,1)||result;
num_pre:=0;--元、万,亿前不准加零
end if;
if num_pre>0 or length(num_left)=1 then --上一位数字不为0或只有个位时
result:=substr(str1,num_current+1,1)||result;
end if;
end if;
num_pre:=num_current;
end loop;
if p_num<0 then --转换数字是负数时
result:='负'||result;
end if;
return Result;
exception
when others then
raise_application_error(-20001,'数字转换大写出现错误！'||sqlerrm);
end ;
/*------SQL Server数据库------*/
/********************************************************
作者：(birdie_7761@)
版本：1.0
创建时间：2007
修改时间：
功能：小写金额转换成大写
参数：n_LowerMoney 小写金额
v_TransType 种类 -- 1: directly translate, 0: read it in words
输出：大写金额
********************************************************/
CREATE FUNCTION dbo.L2U (@n_LowerMoney numeric(15,2),@v_TransType int)
RETURNS VARCHAR(200) AS
BEGIN
Declare @v_LowerStr VARCHAR(200) -- 小写金额
Declare @v_UpperPart VARCHAR(200)
Declare @v_UpperStr VARCHAR(200) -- 大写金额
Declare @i_I int
set @v_LowerStr = LTRIM(RTRIM(ROUND(@n_LowerMoney,2))) --四舍五入为指定的精度并删除数据左右空格
set @i_I = 1
set @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
set @v_UpperStr = @v_UpperPart + @v_UpperStr
set @i_I = @i_I + 1
end
if ( 0 = @v_TransType)
begin
set @v_UpperStr = REPLACE(@v_UpperStr,'零拾','零')
set @v_UpperStr = REPLACE(@v_UpperStr,'零佰','零')
set @v_UpperStr = REPLACE(@v_UpperStr,'零仟','零')
set @v_UpperStr = REPLACE(@v_UpperStr,'零零零','零')
set @v_UpperStr = REPLACE(@v_UpperStr,'零零','零')
set @v_UpperStr = REPLACE(@v_UpperStr,'零角零分','整')
set @v_UpperStr = REPLACE(@v_UpperStr,'零分','整')
set @v_UpperStr = REPLACE(@v_UpperStr,'零角','零')
set @v_UpperStr = REPLACE(@v_UpperStr,'零亿零万零元','亿元')
set @v_UpperStr = REPLACE(@v_UpperStr,'亿零万零元','亿元')
set @v_UpperStr = REPLACE(@v_UpperStr,'零亿零万','亿')
set @v_UpperStr = REPLACE(@v_UpperStr,'零万零元','万元')
set @v_UpperStr = REPLACE(@v_UpperStr,'万零元','万元')
set @v_UpperStr = REPLACE(@v_UpperStr,'零亿','亿')
set @v_UpperStr = REPLACE(@v_UpperStr,'零万','万')
set @v_UpperStr = REPLACE(@v_UpperStr,'零元','元')
set @v_UpperStr = REPLACE(@v_UpperStr,'零零','零')
end
-- 对壹元以下的金额的处理
if ( '元' = substring(@v_UpperStr,1,1))
begin
set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
end
if ( '零' = substring(@v_UpperStr,1,1))
begin
set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
end
if ( '角' = substring(@v_UpperStr,1,1))
begin
set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
end
if ( '分' = substring(@v_UpperStr,1,1))
begin
set @v_UpperStr = substring(@v_UpperStr,2,(len(@v_UpperStr) - 1))
end
if ('整' = substring(@v_UpperStr,1,1))
begin
set @v_UpperStr = '零元整'
end
return @v_UpperStr
END
例子：
select dbo.L2U(56588441.111,0)
select dbo.L2U(00.00,0)
