实验九游标与存储过程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.00P2******* 3.5寸软驱 18.00P2******* 网卡 16.00(8) 创建存储过程，要求：输入年度，计算每个业务员的年终奖金。
年终奖金＝年销售总额×提成率。
提成率规则如下：年销售总额5000元以下部分，提成率为10％，对于5000元及超过5000元部分，则提成率为15％。
(9) 创建存储过程，要求将OrderMaster表中每一个订单所对应的明细数据信息按规定格式输出，格式如图7-1所示。
===================订单及其明细数据信息====================--------------------------------------------------- 订单编号 200801090001--------------------------------------------------- 商品编号数量价格P2******* 5 403.50P2******* 3 2100.00P2******* 2 600.00--------------------------------------------------- 合计订单总金额 3103.50图7-1 订单及其明细数据信息(10) 请使用游标和循环语句创建存储过程proSearchCustomer，根据客户编号查找该客户的名称、住址、总订单金额以及所有与该客户有关的商品销售信息，并按商品分组输出。
输出格式如图7-2所示。
===================客户订单表====================--------------------------------------------------- 客户名称：统一股份有限公司客户地址：天津市总金额： 31121.86--------------------------------------------------- 商品编号总数量平均价格P2******* 5 80.70P2******* 19 521.05P2******* 5 282.00P2******* 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 forselect*from Customer62select@text='================================Customer62表的记录===================='print @textselect@text='客户编号'+'------'+'客户名称'+'-----------'+'客户电话'+'-------'+'客户住址'+'------'+'邮政编码'print @textselect@text='======================================================================'print @textopen cus_curfetch cus_cur into @C_no,@C_name,@C_phone,@C_add,@C_zipwhile(@@fetch_status=0)beginselect@text=@C_no+' '+@C_name+' '+@C_phone+' '+@C_add+''+@C_zipprint @textfetch cus_cur into @C_no,@C_name,@C_phone,@C_add,@C_zip endclose cus_curdeallocate cus_cur/*(2) 利用游标修改OrderMaster表中orderSum的值*/declare @orderNo varchar(20),@total numeric(9,2)declare om_cur cursor forselect orderNo,sum(quantity*price)from OrderDetail62group by orderNoopen om_curfetch om_cur into @orderNo,@totalwhile(@@fetch_status=0)beginupdate OrderMaster62set orderSum=@totalwhere orderNo=@orderNofetch om_cur into @orderNo,@totalendclose om_curdeallocate 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 forselect employeeNo,employeeName,sex,department,headShip,salaryfrom Employee62where sex='M'select @text='=====================================================' print @textselect @text='编号姓名性别所属部门职务薪水'print @textselect @text='=====================================================' print @textopen em_curfetch em_cur into @emNo,@emNa,@emse,@emde,@emhe,@emsawhile(@@fetch_status=0)beginselect @text=@emNo+' '+@emNa+' '+@emse+' '+@emde+' '+@emhe +' '+convert(char(10),@emsa)print @textfetch em_cur into @emNo,@emNa,@emse,@emde,@emhe,@emsaendclose em_curdeallocate em_cur/*(4) 创建存储过程，要求：按表定义中的CHECK约束自动产生员工编号*/create table Rnum(number char(8)null,ename char(10)null)--先创建一张新表用来存储已经产生的员工编号create procedure no_tot(@name nvarchar(50))asbegindeclare @i int,@text char(100)set @i=1while(@i<1000)beginif exists(select numberfrom Rnumwherenumber=('E'+convert(char(4),year(getdate()))+right('00'+convert(varchar(3),@i),3))) beginset @i=@i+1continueendelsebegininsert Rnum values(('E'+convert(char(4),year(getdate()))+right('00'+convert(varchar(3),@i),3)),@name)select @text='员工编号'+' '+'员工姓名'print @textselect@text=('E'+convert(char(4),year(getdate()))+right('00'+convert(varchar(3),@i),3))+' '+@name--这里的两个数字'3' 就是我们要设置的id长度print @textbreakendendend/*执行过程*/exec no_tot 张三/*(5) 创建存储过程，要求：查找姓“李”的职员的员工编号、订单编号、订单金额*/ create procedure emli_tot @emNo char(8)asselect a.employeeNo 员工编号,b.orderNo 订单编号,b.orderSum 订单金额from Employee62 a,OrderMaster62 bwhere a.employeeNo=b.salerNo and a.employeeName like'@emNo'/*执行过程*/exec emli_tot '李%'/*(6) 创建存储过程，要求：统计每个业务员的总销售业绩，显示业绩最好的前3位业务员的销售信息*/create procedure saler_totasselect top 3 salerNo 业务员编号,sum(orderSum)总销售业绩from OrderMaster62group by salerNoorder by sum(orderSum)desc/*执行过程*/exec saler_tot/*(7) 创建存储过程，要求将大客户(销售数量位于前5名的客户)中热销的前3种商品的销售信息按如下格式输出：=======大客户中热销的前种商品的销售信息================商品编号商品名称总销售数量P2******* 120GB硬盘21.00P2******* 3.5寸软驱18.00P2******* 网卡16.00*/create procedure product_totasdeclare @proNo char(10),@proNa char(20),@total intdeclare @text char(100)declare sale_cur scroll cursor forselect top 3 a.productNo,a.productName,sum(c.quantity)from Product62 a,OrderMaster62 b,OrderDetail62 cwhere a.productNo=c.productNo and b.orderNo=c.orderNo andb.customerNo in(select top 5 m.customerNofrom OrderMaster62 m,OrderDetail62 nwhere m.orderNo=n.orderNogroup by m.customerNoorder by sum(quantity)desc)group by a.productNo,a.productNameorder by sum(c.quantity)descselect @text='=======大客户中热销的前种商品的销售信息======'print @textselect @text='商品编号商品名称总销售数量'print @textopen sale_curfetch sale_cur into @proNo,@proNa,@totalwhile(@@fetch_status=0)beginselect @text=@proNo+' '+@proNa+' '+convert(char(10),@total)print @textfetch sale_cur into @proNo,@proNa,@totalendclose sale_curdeallocate sale_cur/*执行过程*/exec product_tot/*(8) 创建存储过程，要求：输入年度，计算每个业务员的年终奖金。