实验十一游标与存储过程(1)创建游标，逐行显示表Customer.的记录，并用WHILE结构来测试@@Fetch_Status的返回值。

输出格式如下：'客户编号'+'-----'+'客户名称'+'----'+'客户地址'+'-----'+'客户电话'+'------'+'客户邮编'+'------'DECLARE cur_cust SCROLL cursor FORSELECT*FROM customerDECLARE @p_CustId char(5)DECLARE @p_CustName char(20)DECLARE @p_address char(40)DECLARE @p_Tel char(10)DECLARE @p_Zip char(6)DECLARE @p_All char(100)SELECT @p_All='客户编号'+'------'+'客户名称'+'------'+'客户地址'+'-------------------------------------'+'客户电话'+'-------'+'客户邮编'+'------'PRINT @p_AllOPEN cur_custFETCH cur_cust into @p_CustId,@p_CustName,@p_address,@p_Tel,@p_Zip WHILE(@@fetch_status<>-1)BEGINSELECT @p_All=@p_CustId+' '+@p_CustName+@p_address+@p_Tel+' '+@p_Zipprint @p_AllFETCH cur_cust into @p_CustId,@p_CustName,@p_address,@p_Tel,@p_Zip ENDPRINT'客户数目: '+CONVERT(char(5),@@CURSOR_ROWS)CLOSE cur_custDEALLOCATE cur_cust(2)利用游标修改OrderMaster表中Ordersum的值。

update ordermasterset ordersum=0declare @orderno char(12)declare @total numeric(9,2)declare cur_orderdetail scroll cursor forselect orderno,sum(quantity*price)from orderdetailgroup by ordernoopen cur_orderdetailfetch next from cur_orderdetail into @orderno,@total while(@@fetch_status=0)beginupdate ordermaster set ordersum=@totalwhere orderno=@ordernofetch next from cur_orderdetail into @orderno,@total endclose cur_orderdetaildeallocate cur_orderdetailselect*from ordermaster(3)创建游标，要求：输出所有女业务员的编号、姓名、性别、部门、职务、入职日期工资。

declare @emp_no char(5),@emp_name char(10),@sex char(1),@dept char(4) declare @date_hired datetime,@salary int,@text char(100)declare female_emp_cur cursor forselect employeeno,employeename,sex,department,hiredate,salaryfrom employee where sex='F'select @text='==================女业务员情况列表================='print @textselect @text=' 编号姓名性别部门职务入职日期工资'print @textselect @text='--------------------------------------------------'print @textopen female_emp_curfetch female_emp_cur into@emp_no,@emp_name,@sex,@dept,@date_hired,@salarywhile(@@fetch_status=0)beginselect @text=@emp_no+' '+@emp_name+' '+@sex+' '+@dept+' '+''+convert(char(4),year(@date_hired))+convert(char(2),month(@date_hire d))+' '+convert(char(6),@salary)print @textfetch female_emp_cur into@emp_no,@emp_name,@sex,@dept,@date_hired,@salaryendclose female_emp_curdeallocate female_emp_cur(4)创建存储过程，要求：按表定义中的CHECK约束自动产生员工编号。

create procedure createEmployeeNo(@sYear char(4),@sEmployeeNo char(8) output)asbegindeclare @employeeNo char(8),@num intdeclare @sNum char(3)select @employeeNo=max(employeeNo)from Employeewhere employeeNo like'E'+@sYear+'%'set @num=isnull(convert(int,right(@employeeNo,3)),0)+1if @num<10set @sNum='00'+convert(char(1),@num)elseif @num<100set @sNum='0'+convert(char(2),@num)elseset @sNum=convert(char(3),@num)set @sEmployeeNo='E'+@sYear+@sNumendgodeclare @sEmployeeNo char(8)execute createEmployeeNo'2006',@sEmployeeNo outputselect @sEmployeeNo 员工编号结果：(5)创建存储过程，要求：查找姓“李”的“职员”的员工编号、订单编号、销售金额。

create procedure employee_tot @e_name varchar(10),@e_headship varchar(10)asbegindeclare @emp_no char(10),@order_no char(12),@order_sum char(9) declare @text char(100)declare get_tot cursor forselect employeeno,b.orderno,ordersumfrom employee a,ordermaster bwhere employeename like @e_name and headship=@e_headship and a.employeeno=b.salernoselect @text='员工编号订单编号销售金额'print @textopen get_totfetch get_tot into @emp_no,@order_no,@order_sumwhile(@@fetch_status=0)beginselect @text=@emp_no+' '+@order_no+' '+@order_sumprint @textfetch get_tot into @emp_no,@order_no,@order_sumendclose get_totdeallocate get_totendgoexec employee_tot @e_name='李%', @e_headship='职员'(6)创建存储过程，要求：统计每个业务员的总销售业绩，显示业绩最好的前3位业务员的销售信息。

create procedure emp_totasbegindeclare@emp_no char(8),@emp_name char(10),@order_num numeric(9,2) declare@pro_no char(9)declare @text varchar(100),@count intset @count=0select @text='=======业绩最好的前三位业务员的销售信息================' print @textselect @text='业务员编号业务员姓名总销售业绩'print @textdeclare get_tot cursor forselect employeeno,employeename,sum(ordersum)from employee a,ordermaster bwhere a.employeeno=b.salernogroup by employeeno,employeenameorder by sum(ordersum)descopen get_totfetch get_tot into @emp_no, @emp_name,@order_numwhile(@@fetch_status=0)beginselect @text=@emp_no+' '+ @emp_name+''+convert(char(10),@order_num)print @textset @count=@count+1if @count<3fetch get_tot into @emp_no, @emp_name,@order_numelsebreakendclose get_totdeallocate get_totendgoexec emp_tot(7)创建存储过程，要求将大客户(销售数量位于前5名的客户)中热销的前3种商品的销售信息按如下格式输出：=======大客户中热销的前种商品的销售信息================商品编号商品名称总销售金额P2******* Pentium100CPU 22400.00P2******* Pentium主板 15130.00P2******* 17寸显示器 10600.00create procedure cus_totasbegindeclare @prod_no char(9),@prod_name char(20),@order_num numeric(9,2) declare @text varchar(100),@count intset @count=0select @text='=======大客户中热销的前种商品的销售信息================' print @textselect @text='商品编号商品名称总销售金额'print @textdeclare get_tot cursor forselect a.productNo,productName,sum(quantity*price)from Product a,OrderDetail b,(select top 5a.customerNo,customerName,sum(orderSum)客户总金额from Customer a,OrderMaster bwhere a.customerNo=b.customerNogroup by a.customerNo,customerNameorder by sum(orderSum)desc)c,OrderMaster dwhere a.productNo=b.productNo and c.customerNo=d.customerNo and b.orderNo=d.orderNogroup by a.productNo,productNameorder by sum(quantity*price)descopen get_totfetch get_tot into @prod_no, @prod_name,@order_numwhile(@@fetch_status=0)beginselect @text=@prod_no+' '+ @prod_name+''+convert(char(10),@order_num)print @textset @count=@count+1if @count<3fetch get_tot into @prod_no, @prod_name,@order_numelsebreakendclose get_totdeallocate get_tot endgoexec cus_tot(8)创建存储过程，要求：输入年度，计算每个业务员的年终奖金额。