MYSQL 人民币大写金额转小写

原创已于 2022-06-07 11:40:41 修改 · 709 阅读

0 ·

CC 4.0 BY-SA版权

文章标签：

#mysql

于 2022-06-07 11:14:27 首次发布

开发笔记专栏收录该内容

1 篇文章

订阅专栏

这篇博客分享了如何在MySQL中编写自定义函数，将人民币大写金额转换为小写。作者通过两个函数`func_get_rmb_xx`和`func_get_rmb_xx_qian`实现了从0.01到9999亿的金额转换，处理了包括亿、万、元、角、分等单位的转换，并提供了详细的过程和代码示例。

最近现场客户提了一个奇葩的需求，某个表存放的是人民币大写金额，现在需要对大写金额进行统计，后来想了想，用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;