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

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;
1
2

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

drop FUNCTION if exists f_bookid2borrowername;
delimiter $
create FUNCTION f_bookid2borrowername(bookid char(12))
		returns varchar(20) # 返回借阅人姓名
begin
	declare borrowername varchar(20);
	
	select borrower.`name` into borrowername
	from borrower join information on borrower.id = information.borrowerid
								join book on book.id = information.bookid
	where book.id = bookid and information.returnDateReality IS NULL;
	
	return borrowername;
end$
delimiter ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

检测存储函数是否正确：

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

测试结果如下：

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

# 调用存储函数查询‘小邓在森林’已借未还的图书情况
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.bookid
					join borrower on borrower.id = information.borrowerid
where f_bookid2borrowername(book.id) = '小邓在森林' and information.returnDateReality IS NULL;
1
2
3
4
5

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

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

drop FUNCTION if exists f_ReBorrowTimes;
delimiter $
create FUNCTION f_ReBorrowTimes(borrowerid char(13))
		returns int # 返回还能借阅的图书数目
begin
	declare ReBorrowTimes int;	# 还能借阅的图书数目
	declare Overdue int;				# 已借没还书籍的数量
	
	select COUNT(*) into Overdue
	from borrower join information on borrower.id = information.borrowerid
								join book on book.id = information.bookid
	where returnDateReality IS NULL and borrower.id = borrowerid;

	select if(borrower.category='教师',10 - Overdue, 5 - Overdue) into ReBorrowTimes
	from borrower
	where borrower.id = borrowerid;
	
	return ReBorrowTimes;
end$
delimiter ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20

检测存储函数是否正确：

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

测试结果如下：

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

drop FUNCTION if exists f_OverdueBook;
delimiter $
create FUNCTION f_OverdueBook(bookid char(12))
		returns int # 返回某本图书逾期未还的时长(天)
begin
	declare OverdueTimes int;	# 图书逾期未还的时长(天)
	select TO_DAYS(NOW()) - TO_DAYS(information.returnDate) into OverdueTimes
	from book join information on book.id = information.bookid
						join borrower on borrower.id = information.borrowerid
	WHERE book.id = bookid AND returnDateReality IS NULL AND TO_DAYS(NOW()) - TO_DAYS(information.returnDate) > 0;
	return OverdueTimes;
end$
delimiter ;
1
2
3
4
5
6
7
8
9
10
11
12
13

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

# 逾期图书(即超过了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 '图书逾期未还的时长(天)';
1
2
3
4
5
6
7
8
9
10
11
12
13

测试结果如下：

注：图片虽然没显示还书过程，即 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.bookid
					join 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;
1
2
3
4
5

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

drop FUNCTION if exists f_OverdueBorrower;
delimiter $
create FUNCTION f_OverdueBorrower(borrowerid char(13))
		returns int # 返回某借阅人逾期未还图书数量
begin
	declare OverdueCount int;	# 逾期未还图书数量
	
	select count(*) into OverdueCount
	from book join information on book.id = information.bookid
						join borrower on borrower.id = information.borrowerid
	WHERE borrower.id = borrowerid AND returnDateReality IS NULL AND TO_DAYS(NOW()) - TO_DAYS(information.returnDate) > 0;
	
	return OverdueCount;
end$
delimiter ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

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

# 教师(已借未还且逾期)
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 '逾期未还图书数(本)';
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16

测试结果如下：

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

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

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

drop FUNCTION if exists f_fine;
delimiter $
create FUNCTION f_fine(borrowerid char(13))
		returns int # 返回某借阅人逾期未还图书数量
begin
	declare 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 for
			select borrower.id, TO_DAYS(NOW()) - TO_DAYS(information.returnDate)
			from book join information on book.id = information.bookid
								join borrower on borrower.id = information.borrowerid
			WHERE borrower.id = borrowerid AND returnDateReality IS NULL AND (TO_DAYS(NOW()) - TO_DAYS(information.returnDate)) > 0;
	
	# 错误处理
	declare continue handler for not found
			set f = 0;
	
	open c_finesum;		# 打开游标
		while f = 1 do
				fetch c_finesum into borrower_id, OverdueDay;		# 读取游标
				
				if OverdueDay < 0 then
						set fine_per = 0;
				elseif OverdueDay <= 30 then
						set fine_per = 1;
				elseif OverdueDay <= 90 then
						set fine_per = 3;
				elseif OverdueDay > 90 then
						set 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 ;
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44

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

# 逾期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);
1
2
3
4
5
6
7
8
9
10
11
12
13

测试结果如下：

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

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;
1
2
3

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

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