实验游标和存储过程.doc

1、1 / 12实验九 游标与存储过程1 实验目的与要求(1) 掌握游标的定义和使用方法。(2) 掌握存储过程的定义、执行和调用方法。(3) 掌握游标和存储过程的综合应用方法。2 实验内容请完成以下实验内容：(1) 创建游标，逐行显示 Customer 表的记录，并用 WHILE 结构来测试Fetch_Status 的返回值。输出格式如下：客户编号+-+ 客户名称+-+客户住址+-+客户电话+-+ 邮政编码(2) 利用游标修改 OrderMaster 表中 orderSum 的值。(3) 创建游标，要求：输出所有女业务员的编号、姓名、性别、所属部门、职务、薪水。(4) 创建存储过程，要求：按表定义

2、中的 CHECK 约束自动产生员工编号。(5) 创建存储过程，要求：查找姓“李”的职员的员工编号、订单编号、 订单金额。(6) 创建存储过程，要求：统计每个业务员的总销售业绩，显示业绩最好的前 3 位业务员的销售信息。(7) 创建存储过程，要求将大客户( 销售数量位于前 5 名的客户)中热销的前 3 种商品的销售信息按如下格式输出：=大客户中热销的前 3种商品的销售信息=商品编号 商品名称 总销售数量P20050003 120GB硬盘 21.00P20050004 3.5寸软驱 18.00P20060002 网卡 16.00(8) 创建存储过程，要求：输入年度，计算每个业务员的年终奖金。年终奖

3、金年销售总额提成率。提成率规则如下：年销售总额 5000 元以下部分，提成率为 10，对于5000 元及超过 5000 元部分，则提成率为 15。(9) 创建存储过程，要求将 OrderMaster 表中每一个订单所对应的明细数据信息按规定格式输出，格式如图 7-1 所示。=订单及其明细数据信息= - 订单编号 200801090001 - 商品编号 数量 价格 P20050001 5 403.50 P20050002 3 2100.00 P20050003 2 600.00 - 合计订单总金额 3103.50 2 / 12图7-1 订单及其明细数据信息(10) 请使用游标和循环语句创建存储过

4、程proSearchCustomer，根据客户编号查找该客户的名称、住址、总订单金额以及所有与该客户有关的商品销售信息，并按商品分组输出。输出格式如图7-2所示。=客户订单表= - 客户名称： 统一股份有限公司 客户地址： 天津市 总金额： 31121.86 - 商品编号 总数量 平均价格 P20050001 5 80.70 P20050002 19 521.05 P20050003 5 282.00 P20070004 2 320.00 报表制作人 陈辉 制作日期 06 8 2012 图 7-2 客户订单表实验脚本：/*(1) 创建游标，逐行显示Customer表的记录，并用WHILE结构来

5、测试Fetch_Status 的返回值。输出格式如下：客户编号+-+ 客户名称+-+客户电话+-+客户住址+-+ 邮政编码*/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 text=Customer62表的记录=print textselect text=客户编号 +-+客户名称+-+客户电话+-+客户住址+-+

6、邮政编码print textselect text=print textopen cus_curfetch cus_cur into C_no,C_name,C_phone,C_add,C_zipwhile (fetch_status=0)begin3 / 12select 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) 利用游标修改OrderMaster

7、 表中orderSum的值*/declare orderNo varchar(20),total numeric(9,2)declare om_cur cursor forselect orderNo,sum(quantity*price)from OrderDetail62group by orderNoopen om_curfetch om_cur into orderNo,total while (fetch_status=0)begin update OrderMaster62 set orderSum=totalwhere orderNo=orderNofetch om_cur in

8、to orderNo,total endclose om_curdeallocate om_cur4 / 12/*(3) 创建游标，要求：输出所有女业务员的编号、姓名、性别、所属部门、职务、薪水*/declare emNo varchar(8),emNa char(8),emse 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,h

