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

一、实验目的：

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

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

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

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

二、验证性实验

1．某超市的食品管理的数据库的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 database foodinfo;

use foodinfo;

create table food(

foodid int(4) primary key not null unique auto_increment,

name varchar(20) not null,

company varchar(30) not null,

price float not null,

product_time year,

validity_time int(4),

address varchar(50)

);

desc food;

//机房

insert into food(name,company,price,product_time,validity_time,address)

values

('QQbingban','QQbingbanchang',2.5,'2008',3,'Beijing'),

('MNniunai','MNniunaichang',3.5,'2009',1,'Hebei'),

('EEguodong','EEguodongchang',1.5,'2007',2,'Beijing'),

('FFkafei','FFkafeichang',20,'2002',5,'Tianjin'),

('GGnaitang','GGnaitangchang',14,'2003',3,'Guangdong');

select * from food;

insert into food(name,company,price,product_time,validity_time,address)

values

('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,'广东');

select * from food;

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

代码如下：

delimiter &&

create procedure pfood_price_count(in price_info1 float,in price_info2 float,out count int)

reads sql data

begin

declare temp float;

declare match_price cursor for select price from food;

declare exit handler for not found close match_price;

set @sum=0;

select count(*) into count from food

where price>price_info1 and price

open match_price;

repeat

fetch match_price into temp;

if temp>price_info1 and temp

then set @sum=@sum+temp;

end if;

until 0 end repeat;

close match_price;

end &&

delimiter ;

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

call pfood_price_count(2,18,@count);

(3)使用SELECT语句查看结果。代码如下：

select @count,@sum;

其中，count是存储过程的输出结果：sum是存储过程中的变量，sum中的值满足足条件的单价的总和。

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

drop procedure pfood_price_count;

(5)使用存储函数来实现(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

end &&

delimiter ;

(6)调用存储函数

select pfood_price_count1(2,18);

(7)删除存储函数

drop function pfood_price_count1;

注：存储函数只能返回一个值，所以只实现了计算满足条件的食品种数。使用RETURN来将计算的食品种数返回回来。调用存储函数与调用MySQL内部函数的方式是一样的。

三、设计性实验

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

向teacherInfo表中插入记录：

1001,‘张龙’,‘男’,‘1984-11-08’,‘北京市昌平区’

1002,‘李梅’,‘女’,‘1970-01-21’,‘北京市海淀区’

1003,‘王一丰’,‘男’,‘1976-10-30’,‘北京市昌平区’

1004,‘赵六’,‘男’,‘1980-06-05’,‘北京市顺义区’

create database teacher;

use teacher;

create table teacherinfo(

num int(10) primary key not null unique,

name varchar(20) not null,

sex varchar(4) not null,

birthday datetime,

address varchar(50)

);

desc teacherinfo;

//机房

insert into teacherinfo(num,name,sex,birthday,address)

values

(1001,'ZL','M','1984-11-08','BeijingChangping'),

(1002,'LM','W','1970-01-21','BeijingHaiding'),

(1003,'WYF','M','1976-10-30','BeijingChangping'),

(1004,'ZN','M','1980-06-05','BeijingShunyi');

select * from teacherinfo;

insert into teacherinfo(num,name,sex,birthday,address)

values

(1001,'张龙','男','1984-11-08','北京市昌平区'),

(1002,'李梅','女','1970-01-21','北京市海淀区'),

(1003,'王一丰','男','1976-10-30','北京市昌平区'),

(1004,'赵六','男','1980-06-05','北京市顺义区');

select * from teacherinfo;

(1)创建名为teachernfo1的存储过程。要求：存储过程teachernfo1有3个参数。输入参数为teacherid和type，输出参数为info。满足：根据编号(teacherid)来查询teachernfo表中的记录。如果type的值为1时，将姓名(name)传给输出参数info;如果type的值为2时，将年龄传给输出参数info;如果type为其他值，则返回字符串“Error”。

//使用“DELIMITER &&”将SQL语句的结束符号变成&&

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 ;

(2)调用存储过程，参数值teacherid为1001，type为1。

call teacherinfo1(1001,1,@info);

select @info;

(3)使用DROP PRODECURE语句来删除存储过程

drop procedure teacherinfo1;

(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(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 ;

(5)使用SELECT语句调用teacherinfo2存储函数。

select teacherinfo2(1002,1);

select teacherinfo2(1002,2);

select teacherinfo2(1002,3);

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

drop function teacherinfo2;

四、观察与思考

(1) 什么时候适合通过创建存储过程来实现？

答：函数限制比较多，如不能用临时表，只能用表变量等，而存储过程的限制相对就比较少。

(2)功能相同的存储过程和存储函数的不同点有哪些？

答： 存储过程：可以使得对的管理、以及显示关于及其用户信息的工作容易得多。存储过程是 SQL 语句和可选控制流语句的预编译集合，以一个名称存储并作为一个单元处理。存储过程存储在数据库内，可由应用程序通过一个调用执行，而且允许用户声明变量、有条件执行以及其它强大的编程功能。存储过程可包含程序流、逻辑以及对数据库的查询。它们可以接受参数、输出参数、返回单个或多个结果集以及返回值。

(3)使用游标对于数据检索的好处有哪些？

答：在数据库中，游标是一个十分重要的概念。游标提供了一种对从表中检索出的数据进行操作的灵活手段，就本质而言，游标实际上是一种能从包括多条数据记录的结果集中每次提取一条记录的机制。游标总是与一条SQL 选择语句相关联因为游标由结果集(可以是零条、一条或由相关的选择语句检索出的多条记录)和结果集中指向特定记录的游标位置组成。当决定对结果集进行处理时，必须声明一个指向该结果集的游标。如果曾经用 C 语言写过对文件进行处理的程序，那么游标就像您打开文件所得到的文件句柄一样，只要文件打开成功，该文件句柄就可代表该文件。对于游标而言，其道理是相同的。可见游标能够实现按与传统程序读取平面文件类似的方式处理来自基础表的结果集，从而把表中数据以平面文件的形式呈现给程序。

我们知道关系数据库管理系统实质是面向集合的，在MS SQL SERVER 中并没有一种描述表中单一记录的表达形式，除非使用where 子句来限制只有一条记录被选中。因此我们必须借助于游标来进行面向单条记录的数据处理。由此可见，游标允许应用程序对查询语句select 返回的行结果集中每一行进行相同或不同的操作，而不是一次对整个结果集进行同一种操作；它还提供对基于游标位置而对表中数据进行删除或更新的能力；而且，正是游标把作为面向集合的数据库管理系统和面向行的程序设计两者联系起来，使两个数据处理方式能够进行沟通。

四、实验内容及程序代码

1、sch数据表的表结构如表7、1所示,sch表的数据如表7、2

表7、1 sch 表结构

字段名

数据类型

主键

外键

非空

唯一

自增

id

INT(10)

就是

否

就是

就是

否

n ame

VARCHAR(50)

否

否

就是

否

否

class

VARCHAR(50)

否

否

就是

否

否

表7、2 sch表的内容

id

n ame

class

1

李明

C1

2

小梅

C2

1)建表sch并插入数据。

LMLdDdse uidriyeu

mysql> 匚reate table sch(

> i d i nt(10) pri mary key,

-> neme varchar(50) not null,

-> class varchar(50) not nul1);

Query OK, 0 rows affected (0.42 sec)

mysql> i nsert into sch values (1, ' 明"「匚1?儿〔2「小梅',rc2 r); Query OK, 2 rows affected CO. 10 sec^)

Records: 2 Duplicates: 0 Warnings: 0

创建一个存储函数，用来统计表sch中的记录数。

delimiter $$

create PROCEDURE coun t_sch5(out size int)

BEGIN

select coun t(*) into size from sch;

END

$$

delimiter;

TOC \o "1-5" \h \z tzmTI 1 rz <1 li rTt_￡ <_ ki>

Qu ery OK w IL ir-ow aLiF"Fe

my s q 1 > sdaut住；

*—— *

I〒2已 I

鲁+

2 I

1 row i n 乞已七 €0. 0*0 s e

3)创建一个存储过程，通过调用存储函数的方法来获取表sch中的记录数与sch表中id的与。

delimiter $$

create PROCEDURE coun t_sch2(out s_a in t,out s_id int) BEGIN

select coun t(*) into s_a from sch;

select sum(id) as s_id from sch;

END

$$

delimiter;

++

I s_id I

44

I 3 I

1 row in set 〔0.01 sec)

