数据库实验十二--游标与存储过程

实验九游标与存储过程

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 scrollcursorfor

select customerNo,customerName,telephone,address,zip

from customer

orderby customerNo

set

@text='===============================customer======================= ====================='

print @text

set @text='客户编号'+' '+'客户名称'+' '+'客户电话'+' '+'客户住址'+' '+'邮政编码'

print @text

open cus_cur

fetch cus_cur into @cno,@cname,@tele,@addr,@zi

while(@@fetch_status=0)

begin

set @text = @cno+' '+@cname+'

'+space(2*(9-len(@cname)))+@tele+' '+@addr+' '+@zi

print @text

fetch cus_cur into @cno,@cname,@tele,@addr,@zi

end

close cus_cur

deallocate cus_cur

(2)利用游标修改OrderMaster表中orderSum的值。

declare @no varchar(12),@sumprice varchar(10)

declare @text varchar(100)

declare cur_ordersum scrollcursorfor

select orderNo,sum(quantity*price)

from orderdetail od

groupby orderNo

open cur_ordersum

fetch cur_ordersum into @no,@sumprice

while(@@fetch_status=0)

begin

update ordermaster

set ordersum = @sumprice

where orderno=@no

fetch cur_ordersum into @no,@sumprice

end

close cur_ordersum

deallocate cur_ordersum

select*

from ordermaster

(3)创建游标，要求：输出所有女业务员的编号、姓名、性别、所属部门、职务、薪水。declare @no varchar(12),@name varchar(4),@sex varchar(2),@dp

varchar(10),@hship varchar(8),@money numeric(7,2)

declare @text varchar(50)

declare cur_employee scrollcursorfor

select employeeno,employeename,sex,department,headship,salary

from employee

where sex='f'

open cur_employee

fetch cur_employee into @no,@name,@sex,@dp,@hship,@money

print'员工编号 '+'姓名 '+'性别'+'所属部门 '+'职务 '+'薪水'

while(@@fetch_status=0)

begin

set@text =@no+' '+@name+space(6-2*len(@name))+@sex+' '+@dp+' '+@hship+' '+convert(char(9),@money)

print @text

fetch cur_employee into @no,@name,@sex,@dp,@hship,@money

end

close cur_employee

deallocate cur_employee

(4)创建存储过程，要求：按表定义中的CHECK约束自动产生员工编号。createprocedure pro_employeeno @name varchar,@sex varchar,@birthday varchar,@address varchar,@telephone varchar,@hiredate

varchar,@department varchar,@headship varchar,@salary varchar

as

declare @y varchar,@countt int,@no varchar

declare cur_employeeno scrollcursorfor

select year(hiredate),count(*)

from employee

groupby year(hiredate)

open cur_employeeno

fetch cur_employeeno into @y,@countt

while(@@fetch_status=0)

begin

if(@y =convert(varchar,year(@hiredate)))

begin

if(@countt<9)

set @no ='E'+@y+'00'+convert(varchar,@countt+1)

elseif(@countt<99)

set @no ='E'+@y+'0'+convert(varchar,@countt+1)

else

set @no ='E'+@y+convert(varchar,@countt+1)

end

fetch cur_employeeno into @y,@countt

end

insert Employee

values(@no,@name,@sex,@birthday,@address,@telephone,@hiredate,@depart ment,@headship,@salary)

close cur_employeeno

deallocate cur_employeeno

(5)创建存储过程，要求：查找姓“李”的职员的员工编号、订单编号、订单金额。createprocedure pro_employeeLi

as

select employeeno,orderno,ordersum

from employee e,ordermaster om

where e.employeename like'李%'

exec pro_employeeLi --结果有两张表？

(6)创建存储过程，要求：统计每个业务员的总销售业绩，显示业绩最好的前3位业务员的销售信息。

createprocedure pro_employeetop3

as

selecttop 3 temp.employeeno,sum(ordersum)he

from(select employeeno,ordersum

from employee e,ordermaster om

where employeeno=salerno

)temp

groupby temp.employeeno

order by he desc

(7)创建存储过程，要求将大客户(销售数量位于前5名的客户)中热销的前3种商品的销售信息按如下格式输出：

=======大客户中热销的前3种商品的销售信息================

商品编号商品名称总销售数量

P2******* 120GB硬盘 21.00

P2******* 3.5寸软驱 18.00

P2******* 网卡 16.00

createprocedure pro_producttop3

as

declare @no varchar(12),@name varchar(10),@he varchar(10)

declare cur_producttop3 scrollcursorfor

selecttop 3 od.productno 商品编号,productname 商品名称,sum(quantity)总量from ordermaster om,orderdetail od,product p,(selecttop 5 customerno,sum(quantity) he

from orderdetail od,ordermaster om

where od.orderno=om.orderno

groupby customerno

orderby he desc) temp

where om.customerno = temp.customerno

and om.orderno = od.orderno

and p.productno = od.productno

groupby od.productno,productname

orderby总量desc

open cur_producttop3

fetch cur_producttop3 into @no,@name,@he

print'=======大客户中热销的前种商品的销售信息==============='

print' 商品编号 '+'商品名称 '+'商品销售数量'

while(@@fetch_status=0)

begin

