Oracle计算日期差时出现'expression is of wrong type'错误求助

错误原因 #

你遇到的PLS-00382: expression is of wrong type错误，根源在于变量类型不匹配：

• final_date - initial_date的运算结果是数值类型（两个日期之间的天数差），但你将这个结果赋值给了声明为date类型的变量date_calc，类型不兼容导致报错。

修正方案1：修改PL/SQL变量类型 #

将date_calc的类型从date改为number，即可正确接收天数差值：

create table borrower(
    roll_no number,
    date_of_issue date,
    name_of_book varchar(20),
    status varchar(10)
);

insert into borrower values(1,to_date('02-JAN-2022'),'dbms','issued');
insert into borrower values(2,to_date('10-JAN-2022'),'cns','issued');
insert into borrower values(3,to_date('17-JAN-2022'),'spos','issued');
insert into borrower values(4,to_date('26-JAN-2022'),'toc','issued');

create table fine(
    roll_no number,
    current_date date,
    amount number
);

insert into fine values(1,to_date('14-FEB-2022'),null);
insert into fine values(2,to_date('14-FEB-2022'),null);
insert into fine values(3,to_date('14-FEB-2022'),null);
insert into fine values(4,to_date('14-FEB-2022'),null);

DECLARE
    roll_counter number:=1;
    initial_date date;
    final_date date;
    date_calc number; -- 此处修改为number类型

BEGIN
    loop
        select date_of_issue into initial_date from borrower where roll_no=roll_counter;
        select current_date into final_date from fine where  roll_no=roll_counter;
        date_calc:=final_date-initial_date;
        dbms_output.put_line('学号' || roll_counter || '的借阅天数差：' || date_calc);
        roll_counter:=roll_counter+1;
        exit when roll_counter>4;
    end loop;
END;
/

drop table borrower;
drop table fine;

修正方案2：用SQL直接计算（更高效） #

PL/SQL循环逐行查询效率较低，推荐直接通过SQL关联两张表计算天数差，代码更简洁高效：

create table borrower(
    roll_no number,
    date_of_issue date,
    name_of_book varchar(20),
    status varchar(10)
);

insert into borrower values(1,to_date('02-JAN-2022'),'dbms','issued');
insert into borrower values(2,to_date('10-JAN-2022'),'cns','issued');
insert into borrower values(3,to_date('17-JAN-2022'),'spos','issued');
insert into borrower values(4,to_date('26-JAN-2022'),'toc','issued');

create table fine(
    roll_no number,
    return_date date, -- 建议修改列名，避免与Oracle内置函数CURRENT_DATE混淆
    amount number
);

insert into fine values(1,to_date('14-FEB-2022'),null);
insert into fine values(2,to_date('14-FEB-2022'),null);
insert into fine values(3,to_date('14-FEB-2022'),null);
insert into fine values(4,to_date('14-FEB-2022'),null);

-- 直接计算并输出天数差
SELECT 
    b.roll_no,
    f.return_date - b.date_of_issue AS days_borrowed
FROM borrower b
JOIN fine f ON b.roll_no = f.roll_no;

drop table borrower;
drop table fine;

额外提示 #

• 避免使用CURRENT_DATE作为列名，它是Oracle的内置函数，容易引发语法歧义，建议改为return_date这类语义明确的名称。
• 当需要批量计算数据时，优先使用SQL语句而非PL/SQL循环，性能更优。

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