【数据库——MySQL（实战项目1）】（3）图书借阅系统——存储函数

函数"/>

【数据库——MySQL（实战项目1）】（3）图书借阅系统——存储函数

目录

• 1. 简述
• 2. 功能代码
• • 2.1 创建存储函数，根据图书编号查借阅人姓名，并调用该函数查询‘ **小邓在森林** ’已借未还的图书情况；
  • 2.2 创建存储函数，计算某借阅人还能借阅的图书数目，学生限额 `5` 本，教师限额 `10` 本。
  • 2.3 创建存储函数，查询某本图书逾期未还的时长，并调用该函数显示所有逾期未归还图书的书名，借阅人和逾期时长并按逾期时长排序；
  • 2.4 创建存储函数，查询某借阅人有几本逾期未还图书，并调用该函数显示有逾期未归还图书的借阅人和未归还图书数目；
  • 2.5 创建存储函数，利用游标计算计算某借阅人逾期未还图书应缴纳的罚款，逾期 `30` 日内罚款 `1` 元，逾期 `90` 日内罚款 `3` 元，逾期超过 `90` 日罚款 `5` 元。调用该函数显示所有应缴纳罚款的借阅人的姓名，逾期罚款和电话；

1. 简述

这篇文章将主要完成图书借阅系统的 4~8 题（存储函数），即：

4. 创建存储函数，根据图书编号查借阅人姓名，并调用该函数查询‘ 小邓在森林 ’已借未还的图书情况；
5. 创建存储函数，计算某借阅人还能借阅的图书数目，学生限额 5 本，教师限额 10 本。
6. 创建存储函数，查询某本图书逾期未还的时长，并调用该函数显示所有逾期未归还图书的书名，借阅人和逾期时长并按逾期时长排序；
7. 创建存储函数，查询某借阅人有几本逾期未还图书，并调用该函数显示有逾期未归还图书的借阅人和未归还图书数目；
8. 创建存储函数，利用游标计算计算某借阅人逾期未还图书应缴纳的罚款，逾期 30 日内罚款 1 元，逾期 90 日内罚款 3 元，逾期超过 90 日罚款 5 元。调用该函数显示所有应缴纳罚款的借阅人的姓名，逾期罚款和电话；

2. 功能代码

注：若读者并不是从第一篇文章一直到本篇文章执行代码的话（即中途退出了 Navicat），那么需要再次运行下述语句：

# 开启此权限才能创建存储函数（此权限在退出软件后会自动关闭）
set global log_bin_trust_function_creators=TRUE;

2.1 创建存储函数，根据图书编号查借阅人姓名，并调用该函数查询‘ 小邓在森林 ’已借未还的图书情况；

drop FUNCTION if exists f_bookid2borrowername;
delimiter $
create FUNCTION f_bookid2borrowername(bookid char(12))returns varchar(20) # 返回借阅人姓名
begindeclare borrowername varchar(20);select borrower.`name` into borrowernamefrom borrower join information on borrower.id = information.borroweridjoin book on book.id = information.bookidwhere book.id = bookid and information.returnDateReality IS NULL;return borrowername;
end$
delimiter ;

检测存储函数是否正确：

set @bookid = 101102512651;
select f_bookid2borrowername(@bookid) as 借阅人姓名;

测试结果如下：

调用该函数查询‘ 小邓在森林 ’已借未还的图书情况：

# 调用存储函数查询‘小邓在森林’已借未还的图书情况
select book.id as 图书编号, book.`name` as 书名, information.leadDate as 借出日期, information.returnDate as 应还日期, if(TO_DAYS(NOW()) - TO_DAYS(information.returnDate)>=0, TO_DAYS(NOW()) - TO_DAYS(information.returnDate), '未逾期') as '逾期时长(天)',borrower.id as 证件号, borrower.`name` as 姓名
from book join information on book.id = information.bookidjoin borrower on borrower.id = information.borrowerid
where f_bookid2borrowername(book.id) = '小邓在森林' and information.returnDateReality IS NULL;

注：逾期时长是会每天自动增长的，因此读者运行的逾期时长结果可能和我的结果不同~

2.2 创建存储函数，计算某借阅人还能借阅的图书数目，学生限额 5 本，教师限额 10 本。

