數(shù)據(jù)庫原理實驗9

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

2、ar(10),cus_Name char(20),cus_add char(10),cus_Tele char(10),cus_Code char(7)-定義幾個變量DECLARE text char(100)DECLARE cus_cur SCROLL CURSOR FORSELECT customerNo48,customerName48,address48,telephone48,zip48FROM customer48ORDER BY customerNo48 -定義游標(biāo)select text='=0103348熊昌磊='PRINT textselect text=&#

3、39; 客戶編號'+'-'+'客戶名稱'+'-'+'客戶住址'+'-'+'客戶電話'+'-'+'郵政編碼'PRINT textselect text='-'PRINT text -格式化輸出open cus_cur -打開游標(biāo)FETCH cus_cur INTO cus_No,cus_Name,cus_add,cus_Tele,cus_Code-提取游標(biāo)中的信息并分別給內(nèi)存變量while (FETCH_status=0)BEGIN SELECT

4、 text='|'+cus_No+' '+cus_Name+' '+cus_add+' '+cus_Tele+' '+cus_Code+'|'PRINT textFETCH cus_cur INTO cus_No,cus_Name,cus_add,cus_Tele,cus_CodeENDselect text='-'PRINT textselect text='='PRINT text CLOSE cus_cur-關(guān)閉游標(biāo)DEALLOCATE cus_cur截圖：(2

5、) 利用游標(biāo)修改OrderMaster表中orderSum的值。腳本：DECLARE cus_No char(15)-定義幾個變量DECLARE cus_total numeric(8,2),text char(100)DECLARE cus_cur SCROLL CURSOR FORselect orderNo48,sum(quantity48*price48) as totalfrom orderdetail48group by orderNo48select text=' 訂單編號'+' '+' 訂單總額'print textopen cu

6、s_curfetch cus_cur into cus_No,cus_totalwhile(fetch_status=0)beginselect cus_total=ordersum48from orderMaster48where orderNo48=cus_Noselect text=cus_No+convert(char(10),cus_total)print text fetch cus_cur into cus_No,cus_totalENDCLOSE cus_cur-關(guān)閉游標(biāo)DEALLOCATE cus_cur截圖：(3) 創(chuàng)建游標(biāo)，要求：輸出所有女業(yè)務(wù)員的編號、姓名、性別、所屬部

7、門、職務(wù)、薪水。腳本：declare emp_No char(10),emp_Name char(10),emp_sex char(3),emp_dep char(8),emp_head char(8)declare emp_salary numeric(8,2),text char(100)declare emp_cur SCROLL CURSOR FORselect employeeNo48,employeeName48,sex48,department48,headship48,salary48from employee48where sex48='M' select t

8、ext='=女業(yè)務(wù)員的信息='print textselect text='編號 姓名 性別 部門 職位 薪水 'print textselect text='-'print textopen emp_curfetch emp_cur into emp_No,emp_Name ,emp_sex ,emp_dep ,emp_head ,emp_salarywhile(fetch_status=0)begin select text=emp_No+' '+emp_Name+' '+emp_sex +' '

9、;+emp_dep +' '+emp_head +' '+convert(char(10),emp_salary)print textfetch emp_cur into emp_No,emp_Name ,emp_sex ,emp_dep ,emp_head ,emp_salaryendclose emp_curdeallocate emp_cur截圖：(4) 創(chuàng)建存儲過程，要求：按表定義中的CHECK約束自動產(chǎn)生員工編號。腳本：create procedure num6 num intasdeclare year char(4),pro_max int,nco

