實(shí)驗(yàn)游標(biāo)和存儲(chǔ)過(guò)程

1、實(shí)驗(yàn)九游標(biāo)與存儲(chǔ)過(guò)程1實(shí)驗(yàn)?zāi)康呐c要求(1)掌握游標(biāo)的定義和使用方法。(2)掌握存儲(chǔ)過(guò)程的定義、執(zhí)行和調(diào)用方法。(3)掌握游標(biāo)和存儲(chǔ)過(guò)程的綜合應(yīng)用方法。2實(shí)驗(yàn)內(nèi)容請(qǐng)完成以下實(shí)驗(yàn)內(nèi)容：(1)創(chuàng)建游標(biāo)，逐行顯示 Customer表的記錄，并用 WHILE結(jié)構(gòu)來(lái)測(cè)試Fetch_Status 的返回值。輸出格式如下：客戶編號(hào)'+'-'+'客戶名稱'+''+'客戶住址'+'-'+'客戶電話'+''+'郵政編碼'(2)利用游標(biāo)修改 OrderMaster 表中orderSu

2、m 的值。(3)創(chuàng)建游標(biāo)，要求：輸出所有女業(yè)務(wù)員的編號(hào)、姓名、性別、 所屬部門、職務(wù)、薪水。(4)創(chuàng)建存儲(chǔ)過(guò)程，要求：按表定義中的CHECK約束自動(dòng)產(chǎn)生員工編號(hào)。(5)創(chuàng)建存儲(chǔ)過(guò)程，要求：查找姓 “李”的職員的員工編號(hào)、訂單編號(hào)、訂單金額。(6)創(chuàng)建存儲(chǔ)過(guò)程，要求：統(tǒng)計(jì)每個(gè)業(yè)務(wù)員的總銷售業(yè)績(jī)，顯示業(yè)績(jī)最好的前3位業(yè)務(wù)員的銷售信息。(7)創(chuàng)建存儲(chǔ)過(guò)程，要求將大客戶(銷售數(shù)量位于前5名的客戶)中熱銷的前3種商品的銷售信息按如下格式輸出：=大客戶中熱銷的前3種商品的銷售信息=商品編號(hào)商品名稱總銷售數(shù)量P20050003120GB 硬盤21.00P200500043.5 寸軟驅(qū)18.00P200600

3、02網(wǎng)卡16.00(8)創(chuàng)建存儲(chǔ)過(guò)程，要求：輸入年度，計(jì)算每個(gè)業(yè)務(wù)員的年終獎(jiǎng)金。年終獎(jiǎng)金=年銷售 總額X提成率。提成率規(guī)則如下：年銷售總額5000元以下部分，提成率為10%,對(duì)于5000 元及超過(guò)5000元部分，則提成率為15%。(9)創(chuàng)建存儲(chǔ)過(guò)程，要求將 OrderMaster表中每一個(gè)訂單所對(duì)應(yīng)的明細(xì)數(shù)據(jù)信息按規(guī)定格式輸出，格式如圖7-1所示。=訂單及其明細(xì)數(shù)據(jù)信息=訂單編號(hào)200801090001商品編號(hào)數(shù)量?jī)r(jià)格P200500015403.50P2005000232100.00P200500032600.00合計(jì)訂單總金額3103.50圖7-1訂單及其明細(xì)數(shù)據(jù)信息(10)請(qǐng)使用游標(biāo)和循環(huán)

4、語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)程 proSearchCustomer ,根據(jù)客戶編號(hào)查找該客 戶的名稱、住址、總訂單金額以及所有與該客戶有關(guān)的 商品銷售信息，并按商品分組輸出。 輸出格式如圖7-2所示。=客戶訂單表=客戶名稱：統(tǒng)一股份有限公司客戶地址：天津市總金額：31121.86商品編號(hào)總數(shù)量平均價(jià)格P20050001580.70P2005000219521.05P200500035282.00P200700042320.00報(bào)表制作人陳輝制作日期068 2012圖7-2客戶訂單表實(shí)驗(yàn)?zāi)_本：/*(1)創(chuàng)建游標(biāo)，逐行顯示 Customer表的記錄，并用 WHILE結(jié)構(gòu)來(lái)測(cè)試 Fetch_Status 的返回值

5、。輸出格式如下：客戶編號(hào)'+'-'+'客戶名稱'+'-'+'客戶電話'+'-'+'客戶住址'+''+'郵政編碼'*/declare C_no char (9),C_name char (18),C_phone char (10),C_add char (8),C_zip char (6)declare text char (100)declare cus_cur scroll cursor forselect *from Customer62select te

