在oracle中生成IP地址

Question

我有一个数据库，其中一个字段具有IP地址的值。默认值为12.123.60.165。根据这些默认值，我必须生成IP地址。最大值为256.256.256.256。

这是我的功能，还不能满足我的需要。

/* Formatted on 9/26/2017 9:44:51 AM (QP5 v5.115.810.9015) */
SET SERVEROUTPUT ON

DECLARE
   IP    INTEGER;
   IP1   INTEGER;
   IP2   INTEGER;
   IP3   INTEGER;
   IP4   INTEGER;
   IPI   INTEGER := 1;
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;

   DBMS_OUTPUT.PUT_LINE (IP1 || '.' || IP2 || '.' || IP3 || '.' || IP4);
END;
/

任何帮助都是非常感谢的。

Answer 1

要生成所有IPv4 4的一组，可以使用16条记录和交叉连接的交叉连接和CTE。但我还不确定你到底想要什么。

with cte (A)  as (
SELECT 0 from dual UNION ALL
SELECT 1 from dual UNION ALL
SELECT 2 from dual UNION ALL
SELECT 3 from dual UNION ALL
SELECT 4 from dual UNION ALL
SELECT 5 from dual UNION ALL
SELECT 6 from dual UNION ALL
SELECT 7 from dual UNION ALL
SELECT 8 from dual UNION ALL
SELECT 9 from dual UNION ALL
SELECT 10 from dual UNION ALL
SELECT 11 from dual UNION ALL
SELECT 12 from dual UNION ALL
SELECT 13 from dual UNION ALL
SELECT 14 from dual UNION ALL
SELECT 15 from dual), 
--Get data set for 0 to 255 numbers. 
Get256Range as (SELECT  row_number() over (order by A.A)-1 seg
    FROM CTE A
    CROSS JOIN cte B)
--Now cross join the 256 4 times for each class of Ip.

SELECT A.Seg ||'.'|| B.Seg||'.'|| C.Seg||'.'||D.Seg as IPAddress
from Get256Range A
CROSS JOIN Get256Range B
CROSS JOIN Get256Range C
CROSS JOIN Get256Range D
--Not sure what part of the "set" you need but one could change these values 
--Limited for sizing; but one could create a table of all IPv4 addresses...
WHERE A.seg = 12
  and B.seg = 123;

如果需要的话你可以包括前导零..。

lpad(A.Seg,3,'0') ||'.'|| lpad(B.Seg,3,'0')||'.'|| lpad(C.Seg,3,'0')||'.'|| lpad(D.Seg,3,'0') IpAddressWithLeadZero

这样就可以根据特定的范围进行排序或筛选。

所以只返回那些大于012.123.060.165的(但不是最有效的)

WHERE lpad(A.Seg,3,'0') ||'.'|| lpad(B.Seg,3,'0')||'.'|| lpad(C.Seg,3,'0')||'.'|| lpad(D.Seg,3,'0') >='012.123.060.165'

可能更有效率：

WHERE (A.Seg = 12 and B.Seg = 123 and C.Seg = 60 and D.Seg > 165) OR (A.Seg = 12 and B.Seg = 123 and C.Seg > 60) OR (A.Seg = 12 and B.Seg > 123) OR (A.Seg > 12)

Answer 2

可以使用dbms_random包生成随机值。你需要这个包裹的特权。例如：

SQL>  select trunc(dbms_random.value(12,256) ) ip1,
  2        trunc(dbms_random.value(123,256) ) ip2,
  3        trunc(dbms_random.value(60,256) ) ip3,
  4        trunc(dbms_random.value(165,256) ) ip4
  5*  from dual

   IP1        IP2        IP3        IP4
---------- ---------- ---------- ----------
   156        163         78        231

或者在循环中生成。就像这样：

declare
  ip1 int;
  ip2 int;
  cnt int := 0;
begin
  for i in 12..256 loop
    ip1 := i;
    for j in 123..256 loop
      ip2 := j; 
    end loop;
  end loop;
end;
/