create or replace function comm.F_upper_money(p_num in number default null)
return nvarchar2 is
/*Ver:1.0 Created By xsb on 2003-8-18 For:
將金額數(shù)字(單位元)轉(zhuǎn)換為大寫(采用從低至高算法)
數(shù)字整數(shù)部分不得超過16位,可以是負(fù)數(shù)。
Ver：1.1 Modified By xsb on 2003-8-20 For:個位數(shù)處理也放在For循環(huán)中。
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 '無參數(shù)時='||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))); --轉(zhuǎn)換數(shù)字為小數(shù)點后2位的字符(正數(shù))
num_left nvarchar2(100); --小數(shù)點左邊的數(shù)字
num_right nvarchar2(2); --小數(shù)點右邊的數(shù)字
str1 nchar(10) := '零壹貳叁肆伍陸柒捌玖'; --數(shù)字大寫
str2 nchar(16) := '元拾佰仟萬拾佰仟億拾佰仟萬拾佰仟'; --數(shù)字位數(shù)(從低至高)
num_pre number(1) := 1; --前一位上的數(shù)字
num_current number(1); --當(dāng)前位上的數(shù)字
num_count number := 0; --當(dāng)前數(shù)字位數(shù)
begin
if p_num is null then
return null;
end if; --轉(zhuǎn)換數(shù)字為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; --取得小數(shù)點左邊的數(shù)字
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; --取得小數(shù)點右邊的數(shù)字
if length(num_left) > 16 then
return '**********';
end if; --數(shù)字整數(shù)部分超過16位時
--采用從低至高的算法，先處理小數(shù)點右邊的數(shù)字
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;
--再處理小數(shù)點左邊的數(shù)字
for i in reverse 1 .. length(num_left) loop
--(從低至高)
num_count := num_count + 1; --當(dāng)前數(shù)字位數(shù)
num_current := to_number(substr(num_left, i, 1)); --當(dāng)前位上的數(shù)字
if num_current > 0 then
--當(dāng)前位上數(shù)字不為0按正常處理
result := substr(str1, num_current + 1, 1) ||
substr(str2, num_count, 1) || result;
else
--當(dāng)前位上數(shù)字為0時
if mod(num_count - 1, 4) = 0 then
--當(dāng)前位是元、萬或億時
result := substr(str2, num_count, 1) || result;
num_pre := 0; --元、萬,億前不準(zhǔn)加零
end if;
if num_pre > 0 or length(num_left) = 1 then
--上一位數(shù)字不為0或只有個位時
result := substr(str1, num_current + 1, 1) || result;
end if;
end if;
num_pre := num_current;
end loop;
if p_num < 0 then
--轉(zhuǎn)換數(shù)字是負(fù)數(shù)時
result := '負(fù)' || result;
end if;
return Result;
exception
when others then
raise_application_error(-20001, '數(shù)字轉(zhuǎn)換大寫出現(xiàn)錯誤！' || sqlerrm);
end;
網(wǎng)頁題目：plsql小寫金額轉(zhuǎn)大寫金額函數(shù)
URL分享：http://jinyejixie.com/article28/jpoccp.html
成都網(wǎng)站建設(shè)公司_創(chuàng)新互聯(lián)，為您提供網(wǎng)站內(nèi)鏈、品牌網(wǎng)站制作、App設(shè)計、標(biāo)簽優(yōu)化、企業(yè)網(wǎng)站制作、小程序開發(fā)
廣告
![]()
聲明：本網(wǎng)站發(fā)布的內(nèi)容（圖片、視頻和文字）以用戶投稿、用戶轉(zhuǎn)載內(nèi)容為主，如果涉及侵權(quán)請盡快告知，我們將會在第一時間刪除。文章觀點不代表本網(wǎng)站立場，如需處理請聯(lián)系客服。電話：028-86922220；郵箱：631063699@qq.com。內(nèi)容未經(jīng)允許不得轉(zhuǎn)載，或轉(zhuǎn)載時需注明來源：
創(chuàng)新互聯(lián)