6、xt ='=Customer62表 的記錄='print textselect text ='客戶編號(hào)'+''+ '客戶名稱'+'' +'客戶 電話'+'' +'客戶住址'+''+'郵政編碼' print textselecttext = ='print textopen cus_curfetch cus_cur into C_no ,C_name ,C_phone ,C_add ,C_zipwhile (fetch_statu

7、s =0) beginselect text =C_no +''+ C_name +' '+C_phone +''+ C_add +''+C_zipprint textfetch cus_cur into C_no ,C_name ,C_phone ,C_add ,C_zip end close cus_curdeallocate cus_cur0消息客戶編號(hào)客戶名稱：Cust 口 mw 匚6 2喑戶電話-表的記錄,客尸住址Word資料市市市市市市市市市市津津京海京海昌海津京天天北上北上南上天北220012 220301 loeo

8、oi100903 ELCHg 33004 210230 220400 10JSOCC200&0001統(tǒng)一股份有限公司022-3&fE02C20a50092關(guān)隆就分首限公司022-95G2456四孔。9上每生物的究室.010212100£20050004五一商廈021-4EaJ1522 3'"£30 31大地商城010-11GG15CSQSQ02聯(lián)合股份有限公司0S1-GS534&C20070001南昌市朝西舟制中心0791-44121C2007a00；世界技術(shù)開(kāi)發(fā)公司021-45&4&120070003萬(wàn)事達(dá)鹿棺有限

9、公司D22-453314CIOOBOOCl紅度股林廓良公司010-S42:ltB/*(2)利用游標(biāo)修改 OrderMaster 表中orderSum 的值*/ declare orderNo varchar (20),total numeric (9,2) declare om_cur cursor forselect orderNo ,sum (quantity * price )from OrderDetail62group by orderNo open om_cur fetch om_cur into orderNo ,total while (fetch_status =0) beg

