56,940
社区成员




call library_borrow(1,1234567)
set @id = 1
set @isbn =1234567
call `library_borrow`(@id,@isbn)
CREATE DEFINER=`root`@`localhost` PROCEDURE `library_borrow`(IN id int, IN isbn char(30))
begin
DECLARE lid int default 0; #新插入的记录id
DECLARE uname char(30) default'';#用户名
DECLARE bname char(30) default'';#书名
DECLARE sdate datetime default now();
DECLARE edate datetime default date_add(sdate,INTERVAL '1' MONTH);
set @uname = (select user_name from web_user where user_id=@id);
set @bname = (select book_name from web_book where book_isbn=@isbn);
set @sdate = now();
set @edate = date_add(sdate,INTERVAL '1' MONTH);
#把借书记录插入表格
insert into web_borrow_record(borrow_user_id,borrow_user_name,borrow_book_isbn,borrow_book_name,
borrow_start_date,borrow_end_date,borrow_return_status)
values(@id,@uname,@isbn,@bname,@sdate,@edate,'false');
#获取刚插入数据的id
set @lid = LAST_INSERT_ID();
#用户借书限额减一
update web_user
set user_book_limit = user_book_limit - 1
where user_id = @id;
#库存减一
update web_stock
set stock_book_remain = stock_book_remain - 1
where stock_book_isbn = @isbn;
#返回结果
select * from web_borrow_record where borrow_id = @lid;
end