罗马数字 转 阿拉伯数字

/*自己寫的*/

create or replace function RN_NO(p_RN in varchar2) return number is
Result number;
Len number;
i number;
v_rn varchar2(20);
prv number:=0;
nxt number:=0;
res number:=0;
begin
v_rn := lower(p_rn);
select replace(v_rn,'i',1) into v_rn from dual;
select replace(v_rn,'v',2) into v_rn from dual;
select replace(v_rn,'x',3) into v_rn from dual;
select replace(v_rn,'l',4) into v_rn from dual;
select replace(v_rn,'c',5) into v_rn from dual;
select replace(v_rn,'d',6) into v_rn from dual;
select replace(v_rn,'m',7) into v_rn from dual;
Len := length(v_rn);
for i in 0..Len-1 loop
select decode(substr(v_rn,1+i,1),1,1,2,5,3,10,4,50,5,100,6,500,7,1000)into nxt from dual;
if prv=0 then
res := nxt;
else
if nxt > prv then
res := res + nxt -2*prv;
else
res := res + nxt;
end if;
end if;
prv := nxt;
end loop;
result := res;
return(Result);
end RN_NO;
可以轉換1~3999之間的任意一個羅馬數

/*別人幫我優化的*/

CREATE OR REPLACE function RN_NO2(p_RN in varchar2) return number is
Result number;
Len number;
i number;
v_rn varchar2(20);
prv number:=0;
nxt number:=0;
res number:=0;
begin
v_rn := lower(p_rn);
Len := length(v_rn);
for i in 0..Len-1 loop
nxt:= case substr(v_rn,1+i,1) when 'i' then 1 when 'v' then 5 when 'x' then 10
when 'l' then 50 when 'c' then 100 when 'd' then 500 when 'm' then 1000 end;
if prv=0 then
res := nxt;
else
if nxt > prv then
res := res + nxt -2*prv;
else
res := res + nxt;
end if;
end if;
prv := nxt;
end loop;
result := res;
return(Result);
end RN_NO2;
/


现在这个速度就和cooler的相差无几了

create or replace function roman(p_num varchar2) return number as

i number :=1;
v number :=5;
x number :=10;
l number := 50;
c number:= 100;
d number :=500;
m number :=1000;
v_num number :=0;

begin
v_num :=
CASE
when p_num='m' then m
when p_num='c' then c
when p_num='i' then i
when p_num='v' then v
when p_num='x' then x
when p_num='d' then d
when p_num='l' then l
end;
return v_num;
end;

/*還有一個*/
create or replace function roman_to_decimal(p_rnum varchar2) return number
as
v_len number:=0;
v_retnum number:=0;
v_count number :=1;
v_str varchar2(1);
v_num number:=0;
v_prevnum number:=0;
begin
v_len :=length(p_rnum);

while v_count<= (v_len) loop
v_str:= substr (p_rnum, v_count, 1);
v_num:= ROMAN ( lower ( v_str ));

if v_count = 1 then
v_prevnum:= v_num;
end if;

if v_num > v_prevnum then
v_retnum:= v_retnum - v_prevnum;
v_retnum:= v_retnum + (v_num - v_prevnum);
else
v_retnum:= v_retnum + v_num;
end if;

v_count:= v_count + 1;
v_prevnum:= v_num;
end loop;
return v_retnum;
end;

来自 “ ITPUB博客 ” ，链接：http://blog.itpub.net/785120/viewspace-332298/，如需转载，请注明出处，否则将追究法律责任。

转载于:http://blog.itpub.net/785120/viewspace-332298/