任务描述
1、 在图书管理数据库中创建一个存储过程P_IsLoan,在用户借书之前,判断该用户能否借书。用户借阅证号和借阅图书的书号作为输入参数,输出参数vflag为整数,有以下几种状态值:
(1)vflag为0,表示“该用户可以借阅此书”;
(2)vflag为1,表示此书已经预约或已经借出或不出借;
(3)vflag为2,表示此书不对该类用户出借,每种图书只对允许的用户类别出借(具体参见上方Books表的字段说明);
(4)vflag为3,表示“已达到借书上限”;
(5)vflag为4,表示“此用户存在超期未还图书”。
/* 1、 在图书管理数据库中创建一个存储过程P_IsLoan,在用户借书之前,判断该用户能否借书。用户借阅证号和借阅图书的书号作为输入参数,
输出参数vflag为整数,有以下几种状态值:
(1)vflag为0,表示“该用户可以借阅此书”;
(2)vflag为1,表示此书已经预约或已经借出或不出借;
(3)vflag为2,表示此书不对该类用户出借,每种图书只对允许的用户类别出借(具体参见上方Books表的字段说明);
(4)vflag为3,表示“已达到借书上限”;
(5)vflag为4,表示“此用户存在超期未还图书”。*/
use library;
DROP PROCEDURE IF EXISTS P_IsLoan;
delimiter //
create procedure P_IsLoan(vloanno varchar(16),vbookno char(9),out vflag tinyint)
begin
######### Begin #########
declare bstatus char(1);
declare ctr_no tinyint;
declare clNo tinyint;
declare cnt tinyint;
declare canBro tinyint;
declare term tinyint;
declare difday tinyint;
select Users.classNo into clNo from Users where loanNo = vloanno;
select Class_User.term into term from Class_User where classNo = clNo;
select max(datediff(now(), borrowDate)) into difday from Loan where Loan.loanNo = vloanno;
select ceilingNum into canBro from Class_User where Class_User.classNo = clNo;
select count(*) into cnt from Loan where loanNo = vloanno;
select Books.bstatus into bstatus from Books where vbookno = bookNo;
select Books.ctr_no into ctr_no from Books where vbookno = bookNo;
if bstatus = '0' then set vflag = 0;
end if;
if bstatus = '1' || bstatus = '2' then set vflag = 1;
end if;
if (ctr_no | clNo) != ctr_no then set vflag = 2;
end if;
if difday > term then set vflag = 4;
end if;
if cnt >= canBro then set vflag = 3;
end if;
######### End #########
end//
delimiter ;