- html - 出于某种原因,IE8 对我的 Sass 文件中继承的 html5 CSS 不友好?
- JMeter 在响应断言中使用 span 标签的问题
- html - 在 :hover and :active? 上具有不同效果的 CSS 动画
- html - 相对于居中的 html 内容固定的 CSS 重复背景?
带有 CIDR 的 ex.IPv6 地址:2620:0:2d0:200::7/32输出起始范围:2620:0:0:0:0:0:0:0结束范围:2620:0:ffff:ffff:ffff:ffff:ffff:ffff
如何用PL/SQL计算?
最佳答案
有一次我写了一个通用的 PL/SQL 包,您可以在其中进行此类转换。它适用于 IPv4 和 IPv6。
CREATE OR REPLACE PACKAGE IP_Util AS
/**
* Convert an IP-Address into decimal value.
* @param IP The IP-Address, e.g. '10.151.20.224' or '1080::8:800:200C:417A'.
* Supports also mixed notation like '0:0:0:0:0:FFFF:129.144.52.38'. CIDR value (e.g. '1080::8:800:200C:417A/80') is ignored.
* @return The decimal equivalent
*/
FUNCTION IP2Decimal(IP IN VARCHAR2) RETURN NUMBER DETERMINISTIC;
/**
* Convert an IP-Address into RWA value.
* @param IP The IP-Address, e.g. '10.151.20.224' or '1080::8:800:200C:417A'.
* Supports also mixed notation like '0:0:0:0:0:FFFF:129.144.52.38'. CIDR value (e.g. '1080::8:800:200C:417A/80') is ignored.
* @param ver IP version, either 4 or 6. If NULL then function determines the IP version.
* @return The RAW equivalent
*/
FUNCTION IP2RAW(IP IN VARCHAR2, ver IN INTEGER DEFAULT NULL) RETURN RAW DETERMINISTIC;
/**
* Convert an IP-Address from decimal value into IPv4 or IPv6 format.
* @param ip Decimal IP-Address, 0..(2**32)-1 or 0..(2**128)-1
* @param ver IP version, either 4 or 6
* @return The IP in IPv4 or IPv6 format
*/
FUNCTION Decimal2IP(ip IN NUMBER, ver IN INTEGER) RETURN VARCHAR2 DETERMINISTIC;
/**
* Convert an IP-Address from RAW value into IPv4 or IPv6 format.
* @param ip RAW value of IP-Address, 0..FFFFFFFF or 0..FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF
* @param ver IP version, either 4 or 6
* @return The IP in IPv4 or IPv6 format
*/
FUNCTION RAW2IP(ip IN RAW, ver IN INTEGER) RETURN VARCHAR2 DETERMINISTIC;
/**
* Returns SubnetMask of given IP-Subnet in CIDR notation.
* @param Ip Subnet IP-Address with CIDR notation, e.g. '10.152.10.17/24' or '1080::8:800:200C:417A/60'
* @return SubnetMask Subnet mask of IP-Subnet, e.g. '255.255.255.0' or 'ffff:ffff:ffff::'
*/
FUNCTION SubnetMask(Ip IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC;
/**
* Returns Network prefix of given IP-Subnet. In IPv4 this address was called subnet address.
* @param Ip IP-Address of subnet, e.g. '10.152.10.17' or '1080:0:100:8:800:200C:FFFF:417A'
* @param SubnetMask Subnet mask of subnet, e.g. '255.255.0.0' or 'FFFF:FFFF:FFFF::'
* @return Network prefix, i.e. the first address from subnet, for example '10.152.0.0' or '1080:0:100:8:800:200C:FFFF::'
*/
FUNCTION NetworkPrefix(Ip IN VARCHAR2, SubnetMask IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC;
/**
* Returns Network prefix of given IP-Subnet. In IPv4 this address was called subnet address.
* @param Ip IP-Subnet with CIDR notation, e.g. '10.152.10.17/24' or '1080:0:100:8:800:200C:FFFF:417A/60'
* @return Network prefix, i.e. the first address from subnet, for example '10.152.0.0' or '1080:0:100:8:800:200C:FFFF::'
*/
FUNCTION NetworkPrefix(Ip IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC;
/**
* Returns Broadcast address of given IP-Subnet.
* IPv6 does not provide Broadcast anymore. However, function supports IPv6 for internal purpose.
* @param Ip IP-Address of subnet, e.g. '10.152.10.17' or '1080:0:100:8:800:200C:FFFF:417A'
* @param SubnetMask Subnet mask of subnet, e.g. '255.255.0.0' or 'FFFF:FFFF:FFFF::'
* @return Broadcast address, i.e. the last address from subnet, for example '10.152.10.255' or '1080:0:100:8:800:ffff:ffff:ffff'
*/
FUNCTION BroadcastIp(Ip IN VARCHAR2, SubnetMask IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC;
/**
* Returns Broadcast address of given IP-Subnet.
* IPv6 does not provide Broadcast anymore. However, function supports IPv6 for internal purpose.
* @param Ip IP-Subnet with CIDR notation, e.g. '10.152.10.17/24' or '1080:0:100:8:800:200C:FFFF:417A/60'
* @return Broadcast address, i.e. the last address from subnet, for example '10.152.10.255' or '1080:0:100:8:800:ffff:ffff:ffff'
*/
FUNCTION BroadcastIp(Ip IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC;
/**
* Translate Subnet mask to CIDR.
* @param SubnetMask Subnet mask of subnet, e.g. '255.255.0.0' or 'FFFF:FFFF:FFFF::'
* @return CIDR value, e.g. 26
*/
FUNCTION SubnetMask2CIDR(SubnetMask VARCHAR2) RETURN INTEGER RESULT_CACHE DETERMINISTIC;
/**
* Translate CIDR to Subnet mask in IPv4 or IPv6 format.
* @param CIDR Length of network prefix
* @param ver IP version, either 4 or 6
* @return Subnet mask, e.g. '255.255.0.0' or 'FFFF:FFFF:FFFF::'
*/
FUNCTION CIDR2SubnetMask(CIDR IN INTEGER, ver IN INTEGER) RETURN VARCHAR2 RESULT_CACHE DETERMINISTIC;
/**
* Returns full uncompressed IPv6 Address. Mainly used for internal purpose like conversion, storage, comparison, etc.
* '::' is replaced by zero pads, leading '0' are inserted (if leadingZero = TRUE), converted to lower cases.
* @param Ip Compact IPv6-Address (with CIDR or without CIDR, e.g. 2620:0:2D0:A2A2::7)
* @param leadingZero If TRUE then bit fields are padded with '0' in order to have always 4 characters
* @return The full IPv6 Address with 8 x 16 bits, e.g. '2620:0000:02d0:a2a2:0000:0000:0000:0007'
*/
FUNCTION UncompressIpV6(Ip IN VARCHAR2, leadingZero IN BOOLEAN DEFAULT TRUE) RETURN VARCHAR2 DETERMINISTIC;
/**
* Makes an canonical IPv6 address according to RFC 5952, i.e. human readable.
* @param IPv6 IPv6-Address (with or without '::', with or without leading '0')
* @return Canonical IPv6 Address, e.g. 2620:0:2d0:200::7
*/
FUNCTION Canonical_IPv6(IPv6 IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC;
END IP_Util;
/
CREATE OR REPLACE PACKAGE BODY IP_Util AS
NUMERIC_OVERFLOW EXCEPTION;
PRAGMA EXCEPTION_INIT(NUMERIC_OVERFLOW, -1426);
FUNCTION IP2Decimal(IP IN VARCHAR2) RETURN NUMBER DETERMINISTIC IS
DecimalIp NUMBER; -- INTEGER does not cover (2**128)-1
BEGIN
IF REGEXP_LIKE(IP, ':') THEN
-- IPv6 Address
IF REGEXP_LIKE(IP, '\d+\.\d+\.\d+\.\d+') THEN
-- Mixed notation, e.g.: 0:0:0:0:0:FFFF:129.144.52.38
SELECT SUM(TO_NUMBER(REGEXP_SUBSTR(UncompressIpV6(IP), '[[:xdigit:]]+', 1, LEVEL), 'XXXX') * POWER(65536, 8-LEVEL))
INTO DecimalIp
FROM dual
CONNECT BY LEVEL <= 6;
SELECT DecimalIp + SUM(REGEXP_SUBSTR(REGEXP_SUBSTR(UncompressIpV6(IP), '\d+\.\d+\.\d+\.\d+'), '\d+', 1, LEVEL) * POWER(256, 4-LEVEL))
INTO DecimalIp
FROM dual
CONNECT BY LEVEL <= 4;
RETURN DecimalIp;
ELSE
SELECT SUM(TO_NUMBER(REGEXP_SUBSTR(UncompressIpV6(IP), '[[:xdigit:]]+', 1, LEVEL), 'XXXX') * POWER(65536, 8-LEVEL))
INTO DecimalIp
FROM dual
CONNECT BY LEVEL <= 8;
RETURN DecimalIp;
END IF;
ELSE
-- IPv4 Address
SELECT SUM(REGEXP_SUBSTR(IP, '\d+', 1, LEVEL) * POWER(256, 4-LEVEL))
INTO DecimalIp
FROM dual
CONNECT BY LEVEL <= 4;
RETURN DecimalIp;
END IF;
END IP2Decimal;
FUNCTION IP2RAW(IP IN VARCHAR2, ver IN INTEGER DEFAULT NULL) RETURN RAW DETERMINISTIC IS
BEGIN
IF ver IS NULL THEN
IF REGEXP_LIKE(IP, ':') THEN
RETURN IP2RAW(IP, 6);
ELSE
RETURN IP2RAW(IP, 4);
END IF;
ELSE
IF ver = 6 THEN
RETURN HEXTORAW(LPAD(TO_CHAR(IP2Decimal(ip), 'fmXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'), 32, '0'));
ELSIF ver = 4 THEN
RETURN HEXTORAW(LPAD(TO_CHAR(IP2Decimal(ip), 'fmXXXXXXXX'), 8, '0'));
ELSE
RAISE VALUE_ERROR;
END IF;
END IF;
END IP2RAW;
FUNCTION RAW2IP(ip IN RAW, ver IN INTEGER) RETURN VARCHAR2 DETERMINISTIC IS
res VARCHAR2(45);
BEGIN
-- Range check "TO_NUMBER(ip, 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') < 2**32, resp 2**128" not needed, because RAW values are usually not based on error-prone user input
IF ver = 4 THEN
-- Take only last 32 bit from RAW value with UTL_RAW.SUBSTR(ip, -4)
SELECT LISTAGG(TO_NUMBER(SUBSTR(SUBSTR(LPAD(RAWTOHEX(UTL_RAW.SUBSTR(ip, -4)), 8, '0'), -8), 2*LEVEL-1, 2), 'XX'), '.') WITHIN GROUP (ORDER BY LEVEL)
INTO res
FROM DUAL
CONNECT BY LEVEL <= 4;
RETURN res;
ELSIF ver = 6 THEN
RETURN Canonical_IPv6(SUBSTR(REGEXP_REPLACE(LPAD(RAWTOHEX(ip), 32, '0'), '([[:xdigit:]]{4})', ':\1'), 2));
ELSE
RAISE VALUE_ERROR;
END IF;
END RAW2IP;
FUNCTION Decimal2IP(ip IN NUMBER, ver IN INTEGER) RETURN VARCHAR2 DETERMINISTIC IS
res VARCHAR2(45);
BEGIN
IF ip IS NULL THEN
RETURN NULL;
END IF;
IF ver = 4 THEN
IF ip > 2**32 - 1 THEN
RAISE NUMERIC_OVERFLOW;
END IF;
SELECT LISTAGG(TO_NUMBER(SUBSTR(LPAD(TO_CHAR(ip, 'fmXXXXXXXX'), 8, '0'), 2*LEVEL-1, 2), 'XX'), '.') WITHIN GROUP (ORDER BY LEVEL)
INTO res
FROM dual
CONNECT BY LEVEL <= 4;
RETURN res;
ELSIF ver = 6 THEN
IF ip > 2**128 - 1 THEN
RAISE NUMERIC_OVERFLOW;
END IF;
res := LPAD(TO_CHAR(ip, 'fmxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx'),32, '0');
RETURN Canonical_IPv6(SUBSTR(REGEXP_REPLACE(res, '([[:xdigit:]]{4})', ':\1'), 2));
ELSE
RAISE VALUE_ERROR;
END IF;
END Decimal2IP;
FUNCTION SubnetMask(Ip IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
IF Ip IS NULL OR NOT REGEXP_LIKE(Ip, '/\d{1,3}$') THEN
RETURN NULL;
END IF;
IF REGEXP_LIKE(Ip, ':') THEN
RETURN CIDR2SubnetMask(REGEXP_SUBSTR(Ip, '\d{1,3}$'), 6);
ELSE
RETURN CIDR2SubnetMask(REGEXP_SUBSTR(Ip, '\d{1,2}$'), 4);
END IF;
END SubnetMask;
FUNCTION NetworkPrefix(Ip IN VARCHAR2, SubnetMask IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
IF REGEXP_LIKE(ip, ':') THEN
RETURN RAW2IP(UTL_RAW.BIT_AND(Ip2RAW(Ip, 6), Ip2RAW(SubnetMask, 6)), 6);
ELSE
RETURN RAW2IP(UTL_RAW.BIT_AND(Ip2RAW(Ip, 4),Ip2RAW(SubnetMask, 4)), 4);
END IF;
END NetworkPrefix;
FUNCTION NetworkPrefix(Ip IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
RETURN NetworkPrefix(REGEXP_REPLACE(Ip, '/\d{1,3}$'), SubnetMask(Ip));
END NetworkPrefix;
FUNCTION BroadcastIp(Ip IN VARCHAR2, SubnetMask IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS
Subnet RAW(16);
SubnetInv RAW(16);
BEGIN
IF REGEXP_LIKE(ip, ':') THEN
Subnet := UTL_RAW.BIT_AND(Ip2RAW(Ip, 6), Ip2RAW(SubnetMask, 6));
SubnetInv := UTL_RAW.BIT_COMPLEMENT(Ip2RAW(SubnetMask, 6));
RETURN RAW2IP(UTL_RAW.BIT_OR(Subnet, SubnetInv), 6);
ELSE
Subnet := UTL_RAW.BIT_AND(Ip2RAW(Ip, 4), Ip2RAW(SubnetMask, 4));
SubnetInv := UTL_RAW.BIT_COMPLEMENT(Ip2RAW(SubnetMask, 4));
RETURN RAW2IP(UTL_RAW.BIT_OR(Subnet, SubnetInv), 4);
END IF;
END BroadcastIp;
FUNCTION BroadcastIp(Ip IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS
BEGIN
RETURN BroadcastIp(REGEXP_REPLACE(Ip, '/\d{1,3}$'), SubnetMask(Ip));
END BroadcastIp;
FUNCTION SubnetMask2CIDR(SubnetMask VARCHAR2) RETURN INTEGER RESULT_CACHE DETERMINISTIC IS
ip RAW(16);
cidr INTEGER;
BEGIN
IF SubnetMask IS NULL THEN
RETURN NULL;
END IF;
IF REGEXP_LIKE(SubnetMask, ':') THEN
ip := IP2RAW(SubnetMask, 6);
cidr := 128-LOG(2, TO_NUMBER(UTL_RAW.BIT_COMPLEMENT(ip), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')+1);
ELSE
ip := IP2RAW(SubnetMask, 4);
cidr := 32-LOG(2, TO_NUMBER(UTL_RAW.BIT_COMPLEMENT(ip), 'XXXXXXXX')+1);
END IF;
RETURN cidr;
END SubnetMask2CIDR;
FUNCTION CIDR2SubnetMask(CIDR IN INTEGER, ver IN INTEGER) RETURN VARCHAR2 RESULT_CACHE DETERMINISTIC IS
BEGIN
IF CIDR IS NULL THEN
RETURN NULL;
END IF;
IF ver = 4 THEN
IF CIDR NOT BETWEEN 0 AND 32 THEN
RAISE VALUE_ERROR;
END IF;
RETURN RAW2IP(UTL_RAW.BIT_COMPLEMENT(HEXTORAW(LPAD(TO_CHAR(2**(32-cidr)-1, 'fmXXXXXXXX'),8 , '0'))), 4);
ELSIF ver = 6 THEN
IF CIDR NOT BETWEEN 0 AND 128 THEN
RAISE VALUE_ERROR;
END IF;
RETURN RAW2IP(UTL_RAW.BIT_COMPLEMENT(HEXTORAW(LPAD(TO_CHAR(2**(128-cidr)-1, 'fmXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),32 , '0'))), 6);
ELSE
RAISE VALUE_ERROR;
END IF;
END CIDR2SubnetMask;
FUNCTION UncompressIpV6(Ip IN VARCHAR2, leadingZero IN BOOLEAN DEFAULT TRUE) RETURN VARCHAR2 DETERMINISTIC IS
IpFull VARCHAR2(50);
len INTEGER := 7;
TYPE VARCHAR_TABLE_TYPE IS TABLE OF VARCHAR2(10);
BitFields VARCHAR_TABLE_TYPE;
cidr VARCHAR2(5);
BEGIN
IF NOT REGEXP_LIKE(Ip, ':') THEN
RETURN Ip;
END IF;
cidr := REGEXP_SUBSTR(Ip, '/\d{1,3}$');
IpFull := REGEXP_REPLACE(Ip, '/\d{1,3}$');
IF REGEXP_LIKE(IpFull, '::') THEN
IpFull := REGEXP_REPLACE(REGEXP_REPLACE(IpFull, '^::', '0::'), '::$', '::0');
IF REGEXP_LIKE(IpFull, ':\d+\.\d+\.\d+\.\d+') THEN
-- Mixed notation, e.g.: 2002::FFFF:129.144.52.38
len := 6;
END IF;
WHILE REGEXP_COUNT(IpFull, ':') <= len LOOP
IpFull := REGEXP_REPLACE(IpFull, '::', ':0::');
END LOOP;
IpFull := REGEXP_REPLACE(IpFull, '::', ':');
END IF;
IF NOT leadingZero THEN
RETURN LOWER(IpFull||cidr);
END IF;
SELECT REGEXP_SUBSTR(IpFull, '[^:]+', 1, LEVEL)
BULK COLLECT INTO BitFields
FROM dual
CONNECT BY REGEXP_SUBSTR(IpFull, '[^:]+', 1, LEVEL) IS NOT NULL;
IpFull := LPAD(BitFields(1), 4, '0');
FOR i IN 2..BitFields.COUNT LOOP
IF REGEXP_LIKE(BitFields(i), '\d+\.\d+\.\d+\.\d+') THEN
IpFull := IpFull ||':'||BitFields(i);
ELSE
IpFull := IpFull ||':'||LPAD(BitFields(i), 4, '0');
END IF;
END LOOP;
RETURN LOWER(IpFull)||cidr;
END UncompressIpV6;
FUNCTION Canonical_IPv6(IPv6 IN VARCHAR2) RETURN VARCHAR2 DETERMINISTIC IS
res VARCHAR2(50);
cidr VARCHAR2(5);
BEGIN
IF NOT REGEXP_LIKE(IPv6, ':') THEN
RETURN IPv6;
ELSIF REGEXP_LIKE(IPv6, '::') THEN
-- Do not shorten twice
res := UncompressIpV6(IPv6, FALSE);
ELSE
-- RFC 5952 section-4.3
res := LOWER(IPv6);
END IF;
-- Split CIDR if existing
cidr := REGEXP_SUBSTR(res, '/\d{1,3}$');
res := REGEXP_REPLACE(res, '/\d{1,3}$');
-- remove leading '0', RFC 5952 section-4.1
res := REGEXP_REPLACE(res, '(:|^)0+([[:xdigit:]]+)', '\1\2');
WITH ip AS
-- split IP into 16-bit fields
(SELECT REGEXP_SUBSTR(res, '[^:]+', 1, LEVEL) AS val, LEVEL AS pos
FROM DUAL
CONNECT BY REGEXP_SUBSTR(res, '[^:]+', 1, LEVEL) IS NOT NULL),
p AS
-- find consecutive (at least 2) 0 fields, RFC 5952 section-4.2.2
(SELECT pos, len, match_num
FROM ip
MATCH_RECOGNIZE (
ORDER BY pos
MEASURES
FINAL COUNT(*) AS len,
MATCH_NUMBER() AS match_num
ALL ROWS PER MATCH
PATTERN(zero{2,})
DEFINE zero AS val = '0')
),
m AS
-- select longest run of consecutive 0 fields, RFC 5952 section-4.2.3
(SELECT * FROM p WHERE len = (SELECT MAX(len) FROM p)),
f AS
-- select first sequence of longest run of consecutive 0 fields, RFC 5952 section-4.2.3
(SELECT * FROM m WHERE match_num = (SELECT MIN(match_num) FROM m))
SELECT REGEXP_REPLACE(LISTAGG(NVL2(match_num, ':', val), ':') WITHIN GROUP (ORDER BY pos), ':{2,}', '::')
INTO res
FROM ip
LEFT OUTER JOIN f USING (pos);
RETURN res||cidr;
END Canonical_IPv6;
END IP_Util;
/
然后你可以像这样使用它:
SELECT
IP_Util.NetworkPrefix('2620:0:2d0:200::7/32'),
IP_Util.BroadcastIp('2620:0:2d0:200::7/32')
FROM dual;
2620:: 2620:0:ffff:ffff:ffff:ffff:ffff:ffff
如果您喜欢 2620:0:0:0:0:0:0:0
则使用
IP_Util.UncompressIpV6(IP_Util.NetworkPrefix('2620:0:2d0:200::7/32'), false)
然而,根据RFC 5952 2620::
将是首选格式。
下面是几个如何使用这个包的例子:
-- Determine if (IPv4) Address is a Private IP:
CREATE OR REPLACE FUNCTION IsPrivate_IP(ip IN VARCHAR2) RETURN NUMBER DETERMINISTIC IS
BEGIN
IF IP_Util.NetworkPrefix('10.0.0.0', '255.0.0.0') = IP_Util.NetworkPrefix(ip, '255.0.0.0') THEN
RETURN 1;
ELSIF IP_Util.NetworkPrefix('172.16.0.0', '255.240.0.0') = IP_Util.NetworkPrefix(ip, '255.240.0.0') THEN
RETURN 1;
ELSIF IP_Util.NetworkPrefix('192.168.0.0', '255.255.0.0') = IP_Util.NetworkPrefix(ip, '255.255.0.0') THEN
RETURN 1;
ELSE
RETURN 0;
END IF;
END IsPrivate_IP;
或者更复杂的使用 6to4 和 6RD 网络前缀将 IPv4 转换为 IPv6,反之亦然:
CREATE OR REPLACE FUNCTION NAT64(ip IN VARCHAR2, IpV6mask IN VARCHAR2 DEFAULT '::ffff:0:0') RETURN VARCHAR2 DETERMINISTIC IS
shift INTEGER;
cidr INTEGER;
n NUMBER;
a RAW(16);
b RAW(16);
BEGIN
IF REGEXP_LIKE(ip, ':') THEN
-- Translate from IPv6 to IPv4
IF NOT REGEXP_LIKE(IpV6mask, '/\d+{1,3}$') THEN
RETURN IP_Util.RAW2IP(UTL_RAW.BIT_AND(IP_Util.IP2Raw(ip), HEXTORAW('000000000000000000000000FFFFFFFF')), 4);
ELSE
shift := 128 - REGEXP_SUBSTR(IpV6mask, '\d+{1,3}$');
IF shift < 32 THEN
RAISE VALUE_ERROR;
END IF;
-- Generate mask for IPv4 address, e.g. '0000000000000000FFFFFFFF00000000'
b := HEXTORAW(LPAD(TO_CHAR((2**shift-1) - (2**(shift-32)-1), 'fmXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),32 , '0'));
n := TO_NUMBER(UTL_RAW.BIT_AND(IP_Util.IP2Raw(ip), b), 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX');
-- UTL_RAW.SUBSTR does not work because you can shift only Bytes, I need Bits
RETURN IP_Util.Decimal2IP(TRUNC(n / 2**(shift-32)), 4);
END IF;
ELSE
-- Translate from IPv4 to IPv6
IF NOT REGEXP_LIKE(IpV6mask, '/\d+{1,3}$') THEN
a := UTL_RAW.BIT_AND(IP_Util.IP2Raw(IpV6mask), UTL_RAW.BIT_COMPLEMENT(HEXTORAW(LPAD(TO_CHAR(2**32-1 , 'fmXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),32 , '0'))));
RETURN IP_Util.RAW2IP(UTL_RAW.BIT_OR(a, IP_Util.IP2RAW(ip, 6)), 6);
ELSE
cidr := REGEXP_SUBSTR(IpV6mask, '\d+{1,3}$');
shift := 128 - 32 - cidr;
IF shift < 0 THEN
RAISE VALUE_ERROR;
END IF;
a := UTL_RAW.BIT_AND(IP_Util.IP2Raw(IpV6mask), UTL_RAW.BIT_COMPLEMENT(HEXTORAW(LPAD(TO_CHAR(2**(128-cidr)-1 , 'fmXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),32 , '0'))));
b := HEXTORAW(LPAD(TO_CHAR(2**shift * IP_Util.IP2Decimal(ip), 'fmXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX'),32 , '0'));
RETURN IP_Util.RAW2IP(UTL_RAW.BIT_OR(a, b), 6);
END IF;
END IF;
END NAT64;
关于oracle PL/SQL 如何计算 IPv6 cidr 的范围 ip,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/43221720/
Oracle 即时客户端和 Oracle 客户端有什么区别?你能给我解释一下吗?谢谢 最佳答案 Oracle 客户端带有安装程序和许多可执行文件,例如 sqlplus,tnsping,它是完整而庞大的
我正在寻找一个Delphi组件来直接连接到ORACLE数据库服务器,而无需安装oracle客户端。 我知道Oracle Data Access (ODAC)来自DevArt 。还有其他组件具有此功能吗
如何编写 Oracle 存储过程,以表 (X) 作为输入参数,并在过程内部使用表 X 来与另一个表 Y 联接? 表 X 将包含数千条记录。 不希望将表名作为 varchar 传递,然后使用动态 SQL
如何编写 Oracle 存储过程,以表 (X) 作为输入参数,并在过程内部使用表 X 来与另一个表 Y 联接? 表 X 将包含数千条记录。 不希望将表名作为 varchar 传递,然后使用动态 SQL
我要过滤COMMENTS属性为空的记录 我试过了 SELECT TABLE_NAME, COMMENTS FROM (SELECT TABLE_NAME, COMMENTS FROM (sel
我要下载 Oracle Instant Client for Linux x86-64 (64-bit)现在有一段时间了。 现在我注意到该网站在过去几个月中一直遇到技术问题。 要从 Oracle 下载
有什么方法可以将我的 Delphi 应用程序 (FireDac) 直接连接到 Oracle 数据库? 目前可以连接,但需要安装Oracle Client 在 embarcadero 站点 ( http
我有一张表,其中日期列的数据格式如下:“7/25/2014 12:14:27 AM”。我需要通过放入 where 子句来获取此日期。有人可以建议我该怎么做吗? 最佳答案 日期(存储在表中)是 repr
如果两个事务试图同时修改同一行会发生什么?通常,一旦行被修改,另一个事务等待直到第一个事务执行提交或回滚。但是,如果他们恰好在同一时刻发送更新请求怎么办? 最佳答案 答案是否定的。两个事务不能同时修改
我想知道为什么我不能在 Oracle 模式中有两个同名的索引?它抛出一个错误,指出该名称已被使用。我的印象是,由于索引在一个特定的表上,这应该不会导致任何错误,除非我们对同一个表上的两个不同列使用相同
我需要构建一个查询来按成员和到期日期检索信息组,但我需要为每个成员提供一个序列号.. 例如: 如果成员“A”有 3 条记录要过期,“B”只有 1 条,“C”有 2 条,我需要这样的结果: Number
独立程序 create procedure proc1 ( begin end; ) 存储过程 create package pkg1 ( procedure proc2 begin end; ) 最
在 Oracle 9i 中声明 FK 时遇到问题。我在这里查看了许多关于 SO 和一些在线文档(例如 http://www.techonthenet.com/oracle/foreign_keys/f
我和我的同事维护的应用程序在后端有一个 Oracle 数据库。我们正在考虑偶尔以“受限”模式运行应用程序,其中一个数据库表空间设置为只读。我们可以轻松地将必要的表和索引移动到单独的表空间,这些表空间将
我想实现一个自定义的回归聚合函数,类似于现有的 REGR_SLOPE . 我要定义的函数需要获取两列作为参数,例如 select T.EMPLOYEE_ID, CUSTOM_REGR_SL
我已经尝试解决这个问题一段时间了,我认为是时候寻求帮助了。我正在构建一个架构配置脚本,我想添加一些脚本输出和错误处理。这个想法是脚本输出窗口只会向我显示关键消息而没有所有噪音。 Create Temp
在旧的 Oracle 服务器(我被告知是 8i)上使用 JDBC 时,我遇到了一个非常令人困惑和奇怪的问题。我在那里准备了一个表,其中包含大约 10 列、数字、varchars、一个 raw(255)
我有一张 table Customer_Chronics在 Oracle 11g 中。 该表具有三个关键列,如下所示: 分支代码 客户 ID 期 我已按 branch_code 列表按表分区,现在我进
是否有存储用户自定义异常的oracle表? 最佳答案 没有。 与其他变量一样,用户定义的异常在 PL/SQL block 中定义,并且具有 PL/SQL 变量将具有的任何范围。所以,例如 DECLAR
在 oracle 中使用序列并使用 Before insert trigger 自动递增列或使用标识列是否更好,因为它在 Oracle 12 c 中可用? 最佳答案 无论哪种方式,您都将使用序列。 1
我是一名优秀的程序员,十分优秀!