实验9 游标与存储过程.docx
《实验9 游标与存储过程.docx》由会员分享，可在线阅读，更多相关《实验9 游标与存储过程.docx（20页珍藏版）》请在冰点文库上搜索。
实验9游标与存储过程
实验九游标与存储过程
1实验目的与要求
（1）掌握游标的定义和使用方法。
（2）掌握存储过程的定义、执行和调用方法。
（3）掌握游标和存储过程的综合应用方法。
2实验内容
请完成以下实验内容：
（1）创建游标，逐行显示Customer表的记录，并用WHILE结构来测试@@Fetch_Status的返回值。
输出格式如下：
'客户编号'+'-----'+'客户名称'+'----'+'客户住址'+'-----'+'客户电话'+'------'+'邮政编码'
脚本：
DECLARE@cus_Nochar（10）,@cus_Namechar（20）,@cus_addchar（10）,@cus_Telechar（10）,@cus_Codechar（7）--定义几个变量
DECLARE@textchar（100）
DECLAREcus_curSCROLLCURSORFOR
SELECTcustomerNo,customerName,address,telephone,zip
FROMcustomer
ORDERBYcustomerNo--定义游标
select@text='=================================01033熊昌磊==================================='
PRINT@text
select@text='客户编号'+'----------'+'客户名称'+'----------'+'客户住址'+'----------'+'客户电话'+'----------'+'邮政编码'
PRINT@text
select@text='---------------------------------------------------------------------------------'
PRINT@text--格式化输出
opencus_cur--打开游标
FETCHcus_curINTO@cus_No,@cus_Name,@cus_add,@cus_Tele,@cus_Code--提取游标中的信息并分别给内存变量
while（@@FETCH_status=0）
BEGIN
SELECT@text='|'+@cus_No+''+@cus_Name+''+@cus_add+''+@cus_Tele+''+@cus_Code+'|'
PRINT@text
FETCHcus_curINTO@cus_No,@cus_Name,@cus_add,@cus_Tele,@cus_Code
END
select@text='---------------------------------------------------------------------------------'
PRINT@text
select@text='================================================================================='
PRINT@text
CLOSEcus_cur--关闭游标
DEALLOCATEcus_cur
结果为：
（2）利用游标修改OrderMaster表中orderSum的值。
脚本：
DECLARE@cus_Nochar（15）--定义几个变量
DECLARE@cus_totalnumeric（8,2）,@textchar（100）
DECLAREcus_curSCROLLCURSORFOR
selectorderNo,sum（quantity*price）astotal
fromorderdetail
groupbyorderNo
select@text='订单编号'+''+'订单总额'
print@text
opencus_cur
fetchcus_curinto@cus_No,@cus_total
while（@@fetch_status=0）
begin
select@cus_total=ordersum
fromorderMaster
whereorderNo=@cus_No
select@text=@cus_No++convert（char（10）,@cus_total）
print@text
fetchcus_curinto@cus_No,@cus_total
END
CLOSEcus_cur--关闭游标
DEALLOCATEcus_cur
结果为：
（3）创建游标，要求：
输出所有女业务员的编号、姓名、性别、所属部门、职务、薪水。
脚本：
declare@emp_Nochar（10）,@emp_Namechar（10）,@emp_sexchar（3）,@emp_depchar（8）,@emp_headchar（8）
declare@emp_salarynumeric（8,2）,@textchar（100）
declareemp_curSCROLLCURSORFOR
selectemployeeNo,employeeName,sex,department,headship,salary
fromemployee
wheresex='M'
select@text='=====================女业务员的信息========================='
print@text
select@text='编号姓名性别部门职位薪水'
print@text
select@text='------------------------------------------------------------'
print@text
openemp_cur
fetchemp_curinto@emp_No,@emp_Name,@emp_sex,@emp_dep,@emp_head,@emp_salary
while（@@fetch_status=0）
begin
select@text=@emp_No+''+@emp_Name+''+@emp_sex+''+@emp_dep+''+@emp_head+''+convert（char（10）,@emp_salary）
print@text
fetchemp_curinto@emp_No,@emp_Name,@emp_sex,@emp_dep,@emp_head,@emp_salary
end
closeemp_cur
deallocateemp_cur
结果：
（4）创建存储过程，要求：
按表定义中的CHECK约束自动产生员工编号。
createprocedurenum6@numint
as
declare@yearchar（4）,@pro_maxint,@ncountint
declare@autoNumchar（8）,@textchar（50）
select@ncount=0
declareanumscrollcursorfor
selectsubstring（max（employeeNo）,2,4）pro_year,convert（int,substring（max（employeeNo）,6,3））pro_max
fromEmployee
select@text='===自动产生的员工编号==='
print@text
openanum
fetchanuminto@year,@pro_max
while（@@fetch_status=0）
begin
if@year=convert（char（4）,（year（getdate（））））
begin
while（@ncount<@numand@pro_max<999）
begin
select@pro_max=@pro_max+1
select@autoNum='E'+convert（char（4）,year（getdate（）））+substring（convert（char（4）,1000+@pro_max）,2,3）
print@autoNum
select@ncount=@ncount+1
end
if@pro_max=999
begin
print'编号溢出！
'
end
end
else
begin
select@pro_max=0
while（@ncount<@numand@pro_max<999）
begin
select@pro_max=@pro_max+1
select@autoNum='E'+convert（char（4）,year（getdate（）））+substring（convert（char（4）,1000+@pro_max）,2,3）
print@autoNum
select@ncount=@ncount+1
end
if@pro_max=999
begin
print'编号溢出！
'
end
end
fetchanuminto@year,@pro_max
end
closeanum
deallocateanum
（5）创建存储过程，要求：
查找姓“李”的职员的员工编号、订单编号、订单金额。
创建存储过程：
createprocedureemp_tot@emp_namevarchar（10）
AS
selectemployeeNo,orderNo,ordersum
fromorderMastera,（selectemployeeNo
fromemployee
whereemployeeNamelike@emp_name）b
wherea.salerNo=b.employeeNo
execemp_tot@emp_name='李%'
结果为：
（6）创建存储过程，要求：
统计每个业务员的总销售业绩，显示业绩最好的前3位业务员的销售信息。
创建存储过程：
createprocedureemp_tot2
AS
selectemployeeNo,employeeName,orderNo,ordersum
fromorderMastera,（
selecttop3employeeNo,employeeName,sum（ordersum）total
fromemployeea,orderMasterb
wherea.employeeNo=b.salerNo
groupbyemployeeName,employeeNO
orderbytotaldesc）b
wherea.salerNo=b.employeeNo
orderbyordersumdesc执行存储过程：
Execemp_tot2
截图：
（7）创建存储过程，要求将大客户（销售数量位于前5名的客户）中热销的前3种商品的销售信息按如下格式输出：
=======大客户中热销的前3种商品的销售信息================
商品编号商品名称总销售数量
P20050003120GB硬盘21.00
P200500043.5寸软驱18.00
P20060002网卡16.00
createprocedureemp_tzt
AS
begin
declare@textchar（100）,@emp_Nochar（15）,@emp_namechar（20）,@emp_qtynumeric（8,2）
select@text='=========大客户中热销的前种商品的销售信息================='
print@text
select@text='商品编号商品名称总销售数量'
print@text
declareget_totcursorfor
selecta.productNo,productName,总销售数量
fromproducta,
（
selecttop3productNo,sum（quantity）总销售数量
fromorderDetaila,
（
selecttop5customerNo,a.orderNo,sum（sun）total
fromorderMastera,
（
selectorderNo,sum（quantity）sun
fromorderdetail
groupbyorderNo
）b
wherea.orderNo=b.orderNo
groupbycustomerNo,a.orderNo
orderbytotaldesc
）b
wherea.orderNo=b.orderNo
groupbyproductNo,quantity
orderby总销售数量desc
）b
wherea.productNo=b.productNo
openget_tot
fetchget_totinto@emp_No,@emp_name,@emp_qty
while（@@fetch_status=0）
begin
select@text=@emp_No+''+@emp_name+''+convert（char（10）,@emp_qty）
print@text
fetchget_totinto@emp_No,@emp_name,@emp_qty
end
closeget_tot
deallocateget_tot
end
结果：
（8）创建存储过程，要求：
输入年度，计算每个业务员的年终奖金。
年终奖金＝年销售总额×提成率。
提成率规则如下：
年销售总额5000元以下部分，提成率为10％，对于5000元及超过5000元部分，则提成率为15％。
脚本：
CREATEprocedureproc_caa_bonus1@yearchar（4）
AS
begin
declare@emp_namechar（10）,@emp_nochar（5）,@year_tot_amtnumeric（12,2）,@bonusnumeric（9,2）
declare@textchar（100）
select@text='============================年终奖金信息========================='
print@text
select@text='业务员编号业务员姓名总销售金额年终奖金'
print@text
select@text='------------------------------------------------------------------'
print@text
declaremycurcursorfor
selectb.employeeNo,b.employeeName,sum（ordersum）asyear_tot_amt
fromordermastera,employeeb
whereconvert（char（4）,orderdate,120）=@yearanda.salerNo=b.employeeNo
groupbyemployeeNo,employeeName
openmycur
fetchmycurinto@emp_no,@emp_name,@year_tot_amt
while（@@fetch_status=0）
begin
if@year_tot_amt<5000
select@bonus=@year_tot_amt*0.1
else
select@bonus=5000*0.1+（@year_tot_amt-5000）*0.15
select@text=''+@emp_no+''+@emp_name+''+convert（char（10）,@year_tot_amt）+''+convert（char（10）,@bonus）
print@text
fetchmycurinto@emp_no,@emp_name,@year_tot_amt
end
closemycur
deallocatemycur
end
截图：
（9）创建存储过程，要求将OrderMaster表中每一个订单所对应的明细数据信息按规定格式输出，格式如图7-1所示。
===================订单及其明细数据信息====================
---------------------------------------------------
订单编号200801090001
---------------------------------------------------
商品编号数量价格
P200500015403.50
P2005000232100.00
P200500032600.00
---------------------------------------------------
合计订单总金额3103.50
图7-1订单及其明细数据信息
CREATEprocedurekm_tot
AS
begin
declare@emp_nochar（12）,@emp_pnochar（10）,@quantitynumeric（8,2）,@pricenumeric（8,2）,@cuschar（12）,@shunumeric（8,2）
declare@textchar（100）
select@text='========================订单及其明细数据信息======================'
print@text
select@text='-----------------------------------------------------------------'
print@text
declareorder_cuscursorfor
selectorderNo,productNo,quantity,price
fromorderdetail
openorder_cus
fetchorder_cusinto@emp_no,@emp_pno,@quantity,@price
set@cus=''
set@shu=0.00
while（@@fetch_status=0）
begin
if（@cus!
=@emp_no）
begin
if（@cus!
=''）
begin
select@text='-----------------------------------------------------------------'
print@text
select@text='合计订单总金额'+convert（char（10）,@shu）
print@text
select@text='-----------------------------------------------------------------'
print@text
select@text=''
print@text
set@shu=0.00
end
else
begin
print''
end
select@text='订单编号'+@emp_no
print@text
select@text='----------------------------------------------------------------'
print@text
select@text='商品编号数量价格'
print@text
select@text=@emp_pno+''+convert（char（10）,@quantity）+''+convert（char（10）,@price）
print@text
set@shu=@price+@shu
set@cus=@emp_no
fetchorder_cusinto@emp_no,@emp_pno,@quantity,@price
end
else
begin
select@text=@emp_pno+''+convert（char（10）,@quantity）+''+convert（char（10）,@price）
print@text
set@shu=@price+@shu
fetchorder_cusinto@emp_no,@emp_pno,@quantity,@price
end
end
select@text='-----------------------------------------------------------------'
print@text
closeorder_cus
deallocateorder_cus
end
结果为
（10）请使用游标和循环语句创建存储过程proSearchCustomer，根据客户编号查找该客户的名称、住址、总订单金额以及所有与该客户有关的商品销售信息，并按商品分组输出。
输出格式如图7-2所示。
===================客户订单表====================
---------------------------------------------------
客户名称：
统一股份有限公司
客户地址：
天津市
总金额：
31121.86
---------------------------------------------------
商品编号总数量平均价格
P20050001580.70
P2005000219521.05
P200500035282.00
P200700042320.00
报表制作人陈辉制作日期06
升级会员 