9、eadShip,salaryfrom Employee62where sex=Mselect text=print textselect text=编号 姓名 性别 所属部门 职务 薪水print textselect text=print textopen em_curfetch em_cur into emNo,emNa,emse,emde,emhe,emsa5 / 12while (fetch_status=0)beginselect text=emNo+ +emNa+ +emse+ +emde+ +emhe+ +convert(char(10),emsa)print text fetc

10、h em_cur into emNo,emNa,emse,emde,emhe,emsaendclose em_curdeallocate em_cur/*(4) 创建存储过程，要求：按表定义中的CHECK约束自动产生员工编号*/create table Rnum(number char(8) null,ename char(10) null) -先创建一张新表用来存储已经产生的员工编号create procedure no_tot(name nvarchar(50) asbegindeclare i int,text char(100)set i=1while(i1000)beginif ex

11、ists(select numberfrom Rnumwhere number=(E+convert(char(4),year(getdate()+right(00+convert(varchar(3),i),3)beginset i=i+1continueendelsebegininsert Rnum values(E+convert(char(4),year(getdate()+right(00+convert(varchar(3),i),3),name)6 / 12select text=员工编号+ +员工姓名print textselect text=(E+convert(char(4

12、),year(getdate()+right(00+convert(varchar(3),i),3)+name-这里的两个数字3 就是我们要设置的 id长度print textbreakendendend/*执行过程*/exec no_tot 张三/*(5) 创建存储过程，要求：查找姓“李” 的职员的员工编号、订单编号、订单金额 */create procedure emli_tot emNo char(8)asselect a.employeeNo 员工编号 ,b.orderNo 订单编号,b .orderSum 订单金额from Employee62 a,OrderMaster62 bwh

13、ere a.employeeNo=b.salerNo and a.employeeName like emNo/*执行过程*/exec emli_tot 李 %/*(6) 创建存储过程，要求：统计每个业务员的总销售业绩，显示业绩最好的前3位业务员的销售信息*/create procedure saler_totasselect top 3 salerNo 业务员编号 ,sum(orderSum) 总销售业绩from OrderMaster62group by salerNoorder by sum(orderSum) desc7 / 12/*执行过程*/exec saler_tot/* (7)

14、 创建存储过程，要求将大客户(销售数量位于前 5名的客户) 中热销的前3种商品的销售信息按如下格式输出：=大客户中热销的前种商品的销售信息=商品编号 商品名称 总销售数量P20050003 120GB硬盘 21.00P20050004 3.5寸软驱 18.00P20060002 网卡 16.00*/create procedure product_totasdeclare proNo char(10),proNa char(20),total intdeclare text char(100)declare sale_cur scroll cursor forselect top 3 a.pr

15、oductNo,a.productName,sum(c.quantity)from Product62 a,OrderMaster62 b,OrderDetail62 cwhere a.productNo=c.productNo and b.orderNo=c.orderNo andb.customerNo in(select top 5 m.customerNofrom OrderMaster62 m,OrderDetail62 nwhere m.orderNo=n.orderNogroup by m.customerNoorder by sum(quantity) desc)group b

16、y a.productNo,a.productNameorder by sum(c.quantity) descselect text=大客户中热销的前种商品的销售信息=print textselect text=商品编号 商品名称 总销售数量print textopen sale_curfetch sale_cur into proNo,proNa,totalwhile(fetch_status=0)beginselect text=proNo+ +proNa+ +convert(char(10),total)print textfetch sale_cur into proNo,proNa

17、,totalendclose sale_cur8 / 12deallocate sale_cur/*执行过程*/exec product_tot/*(8) 创建存储过程，要求：输入年度，计算每个业务员的年终奖金。年终奖金年销售总额提成率。提成率规则如下：年销售总额元以下部分，提成率为，对于元及超过元部分，则提成率为*/create procedure pride_tot date intasdeclare saleNo char(15),total numeric(9,2)declare text char(100),money numeric(8,2)declare pride_cur sc

18、roll cursor forselect salerNo,sum(orderSum)from OrderMaster62where year(orderDate)=dategroup by salerNoselect text=业务员的年终奖金=print textselect text=业务员编号 年终奖金print textopen pride_curfetch pride_cur into saleNo,totalwhile(fetch_status=0)beginif(total5000)select money=total*0.1elseselect money=500+(tota

19、l-5000)*0.15select text=saleNo+ +convert(char(10),money)print textfetch pride_cur into saleNo,totalendclose pride_curdeallocate pride_cur/*执行过程*/exec pride_tot 20129 / 12/*(9) 创建存储过程，要求将OrderMaster62 表中每一个订单所对应的明细数据信息按规定格式输出，格式如图-1所示。=订单及其明细数据信息= - 订单编号 200801090001 - 商品编号 数量 价格 P20050001 5 403.50 P

20、20050002 3 2100.00 P20050003 2 600.00 - 合计订单总金额 3103.50 图-1 订单及其明细数据信息*/create procedure orderm_tot orderno char(15)asdeclare prono char(15),quantity int,price numeric(9,2)declare text char(100)declare orderm_cur scroll cursor forselect productNo,sum(quantity),sum(quantity*price)from OrderDetail62wh

21、ere orderNo=ordernogroup by productNoselect text=订单及其明细数据信息=print textselect text=-print textselect text=订单编号 +ordernoprint textselect text=-print textselect text=商品编号 数量 价格print textopen orderm_curfetch orderm_cur into prono,quantity,pricewhile(fetch_status=0)beginselect text=prono+ +convert(char(5

22、),quantity)+ +convert(char(10),price)print text10 / 12fetch orderm_cur into prono,quantity,priceendselect text=-print textclose orderm_curdeallocate orderm_curdeclare sum numeric(9,2)declare orm_cur scroll cursor forselect orderSumfrom OrderMaster62where orderNo=ordernoopen orm_curfetch orm_cur into

23、 sumwhile(fetch_status=0)beginselect text=合计订单总金额+ +convert(char(12),sum)print textfetch orm_cur into sumendclose orm_curdeallocate orm_cur/*执行过程*/exec orderm_tot 200801090001/*(10) 请使用游标和循环语句创建存储过程proSearchCustomer，根据客户编号查找该客户的名称、住址、总订单金额以及所有与该客户有关的商品销售信息，并按商品分组输出。输出格式如图-2所示。=客户订单表= - 客户名称： 统一股份有限公

24、司 客户地址： 天津市 总金额： 31121.86 - 商品编号 总数量 平均价格 P20050001 5 80.70 P20050002 19 521.05 P20050003 5 282.00 11 / 12P20070004 2 320.00 报表制作人陈辉 制作日期06 8 2012 */create procedure proSearchCustomer (cusno char(10)asdeclare cusname char(40),address char(20),total numeric(9,2)declare text char(100)declare sear_cur

25、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

26、_status=0)beginselect text=客户名称: + +cusnameprint textselect text=客户地址: + +addressprint textselect text=总金额: + +convert(char(12),total)print textfetch sear_cur into cusname,address,totalendselect text=-close sear_curdeallocate sear_curdeclare productno char(10),num int,avg numeric(9,2)declare searpro

27、_cur scroll cursor forselect productNo,sum(quantity),avg(price)from OrderMaster62 a,OrderDetail62 bwhere a.orderNo=b.orderNo and a.customerNo=C20050001group by productNoselect text=商品编号 总数量 平均价格print textopen searpro_curfetch searpro_cur into productno,num,avgwhile(fetch_status=0)beginselect text=productno+ +convert(char(8),num)+ +convert(char(12),avg)12 / 12print textfetch searpro_cur into productno,num,avgendselect text=报表制作人杨学森 制作日期 2012-12-1print textclose searpro_curdeallocate searpro_cur/*执行过程*/exec proSearchCustomer C20050001