Oracle APEX中验证穿梭列表值与表日期范围冲突的问题

问题背景 #

在Oracle APEX表单中，需验证冒号分隔的穿梭列表工单值，确保同一工单不会在重叠的日期范围内被重复分配到TKT_ASSIGN表。当前验证逻辑存在两个问题：

1. 编辑ID=4的记录并移除工单60后，仍触发验证错误；
2. 为ID=3添加工单20或70时，无日期范围重叠却仍触发错误。

涉及表结构：

• ACCT_CAL：存储月份日历及状态（C=已关闭，O=开放，F=未来）
• TKT_DETAILS：穿梭列表工单数据源
• TKT_ASSIGN：工单分配记录表，TKT_NUM为冒号分隔的工单值

修正后的PL/SQL验证函数 #

假设表单页面的开始日期项为P83310_START_DATE，结束日期项为P83310_END_DATE，记录ID项为P83310_ID，以下是返回错误文本的验证函数体：

DECLARE
    l_tkt_num VARCHAR2(4000) := :P83310_TKT_NUM;
    l_start_date DATE := :P83310_START_DATE;
    l_end_date DATE := :P83310_END_DATE;
    l_current_id NUMBER := :P83310_ID;
    l_tkt VARCHAR2(100);
    l_conflict_exists BOOLEAN := FALSE;
    l_conflict_tkts VARCHAR2(4000) := '';
BEGIN
    -- 处理空工单情况
    IF l_tkt_num IS NULL OR l_tkt_num = '' THEN
        RETURN NULL;
    END IF;

    -- 拆分冒号分隔的工单列表
    FOR rec IN (
        SELECT TRIM(regexp_substr(l_tkt_num, '[^:]+', 1, LEVEL)) AS tkt
        FROM dual
        CONNECT BY LEVEL <= regexp_count(l_tkt_num, ':') + 1
    ) LOOP
        l_tkt := rec.tkt;

        -- 检查是否存在其他记录的工单包含当前工单，且日期范围重叠
        SELECT CASE WHEN EXISTS (
            SELECT 1
            FROM TKT_ASSIGN ta
            WHERE ta.ID != l_current_id -- 排除当前编辑的记录
              AND REGEXP_LIKE(ta.TKT_NUM, '(^|:)(' || l_tkt || ')(:|$)') -- 匹配完整工单编号
              AND ta.START_DATE <= l_end_date -- 日期范围重叠判断
              AND ta.END_DATE >= l_start_date
        ) THEN 'Y' ELSE 'N' END
        INTO l_conflict_exists
        FROM dual;

        IF l_conflict_exists THEN
            l_conflict_tkts := l_conflict_tkts || ', ' || l_tkt;
        END IF;
    END LOOP;

    -- 返回错误信息
    IF l_conflict_tkts IS NOT NULL AND l_conflict_tkts != '' THEN
        RETURN '工单' || LTRIM(l_conflict_tkts, ', ') || '存在日期范围重叠的分配记录，请检查！';
    ELSE
        RETURN NULL;
    END IF;
EXCEPTION
    WHEN OTHERS THEN
        RETURN '验证出错：' || SQLERRM;
END;

关键修正点 #

1. 排除当前记录：加入ta.ID != l_current_id条件，编辑现有记录时，不会把自身原有的工单数据判定为冲突，解决第一个问题。
2. 精准工单匹配：使用REGEXP_LIKE正则匹配完整的工单编号，避免部分匹配导致的误判，比如工单20不会被误匹配到工单200。
3. 正确的日期重叠判断：采用标准区间重叠条件ta.START_DATE <= l_end_date AND ta.END_DATE >= l_start_date，确保只有真正重叠的日期范围才会被判定为冲突，解决第二个问题。
4. 拆分逻辑优化：使用CONNECT BY拆分冒号分隔的工单列表，确保每个工单都被单独校验。

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