试验游标和存储过程

1、WOR格式实验九游标与存储过程1实验目的与要求(1) 掌握游标的定义和使用方法。(2) 掌握存储过程的定义、执行和调用方法(3) 掌握游标和存储过程的综合应用方法。2 实验内容请完成以下实验内容：(1)创建游标，逐行显示Customer表的记录，并用 WHILE 结构来测试 Fetch_Status的返回值。输出格式如下：'客户编号'+'-'+'客户名称'+''+'客户住址 '+'客户电话'+'-'+''+'邮政编码'(2)利用游标修改OrderMas

2、ter表中orderSum的值。(3创建游标，要求：输出所有女业务员的编号、姓名、性)别、所属部门、职务、薪水(4) 创建存储过程，要求：按表定义中的CHECK勺束自动产生员工编号。(5) 创建存储过程，要求：查找姓“李”的职员的员工编号、订单编号、订单金额。(6) 创建存储过程，要求：统计每个业务员的总销售业绩，显示业绩最好的前3位业务员的销售信息。(7) 创建存储过程，要求将大客户(销售数量位于前5名的客户)中热销的前3种商品的销售信息按如下格式输出：=大客户中热销的前 3种商品的销售信息 =商品编号商品名称总销售数量P20050003120GB硬盘21.00P200500043.5寸软驱

3、18.00P20060002网卡16.00(8) 创建存储过程，要求：输入年度，计算每个业务员的年终奖金。年终奖金=年销售总额X提成率。提成率规则如下：年销售总额5000元以下部分，提成率为10%，对于5000元及超过5000元部分，则提成率为 15%。(9) 创建存储过程，要求将 OrderMaster表中每一个订单所对应的明细数据信息按规 定格式输出，格式如图 7-1所示。=订单及其明细数据信息=20080109000订单编号1商品编号P2005000数量价15403.50P2005000232100.00P20050002600.00专业资料整理WOR格式合计订单总金额3103.50图7

4、-1订单及其明细数据信息1/12专业资料整理WOR格式(10) 请使用游标和循环语句创建存储过程proSearchCustomer，根据客户编号查找该客户的名称、住址、总订单金额以及所有与该客户有关的商品销售信息，并按商品分组输出。输出格 式如图7-2所示。=客户订单表=客户名称：统一股份有限公司客户地址：天津市31121.8总金额：6商品编号P2005000总数量平均价1580.70P2005000219521.05P200500035282.00P200700042320.00报表制作人陈辉制作日期06 82012图7-2客户订单表实验脚本：/*(1)创建游标，逐行显示 Customer表

5、的记录，并用 WHILE吉构来测试 Fetch_Status的返回 值。输出格式如下：'客户编号'+'-'+'客户名称'+'-'+'客户电话'+'-'+'客户住址'+''+'邮政编码'*/declareC_nochar(9),C_namechar(18),C_phonechar(10),C_addchar(8),C_zipchar(6)declaretextchar(100)declarecus_curscrollcursorforselect*fro

6、mCustomer62select text='=Customer62表 的 记 录客户电话'+''+'客户住址'+'-printtextselecttext='客户编号'+''+'客户名称'+''+''+'邮政编码'printtextselect text=' printtext专业资料整理WOR格式opencus_curfetchcus_curintoC_no,C_name,C_phone,C_add,C_zip while(fet

7、ch_status=O)'+C_add+'beginselect text=C_no+' '+C_name+''+C_phone+'2/12专业资料整理WOR格式'+C_zipprinttextfetchcus_curintoC_no,C_name,C_phone,C_add,C_zipend closecus_curdeallocatecus cur匚 ira tea 直召 2表 的记录客户电话客户住址一编码csoosoaoiKO3SOOO4z20DssiitiC 2 0 37b30airacKFFwjisaczoz-so cm

