实验7：存储过程与函数的创建管理
一、实验目的
理解存储过程和函数的概念。
掌握创建存储过程和函数的方法。
掌握执行存储过程和函数的方法。
掌握游标的定义、使用方法。
二、实验内容
1.验证性实验：某超市的食品管理的数据库的Food表对其操作。
2.设计性试验：学校教师管理数据库中的teacherInfo表对其操作。
三、实验步骤与实验结果
（一）验证性实验
Food表的定义
字段名
字段描述
数据类型
主键
外键
非空
唯一
自增
foodid
食品编号
INT(4)
是
否
是
是
是
Name
食品名称
VARCHAR(20)
否
否
是
否
否
Company
生产厂商
VARCHAR(30)
否
否
是
否
否
Price
价格（单位：元）
FLOAT
否
否
是
否
否
Product_time
生产年份
YEAR
否
否
否
否
否
Validity_time
保质期（单位：年）
INT(4)
否
否
否
否
否
address
厂址
VARCHAR(50)
否
否
否
否
否
某超市的食品管理的数据库的Food表，Food表的定义如表所示，
1.创建food表：
CREATE TABLE food(
foodid INT(4) NOT NULL UNIQUE auto_increment PRIMARY KEY,
name VARCHAR(20) NOT NULL,
company VARCHAR(20) NOT NULL,
price FLOAT NOT NULL,
product_time YEAR,
validity_time INT(4),
address VARCHAR(50)
)ENGINE=InnoDBDEFAULTCHARSET=utf8;
2.向food插入数据
INSERT INTO food
VALUES
(1,'QQ饼干','QQ饼干厂',2.5,'2008',3,'北京'),
(2,'MN牛奶','MN牛奶厂',3.5,'2009',1,'河北'),
(3,'EE果冻','EE果冻厂',1.5,'2007',2,'北京'),
(4,'FF咖啡','FF咖啡厂',20,'2002',5,'天津'),
(5,'GG奶糖','GG奶糖',14,'2003',3,'广东');
3.在food表上创建名为Pfood_price_count的存储过程。其中存储过程Pfood_price_count有3个参数。输入参数为price_infol和price_info2，输出参数为count。存储过程的满足：查询food表中食品单价高于price_infol且低于price_info2的食品种数，然后由count参数来输出，并且计算满足条件的单价的总和。
代码如下：
//使用“DELIMITER &&”将SQL语句的结束符号变成&&
DELIMITER &&
CREATE PROCEDURE Pfood_price_count (IN price_info1 FLOAT,IN price_info2 FLOAT, OUT count INT )
READS SQL DATA
BEGIN
//定义变量temp
DECLARE temp FLOAT;
//定义游标match_price
DECLARE match_price CURSOR FOR SELECT price FROM food;
//定义条件处理。如果没有遇到关闭游标，就退出存储过程
DECLARE EXIT HANDLER FOR NOT FOUND CLOSE match_price;
//为临时变量sum赋值
SET @sum=0;
//用SELECT … INTO 语句来为输出变量count赋值
SELECT COUNT(*) INTO count FROM food
WHERE price>price_info1 AND price<price_info2 ;
//打开游标
OPEN match_price;
//执行循环
REPEAT
//使用游标match_price
FETCH match_price INTO temp;
//执行条件语句
IF temp>price_info1 AND temp<price_info2
THEN SET @sum=@sum+temp;
END IF;
//结束循环
UNTIL 0 END REPEAT;
//关闭游标
CLOSE match_price;
END &&
//将SQL语句的结束符号变成“;”
DELIMITER ;
4.使用CALL语句来调用存储过程。查询价格在2至18之间的食品种数。代码如下：
CALL Pfood_price_count(2,18,@count) ;
5.使用SELECT语句查看结果。代码如下：
SELECT @count,@sum;
其中，count是存储过程的输出结果：sum是存储过程中的变量，sum中的值满足足条件的单价的总和。
6.使用DROP语句删除存储过程Pfood_price_count。代码如下：
DROP PROCEDURE Pfood_price_count ;
7.使用存储函数来实现（1）的要求。存储函数的代码如下：
DELIMITER &&
CREATE FUNCTION Pfood_price_count1(price_info1 FLOAT,price_info2 FLOAT )
RETURNS INT READS SQL DATA
BEGIN
RETURN (SELECT COUNT(*) FROM food
WHERE price>price_info1 AND price<price_info2 );
END &&
DELIMITER ;
8.调用存储函数
SELECT Pfood_price_count1(2,18);
9.删除存储函数
DROP FUNCTION Pfood_price_count1;
注：存储函数只能返回一个值，所以只实现了计算满足条件的食品种数。使用RETURN来将计算的食品种数返回回来。调用存储函数与调用MySQL内部函数的方式是一样的。
（二）设计性试验
学校教师管理数据库中的teacherInfo表，其表的定义如下表所示，请完成如下操作。
字段名
字段描述
数据类型
主键
外键
非空
唯一
自增
num
教工号
INT(10)
是
否
是
是
否
name
姓名
VARCHAR(20)
否
否
是
否
否
sex
性别
VARCHAR(4)
否
否
是
否
否
birthday
出生日期
DATETIME
否
否
否
否
否
address
家庭住址
VARCHAR(50)
否
否
否
否
否
1.创建teacherinfo表
CREATE TABLE teacherinfo(
num INT(10) NOT NULL UNIQUE PRIMARY KEY,
name VARCHAR(20) NOT NULL,
sex VARCHAR(4) NOT NULL,
birthday DATETIME,
address VARCHAR(50)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
2.向teacherInfo表中插入记录：
INSERT INTO teacherinfo
VALUES
(1001,'张龙','男','1984-11-08','北京市昌平区'),
(1002,'李梅','女','1970-01-21','北京市海淀区'),
(1003,'王一丰','男','1976-10-30','北京市昌平区'),
(1004,'赵六','男','1980-06-05','北京市顺义区');
3.创建名为teachernfo1的存储过程。要求：存储过程teachernfo1有3个参数。输入参数为teacherid和type，输出参数为info。满足：根据编号(teacherid)来查询teachernfo表中的记录。如果type的值为1时，将姓名(name)传给输出参数info;如果type的值为2时，将年龄传给输出参数info;如果type为其他值，则返回字符串“Error”。
delimiter &&
create procedure teachernfo1(in teacherid int,in type int,out info varchar(20))
reads sql data
begin
case type
when 1 then
select name into info from teacherinfo where num=teacherid;
when 2 then
select year(now())-year(birthday) into info from teacher where num=teacherid;
else
select 'Error' into info;
end case;
end &&
delimiter ;
4.调用存储过程，参数值teacher id为2，type为1
USE student;
CALL teachernfo1(1001,1,@info);
5.使用DROP PRODECURE语句来删除存储过程
DROP PROCEDURE teachernfo1
6.创建名为teacherinfo2的存储函数。要求：存储过程teacherinfo2有两个参数：teacher id和type。满足：根据编号(teacher id)来查询teacher表中的记录。如果type的值是1时，则返回姓名(name)值；如果type的值是2时，则返回年龄；如果type为其他值，则返回字符串“Error”。
delimiter &&
create function teacherinfo2(teacherid int,type int)
returns varchar(20) reads sql data
begin
declare temp varchar(20);
if type=1
then
select name into temp from teacherinfo where num=teacherid;
elseif type=2
then
select year(now())-year(birthday) into temp from teacherinfo where num=teacherid;
else
set temp='Error';
end if;
return temp;
end &&
delimiter ;
7.使用SELECT语句调用teacherinfo2存储函数
select teacherinfo2(1002,1);
8.使用DROP FUNCTION语句来删除teacherinfo2存储函数。
drop function teacherinfo2;
四、观察与思考
1.什么时候适合通过创建存储过程来实现？
答：函数限制比较多，如不能用临时表，只能用表变量等，而存储过程的限制相对就比较少。
2.功能相同的存储过程和存储函数的不同点有哪些？
答： 存储过程：可以使得对的管理、以及显示关于及其用户信息的工作容易得多。存储过程是 SQL 语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其它强大的编程功能。存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。
3.使用游标对于数据检索的好处有哪些？
答:游标提供了一种对从表中检索出的数据进行操作的灵活手段游标总是与一条SQL 选择语句相关联因为游标由结果集和结果集中指向特定记录的游标位置组成。
五、实验要求
按要求完成，详细记录操作步骤，书写实验报告。所有实验环节均由每位学 生独立完成，认真记录操作过程，严禁抄袭他人实验结果。
六、实验总结
通过本次实验学习了SQL的存储过程与函数，课有效的避免重复地编写相同的SQL语句，方便开发人员，增强了SQL的功能和灵活性。
版权归原作者 小孙同学1024 所有， 如有侵权，请联系我们删除。