实验6：存储过程和存储函数（2学时）

一、实验目的
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表的定义

各列有如下数据：
‘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;