实验五 存储过程和触发器的定义和使用3

实验五存储过程和触发器的定义和使用

一、实验目的

1、掌握局部变量、全局变量、流程控制语句的使用方法

2、了解存储过程的类型和作用，并掌握使用对象资源管理器和Transact-SQL语句创建

存储过程的方法及使用方法。

3、理解触发器的特点和作用，并掌握使用Transact-SQL语言创建触发器的方法

二、实验内容

1.在学生成绩库中中有如下各表：

学生表（Student）

学号姓名性别出生日期专业所在系联系电话020101杨颖01980-7-20计算机应用计算机88297147 020102方露露01981-1-15信息管理计算机88297147 020103俞奇军11980-2-20信息管理计算机88297151 020104胡国强11980-11-7信息管理计算机88297151 020105薛冰11980-7-29水利工程水利系88297152 020201秦盈飞01981-3-10电子商务经济系88297161 020202董含静01980-9-25电子商务经济系88297062 020203陈伟11980-8-7电子商务经济系88297171 020204陈新江11980-7-20房建水利系88297171 create database学生成绩数据库

create table Student

(

学号Char(6)not null,

姓名Char(8)not null,

性别Bit not null,

出生日期smalldatetime,

专业Char(10),

所在系Char(10),

联系电话Char(11)null

)

课程表（Course）

课程号课程名教师开课学期学时学分

101计算机原理陈红2453

102计算方法王颐3453

103操作系统徐格2604

104数据库原理及应用应对刚3755

105网络基础吴江江4453

106高等数学孙中文1906

107英语陈刚1906

108VB程序设计赵红韦3705

create table Course

(

课程号Char(3)not null,

课程名Char(20)not null,

教师Char(10),

开课学期Tinyint,

学时Tinyint,

学分Tinyint not null,

)

学生选课成绩表（SC）

学号课程号成绩

020********

020********

020********

020********

020********

020********

020********

020********

020********

020********

create table SC

(

学号Char(6)not null,

课程号Char(3)not null,

成绩Smallint,

)

对三个表格分别导入，截图如下：

2、T-SQL语句中流程控制语句的使用

（1）全局变量的使用。显示到当前日期和时间为止试图登录SQL Server的次数。select getdate()as'当前的日期和时间',

@@connections as'试图登陆的次数'

（2）IF语句的使用。

①在Student表中，若存在学号“020205”的学生，则显示该学生的信息，否则插入

该学生的记录（020205,李萍,0,1983-7-20,电子商务,经济系,88297171）

If exists(select*from Student where学号='020205')

select*from Student where学号='020205'

Else

insert into Student values('020205','李萍','0','1983-7-20','电子商务','经济系','88297171')

②查询杨颖有没有选课，若选了课，则统计其平均成绩，若没有选课，则输出“杨颖没

有选课”

If exists(select姓名from Student,SC where姓名='杨颖'and Student.学号=SC.学号)

select avg(成绩)from SC,Student where Student.姓名='杨颖'and Student.学号=SC.学号

Else

print'杨颖没有选课!'

（3）循环语句的使用。

①用WHILE语句编程计算1－100之间所有能被3整除的数的个数及总和。DECLARE@S SMALLINT,@I SMALLINT,@NUMS SMALLINT

SET@S=0

SET@I=1

SET@NUMS=0

WHILE(@I<=100)

BEGIN

IF(@I%3=0)

BEGIN

SET@S=@S+@I

SET@NUMS=@NUMS+1

END

SET@I=@I+1

END

PRINT@S

PRINT@NUMS

②利用GOTO语句求出从1加到5的总和。

DECLARE@S SMALLINT,@I SMALLINT

SET@I=1

SET@S=0

BEG:

IF(@I<=5)

BEGIN

SET@S=@S+@I

SET@I=@I+1

GOTO BEG

END

PRINT@S

（4）WAITFOR语句的使用。

①等待5秒后执行查询Student学生信息

waitfor delay'00:00:05'

select*

from Student

②等到晚上10:20执行存储过程update_all_stats。BEGIN

WAITFOR TIME'10:20'

EXECUTE update_all_stats

END

（5）CASE语句的使用。

①查询Student的SNO，SEX，如果SEX为“1”则输出“男”，如果为“0”输出“女”。

SELECT学号,

性别=

CASE性别

WHEN'1'THEN'男'

WHEN'0'THEN'女'

END

FROM Student

②从SC表中查询所有同学选课成绩情况，凡成绩为空者输出“未考”、小于60分输出“不

及格”、60分至70分输出“及格”、70分至90分输出“良好”、大于或等于90分时输出“优秀”。

SELECT学号,课程号,

成绩=

CASE

WHEN成绩IS NULL THEN'未考'

WHEN成绩<60THEN'不及格'

WHEN成绩>=60AND成绩<70THEN'及格'

WHEN成绩>=70AND成绩<90THEN'良好'

WHEN成绩>=90THEN'优秀'

END

FROM SC

3、存储过程的的使用。在上面学生成绩库中完成如下操作：（1）创建如下不带参数的简单存储过程：

查询成绩在60至80分之间的学生的学号和课程号。create procedure SCList

as

select学号,课程号

from SC

where成绩>60and成绩<80

（2）分别创建如下的带输入参数的存储过程：

①根据用户输入的学号，删除该学生选课成绩记录；create procedure studentqk1

