大型数据库实验6存储过程.docVIP

一．实验内容 1）创建并执行不带参数的存储过程 ①针对项目表创建名为“P1_存储过程”的存储过程，要求显示所有记录。 ②执行“P1_存储过程” 存储过程进行数据浏览。 2）创建并执行带输入参数的存储过程 ①部门人数应该等于员工表中对应部门实际员工数，由于有员工调入调出，可能存在不等的情况。编写存储过程“P2_存储过程”，检查指定部门人数的正确性，如果不正确，则进行修改。 ②显示部门表和员工表数据；然后执行存储过程；再显示部门表和员工表数据，比较数据是否变化。 3）创建带OUTPUT输出参数的存储过程 ①设计存储过程“P3_存储过程”，从员工表计算某部门人员平均工资。要求输入参数为部门号，输出参数是该部门的平均工资。 ②编写主程序，调用存储过程，在主程序中显示指定部门的平均工资。 4）创建并执行带输入参数和返回状态的存储过程 ①设计存储过程“P4_存储过程”，完成对员工表的元组插入工作。要求使用输入参数。插入操作成功返出状态值0，失败返出状态值-1。 ②执行存储过程，如果返回状态值为0，输出“数据插入成功”，否则输出“数据插入失败”。 5）修改和删除存储过程 ①修改“P1_存储过程”存储过程，要求指定项目编号作为输入参数，并增加WITH ENCRYPTION选项。 ②查看修改后的“P1_存储过程”存储过程文本。 ③执行“P1_存储过程”存储过程 ④删除“P1_存储过程”存储过程。 二．测试数据与实验结果 1、创建并执行不带参数的存储过程 a、针对项目表创建名为“P1_存储过程”的存储过程，显示所有记录，代码如下： create procedure P1_存储过程 as select * from 项目表 b、执行“P1_存储过程” 存储过程进行数据浏览，结果如下： 2、创建并执行带输入参数的存储过程 a、编写存储过程“P2_存储过程”，其中利用游标逐行检查部门人数是否和在员工表中的数据相符合，如果不符合，则利用游标进行定位修改，代码如下： create procedure P2_存储过程 as declare @部门号 char(6),@部门人数 int declare @n int declare 部门表_cursor cursor for select 部门号,部门人数 from 部门表 for update of 部门人数 open 部门表_cursor fetch next from 部门表_cursor into @部门号,@部门人数 while @@FETCH_STATUS=0 begin select @部门号,@部门人数 select @n=COUNT(员工号) from 员工表 where 所在部门号=@部门号 update 部门表 set 部门人数=@n where current of 部门表_cursor fetch next from 部门表_cursor into @部门号,@部门人数 end close 部门表_cursor deallocate 部门表_cursor 执行存储过程，代码如下： exec P2_存储过程 b、显示部门表和员工表数据，然后执行存储过程，可以看出，在执行存储过程之前，“办公室”的人数和员工表中的人数是不相符的，在执行存储过程之后，部门人数成功更新，如下图所示： 图1 员工表中的数据 图2执行“P2_存储过程”之前的部门表数据 图3 执行“P2_存储过程”之后的部门表数据 3、创建带OUTPUT输出参数的存储过程 a、设计存储过程“P3_存储过程”，从员工表计算某部门人员平均工资，输入参数为部门号，输出参数是该部门的平均工资 ，创建的过程如下： create procedure P3_存储过程(@部门号 char(10),@部门平均工资 float output) as select @部门平均工资=AVG(工资) from 员工表 where 所在部门号=@部门号declare @部门平均工资 float exec P3_存储过程 1011,@部门平均工资 output select 部门平均工资=@部门平均工资create procedure P4_存储过程 (@员工号 char(4),@姓名 varchar(20),@性别 char(2), @出生年月 varchar(60),@技术职称 char(10),@工资 int ,@所在部门号 char(6),@参加的项目总数 int) as begin transaction insert into 员工表 values (@员工号,@姓名,@性别,cast(@出生年月 as datetime),@技术职称,@工资,@所在部门号,@参加的项目总数) if @@error0