10、inupdate OrderMaster62set orderSum =totalwhere orderNo =orderNofetch om_cur into orderNo ,total end close om_cur deallocate om cur- 1行受影響， .行受影晌1 ，行受鼻融向， 1行受檢函I(1行受斃晌1 O行受;影晌* ；工行受同晌， 1彳亍受勒晌， 工行受盤唯：*/ (10),r行4m切的.rderDateorderSuminvotceMt?2006/1/9 0:00:003103.50I0000000012006/10:00:003910,0010000000

11、02汨06/1用 Q：00：QO1005.3010000000032COSR/均址 OtkOO6312.0010000000042008/2/19 0：M：00901.80100000000520003/1 0:00:001669.&01000000006MOS/3 0:0001161.40IM00000072008/3/0:00:003047.00looooooooa2008/5/9 0:00:0010756,0010000000092008/6/12 fl： 0C：Q01971,MIOOOOOCQ1Q2009/1/10:00:00lOOOCOOOll2012/6/7 0:00:00

12、0130.00I000000012M 12/6/7 0:00:001000000013/*(3)創(chuàng)建游標(biāo)，要求：輸出所有女業(yè)務(wù)員的編號(hào)、姓名、性別、所屬部門、職務(wù)、薪水 declare emNo varchar (8),emNa char (8),emse char (1 ),emde varchar emhe varchar (8),emsa numeric (8,2)declare text char (100)declare em_cur scroll cursor forselect employeeNo ,employeeName ,sex.department ,headShip

13、,salary from Employee62 where sex = 'M' selecttext ='=' print textselect text=編號(hào) 姓名 性別 所屬部門 職務(wù) 薪水 print text selecttext ='=' print textopen em_curfetch em_cur into emNo ,emNa ,emse ,emde ,emhe ,emsawhile (fetch_status =0) beginselect text =emNo +' '+emNa +' '+

14、emse +''+emde +'+emhe+ ''+ convert (char (10),emsa )print textfetch em_cur into emNo ,emNa ,emse ,emde ,emhe ,emsa end close em_cur deallocate em_cur口消息編號(hào) 姓名 性別所屬部門職務(wù)薪水B200S001 ESOOtOOS zzaocdci E7QQ7001 0工 £2008001 £200002目小意仔代詩(shī)良喻張陳吳高陳張強(qiáng)東萍鵬杰E 紀(jì) 工H M MM長(zhǎng)員任員員納員科職王職文出職科科室

15、科科丸SK公務(wù)公筍若財(cái)業(yè)辦北辦財(cái)業(yè)Leao_DOc&oo.ao 2Q 0口皿 3200.00 2700.00/*(4)創(chuàng)建存儲(chǔ)過(guò)程，要求：按表定義中的CHECK約束自動(dòng)產(chǎn)生員工編號(hào)*/create table Rnum (numberchar (8)null,enamechar (10)null)-先創(chuàng)建一張新表用來(lái)存儲(chǔ)已經(jīng)產(chǎn)生的員工編號(hào)create procedure no_tot (name nvarchar (50) as begindeclare i int ,text char (100) set i=1while (i<1000 ) beginif exists( s

16、elect number from Rnum wherenumber =( 'E'+ convert (char (4),year (getdate ()+right( '00' +convert (varchar (3),i),3 )begin set i=i+1 continueendelse begin insertRnumvalues ('E'+ convert (char (4),year (getdate ()+right( '00' + convert (varchar (3),i),3), name ) selec

17、t text ='員工編號(hào)'+''+'員工姓名'print text selecttext =('E'+convert (char (4),year (getdate ()+right( '00' +convert (varchar (3),i),3)+ ''+name-這里的兩個(gè)數(shù)字'3'就是我們要設(shè)置的id長(zhǎng)度 print text break end endend/*執(zhí)行過(guò)程*/exec no_tot 張三衣-a&Q,Rnum 族 Ubo,匕 mploy，。電工numb

18、ererisme卜E2012001tatEZO 12002楊學(xué)森E 2012003侯繼脆E201200萬(wàn)用E2012005張轂E201200G張三*NULtNULL/*(5)創(chuàng)建存儲(chǔ)過(guò)程，要求：查找姓“李”的職員的員工編號(hào)、訂單編號(hào)、訂單金額*/create procedure emli_tot emNo char (8) asselect a .employeeNo 員工編號(hào)，b .orderNo訂單編號(hào)，b .orderSum 訂單金額from Employee62 a ,OrderMaster62 b where a .employeeNo =b .salerNo and a .empl

19、oyeeName like 'emNo'/*執(zhí)行過(guò)程*/exec emli_tot '李 '/*(6)創(chuàng)建存儲(chǔ)過(guò)程，要求：統(tǒng)計(jì)每個(gè)業(yè)務(wù)員的總銷售業(yè)績(jī)，顯示業(yè)績(jī)最好的前3位業(yè)務(wù)員的銷售信息*/create procedure saler_totasselect top 3 salerNo 業(yè)務(wù)員編號(hào)，sum (orderSum )總銷售業(yè)績(jī) from OrderMaster62group by salerNoorder by sum (orderSum ) desc/*執(zhí)行過(guò)程*/exec saler_tot/*(7)創(chuàng)建存儲(chǔ)過(guò)程，要求將大客戶(銷售數(shù)量位于前5名

20、的客戶)中熱銷的前3種商品的銷售信息按如下格式輸出：大客戶中熱銷的前種商品的銷售信息商口口編rP20050003P20050004P20060002create procedure as商品名稱總銷售數(shù)量120GB 硬盤21.003.5寸軟馬區(qū)18.00網(wǎng)卡16.00*/product_totdeclare proNo char (10),proNa char (20),total int declare text char (100 ) declare sale_cur scroll cursor forselect top 3 a .productNo ,a .productName ,s

21、um (c .quantity ) from Product62 a ,OrderMaster62 b ,OrderDetail62 cwhere ductNo =ductNo and b .orderNo =c .orderNo and b .customerNo in( select top 5 m .customerNofrom OrderMaster62 m ,OrderDetail62 n where m .orderNo =n.orderNo group by m .customerNoorder by sum (quantity ) desc )group b

22、y a .productNo ,a .productNameorder by sum (c .quantity ) descselect text ='=大客戶中熱銷的前種商品的銷售信息=print textselect text ='商品編號(hào)商品名稱總銷售數(shù)量'print textopen sale_curfetch sale_cur into proNo ,proNa ,totalwhile (fetch_status =0)beginselect text =proNo +' '+proNa +' '+convert (char (

23、10),total ) print text fetch sale_cur into proNo ,proNa ,total end close sale_cur deallocate sale_cur/*執(zhí)行過(guò)程*/ exec product_tot 消息客戶悖鄴前阡中商品商品編用商品需彩P20050003L 之 更盤21P200500043.5 寸軟驅(qū)1SH 0口“0口£網(wǎng)卡1-4/*(8)創(chuàng)建存儲(chǔ)過(guò)程，要求：輸入年度，計(jì)算每個(gè)業(yè)務(wù)員的年終獎(jiǎng)金。年終獎(jiǎng)金=年銷售總 額x提成率。提成率規(guī)則如下：年銷售總額元以下部分，提成率為%,對(duì)于元及超過(guò)元部分,則提成率為% */create p

24、rocedure pride_tot date int asdeclare saleNo char (15),total numeric (9,2)declare text char (100),money numeric (8,2)declare pride_cur scroll cursor for select salerNo ,sum (orderSum ) from OrderMaster62where year (orderDate )= dategroup by salerNoselect text ='=業(yè)務(wù)員的年終獎(jiǎng)金='print textselect te

25、xt ='業(yè)務(wù)員編號(hào)年終獎(jiǎng)金'print text open pride_cur fetch pride_cur into saleNo ,total while (fetch_status =0) beginif(total <5000 )select money =total *0.1 elseselect money =500 +(total -5000 )* 0.15select text =saleNo +''+convert (char (10),money )print textfetch pride_cur into saleNo ,tot

26、al endclose pride_cur業(yè)務(wù)員編號(hào)年終獎(jiǎng)金EZ0050033ff3,50Eianaocz337,bodeallocate pride_cur /*執(zhí)行過(guò)程*/ exec pride_tot 2012 扃道息/*(9)創(chuàng)建存儲(chǔ)過(guò)程，要求將OrderMaster62 表中每一個(gè)訂單所對(duì)應(yīng)的明細(xì)數(shù)據(jù)信息按規(guī)定 格式輸出，格式如圖-1所示。=訂單及其明細(xì)數(shù)據(jù)信息=訂單編號(hào)200801090001商口口編P數(shù)量?jī)r(jià)格P200500015403.50P2005000232100.00P200500032600.00合計(jì)訂單總金額3103.50圖-1訂單及其明細(xì)數(shù)據(jù)信息*/create pr

27、ocedure orderm_tot orderno char (15) asdeclare prono char (15),quantity int,price numeric (9,2) declare text char (100 ) declare orderm_cur scroll cursor for select productNo ,sum (quantity ),sum (quantity *price ) from OrderDetail62 where orderNo =orderno group by productNo select text ='=訂單及其明

28、細(xì)數(shù)據(jù)信息= print text select text =''print text select text ='訂單編號(hào)'+ ordernoprint text select text =''print text select text ='商品編號(hào)數(shù)量?jī)r(jià)格print text open orderm_cur fetch orderm_cur into prono ,quantity ,price while (fetch_status =0) beginselect text =prono + convert (char (5),q

29、uantity )+ convert (char (10),price ) print text fetch orderm_cur into prono ©quantity ,price end select text =''print text close orderm_cur deallocate orderm_cur declaresum numeric (9,2)declareorm_cur scroll cursor for商晶華 及口砧二 P20C500M數(shù)量select orderSum from OrderMaster62 where orderNo

30、=orderno open orm_cur fetch orm_cur into sum while (fetch_status =0) begin select text ='合計(jì)訂單總金額'+' '+convert (char (12),sum) print text fetch orm_cur into sum end close orm_cur deallocate orm_cur /*執(zhí)行過(guò)程*/ exec ordermtot 200801090001價(jià)格 103.0 2100.00 hOC.OJ合計(jì)訂單總金額/*(10)請(qǐng)使用游標(biāo)和循環(huán)語(yǔ)句創(chuàng)建存儲(chǔ)過(guò)

31、程proSearchCustomer，根據(jù)客戶編號(hào)查找該客戶的名稱、住址、總訂單金額以及所有與該客戶有關(guān)的商品銷售信息，并按商品分組輸出。輸出格式如圖-2所示。=客戶訂單表=客戶名稱：統(tǒng)一股份有限公司客戶地址： 天津市總金額：31121.86商口口編r總數(shù)量平均價(jià)格P20050001580.70P2005000219521.05P200500035282.00P200700042320.00報(bào)表制作人陳輝制作日期068 2012 */create procedure proSearchCustomer (cusno char (10) asdeclare cusname char (40),a

32、ddress char (20),total numeric (9,2)declare text char (100 )declare sear_cur scroll cursor forselect a .customerName ,a .address ,sum (b .orderSum )from Customer62 a ,OrderMaster62 bwhere a. customerNo =b .customerNo and a .customerNo =cusnogroup by a .customerName ,a .addressselect text =客戶訂單表=print text select text =''print textopen sear_curfetch sear_cur into cusname while (fetch_status =0) beginselect text ='客戶名稱：'+' print textselect text ='客戶地址:'+' print textselect text =&