实验十：存储过程
一、实验目的
通过实验使学生加深对数据完整性的理解，学会创建和使用存储过程。 二、原理解析 1、存储过程概述
存储过程是指，在一个执行规划中预先定义并编译好的一组Transact-SQL语句。这些语句在一个名称下存储并作为一个单元进行处理。存储过程是编写数据库代码中的重要成份。它们可以是构成任何一个由数据库支撑的应用程序的代码，并且能被这些应用程序中的任何一个调用。
存储过程能在查询分析器（Query Analyzer）窗口运行。它们可以被批处理文件调用，也可以由Access、Visual Basic或者其他编程语言的用户接口程序来调用。可以像其他子例程调用一样，把参数传给存储过程。存储过程会返回数据值、状态代码以及错误信息，这样用户就可以做出适当的响应。存储过程也能调用其他存储过程。存储过程能够在本地计算机或远程服务器上运行。在执行系统内部任务时，用户可以指示SQL Server自动运行某种存储过程。
存储过程类型包括系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程。通过存储过程的创建、执行、修改和删除操作，初步了解存储过程的类型，掌握利用存储过程进行程序设计的方法技巧。 2、存储过程的类型
系统存储过程、用户自定义存储过程、扩展存储过程。 3、在SQL中创建存储过程的语法 创建存储过程的语法为：
CREATE PROC [ EDURE ] procedure_name [ ; number ] [ { @parameter data_type }
[ VARYING ] [ = default ] [ OUTPUT ] ] [ ,...n ] [ WITH
{ RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION } ] [ FOR REPLICATION ]
29
AS sql_statement [ ...n ]
4、存储过程的执行 执行存储过程： [ [ EXEC [ UTE ] ] {
[ @return_status = ]
{ procedure_name [ ;number ] | @procedure_name_var }
[ [ @parameter = ] { value | @variable [ OUTPUT ] | [ DEFAULT ] } [ ,...n ]
[ WITH RECOMPILE ] 5、存储过程中的参数
输入参数和输出参数（OUTPUT）。 三、实验内容
（1） 创建不带参数的存储过程。 （2） 创建带输入参数的存储过程。 （3） 创建带输出参数的存储过程。 四、实验步骤
1、在学生成绩库中创建存储过程，存储过程名为proc1，要求实现如下功能：根据学生学
号，查询该学生的选课情况，其中包括该学生学号、姓名、性别、课程号、课程名、成绩和学分等。
CREATE PROCEDURE proc1
@sno char(8) --@sno是参数，字符型，表示某个学生的学号 AS
SELECT xscj.学号, xsqk.姓名, xsqk.性别, xscj.课程号, xskc.课程名, xscj.成绩, xscj.学分 FROM xscj, xsqk, xskc WHERE xscj.学号 = xsqk.学号 AND xscj.课程号 = xskc.课程号 AND xsqk.学号 = @sno GO
30
2、现有一学生学号为“02020101”，要求通过调用上例中所建的名为proc1的存储过程，实现显示该学生的选课情况列表。 EXECUTE proc1 ?02020101? 或
EXECUTE proc1 @sno = ?02020101? 或
EXEC proc1 @sno =?02020101?
3、在学生成绩库中创建一个名为proc2的存储过程，产生一个学生选课情况列表，其中包括学号、姓名、性别、课程号、课程名、成绩、学分等，并调用该存储过程查看学生的选课情况。
CREATE PROCEDURE proc2 AS
SELECT xscj.学号, xsqk.姓名, xsqk.性别, xscj.课程号, xskc.课程名, xscj.成绩, xscj.学分 FROM xscj, xsqk, xskc WHERE xscj.学号 = xsqk.学号 AND xscj.课程号 = xskc.课程号 GO EXEC proc2
4、在学生成绩库中创建一个名为proc3的存储过程，产生某门课程的选课学生情况列表，其中包括课程号、课程名、学号、姓名、系别、班级、专业、性别等。要求输入某门课程的课程号，得到上述信息。 CREATE PROCEDURE proc3
@cno char (3) --@cno是参数，字符型，表示某门课的课程号 AS
SELECT xscj.课程号, xskc.课程名,xscj.学号, xsqk.姓名, xsqk.系别,xsqk.班级, xsqk.专业,xsqk.性别 FROM xscj, xsqk, xskc WHERE xscj.学号 = xsqk.学号 AND xscj.课程号 = xskc.课程号 AND xscj.课程号 = @cno
31
GO
5、利用上述存储过程查询课程号为“101”的课程的选课学生情况列表。 Exec Proc3 ?101?
6、定义存储过程proc4，要求实现输入学生学号，根据该学生所选课程的平均成绩显示提示信息，即如果平均成绩在60分以上，显示“此学生综合成绩合格！”，否则显示“此学生综合成绩不合格！”。
CREATE PROCEDURE proc4
@sno char(8) --@sno是参数，字符型，表示学生的学号 AS
DECLARE @savg tinyint --声明变量@savg，用于存放学生平均成绩 SELECT @savg = avg(成绩) FROM xscj
WHERE xscj.学号 = @sno --变量@savg存放学号为@sno的学生平均成绩 IF @savg>=60 --进行平均成绩的判断 PRINT ?此学生综合成绩合格！? ELSE
PRINT ?此学生综合成绩不合格！? GO
7、利用已创建的存储过程查询学号为“02020101”同学的平均成绩情况。 Exec proc4 ?02020101?
8、定义存储过程proc5，要求实现输入学号，输出该学生所选课程的平均成绩，要求用输入参数和输出参数实现。 CREATE PROCEDURE proc5
@sno char(10),@avgscore int OUTPUT AS
IF @sno=NULL
BEGIN
PRINT ?学号不能为空！? RETURN END
SELECT @avgscore=AVG(成绩)
32
from xscj
WHERE 学号=@sno RETURN
9、利用创建的存储过程查询学号为“02020101”的同学的平均成绩。 DECLARE @avgscore int
EXECUTE proc5 ?02020101?,@avgscore OUTPUT SELECT ?His average score is :?,@avgscore
10、定义存储过程proc6，要求实现如下功能：根据某学生的学号查询该生的已修学分，如果已修学分>9，则将已修学分加2分；如果已修学生小于9，则将已修学分加4分。 create proc proc6
@sno char(10),@xxf tinyint output as
declare @yxf tinyint
select @yxf=总学分from dbo.xsqk where 学号=@sno if @yxf>9 update dbo.xsqk set 总学分=总学分+2 where 学号=@sno else
update dbo.xsqk set 总学分=总学分+4 where 学号=@sno
select @xxf=总学分from dbo.xsqk where 学号=@sno
11、利用已经创建的存储过程查询学号为“02020101”的同学修改后的已修学分。 declare @xxf tinyint
exec proc6 '02020101',@xxf output select @Xxf
33