大型数据库实验6存储过程.doc
文本预览下载声明
一．实验内容
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
显示全部