oracle – 以十进制形式存储的IP地址 – PL / SQL以虚线四边形显

我们有一个Oracle数据库,其中包含存储为十进制整数的IP地址 – 这在用手操作数据而不是通过Web界面时非常痛苦,但是手动操作非常方便,因为网络人员不断要求我们做一些奇怪的事情.网络界面的作者没有预料到.

有人可以提供PL / SQL或其他方法来显示这些十进制IP作为点分十进制,即123.123.123.123格式？

即我希望能够运行如下查询：

select hostname,inttoip(ip_address) from host;

并使用inttoip()过程将ip_address显示为203.30.237.2而不是3407801602.

理想情况下,我也想要一个提供反函数的过程,例如

insert into host (hostname,ip_address) values ('some-hostname',iptoint('203.30.237.2'));

我有perl这样做,但我的PL / SQL / Oracle知识不足以将其移植到PL / SQL中.

或者,在oracle上下文中运行perl作为过程语言的方法类似于postgres中的以下内容：

CREATE FUNCTION perl_func (integer) RETURNS integer AS $$
 <some perl>
$$LANGUAGE plperl;

会很棒 – 如果可能的话 – 可能会更好,因为我可以用我熟悉的语言在Oracle中做很多程序性的东西.

解决方法

这是您需要的功能：

create or replace
function inttoip(ip_address integer) return varchar2
deterministic
is
begin
    return to_char(mod(trunc(ip_address/256/256/256),256))
           ||'.'||to_char(mod(trunc(ip_address/256/256),256))
           ||'.'||to_char(mod(trunc(ip_address/256),256))
           ||'.'||to_char(mod(ip_address,256));
end;

(关于使功能确定性和使用to_char的评论 – 谢谢).

在Oracle 11G中,您可以将格式化的IP地址设置为主机表上的虚拟列：

alter table host
add formatted_ip_address varchar2(15)
generated always as
( to_char(mod(trunc(ip_address/256/256/256),256))
          ||'.'||to_char(mod(trunc(ip_address/256/256),256))
          ||'.'||to_char(mod(trunc(ip_address/256),256))
          ||'.'||to_char(mod(ip_address,256))
) virtual;

如果需要,可以为查询索引此列.

您的查询变为：

select hostname,formatted_ip_address from host;