drop FUNCTION if exists f_ReBorrowTimes;
delimiter $
create FUNCTION f_ReBorrowTimes(borrowerid char(13))returns int # 返回还能借阅的图书数目
begindeclare ReBorrowTimes int;	# 还能借阅的图书数目declare Overdue int;				# 已借没还书籍的数量select COUNT(*) into Overduefrom borrower join information on borrower.id = information.borroweridjoin book on book.id = information.bookidwhere returnDateReality IS NULL and borrower.id = borrowerid;select if(borrower.category='教师',10 - Overdue, 5 - Overdue) into ReBorrowTimesfrom borrowerwhere borrower.id = borrowerid;return ReBorrowTimes;
end$
delimiter ;

检测存储函数是否正确：

# 教师(没有未还图书)
set @borrowerid = 1000000000001;
select f_ReBorrowTimes(@borrowerid) as 还能借阅的图书数目;
# 学生(已借未还3本书,只能再借2本书)
set @borrowerid = 2020312011047;
select f_ReBorrowTimes(@borrowerid) as 还能借阅的图书数目;

测试结果如下：

2.3 创建存储函数，查询某本图书逾期未还的时长，并调用该函数显示所有逾期未归还图书的书名，借阅人和逾期时长并按逾期时长排序；

drop FUNCTION if exists f_OverdueBook;
delimiter $
create FUNCTION f_OverdueBook(bookid char(12))returns int # 返回某本图书逾期未还的时长(天)
begindeclare OverdueTimes int;	# 图书逾期未还的时长(天)select TO_DAYS(NOW()) - TO_DAYS(information.returnDate) into OverdueTimesfrom book join information on book.id = information.bookidjoin borrower on borrower.id = information.borroweridWHERE book.id = bookid AND returnDateReality IS NULL AND TO_DAYS(NOW()) - TO_DAYS(information.returnDate) > 0;return OverdueTimes;
end$
delimiter ;

检测存储函数是否正确：【此部分检测代码待完成全部功能后再运行】

# 逾期图书(即超过了30天未还的图书)
set @bookid = 101102512651;
select if(f_OverdueBook(@bookid) is null, '未逾期', f_OverdueBook(@bookid)) as '图书逾期未还的时长(天)';
# 未逾期图书(即未超过30天未还的图书)
# 先借一本书来测试未逾期图书
call p_InsertLeadInfo(2020312011047,101101699412);
set @bookid = 101101699412;
select if(f_OverdueBook(@bookid) is null, '未逾期', f_OverdueBook(@bookid)) as '图书逾期未还的时长(天)';
# 将图书归还
call p_UpdateLeadInfo(2020312011047,101101699412);
# 在架上图书(未借出图书)
set @bookid = 101101699410;
select if(f_OverdueBook(@bookid) is null, '未逾期', f_OverdueBook(@bookid)) as '图书逾期未还的时长(天)';

测试结果如下：

注：图片虽然没显示还书过程，即 call p_UpdateLeadInfo(2020312011047,101101699412);，但是我另外自己执行了，所以读者就按照我给的代码执行即可，图片只是参考~

调用存储函数显示所有逾期未归还图书的书名，借阅人和逾期时长并按逾期时长排序：

select book.`name` as 书名, borrower.id as 证件号, borrower.`name` as 姓名, f_OverdueBook(book.id) as 逾期天数
from book join information on book.id = information.bookidjoin borrower on borrower.id = information.borrowerid
WHERE f_OverdueBook(book.id) IS NOT NULL AND returnDateReality IS NULL
ORDER BY f_OverdueBook(book.id) desc;

2.4 创建存储函数，查询某借阅人有几本逾期未还图书，并调用该函数显示有逾期未归还图书的借阅人和未归还图书数目；

drop FUNCTION if exists f_OverdueBorrower;
delimiter $
create FUNCTION f_OverdueBorrower(borrowerid char(13))returns int # 返回某借阅人逾期未还图书数量
begindeclare OverdueCount int;	# 逾期未还图书数量select count(*) into OverdueCountfrom book join information on book.id = information.bookidjoin borrower on borrower.id = information.borroweridWHERE borrower.id = borrowerid AND returnDateReality IS NULL AND TO_DAYS(NOW()) - TO_DAYS(information.returnDate) > 0;return OverdueCount;
end$
delimiter ;

检测存储函数是否正确：【此部分检测代码待完成全部功能后再运行】

