当前位置：文江博客话题详情

在 Oracle 中表示 IPv4/IPv6 地址

发布于 2024-10-16 13:45:15 字数 337 浏览 15 评论 0原文

在 Oracle 中，表示网络地址（哪些地址可能是 IPv4 或 IPv6）的适当数据类型或技术是什么？

背景：我正在转换一个记录网络活动的表，该表使用 PostgreSQL inet 数据类型将 v4 和 v6 地址保存在同一个表中。

但是，没有行同时包含 v4 和 v6 地址。（也就是说，记录要么来自机器的 v4 堆栈，要么来自机器的 v6 堆栈。）

原文

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

夜司空 2024-10-23 13:45:15

在Oracle中，什么是合适的
数据类型或技术
代表网络地址，其中
地址可以是 IPv4 或 IPv6

有两种方法：

仅存储。
存储常规表示

仅用于存储。 IPV4 地址应该是整数（32 位就足够了）。对于 IP V6，128 位，INTEGER（类似于 Number(38)）就可以了。当然，这是存储。该方法认为表示是应用程序的事情。

如果采取相反的策略，即存储常规表示形式，则需要确保 IP V4 和 IPV6 地址仅具有一种常规（字符串）表示形式。它因 ipV4 而闻名。至于IPV6，也有一个标准格式。

我倾向于第一个策略。在最坏的情况下，您可以采用混合方法（尽管不是酸性的）并将二进制和 ASCII 表示并排存储，并“优先”于二进制值。

没有行同时包含 v4 和 v6
但是，地址。

IPV6 格式的 IPV4 地址的标准表示为：::ffff:192.0.2.128。

我不知道上下文，但我会保留 2 列，一列用于 IPV4，另一列用于不同的 ipV6 地址。

更新
根据 @sleepyMonad 的精彩评论，我想指出，最好使用 INTEGER 数据类型，而不是 Number 数据类型，它会很乐意容纳可能的最高值用 128 位整数“ff...ff”表示（需要 39 十进制数字）。 38 是 10 的最高幂，范围从 0 到 9，可以在 128 位上进行编码，但仍然可以插入 2**128 - 1 的最大无符号值（十进制 340282366920938463463374607431768211455）。这是一个小测试来说明这种可能性。

create table test (
  id integer primary key,
  ipv6_address_bin INTEGER );

-- Let's enter 2**128 - 1 in the nueric field
insert into test (id, ipv6_address_bin) values ( 1, to_number ( 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF', 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') ) ;

