0
0

数据库系统原理及MySQL应用教程实验七存储过程与函数的创建管理

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

一、实验目的

  1. 理解存储过程和函数的概念。

  2. 掌握创建存储过程和函数的方法。

  3. 掌握执行存储过程和函数的方法。

  4. 掌握游标的定义、使用方法。

二、实验内容

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的功能和灵活性。

标签： 数据库 mysql windows
本文转载自: https://blog.csdn.net/Annzz123/article/details/131197031
版权归原作者 小孙同学1024 所有， 如有侵权，请联系我们删除。
登录后发布评论

“数据库系统原理及MySQL应用教程实验七存储过程与函数的创建管理”的评论:

还没有评论
关于作者
...
overfit同步小助手
文章同步
相关阅读

【Python】【持续项目】Python-安全项目搜集

泷羽sec学习day5-安全见闻6

随身 WiFi 通过 USB 连接路由器共享网络 扩展网络覆盖范围

如何将 Kubernetes 中的两个 Nginx Ingress 合并成一个：操作步骤与注意事项

单元测试使用JUnit 5

【大数据学习 | HBASE高级】hive操作hbase

MySQL Workbench 8.0 CE 中文汉化教程

文章导航