将CIDR地址转换为子网掩码和网络地址

--plsql ip_calc by ku1gun
with a as
(
select
  '12.34.56.78/20' ip
from dual
),b as
(
select
  utl_raw.concat(utl_raw.substr(utl_raw.cast_from_binary_integer(to_number(regexp_substr(ip,'[^.]+',1,1)),2),1,1),utl_raw.substr(utl_raw.cast_from_binary_integer(to_number(regexp_substr(ip,'[^.]+',1,2)),2),1,1),utl_raw.substr(utl_raw.cast_from_binary_integer(to_number(regexp_substr(ip,'[^.]+',1,3)),2),1,1),utl_raw.substr(utl_raw.cast_from_binary_integer(to_number(regexp_substr(replace(ip,'/','.'),'[^.]+',1,4)),2),1,1)) ip,
  utl_raw.cast_from_binary_integer(power(2,32-to_number(regexp_substr(ip,'[^/]+',1,2)))-1) wildcard,
  utl_raw.bit_xor(utl_raw.cast_from_binary_integer(-1),utl_raw.cast_from_binary_integer(power(2,32-to_number(regexp_substr(ip,'[^/]+',1,2)))-1)) mask
from a
),c as
(
select
  utl_raw.bit_and(ip,mask) network_address,
  utl_raw.bit_or(utl_raw.bit_and(ip,mask),wildcard) broadcast_address,
  utl_raw.cast_from_binary_integer(utl_raw.cast_to_binary_integer(utl_raw.bit_and(ip,mask))+1) first_adress,
  utl_raw.cast_from_binary_integer(utl_raw.cast_to_binary_integer(utl_raw.bit_or(utl_raw.bit_and(ip,mask),wildcard))-1) last_adress,
  utl_raw.cast_from_binary_integer(utl_raw.cast_to_binary_integer(utl_raw.bit_and(ip,mask))+level) ip_address
from b
connect by level<utl_raw.cast_to_binary_integer(utl_raw.bit_or(utl_raw.bit_and(ip,mask),wildcard))-utl_raw.cast_to_binary_integer(utl_raw.bit_and(ip,mask))
)
select
  to_char(utl_raw.cast_to_binary_integer(utl_raw.substr(network_address,1,1)))||'.'||to_char(utl_raw.cast_to_binary_integer(utl_raw.substr(network_address,2,1)))||'.'||to_char(utl_raw.cast_to_binary_integer(utl_raw.substr(network_address,3,1)))||'.'||to_char(utl_raw.cast_to_binary_integer(utl_raw.substr(network_address,4,1))) network_address,
  to_char(utl_raw.cast_to_binary_integer(utl_raw.substr(broadcast_address,1,1)))||'.'||to_char(utl_raw.cast_to_binary_integer(utl_raw.substr(broadcast_address,2,1)))||'.'||to_char(utl_raw.cast_to_binary_integer(utl_raw.substr(broadcast_address,3,1)))||'.'||to_char(utl_raw.cast_to_binary_integer(utl_raw.substr(broadcast_address,4,1))) broadcast_address,
  to_char(utl_raw.cast_to_binary_integer(utl_raw.substr(first_adress,1,1)))||'.'||to_char(utl_raw.cast_to_binary_integer(utl_raw.substr(first_adress,2,1)))||'.'||to_char(utl_raw.cast_to_binary_integer(utl_raw.substr(first_adress,3,1)))||'.'||to_char(utl_raw.cast_to_binary_integer(utl_raw.substr(first_adress,4,1))) first_adress,
  to_char(utl_raw.cast_to_binary_integer(utl_raw.substr(last_adress,1,1)))||'.'||to_char(utl_raw.cast_to_binary_integer(utl_raw.substr(last_adress,2,1)))||'.'||to_char(utl_raw.cast_to_binary_integer(utl_raw.substr(last_adress,3,1)))||'.'||to_char(utl_raw.cast_to_binary_integer(utl_raw.substr(last_adress,4,1))) last_adress,
  to_char(utl_raw.cast_to_binary_integer(utl_raw.substr(ip_address,1,1)))||'.'||to_char(utl_raw.cast_to_binary_integer(utl_raw.substr(ip_address,2,1)))||'.'||to_char(utl_raw.cast_to_binary_integer(utl_raw.substr(ip_address,3,1)))||'.'||to_char(utl_raw.cast_to_binary_integer(utl_raw.substr(ip_address,4,1))) ip_address
from c
;