实验五 存储过程和触发器(参考预习)

实验五存储过程、触发器的创建和使用

一．实验目的：

1)了解存储过程的概念

2)了解使用存储过程的特点及用途

3)掌握创建存储过程的方法

4)掌握执行存储过程的方法

5)了解查看、修改和删除存储过程的方法

6)了解触发器和一般存储过程的主要区别

7)了解使用触发器的优点

8)掌握创建触发器的方法

9)掌握查看触发器信息的方法

10)了解删除触发器的方法

二．实验准备

1．熟悉存储过程的使用

2．熟悉触发器的使用；

三．实验要求

1．在实验之前做好实验准备

2. 完成数据库设计，并验收实验结果，提交实验报告

四．实验内容

实验内容及步骤：

（一）存储过程的创建和使用

例1：在企业管理器中创建一个名为StuInfo的存储过程，完成的功能是在student表中查询系号为D2的学号、姓名、性别、年龄、系号的内容。

CREATE PROCEDURE StuInfo

AS

SELECT SNO AS 学号，

SNAME AS 姓名，

SSEX AS 性别，

SAGE AS 年龄,

DNO AS 系号

FROM S

WHERE DNO=’D2’

例2：创建存储过程StuScoreInfo，完成的功能是在表student,course和study中查询以下字段：学号、姓名、性别、课程名称、考试分数。

程序清单：

打开student数据库

use mydb

--查询是否已存在此存储过程，如果存在，就删除它

if exists (select name from sysobjects

where name =’StuScoreInfo’ and type= ‘P’)

drop procedure StuScoreInfo

go

--创建存储过程

creat proceure StuScoreInfo

as

select student.sno as 学号，

sname as 姓名，

sex as 性别,

ame as 课程名称,

study.grade as 考试分数

from student,course,study

where student.sno=study.sno and o=o

例3：创建一个带有参数的存储过程stu_info，该存储过程根据传入的学生编号，在t_student中查询此学生的信息。

程序清单：

--删除已存在的存储过程

if exists (select name from sysobjects

where name = ‘stu_info’ and type =’P’)

drop procedure stu_info

go

--创建存储过程

create procedure stu_info

@sno varchar(8)

as

select

sno as 学号,

sname as 姓名,

sex as 性别,

birthday as 出生日期

polity as 政治面貌

from student

where sno=@sno

（二）执行存储过程的方法

例1：执行前面例1中创建的StuInfo存储过程。

Use mydb

Exec StuInfo /*或者直接写存储过程的名称StuInfo*/

注意：如果省略Exec关键字，则存储过程必须是批处理中的第一条语句，否则会出错。例2：执行前面例2中创建的StuScoreInfo存储过程。

Use mydb

Exec StuScoreInfo

例3：执行前面例3中创建的Stu_Info存储过程，该存储过程有一个输入参数“学号”，在执行时要传入一个学号值。

Use mydb

Exec Stu_Info ‘s1’

或：

Use mydb

Exec Stu_Info @sno=‘s1’

例4：创建存储过程d_grade，根据指定的课程名（输入参数）返回该课程的最高分、最低分、平均分（输出参数）。要求在创建存储过程前要先判断该存储过程是否已存在，如果存在，则将其删除。

（三）查看存储过程

使用企业管理器，右击要查看的存储过程，在弹出的快捷菜单中选择“属性”选项，弹出“存储过程属性”对话框，在此对话框中可看到存储过程的源代码。

1.使用系统存储过程查看用户创建的存储过程：

sp_help [[@objname=]name]

用于显示存储过程的参数及其数据类型。

sp_helptext [[@objname=]name]

用于显示存储过程的代码。

sp_depends [@objname=]’object’

用于显示和存储过程相关的数据库对象。

（四）修改存储过程

使用企业管理器中右击要查看的存储过程，在弹出的快捷菜单中选择“属性”选项，弹出“存储过程属性”对话框，在此对话框中可直接修改存储过程的代码。

1.使用T_SQl修改存储过程

例1：修改前面创建的stu_info存储过程，使之完成以下功能：根据传入的学号，在表student,course,study中查询此学生的学号，姓名、性别、考试课程名称和考试分数。Use mydb

Alter procedure stu_info

@sno varchar(10)

as

select sname as 姓名，

sex as 性别,

ame as 课程名称,

score as 考试成绩

from student,course,study

where student.sno=@sno and student.sno=study.sno and o=o

exec stu_info ‘s1’

（五）重命名存储过程

1. 使用企业管理器，右击要操作的存储过程，在弹出的快捷菜单中选择“重命名”选项。

2.使用T_SQl修改存储过程

sp_rename 原存储过程名称，新存储过程名称

（六）删除存储过程

3.使用SQL Server Management Studio 修改存储过程在SQL Server Management Studio 中，右击要操作的存储过程，在弹出的快捷菜单中选择“删除”选项。

4.使用T_SQl修改存储过程

drop procedure {procedure} [,…n]