# 教师(已借未还且逾期)
set @borrowerid = 1000000000002;
select f_OverdueBorrower(@borrowerid) as '逾期未还图书数(本)';
# 学生(已借未还未逾期)
# 先借一本书来测试未逾期图书
call p_InsertLeadInfo(2018210210205,101101699412);
set @borrowerid = 2018210210205;
select f_OverdueBorrower(@borrowerid) as '逾期未还图书数(本)';
# 将图书归还
call p_UpdateLeadInfo(2018210210205,101101699412);
# 学生(已借未还且逾期)
set @borrowerid = 2020312011047;
select f_OverdueBorrower(@borrowerid) as '逾期未还图书数(本)';
# 学生(未借过书)
set @borrowerid = 2023513266557;
select f_OverdueBorrower(@borrowerid) as '逾期未还图书数(本)';

测试结果如下：

调用存储函数显示有逾期未归还图书的借阅人和未归还图书数目：

select borrower.id as 证件号, borrower.`name` as 姓名, f_OverdueBorrower(borrower.id) as 逾期未还图书数量
from book join information on book.id = information.bookidjoin borrower on borrower.id = information.borrowerid
WHERE f_OverdueBorrower(borrower.id) > 0
GROUP BY borrower.id;

2.5 创建存储函数，利用游标计算计算某借阅人逾期未还图书应缴纳的罚款，逾期 30 日内罚款 1 元，逾期 90 日内罚款 3 元，逾期超过 90 日罚款 5 元。调用该函数显示所有应缴纳罚款的借阅人的姓名，逾期罚款和电话；

drop FUNCTION if exists f_fine;
delimiter $
create FUNCTION f_fine(borrowerid char(13))returns int # 返回某借阅人逾期未还图书数量
begindeclare fine int DEFAULT(0);	# 逾期总罚款declare fine_per int default(0);	# 每本书罚款(临时)declare borrower_id char(13);	# 逾期借阅人证件号declare OverdueDay int;	# 逾期天数declare f int DEFAULT 1;# 设置游标declare c_finesum cursor forselect borrower.id, TO_DAYS(NOW()) - TO_DAYS(information.returnDate)from book join information on book.id = information.bookidjoin borrower on borrower.id = information.borroweridWHERE borrower.id = borrowerid AND returnDateReality IS NULL AND (TO_DAYS(NOW()) - TO_DAYS(information.returnDate)) > 0;# 错误处理declare continue handler for not foundset f = 0;open c_finesum;		# 打开游标while f = 1 dofetch c_finesum into borrower_id, OverdueDay;		# 读取游标if OverdueDay < 0 thenset fine_per = 0;elseif OverdueDay <= 30 thenset fine_per = 1;elseif OverdueDay <= 90 thenset fine_per = 3;elseif OverdueDay > 90 thenset fine_per = 5;end if;set fine = fine + fine_per;end while;close c_finesum;		# 关闭游标set fine = fine - fine_per;		# 由于continue会导致程序多运行一次，所以要减去最后一次扣多的钱！return fine;
end$
delimiter ;

检测存储函数是否正确：【此部分检测代码待完成全部功能后再运行】

# 逾期3本书（5+5+5）
set @borrowerid = 2020312011047;
select f_fine(@borrowerid) as '应缴纳的罚款(元)';
# 逾期1本书（5）
set @borrowerid = 1000000000002;
select f_fine(@borrowerid) as '应缴纳的罚款(元)';
# 未逾期
# 先借一本书来测试未逾期图书
call p_InsertLeadInfo(2018210210205,101101699412);
set @borrowerid = 2018210210205;
select f_fine(@borrowerid) as '应缴纳的罚款(元)';
# 将图书归还
call p_UpdateLeadInfo(2018210210205,101101699412);

测试结果如下：

调用存储函数显示所有应缴纳罚款的借阅人的姓名，逾期罚款和电话(附加邮箱)：

select borrower.id as 证件号, borrower.`name` as 姓名, f_fine(borrower.id) as '逾期罚款(元)', borrower.tel as 电话, borrower.email as 邮箱
from borrower
where f_fine(borrower.id) != 0;

注：由于这里的逾期数据每本书都是大于 90 天的，所以都是 5 元往上加，读者可自行修改或者添加数据，来查看不同逾期的罚款情况~

上一篇文章：【数据库——MySQL（实战项目1）】（2）图书借阅系统——数据库测试、视图以及存储过程
下一篇文章：【数据库——MySQL（实战项目1）】（4）图书借阅系统——触发器