8、纯兴上五大联南隽红司司 司中司.公司 公公室 公制公限公 限限究 限矍有限 na n* h* id± in dtidsb>S9Etl D1O*iLZ1.DH 醪(l4弹轉 oia-iiesis 021-4gCS4g%x _4 n33皿天天北尤上南上天北矗通|卫 ioaoai 21010021Jlj：33C346缺韩黏lOQdai/*(2)利用游标修改OrderMaster表中orderSum的值*/declareorderNovarchar(20),totalnumeric(9,2) declareom_curcursorforselectorderNo,sum(quantit

9、y*price)fromOrderDetail62groupbyorderNoopenom_curfetchom_curintoorderNo,total while(fetch_status=O)beginupdateOrderMaster62setorderSum=totalwhereorderNo=orderNo fetchom_curintoorderNo,totalendcloseom_curdeallocateom cur专业资料整理WOR格式3/12专业资料整理WOR格式口行萱如脊玛旳郭 轄行受訥向i-讦寺务响 毋 Tr£3m$ 嬉；行勇彩响） 通靳行向-2Q08/LB

10、;0-?<X)；3W501000000001戲03/谚辨6血3 11000000002M08/1/9 0:OC:001005 3<1000000003ZOOS融1 总UttAO !ICOOOOOOD42003廂轴财M i1000000005起08彎題S&00第的皿OOOH虞108/華串事81161 43I0QQQO0G072008/3/9 0100:003S4Z®210000000032008/5/9 焦 00010756.0010000000091000000010489C. 43ICOOODOOll1000000012ioooooqQ创建游标，*/*要求：输出

11、所有女业务员的编号、姓名、性别、所属部门、职务、薪水declareemNovarchar(8),emNachar(8),emsechar(1),emdevarchar(10), emhevarchar(8),emsanumeric(8,2)declaretextchar(1OO) declareem_curscrollcursorforselectemployeeNo,employeeName,sex,department,headShip,salary fromEmployee62wheresex='M'selecttext='=' printtextsele

12、cttext='编号 姓名性别 所属部门 职务薪水'printtext selecttext='='pri nttextopenem_curfetchem_curintoemNo,emNa,emse,emde,emhe,emsa专业资料整理WOR格式4/12专业资料整理WOR格式while(fetch_status=O)beginselecttext=emNo+”+emNa+”+emse+”+emde+”+emhe+”+convert(char(10),emsa)printtextfetchem_curintoemNo,emNa,emse,emde,emhe,e

13、msa endcloseem_curdeaHocateem_cur 编号 姓名性别所属部门职务薪水强应 萍鹏杰 自小辉浮代婆 喻张陈吴高陈张科科室科室科科务务公务公务务/*(4)工编号创建存储过程，要求：按表定义中的*/CHECK勺束自动产生员createtableRnum(numberchar(8)null,enamechar(10)null)-先创建一张新表用来存储已经产生的员工编号createprocedureno_tot(namenvarchar(50)asbegindeclareiint,textchar(100)seti=1while(i<1000)beginifexists

14、(selectnumberfromRnumwherenumber=('E'+convert(char(4),year(getdate()+right('00'+convert(varchar(3),i),3) beginseti=i+1continueendelse专业资料整理WOR格式begininsertRnumvalues('E'+convert(char(4),year(getdate()+right('00'+convert(varchar(3),i),3),name)5/12专业资料整理WOR格式selecttext=

15、' 员工编号'+''+'员工姓名'printtextselecttext=('E'+convert(char(4),year(getdate()+right('00'+convert(varchar(3),i),3)+''+name-这里的两个数字'3'就是我们要设置的id长度printtextbreakendendend/*执行过程*/表-dbO-Rrium 衷-dbo,tmplcyeebzjriame：E2012001test畅学琛E2012003:侯维腮万腿IE2012005弓

16、綴E2012006张三AUln Jt jj" t，/*(5)创建存储过程，要求：查找姓“李”的职员的员工编号、订单编号、订单金额*/createprocedureemli_totemNochar(8) asselecta.employeeNo 员工编号，b.orderNo 订单编号，b.orderSum 订单金额 fromEmployee62a,OrderMaster62b wherea.employeeNo=b.salerNoanda.employeeNamelike'emNo'/*执行过程*/員工绸号订单辭订单主訓3位业务员的/*(6)创建存储过程，要求：统计每个

17、业务员的总销售业绩，显示业绩最好的前 销售信息*/createproceduresaler_totasselecttop3salerNo 业务员编号,sum(orderSum)总销售业绩 fromOrderMaster62groupbysalerNoorderbysum(orderSum)desc专业资料整理WOR格式6/12专业资料整理WOR格式/*执行过程*/execsaler_tot销售信息按如下格式输出：= 大客户中热销的前种商品的销售信息3种商品商品编号商品名称总销售数量P20050003120GB硬盘21.00P200500043.5寸软驱18.00P20060002网卡16.00

18、*/createprocedureproduct_totasdeclareproNochar(10),proNachar(20),totalintdeclaretextchar(1OO)declaresale_ductNo,ductName,sum(c.quantity) fromProduct62a,OrderMaster62b,OrderDetail62c ductNo=ductNoandb.orderNo=c.orderNoand b.customerNoin(selecttop5m

19、.customerNofromOrderMaster62m,OrderDetail62nwherem.orderNo=n.orderNogroupbym.customerNo orderbysum(quantity)desc) ductNo,ductName orderbysum(c.quantity)descselecttext='= 大客户中热销的前种商品的销售信息=printtext selecttext='商品编号 商品名称总销售数量'printtextopensale_curfetchsale_curintoproNo,pro

20、Na,totalwhile(fetch_status=0)beginselecttext=proNo+' '+proNa+' '+convert(char(10),total) printtext fetchsale_curintoproNo,proNa,totalend专业资料整理WOR格式closesale_cur7/12专业资料整理WOR格式deallocatesale cur/*执行过程*/ execproduct_tot區消息L商品编方二=才客户中热镇的前聃商品的箱售信息产 ill商品名称总销售蜒！21寻丄寸楣目Ifi/*(8)创建存储过程，要求：输入

21、年度，计算每个业务员的年终奖金。年终奖金=年销售总额x提成率。提成率规则如下：年销售总额元以下部分，提成率为，对于元及超过元部分， 则提成率为 */createprocedurepride_totdateint asdeclaresaleNochar(15),totalnumeric(9,2)declaretextchar(100),moneynumeric(8,2) declarepride_curscrollcursorforselectsalerNo,sum(orderSum)fromOrderMaster62whereyear(orderDate)=dategroupbysalerNo

22、selecttext='=业务员的年终奖金='printtext selecttext=' 业务员编号年终奖金'printtextopenpride_curfetchpride_curintosaleNo,totalwhile(fetch_status=0)beginif(total<5000)selectmoney=total*0.1elseselectmoney=500+(total-5000)*0.15selecttext=saleNo+''+convert(char(10),money)printtextfetchpride_cur

23、intosaleNo,totalendclosepride_cur deallocatepride_cur/*执行过程*/execpride_tot2012专业资料整理WOR格式8/12专业资料整理WOR格式1扇消息I | 叮"！业曲 阵业务员编号舷奖金005003/*(9)创建存储过程，要求将OrderMaster62表中每一个订单所对应的明细数据信息按规定格式输出，格式如图-1所示。=订单及其明细数据信息=订单编号200801090001商品编号数量价格P200500015403.50P2005000232100.00P200500032600.00合计订单总金额3103.50图

24、-1订单及其明细数据信息*/createprocedureorderm_totordernochar(15)asdeclarepronochar(15),quantityint,pricenumeric(9,2)declaretextchar(1OO)declareorderm_curscrollcursorforselectproductNo,sum(quantity),sum(quantity*price)fromOrderDetail62whereorderNo=ordernogroupbyproductNoselecttext='=订单及其明纟田数据信息=printtextse

25、lecttext=''printtextselecttext='订单编号'+ordernoprinttextselecttext=''printtextselecttext=' 商品编号数量价格'printtextopenorderm_curfetchorderm_curintoprono,quantity,pricewhile(fetch_status=0)beginselecttext=prono+''+convert(char(5),quantity)+'+convert(char(10),price

26、)专业资料整理WOR格式printtext9/12专业资料整理WOR格式fetchorderm_curintoprono,quantity,priceendselecttext=''printtextcloseorderm_cur deallocateorderm_cur declaresumnumeric(9,2) declareorm_curscrollcursorforselectorderSumfromOrderMaster62whereorderNo=ordernoopenorm_curfetchorm_curintosumwhile(fetch_status=0)b

27、eginselecttext='合计订单总金额'+' '+convert(char(12),sum)printtextfetchorm_curintosumend closeorm_curdeallocateorm cur/*执行过程- */ -之订.轧交貝邨獭进exeqordesm_tot20080109Q001si/*(10)请使用游标和循环语句创建存储过程proSearchCustomer，根据客户编号查找该客户的名称、住址、总订单金额以及所有与该客户有关的商品销售信息，并按商品分组输出。输出格式如图-2所示。=客户订单表客户名称:客户地址:总金额:统一股

28、份有限公司天津市31121.86商品编号P20050001总数量5平均价格80.70专业资料整理WOR格式521.0P20050002195282.0P200500035010/12专业资料整理WOR格式P200700042320.00报表制作人陈辉制作日期06 82012*/createprocedureproSearchCustomer(cusnochar(10)asdeclarecusnamechar(40),addresschar(20),totalnumeric(9,2)declaretextchar(100)declaresear_curscrollcursorforselecta

29、.customerName,a.address,sum(b.orderSum) fromCustomer62a,OrderMaster62b wherea.customerNo=b.customerNoanda.customerNo=cusno groupbya.customerName,a.addressselecttext='= 客户订单表= printtextselecttext=''printtextopensear_cur fetchsear_curintocusname,address,total while(fetch_status=0)beginselecttext='客户名称：'+'printtextselecttext='客户地址:'+'printt