实验12存储过程设计大型数据库浙江财经学院东方学院信息实验报告免费分享请大家评个分.docx
《实验12存储过程设计大型数据库浙江财经学院东方学院信息实验报告免费分享请大家评个分.docx》由会员分享，可在线阅读，更多相关《实验12存储过程设计大型数据库浙江财经学院东方学院信息实验报告免费分享请大家评个分.docx（11页珍藏版）》请在冰豆网上搜索。
实验12存储过程设计大型数据库浙江财经学院东方学院信息实验报告免费分享请大家评个分
实验（实训）报告
项目名称存储过程设计
所属课程名称高级数据库
项目类型
实验（实训）日期2010-5-12
班级计算机科学与技术
学号**********
姓名朱优苗
指导教师孟宪虎
浙江财经学院教务处制
一、实验（实训）概述：
【目的及要求】
【基本原理】
【实施环境】（使用的材料、设备、软件）
计算机、Widows、SQLServer2000、Word
二、实验（实训）内容：
【项目内容】
【方案设计】
【实验（实训）过程】（步骤、记录、数据、程序等）
一、利用企业管理器创建存储过程。
（1）创建存储过程，通过员工号查询员工姓名、年龄、性别和所在部门（注意不是部门编号）。
createprocedure员工基本信息（@员工号char（4））
AS
select姓名,year（getdate（））-year（cast（出生年月asdatetime））年龄,性别,部门名
from员工表,部门表
where员工表.所在部门号=部门表.部门号
and员工号=@员工号
（2）执行该存储过程，查询并显示员工号为‘2004’的姓名、年龄、性别和所在部门。
exec员工基本信息'2004'
二、使用SQL语句中的CREATEPROCEDURE命令创建存储过程。
（1）设计存储过程，完成对员工表的元组插入工作。
要求使用输入参数。
插入操作成功返出状态值0，失败返出状态值-1。
ifexists（select*
fromsysobjects
wherename='员工表插入'andtype='p'）
begin
dropprocedure员工表插入
end
go
createprocedure员工表插入（@员工号char（4）,@姓名char（8）,@性别char
（2）,@出生年月varchar（60）,@所在部门号char（4）,@技术职称char（10）,@工资money,@参加的项目总数int）
as
begintran
insertinto员工表values（@员工号,@姓名,@性别,cast（@出生年月asdatetime）,@所在部门号,@技术职称,@工资,@参加的项目总数）
if@@error<>0
begin
rollbacktran
return-1
end
else
begin
committran
return0
end
declare@statusint
exec@status=员工表插入'2001','王一','男','1983-03-15','1001','采购部长',4232,12
if@status=0
print'插入成功'
else
print'插入失败'
declare@statusint
exec@status=员工表插入'2015','王一','男','1983-03-15','1001','采购部长',4232,12
if@status=0
print'插入成功'
else
print'插入失败'
（2）编写以下存储过程并执行
1、指定部门，求该部门的总人数，总工资，平均工资，最高工资和最低工资。
ifexists（select*
fromsysobjects
wherename='部门查询'andtype='p'）
begin
dropprocedure部门查询
end
go
createprocedure部门查询（@部门号char（4））
as
select部门号,count（员工号）as部门总人数,sum（工资）as部门总工资,avg（工资）as部门平均工资,max（工资）as部门最高工资,min（工资）as部门最低工资
from部门表,员工表
where员工表.所在部门号=部门表.部门号
and部门号=@部门号
groupby部门号
exec部门查询'1001'
2、利用员工号查询员工参加的项目名称和该项目所在的城市，并要求显示员工的姓名。
ifexists（select*
fromsysobjects
wherename='项目查询'andtype='p'）
begin
dropprocedure项目查询
end
go
createprocedure项目查询（@员工号char（4））
as
select姓名,项目名称,所在地方as项目所在的城市
from项目表,员工参与项目表,员工表
where员工表.员工号=员工参与项目表.员工号
and员工参与项目表.项目编号=项目表.项目编号
and员工参与项目表.员工号=@员工号
exec项目查询'2002'
3、根据项目编号查询参加该项目最多人的部门和最少人的部门。
ifexists（select*
fromsysobjects
wherename='参加项目查询'andtype='p'）
begin
dropprocedure参加项目查询
end
go
createprocedure参加项目查询（@项目编号char（4））
as
selectcount（所在部门号）as部门人数,所在部门号
into#temp
from员工表,员工参与项目表
where员工参与项目表.员工号=员工表.员工号
and项目编号=@项目编号
groupby所在部门号
declare@maxNumchar（4）,@minNumchar（4）
select@maxNum=（selectmax（部门人数））from#temp
select@minNum=（selectmin（部门人数））from#temp
select部门名as参加该项目最多人的部门
from部门表
where部门号=
（select所在部门号
from#temp
where部门人数=@maxNum）
select部门名as参加该项目最少人的部门
from部门表
where部门号=
（select所在部门号
from#temp
where部门人数=@minNum）
exec参加项目查询'J10'
三、对员工表结构进行修改，增加‘技术职称’字段。
在部门表里增加‘部门人数’字段。
（1）编写修改工资存储过程，要求由用户指定员工号，以及增长幅度（比如增长10%，则该变量的值就为0.1），存储过程完成对指定员工工资的修改，如果职称是高级工程师，增长20%，如果职称是工程师，增长15%，如果职称是助理工程师，增长10%。
调用该存储过程，修改工资。
ifexists（select*
fromsysobjects
wherename='工资修改'andtype='p'）
begin
dropprocedure工资修改
end
go
createprocedure工资修改（@员工号char（4））
as
declare@技术职称char（10）
select@技术职称=技术职称
from员工表
where员工号=@员工号
if@技术职称='助理工程师'
update员工表
set工资=工资*1.1
where员工号=@员工号
if@技术职称='工程师'
update员工表
set工资=工资*1.15
where员工号=@员工号
if@技术职称='高级工程师'
update员工表
set工资=工资*1.2
where员工号=@员工号
select*from员工表where员工号='2003'
exec工资修改'2003'
select*from员工表where员工号='2003'
（2）部门人数应该等于员工表中实际员工数，由于有员工调入调出，可能存在不等的情况。
编写存储过程，检查所有部门人数的正确性，如果不正确，则进行修改。
显示部门表数据；
执行存储过程；
再显示部门表数据。
ifexists（select*
fromsysobjects
wherename='部门人数修改'andtype='p'）
begin
dropprocedure部门人数修改
end
go
createprocedure部门人数修改
as
selectcount（所在部门号）as部门人数,所在部门号
into#temp
from员工表
groupby所在部门号
declare@maxNumchar（4）,@minNumchar（4）,@nint
select@maxNum=（selectmax（部门号））from部门表
select@minNum=（selectmin（部门号））from部门表
while（cast（@minNumasint）<=cast（@maxNumasint））
begin
select@n=部门人数
from#temp
where所在部门号=@minNum
update部门表
set部门人数=@n
where部门号=@minNum
set@minNum=cast（（cast（@minNumasint）+1）aschar（4））
end
select*from部门表
exec部门人数修改
select*from部门表
【结论】（结果、分析）
三、指导教师评语及成绩：
评语：
成绩：
指导教师签名：
批阅日期：
升级会员 