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 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 ; ，今天小編就來聊一聊關于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 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 ;

測試用例：

-- 無參數，結果為：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;