oracle将表字段转为小写?
oracle将表字段转为小写?函数代码：create or replace function F_upper_money(p_num in number default null)return nvarchar2 is/*Ver:1.0 Created By xsb on 2003-8-18 For:将金额数字(单位元)转换为大写(采用从低至高算法)数字整数部分不得超过16位,可以是负数Ver：1.1 Modified By xsb on 2003-8-20 For:个位数处理也放在For循环中Ver：1.2 Modified By xsb on 2003-8-22 For:分后不带整字Ver：1.3 Modified By xsb on 2003-8-28 For:完善测试用例测试用例:SET HEAD OFFSET FEED OFFselect '无参数时='||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;--当前数字位数beginif p_num is null then return null;end if;--转换数字为null时返回nullselect 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 thenif to_number(substr(num_right,1,1))=0 thenresult:='零'||substr(str1,to_number(substr(num_right,2,1)) 1,1)||'分';elseresult:=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 thenresult:=substr(str1,to_number(substr(num_right,1,1)) 1,1)||'角整';elseresult :='整';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;ifnum_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;exceptionwhen othersthenraise_application_error(-20001,'数字转换大写出现错误'||sqlerrm);end ;，今天小编就来聊一聊关于oracle将表字段转为小写?接下来我们就一起去研究一下吧!
oracle将表字段转为小写
函数代码：
create or replace function F_upper_money(p_num in number default null)return nvarchar2 is/*Ver:1.0 Created By xsb on 2003-8-18 For:将金额数字(单位元)转换为大写(采用从低至高算法)数字整数部分不得超过16位,可以是负数。Ver：1.1 Modified By xsb on 2003-8-20 For:个位数处理也放在For循环中。Ver：1.2 Modified By xsb on 2003-8-22 For:分后不带整字。Ver：1.3 Modified By xsb on 2003-8-28 For:完善测试用例。测试用例:SET HEAD OFFSET FEED OFFselect '无参数时='||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;--当前数字位数beginif p_num is null then return null;end if;--转换数字为null时返回nullselect 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 thenif to_number(substr(num_right,1,1))=0 thenresult:='零'||substr(str1,to_number(substr(num_right,2,1)) 1,1)||'分';elseresult:=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 thenresult:=substr(str1,to_number(substr(num_right,1,1)) 1,1)||'角整';elseresult :='整';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;ifnum_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;exceptionwhen othersthenraise_application_error(-20001,'数字转换大写出现错误！'||sqlerrm);end ;
测试用例：
-- 无参数，结果为：null=select 'null='||f_upper_money(null) total from dual;-- 0=零元整select '0='||f_upper_money(0) total from dual;-- 0.05=零元零伍分select '0.05='||f_upper_money(0.05) total from dual;-- 0.155=零元壹角陆分select '0.155='||f_upper_money(0.155) total from dual;-- 01.125=壹元壹角参分select '01.125='||f_upper_money(01.125) total from dual;-- 10=壹拾元整select '10='||f_upper_money(10) total from dual;-- 100.5=壹佰元伍角整select '100.5='||f_upper_money(100.5) total from dual;-- 100.05=壹佰元零伍分select '100.05='||f_upper_money(100.05) from dual;-- 10000=壹万元整select '10000='||f_upper_money(10000) from dual;-- 10025.52=壹万零贰拾伍元伍角贰分select '10025.52='||f_upper_money(10025.52) from dual;-- 50000020.05=伍仟万零贰拾元零伍分select '50000020.05='||f_upper_money(50000020.05) from dual;-- 5040302105.501=伍拾亿肆仟零参拾万贰仟壹佰零伍元伍角整select '5040302105.501='||f_upper_money(5040302105.501) from dual;-- 50002001075.001=伍佰亿零贰佰万壹仟零柒拾伍元整select '50002001075.001='||f_upper_money(50002001075.001) from dual;-- -123456789.15=负壹亿贰仟参佰肆拾伍万陆仟柒佰捌拾玖元壹角伍分select '-123456789.15='||f_upper_money(-123456789.15) from dual;-- 123456789123456789.89=**********select '123456789123456789.89='||f_upper_money(123456789123456789.89) from dual;
