实验九游标与存储过程1 实验目的与要求(1) 掌握游标的定义和使用方法。

(2) 掌握存储过程的定义、执行和调用方法。

(3) 掌握游标和存储过程的综合应用方法。

2 实验内容请完成以下实验内容：(1) 创建游标，逐行显示Customer表的记录，并用WHILE结构来测试@@Fetch_Status 的返回值。

输出格式如下：'客户编号'+'-----'+'客户名称'+'----'+'客户住址'+'-----'+'客户电话'+'------'+'邮政编码' declare @cno char(9),@cname varchar(20),@tele varchar(20),@addr varchar(12),@zi char(7)declare @text varchar(180)declare cus_cur scroll cursor forselect customerNo,customerName,telephone,address,zipfrom customerorder by customerNoset@text='===============================customer======================= ====================='print @textset @text='客户编号'+' '+'客户名称'+' '+'客户电话'+' '+'客户住址'+' '+'邮政编码'print @textopen cus_curfetch cus_cur into @cno,@cname,@tele,@addr,@ziwhile(@@fetch_status=0)beginset @text = @cno+' '+@cname+''+space(2*(9-len(@cname)))+@tele+' '+@addr+' '+@ziprint @textfetch cus_cur into @cno,@cname,@tele,@addr,@ziendclose cus_curdeallocate cus_cur(2) 利用游标修改OrderMaster表中orderSum的值。

declare @no varchar(12),@sumprice varchar(10)declare @text varchar(100)declare cur_ordersum scroll cursor forselect orderNo,sum(quantity*price)from orderdetail odgroup by orderNoopen cur_ordersumfetch cur_ordersum into @no,@sumpricewhile(@@fetch_status=0)beginupdate ordermasterset ordersum = @sumpricewhere orderno=@nofetch cur_ordersum into @no,@sumpriceendclose cur_ordersumdeallocate cur_ordersumselect*from ordermaster(3) 创建游标，要求：输出所有女业务员的编号、姓名、性别、所属部门、职务、薪水。

declare @no varchar(12),@name varchar(4),@sex varchar(2),@dpvarchar(10),@hship varchar(8),@money numeric(7,2)declare @text varchar(50)declare cur_employee scroll cursor forselect employeeno,employeename,sex,department,headship,salaryfrom employeewhere sex='f'open cur_employeefetch cur_employee into @no,@name,@sex,@dp,@hship,@moneyprint'员工编号 '+'姓名 '+'性别'+'所属部门 '+'职务 '+'薪水'while(@@fetch_status=0)beginset@text =@no+' '+@name+space(6-2*len(@name))+@sex+' '+@dp+' '+@hship+' '+convert(char(9),@money)print @textfetch cur_employee into @no,@name,@sex,@dp,@hship,@moneyendclose cur_employeedeallocate cur_employee(4) 创建存储过程，要求：按表定义中的CHECK约束自动产生员工编号。

create procedure pro_employeeno @name varchar,@sex varchar,@birthday varchar,@address varchar,@telephone varchar,@hiredatevarchar,@department varchar,@headship varchar,@salary varcharasdeclare @y varchar,@countt int,@no varchardeclare cur_employeeno scroll cursor forselect year(hiredate),count(*)from employeegroup by year(hiredate)open cur_employeenofetch cur_employeeno into @y,@counttwhile(@@fetch_status=0)beginif(@y =convert(varchar,year(@hiredate)))beginif(@countt<9)set @no ='E'+@y+'00'+convert(varchar,@countt+1)else if(@countt<99)set @no ='E'+@y+'0'+convert(varchar,@countt+1)elseset @no ='E'+@y+convert(varchar,@countt+1)endfetch cur_employeeno into @y,@counttendinsert Employeevalues(@no,@name,@sex,@birthday,@address,@telephone,@hiredate,@depart ment,@headship,@salary)close cur_employeenodeallocate cur_employeeno(5) 创建存储过程，要求：查找姓“李”的职员的员工编号、订单编号、订单金额。

create procedure pro_employeeLiasselect employeeno,orderno,ordersumfrom employee e,ordermaster omwhere e.employeename like'李%'exec pro_employeeLi --结果有两张表？(6) 创建存储过程，要求：统计每个业务员的总销售业绩，显示业绩最好的前3位业务员的销售信息。

create procedure pro_employeetop3asselect top 3 temp.employeeno,sum(ordersum)hefrom(select employeeno,ordersumfrom employee e,ordermaster omwhere employeeno=salerno)tempgroup by temp.employeenoorder by he desc(7)创建存储过程，要求将大客户(销售数量位于前5名的客户)中热销的前3种商品的销售信息按如下格式输出：=======大客户中热销的前3种商品的销售信息================商品编号商品名称总销售数量P2******* 120GB硬盘 21.00P2******* 3.5寸软驱 18.00P2******* 网卡 16.00create procedure pro_producttop3asdeclare @no varchar(12),@name varchar(10),@he varchar(10)declare cur_producttop3 scroll cursor forselect top 3 od.productno 商品编号,productname 商品名称,sum(quantity)总量from ordermaster om,orderdetail od,product p,(select top 5 customerno,sum(quantity) hefrom orderdetail od,ordermaster omwhere od.orderno=om.ordernogroup by customernoorder by he desc) tempwhere om.customerno = temp.customernoand om.orderno = od.ordernoand p.productno = od.productnogroup by od.productno,productnameorder by总量descopen cur_producttop3fetch cur_producttop3 into @no,@name,@heprint'=======大客户中热销的前种商品的销售信息==============='print' 商品编号 '+'商品名称 '+'商品销售数量'while(@@fetch_status=0)beginprint' '+@no+' '+@name+space((16-len(@name)))+@hefetch cur_producttop3 into @no,@name,@heendclose cur_producttop3deallocate cur_producttop3(8) 创建存储过程，要求：输入年度，计算每个业务员的年终奖金。