《实验12存储过程设计》由会员分享，可在线阅读，更多相关《实验12存储过程设计（9页珍藏版）》请在金锄头文库上搜索。
1、浙注附滋迨院实 验（实训）报 告项目名称存储过程设计所属课程名称 高级数据库项目类型实验（实讪I）日期2010-5-12班 级计算机科学与技术学号0720410149姓名朱优苗指导教师孟宪虎浙江财经学院教务处制一、实验（实训）概述: 【目的及要求】【基本原理】【实施环境】（使用的材料、设备、软件）计算机、Widows、SQL Server 2000、Word二、实验（实训）内容: 【项目内容】【方案设计】【实验（实训）过程】（步骤、记录、数据、程序等） 一、利用企业管理器创建存储过程。-團LLJ_ Ullip -LL 禎J 对关系图 二I表 知视图或用户厂逮角色J规则 二I默认 畠用户 疚用户
2、_从这里创建窗口刷新（1）帮助(1)创建存储过程，通过员工号査询员工姓名、年龄、性别和所在部门(注意 不是部门编号)。create procedure员工基本信息(员工号char(4)ASselect 姓名,year(getdate()-year(cast(出生年月 as datetime)年龄，性别, 部门名from员工表，部门表where员工表所在部门号=部门表部门号and员工号=员工号(2)执行该存储过程，査询并显示员工号为2004的姓名、年龄、性别和所 在部门。exec员工基本信息2004二、使用SQL语句中的CREATE PROCEDURE命令创建存储过程。(1)设计存储过程，完成对
3、员工表的元组插入工作。要求使用输入参数。插入 操作成功返出状态值0,失败返出状态值-1。if exists(select*from sysobjectswhere name=员工表插入and type=p)begindrop procedure员工表插入endgocreate procedure员工表插入(员工号char(4),姓名char(8),性别 char(2),出生年月 varchar(60),所在部门号char(4),技术职称 char(10),X资 money,加的项目总数 int)asbegin traninsert into员工表values(员工号,姓名,性别,cast(出生
4、年月as datetime),所在部门号,技术职称,工资,参加的项目总数)if error0beginrollback tranreturn -1endelsebegincommit tranreturn 0enddeclare status intexec status=员工表插入2001,王一，男,1983-03-15,1001,采购部长 ,4232,12if status=0print 插入成功elseprint 插入失败|矗务器：消息 丹D1,级别14,状态 引 过程 员工表插入，行4际能在具有唯一索引员工表一员工号的对榦员工表中插入重复键的行。修句已终止。懾入失败declare st
5、atus intexec status=员工表插入2015,王一，男,1983-03-15,1001,采购部长 ,4232,12if status=0print 插入成功elseprint 插入失败所影响的行数为1行）插入成功（2）编写以下存储过程并执行1、指定部门，求该部门的总人数，总工资，平均工资，最高工资和最低工资。if exists（select*from sysobjectswhere name=部门査询and type=p） begindrop procedure 部门査询endgo create procedure 部门査询（部门号 char（4） asselect部门号,cou
6、nt（员工号）as部门总人数,sum（工资）as部门总工 资,avg（工资）as部门平均工资,max（工资）as部门最高工资,min（工资）as部 门最低工资from部门表员工表where员工表所在部门号=部门表部门号and部门号=部门号group by部门号 exec部门査询1001r部门号部门总人数部门总工演部门平均工資部门最咼工資部门最低工演11001413446.12013361.53005103.37011575.00002、利用员工号査询员工参加的项目名称和该项目所在的城市，并要求显示员 工的姓名。if exists（select*from sysobjectswhere name
7、=项目査询and type=p） begindrop procedure 项目査询endgo create procedure 项目査询（员工号 char（4） asselect姓名项目名称，所在地方as项目所在的城市 from项目表，员工参与项目表，员工表 where员工表员工号=员工参与项目表员工号and员工参与项目表项目编号=项目表项目编号 and员工参与项目表员工号=员工号exec项目査询2002项目名称项目所在的城市12345678910-二- - - - - - - -二-二- - - - - - - - - - nTJ FTTJ nTJ H. nTJ nTJ nTJ FTTJ H
8、. H. .K4r,;Kf-;Kf-;K4r,;K.K PTV员工加薪浙江省嘉兴市婴儿用品广告浙江省温州市保健品广告浙江省萧L1J市冢电促销浙江省宁波市公司装潢浙江省嗓州市食品促销浙江省台州市公司重组浙江省杭州市采购瓷器浙江省金华市公司收购上海市采购建材浙江省杭州市3、根据项目编号査询参加该项目最多人的部门和最少人的部门。if exists(select*from sysobjects where name=参加项目査询and type=p) begindrop procedure参加项目査询endgocreate procedure参加项目査询(项目编号char(4) asselect co
9、unt(所在部门号)as部门人数，所在部门号 into #tempfrom员工表，员工参与项目表 where员工参与项目表员工号=员工表员工号 and项目编号=项目编号group by所在部门号 declare maxNum char(4),minNum char(4) select maxNum=(select max(部 门人数)from #temp select minNum=(select min(部 门人数)from #temp select部门名as参加该项目最多人的部门 from部门表 where部门号=(select所在部门号 from #temp where 部门人数=max
10、Num)select部门名as参加该项目最少人的部门 from部门表where部门号=(select所在部门号 from #temp where 部门人数=minNum) exec参加项目査询J101J養加诗项目最爭人的部门1 财务部養加诗项目最少人的部门1广告部三、对员工表结构进行修改，增加技术职称字段。在部门表里增加部门 人数字段。(1)编写修改工资存储过程，要求由用户指定员工号，以及增长幅度(比如增 长10%，则该变量的值就为0.1)，存储过程完成对指定员工工资的修改，如果 职称是高级工程师，增长20%,如果职称是工程师，增长15%，如果职称是助理 工程师，增长10%。调用该存储过程，修
11、改工资。if exists(select*from sysobjects where name=工资修改and type=p) begindrop procedure 工资修改 endgo create procedure 工资修改(员工号 char(4) as declare 技术职称 char(10)select 技术职称=技术职称from员工表where员工号=员工号if 技术职称=助理工程师update员工表set工资=工资*11where员工号=员工号if 技术职称=工程师update员工表set工资=工资*1.15where员工号=员工号if 技术职称=高级工程师update员工表
12、set工资=工资*12where员工号=员工号select *from 员工表 where 员工号=2003I |员工号 姓塔性别出生年月所在部门号拽术职称工贅参加的项目总数12003 刘苦女116 198412:00AM 1003工程师2205.00002exec工资修改2003 select *from 员工表 where 员工号=20037员工号姓名1性别1出生年月.所在部门号拽术职称工资奏加的项目总数12003 刘苦女116 1984 12:00AM1003工程师2535.750022)部门人数应该等于员工表中实际员工数，由于有员工调入调出，可能存在 不等的情况。编写存储过程，检査所有
13、部门人数的正确性，如果不正确，则进 行修改。显示部门表数据；执行存储过程； 再显示部门表数据。if exists(select*from sysobjectswhere name=部门人数修改and type=p)begindrop procedure部门人数修改endgocreate procedure部门人数修改asselect count(所在部门号)as部门人数，所在部门号 into #temp from员工表 group by所在部门号declare maxNum char(4),minNum char(4),n intselect maxNum=(select max(部门号)from 部门表select minNum=(select min(部门号)from 部门表 while(cast(minNum as int)=cast(maxNum as int) beginselect n=部门人数from #tempwhere所在部门号=minNumupdate部门表set部门人数=nwhere 部门号=minNumset minNum=cast(cast(minNum as int)+1) as char(4)end
