金陵科技学院实验报告

group by sales.title_id,titles.price,titles.title_id

having(sales.title_id=titles.title_id)

（2） select title_id,sum(qty) qty from sales group by title_id （3）select title_id,sum(qty) qty from sales group by title_id having

(sum(qty)>30)

（4） select count(title_id) num from titles where price is not null

3、连接查询

（1）select distinct(a.title_id),b.au_id,a.au_id from titleauthor

a,titleauthor b

where a.title_id=b.title_id

（2）select a.title,b.stor_name,c.qty from titles a,stores b ,sales c where a.title_id=c.title_id and b.stor_id=c.stor_id （3）select a.title,b.pub_name from titles a,publishers b where a.pub_id=b.pub_id

4、子查询

（1） select * from titles,sales where sales.title_id=titles.title_id （2） select sales.title_id,titles.title,titles.[type],titles.price from

titles,sales where sales.title_id=titles.title_id

二、函数

（1）CREATE FUNCTION Sage_func ( @vardate datetime , @curdate datetime ) RETURNS tinyint AS BEGIN RETURN datediff ( yy , @vardate , @curdate ) END

select SNO as 学号, SName as 姓名, dbo.Sage_func(Birthday,getdate()) as 年龄

from Student

（2）CREATE FUNCTION grade_func ( @Scorce int ) RETURNS tinyint AS BEGIN RETURN @Scorce/20 END

select SName as 姓名, dbo.grade_func(Scorce) as 五级分制 from Grade,Student where Grade.SNO=Student.SNO--三、流程控制

三、流程控制

(1)IF exists (select * from Student where SNO='19920101')

BEGIN

select * from Student where SNO='19920101'

END ELSE BEGIN

insert into Student values('19920101','王军','男','CS01','下关

#','1976-12-21 00:00:00',1.82,10) END

delete from Student where SNO='19920101' (2) declare

20

金陵科技学院实验报告

@sum int, @start int set @start=1 set @sum=0

begin

while(@start<101) begin

set @sum=@sum+@start set @start=@start+1

end

print @sum end

六、实验体会和收获

实验项目名称： 存储过程和触发器 实验学时： 同组学生姓名： 实验地点： 实验日期： 实验成绩： 批改教师： 批改时间：

实验6 存储过程和触发器

一、实验目的和要求

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

2、 通过创建和执行存储过程了解存储过程的基本概念掌握使用存储过程操作技巧和方法 3、 通过对已创建的存储过程的改变，掌握修改、删除存储过程的技巧。 4、 了解触发器的基本概念，理解触发器的功能。 5、 掌握创建、修改和删除和使用触发器的操作方法。

二、实验设备、环境

设备：奔腾Ⅳ或奔腾Ⅳ以上计算机

环境：WINDOWS 2000 SERVER或WINDOWS 2003 SERVER、SQL Server2005中文版。

三、实验步骤

1、根据题目要求熟悉SQL Server2005的各种管理工具。 2、分析题意，重点分析题目要求并给出解决方法。

3、按题目要求完成实际操作任务，并将相关文档资料保存在以自己学号命名的文件夹中。

21

金陵科技学院实验报告

4、提交完成的实验结果。

四、实验内容

一、存储过程的类型。

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

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

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

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

1、对MyDB中已创建的存储过程proc_1进行修改，要求在显示列表中增加班级字段，即产生学分为“4”的课程学生选课情况列表，其中包括课程号、课程名、学分、学号、姓名、专业、班级、性别等。

2、在MyDB中创建的存储过程proc_2进行修改，要求实现如下功能：输入专业名称，产生该专业所有男生的选课情况列表，其中包括专业、学号、姓名、课程号、课程名、成绩、学分等。并调用修改后的存储过程，显示“计算机应用”专业男生的选课情况列表。 3、对MyDB中已创建的存储过程proc_3进行修改，要求实现如下功能：输入学生学号，根据该学生所选课程的总学分显示提示信息，如果总学分<9，则显示“此学生所选总学分为XXX，学分不足！”，否则显示“此学生所选总学分为XXX，学分已足！”。并调用修改后的存储过程，显示“19920102”学生的总学分情况。 四、删除存储过程

1、删除MyDB中的存储过程proc_1。 五、创建触发器

1、创建触发器trigger_1,实现当修改学生表（Student）中的数据时，显示提示信息“学生情况表被修改了”。

2、在MyDB中创建触发器trigger_2,实现如下功能：当在学生成绩表（Gread）中删除一条学生选课信息后，自动实现更新该学生在学生情况表（Student）中的总学分信息。 3、创建触发器trigger_3,实现当修改学生情况表（Student）中的某个学生的学号时，对应学生成绩表（Grade）中的学号也作修改。 六、修改触发器

22

金陵科技学院实验报告

1、对已创建的触发器trigger_1进行修改，实现当修改学生情况表（Student）中的数据时，显示提示信息“学生情况表中XXX号学生记录被修改了”。 七、删除触发器

1、删除学生情况表上的触发器trigger_1。

五、问题解答及实验结果

一、存储过程的类型

use pubs exec sp_helptext byroyalty

二、创建与执行存储过程

1、if exists (select name from sysobjects where name ='proc_8_t1 'and type

='p') drop procedure proc_8_t1 go create procedure proc_8_t1

as select xskc.课程号,课程名,xskc.学分,xsqk.学号,姓名,系别,专业,性别 from xsqk,xskc,xscj

where xscj.课程号=xskc.课程号and xscj.学号=xsqk.学号and xscj.学分='4' exec sp_helptext proc_8_t1 exec proc_8_t1

2、if exists (select name from sysobjects where name ='proc_8_t2' and

type='p') drop procedure proc_8_t2

Go create procedure proc_8_t2(@speciality varchar(255))

As select 专业,xscj.学号,姓名,xscj.课程号,课程名,xscj.学分,xscj.成绩 from xsqk,xscj,xskc

where xsqk.专业=@speciality and xsqk.学号=xscj.学号and xscj.课程号=xskc.课程号 exec sp_helptext proc_8_t2 exec proc_8_t2 '信息管理'

3、if exists(select name from sysobjects where name = 'proc_8_t3' and type

='p') drop procedure proc_8_t3

Go create procedure proc_8_t3(@学号 varchar(255)) As declare @总学分 char(2)

select @总学分=总学分 from xsqk where 学号=@学号 if(@总学分<9) print '学分不足' else print'学分已充足'

go exec sp_helptext proc_8_t3 exec proc_8_t3 '02020101'

三、修改存储过程

1、alter procedure proc_8_t1

as select xskc.课程号,课程名,xskc.学分,xsqk.学号,姓名,系别,专业,性别,班级 from xsqk,xskc,xscj

where xscj.课程号=xskc.课程号and xscj.学号=xsqk.学号and xscj.学分='4' exec proc_8_t1

2、alter procedure proc_8_t2(@speciality varchar(255))

As select 专业,xscj.学号,姓名,xscj.课程号,课程名,xscj.学分,xscj.成绩 from xsqk,xscj,xskc

where xsqk.专业=@speciality and xsqk.学号=xscj.学号 and xscj.课程号=xskc.课程号 and xsqk.性别='男' exec proc_8_t2 '信息管理'

3、alter procedure proc_8_t3(@学号 varchar(255))

As declare @总学分 varchar(255)

23