最近现场客户提了一个奇葩的需求,某个表存放的是人民币大写金额,现在需要对大写金额进行统计,后来想了想,用MYSQL来实现吧。百度了好多资料,要么言不达意,要么需要开通VIP,于是一想,还不如自己写呢。经过半个晚上的编写与调试,终于大功告成。
这个功能由两个函数组成,func_get_rmb_xx是主函数,func_get_rmb_xx_qian是主函数调用的子函数,支持0.01至9999亿的数据范围,足够满足日常需求,希望能帮助各位码农。
-- 主函数部分
CREATE FUNCTION `func_get_rmb_xx`(v_rmb_dx VARCHAR ( 200 )) RETURNS varchar(20) CHARSET utf8
-- 人民币大写转小写
BEGIN
DECLARE v_rmb_xx decimal(18,2);-- 小写总计
DECLARE v_rmb_xx_wan decimal(18,2);-- 小写万
DECLARE v_rmb_xx_yi decimal(18,2);-- 小写亿
DECLARE v_rmb_xx_yuan decimal(18,2);-- 小写元
set v_rmb_xx =0;
-- 数据预处理
set v_rmb_dx=REPLACE(v_rmb_dx,'一','壹');
set v_rmb_dx=REPLACE(v_rmb_dx,'二','贰');
set v_rmb_dx=REPLACE(v_rmb_dx,'三','叁');
set v_rmb_dx=REPLACE(v_rmb_dx,'四','肆');
set v_rmb_dx=REPLACE(v_rmb_dx,'五','伍');
set v_rmb_dx=REPLACE(v_rmb_dx,'六','陆');
set v_rmb_dx=REPLACE(v_rmb_dx,'七','柒');
set v_rmb_dx=REPLACE(v_rmb_dx,'八','捌');
set v_rmb_dx=REPLACE(v_rmb_dx,'九','玖');
set v_rmb_dx=REPLACE(v_rmb_dx,'千','仟');
set v_rmb_dx=REPLACE(v_rmb_dx,'百','佰');
set v_rmb_dx=REPLACE(v_rmb_dx,'整','');
set v_rmb_dx=REPLACE(v_rmb_dx,'园','元');
set v_rmb_dx=REPLACE(v_rmb_dx,'圆','元');
set v_rmb_xx=0;
-- 获取亿之前的字段
if LOCATE('亿',v_rmb_dx) then
insert into text_table (name,VALUE) VALUEs('亿',substring_index(v_rmb_dx,'亿',1));
set v_rmb_xx=v_rmb_xx+func_get_rmb_xx_qian(substring_index(v_rmb_dx,'亿',1))*100000000;
set v_rmb_dx=substring_index(v_rmb_dx,'亿',-1);
end if;
-- 获取万的数值
if LOCATE('万',v_rmb_dx) then
insert into text_table (name,VALUE) VALUEs('万',substring_index(v_rmb_dx,'万',1));
set v_rmb_xx=v_rmb_xx+func_get_rmb_xx_qian(substring_index(v_rmb_dx,'万',1))*10000;
set v_rmb_dx=substring_index(v_rmb_dx,'万',-1);
end if;
-- 获取元万之前的字段
if v_rmb_dx is not null then
set v_rmb_xx=v_rmb_xx+func_get_rmb_xx_qian(v_rmb_dx);
insert into text_table (name,VALUE) VALUEs('元',v_rmb_dx);
end if;
RETURN v_rmb_xx;
END
-- 子函数部分
CREATE FUNCTION `func_get_rmb_xx_qian`(v_rmb_dx VARCHAR ( 200 )) RETURNS varchar(20) CHARSET utf8
BEGIN
DECLARE v_rmb_xx decimal(18,2);
DECLARE v_rmb_xx_temp decimal(18,2);
declare v_rmb_dx_temp VARCHAR(10);
declare v_rmb_dx_temp_next VARCHAR(10);
declare i int ;
DECLARE length int;
set v_rmb_xx =0;
set i=1;
-- 获取汉字的长度
set length=char_LENGTH(v_rmb_dx);
while i<=length do
set v_rmb_dx_temp=SUBSTRING(v_rmb_dx,i,1);
if v_rmb_dx_temp ='壹' or v_rmb_dx_temp ='贰' or v_rmb_dx_temp ='叁' or v_rmb_dx_temp ='肆' or v_rmb_dx_temp ='伍' or v_rmb_dx_temp ='陆' or v_rmb_dx_temp ='柒' or v_rmb_dx_temp ='捌' or v_rmb_dx_temp ='玖' then
if v_rmb_dx_temp ='壹' then
set v_rmb_xx_temp =1;
elseif v_rmb_dx_temp ='贰' then
set v_rmb_xx_temp =2;
elseif v_rmb_dx_temp ='叁' then
set v_rmb_xx_temp =3;
elseif v_rmb_dx_temp ='肆' then
set v_rmb_xx_temp =4;
elseif v_rmb_dx_temp ='伍' then
set v_rmb_xx_temp =5;
elseif v_rmb_dx_temp ='陆' then
set v_rmb_xx_temp =6;
elseif v_rmb_dx_temp ='柒' then
set v_rmb_xx_temp =7;
elseif v_rmb_dx_temp ='捌' then
set v_rmb_xx_temp =8;
elseif v_rmb_dx_temp ='玖' then
set v_rmb_xx_temp =9;
end if;
insert into text_table (name,VALUE) VALUEs('v_rmb_xx_temp',v_rmb_xx_temp);
set i=i+1;
set v_rmb_dx_temp_next=SUBSTRING(v_rmb_dx,i,1);
if v_rmb_dx_temp_next='佰' then
set v_rmb_xx_temp=v_rmb_xx_temp*100;
end if;
if v_rmb_dx_temp_next='拾' then
set v_rmb_xx_temp=v_rmb_xx_temp*10;
end if;
if v_rmb_dx_temp_next='仟' then
set v_rmb_xx_temp=v_rmb_xx_temp*1000;
end if;
if v_rmb_dx_temp_next='角' then
set v_rmb_xx_temp=v_rmb_xx_temp*0.1;
end if;
if v_rmb_dx_temp_next='分' then
set v_rmb_xx_temp=v_rmb_xx_temp*0.01;
end if;
set v_rmb_xx = v_rmb_xx+v_rmb_xx_temp;
insert into text_table (name,VALUE) VALUEs('v_rmb_xx',v_rmb_xx);
end if;
set i=i+1;
end while;
RETURN v_rmb_xx;
END