实验游标和存储过程.docx
《实验游标和存储过程.docx》由会员分享，可在线阅读，更多相关《实验游标和存储过程.docx（16页珍藏版）》请在冰豆网上搜索。
实验游标和存储过程
实验九游标与存储过程
1实验目的与要求
（1）掌握游标的定义和使用方法。
（2）掌握存储过程的定义、执行和调用方法。
（3）掌握游标和存储过程的综合应用方法。
2实验内容
请完成以下实验内容：
（1）创建游标，逐行显示Customer表的记录，并用WHILE结构来测试@@Fetch_Status的返回值。
输出格式如下：
'客户编号'+'-----'+'客户名称'+'----'+'客户住址'+'-----'+'客户电话'+'------'+'邮政编码'
（2）利用游标修改OrderMaster表中orderSum的值。
（3）创建游标，要求：
输出所有女业务员的编号、姓名、性别、所属部门、职务、薪水。
（4）创建存储过程，要求：
按表定义中的CHECK约束自动产生员工编号。
（5）创建存储过程，要求：
查找姓“李”的职员的员工编号、订单编号、订单金额。
（6）创建存储过程，要求：
统计每个业务员的总销售业绩，显示业绩最好的前3位业务员的销售信息。
（7）创建存储过程，要求将大客户（销售数量位于前5名的客户）中热销的前3种商品的销售信息按如下格式输出：
=======大客户中热销的前3种商品的销售信息================
商品编号商品名称总销售数量
P20050003120GB硬盘21.00
P200500043.5寸软驱18.00
P20060002网卡16.00
（8）创建存储过程，要求：
输入年度，计算每个业务员的年终奖金。
年终奖金＝年销售总额×提成率。
提成率规则如下：
年销售总额5000元以下部分，提成率为10％，对于5000元及超过5000元部分，则提成率为15％。
（9）创建存储过程，要求将OrderMaster表中每一个订单所对应的明细数据信息按规定格式输出，格式如图7-1所示。
===================订单及其明细数据信息====================
---------------------------------------------------
订单编号200801090001
---------------------------------------------------
商品编号数量价格
P200500015403.50
P2005000232100.00
P200500032600.00
---------------------------------------------------
合计订单总金额3103.50
图7-1订单及其明细数据信息
（10）请使用游标和循环语句创建存储过程proSearchCustomer，根据客户编号查找该客户的名称、住址、总订单金额以及所有与该客户有关的商品销售信息，并按商品分组输出。
输出格式如图7-2所示。
===================客户订单表====================
---------------------------------------------------
客户名称：
统一股份有限公司
客户地址：
天津市
总金额：
31121.86
---------------------------------------------------
商品编号总数量平均价格
P20050001580.70
P2005000219521.05
P200500035282.00
P200700042320.00
报表制作人陈辉制作日期0682012
图7-2客户订单表
实验脚本：
/*
（1）创建游标，逐行显示Customer表的记录，并用WHILE结构来测试@@Fetch_Status的返回值。
输出格式如下：
'客户编号'+'-----'+'客户名称'+'----'+'客户电话'+'-----'+'客户住址'+'------'+'邮政编码'*/
declare@C_nochar（9）,@C_namechar（18）,@C_phonechar（10）,
@C_addchar（8）,@C_zipchar（6）
declare@textchar（100）
declarecus_curscrollcursorfor
select*
fromCustomer62
select@text='================================Customer62表的记录===================='
print@text
select@text='客户编号'+'------'+'客户名称'+'-----------'+'客户电话'+'-------'+'客户住址'+'------'+'邮政编码'
print@text
select@text='======================================================================'
print@text
opencus_cur
fetchcus_curinto@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
fetchcus_curinto@C_no,@C_name,@C_phone,@C_add,@C_zip
end
closecus_cur
deallocatecus_cur
/*
（2）利用游标修改OrderMaster表中orderSum的值*/
declare@orderNovarchar（20）,@totalnumeric（9,2）
declareom_curcursorfor
selectorderNo,sum（quantity*price）
fromOrderDetail62
groupbyorderNo
openom_cur
fetchom_curinto@orderNo,@total
while（@@fetch_status=0）
begin
updateOrderMaster62
setorderSum=@total
whereorderNo=@orderNo
fetchom_curinto@orderNo,@total
end
closeom_cur
deallocateom_cur
/*（3）创建游标，要求：
输出所有女业务员的编号、姓名、性别、所属部门、职务、薪水*/
declare@emNovarchar（8）,@emNachar（8）,@emsechar
（1）,@emdevarchar（10）,
@emhevarchar（8）,@emsanumeric（8,2）
declare@textchar（100）
declareem_curscrollcursorfor
selectemployeeNo,employeeName,sex,department,headShip,salary
fromEmployee62
wheresex='M'
select@text='====================================================='
print@text
select@text='编号姓名性别所属部门职务薪水'
print@text
select@text='====================================================='
print@text
openem_cur
fetchem_curinto@emNo,@emNa,@emse,@emde,@emhe,@emsa
while（@@fetch_status=0）
begin
select@text=@emNo+''+@emNa+''+@emse+''+@emde+''+@emhe
+''+convert（char（10）,@emsa）
print@text
fetchem_curinto@emNo,@emNa,@emse,@emde,@emhe,@emsa
end
closeem_cur
deallocateem_cur
/*（4）创建存储过程，要求：
按表定义中的CHECK约束自动产生员工编号*/
createtableRnum（
numberchar（8）null,
enamechar（10）null
）--先创建一张新表用来存储已经产生的员工编号
createprocedureno_tot（@namenvarchar（50））
as
begin
declare@iint,@textchar（100）
set@i=1
while（@i<1000）
begin
ifexists（selectnumber
fromRnum
wherenumber=（'E'+convert（char（4）,year（getdate（）））+right（'00'+convert（varchar（3）,@i）,3）））
begin
set@i=@i+1
continue
end
else
begin
insertRnumvalues（（'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
/*执行过程*/
execno_tot张三
/*（5）创建存储过程，要求：
查找姓“李”的职员的员工编号、订单编号、订单金额*/
createprocedureemli_tot@emNochar（8）
as
selecta.employeeNo员工编号,b.orderNo订单编号,b.orderSum订单金额
fromEmployee62a,OrderMaster62b
wherea.employeeNo=b.salerNoanda.employeeNamelike'@emNo'
/*执行过程*/
execemli_tot'李%'
/*（6）创建存储过程，要求：
统计每个业务员的总销售业绩，显示业绩最好的前3位业务员的销售信息*/
createproceduresaler_tot
as
selecttop3salerNo业务员编号,sum（orderSum）总销售业绩
fromOrderMaster62
groupbysalerNo
orderbysum（orderSum）desc
/*执行过程*/
execsaler_tot
/* （7）创建存储过程，要求将大客户（销售数量位于前5名的客户）中热销的前3种商品的销售信息按如下格式输出：
=======大客户中热销的前种商品的销售信息================
商品编号商品名称总销售数量
P20050003120GB硬盘21.00
P200500043.5寸软驱18.00
P20060002网卡16.00*/
createprocedureproduct_tot
as
declare@proNochar（10）,@proNachar（20）,@totalint
declare@textchar（100）
declaresale_curscrollcursorfor
selecttop3a.productNo,a.productName,sum（c.quantity）
fromProduct62a,OrderMaster62b,OrderDetail62c
wherea.productNo=c.productNoandb.orderNo=c.orderNoand
b.customerNoin（selecttop5m.customerNo
fromOrderMaster62m,OrderDetail62n
wherem.orderNo=n.orderNo
groupbym.customerNo
orderbysum（quantity）desc）
groupbya.productNo,a.productName
orderbysum（c.quantity）desc
select@text='=======大客户中热销的前种商品的销售信息======'
print@text
select@text='商品编号商品名称总销售数量'
print@text
opensale_cur
fetchsale_curinto@proNo,@proNa,@total
while（@@fetch_status=0）
begin
select@text=@proNo+''+@proNa+''+convert（char（10）,@total）
print@text
fetchsale_curinto@proNo,@proNa,@total
end
closesale_cur
deallocatesale_cur
/*执行过程*/
execproduct_tot
/*（8）创建存储过程，要求：
输入年度，计算每个业务员的年终奖金。
年终奖金＝年销售总额×提成率。
提成率规则如下：
年销售总额元以下部分，提成率为％，对于元及超过元部分，
则提成率为％*/
createprocedurepride_tot@dateint
as
declare@saleNochar（15）,@totalnumeric（9,2）
declare@textchar（100）,@moneynumeric（8,2）
declarepride_curscrollcursorfor
selectsalerNo,sum（orderSum）
fromOrderMaster62
whereyear（orderDate）=@date
groupbysalerNo
select@text='=========业务员的年终奖金========='
print@text
select@text='业务员编号年终奖金'
print@text
openpride_cur
fetchpride_curinto@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
fetchpride_curinto@saleNo,@total
end
closepride_cur
deallocatepride_cur
/*执行过程*/
execpride_tot2012
/*（9）创建存储过程，要求将OrderMaster62表中每一个订单所对应的明细数据信息按规定格式输出，格式如图-1所示。
===================订单及其明细数据信息====================
---------------------------------------------------
订单编号200801090001
---------------------------------------------------
商品编号数量价格
P200500015403.50
P2005000232100.00
P200500032600.00
---------------------------------------------------
合计订单总金额3103.50
图-1订单及其明细数据信息*/
createprocedureorderm_tot@ordernochar（15）
as
declare@pronochar（15）,@quantityint,@pricenumeric（9,2）
declare@textchar（100）
declareorderm_curscrollcursorfor
selectproductNo,sum（quantity）,sum（quantity*price）
fromOrderDetail62
whereorderNo=@orderno
groupbyproductNo
select@text='=============订单及其明细数据信息================'
print@text
select@text='-------------------------------------------------'
print@text
select@text='订单编号'+@orderno
print@text
select@text='-------------------------------------------------'
print@text
select@text='商品编号数量价格'
print@text
openorderm_cur
fetchorderm_curinto@prono,@quantity,@price
while（@@fetch_status=0）
begin
select@text=@prono+''+convert（char（5）,@quantity）+''
+convert（char（10）,@price）
print@text
fetchorderm_curinto@prono,@quantity,@price
end
select@text='-------------------------------------------------'
print@text
closeorderm_cur
deallocateorderm_cur
declare@sumnumeric（9,2）
declareorm_curscrollcursorfor
selectorderSum
fromOrderMaster62
whereorderNo=@orderno
openorm_cur
fetchorm_curinto@sum
while（@@fetch_status=0）
begin
select@text='合计订单总金额'+''+convert（char（12）,@sum）
print@text
fetchorm_curinto@sum
end
closeorm_cur
deallocateorm_cur
/*执行过程*/
execorderm_tot200801090001
/*（10）请使用游标和循环语句创建存储过程proSearchCustomer，根据客户编号查找该客户的名称、住址、总订单金额以及所有与该客户有关的商品销售信息，并按商品分组输出。
输出格式如图-2所示。
===================客户订单表====================
---------------------------------------------------
客户名称：
统一股份有限公司
客户地址：
天津市
总金额：
31121.86
---------------------------------------------------
商品编号总数量平均价格
P20050001580.70
P2005000219521.05
P200500035282.00
P200700042320.00
报表制作人陈辉制作日期0682012*/
createprocedureproSearchCustomer（@cusnochar（10））
as
declare@cusnamechar（40）,@addresschar（20）,@totalnumeric（9,2）
declare@textchar（100）
declaresear_curscrollcursorfor
selecta.customerName,a.address,sum（b.orderSum）
fromCustomer62a,OrderMaster62b
wherea.customerNo=b.customerNoanda.customerNo=@cusno
groupbya.customerName,a.address
select@text='===================客户订单表===================='
print@text
select@tex
升级会员 