Oracle SQL：基于起止值校验REG_NUM是否存在的技术问题

现有TABLE1表结构及数据 #

REG_N生成规则 #

每行数据按以下规则生成连续的REG_N：

• 第1行：从CAL0001开始生成20个REG_N，范围为CAL0001至CAL0020
• 第2行：从CAL0031开始生成10个REG_N，范围为CAL0031至CAL0040
• 第3行：从CAL0299开始生成6个REG_N，范围为CAL0299至CAL0304

需求 #

给定新申请的REG_NUM起止范围（NEW_REG_NUM_FROM、NEW_REG_NUM_TO），校验该范围内是否有REG_NUM已存在于上述生成的集合中，返回PASS（无冲突）或FAIL（有冲突）。

预期结果 #

错误尝试的SQL #

用户最初尝试的SQL仅判断了现有起始REG_N是否在新申请范围内，无法覆盖区间重叠的场景：

SELECT count(*) FROM TABLE1 WHERE TABLE1.REG_N_START <= NEW_REG_NUM_TO AND TABLE1.REG_N_START >= NEW_REG_NUM_FROM;

正确的Oracle SQL实现 #

核心思路 #

将REG_NUM拆分为前缀和数字部分，转换为数值区间后，判断新申请的区间与现有生成的区间是否存在重叠（前缀需一致）：

1. 计算每行数据对应的REG_N数字起止范围
2. 解析新申请REG_NUM的前缀和数字起止范围
3. 判断同前缀下的区间是否重叠，存在重叠则返回FAIL，否则返回PASS

实现代码 #

SELECT CASE 
         WHEN EXISTS (
             SELECT 1 
             FROM TABLE1
             -- 计算当前行的REG_N前缀、起始数字、结束数字
             CROSS JOIN (SELECT 
                            PREF_REG_N AS curr_prefix,
                            TO_NUMBER(SUBSTR(REG_N_START, LENGTH(PREF_REG_N)+1)) AS curr_start_num,
                            TO_NUMBER(SUBSTR(REG_N_START, LENGTH(PREF_REG_N)+1)) + (BATCH_TO_N - BATCH_FROM_N) AS curr_end_num
                         FROM TABLE1) curr
             -- 解析新申请的REG_NUM前缀和数字范围
             CROSS JOIN (SELECT 
                            SUBSTR(:new_from, 1, INSTR(:new_from, REGEXP_SUBSTR(:new_from, '\d')) - 1) AS new_prefix,
                            TO_NUMBER(REGEXP_SUBSTR(:new_from, '\d+')) AS new_start_num,
                            TO_NUMBER(REGEXP_SUBSTR(:new_to, '\d+')) AS new_end_num
                         FROM DUAL) new_reg
             -- 前缀匹配且区间重叠则判定冲突
             WHERE curr.curr_prefix = new_reg.new_prefix
               AND curr.curr_start_num <= new_reg.new_end_num 
               AND curr.curr_end_num >= new_reg.new_start_num
         ) THEN 'FAIL'
         ELSE 'PASS'
       END AS RESULT
FROM DUAL;

说明 #

• 使用:new_from和:new_to作为绑定变量，替换为实际的新申请REG_NUM起止值
• 利用REGEXP_SUBSTR提取数字部分，兼容不同长度的前缀
• 区间重叠判断逻辑：现有区间起始 <= 新区间结束 且 现有区间结束 >= 新区间起始，只要存在任一满足条件的行，即判定为冲突

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