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

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

实验内容：

1.在学生成绩数据库中建立表，并相应的导入数据

create database学生成绩

use学生成绩

create table student(学号char(6)not null,

姓名char(8)not null,

性别bit not null,

出生日期smalldatetime,

专业名char(10),

所在系char(10),

联系电话char(11)null)

create table course(课程号char(3)not null,

课程名char(20)not null,

教师char(10),

开课学期tinyint,

学时tinyint,

学分tinyint not null)

create table sc(学号char(6)not null,

课程号char(3)not null,

成绩smallint)

2.

（1）全局变量

（2）if语句

①

②

（3）循环语句

①

declare@i int,@sum int,@count int

set@i=1

set@sum=0

set@count=0

while (@i<100)

begin

if(@i%3=0)

begin

set@sum=@sum+@i

set@count=@count+1

end

set@i=@i+1

end

print'总和为:'+convert(varchar(10),@sum)

print'个数为:'+convert(varchar(10),@count)

②

declare@i1int,@s1int

set@i1=1

set@s1=0

beg:

if(@i1<=5)

begin

set@s1=@s1+@i1

set@i1=@i1+1

goto beg

end

print@s1

（4）waitfor 语句

①

waitfor delay'00:00:05'

select*

from student

go

②

waitfor time'10:20'

exec update_all_stats

（5）case语句

①select学号,

性别=

case性别

when'1'then'男'

when'0'then'女'

end

from student

go

②select学号,

成绩=

case

when成绩IS NULL then'未考'

when成绩<60 then'不及格'

when成绩>=60 and成绩<70 then'及格'

when成绩>=70 and成绩<90then'良好'

when成绩>=90 then'优秀'

end

from sc

3．存储过程

（1）create proc proc1

as

select学号,课程号

from sc

where成绩between 60 and 80

go

exec proc1

（2）

①create proc proc21(@学号char(10))

as

delete成绩

from sc

where学号=@学号

go

②create proc proc22(@学号char(6),@课程号char(3),@成绩smallint)

as

insert into sc

values(@学号,@课程号,@成绩)

go

（3）create proc proc3

(@学号char(6),@课程号char(3),@成绩smallint output)

as

select@成绩=成绩

from sc

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

go

declare@成绩smallint

exec proc3'020101','101',@成绩output select'成绩'=@成绩

（4）

create proc proc_t1(@专业名称char(10))

as

select专业名,student.学号,姓名,course.课程号,course.课程名,成绩,学分

from student,sc,course

where专业名=@专业名称and student.学号=sc.学号and sc.课程号=course.课程号go

exec proc_t1'信息管理'

go

（5）

alter proc proc_t1(@专业名称char(10))

as

select专业名,student.学号,姓名,course.课程号,course.课程名,成绩,学分

from student,sc,course

where专业名=@专业名称and student.学号=sc.学号and sc.课程号=course.课程号

and student.性别=1

go

exec proc_t1'信息管理'

go

（6）drop proc proc_t1

（7）

create proc proc7(@课程名char(10))

as

begin

select课程名,

sum(case when成绩between 0 and 59 then 1 else 0 end)as'0-60分', sum(case when成绩between 60 and 79 then 1 else 0 end)as'60-79分', sum(case when成绩between 80 and 89 then 1 else 0 end)as'80-89分', sum(case when成绩between 90 and 100 then 1 else 0 end)as'90-100分' from sc,course

where课程名=@课程名and sc.课程号=course.课程号

group by课程名

end

go

exec proc7'英语'

go

4.

（1）create trigger tri_sc_insert on sc

after insert

as

begin

declare@sno char(6)

select@sno=inserted.学号

from inserted