oracle - IP 地址存储为十进制 - PL/SQL 显示为点四边形

Question

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

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

IE。我希望能够运行一个查询，例如:

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 中做很多程序性的东西。

Answer 1

这是您需要的功能:

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;