2、创建一存储过程 in sert_stude nt_con dition_user,利用自定义错误触发条件定义

当插入学生的性别不就是“男”或“女”时结束存储过程，并提示“学生性别不正确”

表7、3 student表结构

字段名

数据类型

主键

外键

非空

唯一

自增

sno

Char(10)

就是

否

就是

就是

否

sn ame

VARCHAR(20)

否

否

就是

否

否

ssex

CHAR(2)

否

否

就是

否

否

Sage

smalli nt

否

否

就是

否

否

Sdept

VARCHAR(30)

否

否

就是

否

否

en terdate

datetime

否

否

就是

否

否

delimiter $$

create PROCEDURE insert_student_condition_user(insnoint,insname

VARCHAR(20),

in ssex varchar(2),i n sage in t,i n sdept varchar(30))

BEGIN

DECLARE continue han dler for sqlstate '42S02' select '学生性别不正确 '；

if ssex=' 男'or ssex=' 女'THEN

insert into stude nt values(s no,sn ame,ssex,sage,sdept);

end if;

end $$ delimiter;

她',20,'C1');call in sert_stude nt_con diti on _user(1,'pqw','

她',20,'C1');

drop PROCEDURE in sert_stude nt_co ndition_user;

信息

结栗1

畴兄

孚圭宓KIM

卜学主性別KU确

3、创建一存储过程update_student_borthyear,在学生表(表结构如表 7、3)中添加

字段” borthyear ”(出生年份),在存储过程中利用游标,通过学生年龄计算出出生年

份并修改表中对应字段。

create procedure update_stude nt_borthyear(i n s_no char(10))

BEGIN

declare s_age char(10);

declare state char(20);

declare cur cursor for select sage from stude nt;

declare exit han dler for 1329 set state='error';

ope n cur;

REPEAT

fetch cur into s_age;

update stude nt set borthyear=2018-s_age where sno=s_no;

un til state='error'

end repeat;

close cur;

END

srosnamessexugesdept

srosnamessex

uge

sdept

borthyea*

?

201818 pqwS

18

Ci

call update_stude nt_borthyear(201818);

mysql语句对于大小写不敏感，所以在写的时候要注意区分，整整找了半个小时的错误。