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

2021-12-24 00:00:00 oracle plsql plperl

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

We have an Oracle database that contains IP addresses stored as decimal integers - this is incredibly painful when manipulating the data by hand instead of via the web interface, yet hand manipulation is really handy as the network guys continually ask us to do strange things that the web interface authors did not anticipate.

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

Could someone provide me with the PL/SQL or other method to display these decimal IPs as dotted decimal i.e. 123.123.123.123 format?

即我希望能够运行查询，例如:

I.e. I'd like to be able to run a query such as :

select hostname, inttoip(ip_address) from host;

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

and have the inttoip() procedure display ip_address as 203.30.237.2 instead of as 3407801602.

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

Ideally I'd like a procedure which provides the inverse function too, e.g.

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

我有 perl 来做这件事，但我的 PL/SQL/Oracle 知识不足以将它移植到 PL/SQL.

I have perl to do this, but my PL/SQL/Oracle knowledge is not good enough to port it into PL/SQL.


或者一种在 oracle 上下文中将 perl 作为过程语言运行的方法，类似于 postgres 中的以下内容:

Alternatively a way to run the perl as the procedural language within the oracle context analogous to the following in postgres:

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

会很棒 - 如果可能的话 - 可能会更好，因为我可以用我熟悉的语言在 Oracle 中做很多程序性的事情.

Would be great - if possible - probably even better as I could then do lots of procedural stuff within Oracle in a language I am familiar with.

推荐答案

这是你需要的功能:

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 的评论 - 谢谢).

(Comments about making function deterministic and using to_char taken on board - thanks).

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

In Oracle 11G you could make the formatted IP address a virtual column on the host table:

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;

如果需要，可以为该列编制索引以进行查询.

This column could then be indexed for queries if required.

您的查询变为:

select hostname, formatted_ip_address from host;

相关文章