实验游标和存储过程.docx

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) 创建存储过程，要求：输入年度，计算每个业务员的年终奖金。年终奖金年销售总额提成率。提成率规则如下：年

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 图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

6、 cus_curfetch cus_cur into C_no,C_name,C_phone,C_add,C_zipwhile (fetch_status=0)begin select text=C_no+ +C_name+ +C_phone+ +C_add+ +C_zip print text fetch 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),t

7、otal numeric(9,2)declare om_cur cursor for select orderNo,sum(quantity*price) from OrderDetail62 group by orderNoopen om_curfetch om_cur into orderNo,total while (fetch_status=0)begin update OrderMaster62 set orderSum=total where orderNo=orderNo fetch om_cur into orderNo,total endclose om_curdealloc

8、ate om_cur/*(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 for select employeeNo,employeeName,sex,department,headShip,salary from Employee62 where sex=M

9、select text= print text select text=编号 姓名 性别 所属部门 职务 薪水 print text select text= print textopen em_curfetch em_cur into emNo,emNa,emse,emde,emhe,emsawhile (fetch_status=0)begin select text=emNo+ +emNa+ +emse+ +emde+ +emhe + +convert(char(10),emsa) print text fetch em_cur into emNo,emNa,emse,emde,emhe

10、,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) asbegin declare i int,text char(100) set i=1 while(i1000) begin if exists(select number from Rnum wh

11、ere number=(E+convert(char(4),year(getdate()+right(00+convert(varchar(3),i),3) begin set i=i+1 continue end else begin insert Rnum values(E+convert(char(4),year(getdate()+right(00+convert(varchar(3),i),3),name) select text=员工编号+ +员工姓名 print text select text=(E+convert(char(4),year(getdate()+right(00

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

13、alerNo and a.employeeName like emNo/*执行过程*/exec emli_tot 李%/*(6) 创建存储过程，要求：统计每个业务员的总销售业绩，显示业绩最好的前3位业务员的销售信息*/create procedure saler_totas select top 3 salerNo 业务员编号,sum(orderSum) 总销售业绩 from OrderMaster62 group by salerNo order by sum(orderSum) desc/*执行过程*/exec saler_tot/*(7) 创建存储过程，要求将大客户(销售数量位于前5名的

14、客户)中热销的前3种商品的销售信息按如下格式输出：=大客户中热销的前种商品的销售信息=商品编号 商品名称 总销售数量P20050003 120GB硬盘 21.00P20050004 3.5寸软驱 18.00P20060002 网卡 16.00*/create procedure product_totas declare proNo char(10),proNa char(20),total int declare text char(100) declare sale_cur scroll cursor for select top 3 a.productNo,a.productName,s

15、um(c.quantity) from Product62 a,OrderMaster62 b,OrderDetail62 c where a.productNo=c.productNo and b.orderNo=c.orderNo and b.customerNo in(select top 5 m.customerNo from OrderMaster62 m,OrderDetail62 n where m.orderNo=n.orderNo group by m.customerNo order by sum(quantity) desc) group by a.productNo,a

16、.productName order by sum(c.quantity) desc select text=大客户中热销的前种商品的销售信息= print text select text=商品编号 商品名称 总销售数量 print text open sale_cur fetch sale_cur into proNo,proNa,total while(fetch_status=0) begin select text=proNo+ +proNa+ +convert(char(10),total) print text fetch sale_cur into proNo,proNa,to

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

18、l cursor for select salerNo,sum(orderSum) from OrderMaster62 where year(orderDate)=date group by salerNo select text=业务员的年终奖金= print text select text=业务员编号 年终奖金 print text open pride_cur fetch pride_cur into saleNo,total while(fetch_status=0) begin if(total5000) select money=total*0.1 else select mo

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

20、01 5 403.50 P20050002 3 2100.00 P20050003 2 600.00 - 合计订单总金额 3103.50 图-1 订单及其明细数据信息*/create procedure orderm_tot orderno char(15)as declare prono char(15),quantity int,price numeric(9,2) declare text char(100) declare orderm_cur scroll cursor for select productNo,sum(quantity),sum(quantity*price) fr

21、om OrderDetail62 where orderNo=orderno group by productNo select text=订单及其明细数据信息= print text select text=- print text select text=订单编号 +orderno print text select text=- print text select text=商品编号 数量 价格 print text open orderm_cur fetch orderm_cur into prono,quantity,price while(fetch_status=0) begin

22、 select text=prono+ +convert(char(5),quantity)+ +convert(char(10),price) print text fetch orderm_cur into prono,quantity,price end select text=- print text close orderm_cur deallocate orderm_cur declare sum numeric(9,2) declare orm_cur scroll cursor for select orderSum from OrderMaster62 where order

23、No=orderno open orm_cur fetch orm_cur into sum while(fetch_status=0) begin select text=合计订单总金额+ +convert(char(12),sum) print text fetch orm_cur into sum end close orm_cur deallocate orm_cur/*执行过程*/exec orderm_tot 200801090001/*(10) 请使用游标和循环语句创建存储过程proSearchCustomer，根据客户编号查找该客户的名称、住址、总订单金额以及所有与该客户有关的

24、商品销售信息，并按商品分组输出。输出格式如图-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)as declare cusname char(40),address char(20),total numeric(9,2) declare text char(100) declare sear_cur scroll cursor for select a.customerName,a.address,sum(b.orderSum) from Customer62 a,OrderMaster62 b where a.customerNo=b.customerNo and a.customerNo=cusno group by a.customerName,a.address select text=客户订单表= print text select tex