如何实现预订管控功能？含Oracle表与触发器优化需求

现有触发器问题梳理 #

1. 插入新预订时查询reservation表中未存在的reservation_id，会触发NO_DATA_FOUND异常。
2. 物品关联会员的判断逻辑错误，原代码错误覆盖了预订用户的member_id，且报错信息模糊。
3. 未实现预订时自动将isReserved设为1的核心需求。
4. 未校验同一物品在目标时间段内是否已有其他有效预订。

完善后的PreInsertreservation触发器 #

CREATE OR REPLACE TRIGGER PreInsertreservation
BEFORE INSERT ON reservation
FOR EACH ROW
DECLARE
    v_member_status NUMBER(1);
    v_item_owner_id NUMBER(11);
    v_overlap_count NUMBER;
BEGIN
    -- 1. 检查会员状态是否活跃
    SELECT status INTO v_member_status
    FROM member
    WHERE member_id = :NEW.member_id;

    IF v_member_status != 1 THEN
        RAISE_APPLICATION_ERROR(-20001, '错误：会员状态不活跃，无法预订');
    END IF;

    -- 2. 禁止物品所有者预订自己的物品（修正原逻辑）
    SELECT member_id INTO v_item_owner_id
    FROM item
    WHERE item_id = :NEW.item_id;

    IF v_item_owner_id = :NEW.member_id THEN
        RAISE_APPLICATION_ERROR(-20002, '错误：无法预订自己发布的物品');
    END IF;

    -- 3. 校验预订日期合法性：开始日期需早于结束日期，且时长不超过15天
    IF :NEW.start_date >= :NEW.end_date OR (:NEW.end_date - :NEW.start_date) > 15 THEN
        RAISE_APPLICATION_ERROR(-20003, '错误：开始日期必须早于结束日期，且预订时长不能超过15天');
    END IF;

    -- 4. 检查同一物品在目标时间段内是否已有其他有效预订（isReserved=1）
    SELECT COUNT(*) INTO v_overlap_count
    FROM reservation
    WHERE item_id = :NEW.item_id
      AND isReserved = 1
      AND (
          (:NEW.start_date BETWEEN start_date AND end_date)
          OR (:NEW.end_date BETWEEN start_date AND end_date)
          OR (start_date BETWEEN :NEW.start_date AND :NEW.end_date)
      );

    IF v_overlap_count > 0 THEN
        RAISE_APPLICATION_ERROR(-20004, '错误：该物品在指定时间段内已有其他用户预订');
    END IF;

    -- 5. 预订成功时自动将isReserved设为1
    :NEW.isReserved := 1;
    -- 同步会员状态到预订记录（保留原逻辑）
    :NEW.status := v_member_status;
END;
/

自动重置isReserved的定时任务 #

由于触发器无法实现延时执行逻辑，需创建定时任务，每天清理超过15天的失效预订：

-- 创建定时任务存储过程
CREATE OR REPLACE PROCEDURE ResetExpiredReservations
IS
BEGIN
    UPDATE reservation
    SET isReserved = 0
    WHERE isReserved = 1
      AND end_date <= SYSDATE - 15;
    COMMIT;
END;
/

-- 创建每日执行的定时任务
BEGIN
    DBMS_SCHEDULER.CREATE_JOB (
        job_name        => 'RESET_EXPIRED_RESERVATIONS_JOB',
        job_type        => 'STORED_PROCEDURE',
        job_action      => 'ResetExpiredReservations',
        start_date      => SYSTIMESTAMP,
        repeat_interval => 'FREQ=DAILY; BYHOUR=0; BYMINUTE=0; BYSECOND=0', -- 每日凌晨执行
        enabled         => TRUE,
        comments        => '每日重置超过15天的失效预订isReserved状态'
    );
END;
/

逻辑说明 #

1. 预订时设置isReserved=1：触发器在插入前自动将该字段赋值为1，确保预订生效。
2. 禁止同时间段重复预订：通过查询同一物品的有效预订记录，判断时间段是否重叠，若存在重叠则抛出错误。
3. 15天后自动重置：每日定时任务检查所有结束日期超过15天的有效预订，将其isReserved重置为0，释放物品预订权限。

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