數(shù)據(jù)庫系統(tǒng)原理與設(shè)計(jì)實(shí)驗(yàn)教程答案 實(shí)驗(yàn)十二_游標(biāo)和存儲過程

1、實(shí)驗(yàn)十二 游標(biāo)與存儲過程請完成以下實(shí)驗(yàn)內(nèi)容：(1) 創(chuàng)建游標(biāo)，逐行顯示Customer表的記錄，并用WHILE結(jié)構(gòu)來測試Fetch_Status的返回值。輸出格式如下：客戶編號+-+客戶名稱+-+客戶住址+-+客戶電話+-+郵政編碼(2) 利用游標(biāo)修改OrderMaster表中orderSum的值。(3) 創(chuàng)建游標(biāo)，要求：輸出所有女業(yè)務(wù)員的編號、姓名、性別、所屬部門、職務(wù)、薪水。(4) 創(chuàng)建存儲過程，要求：按表定義中的CHECK約束自動產(chǎn)生員工編號。(5) 創(chuàng)建存儲過程，要求：查找姓“李”的職員的員工編號、訂單編號、訂單金額。(6) 創(chuàng)建存儲過程，要求：統(tǒng)計(jì)每個(gè)業(yè)務(wù)員的總銷售業(yè)績，顯示業(yè)績最好

2、的前3位業(yè)務(wù)員的銷售信息。(7) 創(chuàng)建存儲過程，要求將大客戶(銷售數(shù)量位于前5名的客戶)中熱銷的前3種商品的銷售信息按如下格式輸出：=大客戶中熱銷的前3種商品的銷售信息=商品編號 商品名稱 總銷售數(shù)量P20050003 120GB硬盤 21.00P20050004 3.5寸軟驅(qū) 18.00P20060002 網(wǎng)卡 16.00(8) 創(chuàng)建存儲過程，要求：輸入年度，計(jì)算每個(gè)業(yè)務(wù)員的年終獎(jiǎng)金。年終獎(jiǎng)金年銷售總額提成率。提成率規(guī)則如下：年銷售總額5000元以下部分，提成率為10，對于5000元及超過5000元部分，則提成率為15。(9) 創(chuàng)建存儲過程，要求將OrderMaster表中每一個(gè)訂單所對應(yīng)的

3、明細(xì)數(shù)據(jù)信息按規(guī)定格式輸出，格式如圖7-1所示。=訂單及其明細(xì)數(shù)據(jù)信息= - 訂單編號 200801090001 - 商品編號 數(shù)量 價(jià)格 P20050001 5 403.50 P20050002 3 2100.00 P20050003 2 600.00 - 合計(jì)訂單總金額 3103.50 圖7-1 訂單及其明細(xì)數(shù)據(jù)信息(10) 請使用游標(biāo)和循環(huán)語句創(chuàng)建存儲過程proSearchCustomer，根據(jù)客戶編號查找該客戶的名稱、住址、總訂單金額以及所有與該客戶有關(guān)的商品銷售信息，并按商品分組輸出。輸出格式如圖7-2所示。=客戶訂單表= - 客戶名稱： 統(tǒng)一股份有限公司 客戶地址： 天津市 總金額

4、： 31121.86 - 商品編號 總數(shù)量 平均價(jià)格 P20050001 5 80.70 P20050002 19 521.05 P20050003 5 282.00 P20070004 2 320.00 報(bào)表制作人 陳輝 制作日期 06 8 2012 圖7-2 客戶訂單表實(shí)驗(yàn)?zāi)_本：/*(1) 創(chuàng)建游標(biāo)，逐行顯示Customer表的記錄，并用WHILE結(jié)構(gòu)來測試Fetch_Status的返回值。輸出格式如下：客戶編號+-+客戶名稱+-+客戶電話+-+客戶住址+-+郵政編碼*/declare C_no char(9),C_name char(18),C_phone char(10),C_add

5、char(8),C_zip char(6)declare text char(100)declare cus_cur scroll cursor forselect *from Customer62select text=Customer62表的記錄=print textselect text=客戶編號+-+客戶名稱+-+客戶電話+-+客戶住址+-+郵政編碼print textselect text=print textopen cus_curfetch cus_cur into C_no,C_name,C_phone,C_add,C_zipwhile (fetch_status=0)begi

