实验九 游标与存储过程
实验九游标与存储过程
1 实验目的与要求
(1) 掌握游标的定义和使用方法。
(2) 掌握存储过程的定义、执行和调用方法。
(3) 掌握游标和存储过程的综合应用方法。
2 实验内容
请完成以下实验内容：
(1) 创建游标，逐行显示Customer表的记录，并用WHILE结构来测试@@Fetch_Status
的返回值。输出格式如下：
'客户编号'+'-----'+'客户名称'+'----'+'客户住址'+'-----'+'客户电话'+'------'+'邮政编码'
(2) 利用游标修改OrderMaster表中orderSum的值。
(3) 创建游标，要求：输出所有女业务员的编号、姓名、性别、所属部门、职务、薪水。
(4) 创建存储过程，要求：按表定义中的CHECK约束自动产生员工编号。
(5) 创建存储过程，要求：查找姓“李”的职员的员工编号、订单编号、订单金额。
(6) 创建存储过程，要求：统计每个业务员的总销售业绩，显示业绩最好的前3位业务
员的销售信息。
(7)创建存储过程，要求将大客户(销售数量位于前5名的客户)中热销的前3种商品的
销售信息按如下格式输出：
=======大客户中热销的前3种商品的销售信息================
商品编号商品名称总销售数量
P2******* 120GB硬盘 21.00
P2******* 3.5寸软驱 18.00
P2******* 网卡 16.00
(8) 创建存储过程，要求：输入年度，计算每个业务员的年终奖金。年终奖金＝年销售
总额×提成率。提成率规则如下：年销售总额5000元以下部分，提成率为10％，对于5000
元及超过5000元部分，则提成率为15％。
(9) 创建存储过程，要求将OrderMaster表中每一个订单所对应的明细数据信息按规定
格式输出，格式如图7-1所示。
===================订单及其明细数据信息====================
--------------------------------------------------- 订单编号 2
--------------------------------------------------- 商品编号数量价格
P2******* 5 403.50
P2******* 3 2100.00
P2******* 2 600.00
--------------------------------------------------- 合计订单总金额 3103.50
图7-1 订单及其明细数据信息
(10) 请使用游标和循环语句创建存储过程proSearchCustomer，根据客户编号查找该客户的
名称、住址、总订单金额以及所有与该客户有关的商品销售信息，并按商品分组输出。输出
格式如图7-2所示。
===================客户订单表====================
--------------------------------------------------- 客户名称：统一股份有限公司
客户地址：天津市
总金额： 31121.86
--------------------------------------------------- 商品编号总数量平均价格
P2******* 5 80.70
P2******* 19 521.05
P2******* 5 282.00
P2******* 2 320.00
报表制作人陈辉制作日期 06 8 2012
图7-2 客户订单表
实验脚本：
/*(1) 创建游标，逐行显示Customer表的记录，并用WHILE结构来测试@@Fetch_Status的返
回值。
输出格式如下：
'客户编号'+'-----'+'客户名称'+'----'+'客户电话'+'-----'+'客户住址'+'------'+'邮政编码'*/
declare @C_no char(9),@C_name char(18),@C_phone char(10),
@C_add char(8),@C_zip char(6)
declare @text char(100)
declare cus_cur scroll cursor for
select*
from Customer62
select@text='================================Customer62表的记录
===================='
print @text
select@text='客户编号'+'------'+'客户名称'+'-----------'+'客户电话'+'-------'+'客户住址'+'------'+'
邮政编码'
print @text
select
@text='================================================================
======'
print @text
open cus_cur
fetch cus_cur into @C_no,@C_name,@C_phone,@C_add,@C_zip
while(@@fetch_status=0)
begin
select@text=@C_no+' '+@C_name+' '+@C_phone+' '+@C_add+'
'+@C_zip
print @text
fetch cus_cur into @C_no,@C_name,@C_phone,@C_add,@C_zip end
close cus_cur
deallocate cus_cur
/*(2) 利用游标修改OrderMaster表中orderSum的值*/
declare @orderNo varchar(20),@total numeric(9,2)
declare om_cur cursor for
select orderNo,sum(quantity*price)
from OrderDetail62
group by orderNo
open om_cur
fetch om_cur into @orderNo,@total
while(@@fetch_status=0)
begin
update OrderMaster62
set orderSum=@total
where orderNo=@orderNo
fetch om_cur into @orderNo,@total
end
close om_cur
deallocate om_cur
/*(3) 创建游标，要求：输出所有女业务员的编号、姓名、性别、所属部门、职务、薪水*/ declare @emNo varchar(8),@emNa char(8),@emse char(1),@emde varchar(10),
@emhe varchar(8),@emsa numeric(8,2)
declare @text char(100)
declare em_cur scroll cursor for
select employeeNo,employeeName,sex,department,headShip,salary
from Employee62
where sex='M'
select @text='=====================================================' print @text
select @text='编号姓名性别所属部门职务薪水'
print @text
select @text='=====================================================' print @text
open em_cur
fetch em_cur into @emNo,@emNa,@emse,@emde,@emhe,@emsa
while(@@fetch_status=0)
begin
select @text=@emNo+' '+@emNa+' '+@emse+' '+@emde+' '+@emhe +' '+convert(char(10),@emsa)
print @text
fetch em_cur into @emNo,@emNa,@emse,@emde,@emhe,@emsa
end
close em_cur
deallocate em_cur
/*(4) 创建存储过程，要求：按表定义中的CHECK约束自动产生员工编号*/
create table Rnum(
number char(8)null,
ename char(10)null
)--先创建一张新表用来存储已经产生的员工编号
create procedure no_tot(@name nvarchar(50))
as
begin
declare @i int,@text char(100)
set @i=1
while(@i<1000)
begin
if exists(select number
from Rnum
where
number=('E'+convert(char(4),year(getdate()))+right('00'+convert(varchar(3),@i),3))) begin
set @i=@i+1
continue
end
else
begin
insert Rnum values(('E'+convert(char(4),year(getdate()))+right('00'+convert(varchar(3),@i),3)),@name)
select @text='员工编号'+' '+'员工姓名'
print @text
select
@text=('E'+convert(char(4),year(getdate()))+right('00'+convert(varchar(3),@i),3))+' '+@name
--这里的两个数字'3' 就是我们要设置的id长度
print @text
break
end
end
end
/*执行过程*/
exec no_tot 张三
/*(5) 创建存储过程，要求：查找姓“李”的职员的员工编号、订单编号、订单金额*/ create procedure emli_tot @emNo char(8)
as
select a.employeeNo 员工编号,b.orderNo 订单编号,b.orderSum 订单金额
from Employee62 a,OrderMaster62 b
where a.employeeNo=b.salerNo and a.employeeName like'@emNo'
/*执行过程*/
exec emli_tot '李%'
/*(6) 创建存储过程，要求：统计每个业务员的总销售业绩，显示业绩最好的前3位业务员的销售信息*/
create procedure saler_tot
as
select top 3 salerNo 业务员编号,sum(orderSum)总销售业绩
from OrderMaster62
group by salerNo
order by sum(orderSum)desc