数据库实验七：存储过程及应用

数据库实验七：存储过程及应用

一、实验目的与要求：

1．实验目的

1．理解存储过程的概念。

2．掌握存储过程的使用方法。

2．实验要求

1．建立如下的存储过程（基于前面实验建立的表和插入的数据，并为每个存储过程设计返回的状态值）：

（1）按要求设计完成如下功能的存储过程。

①查询平均分数在x到y范围内的学生信息。

说明：

●该存储过程有两个参数；

●要求查询的学生信息包括学号、姓名、院系名称和平均分数。

②更新操作，以学号、课程编号和考试成绩作参数更新指定学生和课程的考

试成绩，并返回该学生的平均成绩。

③更新操作，以学号、课程编号和考试成绩作参数更新指定学生和课程的考

试成绩，并返回该学生的平均成绩。

（2）自行再分别设计一个完成查询和完成操作功能的存储过程（在实验报告中要准确描述功能需求）。

（3）在客户端以存储过程和输入SQL语句的方式分别执行相同的查询或操作，比较使用和不使用存储过程的区别。

二、实验内容

1、实验原理

1．创建存储过程的SQL语句的一般格式是：

CREATE PROC[edure] [schema_name].procedure-name [; number ]

[@parameter data-type [VARYING] [= default ][OUT | OUTPUT],…] AS sql-statement

2．执行存储过程的语句是：

[EXECute]

[@<返回状态码> =]

<存储过程名>

[[@<参数>=]｛<值>|@<变量>｝…]

或

EXECUTE [@return_status=] [schema_name].procedure-name [; number ] [@parameter =]{value | variable [ OUTPUT]}[,…n]

2、实验步骤与结果

（1）调出SQL Server2005软件的用户界面，进入SQL Server Management Studio。

（2）输入自己编好的程序。

（3）检查已输入的程序正确与否。

（4）运行程序，并分析运行结果是否合理和正确。在运行时要注意当输入不同的数据时所得到的结果是否正确。

（5）输出程序清单和运行结果。

实验程序：

create procedure Stu

@minmark smallint,@maxmark smallint

AS

select学生.学号,学生.姓名,院系.名称,平均成绩

from学生join院系

on学生.院系=院系.编号

where平均成绩>=@minmark and平均成绩<=@maxmark

execute Stu80,100

create procedure Renew

@StuNum char(8)output,@CouNum char(8),@Mark smallint

AS

update选课

set成绩=@Mark

where学号=@StuNum and课程编号=@CouNum declare@A VGM smallint

select@A VGM=A VG(成绩)

from选课

where学号=@StuNum

return@A VGM

declare@avg smallint

execute@avg=Renew003,01,91

print'更新后平均成绩：'+str(@avg,6)

查询所有任课老师的工资,并将制定课程的的责任教师的工资改为

create procedure Sal

@CouNum char(8)

AS

update教师

set工资=100

where教师编号=(select责任教师

from课程

where课程编号=@CouNum)

select教师编号,工资

from教师

where教师编号in(select责任教师

from课程)

exec Sal102

三、实验分析与小结：

（实验过程中的问题分析、产生的原因以及解决方法；实验结果分析；有待优化思路）

1、实验过程中的问题分析、产生的原因以及解决方法。

在使用cursor数据类型的参数的存储过程时，T-SQL存储过程只能将cursor数

据类型用于OUTPUT参数，并且需要配合关键字VARYING一起使用。在存储的过

程中，实践返回的是状态，所以只能是数值。

四、其它

思考题：

1、为什么要使用存储过程？

（1）在数据库服务器中只有首次对存储过程中的命令进行编译，以后直接调用无需编译，加快执行速度。

（2）只提供给用户参数和结果，存储过程对查询过程封装和加密，简化用户使用，防止非法修改。

（3）存储过程可以用于降低网络流量，存储过程代码直接存储于数据库中，所以不会产生大量sql语句的代码流量。

（4）可以只赋给用户执行存储过程的权利，而不给用户操作相应数据表的权利，这样可以有效防止注入攻击。

（5）维护性高，更新存储过程通常比更改、测试以及重新部署程序集需要较少的时间和精力。

得分（百分制）