print' '+@no+' '+@name+space((16-len(@name)))+@he

fetch cur_producttop3 into @no,@name,@he

end

close cur_producttop3

deallocate cur_producttop3

(8)创建存储过程，要求：输入年度，计算每个业务员的年终奖金。年终奖金＝年销售总额×提成率。提成率规则如下：年销售总额5000元以下部分，提成率为10％，对于5000元及超过5000元部分，则提成率为15％。

createprocedure pro_yearmoney @ye varchar(5)

as

declare @no varchar(12),@he varchar(9),@temp float

set @temp = 0

declare cur_yearmoney scrollcursorfor

select salerno,sum(ordersum)

from ordermaster

where year(orderdate)like'2012%'

groupby salerno

open cur_yearmoney

print'员工编号 '+'年终奖'

fetch cur_yearmoney into @no,@he

while(@@fetch_status=0)

begin

if(convert(float,@he)>5000)

begin

print @no+'

'+convert(varchar(10),(convert(float,@he)-5000)*0.15+500)

end

else

begin

print @no+' '+convert(varchar(10),convert(float,@he)*0.1)

end

fetch cur_yearmoney into @no,@he

end

close cur_yearmoney

deallocate cur_yearmoney

exec pro_yearmoney '2012'

(9)创建存储过程，要求将OrderMaster表中每一个订单所对应的明细数据信息按规定格

式输出，格式如图7-1所示。

===================订单及其明细数据信息====================

--------------------------------------------------- 订单编号 200801090001

--------------------------------------------------- 商品编号数量价格

P2******* 5 403.50

P2******* 3 2100.00

P2******* 2 600.00

--------------------------------------------------- 合计订单总金额 3103.50

图7-1 订单及其明细数据信息

declare @ono varchar(12),@pno varchar(12),@q varchar(6),@money

varchar(10),@no varchar(12),@temp float

--创建游标得到数据信息

declare cur_orderdetail scrollcursorfor

select orderno,productno,quantity,quantity*price he

from orderdetail od

--创建游标得到订单编号集

declare cur_orderno scrollcursorfor

select orderno

from orderdetail od

groupby orderno

print'===================订单及其明细数据信息===================='

--先打开订单编号集游标

open cur_orderno

fetch cur_orderno into @no

while(@@fetch_status=0)

begin

--打开数据信息集游标，并且遍历

print'---------------------------------------------------'

print' 订单编号 '+@no

print'---------------------------------------------------'

print'商品编号 '+'销售数量 '+'总额'

set @temp =0

open cur_orderdetail

fetch cur_orderdetail into @ono,@pno,@q,@money

while(@@fetch_status=0)

begin

if(@no = @ono)--满足条件就是同一个订单下的

begin--不加begin和end if的范围就不是预期的了

print @pno+' '+@q+' '+@money

set @temp =@temp+convert(float,@money)

end

fetch cur_orderdetail into @ono,@pno,@q,@money

end

print'---------------------------------------------------'

print'合计订单总额'+convert(varchar(8),@temp)

print''

print''

close cur_orderdetail--此处一定要关掉在打开

fetch cur_orderno into @no

end

close cur_orderno

deallocate cur_orderdetail

deallocate cur_orderno

(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 客户订单表

createprocedure proSearchCustomer @_customerno varchar(12)

as

declare @no varchar(12),@cno varchar(12),@he varchar(8),@pno

varchar(12),@q varchar(3),@p varchar(6),@cname varchar(12),@addr

varchar(12)

declare @money varchar(8),@countt varchar(10)

declare cur_customersearch1 scrollcursorfor

select sum(ordersum),customername,address

from ordermaster om,orderdetail od,customer c

where om.orderno = od.orderno

and c.customerno = om.customerno

and c.customerno = @_customerno

groupby c.customerno,customername,address

set @money =0

set @countt = 0

print'===================客户订单表===================='

print'---------------------------------------------------'

open cur_customersearch1

fetch cur_customersearch1 into @he,@cname,@addr

while(@@fetch_status=0)

begin

print'客户名称:'+@cname

print'客户地址:'+@addr

print'总金额:'+@he

fetch cur_customersearch1 into @he,@cname,@addr

end

print'---------------------------------------------------'

close cur_customersearch1

deallocate cur_customersearch1

declare cur_customersaerch2 scrollcursorfor

select od.productno,quantity,convert(varchar,price)--此处需要先转化

from ordermaster om,orderdetail od,customer c

where om.orderno = od.orderno

and c.customerno = om.customerno

and c.customerno = @_customerno

print'商品编号 '+'总数量 '+'平均价格 '

print''

open cur_customersaerch2

fetch cur_customersaerch2 into @pno,@q,@p

while(@@fetch_status=0)

begin

print @pno+' '+@q+'

'+space(3-len(@q))+convert(varchar,convert(float,@p)/convert(float,@q ))

fetch cur_customersaerch2 into @pno,@q,@p

end

close cur_customersaerch2

deallocate cur_customersaerch2

print'---------------------------------------------------'

print'报表制作人郭蚕制作日期:'+convert(char(2),day(getdate()))+'

'+convert(char(2),month(getdate()))+'

'+convert(char(4),year(getdate()))

exec proSearchCustomer 'C20050001'