10、unt intdeclare autoNum char(8),text char(50)select ncount = 0declare anum scroll cursor forselect substring(max(employeeNo48),2,4) pro_year,convert(int,substring(max(employeeNo48),6,3) pro_maxfrom Employee48select text = '=自動產(chǎn)生的員工編號='print textopen anumfetch anum into year,pro_maxwhile(fetch

11、_status = 0)beginif year = convert(char(4),(year(getdate()beginwhile(ncount < num and pro_max < 999)beginselect pro_max = pro_max + 1select autoNum = 'E' + convert(char(4),year(getdate() + substring(convert(char(4),1000 + pro_max),2,3)print autoNumselect ncount = ncount + 1endif pro_ma

12、x = 999beginprint '編號溢出！'endendelsebeginselect pro_max = 0while(ncount < num and pro_max < 999)beginselect pro_max = pro_max + 1select autoNum = 'E' + convert(char(4),year(getdate() + substring(convert(char(4),1000 + pro_max),2,3)print autoNumselect ncount = ncount + 1endif pro

13、_max = 999beginprint '編號溢出！'endendfetch anum into year,pro_maxendclose anumdeallocate anum(5) 創(chuàng)建存儲過程，要求：查找姓“李”的職員的員工編號、訂單編號、訂單金額。創(chuàng)建存儲過程：create procedure emp_tot emp_name varchar(10)ASselect employeeNo48,orderNo48,ordersum48from orderMaster48 a,(select employeeNo48from employee48where employe

14、eName48 like emp_name)bwhere a.salerNo48=b.employeeNo48 執(zhí)行存儲過程：exec emp_tot emp_name='李%'截圖：(6) 創(chuàng)建存儲過程，要求：統(tǒng)計每個業(yè)務(wù)員的總銷售業(yè)績，顯示業(yè)績最好的前3位業(yè)務(wù)員的銷售信息。創(chuàng)建存儲過程：create procedure emp_tot2 ASselect employeeNo48,employeeName48,orderNo48,ordersum48from orderMaster48 a,(select top 3 employeeNo48,employeeName48,

15、sum(ordersum48) totalfrom employee48 a,orderMaster48 bwhere a.employeeNo48=b.salerNo48group by employeeName48,employeeNO48order by total desc) bwhere a.salerNo48=b.employeeNo48order by ordersum48 desc執(zhí)行存儲過程：Exec emp_tot2截圖：(7) 創(chuàng)建存儲過程，要求將大客戶(銷售數(shù)量位于前5名的客戶)中熱銷的前3種商品的銷售信息按如下格式輸出：=大客戶中熱銷的前3種商品的銷售信息=商品編號

16、商品名稱 總銷售數(shù)量P20050003 120GB硬盤 21.00P20050004 3.5寸軟驅(qū) 18.00P20060002 網(wǎng)卡 16.00腳本：create procedure emp_tzt ASbegin declare text char(100),emp_No char(15),emp_name char(20),emp_qty numeric(8,2)select text='=大客戶中熱銷的前種商品的銷售信息='print textselect text='商品編號 商品名稱 總銷售數(shù)量 'print textdeclare get_tot

17、cursor forselect ductNo48,productName48,總銷售數(shù)量from product48 a,(select top 3 productNo48,sum(quantity48 ) 總銷售數(shù)量from orderDetail48 a,(select top 5 customerNo48,a.orderNo48 ,sum(sun) totalfrom orderMaster48 a,(select orderNo48,sum(quantity48) sunfrom orderdetail48group by orderNo48) bwhere a.order

18、No48=b.orderNo48group by customerNo48,a.orderNo48order by total desc)bwhere a.orderNo48=b.orderNo48group by productNo48,quantity48order by 總銷售數(shù)量desc) bwhere ductNo48=ductNo48open get_totfetch get_tot into emp_No,emp_name ,emp_qty while(fetch_status=0)begin select text=emp_No+' '+em

19、p_name+' '+convert(char(10),emp_qty )print textfetch get_tot into emp_No ,emp_name ,emp_qtyendclose get_totdeallocate get_totend結(jié)果：(8) 創(chuàng)建存儲過程，要求：輸入年度，計算每個業(yè)務(wù)員的年終獎金。年終獎金年銷售總額×提成率。提成率規(guī)則如下：年銷售總額5000元以下部分，提成率為10，對于5000元及超過5000元部分，則提成率為15。腳本：CREATE procedure proc_caa_bonus1 year char(4)ASbegi

20、n declare emp_name char(10) ,emp_no char(5) ,year_tot_amt numeric(12,2) , bonus numeric(9,2)declare text char(100)select text='=年終獎金信息='print textselect text=' 業(yè)務(wù)員編號 業(yè)務(wù)員姓名 總銷售金額 年終獎金 'print textselect text='-'print textdeclare mycur cursor for select b.employeeNo48, b.employe

21、eName48 , sum(ordersum48) as year_tot_amt from ordermaster48 a, employee48 b where convert(char(4), orderdate48,120)=year and a.salerNo48=b.employeeNo48group by employeeNo48,employeeName48 open mycurfetch mycur into emp_no,emp_name, year_tot_amt while (fetch_status=0) begin if year_tot_amt<5000 s

22、elect 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 textfetch mycur into emp_no,emp_name, year_tot_amt end close mycur deallocate myc

23、ur end截圖：(9) 創(chuàng)建存儲過程，要求將OrderMaster表中每一個訂單所對應(yīng)的明細(xì)數(shù)據(jù)信息按規(guī)定格式輸出，格式如圖7-1所示。=訂單及其明細(xì)數(shù)據(jù)信息= - 訂單編號 200801090001 - 商品編號 數(shù)量 價格 P20050001 5 403.50 P20050002 3 2100.00 P20050003 2 600.00 - 合計訂單總金額 3103.50 圖7-1 訂單及其明細(xì)數(shù)據(jù)信息腳本如下：CREATE procedure km_totASbegin declare emp_no char(12) ,emp_pno char(10) ,quantity numeri

24、c(8,2) , price numeric(8,2),cus char(12),shu numeric(8,2)declare text char(100)select text='=訂單及其明細(xì)數(shù)據(jù)信息='print textselect text='-'print textdeclare order_cus cursor forselect orderNo48,productNo48,quantity48,price48from orderdetail48open order_cusfetch order_cus into emp_no ,emp_pno

25、,quantity , price set cus=''set shu=0.00while(fetch_status=0)beginif(cus!=emp_no)beginif(cus!='')beginselect text='-'print textselect text='合計訂單總金額 '+convert(char(10),shu)print textselect text='-'print textselect text=' 'print textset shu=0.00endelse b

26、eginprint ' 'endselect text='訂單編號 '+emp_noprint textselect text='-'print textselect text='商品編號 數(shù)量 價格'print textselect text=emp_pno+' '+convert(char(10),quantity)+' '+convert(char(10),price)print textset shu=price+shuset cus=emp_nofetch order_cus into e

27、mp_no ,emp_pno,quantity , price endelse beginselect text=emp_pno+' '+convert(char(10),quantity)+' '+convert(char(10),price)print textset shu=price+shufetch order_cus into emp_no ,emp_pno ,quantity , price endendselect text='-'print textclose order_cusdeallocate order_cusend截圖

28、：(10) 請使用游標(biāo)和循環(huán)語句創(chuàng)建存儲過程proSearchCustomer，根據(jù)客戶編號查找該客戶的名稱、住址、總訂單金額以及所有與該客戶有關(guān)的商品銷售信息，并按商品分組輸出。輸出格式如圖7-2所示。=客戶訂單表= - 客戶名稱： 統(tǒng)一股份有限公司 客戶地址： 天津市 總金額： 31121.86 - 商品編號 總數(shù)量 平均價格 P20050001 5 80.70 P20050002 19 521.05 P20050003 5 282.00 P20070004 2 320.00 報表制作人 陳輝 制作日期 06 8 2012 圖7-2 客戶訂單表腳本如下：Create procedure p

29、roSearchCustomer12 cus_No char(10)ASbegindeclare address char(8),total numeric(8,2),price numeric(8,2),proNo char(10),quantity numeric(5,2),avg_price numeric(8,2)declare text char(100),ss numeric(8,2),cus_Name char(20)declare xp_tot cursor forselect customerName48,address48, productNo48,sum(quantity

30、48) totalquty ,sum(price48) avgprice,sum(quantity48*price48) totalSumfrom(select top 30 a.customerNo48,a.customerName48,address48,productNo48,quantity48,price48from (select customerNo48,customerName48,address48from customer48) a,orderMaster48 b,orderdetail48 cwhere a.customerNo48=b.customerNo48 and b.orderNo48=c.orderNo48order by productNo48) awhere cust