Oracle PL/SQL versions of INET6_ATON and NTOA functions?(INET6_ATON 和 NTOA 函数的 Oracle PL/SQL 版本?)
本文介绍了INET6_ATON 和 NTOA 函数的 Oracle PL/SQL 版本?的处理方法,对大家解决问题具有一定的参考价值,需要的朋友们下面随着跟版网的小编来一起学习吧!
问题描述
是否有将 IPv6 地址字符串转换为整数的好代码?使用一种格式转换 IPv4 似乎相当容易.但是,IPv6 有几种不同的格式来显示地址:
Any have any good code for converting a IPv6 address string into an integer? Converting IPv4 seems to be fairly easy, with the one format. However, IPv6 has several different formats to show an address:
- XXXX:XXXX:XXXX:XXXX::
- XXXX:XXXX:XXXX:XXXX:XXXX:XXXX:XXXX:XXXX
- XXXX:XXX:XXXX:0:0:XXXX:XXX:XXXX
- XXXX:XXX:XXXX::XXXX:XXX:XXXX
- ::ffff:XXXX:XXX(v6 格式的 IPv4)
- ::ffff:###.#.#.###(v6 格式的 IPv4 也是有效的)
我希望能够采用这些字符串之一并将其转换为整数以进行 IP 到网络匹配,并允许将这些格式中的任何一种作为输入.
I'd like to be able to take one of these strings and translate it into an INTEGER for IP-to-network matching, and allow for any of these formats as the input.
推荐答案
最终滚动了我自己的.还意识到 Oracle 的 126 位整数对于 IPv6 的 128 位地址来说是不够的.坦率地说,我不知道原始 C 库的 INET6_ATON(或 INET_PTON)是如何做到的,因为我从未听说过 16 字节整数.
Ended up rolling my own. Also realized that Oracle's 126-bit INTEGER is not enough bits for IPv6's 128-bit addresses. Frankly, I don't know how the original C library's INET6_ATON (or INET_PTON) does it, considering that I've never heard of a 16-byte integer.
我最终得到了一个 32 字节的十六进制字符串,这意味着我必须在 nettohex 上做一些花哨的半字符串"数学运算,并使用 SUBSTR 使 FBI 正常工作.(Blasted PL/SQL 不允许"RETURN CHAR(32)"...)
I ended up with a 32-byte hex string, which means I have to do some fancy "half-string" math on nettohex and use SUBSTR for the FBIs to work correctly. (Blasted PL/SQL doesn't allow for "RETURN CHAR(32)"...)
但总的来说,它运行良好,适用于所有格式,并允许基于索引的字符比较以找出 IP 地址是否在 IP 范围内.
Overall, though, it works well, works in all formats, and allows for index-based character comparisons to find out if an IP address is within an IP range.
完整代码如下:
CREATE OR REPLACE FUNCTION ipguess(
ip_string IN VARCHAR2
) RETURN NATURAL
DETERMINISTIC
IS
BEGIN
IF REGEXP_LIKE(ip_string, 'd{1,3}(.d{1,3}){3}') THEN RETURN 4;
ELSIF REGEXP_LIKE(ip_string, '[[:xdigit:]]{0,4}(:[[:xdigit:]]{0,4}){0,7}') THEN RETURN 6;
ELSE RETURN NULL;
END IF;
END ipguess;
CREATE OR REPLACE FUNCTION iptohex(
ip_string IN VARCHAR2
) RETURN CHAR
DETERMINISTIC
IS
iptype NATURAL := ipguess(ip_string);
ip VARCHAR2(32);
ipwork VARCHAR2(64);
d INTEGER;
q VARCHAR2(3);
BEGIN
IF iptype = 4 THEN
ipwork := REGEXP_SUBSTR(ip_string, 'd{1,3}(.d{1,3}){3}');
IF ipwork IS NULL THEN RETURN NULL; END IF;
ip := '00000000000000000000ffff';
WHILE LENGTH(ipwork) IS NOT NULL
LOOP
d := INSTR(ipwork, '.');
IF d > 0 THEN
q := SUBSTR(ipwork, 1, d - 1);
ipwork := SUBSTR(ipwork, d + 1);
ELSE
q := ipwork;
ipwork := '';
END IF;
ip := ip || TO_CHAR(TO_NUMBER(q), 'FM0x');
END LOOP;
ELSIF iptype = 6 THEN
IF ip_string = '::' THEN RETURN LPAD('0', 32, '0'); END IF;
ipwork := REGEXP_SUBSTR(ip_string, '[[:xdigit:]]{0,4}(:[[:xdigit:]]{0,4}){0,7}');
IF ipwork IS NULL THEN RETURN NULL; END IF;
ipwork := REGEXP_REPLACE(ipwork, '(^|:)([[:xdigit:]]{1,4})', '10002');
ipwork := REGEXP_REPLACE(ipwork, '(^|:)0+([[:xdigit:]]{4})', '12');
ipwork := REPLACE(ipwork, '::', 'Z');
ipwork := REPLACE(ipwork, ':');
ipwork := REPLACE(ipwork, 'Z', LPAD('0', 33 - LENGTH(ipwork), '0'));
ip := LOWER(ipwork);
ELSE
RETURN NULL;
END IF;
RETURN ip;
END iptohex;
CREATE OR REPLACE FUNCTION nettohex(
ip_string IN VARCHAR2,
cidr IN NATURALN,
is_end IN SIGNTYPE DEFAULT 0
) RETURN CHAR
DETERMINISTIC
IS
iptype NATURAL := ipguess(ip_string);
iphex CHAR(32) := iptohex(ip_string);
iphalf1 CHAR(16) := SUBSTR(iphex, 1, 16);
iphalf2 CHAR(16) := SUBSTR(iphex, 17);
ipwork CHAR(16) := iphalf2;
cidr_exp INTEGER := 2 ** (iptype + 1) - cidr;
ipint INTEGER;
subnet INTEGER;
is_big SIGNTYPE := 0;
BEGIN
IF iptype IS NULL THEN RETURN NULL;
ELSIF iphex IS NULL THEN RETURN NULL;
END IF;
IF cidr_exp >= 64 THEN is_big := 1;
ELSIF cidr_exp = 0 THEN RETURN iphex;
ELSIF cidr_exp < 0 THEN RETURN NULL;
ELSIF cidr_exp > 128 THEN RETURN NULL;
END IF;
IF is_big = 1 THEN
ipwork := iphalf1;
iphalf2 := TO_CHAR((2 ** 64 - 1) * is_end, 'FM0xxxxxxxxxxxxxxx');
cidr_exp := cidr_exp - 64;
END IF;
subnet := 2 ** cidr_exp;
ipint := TO_NUMBER(ipwork, 'FM0xxxxxxxxxxxxxxx');
ipwork := TO_CHAR(FLOOR(ipint / subnet + is_end) * subnet - is_end, 'FM0xxxxxxxxxxxxxxx');
IF is_big = 0 THEN iphalf2 := ipwork;
ELSE iphalf1 := ipwork;
END IF;
RETURN SUBSTR(iphalf1 || iphalf2, 1, 32);
END nettohex;
更新: Oracle 11g 确实允许将 SUBSTR 条目放入虚拟列.所以,你可以有这样的列:
UPDATE: Oracle 11g does allow for the SUBSTR entry to be put a virtual column. So, you could have columns like this:
和索引如:
使用 WHERE 子句,例如:
Using WHERE clauses like:
这篇关于INET6_ATON 和 NTOA 函数的 Oracle PL/SQL 版本?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!
本站部分内容来源互联网,如果有图片或者内容侵犯了您的权益,请联系我们,我们会在确认后第一时间进行删除!