(@学号char(10))

as

delete成绩

from SC

where学号=@学号

②向SC表中插入数据。

create procedure sccharu

(@学号char(10),@课程号char(10),@成绩int)

as

insert into SC values(@学号,@课程号,@成绩)

（3）创建如下的带输出参数的存储过程，并写出一个执行该过程的例子：根据用户输入的学号，课程号，输出其成绩。

create procedure grade

(@学号char(10),@课程号char(10)output,@成绩int output)

as

select@成绩=成绩

from SC

where学号=@学号and课程号=@课程号

declare@成绩int

execute grade'020101','101',@成绩output

select'成绩'=@成绩

（4）创建存储过程proc_t1，要求实现如下功能：输入专业名称，产生该专业学生的选课情况列表，其中包括专业、学号、姓名、课程号、课程名、成绩、学分等。并调用此存储过程，显示“信息管理”专业学生的选课情况列表。

create procedure proc_t1(@专业char(10))

as

select Student.学号,Student.专业,Student.姓名,

Course.课程号,Course.课程名,Course.学分,成绩

from Student,Course,SC

where Student.学号=SC.学号and Course.课程号=SC.课程号and Student.专业=@专业

exec proc_t1'信息管理'

（5）对学生成绩库中已创建的存储过程proc_t1进行修改，要求实现如下功能：输入专业

名称，产生该专业所有男生的选课情况列表，其中包括专业、学号、姓名、课程号、课程名、成绩、学分等。并调用修改后的存储过程，显示“信息管理”专业男生的选课情况列表。

alter procedure proc_t1(@专业char(10))

as

select Student.学号,Student.专业,Student.姓名,

Course.课程号,Course.课程名,Course.学分,成绩

from Student,Course,SC

where Student.学号=SC.学号and Course.课程号=SC.课程号and Student.专业=@

专业and性别=1

exec proc_t1'信息管理'

（6）删除学生成绩库中的存储过程proc_t2。

drop procedure proc_t1

（7）编写存储过程，要求实现如下功能：输入课程名称，产生该课程各分数段及其相应人数的成绩分布情况统计。

create procedure scqk(@课程名char(10))

as

begin

select课程名,

sum(case when成绩between0and59then1else0end)as'不及格',

sum(case when成绩between60and69then1else0end)as'60-69',

sum(case when成绩between70and89then1else0end)as'70-89',

sum(case when成绩between90and100then1else0end)as'90-100' from SC,Course

where SC.课程号=Course.课程号and课程名=@课程名

group by课程名

end

exec scqk'计算机原理'

4、触发器的使用。在上面学生成绩库中完成如下操作：

（1）DML触发器的使用

①创建一个INSERT触发器tri_sc_insert，当向sc表中添加数据时，如果添加的数据与

Student表中的数据不匹配（没有对应的学号），则将此数据删除。

create trigger tri_sc_insert on SC

for insert

as

begin

declare@bh char(6)

select@bh=inserted.学号

from inserted

if not exists(select学号from Student where Student.学号=@bh)

delete SC where学号=@bh

end

②创建一个UPDATE触发器tri_sc_upd，用来防止用户修改SC表的成绩。

create trigger tri_sc_upd on SC

for update

as

if update(成绩)

begin

print'修改失败。。。。'

raiserror('不能修改SC表的成绩',16,10)

rollback transaction

end

③创建一个级联修改触发器trigger_1，即当修改学生课程表Course中的某门课的课

程号时，对应学生的选课表SC中的课程号也作修改

create trigger trigger_1on Course

for update

as

if update(课程号)

begin update SC

set课程号=(select课程号from inserted)

from SC,deleted

where SC.课程号=deleted.课程号

end

④建立一个级联删除触发器trigger_2，实现，即当删除表Student中的记录时，

自动删除表sc中对应选课记录。

create trigger trigger_2on Student

for delete

as

begin

delete SC

from SC,deleted

where SC.学号=deleted.学号

end

⑤创建触发器trigger_3，实现当修改学生表中的数据时，显示提示信息“学生表被修改了”。

create trigger trigger_3on Student

for update

as

print'学生表被修改了！'

⑥修改已创建的触发器trigger_3，实现当修改学生表中的数据时，显示提示信息“学

生表中XXX号学生记录被修改了”。

alter trigger trigger_3on Student

for update

as

declare@kch char(6)

select@kch=学号

from deleted

print'学生表中学号为'+@kch+'的记录被修改了'

⑦删除学生表上的触发器trigger_3。

drop trigger trigger_3

（2）DDL触发器的使用（选作）

①使用DDL触发器来防止在数据库中创建表。创建一个safety_1触发器，禁止用户在

学生成绩库数据库创建表。并运行CREATE TABLE newtable(ID int)语句验证。

②用DDL触发器来防止在数据库中修改、删除表。创建一个safety_2触发器，禁止用

户在学生成绩库数据库中修改、删除表。并运行DROP TABLE SC语句验证

③删除触发器safety_1和safety_2

三、思考题

1、存储过程的类型有哪些？分别有什么特征？。

2、如何创建一个存储过程？试述存储过程在程序设计的作用。

3、假设必须修改数据库中的一个存储过程，同时有几个用户被授予执行这个存储过程

的权限，请问执行哪个语句可以实现修改，但又不影响现有的权限。

4、列出本实验你遇到的问题及解决办法