function GET_CAPITAL_MONEY (
P_currency varchar2,
P_money number)
return varchar2 IS
type DIME is table of varchar2(20) index by binary_integer;-----------------plsql这里报错，请问怎么改？？
，
numberstr DIME;
degratstr DIME;
convertm DIME;
tmpstrvarchar2(200);
T_money_int varchar2(10);
i
int;
Len_T_money_int int;
T_money_dec varchar2(2);
R_value varchar2(1000) := '';
NUM number;
begin
numberstr(1) :='zero';
numberstr(2) :='one';
numberstr(3) :='two';
numberstr(4) :='three';
numberstr(5) :='four';
numberstr(6) :='five';
numberstr(7) :='six';
numberstr(8) :='seven';
numberstr(9) :='eight';
numberstr(10) :='nine';
numberstr(11) :='ten';
numberstr(12) :='eleven';
numberstr(13) :='twelve';
numberstr(14) :='thirteen';
numberstr(15) :='fourteen';
numberstr(16) :='fifteen';
numberstr(17) :='sixteen';
numberstr(18) :='seventeen';
numberstr(19) :='eighteen';
numberstr(20) :='nineteen';
numberstr(21) :='';
numberstr(22) :='';
numberstr(23) :='twenty';
numberstr(24) :='thirty';
numberstr(25) :='forty';
numberstr(26) :='fifty';
numberstr(27) :='sixty';
numberstr(28) :='seventy';
numberstr(29) :='eighty';
numberstr(30) :='ninety';
degratstr(1) :='thousand';
degratstr(2) :='million';
degratstr(3) :='billion';
degratstr(4) :='trillion';
num := P_money;
if num = 0 then
R_value := 'ZERO';
end if;
T_money_int := trim(to_char(num,'9999999999'));
T_money_dec := substr(TRIM(to_char(num,'9999999999.99')),length(T_money_int)+2,2);
select decode(mod(length(T_money_int),3),1,'00'||T_money_int,2,'0'||T_money_int,T_money_int) into T_money_int from dual;
Len_T_money_int := length(T_money_int);
i := 0;
while i'0' then
tmpstr := trim(tmpstr||' '||numberstr(to_number(convertm(1))+1)||' hundred');
end if;
if convertm(2) = '1' then
tmpstr := trim(tmpstr||' '||numberstr(to_number(convertm(3))+11));
else
if convertm(2)'0' then
if convertm(3) = '0' then
tmpstr := trim(tmpstr||' '||numberstr(to_number(convertm(2))+21));
else
tmpstr := trim(tmpstr||' '||numberstr(to_number(convertm(2))+21)||'-'||numberstr(to_number(convertm(3))+1));
end if;
else
if convertm(3)'0' then
tmpstr := trim(tmpstr||' '||numberstr(to_number(convertm(3))+1));
end if;
end if;
end if;
if i = 3 then
R_value := trim(tmpstr||' '||R_value);
else
R_value := trim(tmpstr||' '||degratstr(i/3-1)||' '||R_value);
end if;
end loop;
tmpstr := '';
if to_number(T_money_dec)>0 then
if substr(T_money_dec,1,1) = '0' then
tmpstr := 'and '||substr(T_money_dec,2,1)||'/100';
else
tmpstr := 'and '||t_money_dec||'/100';
end if;
end if;
if length(tmpstr)<1 then
R_value := upper(R_value||' only.');
else
R_value := upper(R_value||' '||tmpstr||' only.');
end if;
return P_currency||' '||R_value;
end;
|
