sqlserver数据库操作 实验九 存储过程和触发器 讲义

实验九 存储过程和触发器

一、 实验目的

1． 通过对常用系统存储过程的使用，了解存储过程的类型；

2． 通过创建和执行存储过程，了解存储过程的基本概念，掌握使用存储过程的操作技巧和方法；

3． 通过创建触发器，了解触发器的基本概念，理解触发器的功能，掌握使用触发器的操作技巧和方法；

二、 实验要求

1． 实验前做好上机实验的准备，针对实验内容，认真复习与本次实验有关的知识，完成实验内容的预习准备工作；

2． 能认真独立完成实验内容；

3． 实验后做好实验总结，根据实验情况完成总结报告。

三、 实训操作及流程分析

1、创建与执行存储过程

（1）用T-SQL语句创建存储过程

可以使用CREATE PROCEDURE语句来创建存储过程。

例9.1 在学生成绩库中创建一个名为sp_StuCourseInfo的存储过程，产生一个学生选课情况列表，其中包括学号、姓名、性别、课程号、课程名、成绩、学分等。

分析：要产生一个学生选课情况列表，包含学号、姓名、性别、课程号、课程名、成绩、学分等字段信息，其Select语句如下：

SELECT as 学号, name as 姓名, age as 性别, as 课程号, Cname as 课程名,

Grade as 成绩, Ccredit as 学分

FROM Student, SC, Course

WHERE = AND =

那么，完整的创建存储过程proc_8_2的T-SQL语句如下：

CREATE PROCEDURE sp_StuCourseInfo

AS

SELECT as 学号, name as 姓名, age as 性别, as 课程号, Cname as 课程名,

Grade as 成绩, Ccredit as 学分

FROM Student, SC, Course

WHERE = AND =

GO

上述T-SQL语句执行后，在学生成绩库中就存在了名为sp_StuCourseInfo的存储过程。

（2）用企业管理器创建存储过程

例9.2 用企业管理器实现例9.2所述存储过程的创建。

具体操作如下：

 打开企业管理器，展开服务器组，展开服务器，展开“数据库”文件夹。再展开“学生成绩库”，右击“存储过程”，如图1 用企业管理器创建存储过程。

 单击“新建存储过程”命令，见图2新建存储过程过程窗口。

 在该窗口的文本框中，编写存储过程的语句。如图3编写存储过程语句。

 单击“确定”命令。可以看到在企业管理器中，已存在名为proc_8_3的存储过程。

图1 用企业管理器创建存储过程

图2 新建存储过程窗口

图3 编写存储过程语句

存储过程创建后，可以通过在查询分析器的查询窗口中键入存储过程名来执行，也可以使用EXECUTE语句来执行存储过程。

（3）指定参数

存储过程通过其参数与调用程序通讯。当程序执行存储过程时，可通过存储过程的参数向该存储过程传递值。这些值可作为Transact-SQL编程语言中的标准变量使用。存储过程也可通过 OUTPUT 参数将值返回至调用程序。一个存储过程可有多达2100个参数，每个参数都有名称、数据类型、方向和默认值。

例9.3 在学生成绩库中创建一个名为sp_CourseInfo的存储过程，产生某门课程的选课学生情况列表，其中包括课程号、课程名、学号、姓名、系别、性别等。要求输入某门课程的课程号，得到上述信息。

分析：根据题意，需要在存储过程中使用参数，然后通过参数实现向存储过程传递值，得到所需的信息。

完整的T-SQL语句如下：

CREATE PROCEDURE sp_CourseInfo

@cno nvarchar（10） --@cno是参数，字符型，表示某门课的课程号

AS

SELECT ,,,,,

FROM Course,SC,Student

WHERE = and =

AND = @cno

GO

在查询分析器的查询窗口中输入上述T-SQL语句，执行后，即在“Student”中创建了存储过程sp_CourseInfo。

如果要产生课程号为“0001”的课程的选课学生情况列表，可以调用已创建的存储过程

sp_CourseInfo实现。具体操作如下：

在查询分析器的查询窗口中，输入语句：

sp_CourseInfo ‘0001’

或

EXEC sp_CourseInfo ‘0001’

或

EXEC sp_CourseInfo @cno=’0001’

或

sp_CourseInfo @cno=’0001’

执行后，在结果窗格中可以看到相应信息。

例9.4 在学生成绩库中，定义存储过程sp_StuGradebySno，要求实现输入学生学号，根据该学生所选课程的平均成绩显示提示信息，即如果平均成绩在60分以上，显示“此学生综合成绩合格！”，否则显示“此学生综合成绩不合格！”。

在查询分析器的查询窗口中，输入语句：

CREATE PROCEDURE sp_StuGradebySno

@sno nvarchar(10) --@sno是参数，字符型，表示学生的学号

AS

DECLARE @savg int --声明变量@savg，用于存放学生平均成绩

SELECT @savg = avg(Grade) FROM SC

WHERE = @sno --变量@savg存放学号为@sno的学生平均成绩

IF @savg>=60 --进行平均成绩的判断

PRINT ‘此学生综合成绩合格！’

ELSE

PRINT ‘此学生综合成绩不合格！’

GO

执行后，在学生成绩库中创建了名为sp_StuGradebySno的存储过程。

例9.5 在学生成绩库中，定义存储过程sp_StuSumGradebySno，要求实现输入学生学号，显示学生的学号、姓名、课程名称、成绩，并将学生的总分做为输出。

在查询分析器的查询窗口中，输入语句：

