数据库系统原理与设计实验教程答案 实验十二_游标和存储过程

1、实验十二 游标与存储过程请完成以下实验内容：(1) 创建游标，逐行显示Customer表的记录，并用WHILE结构来测试Fetch_Status的返回值。输出格式如下：客户编号+-+客户名称+-+客户住址+-+客户电话+-+邮政编码(2) 利用游标修改OrderMaster表中orderSum的值。(3) 创建游标，要求：输出所有女业务员的编号、姓名、性别、所属部门、职务、薪水。(4) 创建存储过程，要求：按表定义中的CHECK约束自动产生员工编号。(5) 创建存储过程，要求：查找姓“李”的职员的员工编号、订单编号、订单金额。(6) 创建存储过程，要求：统计每个业务员的总销售业绩，显示业绩最好

2、的前3位业务员的销售信息。(7) 创建存储过程，要求将大客户(销售数量位于前5名的客户)中热销的前3种商品的销售信息按如下格式输出：=大客户中热销的前3种商品的销售信息=商品编号 商品名称 总销售数量P20050003 120GB硬盘 21.00P20050004 3.5寸软驱 18.00P20060002 网卡 16.00(8) 创建存储过程，要求：输入年度，计算每个业务员的年终奖金。年终奖金年销售总额提成率。提成率规则如下：年销售总额5000元以下部分，提成率为10，对于5000元及超过5000元部分，则提成率为15。(9) 创建存储过程，要求将OrderMaster表中每一个订单所对应的

3、明细数据信息按规定格式输出，格式如图7-1所示。=订单及其明细数据信息= - 订单编号 200801090001 - 商品编号 数量 价格 P20050001 5 403.50 P20050002 3 2100.00 P20050003 2 600.00 - 合计订单总金额 3103.50 图7-1 订单及其明细数据信息(10) 请使用游标和循环语句创建存储过程proSearchCustomer，根据客户编号查找该客户的名称、住址、总订单金额以及所有与该客户有关的商品销售信息，并按商品分组输出。输出格式如图7-2所示。=客户订单表= - 客户名称： 统一股份有限公司 客户地址： 天津市 总金额

4、： 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的返回值。输出格式如下：客户编号+-+客户名称+-+客户电话+-+客户住址+-+邮政编码*/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) 利用游标修改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) 创建游标，要求：输出所有女业务员的编号、姓名、性别、所属部门、职务、薪水*/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=编号 姓名 性别 所属部门 职务 薪水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) 创建存储过程，要求：按表定义中的CHECK约束自动产生员工编号*/create table Rnum(numberchar(

10、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()+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-这里的两个数字3 就是我们要设置的id长度print textbreakendendend/*执行过程*/exec no_tot 张三/*(5) 创建存储过程，要求：查找姓“李”的职员的员工编号、

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/*执行过程*/exec emli_tot 李%/*(6) 创建存储过程，要求：统计每个业务员的总销售业绩，显示业绩最好的前3位业务员的销售信息*/create procedure saler_totasse

13、lect top 3 salerNo 业务员编号,sum(orderSum) 总销售业绩from OrderMaster62group by salerNoorder by sum(orderSum) desc/*执行过程*/exec saler_tot/*(7) 创建存储过程，要求将大客户(销售数量位于前5名的客户)中热销的前3种商品的销售信息按如下格式输出：=大客户中热销的前种商品的销售信息=商品编号 商品名称 总销售数量P20050003 120GB硬盘 21.00P20050004 3.5寸软驱 18.00P20060002 网卡 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=商品编号 商品名称 总销售数量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/*执行过程*/exec product_tot/*(8) 创建存储过程，要求：输入年度，计算每个业务员的年终奖金。年终奖金年销售总额提成率。提成率规则如下：年销售总额元以下部分，提成率为，对于元及超过元部分，则提成率为*/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=业务员的年终奖金=print textselect text=业务员编号年终奖金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/*执行过程*/exec pride_tot 2012/*(9) 创建存储过程，要求

19、将OrderMaster62表中每一个订单所对应的明细数据信息按规定格式输出，格式如图-1所示。 =订单及其明细数据信息= - 订单编号 200801090001 - 商品编号 数量 价格 P20050001 5 403.50 P20050002 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

20、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=订单编号+ordernoprint textselect text=-print textselect text=商品编号 数量 价格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=合计订单总金额+convert(char(12),sum)print textfetch orm_cur into sumendclose orm_curdeallocate orm_cur/*执行过程*/exec orderm_tot 200801090001/*(10) 请使用游标和循环语句创建存储过程p

23、roSearchCustomer，根据客户编号查找该客户的名称、住址、总订单金额以及所有与该客户有关的商品销售信息，并按商品分组输出。输出格式如图-2所示。=客户订单表= - 客户名称： 统一股份有限公司 客户地址： 天津市 总金额： 31121.86 - 商品编号 总数量 平均价格 P20050001 5 80.70 P20050002 19 521.05 P20050003 5 282.00 P20070004 2 320.00 报表制作人陈辉 制作日期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=