山东省住房与建设厅网站首页,有名的wordpress主题商,南昌网站优化方案,最新的购物网站 开目录 1. 简述2. 功能代码2.1 创建存储函数#xff0c;根据图书编号查借阅人姓名#xff0c;并调用该函数查询‘ **小邓在森林** ’已借未还的图书情况#xff1b;2.2 创建存储函数#xff0c;计算某借阅人还能借阅的图书数目#xff0c;学生限额 5 本#xff0c;教师限额… 目录 1. 简述2. 功能代码2.1 创建存储函数根据图书编号查借阅人姓名并调用该函数查询‘ **小邓在森林** ’已借未还的图书情况2.2 创建存储函数计算某借阅人还能借阅的图书数目学生限额 5 本教师限额 10 本。2.3 创建存储函数查询某本图书逾期未还的时长并调用该函数显示所有逾期未归还图书的书名借阅人和逾期时长并按逾期时长排序2.4 创建存储函数查询某借阅人有几本逾期未还图书并调用该函数显示有逾期未归还图书的借阅人和未归还图书数目2.5 创建存储函数利用游标计算计算某借阅人逾期未还图书应缴纳的罚款逾期 30 日内罚款 1 元逾期 90 日内罚款 3 元逾期超过 90 日罚款 5 元。调用该函数显示所有应缴纳罚款的借阅人的姓名逾期罚款和电话 1. 简述
这篇文章将主要完成图书借阅系统的 4~8 题存储函数即
创建存储函数根据图书编号查借阅人姓名并调用该函数查询‘ 小邓在森林 ’已借未还的图书情况创建存储函数计算某借阅人还能借阅的图书数目学生限额 5 本教师限额 10 本。创建存储函数查询某本图书逾期未还的时长并调用该函数显示所有逾期未归还图书的书名借阅人和逾期时长并按逾期时长排序创建存储函数查询某借阅人有几本逾期未还图书并调用该函数显示有逾期未归还图书的借阅人和未归还图书数目创建存储函数利用游标计算计算某借阅人逾期未还图书应缴纳的罚款逾期 30 日内罚款 1 元逾期 90 日内罚款 3 元逾期超过 90 日罚款 5 元。调用该函数显示所有应缴纳罚款的借阅人的姓名逾期罚款和电话
2. 功能代码
注若读者并不是从第一篇文章一直到本篇文章执行代码的话即中途退出了 Navicat那么需要再次运行下述语句
# 开启此权限才能创建存储函数此权限在退出软件后会自动关闭
set global log_bin_trust_function_creatorsTRUE;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本书555
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图书借阅系统——触发器
