PL/SQL触发器编译错误求助：图书借阅期限自动计算

数据表结构 #

• Person (id_pers, name, phone, address, birthdate)：birthdate 为 DATE 类型
• Book (book_id, title, nr_of_pages, copies, genre)
• Loan(book_id, loan_id, dateL, dateR, nr_of_days)：loan_id 关联 Person 的 id_pers；dateL 是借阅日期，dateR 是归还日期，nr_of_days 为允许借阅的天数

需求目标 #

插入借阅记录时，自动计算nr_of_days：

• 借阅人年龄18-25岁：按每日30页计算
• 26-65岁：按每日20页计算
• 65岁以上：按每日40页计算

原错误代码 #

create or replace TRIGGER Update_Days
BEFORE INSERT ON Loan
FOR EACH ROW
BEGIN
    DECLARE age INT;
    SELECT DATEDIFF(YEAR, birthdate, CURRENT_DATE) INTO age
    FROM Person
    WHERE id_pers = NEW.loan_id;

    IF age BETWEEN 18 AND 25 THEN
        SET NEW.nr_of_days = NEW.nr_of_pages / 30;
    ELSEIF age BETWEEN 26 AND 65 THEN
        SET NEW.nr_of_days = NEW.nr_of_pages / 20;
    ELSE
        SET NEW.nr_of_days = NEW.nr_of_pages / 40;
    END IF;
END;

原代码问题分析 #

1. 语法结构错误：PL/SQL中DECLARE块必须放在BEGIN之前，不能嵌套在BEGIN内部
2. 字段引用错误：Loan表不存在nr_of_pages字段，必须从关联的Book表通过book_id查询获取
3. 年龄计算函数错误：Oracle不支持DATEDIFF(YEAR, ...)语法，正确的周岁计算方式是TRUNC(MONTHS_BETWEEN(SYSDATE, birthdate)/12)
4. 绑定变量格式错误：PL/SQL触发器中引用:NEW变量必须加冒号前缀
5. 结果未取整：借阅天数应为整数，直接除法会得到小数，需用CEIL向上取整

修正后的触发器代码 #

CREATE OR REPLACE TRIGGER Update_Days
BEFORE INSERT ON Loan
FOR EACH ROW
DECLARE
    v_age INT;
    v_pages INT;
BEGIN
    -- 获取借阅人周岁年龄
    SELECT TRUNC(MONTHS_BETWEEN(SYSDATE, birthdate)/12)
    INTO v_age
    FROM Person
    WHERE id_pers = :NEW.loan_id;

    -- 获取对应书籍的页数
    SELECT nr_of_pages
    INTO v_pages
    FROM Book
    WHERE book_id = :NEW.book_id;

    -- 计算并赋值借阅天数
    IF v_age BETWEEN 18 AND 25 THEN
        :NEW.nr_of_days := CEIL(v_pages / 30);
    ELSIF v_age BETWEEN 26 AND 65 THEN
        :NEW.nr_of_days := CEIL(v_pages / 20);
    ELSE
        :NEW.nr_of_days := CEIL(v_pages / 40);
    END IF;
END;
/

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