一、实验目的
1、理解存储过程和函数的概念；
2、掌握创建存储过程和函数的方法；
3、掌握执行存储过程和函数的方法；
4、掌握游标的定义、使用方法；
二、实验环境
装有软件MySQL5.7或更高版本的PC电脑一台；
三、实验步骤及实验要求
按照下面的实验内容要求完成作业，将作业结果的每一步截图粘贴到word文档中即可。每一个实验都必须编写实验报告，要求如下：
1、实验报告用word编写；
2、word文件命名的格式统一要求：为以“杨健”同学19号为例，他所做的第4次实验的实验报告应该命令为：“DB实验4-- 19号--杨健”（即格式是：实验序号—课内序号--姓名）；课内序号现在是一个人一门课一个号，同一个人在不同课序号会不同，回头我会将课程名单发到群里，自己查阅你自己在本门课中的序号。
3、实验报告用统一的封面，封面模板到时发给大家；
4、报告中截取每题主要步骤结果的截图、实验结果截图；
5、实验报告最后要加上实验总结，总结部分必须写出自己的切身体会；
6、实验报告如有雷同、抄袭现象，后果自负；
7、实验报告上交截止时间：上机后一周之内；
8、实验上交方式：由学委收集齐全后，统一交付老师：
四、实验内容
1、针对上述“简易图书管理系统”数据库BooksDB，完成下述功能：
1）编写不带参数的存储过程，完成如下功能：查看所有读者的姓名、可借本数、可借天数和已借书本数；
SELECT * FROM reader_3;
SELECT * FROM readertype_3;
DELIMITER $
CREATE PROCEDURE select_all_data()
BEGIN
SELECT r.rdName '姓名',rt.canLendQty '可借本数',rt.canLendDay '可借天数',r.rdBorrowQty '已借书本数'
FROM reader_3 r,readertype_3 rt
WHERE r.rdType = rt.rdType;
END $
DELIMITER ;
CALL select_all_data();
2）编写带输入参数和输出参数的存储过程，完成如下功能：输入读者的编号，输出该读者的姓名；
SELECT * FROM reader_3;
DELIMITER
CREATE PROCEDURE show_reader_name(IN rID CHAR(9),OUT rName VARCHAR(20))
BEGIN
SELECT rdName INTO rName FROM reader_3 WHERE rdID = rID;
END
DELIMITER ;
CALL show_reader_name('rd2017001',@rName)
SELECT @rName;
3）创建存储过程，实现读者借书功能；
SELECT * FROM book_3;
SELECT * FROM reader_3;
DELIMITER
CREATE PROCEDURE update_rend_book(IN rID CHAR(9),IN bName VARCHAR(50))
BEGIN
UPDATE reader_3
SET rdBorrowQty = rdBorrowQty + 1
WHERE rdID = rID;
UPDATE book_3
SET bkStatus = 0
WHERE bkName = bName;
END
DELIMITER ;
CALL update_rend_book('rd2017008','SQL2')
2、某超市的食品管理的数据库的Food表，Food表的定义如表所示，
Food表的定义
字段名 | 字段描述 | 数据类型 | 主键 | 外键 | 非空 | 唯一 | 自增 |
foodid | 食品编号 | INT(4) | 是 | 否 | 是 | 是 | 是 |
Name | 食品名称 | VARCHAR(20) | 否 | 否 | 是 | 否 | 否 |
Company | 生产厂商 | VARCHAR(30) | 否 | 否 | 是 | 否 | 否 |
Price | 价格（单位：元） | FLOAT | 否 | 否 | 是 | 否 | 否 |
Product_time | 生产年份 | YEAR | 否 | 否 | 否 | 否 | 否 |
Validity_time | 保质期（单位：年） | INT(4) | 否 | 否 | 否 | 否 | 否 |
address | 厂址 | VARCHAR(50) | 否 | 否 | 否 | 否 | 否 |
各列有如下数据：
'QQ饼干','QQ饼干厂',2.5,'2008',3,'北京'
'MN牛奶','MN牛奶厂',3.5,'2009',1,'河北'
'EE果冻','EE果冻厂',1.5,'2007',2,'北京'
'FF咖啡','FF咖啡厂',20,'2002',5,'天津'
'GG奶糖','GG奶糖',14,'2003',3,'广东'
create table Food
(
foodid int(4) unique key AUTO_INCREMENT,
`name` varchar(20) not null,
company varchar(30) not null,
price float,
product_time year,
validity_time int(4),
address varchar(50),
primary key (foodid)
);
-- 'QQ饼干','QQ饼干厂',2.5,'2008',3,'北京'
-- 'MN牛奶','MN牛奶厂',3.5,'2009',1,'河北'
-- 'EE果冻','EE果冻厂',1.5,'2007',2,'北京'
-- 'FF咖啡','FF咖啡厂',20,'2002',5,'天津'
-- 'GG奶糖','GG奶糖',14,'2003',3,'广东'
INSERT INTO Food VALUE(null,'QQ饼干','QQ饼干厂',2.5,'2008',3,'北京');
INSERT INTO Food VALUE(null,'MN牛奶','MN牛奶厂',3.5,'2009',1,'河北');
INSERT INTO Food VALUE(null,'EE果冻','EE果冻厂',1.5,'2007',2,'北京');
INSERT INTO Food VALUE(null,'FF咖啡','FF咖啡厂',20,'2002',5,'天津');
INSERT INTO Food VALUE(null,'GG奶糖','GG奶糖',14,'2003',3,'广东');
SELECT * FROM Food;
(1)在food表上创建名为Pfood_price_count的存储过程。其中存储过程Pfood_price_count有3个参数。输入参数为price_infol和price_info2，输出参数为count。存储过程的满足：查询food表中食品单价高于price_infol且低于price_info2的食品种数，然后由count参数来输出，并且计算满足条件的单价的总和。
SELECT * FROM Food;
DELIMITER
CREATE PROCEDURE Pfood_price_count(IN price_infol float,IN price_info2 float,OUT count float)
BEGIN
SELECT SUM(price) INTO count
FROM Food
WHERE price < price_info2
AND price > price_infol;
-- SELECT SUM(price)
-- FROM Food
-- WHERE price < 10.0 AND price > 2.0;
END
DELIMITER ;
CALL Pfood_price_count(2.0,10.0,@count);
SELECT @count;
(2)使用CALL语句来调用存储过程。查询价格在2至18之间的食品种数。
CALL Pfood_price_count(2,18,@count);
SELECT @count;
(3)使用SELECT语句查看结果。
SELECT @count;
(4)使用DROP语句删除存储过程Pfood_price_count。
DROP PROCEDURE Pfood_price_count;
(5)使用存储函数来实现（1）的要求。
DELIMITER
CREATE FUNCTION Pfood_price_count(price_infol float,price_info2 float)
RETURNS float
BEGIN
RETURN (
SELECT SUM(price)
FROM Food
WHERE price < price_info2
AND price > price_infol
);
END //
DELIMITER ;
(6)调用存储函数
SELECT Pfood_price_count(2,18);
(7)删除存储函数
DROP FUNCTION Pfood_price_count;
