数据库系统原理与设计实验教程答案 实验十二_游标和存储过程

实验十二游标与存储过程请完成以下实验内容：(1)创建游标，逐行显示Customer表的记录，并用WHILE结构来测试@@Fetch_Status的返回值。输出格式如下：'客户编号'+'-----'+'客户名称'+'----'+'客户住址'+'-----'+'客户电话'+'------'+'邮政编码'(2)利用游标修改OrderMaster表中orderSum的值。(3)创建游标，要求：输出所有女业务员的编号、姓名、性别、所属部门、职务、薪水。(4)创建存储过程，要求：按表定义中的CHECK约束自动产生员工编号。(5)创建存储过程，要求：查找姓“李”的职员的员工编号、订单编号、订单金额。(6)创建存储过程，要求：统计每个业务员的总销售业绩，显示业绩最好的前3位业务员的销售信息。(7)创建存储过程，要求将大客户(销售数量位于前5名的客户)中热销的前3种商品的销售信息按如下格式输出：=======大客户中热销的前3种商品的销售信息================商品编号商品名称总销售数量P20050003120GB硬盘21.00P200500043.5寸软驱18.00P20060002网卡16.00(8)创建存储过程，要求：输入年度，计算每个业务员的年终奖金。年终奖金＝年销售总额×提成率。提成率规则如下：年销售总额5000元以下部分，提成率为10％，对于5000元及超过5000元部分，则提成率为15％。(9)创建存储过程，要求将OrderMaster表中每一个订单所对应的明细数据信息按规定格式输出，格式如图7-1所示。===================订单及其明细数据信息====================---------------------------------------------------订单编号200801090001---------------------------------------------------商品编号数量价格P200500015403.50P2005000232100.00P200500032600.00---------------------------------------------------合计订单总金额3103.50图7-1订单及其明细数据信息(10)请使用游标和循环语句创建存储过程proSearchCustomer，根据客户编号查找该客户的名称、住址、总订单金额以及所有与该客户有关的商品销售信息，并按商品分组输出。输出格式如图7-2所示。===================客户订单表====================---------------------------------------------------客户名称：统一股份有限公司客户地址：天津市总金额：31121.86---------------------------------------------------商品编号总数量平均价格P20050001580.70P2005000219521.05P200500035282.00P200700042320.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_curscrollcursorforselect*fromCustomer62select@text='================================Customer62表的记录===================='print@textselect@text='客户编号'+'------'+'客户名称'+'-----------'+'客户电话'+'-------'+'客户住址'+'------'+'邮政编码'print@textselect@text='======================================================================'print@textopencus_curfetchcus_curinto@C_no,@C_name,@C_phone,@C_add,@C_zipwhile(@@fetch_status=0)beginselect@text=@C_no+''+@C_name+''+@C_phone+''+@C_add+''+@C_zipprint@textfetchcus_curinto@C_no,@C_name,@C_phone,@C_add,@C_zipendclosecus_curdeallocatecus_cur/*(2)利用游标修改OrderMaster表中orderSum的值*/declare@orderNovarchar(20),@totalnumeric(9,2)declareom_curcursorforselectorderNo,sum(quantity*price)fromOrderDetail62groupbyorderNoopenom_curfetchom_curinto@orderNo,@totalwhile(@@fetch_status=0)beginupdateOrderMaster62setorderSum=@totalwhereorderNo=@orderNofetchom_curinto@orderNo,@totalendcloseom_curdeallocateom_cur/*(3)创建游标，要求：输出所有女业务员的编号、姓名、性别、所属部门、职务、薪水*/declare@emNovarchar(8),@emNachar(8),@emsechar(1),@emdevarchar(10),@emhevarchar(8),@emsanumeric(8,2)declare@textchar(100)declareem_curscrollcursorforselectemployeeNo,employeeName,sex,department,headShip,salaryfromEmployee62wheresex='M'select@text='====================================================='print@textselect@text='编号姓名性别所属部门职务薪水'print@textselect@text='====================================================='print@textopenem_curfetchem_curinto@emNo,@emNa,@emse,@emde,@emhe,@emsawhile(@@fetch_status=0)beginselect@text=@emNo+''+@emNa+''+@emse+''+@emde+''+@emhe+''+convert(char(10),@emsa)print@textfetchem_curinto@emNo,@emNa,@emse,@emde,@emhe,@emsaendcloseem_curdeallocateem_cur/*(4)创建存储过程，要求：按表定义中的CHECK约束自动产生员工编号*/createtableRnum(numberchar(8)null,enamechar(10)null)--先创建一张新表用来存储已经产生的员工编号createprocedureno_tot(@namenvarchar(50))asbegindeclare@iint,@textchar(100)set@i=1while(@i1000)beginifexists(selectnumberfromRnumwherenumber=('E'+convert(char(4),year(getdate()))+right('00'+convert(varchar(3),@i),3)))beginset@i=@i+1continueendelsebegininsertRnumvalues(('E'+convert(char(4),year(getdate()))+right('00'+convert(varchar(3),@i),3)),@name)select@text='员工编号'+''+'员工姓名'print@textselect@text=('E'+convert(char(4),year(getdate()))+right('00'+convert(varchar(3),@i),3))+''+@name--这里的两个数字'3'就是我们要设置的id长度print@textbreakendendend/*执行过程*/execno_tot张三/*(5)创建存储过程，要求：查找姓“李”的职员的员工编号、订单编号、订单金额*/createprocedureemli_tot@emNochar(8)asselecta.employeeNo员工编号,b.orderNo订单编号,b.orderSum订单金额fromEmployee62a,OrderMaster62bwherea.employeeNo=b.salerNoanda.employeeNamelike'@emNo'/*执行过程*/execemli_tot'李%'/*(6)创建存储过程，要求：统计每个业务员的总销售业绩，显示业绩最好的前3位业务员的销售信息*/createproceduresaler_totasselecttop3salerNo业务员编号,sum(orderSum)总销售业绩fromOrderMaster62groupbysalerNoorderbysum(orderSum)desc/*执行过程*/execsaler_tot/*(7)创建存储过程，要求将大客户(销售数量位于前5名的客户)中热销的前3种商品的销售信息按如下格式输出：=======大客户中热销的前种商品的销售信息================商品编号商品名称总销售数量P20050003120GB硬盘21.00P200500043.5寸软驱18.00P20060002网卡16.00*/createprocedureproduct_totasdeclare@proNochar(10),@proNachar(20),@totalintdeclare@textchar(100)declaresale_curscrollcursorforselecttop3a.productNo,a.productName,sum(c.quantity)fromProduct62a,OrderMaster62b,OrderDetail62cwherea.productNo=c.productNoandb.orderNo=c.orderNoandb.customerNoin(selecttop5m.customerNofromOrderMaster62m,OrderDetail62nwherem.orderNo=n.orderNogroupbym.customerNoorderbysum(quantity)desc)groupbya.productNo,a.productNameorderbysum(c.quantity)descselect@text='=======大客户中