<small id='ZetGs'></small><noframes id='ZetGs'>

  • <tfoot id='ZetGs'></tfoot>
    1. <legend id='ZetGs'><style id='ZetGs'><dir id='ZetGs'><q id='ZetGs'></q></dir></style></legend>

          <bdo id='ZetGs'></bdo><ul id='ZetGs'></ul>
        <i id='ZetGs'><tr id='ZetGs'><dt id='ZetGs'><q id='ZetGs'><span id='ZetGs'><b id='ZetGs'><form id='ZetGs'><ins id='ZetGs'></ins><ul id='ZetGs'></ul><sub id='ZetGs'></sub></form><legend id='ZetGs'></legend><bdo id='ZetGs'><pre id='ZetGs'><center id='ZetGs'></center></pre></bdo></b><th id='ZetGs'></th></span></q></dt></tr></i><div id='ZetGs'><tfoot id='ZetGs'></tfoot><dl id='ZetGs'><fieldset id='ZetGs'></fieldset></dl></div>
      1. INET6_ATON 和 NTOA 函数的 Oracle PL/SQL 版本?

        时间:2023-11-28

            • <legend id='zKTVN'><style id='zKTVN'><dir id='zKTVN'><q id='zKTVN'></q></dir></style></legend>

            • <i id='zKTVN'><tr id='zKTVN'><dt id='zKTVN'><q id='zKTVN'><span id='zKTVN'><b id='zKTVN'><form id='zKTVN'><ins id='zKTVN'></ins><ul id='zKTVN'></ul><sub id='zKTVN'></sub></form><legend id='zKTVN'></legend><bdo id='zKTVN'><pre id='zKTVN'><center id='zKTVN'></center></pre></bdo></b><th id='zKTVN'></th></span></q></dt></tr></i><div id='zKTVN'><tfoot id='zKTVN'></tfoot><dl id='zKTVN'><fieldset id='zKTVN'></fieldset></dl></div>

              <small id='zKTVN'></small><noframes id='zKTVN'>

                <tbody id='zKTVN'></tbody>

              1. <tfoot id='zKTVN'></tfoot>
                  <bdo id='zKTVN'></bdo><ul id='zKTVN'></ul>
                  本文介绍了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
                     -- Short-circuit the most popular, and also catch the special case of IPv4 addresses in IPv6
                     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     -- INTEGER only holds 126 binary digits, IPv6 has 128
                  DETERMINISTIC
                  IS
                     iptype NATURAL := ipguess(ip_string);
                     ip     VARCHAR2(32);
                     ipwork VARCHAR2(64);
                     d      INTEGER;
                     q      VARCHAR2(3);
                  BEGIN
                     IF    iptype = 4 THEN
                        -- Sanity check
                        ipwork := REGEXP_SUBSTR(ip_string, 'd{1,3}(.d{1,3}){3}');
                        IF ipwork IS NULL THEN RETURN NULL; END IF;
                  
                        -- Starting prefix
                        -- NOTE: 2^48 - 2^32 = 281470681743360 = ::ffff:0.0.0.0
                        --       (for compatibility with IPv4 addresses in IPv6)
                        ip := '00000000000000000000ffff';
                  
                        -- Parse the input
                        WHILE LENGTH(ipwork) IS NOT NULL
                        LOOP
                           d := INSTR(ipwork, '.');  -- find the dot
                           IF d > 0 THEN             -- isolate the decimal octet
                              q      := SUBSTR(ipwork, 1, d - 1);
                              ipwork := SUBSTR(ipwork, d + 1);
                           ELSE
                              q      := ipwork;
                              ipwork := '';
                           END IF;
                  
                           -- convert to a hex string
                           ip := ip || TO_CHAR(TO_NUMBER(q), 'FM0x');
                  
                        END LOOP;
                     ELSIF iptype = 6 THEN
                        -- Short-circuit "::" = 0
                        IF ip_string = '::' THEN RETURN LPAD('0', 32, '0'); END IF;
                  
                        -- Sanity check
                        ipwork := REGEXP_SUBSTR(ip_string, '[[:xdigit:]]{0,4}(:[[:xdigit:]]{0,4}){0,7}');
                        IF ipwork IS NULL THEN RETURN NULL; END IF;
                  
                        -- Replace leading zeros
                        -- (add a bunch to all of the pairs, then remove only the required ones)
                        ipwork := REGEXP_REPLACE(ipwork, '(^|:)([[:xdigit:]]{1,4})', '10002');
                        ipwork := REGEXP_REPLACE(ipwork, '(^|:)0+([[:xdigit:]]{4})',    '12');
                  
                        -- Groups of zeroes
                        -- (total length should be 32+Z, so the gap would be the zeroes)
                        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
                     -- Sanity checks
                     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;  -- the exact IP, such as /32 on IPv4
                     ELSIF cidr_exp <  0   THEN RETURN NULL;
                     ELSIF cidr_exp >  128 THEN RETURN NULL;
                     END IF;
                  
                     -- Change some variables around if we are working with the first/largest half
                     IF is_big = 1 THEN
                        ipwork   := iphalf1;
                        iphalf2  := TO_CHAR((2 ** 64 - 1) * is_end, 'FM0xxxxxxxxxxxxxxx');  -- either all 0 or all F
                        cidr_exp := cidr_exp - 64;
                     END IF;
                  
                     -- Normalize IP to divisions of CIDR
                     subnet := 2 ** cidr_exp;
                     ipint  := TO_NUMBER(ipwork, 'FM0xxxxxxxxxxxxxxx');
                     -- if is_end = 1 then add one net range (then subtract one IP) to get the ending range
                     ipwork := TO_CHAR(FLOOR(ipint / subnet + is_end) * subnet - is_end, 'FM0xxxxxxxxxxxxxxx');
                  
                     -- Re-integrate
                     IF is_big = 0 THEN iphalf2 := ipwork;
                     ELSE               iphalf1 := ipwork;
                     END IF;
                  
                     RETURN SUBSTR(iphalf1 || iphalf2, 1, 32);
                  
                  END nettohex;
                  
                  -- WHERE clause:
                  -- 1. BETWEEN compare:
                  --    iptohex(a.ip_addy) BETWEEN nettohex(b.net_addy, b.cidr, 0) AND nettohex(b.net_addy, b.cidr, 1)
                  --
                  --    Requires three function-based indexes, but all of them would work, as they are all inside the tables.
                  --
                  -- 2. CIDR match:
                  --    nettohex(a.ip_addy, b.cidr) = nettohex(b.net_addy, b.cidr)
                  --
                  --    Only two functions and uses exact match, but first one requires an outside variable.  Last one would be only function-based index.
                  --    An FBI of iptohex(a.ip_addy) could be implemented, but it's questionable if nettohex would use that index.
                  --
                  -- Recommended FBIs:
                  --
                  -- (SUBSTR(iptohex(a.ip_addy), 1, 32))
                  -- (SUBSTR(nettohex(b.ip_addy, b.cidr, 0), 1, 32), SUBSTR(nettohex(b.ip_addy, b.cidr, 1), 1, 32))
                  --
                  -- NOTE: Will need to use the SUBSTR form for the above WHERE clauses!
                  

                  更新: 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:

                  ip              VARCHAR2(39),
                  cidr            NUMBER(2),
                  ip_hex          AS (SUBSTR(iptohex(ip),           1, 32)) VIRTUAL,
                  ip_nethex_start AS (SUBSTR(nettohex(ip, cidr, 0), 1, 32)) VIRTUAL,
                  ip_nethex_end   AS (SUBSTR(nettohex(ip, cidr, 1), 1, 32)) VIRTUAL,
                  

                  和索引如:

                  CREATE INDEX foobar_iphex_idx ON foobar (ip_hex);
                  CREATE INDEX foobar_ipnet_idx ON foobar (ip_nethex_start, ip_nethex_end);
                  

                  使用 WHERE 子句,例如:

                  Using WHERE clauses like:

                  a.ip_hex BETWEEN b.ip_nethex_start AND b.ip_nethex_end
                  nettohex(a.ip, b.cidr) = b.ip_nethex_start  -- not as effective
                  

                  这篇关于INET6_ATON 和 NTOA 函数的 Oracle PL/SQL 版本?的文章就介绍到这了,希望我们推荐的答案对大家有所帮助,也希望大家多多支持跟版网!

                  上一篇:为什么在创建这个简单的物化视图示例时会出现 ORA-12054 错误? 下一篇:如何使用 PL/SQL 创建 PDF 报告

                  相关文章

                  • <bdo id='Wowbs'></bdo><ul id='Wowbs'></ul>
                  <i id='Wowbs'><tr id='Wowbs'><dt id='Wowbs'><q id='Wowbs'><span id='Wowbs'><b id='Wowbs'><form id='Wowbs'><ins id='Wowbs'></ins><ul id='Wowbs'></ul><sub id='Wowbs'></sub></form><legend id='Wowbs'></legend><bdo id='Wowbs'><pre id='Wowbs'><center id='Wowbs'></center></pre></bdo></b><th id='Wowbs'></th></span></q></dt></tr></i><div id='Wowbs'><tfoot id='Wowbs'></tfoot><dl id='Wowbs'><fieldset id='Wowbs'></fieldset></dl></div>

                • <legend id='Wowbs'><style id='Wowbs'><dir id='Wowbs'><q id='Wowbs'></q></dir></style></legend>

                • <small id='Wowbs'></small><noframes id='Wowbs'>

                    <tfoot id='Wowbs'></tfoot>