6、nselect 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 endclose cus_curdeallocate cus_cur/*(2) 利用游標(biāo)修改OrderMaster表中orderSum的值*/declare orderNo varchar(20),total numeric(9,2)declare om_cur cursor forselect orderNo,sum(quantity*price)from OrderD

7、etail62group by orderNoopen om_curfetch om_cur into orderNo,total while (fetch_status=0)begin update OrderMaster62 set orderSum=totalwhere orderNo=orderNofetch om_cur into orderNo,total endclose om_curdeallocate om_cur/*(3) 創(chuàng)建游標(biāo)，要求：輸出所有女業(yè)務(wù)員的編號、姓名、性別、所屬部門、職務(wù)、薪水*/declare emNo varchar(8),emNa char(8),e

8、mse char(1),emde varchar(10),emhe varchar(8),emsa numeric(8,2)declare text char(100)declare em_cur scroll cursor forselect employeeNo,employeeName,sex,department,headShip,salaryfrom Employee62where sex=Mselect text=print textselect text=編號 姓名 性別 所屬部門 職務(wù) 薪水print textselect text=print textopen em_curf

9、etch em_cur into emNo,emNa,emse,emde,emhe,emsawhile (fetch_status=0)beginselect text=emNo+ +emNa+ +emse+ +emde+ +emhe+ +convert(char(10),emsa)print text fetch em_cur into emNo,emNa,emse,emde,emhe,emsaendclose em_curdeallocate em_cur/*(4) 創(chuàng)建存儲過程，要求：按表定義中的CHECK約束自動產(chǎn)生員工編號*/create table Rnum(numberchar(

10、8)null,enamechar(10)null) -先創(chuàng)建一張新表用來存儲已經(jīng)產(chǎn)生的員工編號create procedure no_tot(name nvarchar(50) asbegin declare i int,text char(100)set i=1while(i1000)beginif exists(select numberfrom Rnumwhere number=(E+convert(char(4),year(getdate()+right(00+convert(varchar(3),i),3)beginset i=i+1continueendelsebegininser

11、t Rnum values(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-這里的兩個(gè)數(shù)字3 就是我們要設(shè)置的id長度print textbreakendendend/*執(zhí)行過程*/exec no_tot 張三/*(5) 創(chuàng)建存儲過程，要求：查找姓“李”的職員的員工編號、

12、訂單編號、訂單金額*/create procedure emli_tot emNo char(8)asselect a.employeeNo 員工編號,b.orderNo 訂單編號,b.orderSum 訂單金額from Employee62 a,OrderMaster62 bwhere a.employeeNo=b.salerNo and a.employeeName like emNo/*執(zhí)行過程*/exec emli_tot 李%/*(6) 創(chuàng)建存儲過程，要求：統(tǒng)計(jì)每個(gè)業(yè)務(wù)員的總銷售業(yè)績，顯示業(yè)績最好的前3位業(yè)務(wù)員的銷售信息*/create procedure saler_totasse

13、lect top 3 salerNo 業(yè)務(wù)員編號,sum(orderSum) 總銷售業(yè)績from OrderMaster62group by salerNoorder by sum(orderSum) desc/*執(zhí)行過程*/exec saler_tot/*(7) 創(chuàng)建存儲過程，要求將大客戶(銷售數(shù)量位于前5名的客戶)中熱銷的前3種商品的銷售信息按如下格式輸出：=大客戶中熱銷的前種商品的銷售信息=商品編號 商品名稱 總銷售數(shù)量P20050003 120GB硬盤 21.00P20050004 3.5寸軟驅(qū) 18.00P20060002 網(wǎng)卡 16.00*/create procedure pro

14、duct_totasdeclare proNo char(10),proNa char(20),total intdeclare text char(100)declare sale_cur scroll cursor forselect top 3 ductNo,ductName,sum(c.quantity)from Product62 a,OrderMaster62 b,OrderDetail62 cwhere ductNo=ductNo and b.orderNo=c.orderNo andb.customerNo in(select top 5

15、 m.customerNofrom OrderMaster62 m,OrderDetail62 nwhere m.orderNo=n.orderNogroup by m.customerNoorder by sum(quantity) desc)group by ductNo,ductNameorder by sum(c.quantity) descselect text=大客戶中熱銷的前種商品的銷售信息=print textselect text=商品編號 商品名稱 總銷售數(shù)量print textopen sale_curfetch sale_cur into proNo

16、,proNa,totalwhile(fetch_status=0)beginselect text=proNo+ +proNa+ +convert(char(10),total)print textfetch sale_cur into proNo,proNa,totalendclose sale_curdeallocate sale_cur/*執(zhí)行過程*/exec product_tot/*(8) 創(chuàng)建存儲過程，要求：輸入年度，計(jì)算每個(gè)業(yè)務(wù)員的年終獎(jiǎng)金。年終獎(jiǎng)金年銷售總額提成率。提成率規(guī)則如下：年銷售總額元以下部分，提成率為，對于元及超過元部分，則提成率為*/create procedure

17、 pride_tot date intasdeclare saleNo char(15),total numeric(9,2)declare text char(100),money numeric(8,2)declare pride_cur scroll cursor forselect salerNo,sum(orderSum)from OrderMaster62where year(orderDate)=dategroup by salerNoselect text=業(yè)務(wù)員的年終獎(jiǎng)金=print textselect text=業(yè)務(wù)員編號年終獎(jiǎng)金print textopen pride_

18、curfetch pride_cur into saleNo,totalwhile(fetch_status=0)beginif(total5000)select money=total*0.1elseselect money=500+(total-5000)*0.15select text=saleNo+convert(char(10),money)print textfetch pride_cur into saleNo,totalendclose pride_curdeallocate pride_cur/*執(zhí)行過程*/exec pride_tot 2012/*(9) 創(chuàng)建存儲過程，要求

19、將OrderMaster62表中每一個(gè)訂單所對應(yīng)的明細(xì)數(shù)據(jù)信息按規(guī)定格式輸出，格式如圖-1所示。 =訂單及其明細(xì)數(shù)據(jù)信息= - 訂單編號 200801090001 - 商品編號 數(shù)量 價(jià)格 P20050001 5 403.50 P20050002 3 2100.00 P20050003 2 600.00 - 合計(jì)訂單總金額 3103.50 圖-1 訂單及其明細(xì)數(shù)據(jù)信息*/create procedure orderm_tot orderno char(15)asdeclare prono char(15),quantity int,price numeric(9,2)declare text

20、char(100)declare orderm_cur scroll cursor forselect productNo,sum(quantity),sum(quantity*price)from OrderDetail62where orderNo=ordernogroup by productNoselect text=訂單及其明細(xì)數(shù)據(jù)信息=print textselect text=-print textselect text=訂單編號+ordernoprint textselect text=-print textselect text=商品編號 數(shù)量 價(jià)格print textope

21、n orderm_curfetch orderm_cur into prono,quantity,pricewhile(fetch_status=0)beginselect text=prono+convert(char(5),quantity)+convert(char(10),price)print textfetch orderm_cur into prono,quantity,priceendselect text=-print textclose orderm_curdeallocate orderm_curdeclare sum numeric(9,2)declare orm_cu

22、r scroll cursor forselect orderSumfrom OrderMaster62where orderNo=ordernoopen orm_curfetch orm_cur into sumwhile(fetch_status=0)beginselect text=合計(jì)訂單總金額+convert(char(12),sum)print textfetch orm_cur into sumendclose orm_curdeallocate orm_cur/*執(zhí)行過程*/exec orderm_tot 200801090001/*(10) 請使用游標(biāo)和循環(huán)語句創(chuàng)建存儲過程p

23、roSearchCustomer，根據(jù)客戶編號查找該客戶的名稱、住址、總訂單金額以及所有與該客戶有關(guān)的商品銷售信息，并按商品分組輸出。輸出格式如圖-2所示。=客戶訂單表= - 客戶名稱： 統(tǒng)一股份有限公司 客戶地址： 天津市 總金額： 31121.86 - 商品編號 總數(shù)量 平均價(jià)格 P20050001 5 80.70 P20050002 19 521.05 P20050003 5 282.00 P20070004 2 320.00 報(bào)表制作人陳輝 制作日期06 8 2012 */create procedure proSearchCustomer (cusno char(10)asdecla

24、re cusname char(40),address 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 textselect text=-print textopen sear_curfetch sear_cur into cusname,address,totalwhile(fetch_status=