CREATE PROCEDURE sp_StuSumGradebySno

@sno nvarchar(10), --@sno是参数，字符型，表示学生的学号

@sumGrade int output

AS

Select s.[Name],Sc.[Grade],c.[Name]

From Student s Inner Join Sc

on s.[SNo]=Sc.[SNo] Inner Join Course c

on Sc.[CNo]= c.[CNo]

Where s.[sno]= @sno

Select @SumGrade = Sum(Grade)

From Student s Inner Join Sc

on s.[SNo]=Sc.[SNo] Inner Join Course c

on Sc.[CNo]= c.[CNo]

Where Where s.[sno]= @sno

GO

执行后，在学生成绩库中创建了名为sp_ StuSumGradebySno的存储过程。

调用时：

Declare @TotalGrade int

exec sp_sltName_Stu '20051024',@TotalGrade output

Print @TotalGrade

例9.6银行业务数据库bankdb，其中，帐户信息表(bank)存放帐户的信息，交易信息表(transInfo)存放每次的交易信息。试用编写一个解决银行转帐问题的存储过程

Create proc sp_Transfer

AS

begin tran

update bank set CurrentMoney = CurrentMoney-@TranMoney Where CardID=@outID

if @@error<>0 rollback tran

else

begin

update bank set CurrentMoney = CurrentMoney+@TranMoney Where CardID=@InID

if @@error<>0 rollback tran

else

begin

Insert into transInfo Values(@outID,getdate(),'支取',@TranMoney)

if @@error<>0 rollback tran

else

begin

Insert into transInfo Values(@INID,getdate(),'存入',@TranMoney)

if @@error<>0 rollback tran

else

begin

commit tran

print '交易成功'

end

end

end

end

(@OutID nvarchar (10),

@InID nvarchar (10),

@TranMoney Money)

触发器

1、创建触发器

（1）用T-SQL语句创建触发器

可以使用CREATE TRIGGER语句创建触发器。

例9.1创建触发器Course_Update，实现当修改Course中的数据时，显示提示信息“学生课程表被修改了”。

在学生课程表上建立触发器，具体操作如下：

在查询分析器的查询窗口中，输入如下T-SQL语句：

USE Student

GO

CREATE TRIGGER Course_Update ON course

FOR UPDATE

AS

PRINT ‘学生课程表被修改了’

GO

执行后，在学生课程表上触发器Course_Update创建成功。

（2）用企业管理器创建触发器

例9.2用企业管理器实现例9.1要求，创建触发器Course_Update1。

具体操作如下：

 展开服务器组，然后展开服务器。展开“数据库”文件夹，展开学生成绩库，然后单击“表”文件夹。

 在右边窗格中，右击学生课程表（Course），指向“所有任务”菜单，然后单击“管理触发器”命令。如图2 用企业管理器创建触发器。

 在“名称”中，单击<新建>。如图2 创建触发器。

 在“文本”框中输入触发器的文本。如图3 编写触发器文本。

 单击“检查语法”命令，进行语法检查。

 单击“确定”按钮，完成触发器的创建。

图2 用企业管理器创建触发器

图2 创建触发器

图3 编写触发器文本

此触发器是由学生课程表的修改操作引发的，因此我们可以通过执行一条修改学生课程表中某个记录的语句，来查看触发器是否执行成功。具体操作如下：

在查询分析器的查询窗口中，输入如下语句：

UPDATE Course

SET Ccredit=5

WHERE Cno='0001'

执行后，在结果窗格中，可以看到消息：“学生课程表被修改了”。

另外，触发器可以用来实现数据完整性的维护。

例9.3创建触发器Course_Delete，实现当删除学生表中某门课程的记录时，对应学生成绩表中所有有关此课程的记录均删除。

可以在查询分析器的查询窗口中输入如下语句：

USE Student

GO

CREATE TRIGGER Course_Delete ON Course

FOR DELETE

AS

DELETE SC FROM SC,deleted

WHERE =

GO

此触发器是由学生课程表的删除操作引发的，因此我们可以通过执行一条删除学生课程表中某个记录的语句，来查看触发器是否执行成功。具体操作如下：

在查询分析器的查询窗口中，输入如下语句：

DELETE Course

WHERE 课程号='0009'

然后查看学生成绩表记录，可以看到“0009”号课程的相关记录也被删除。

四、 实训项目

1、使用sp_helptext查看byroyalty存储过程的文本，该存储过程在数据库pubs中。

2、在学生成绩库中创建存储过程sp_Credit4Course，要求实现如下功能：产生学分为’4’的课程学生选课情况列表，其中包括课程号、课程名、学分、学号、姓名、系别、性别等。并调用此存储过程，显示执行结果。

3、在学生成绩库中创建存储过程sp_StuInfobyDept，要求实现如下功能：输入系别专业名称，产生该系别学生的选课情况列表，其中包括专业、学号、姓名、课程号、课程名、成绩、学分等。并调用此存储过程，显示“ECT”专业学生的选课情况列表。

4、在学生成绩库中创建存储过程sp_StuCredit，要求实现如下功能：输入学生学号，根据该学生所选课程的总学分显示提示信息，如果总学分<9，则显示“此学生学分不足！”，否则显示“此学生学分已足！”。并调用此存储过程，显示“02020101”学生的总学分情况。

5、思考题：在学生成绩库中创建存储过程sp_UpdateSno，要求实现如下功能：更改学生的学号为新学号（提示：此存储过程需要两个参数，使用事务来实现存储过程中的语句）