oracle IP 地址存储为十进制 - PL/SQL 显示为点四边形
声明：本页面是StackOverFlow热门问题的中英对照翻译，遵循CC BY-SA 4.0协议，如果您需要使用它，必须同样遵循CC BY-SA许可，注明原文地址和作者信息，同时你必须将它归于原作者（不是我）：StackOverFlow
原文地址: http://stackoverflow.com/questions/1084413/
Warning: these are provided under cc-by-sa 4.0 license. You are free to use/share it, But you must attribute it to the original authors (not me):
StackOverFlow
IP address stored as decimal - PL/SQL to display as dotted quad
提问by Jason Tan
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.
我们有一个 Oracle 数据库，其中包含存储为十进制整数的 IP 地址 - 当手动操作数据而不是通过 Web 界面时，这非常痛苦，但手动操作确实很方便，因为网络人员不断要求我们做一些奇怪的事情网络界面作者没有预料到。
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?
有人可以向我提供 PL/SQL 或其他方法来将这些十进制 IP 显示为点分十进制，即 123.123.123.123 格式吗？
I.e. I'd like to be able to run a query such as :
即我希望能够运行一个查询，例如：
select hostname, inttoip(ip_address) from host;
and have the inttoip()procedure display ip_address as 203.30.237.2 instead of as 3407801602.
并让inttoip()程序将 ip_address 显示为 203.30.237.2 而不是 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'));
I have perl to do this, but my PL/SQL/Oracle knowledge is not good enough to port it into PL/SQL.
我有 perl 来做到这一点，但我的 PL/SQL/Oracle 知识不足以将其移植到 PL/SQL 中。
Alternativelya way to run the perl as the procedural language within the oracle context analogous to the following in postgres:
或者，一种在 oracle 上下文中将 perl 作为过程语言运行的方法，类似于 postgres 中的以下内容：
CREATE FUNCTION perl_func (integer) RETURNS integer AS $$
<some perl>
$$ LANGUAGE plperl;
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.
会很棒 - 如果可能的话 - 可能会更好，因为我可以用我熟悉的语言在 Oracle 中做很多程序性的东西。
回答by Tony Andrews
This is the function you need:
这是您需要的功能：
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;
(Comments about making function deterministic and using to_char taken on board - thanks).
（关于使函数具有确定性和使用 to_char 的评论 - 谢谢）。
In Oracle 11G you could make the formatted IP address a virtual column on the host table:
在 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;
This column could then be indexed for queries if required.
如果需要，然后可以为该列编制索引以进行查询。
Your query becomes:
您的查询变为：
select hostname, formatted_ip_address from host;
回答by Jeffrey Kemp
CREATE OR REPLACE
FUNCTION inttoip(ip_address IN INTEGER) RETURN VARCHAR2 IS
v8 VARCHAR2(8);
BEGIN
-- 1. convert the integer into hexadecimal representation
v8 := TO_CHAR(ip_address, 'FMXXXXXXXX');
-- 2. convert each XX portion back into decimal
RETURN to_number(substr(v8,1,2),'XX')
|| '.' || to_number(substr(v8,3,2),'XX')
|| '.' || to_number(substr(v8,5,2),'XX')
|| '.' || to_number(substr(v8,7,2),'XX');
END inttoip;
CREATE OR REPLACE
FUNCTION iptoint(ip_string IN VARCHAR2) RETURN INTEGER IS
d1 INTEGER;
d2 INTEGER;
d3 INTEGER;
q1 VARCHAR2(3);
q2 VARCHAR2(3);
q3 VARCHAR2(3);
q4 VARCHAR2(3);
v8 VARCHAR2(8);
BEGIN
-- 1. parse the input, e.g. '203.30.237.2'
d1 := INSTR(ip_string,'.'); -- first dot
d2 := INSTR(ip_string,'.',1,2); -- second dot
d3 := INSTR(ip_string,'.',1,3); -- third dot
q1 := SUBSTR(ip_string, 1, d1 - 1); -- e.g. '203'
q2 := SUBSTR(ip_string, d1 + 1, d2 - d1 - 1); -- e.g. '30'
q3 := SUBSTR(ip_string, d2 + 1, d3 - d2 - 1); -- e.g. '237'
q4 := SUBSTR(ip_string, d3 + 1); -- e.g. '2'
-- 2. convert to a hexadecimal string
v8 := LPAD(TO_CHAR(TO_NUMBER(q1),'FMXX'),2,'0')
|| LPAD(TO_CHAR(TO_NUMBER(q2),'FMXX'),2,'0')
|| LPAD(TO_CHAR(TO_NUMBER(q3),'FMXX'),2,'0')
|| LPAD(TO_CHAR(TO_NUMBER(q4),'FMXX'),2,'0');
-- 3. convert to a decimal number
RETURN TO_NUMBER(v8, 'FMXXXXXXXX');
END iptoint;
回答by Rob van Laarhoven
-- INET ATON en INET NTOA and helper function GET TOKEN
CREATE OR REPLACE function inet_ntoa (ip integer) return varchar2
is
ip1 integer;
ip2 integer;
ip3 integer;
ip4 integer;
ipi integer := ip;
begin
ip1 := floor(ipi/power(2,24));
ipi := ipi - (ip1*power(2,24));
ip2 := floor(ipi/power(2,16));
ipi := ipi - (ip2*power(2,16));
ip3 := floor(ipi/power(2,8));
ipi := ipi - (ip3*power(2,8));
ip4 := ipi;
return ip1||'.'||ip2||'.'||ip3||'.'||ip4;
end;
/
CREATE OR REPLACE FUNCTION get_token (the_list VARCHAR2,the_index NUMBER, delim VARCHAR2 := '.') RETURN VARCHAR2
IS
start_pos INTEGER;
end_pos INTEGER;
BEGIN
IF the_index = 1 THEN
start_pos := 1;
ELSE
start_pos := INSTR (the_list, delim, 1, the_index - 1);
IF start_pos = 0 THEN
RETURN NULL;
ELSE
start_pos := start_pos + LENGTH (delim);
END IF;
END IF;
end_pos := INSTR (the_list, delim, start_pos, 1);
IF end_pos = 0 THEN
RETURN SUBSTR (the_list, start_pos);
ELSE
RETURN SUBSTR (the_list, start_pos, end_pos - start_pos);
END IF;
END get_token;
/
CREATE OR REPLACE function inet_aton (ip varchar2) return integer
is
invalid_ip_adres exception;
pragma exception_init(invalid_ip_adres,-6502);
ipi integer;
begin
ipi := get_token(ip,4)
+(get_token(ip,3)*power(2,8))
+(get_token(ip,2)*power(2,16))
+(get_token(ip,1)*power(2,24));
return ipi;
exception
when invalid_ip_adres
then
return null;
end;
/
