Oracle 12.1.0.2中字符串IP的CIDR范围查询方案咨询

我之前也处理过Oracle 12.1.0.2里没有原生IP类型的问题，正好可以给你几个实用的解决方案，帮你筛选指定CIDR块（比如10.1.0.0/10）内的IP地址：

方案1：Java自定义函数生成CIDR正则表达式 #

核心思路是把CIDR转换成对应的IP范围，再生成匹配该范围的正则表达式，然后在Oracle中通过Java存储过程调用这个逻辑。

首先写一个Java工具类，实现CIDR到正则的转换：

import java.util.regex.Pattern;

public class CidrIpRegexGenerator {
    public static String cidrToRegex(String cidr) {
        String[] cidrParts = cidr.split("/");
        String baseIp = cidrParts[0];
        int prefixLength = Integer.parseInt(cidrParts[1]);

        // 将基础IP转换为32位二进制字符串
        StringBuilder ipBinary = new StringBuilder();
        for (String octet : baseIp.split("\\.")) {
            String binaryOctet = Integer.toBinaryString(Integer.parseInt(octet));
            ipBinary.append(String.format("%8s", binaryOctet).replace(' ', '0'));
        }

        // 截取前缀位，剩余位用通配符逻辑处理
        String fixedPrefix = ipBinary.substring(0, prefixLength);
        String regexBinary = fixedPrefix + ".*";

        // 转换回四段式IP的正则表达式
        StringBuilder finalRegex = new StringBuilder();
        for (int i = 0; i < 4; i++) {
            int startIdx = i * 8;
            int endIdx = startIdx + 8;
            String octetBinary = regexBinary.substring(startIdx, endIdx);
            finalRegex.append(binaryOctetToRegex(octetBinary));
            if (i < 3) finalRegex.append("\\.");
        }
        return finalRegex.toString();
    }

    private static String binaryOctetToRegex(String binary) {
        // 计算当前八位的最小和最大十进制值
        int variableStart = binary.indexOf('*');
        variableStart = variableStart == -1 ? 8 : variableStart;
        String fixedPart = binary.substring(0, variableStart);
        int minOctet = Integer.parseInt(fixedPart + "00000000".substring(variableStart), 2);
        int maxOctet = Integer.parseInt(fixedPart + "11111111".substring(variableStart), 2);

        if (minOctet == maxOctet) {
            return String.valueOf(minOctet);
        } else if (minOctet == 0 && maxOctet == 255) {
            return "\\d{1,3}";
        } else {
            // 生成匹配min到max范围的正则（这里实现了简化版，可根据需求优化精度）
            return generateRangeRegex(minOctet, maxOctet);
        }
    }

    private static String generateRangeRegex(int min, int max) {
        StringBuilder regexSeg = new StringBuilder("(");
        boolean firstSegment = true;
        for (int i = min; i <= max; ) {
            int rangeEnd = i;
            while (rangeEnd + 1 <= max && rangeEnd + 1 == rangeEnd + 1) {
                rangeEnd++;
            }
            if (!firstSegment) regexSeg.append("|");
            if (i == rangeEnd) {
                regexSeg.append(i);
            } else {
                regexSeg.append(i).append("-").append(rangeEnd);
            }
            firstSegment = false;
            i = rangeEnd + 1;
        }
        regexSeg.append(")");
        // 替换范围符为正则匹配逻辑（示例简化，实际可优化为更精确的数字匹配）
        return regexSeg.toString().replace("-", "[0-9]");
    }
}

接下来把这个类部署到Oracle：

1. 用loadjava命令加载Java类（替换成你的数据库账号和路径）：

loadjava -user your_username/your_password@your_db CidrIpRegexGenerator.java

2. 创建PL/SQL包装函数：

CREATE OR REPLACE FUNCTION cidr_to_regex(p_cidr IN VARCHAR2) RETURN VARCHAR2
AS LANGUAGE JAVA
NAME 'CidrIpRegexGenerator.cidrToRegex(java.lang.String) return java.lang.String';
/

使用时直接调用：

SELECT ip_address
FROM your_ip_table
WHERE REGEXP_LIKE(ip_address, cidr_to_regex('10.1.0.0/10'));