-- retrieve it to make sure it's not "truncated".
select to_char ( ipv6_address_bin, 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' ) from test where id = 1 ;
-- yields 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF'

select to_char ( ipv6_address_bin ) from test where id = 1 ;
-- yields 340282366920938463463374607431768211455

select LOG(2, ipv6_address_bin) from test where id = 1 ;
-- yields 128

select LOG(10, ipv6_address_bin) from test where id = 1 ;
-- yields > 38

In Oracle, what is the appropriate
data type or technique for
representing network addresses, which
addresses may be IPv4 or IPv6

There are two approaches :

storing only.
storing the conventional representation

For storing only. An IPV4 address should be an integer (32bits are enough). For IP V6, 128 bits, INTEGER (which is similar to Number(38)) will do. Of course, that's storing. That approach takes the view that the representation is a matter for the application.

If one take the opposite strategy, of storing the conventional representation, one needs to make sure that IP V4 and IPV6 addresses have only one conventional (string) representation. It's well-known for ipV4. As for IPV6, there is also a standard format.

My preference goes to the first strategy. In the worst case, you can adopt an hybrid approach (non acid though) and store both the binary and the ascii representation side by side with "priority" to the binary value.

No row contains both v4 and v6
addresses, however.

The standard representation of a IPV4 address in IPV6 format is : ::ffff:192.0.2.128.

I don't know the context but I would however reserve 2 columns, one for IPV4 and the other for a distinct ipV6 address.

Update
Following a good comment by @sleepyMonad's, I'd like to point out that instead of the Number data type it is preferable to use the INTEGER data type, which will happily accommodate the highest possible value that can be expressed with a 128 bits integer 'ff...ff' (which would need 39 decimal digits). 38 is the highest power of ten ranging from 0 to 9 that can be encoded on 128 bits but one can still insert the maximum unsigned value for 2**128 - 1 (decimal 340282366920938463463374607431768211455). Here is a small test to illustrate this possibility.

create table test (
  id integer primary key,
  ipv6_address_bin INTEGER );

-- Let's enter 2**128 - 1 in the nueric field
insert into test (id, ipv6_address_bin) values ( 1, to_number ( 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF', 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX') ) ;

-- retrieve it to make sure it's not "truncated".
select to_char ( ipv6_address_bin, 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX' ) from test where id = 1 ;
-- yields 'FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF'

select to_char ( ipv6_address_bin ) from test where id = 1 ;
-- yields 340282366920938463463374607431768211455

select LOG(2, ipv6_address_bin) from test where id = 1 ;
-- yields 128

select LOG(10, ipv6_address_bin) from test where id = 1 ;
-- yields > 38

回复收藏 0 原文

吃不饱 2024-10-23 13:45:15

将其存储为 RAW。

RAW 是可变长度字节数组，因此...

只需将 IPv4 视为 4 字节数组
，将 IPv6 视为 16 字节数组

...并将其中之一直接存储在 RAW(16) 中。

RAW 可以被索引，可以是 PK、UNIQUE 或 FOREIGN KEY，因此您可以使用 VARCHAR2 或 INT/NUMBER/DECIMAL 执行通常可以执行的任何操作，但转换和存储开销更少。

为了说明 INT 相对于 RAW 的存储开销，请考虑以下示例：

CREATE TABLE IP_TABLE (
    ID INT PRIMARY KEY,
    IP_RAW RAW(16), 
    IP_INT INT
);

INSERT INTO IP_TABLE (ID, IP_RAW, IP_INT) VALUES (
    1,
    HEXTORAW('FFFFFFFF'),
    TO_NUMBER('FFFFFFFF', 'XXXXXXXX')
);

INSERT INTO IP_TABLE (ID, IP_RAW, IP_INT) VALUES (
    2,
    HEXTORAW('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF'),
    TO_NUMBER('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF', 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
);

SELECT VSIZE(IP_RAW), VSIZE(IP_INT), IP_TABLE.*  FROM IP_TABLE;

结果（在 Oracle 10.2 下）：

table IP_TABLE created.
1 rows inserted.
1 rows inserted.
VSIZE(IP_RAW)          VSIZE(IP_INT)          ID                     IP_RAW                           IP_INT                 
---------------------- ---------------------- ---------------------- -------------------------------- ---------------------- 
4                      6                      1                      FFFFFFFF                         4294967295             
16                     21                     2                      FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF 340282366920938463463374607431768211455

Store it in RAW.

RAW is variable-length byte array, so....

just treat the IPv4 as an array of 4 bytes
and IPv6 as an array of 16 bytes

...and store either one of them in directly in RAW(16).

RAW can be indexed, be a PK, UNIQUE or FOREIGN KEY, so you can do anything you normally could with VARCHAR2 or INT/NUMBER/DECIMAL, but with less conversion and storage overhead.

To illustrate the storage overhead of INT over RAW, consider the following example:

CREATE TABLE IP_TABLE (
    ID INT PRIMARY KEY,
    IP_RAW RAW(16), 
    IP_INT INT
);

INSERT INTO IP_TABLE (ID, IP_RAW, IP_INT) VALUES (
    1,
    HEXTORAW('FFFFFFFF'),
    TO_NUMBER('FFFFFFFF', 'XXXXXXXX')
);

INSERT INTO IP_TABLE (ID, IP_RAW, IP_INT) VALUES (
    2,
    HEXTORAW('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF'),
    TO_NUMBER('FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF', 'XXXXXXXXXXXXXXXXXXXXXXXXXXXXXXXX')
);

SELECT VSIZE(IP_RAW), VSIZE(IP_INT), IP_TABLE.*  FROM IP_TABLE;

The result (under Oracle 10.2):

table IP_TABLE created.
1 rows inserted.
1 rows inserted.
VSIZE(IP_RAW)          VSIZE(IP_INT)          ID                     IP_RAW                           IP_INT                 
---------------------- ---------------------- ---------------------- -------------------------------- ---------------------- 
4                      6                      1                      FFFFFFFF                         4294967295             
16                     21                     2                      FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF 340282366920938463463374607431768211455

回复收藏 0 原文

心如狂蝶 2024-10-23 13:45:15

@Alain Pannetier（因为我还不能发表评论）：
ANSI INTEGER 数据类型根据 http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54335。
在表下方，您可以找到 NUMBER 仅提供 126 位二进制精度的信息，这对于 128 位 IPv6 地址来说是不够的。
最大值可能可以很好地存储，但有些地址会四舍五入到下一个较低的地址。

内部数字格式为 ROUND((length(p)+s)/2))+1 (http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#i16209）。

更新：再次解决这个问题后，我现在找到了一个解决方案，可以对包含 IPv6 地址的网络进行高性能查询：将 IPv6 地址和子网掩码存储在 RAW(16) 列中，使用 UTL_RAW.BIT_AND 比较它们：

SELECT name, DECODE(UTL_RAW.BIT_AND('20010DB8000000000000000000000001', ipv6_mask), ipv6_net, 1, 0)
FROM ip_net
WHERE ipv6_net IS NOT NULL;

@Alain Pannetier (because I can't comment yet):
The ANSI INTEGER datatype maps to NUMBER(38) in Oracle according to http://download.oracle.com/docs/cd/B19306_01/server.102/b14200/sql_elements001.htm#i54335.
Below the table you find the info that NUMBER only provides 126bit binary precision which is not enought for a 128bit IPv6 address.
The maximum value might store fine but there will be addresses that are rouned to the next lower one.

The internal numeric format is ROUND((length(p)+s)/2))+1 (http://download.oracle.com/docs/cd/B19306_01/server.102/b14220/datatype.htm#i16209).

Update: After fiddling around with the issue again I've now found a solution that allows high performance querying of networks that contain an IPv6 address: store the IPv6 addresses and subnet masks in RAW(16) columns and compare them using UTL_RAW.BIT_AND:

SELECT name, DECODE(UTL_RAW.BIT_AND('20010DB8000000000000000000000001', ipv6_mask), ipv6_net, 1, 0)
FROM ip_net
WHERE ipv6_net IS NOT NULL;

回复收藏 0 原文

月野兔 2024-10-23 13:45:15

您还可以使用自定义的 oracle 对象。

SQL>set SERVEROUTPUT on
SQL>drop table test;

Table dropped.

SQL>drop type body inaddr;

Type body dropped.

SQL>drop type inaddr;

Type dropped.

SQL>create type inaddr as object
  2  ( /* TODO enter attribute and method declarations here */
  3  A number(5),
  4  B number(5),
  5  C number(5),
  6  D number(5),
  7  E number(5),
  8  F number(5),
  9  G number(5),
 10  H NUMBER(5),
 11  MAP MEMBER FUNCTION display RETURN VARCHAR2,
 12  MEMBER FUNCTION toString( SELF IN INADDR , CONTRACT BOOLEAN DEFAULT TRUE) RETURN VARCHAR2,
 13  CONSTRUCTOR FUNCTION INADDR(SELF IN OUT NOCOPY INADDR, INADDRASSTRING VARCHAR2)  RETURN SELF AS RESULT
 14  
 15  ) NOT FINAL;
 16  /

SP2-0816: Type created with compilation warnings

SQL>
SQL>
SQL>CREATE TYPE BODY INADDR AS
  2  
  3  MAP MEMBER FUNCTION display RETURN VARCHAR2
  4  IS BEGIN
  5  return tostring(FALSE);
  6  END;
  7  
  8  
  9  MEMBER FUNCTION TOSTRING( SELF IN  INADDR , CONTRACT BOOLEAN DEFAULT TRUE) RETURN VARCHAR2 IS
 10  IP4 VARCHAR2(6) := 'FM990';
 11  ip6 varchar2(6) := 'FM0XXX';
 12    BEGIN
 13  IF CONTRACT THEN
 14    ip6 := 'FMXXXX';
 15  end if;
 16  
 17  IF CONTRACT AND A =0 AND B=0 AND C = 0 AND D=0 AND E =0 AND F = 65535 THEN --ipv4
 18      RETURN  '::FFFF:'||TO_CHAR(TRUNC(G/256),'FM990.')||TO_CHAR(MOD(G,256),'FM990.')||TO_CHAR(TRUNC(H/256),'FM990.')||TO_CHAR(MOD(H,256),'FM990');
 19  ELSE
 20      RETURN
 21  TO_CHAR(A,ip6)||':'||
 22  TO_CHAR(B,IP6)||':'||
 23  TO_CHAR(C,ip6)||':'||
 24  TO_CHAR(D,ip6)||':'||
 25  TO_CHAR(E,ip6)||':'||
 26  TO_CHAR(F,ip6)||':'||
 27  TO_CHAR(G,ip6)||':'||
 28  TO_CHAR(H,ip6);
 29  end if;
 30    end;
 31  
 32      CONSTRUCTOR FUNCTION inaddr(SELF IN OUT NOCOPY inaddr, inaddrasstring VARCHAR2)
 33                                 RETURN SELF AS RESULT IS
 34      begin
 35          if instr(inaddrasstring,'.') > 0 then
 36            --ip4
 37  null;
 38              a := 0;
 39              B := 0;
 40              C := 0;
 41              D := 0;
 42              E := 0;
 43              F := TO_NUMBER('FFFF', 'XXXX');
 44              G := TO_NUMBER(TO_CHAR(TO_NUMBER(REGEXP_SUBSTR(INADDRASSTRING,'([0-9]{1,3}).',1,1,'i',1),'999'),'FM0X')
 45  ||TO_CHAR(TO_NUMBER(REGEXP_SUBSTR(INADDRASSTRING,'([0-9]{1,3}).',1,2,'i',1),'999'),'FM0X')
 46  ,'XXXX');
 47              h := TO_NUMBER(TO_CHAR(TO_NUMBER(REGEXP_SUBSTR(INADDRASSTRING,'([0-9]{1,3}).',1,3,'i',1),'999'),'FM0X')
 48  ||TO_CHAR(TO_NUMBER(REGEXP_SUBSTR(INADDRASSTRING,'([0-9]{1,3})',1,4,'i',1),'999'),'FM0X')
 49  ,'XXXX');
 50  
 51          ELSIF instr(inaddrasstring,':') > 0 then
 52              --ip6
 53              a := TO_NUMBER(REGEXP_SUBSTR(inaddrasstring,'([0-9a-fA-F]{1,4})',1,1,'i',1),'XXXX');
 54              b := TO_NUMBER(REGEXP_SUBSTR(inaddrasstring,'([0-9a-fA-F]{1,4})',1,2,'i',1),'XXXX');
 55              c := TO_NUMBER(REGEXP_SUBSTR(inaddrasstring,'([0-9a-fA-F]{1,4})',1,3,'i',1),'XXXX');
 56              d := TO_NUMBER(REGEXP_SUBSTR(inaddrasstring,'([0-9a-fA-F]{1,4})',1,4,'i',1),'XXXX');
 57              E := TO_NUMBER(REGEXP_SUBSTR(inaddrasstring,'([0-9a-fA-F]{1,4})',1,5,'i',1),'XXXX');
 58              f := TO_NUMBER(REGEXP_SUBSTR(inaddrasstring,'([0-9a-fA-F]{1,4})',1,6,'i',1),'XXXX');
 59              g := TO_NUMBER(REGEXP_SUBSTR(inaddrasstring,'([0-9a-fA-F]{1,4})',1,7,'i',1),'XXXX');
 60              H := TO_NUMBER(REGEXP_SUBSTR(inaddrasstring,'([0-9a-fA-F]{1,4})',1,8,'i',1),'XXXX');
 61          end if;
 62  
 63          RETURN;
 64      END;
 65  end;
 66  /

Type body created.

SQL>
SQL>create table test
  2  (id integer primary key,
  3  address inaddr);

Table created.

SQL>
SQL>select * from test;

no rows selected

SQL>
SQL>
SQL>insert into test values (1, INADDR('fe80:0000:0000:0000:0202:b3ff:fe1e:8329') );

1 row created.

SQL>INSERT INTO TEST VALUES (2, INADDR('192.0.2.128') );

1 row created.

SQL>insert into test values (3, INADDR('20.0.20.1') );

1 row created.

SQL>insert into test values (4, INADDR('fe80:0001:0002:0003:0202:b3ff:fe1e:8329') );

1 row created.

SQL>insert into test values (5, INADDR('fe80:0003:0002:0003:0202:b3ff:fe1e:8329') );

1 row created.

SQL>INSERT INTO TEST VALUES (6, INADDR('fe80:0003:0001:0003:0202:b3ff:fe1e:8329') );

1 row created.

SQL>INSERT INTO TEST VALUES (7, INADDR('fe80:0003:0001:0003:0202:b3ff:fe1e:8328') );

1 row created.

SQL>INSERT INTO TEST VALUES (8, INADDR('dead:beef:f00d:cafe:dea1:aced:b00b:1234') );

1 row created.

SQL>
SQL>COLUMN INET_ADDRESS_SHORT FORMAT A40
SQL>column inet_address_full format a40
SQL>
SQL>select t.address.toString() inet_address_short, t.address.display( ) inet_address_full
  2  from test T
  3  order by t.address ;

INET_ADDRESS_SHORT                       INET_ADDRESS_FULL
---------------------------------------- ----------------------------------------
::FFFF:20.0.20.1                         0000:0000:0000:0000:0000:FFFF:1400:1401
::FFFF:192.0.2.128                       0000:0000:0000:0000:0000:FFFF:C000:0280
DEAD:BEEF:F00D:CAFE:DEA1:ACED:B00B:1234  DEAD:BEEF:F00D:CAFE:DEA1:ACED:B00B:1234
FE80:0:0:0:202:B3FF:FE1E:8329            FE80:0000:0000:0000:0202:B3FF:FE1E:8329
FE80:1:2:3:202:B3FF:FE1E:8329            FE80:0001:0002:0003:0202:B3FF:FE1E:8329
FE80:3:1:3:202:B3FF:FE1E:8328            FE80:0003:0001:0003:0202:B3FF:FE1E:8328
FE80:3:1:3:202:B3FF:FE1E:8329            FE80:0003:0001:0003:0202:B3FF:FE1E:8329
FE80:3:2:3:202:B3FF:FE1E:8329            FE80:0003:0002:0003:0202:B3FF:FE1E:8329

8 rows selected.

SQL>spool off

我只是在最后一个小时把这些放在一起（同时自学了对象）所以我确信它可以改进。如果我进行更新，我会在这里重新发布它们

you could also use a custom oracle object.

SQL>set SERVEROUTPUT on
SQL>drop table test;

Table dropped.

SQL>drop type body inaddr;

Type body dropped.

SQL>drop type inaddr;

Type dropped.

SQL>create type inaddr as object
  2  ( /* TODO enter attribute and method declarations here */
  3  A number(5),
  4  B number(5),
  5  C number(5),
  6  D number(5),
  7  E number(5),
  8  F number(5),
  9  G number(5),
 10  H NUMBER(5),
 11  MAP MEMBER FUNCTION display RETURN VARCHAR2,
 12  MEMBER FUNCTION toString( SELF IN INADDR , CONTRACT BOOLEAN DEFAULT TRUE) RETURN VARCHAR2,
 13  CONSTRUCTOR FUNCTION INADDR(SELF IN OUT NOCOPY INADDR, INADDRASSTRING VARCHAR2)  RETURN SELF AS RESULT
 14  
 15  ) NOT FINAL;
 16  /

SP2-0816: Type created with compilation warnings

SQL>
SQL>
SQL>CREATE TYPE BODY INADDR AS
  2  
  3  MAP MEMBER FUNCTION display RETURN VARCHAR2
  4  IS BEGIN
  5  return tostring(FALSE);
  6  END;
  7  
  8  
  9  MEMBER FUNCTION TOSTRING( SELF IN  INADDR , CONTRACT BOOLEAN DEFAULT TRUE) RETURN VARCHAR2 IS
 10  IP4 VARCHAR2(6) := 'FM990';
 11  ip6 varchar2(6) := 'FM0XXX';
 12    BEGIN
 13  IF CONTRACT THEN
 14    ip6 := 'FMXXXX';
 15  end if;
 16  
 17  IF CONTRACT AND A =0 AND B=0 AND C = 0 AND D=0 AND E =0 AND F = 65535 THEN --ipv4
 18      RETURN  '::FFFF:'||TO_CHAR(TRUNC(G/256),'FM990.')||TO_CHAR(MOD(G,256),'FM990.')||TO_CHAR(TRUNC(H/256),'FM990.')||TO_CHAR(MOD(H,256),'FM990');
 19  ELSE
 20      RETURN
 21  TO_CHAR(A,ip6)||':'||
 22  TO_CHAR(B,IP6)||':'||
 23  TO_CHAR(C,ip6)||':'||
 24  TO_CHAR(D,ip6)||':'||
 25  TO_CHAR(E,ip6)||':'||
 26  TO_CHAR(F,ip6)||':'||
 27  TO_CHAR(G,ip6)||':'||
 28  TO_CHAR(H,ip6);
 29  end if;
 30    end;
 31  
 32      CONSTRUCTOR FUNCTION inaddr(SELF IN OUT NOCOPY inaddr, inaddrasstring VARCHAR2)
 33                                 RETURN SELF AS RESULT IS
 34      begin
 35          if instr(inaddrasstring,'.') > 0 then
 36            --ip4
 37  null;
 38              a := 0;
 39              B := 0;
 40              C := 0;
 41              D := 0;
 42              E := 0;
 43              F := TO_NUMBER('FFFF', 'XXXX');
 44              G := TO_NUMBER(TO_CHAR(TO_NUMBER(REGEXP_SUBSTR(INADDRASSTRING,'([0-9]{1,3}).',1,1,'i',1),'999'),'FM0X')
 45  ||TO_CHAR(TO_NUMBER(REGEXP_SUBSTR(INADDRASSTRING,'([0-9]{1,3}).',1,2,'i',1),'999'),'FM0X')
 46  ,'XXXX');
 47              h := TO_NUMBER(TO_CHAR(TO_NUMBER(REGEXP_SUBSTR(INADDRASSTRING,'([0-9]{1,3}).',1,3,'i',1),'999'),'FM0X')
 48  ||TO_CHAR(TO_NUMBER(REGEXP_SUBSTR(INADDRASSTRING,'([0-9]{1,3})',1,4,'i',1),'999'),'FM0X')
 49  ,'XXXX');
 50  
 51          ELSIF instr(inaddrasstring,':') > 0 then
 52              --ip6
 53              a := TO_NUMBER(REGEXP_SUBSTR(inaddrasstring,'([0-9a-fA-F]{1,4})',1,1,'i',1),'XXXX');
 54              b := TO_NUMBER(REGEXP_SUBSTR(inaddrasstring,'([0-9a-fA-F]{1,4})',1,2,'i',1),'XXXX');
 55              c := TO_NUMBER(REGEXP_SUBSTR(inaddrasstring,'([0-9a-fA-F]{1,4})',1,3,'i',1),'XXXX');
 56              d := TO_NUMBER(REGEXP_SUBSTR(inaddrasstring,'([0-9a-fA-F]{1,4})',1,4,'i',1),'XXXX');
 57              E := TO_NUMBER(REGEXP_SUBSTR(inaddrasstring,'([0-9a-fA-F]{1,4})',1,5,'i',1),'XXXX');
 58              f := TO_NUMBER(REGEXP_SUBSTR(inaddrasstring,'([0-9a-fA-F]{1,4})',1,6,'i',1),'XXXX');
 59              g := TO_NUMBER(REGEXP_SUBSTR(inaddrasstring,'([0-9a-fA-F]{1,4})',1,7,'i',1),'XXXX');
 60              H := TO_NUMBER(REGEXP_SUBSTR(inaddrasstring,'([0-9a-fA-F]{1,4})',1,8,'i',1),'XXXX');
 61          end if;
 62  
 63          RETURN;
 64      END;
 65  end;
 66  /

Type body created.

SQL>
SQL>create table test
  2  (id integer primary key,
  3  address inaddr);

Table created.

SQL>
SQL>select * from test;

no rows selected

SQL>
SQL>
SQL>insert into test values (1, INADDR('fe80:0000:0000:0000:0202:b3ff:fe1e:8329') );

1 row created.

SQL>INSERT INTO TEST VALUES (2, INADDR('192.0.2.128') );

1 row created.

SQL>insert into test values (3, INADDR('20.0.20.1') );

1 row created.

SQL>insert into test values (4, INADDR('fe80:0001:0002:0003:0202:b3ff:fe1e:8329') );

1 row created.

SQL>insert into test values (5, INADDR('fe80:0003:0002:0003:0202:b3ff:fe1e:8329') );

1 row created.

SQL>INSERT INTO TEST VALUES (6, INADDR('fe80:0003:0001:0003:0202:b3ff:fe1e:8329') );

1 row created.

SQL>INSERT INTO TEST VALUES (7, INADDR('fe80:0003:0001:0003:0202:b3ff:fe1e:8328') );

1 row created.

SQL>INSERT INTO TEST VALUES (8, INADDR('dead:beef:f00d:cafe:dea1:aced:b00b:1234') );

1 row created.

SQL>
SQL>COLUMN INET_ADDRESS_SHORT FORMAT A40
SQL>column inet_address_full format a40
SQL>
SQL>select t.address.toString() inet_address_short, t.address.display( ) inet_address_full
  2  from test T
  3  order by t.address ;

INET_ADDRESS_SHORT                       INET_ADDRESS_FULL
---------------------------------------- ----------------------------------------
::FFFF:20.0.20.1                         0000:0000:0000:0000:0000:FFFF:1400:1401
::FFFF:192.0.2.128                       0000:0000:0000:0000:0000:FFFF:C000:0280
DEAD:BEEF:F00D:CAFE:DEA1:ACED:B00B:1234  DEAD:BEEF:F00D:CAFE:DEA1:ACED:B00B:1234
FE80:0:0:0:202:B3FF:FE1E:8329            FE80:0000:0000:0000:0202:B3FF:FE1E:8329
FE80:1:2:3:202:B3FF:FE1E:8329            FE80:0001:0002:0003:0202:B3FF:FE1E:8329
FE80:3:1:3:202:B3FF:FE1E:8328            FE80:0003:0001:0003:0202:B3FF:FE1E:8328
FE80:3:1:3:202:B3FF:FE1E:8329            FE80:0003:0001:0003:0202:B3FF:FE1E:8329
FE80:3:2:3:202:B3FF:FE1E:8329            FE80:0003:0002:0003:0202:B3FF:FE1E:8329

8 rows selected.

SQL>spool off

i just put this together in the last hour (and taught myself objects at the same time) so im sure it can be improved upon. if i make updates i'll repost them here

回复收藏 0 原文

白云不回头 2024-10-23 13:45:15

我更喜欢以字符串形式存储 IP 地址，由 SYS_CONTEXT ('USERENV', 'IP_ADDRESS') 返回，

参考 SYS_CONTEXT 仅描述了默认返回值长度为 256 字节，并没有描述确切的“IP_ADDRESS”上下文的返回值大小。

在文档 Oracle 数据库和 IPv6 方向声明中描述：

Oracle Database 11g 第 2 版支持标准 IPv6 地址
RFC2732 指定的符号。 128位IP地址一般是
表示为 8 组 4 个十六进制数字，以“：”符号表示
组分隔符。每组中的前导零都被删除。为了
例如，1080:0:0:0:8:800:200C:417A 将是有效的 IPv6 地址。一
或多个连续的零字段可以选择压缩
“::”分隔符。例如，1080::8:800:200C:417A。

根据这篇笔记，我更喜欢将列 IP_ADDRESS varchar2(39) 设置为允许存储 8 个由 4 位数字组成的组以及这些组之间的 7 个分隔符。

回复收藏 0 原文

鼻尖触碰 2024-10-23 13:45:15

Oracle 文档确实指出 INTEGER 是 NUMBER(38) 的别名，但这可能是一个拼写错误，因为上面的段落指出：

NUMBER(p,s) 其中：p 是精度...Oracle 保证
数字的可移植性，精确度高达 20 个以 100 为基数的数字，
相当于 39 或 40 位十进制数字，具体取决于
小数点位置。

因此 NUMBER 可以存储 39 到 40 位数字，而 INTEGER 很可能是 NUMBER（最大精度）而不是 NUMBER(38) 的别名。这就是为什么提供的示例有效（如果将 INTEGER 更改为 NUMBER，它也有效）。

回复收藏 0 原文

随梦而飞# 2024-10-23 13:45:15

可能性有：

存储为字符串，即 VARCHAR2 （例如 1080::8:800:200c:417a）
存储为数值
- NUMBER 数据类型
- INTEGER 数据类型
存储为 RAW 值
- 一个 RAW 值，即分别针对 IPv4 或 IPv6 的 RAW(4) 或 RAW(16)
- 分别针对 IPv4 或 IPv6 4 x RAW(1) 或 8 x RAW(2)

我建议使用 RAW 值，因为

如果您使用字符串，则必须考虑 IPv6 的不同格式。
<前><代码>1080::8:800:200C:417A
1080::8:800:200c:417a
1080::8:800:32.12.65.122
1080:0:0:0:8:800:200C:417A
1080:0:0:0:0008:0800:200C:417A
1080:0000:0000:0000:0008:0800:200C:417A
都是同一 IPv6 IP 地址的合法表示。您的应用程序需要强制执行通用格式才能正确使用，例如在 WHERE 条件中使用。

如果不转换为人类可读的格式，

NUMBER/INTEGER 值是毫无意义的。您不能在 PL/SQL 中使用INTEGER 数据类型

i 整数 := 2**128-1; -- 即 ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff

-> ORA-06502: PL/SQL: 数字或值错误: 数字精度太大。

如果您必须使用子网划分，则不能使用函数BITAND - 它也仅支持最多 2^127 的数字
您可以使用UTL_RAW函数UTL_RAW.BIT_AND、UTL_RAW.BIT_COMPLMENT、UTL_RAW.BIT_OR 用于子网操作。
如果您必须处理真正大量的数据（我指的是数十亿行），将 IP 地址拆分为多个 RAW 值可能会有所帮助，即 4 x <代码>RAW(1) 或 8 x RAW(2)。此类列将预定用于位图索引，您将节省大量磁盘空间并提高性能。

The possibilities are:

Store as string, i.e. VARCHAR2 (example 1080::8:800:200c:417a)
Store as numeric value
- NUMBER data type
- INTEGER data type
Store as RAW value
- One RAW value, i.e. RAW(4) or RAW(16) for IPv4 or IPv6 respectively
- 4 x RAW(1) or 8 x RAW(2) for IPv4 or IPv6 respectively

I would recommend to use RAW values because

If you use strings then you have to consider different formats of IPv6.
```
1080::8:800:200C:417A
1080::8:800:200c:417a
1080::8:800:32.12.65.122
1080:0:0:0:8:800:200C:417A
1080:0:0:0:0008:0800:200C:417A
1080:0000:0000:0000:0008:0800:200C:417A
```
are all legal representations of the same IPv6 IP-Address. Your application needs to enforce a common format for proper usage, e.g. use in WHERE condition.

NUMBER/INTEGER values are senseless without conversion to human-readable format. You cannot use INTEGER data type in PL/SQL

i INTEGER := 2**128-1; -- i.e. ffff:ffff:ffff:ffff:ffff:ffff:ffff:ffff

-> ORA-06502: PL/SQL: numeric or value error: number precision too large.

In case you have to work with subnetting you cannot use function BITAND - it also supports numbers only up to 2^127
You can use UTL_RAW functions UTL_RAW.BIT_AND, UTL_RAW.BIT_COMPLEMENT, UTL_RAW.BIT_OR for subnet operations.
In case you have to deal with really big amount of data (I am talking about billions of rows) it might be beneficial to split the IP-Address into several RAW values, i.e. 4 x RAW(1) or 8 x RAW(2). Such columns would be predestinated for Bitmap-Indexes and you would save a lot of disc space and gain performance.