实验八(上)：SQL Server用户自定义函数和触发器

实验八（上）用户自定义函数和触发器

一、实验目的

1、掌握SQLServer中用户自定义函数的使用方法。

2、掌握SQL Server中触发器的使用方法。

二、实验内容和要求

1.创建一个返回标量值的用户定义函数RectangleArea：输入矩形的长和宽就能计算

矩形的面积。自选2种实例调用该函数。

create function RectangleArea(@a int,@b int)returns int

as

begin

return @a*@b

end

declare @area int

execute @area=RectangleArea 3,5

print('矩形面积是：')

print @area

declare @area int

execute @area=RectangleArea 7,8

print('矩形面积是：')

print @area

2.创建一个用户自定义函数（内嵌表值函数），功能为产生某个系的学生选修信息，

内容为学号，姓名，课程名，成绩。调用这个函数，显示信息系有选课学生的信息。create function Search (@sdept char(10))returns table

as

return(

select sc.sno 学号,student.sname 姓名,ame 课程名,sc.grade 成绩,

student.sdept 系别from sc,student,course where o=o and

sc.sno = student.sno and sdept=@sdept

)

select*from Search('cs')

3.创建一个作用在P表上的触发器P_checks，确保用户在插入或更新P表的WEIGHT

值时，所提供的WEIGHT值介于20与40之间，否则给出错误提示并回滚此操作。

请测试该触发器，测试方法自定。

create trigger P_checks on p for insert

as

begin

declare @weight int

select @weight=weight from inserted

if @weight<10 or @weight>20

begin

RAISERROR('weight 必须在~20之间！',16,1)

ROLLBACK TRANSACTION

end

end

insert into p(pno,pname,color,weight)values('p7','刀片','红',40)

insert into p(pno,pname,color,weight)values('p7','刀片','红',15)

select*from p

4.创建一个作用在J表上的触发器J_Update，禁止同时修改项目的名称和所在城市，

并进行相应的错误提示。请测试该触发器。测试方法自定。

create trigger J_Update on j for update

as

begin

declare @jname1 char(10),@city1 char(10),@jname2 char(10),@city2 char(10)

select @jname1=jname,@city1= city from inserted

select @jname2=jname,@city2= city from deleted

if @jname1<>@jname2 and @city1<>@city2

begin

RAISERROR('不能同时修改项目名称和项目地点！',16,1)

ROLLBACK TRANSACTION

end

end

update j set jname='建筑',city='上海'where jno='j1'

update j set jname='建筑'where jno='j1'

select*from j

5.学生表（Student）中存放学生的记录，学生选修表(SC) 中存放学生的修课及成绩

情况。创建一个触发器ScDel_Cascade，当删除Student中的数据时，数据表SC中有关刚刚删除学生的修课成绩信息也能被级联删除掉。测试该触发器，测试方法自定。

create trigger ScDel_Cascade on student for delete

as

begin

declare @sno char(10)

select @sno=sno from deleted

delete from sc where sno=@sno

end

delete from student where sname='李勇'

select*from sc

select*from student

附加题：

创建一个用户自定义函数，功能为产生一张有关学生成绩统计的报表。该报表显示每一门课程的课程号、课程名、选修人数、本门最高分、最低分和平均分。调用这个函数，生成相应的报表并给用户浏览。(多语句表值函数)

显示形式如下：

Cno Cname SC_number Max_grade Min_grade Average_grade

---- -------------------- --------- --------- --------- --------------

1 数据库 3 9

2 92 92.0

2 数学 2 85 80 82.0

3 信息系统 1 88 88 88.0

4 操作系统 1 NULL NULL NULL

5 数据结构 1 NULL NULL NULL

6 数据处理 2 55 55 55.0

7 PASCAL语言 1 NULL NULL NULL

(7 行受影响)

select*from sc,student,course where o =o and student.sno =sc.sno

create function studentgrade()returns table

as

return(

select o Cno,ame Cname,count(sc.sno) SC_number,

max(sc.grade)Max_grade,min(sc.grade)Min_grade,avg(sc.grade)Average_grade from sc,student,course where sc.sno = student.sno and

o = o group by o,ame

)

select*from studentgrade()

三、实验报告