实验游标和存储过程
实验九游标与存储过程
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所示。
===================订单及其明细数据信息==================== --------------------------------------------------- 订单编号 200801090001
--------------------------------------------------- 商品编号数量价格
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
/*执行过程*/
exec saler_tot
/* (7) 创建存储过程，要求将大客户(销售数量位于前5名的客户)中热销的前3种商品的销售信息按如下格式输出：
=======大客户中热销的前种商品的销售信息================
商品编号商品名称总销售数量
P2******* 120GB硬盘 21.00
P2******* 3.5寸软驱 18.00
P2******* 网卡 16.00*/
create procedure product_tot
as
declare @proNo char(10),@proNa char(20),@total int
declare @text char(100)
declare sale_cur scroll cursor for
select top 3 a.productNo,a.productName,sum(c.quantity)
from Product62 a,OrderMaster62 b,OrderDetail62 c
where a.productNo=c.productNo and b.orderNo=c.orderNo and
b.customerNo in(select top 5 m.customerNo
from OrderMaster62 m,OrderDetail62 n
where m.orderNo=n.orderNo
group by m.customerNo
order by sum(quantity)desc)
group by a.productNo,a.productName
order by sum(c.quantity)desc
select @text='=======大客户中热销的前种商品的销售信息======'
print @text
select @text='商品编号商品名称总销售数量'
print @text
open sale_cur
fetch sale_cur into @proNo,@proNa,@total
while(@@fetch_status=0)
begin
select @text=@proNo+' '+@proNa+' '+convert(char(10),@total)
print@text
fetch sale_cur into @proNo,@proNa,@total
end
close sale_cur
deallocate sale_cur
/*执行过程*/
exec product_tot
/*(8) 创建存储过程，要求：输入年度，计算每个业务员的年终奖金。年终奖金＝年销售总额×提成率。提成率规则如下：年销售总额元以下部分，提成率为％，对于元及超过元部分，则提成率为％*/
create procedure pride_tot @date int
as
declare @saleNo char(15),@total numeric(9,2)
declare @text char(100),@money numeric(8,2)
declare pride_cur scroll cursor for
select salerNo,sum(orderSum)
from OrderMaster62
where year(orderDate)=@date
group by salerNo
select @text='=========业务员的年终奖金========='
print @text
select @text='业务员编号年终奖金'
print @text
open pride_cur
fetch pride_cur into @saleNo,@total
while(@@fetch_status=0)
begin
if(@total<5000)
select @money=@total*0.1
else
select @money=500+(@total-5000)*0.15
select @text=@saleNo+' '+convert(char(10),@money)
print @text
fetch pride_cur into @saleNo,@total
end
close pride_cur
deallocate pride_cur
/*执行过程*/
exec pride_tot 2012
/*(9) 创建存储过程，要求将OrderMaster62表中每一个订单所对应的明细数据信息按规定
格式输出，格式如图-1所示。
===================订单及其明细数据信息==================== ---------------------------------------------------
订单编号 200801090001
---------------------------------------------------
商品编号数量价格
P2******* 5 403.50
P2******* 3 2100.00
P2******* 2 600.00
---------------------------------------------------
合计订单总金额 3103.50
图-1 订单及其明细数据信息*/
create procedure orderm_tot @orderno char(15)
as
declare @prono char(15),@quantity int,@price numeric(9,2)
declare @text char(100)
declare orderm_cur scroll cursor for
select productNo,sum(quantity),sum(quantity*price)
from OrderDetail62
where orderNo=@orderno
group by productNo
select @text='=============订单及其明细数据信息================'
print @text
select @text='-------------------------------------------------'
print @text
select @text='订单编号'+@orderno
print @text
select @text='-------------------------------------------------'
print @text
select @text='商品编号数量价格'
print @text
open orderm_cur
fetch orderm_cur into @prono,@quantity,@price
while(@@fetch_status=0)
begin
select @text=@prono+' '+convert(char(5),@quantity)+'
'