Oracle 中常用的 function

1.将oracle Date 转为 utc

CREATE OR REPLACE
function to_unixtime(oracleDate date) return number is
begin
return (oracleDate - to_date(19700101,'yyyymmdd') )*24*60*60 ;
end;

2. 将 utc 时间转为 oracle 时间

CREATE OR REPLACE
function from_unixtime(sttTime number) return date is
begin
return to_date(19700101,'yyyymmdd') + (sttTime+8*3600)/24/60/60;
end;

3.将网络序IP地址转为点分式IP

CREATE OR REPLACE
function long2ip(ip number) return varchar2 is
  Result varchar2(40);
      ip0 number;
      ip1 number;
      ip2 number;
      ip3 number;

      flag0 number;
      flag1 number ;
      flag2 number;
begin     
     flag0 := 256 * 256 * 256;
      flag1 := 256 *256;
      flag2 := 256;

     ip3 := mod(ip, flag2);
     ip0 := (ip - mod(ip, flag0))/flag0;
     ip1 := (ip - ip0 * flag0 - mod(ip, flag1))/flag1;
     ip2 := (ip - ip0 * flag0 - ip1 * flag1 - mod(ip, flag2))/flag2;

      Result := to_char(ip3) || '.' || to_char(ip2) || '.' || to_char(ip1) || '.' || to_char(ip0);
  return(Result);

end long2ip;