数据库原理实验9.docx

数据库原理实验9.docx

《数据库原理实验9.docx》由会员分享，可在线阅读，更多相关《数据库原理实验9.docx（18页珍藏版）》请在冰豆网上搜索。

数据库原理实验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

SELECTcustomerNo48,customerName48,address48,telephone48,zip48

FROMcustomer48

ORDERBYcustomerNo48--定义游标

select@text='=================================0103348熊昌磊==================================='

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

selectorderNo48,sum（quantity48*price48）astotal

fromorderdetail48

groupbyorderNo48

select@text='订单编号'+''+'订单总额'

print@text

opencus_cur

fetchcus_curinto@cus_No,@cus_total

while（@@fetch_status=0）

begin

select@cus_total=ordersum48

fromorderMaster48

whereorderNo48=@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

selectemployeeNo48,employeeName48,sex48,department48,headship48,salary48

fromemployee48

wheresex48='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（employeeNo48）,2,4）pro_year,convert（int,substring（max（employeeNo48）,6,3））pro_max

fromEmployee48

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

selectemployeeNo48,orderNo48,ordersum48

fromorderMaster48a,（selectemployeeNo48

fromemployee48

whereemployeeName48like@emp_name）b

wherea.salerNo48=b.employeeNo48

执行存储过程：

execemp_tot@emp_name='李%'

截图：

（6）创建存储过程，要求：

统计每个业务员的总销售业绩，显示业绩最好的前3位业务员的销售信息。

创建存储过程：

createprocedureemp_tot2

AS

selectemployeeNo48,employeeName48,orderNo48,ordersum48

fromorderMaster48a,（

selecttop3employeeNo48,employeeName48,sum（ordersum48）total

fromemployee48a,orderMaster48b

wherea.employeeNo48=b.salerNo48

groupbyemployeeName48,employeeNO48

orderbytotaldesc）b

wherea.salerNo48=b.employeeNo48

orderbyordersum48desc执行存储过程：

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.productNo48,productName48,总销售数量

fromproduct48a,

（

selecttop3productNo48,sum（quantity48）总销售数量

fromorderDetail48a,

（

selecttop5customerNo48,a.orderNo48,sum（sun）total

fromorderMaster48a,

（

selectorderNo48,sum（quantity48）sun

fromorderdetail48

groupbyorderNo48

）b

wherea.orderNo48=b.orderNo48

groupbycustomerNo48,a.orderNo48

orderbytotaldesc

）b

wherea.orderNo48=b.orderNo48

groupbyproductNo48,quantity48

orderby总销售数量desc

）b

wherea.productNo48=b.productNo48

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.employeeNo48,b.employeeName48,sum（ordersum48）asyear_tot_amt

fromordermaster48a,employee48b

whereconvert（char（4）,orderdate48,120）=@yearanda.salerNo48=b.employeeNo48

groupbyemployeeNo48,employeeName48

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

selectorderNo48,productNo48,quantity48,price48

fromorderdetail48

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，根据客户编号查找该客户的名称、住址、总订单金额以及所有与该客户有关的商品销售信息，并按商品分组输出