实验游标和存储过程

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

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

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

4、所有与该客户有关的商品销售信息，并按商品分组输出。输出格式如图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结构来测试Fetch_Status的返回值。输出格式如下：客户编号+-+客户名称+-+客户电话+

5、-+客户住址+-+邮政编码*/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=客户编号+-+客户名称+-+客户电话+-+客户住址+-+邮政编码print textselect text=print textopen cus_curf

6、etch cus_cur into C_no,C_name,C_phone,C_add,C_zipwhile (fetch_status=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 endclose cus_curdeallocate cus_cur/*(2) 利用游标修改OrderMaster表中orderSum的值*/declare orderNo varchar(20),total numeric

7、(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 into orderNo,total endclose om_curdeallocate om_cur/*(3) 创

8、建游标，要求：输出所有女业务员的编号、姓名、性别、所属部门、职务、薪水*/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,headShip,salaryfrom Employee62where sex=Mselect text=print text

9、select text=编号 姓名 性别 所属部门 职务 薪水print textselect text=print textopen em_curfetch 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

10、 em_cur/*(4) 创建存储过程，要求：按表定义中的CHECK约束自动产生员工编号*/create table Rnum(numberchar(8)null,enamechar(10)null) -先创建一张新表用来存储已经产生的员工编号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

11、()+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)select text=员工编号+员工姓名print textselect text=(E+convert(char(4),year(getdate()+right(00+convert(varchar(3),i),3)+name-这里的两个数字3 就是我们要设置的id长度pr

12、int textbreakendendend/*执行过程*/exec no_tot 张三/*(5) 创建存储过程，要求：查找姓“李”的职员的员工编号、订单编号、订单金额*/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/*执行过程*/exec emli_tot 李%/*(6)

13、 创建存储过程，要求：统计每个业务员的总销售业绩，显示业绩最好的前3位业务员的销售信息*/create procedure saler_totasselect top 3 salerNo 业务员编号,sum(orderSum) 总销售业绩from OrderMaster62group by salerNoorder by sum(orderSum) desc/*执行过程*/exec saler_tot/*(7) 创建存储过程，要求将大客户(销售数量位于前5名的客户)中热销的前3种商品的销售信息按如下格式输出：=大客户中热销的前种商品的销售信息=商品编号 商品名称 总销售数量P20050003

14、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 ductNo,ductName,sum(c.quantity)from Product62 a,OrderMaster62 b,OrderDetail62 cwhere a.pr

15、oductNo=ductNo 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 by ductNo,ductNameorder by sum(c.quantity) descselect text=大客户中热销的前种商品的销售信息=print text

16、select 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,totalendclose sale_curdeallocate sale_cur/*执行过程*/exec product_tot/*(8) 创建存储过程，要求：输入年度，计算每个业务员的

17、年终奖金。年终奖金年销售总额提成率。提成率规则如下：年销售总额元以下部分，提成率为，对于元及超过元部分，则提成率为*/create procedure 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 saler

18、Noselect 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+(total-5000)*0.15select text=saleNo+convert(char(10),money)print textfetch pride_cur into saleNo,totalendcl

19、ose pride_curdeallocate pride_cur/*执行过程*/exec pride_tot 2012/*(9) 创建存储过程，要求将OrderMaster62表中每一个订单所对应的明细数据信息按规定格式输出，格式如图-1所示。 =订单及其明细数据信息= - 订单编号 200801090001 - 商品编号 数量 价格 P20050001 5 403.50 P20050002 3 2100.00 P20050003 2 600.00 - 合计订单总金额 3103.50 图-1 订单及其明细数据信息*/create procedure orderm_tot orderno ch

20、ar(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 OrderDetail62where orderNo=ordernogroup by productNoselect text=订单及其明细数据信息=print textselect text=-print textselect text=订单编号+

21、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),quantity)+convert(char(10),price)print textfetch orderm_cur into prono,quantity,priceendselect text=-print text

22、close orderm_curdeallocate orderm_curdeclare sum numeric(9,2)declare orm_cur scroll cursor forselect orderSumfrom OrderMaster62where orderNo=ordernoopen orm_curfetch orm_cur into sumwhile(fetch_status=0)beginselect text=合计订单总金额+convert(char(12),sum)print textfetch orm_cur into sumendclose orm_curdea

23、llocate orm_cur/*执行过程*/exec orderm_tot 200801090001/*(10) 请使用游标和循环语句创建存储过程proSearchCustomer，根据客户编号查找该客户的名称、住址、总订单金额以及所有与该客户有关的商品销售信息，并按商品分组输出。输出格式如图-2所示。=客户订单表= - 客户名称： 统一股份有限公司 客户地址： 天津市 总金额： 31121.86 - 商品编号 总数量 平均价格 P20050001 5 80.70 P20050002 19 521.05 P20050003 5 282.00 P20070004 2 320.00 报表制作人陈

24、辉 制作日期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 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_s