方案2：借助Apache Commons Net库简化CIDR判断 #

如果不想自己写CIDR解析逻辑，可以用成熟的Java库Apache Commons Net，它的SubnetUtils类能直接解析CIDR并判断IP是否在范围内。

首先编写Java工具类：

import org.apache.commons.net.util.SubnetUtils;

public class CidrIpValidator {
    public static boolean isIpInCidr(String ip, String cidr) {
        SubnetUtils subnetUtils = new SubnetUtils(cidr);
        // 关闭严格模式，避免因IP格式小问题报错
        subnetUtils.setInclusiveHostCount(true);
        return subnetUtils.getInfo().isInRange(ip);
    }
}

部署步骤：

1. 加载Commons Net的jar包和你的工具类：

loadjava -user your_username/your_password@your_db commons-net-3.9.0.jar CidrIpValidator.java

2. 创建PL/SQL函数：

CREATE OR REPLACE FUNCTION is_ip_in_cidr(p_ip IN VARCHAR2, p_cidr IN VARCHAR2) RETURN NUMBER
AS LANGUAGE JAVA
NAME 'CidrIpValidator.isIpInCidr(java.lang.String, java.lang.String) return boolean';
/

使用示例：

SELECT ip_address
FROM your_ip_table
WHERE is_ip_in_cidr(ip_address, '10.1.0.0/10') = 1;

方案3：纯PL/SQL实现（无需Java依赖） #

如果不想引入Java环境，完全用PL/SQL也能实现，思路是把IP转换成十进制数字，再计算CIDR对应的起始和结束数字，最后通过范围比较筛选。

先创建IP转数字的函数：

CREATE OR REPLACE FUNCTION ip_to_number(p_ip IN VARCHAR2) RETURN NUMBER
IS
    oct1 NUMBER;
    oct2 NUMBER;
    oct3 NUMBER;
    oct4 NUMBER;
BEGIN
    SELECT TO_NUMBER(REGEXP_SUBSTR(p_ip, '\d+', 1, 1)),
           TO_NUMBER(REGEXP_SUBSTR(p_ip, '\d+', 1, 2)),
           TO_NUMBER(REGEXP_SUBSTR(p_ip, '\d+', 1, 3)),
           TO_NUMBER(REGEXP_SUBSTR(p_ip, '\d+', 1, 4))
    INTO oct1, oct2, oct3, oct4
    FROM DUAL;

    RETURN oct1 * POWER(2, 24) + oct2 * POWER(2, 16) + oct3 * POWER(2, 8) + oct4;
END;
/

再创建CIDR转范围的逻辑，直接在查询中使用：

WITH cidr_range AS (
    SELECT 
        -- 计算CIDR起始IP的数字值
        ip_to_number(REGEXP_SUBSTR('10.1.0.0/10', '^\d+\.\d+\.\d+\.\d+')) 
        & ~(POWER(2, 32 - TO_NUMBER(REGEXP_SUBSTR('10.1.0.0/10', '\d+$'))) - 1) AS start_ip_num,
        -- 计算CIDR结束IP的数字值
        ip_to_number(REGEXP_SUBSTR('10.1.0.0/10', '^\d+\.\d+\.\d+\.\d+')) 
        & ~(POWER(2, 32 - TO_NUMBER(REGEXP_SUBSTR('10.1.0.0/10', '\d+$'))) - 1) 
        + (POWER(2, 32 - TO_NUMBER(REGEXP_SUBSTR('10.1.0.0/10', '\d+$'))) - 1) AS end_ip_num
    FROM DUAL
)
SELECT ip_address
FROM your_ip_table, cidr_range
WHERE ip_to_number(ip_address) BETWEEN start_ip_num AND end_ip_num;

方案对比 #

• Java正则方案：灵活，适合需要正则匹配的场景，但需要维护Java代码和部署。
• Commons Net库方案：代码简洁，减少自己造轮子的错误，但需要引入外部jar包。
• 纯PL/SQL方案：无需额外依赖，部署简单，性能稳定，适合不想碰Java的场景。

内容的提问来源于stack exchange，提问作者MichaelB