当前页面位置: » 丰搜网 » 文档中心 » 详细内容
如何在oracle中實現人民幣大寫的轉換
如何在oracle中實現人民幣大寫的轉換
作者: ccbzzp
oracle在實現報表的功能是很強大的, 特別在現實的應用中會經常用到人民幣大小寫轉換的問題, 在此我寫個函數向大家簡單介紹一下, 希望和大家一起探討, 以便共同進步! 共同發展!
1. 函數dx_money()
create function dx_money
(
money in number
)
return varchar2 as v_money varchar2(150);
rv_money0 varchar2(20);
rv_money1 varchar2(4);
rv_money2 varchar2(4);
v_money0 varchar2(20);
v_money1 varchar2(4);
v_money2 varchar2(4);
v_money3 varchar2(4);
v_money4 varchar2(4);
v_money5 varchar2(4);
v_money6 varchar2(4);
v_money7 varchar2(4);
v_money8 varchar2(4);
v_money9 varchar2(4);
v_money10 varchar2(4);
v_money11 varchar2(4);
v_money12 varchar2(4);
rpv_money1 varchar2(4);
rpv_money2 varchar2(4);
pv_money0 varchar2(14);
pv_money1 varchar2(4);
pv_money2 varchar2(4);
pv_money3 varchar2(4);
pv_money4 varchar2(4);
pv_money5 varchar2(4);
pv_money6 varchar2(4);
pv_money7 varchar2(4);
pv_money8 varchar2(4);
pv_money9 varchar2(4);
pv_money10 varchar2(4);
pv_money11 varchar2(4);
pv_money12 varchar2(4);
begin
v_money0:=ltrim(rtrim(to_char(floor(money))));
rv_money0:=ltrim(rtrim(to_char(floor(money*100))));
rv_money1:=substr(rv_money0,length(rv_money0),1);
if length(rv_money0)>1 then
rv_money2:=substr(rv_money0,length(rv_money0)-1,1);
else
rv_money2:='0';
end if;
v_money1:=substr(v_money0,length(v_money0),1);
if length(v_money0)-1>0 then
v_money2:=substr(v_money0,length(v_money0)-1,1);
else v_money2:=0;
end if;
if length(v_money0)-2>0 then
v_money3:=substr(v_money0,length(v_money0)-2,1);
else v_money3:=0;
end if;
if length(v_money0)-3>0 then
v_money4:=substr(v_money0,length(v_money0)-3,1);
else v_money4:=0;
end if;
if length(v_money0)-4>0 then
v_money5:=substr(v_money0,length(v_money0)-4,1);
else v_money5:=0;
end if;
if length(v_money0)-5>0 then
v_money6:=substr(v_money0,length(v_money0)-5,1);
else v_money6:=0;
end if;
if length(v_money0)-6>0 then
v_money7:=substr(v_money0,length(v_money0)-6,1);
else v_money7:=0;
end if;
if length(v_money0)-7>0 then
v_money8:=substr(v_money0,length(v_money0)-7,1);
else v_money8:=0;
end if;
if length(v_money0)-8>0 then
v_money9:=substr(v_money0,length(v_money0)-8,1);
else v_money9:=0;
end if;
if length(v_money0)-9>0 then
v_money10:=substr(v_money0,length(v_money0)-9,1);
else v_money10:=0;
end if;
if length(v_money0)-10>0 then
v_money11:=substr(v_money0,length(v_money0)-10,1);
else v_money11:=0;
end if;
if length(v_money0)-11>0 then
v_money12:=substr(v_money0,length(v_money0)-11,1);
else v_money12:=0;
end if;
select decode(rv_money1,'0','零分','1','壹分','2','貳分','3','參分','4','肆分','5','伍分',
'6','陸分','7','柒分','8','扒分','9','玖分') into rpv_money1 from dual;
select decode(rv_money2,'0','零角','1','壹角','2','貳角','3','參角','4','肆角','5','伍角',
'6','陸角','7','柒角','8','扒角','9','玖角') into rpv_money2 from dual;
select decode(v_money1,'0','元','1','壹元','2','貳元','3','參元','4','肆元','5','伍元',
'6','陸元','7','柒元','8','扒元','9','玖元') into pv_money1 from dual;
select decode(v_money2,'0','','1','壹拾','2','貳拾','3','參拾','4','肆拾','5','伍拾',
'6','陸拾','7','柒拾','8','扒拾','9','玖拾') into pv_money2 from dual;
select decode(v_money3,'0','','1','壹佰','2','貳佰','3','參佰','4','肆佰','5','伍佰',
'6','陸佰','7','柒佰','8','扒佰','9','玖佰') into pv_money3 from dual;
select decode(v_money4,'0','','1','壹仟','2','貳仟','3','參仟','4','肆仟','5','伍仟',
'6','陸仟','7','柒仟','8','扒仟','9','玖仟') into pv_money4 from dual;
select decode(v_money5,'0','萬','1','壹萬','2','貳萬','3','參萬','4','肆萬','5','伍萬',
'6','陸萬','7','柒萬','8','扒萬','9','玖萬') into pv_money5 from dual;
select decode(v_money6,'0','','1','壹拾','2','貳拾','3','參拾','4','肆拾','5','伍拾',
'6','陸拾','7','柒拾','8','扒拾','9','玖拾') into pv_money6 from dual;
select decode(v_money7,'0','','1','壹佰','2','貳佰','3','參佰','4','肆佰','5','伍佰',
'6','陸佰','7','柒佰','8','扒佰','9','玖佰') into pv_money7 from dual;
select decode(v_money8,'0','','1','壹仟','2','貳仟','3','參仟','4','肆仟','5','伍仟',
'6','陸仟','7','柒仟','8','扒仟','9','玖仟') into pv_money8 from dual;
select decode(v_money9,'0','億','1','壹億','2','貳億','3','參億','4','肆億','5','伍億',
'6','陸億','7','柒億','8','扒億','9','玖億') into pv_money9 from dual;
select decode(v_money10,'0','','1','壹拾','2','貳拾','3','參拾','4','肆拾','5','伍拾',
'6','陸拾','7','柒拾','8','扒拾','9','玖拾') into pv_money10 from dual;
select decode(v_money11,'0','','1','壹佰','2','貳佰','3','參佰','4','肆佰','5','伍佰',
'6','陸佰','7','柒佰','8','扒佰','9','玖佰') into pv_money11 from dual;
select decode(v_money12,'0','','1','壹仟','2','貳仟','3','參仟','4','肆仟','5','伍仟',
'6','陸仟','7','柒仟','8','扒仟','9','玖仟') into pv_money12 from dual;
v_money:=pv_money12pv_money11pv_money10pv_money9pv_money8
pv_money7pv_money6pv_money5pv_money4pv_money3pv_money2pv_money1
rpv_money2rpv_money1;
return (v_money);
end;
2. 執行狀況如下:
sql*plus: release 9.2.0.1.0 - production on 星期二 7月 29 15:03:36 2003
copyright (c) 1982, 2002, oracle corporation. all rights reserved.
連線到:
oracle8i enterprise edition release 8.1.6.1.0 - production
with the partitioning option
jserver release 8.1.6.0.0 - production
sql> select dx_money(324450567.89) from dual;
dx_money(324450567.89)
--------------------------------------------------------------------------------
參億貳仟肆佰肆拾伍萬伍佰陸拾柒元扒角玖分
3. 本例的最大數字999999999999.99
4. 以上函數寫的比較簡單易懂, 語法上不一定精簡, 歡迎大家一起探討!
