任务描述
1、 在图书管理数据库中创建一个函数Count_voverdue,根据书号和借阅证号, 计算该用户的图书的超期天数,超期天数大于0,则返回实际天数,没有超期,返回整数0。
/* 1、 在图书管理数据库中创建一个函数Count_voverdue,
根据书号和借阅证号, 计算该用户的图书的超期天数,
超期天数大于0,则返回实际天数,没有超期,返回整数0。*/
use library;
drop FUNCTION if exists Count_voverdue;
delimiter //
CREATE FUNCTION Count_voverdue(vloanno varchar(16),vbookno char(9))
######### Begin #########
returns tinyint
Begin
declare difday tinyint;
declare term tinyint;
declare clNo tinyint;
select Users.classNo into clNo from Users where loanNo = vloanno;
select Class_User.term into term from Class_User where classNo = clNo;
select datediff(now(), borrowDate) into difday from Loan where Loan.loanNo = vloanno and Loan.bookNo = vbookno;
if(0 > difday - term)
then return 0;
elseif (0 <= difday - term)
then return difday - term;
end if;
######### End #########
end //
delimiter ;