实验9 存储过程与函数的创建管理实验

一、实验目的：

1. 理解存储过程和函数的概念。
2. 掌握创建存储过程和函数的方法。
3. 掌握执行存储过程和函数的方法。
4. 掌握游标的定义、使用方法。

二、实验内容

1．某超市的食品管理的数据库的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,‘广东’

（1） 在food表上创建名为Pfood_price_count的存储过程。其中存储过程Pfood_price_count有3个参数。输入参数为price_infol和price_info2，输出参数为count。存储过程的满足：查询food表中食品单价高于price_infol且低于price_info2的食品种数，然后由count参数来输出，并且计算满足条件的单价的总和。

DELIMITER //
CREATE PROCEDURE Pfood_price_count(IN price_infol FLOAT, IN price_info2 FLOAT, OUT count INT)
BEGIN
  DECLARE sum_price FLOAT DEFAULT 0;
  SELECT COUNT(*), SUM(price) INTO count, sum_price FROM food WHERE price > price_infol AND price < price_info2;
  SELECT CONCAT('满足条件的食品种数为：', count) AS result;
  SELECT CONCAT('满足条件的单价总和为：', sum_price) AS result;
END //
DELIMITER ;

(2) 使用CALL语句来调用存储过程。查询价格在2至18之间的食品种数。代码如下：

CALL Pfood_price_count(2, 18, @count);

(3)使用DROP语句删除存储过程Pfood_price_count。代码如下：

DROP PROCEDURE Pfood_price_count; 

(4) 使用存储函数来实现（1）的要求。

DELIMITER //
CREATE FUNCTION Ffood_price_count(price_infol FLOAT, price_info2 FLOAT) RETURNS VARCHAR(100) DETERMINISTIC READS SQL DATA
BEGIN
  DECLARE count INT DEFAULT 0;
  DECLARE sum_price FLOAT DEFAULT 0;
  SELECT COUNT(*), SUM(price) INTO count, sum_price FROM food WHERE price > price_infol AND price < price_info2;
  RETURN CONCAT('满足条件的食品种数为：', count, '，满足条件的单价总和为：', sum_price);
END //
DELIMITER ;

（5）调用存储函数

SELECT Ffood_price_count(2, 18) AS result;

（6）删除存储函数

DROP FUNCTION Ffood_price_count;

2.学校教师管理数据库中的teacherInfo表，其表的定义如下表所示，请完成如下操作。

向teacherInfo表中插入记录：
1001,‘张龙’,‘男’,‘1984-11-08’,‘北京市昌平区’
1002,‘李梅’,‘女’,‘1970-01-21’,‘北京市海淀区’
1003,‘王一丰’,‘男’,‘1976-10-30’,‘北京市昌平区’
1004,‘赵六’,‘男’,‘1980-06-05’,‘北京市顺义区’

（1）创建名为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(50))
BEGIN
  DECLARE name VARCHAR(20);
  DECLARE birthday DATETIME;
  DECLARE age INT;
  SELECT name, birthday INTO name, birthday FROM teacherInfo WHERE num = teacherid;
  SET age = YEAR(CURDATE()) - YEAR(birthday);
  IF type = 1 THEN
    SET info = name;
  ELSEIF type = 2 THEN
    SET info = age;
  ELSE
    SET info = 'Error';
  END IF;
END //
DELIMITER ;

（2）调用存储过程，参数值teacher id为2，type为1。

CALL teachernfo1(2, 1, @info);
SELECT @info;

（3）使用DROP PRODECURE语句来删除存储过程

DROP PROCEDURE teachernfo1;

（4）创建名为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(50)
BEGIN
  DECLARE name VARCHAR(20);
  DECLARE birthday DATETIME;
  DECLARE age INT;
  SELECT name, birthday INTO name, birthday FROM teacherInfo WHERE num = teacherid;
  SET age = YEAR(CURDATE()) - YEAR(birthday);
  IF type = 1 THEN
    RETURN name;
  ELSEIF type = 2 THEN
    RETURN age;
  ELSE
    RETURN 'Error';
  END IF;
END //
DELIMITER ;

（5）使用SELECT语句调用teacherinfo2存储函数。

SELECT teacherinfo2(2, 1);

（6）使用DROP FUNCTION语句来删除teacherinfo2存储函数。

DROP FUNCTION teacherinfo2;

三、观察与思考

(1) 什么时候适合通过创建存储过程来实现？
mysql中适合通过创建存储过程来实现的情况包括：执行复杂的数据库操作，提高代码重用性；封装业务逻辑，简化客户端开发；减少网络通信量，提升性能；实施细粒度的权限控制；确保事务的一致性和完整性。

(2) 功能相同的存储过程和存储函数的不同点有哪些？
mysql中存储过程和存储函数的主要不同点在于调用方式、返回值和语句类型。存储过程可以执行复杂逻辑，支持输出参数和多种sql语句类型，适合执行修改数据的操作。而存储函数必须返回单个值，通常用于计算和返回数据，适用于select语句中，作为表达式的一部分。

(3)使用游标对于数据检索的好处有哪些？
使用游标进行数据检索的好处包括：可以逐行处理结果集中的数据，提供更细粒度的控制；适合复杂的数据处理逻辑，如多步骤计算或条件判断；减少内存占用，尤其当处理大量数据时；提高代码的灵活性和可读性。