数据库实验报告.doc

实验八 游标与存储过程7.2.3 实验内容请完成以下实验内容：(1) 利用游标查找所有女业务员的基本情况。declare employeeNo2708 char(8),employeeName2708 varchar(10),sex2708 char(1),birthday2708 datetime,address2708 varchar(50),telephone2708 varchar(20),hireDate2708 datetime,department2708 varchar(30),headShip2708 varchar(10),salary2708 numeric(8,2)declare myCur cursor for select *from employee2708where sex2708=Forder by employeeNo2708open myCurfetch myCur into employeeNo2708,employeeName2708,sex2708,birthday2708, address2708,telephone2708,hireDate2708,department2708,headShip2708,salary2708while (fetch_status=0)begin select employeeNo2708 员工编号,employeeName2708 员工姓名,sex2708 员工性别,birthday2708 员工生日,address2708 员工住址,telephone2708员工电话,hireDate2708 雇佣日期,department2708 所属部门,headShip2708 职务,salary2708 薪水 fetch myCur into employeeNo2708,employeeName2708,sex2708,birthday2708,address2708,telephone2708,hireDate2708,department2708,headShip2708,salary2708end close myCurdeallocate myCur (2) 创建一游标，逐行显示表Customer.的记录，要求按客户编号+-+客户名称+-+客户地址+-+客户电话+-+客户邮编+-格式输出，并且用WHILE结构来测试游标的函数Fetch_Status的返回值。declare CustomerNo2708 char(9),CustomerName2708 varchar(40),address2708 char(10),telephone2708 varchar(20),zip2708 char(6)declare text char(150)declare myCur cursor for select CustomerNo2708,CustomerName2708,address2708,telephone2708,zip2708from Customer2708order by CustomerNo2708open myCurfetch myCur into CustomerNo2708,CustomerName2708,address2708,telephone2708,zip2708while (fetch_status=0)begin select text=客户编号：+CustomerNo2708+ 客户名称：+CustomerName2708+ 客户地址：+address2708+客户电话：+telephone2708+ 客户邮编：+zip2708 print text fetch myCur into CustomerNo2708,CustomerName2708,address2708,telephone2708,zip2708end close myCurdeallocate myCur(3) 利用游标修改OrderMaster表中Ordersum的值。declare orderNo char(12),totalSum numeric(9,2)declare sumCur cursor forselect orderNo2708,sum(quantity2708*price2708) totalSumfrom OrderDetail2708group by orderNo2708open sumCurfetch sumCur into orderNo,totalSum while(FETCH_STATUS=0)begin update OrderMaster2708 set orderSum2708=totalSum where OrderMaster2708.orderNo2708=orderNo fetch sumCur into orderNo,totalSum endclose sumCurdeallocate sumCur(4) 利用游标显示出OrderMaster表中每一个定单所对应的明细数据信息。Declare orderNo2708 char(12),CustomerNo2708 char(9),salerNo2708 char(8),orderDate2708 datetime,orderSum2708 numeric(9,2),invoiceNo2708 char(10)declare myCur cursor for select orderNo2708,CustomerNo2708,salerNo2708,orderDate2708,orderSum2708,invoiceNo2708from OrderMaster2708open myCurfetch myCur intoorderNo2708,CustomerNo2708,salerNo2708,orderDate2708,orderSum2708,invoiceNo2708while (fetch_status=0)begin select orderNo2708 订单编号,CustomerNo2708 顾客编号,salerNo2708 业务员编号,orderDate2708 定货日期,orderSum2708 定单金额,invoiceNo2708 发票号码 fetch myCur intoorderNo2708,CustomerNo2708,salerNo2708,orderDate2708,orderSum2708,invoiceNo2708end close myCurdeallocate myCur(5) 利用存储过程，给Employee表添加一条业务部门员工的信息。create proc emp_new (employeeNo2708 char(8),employeeName2708 varchar(10),sex2708 char(1), birthday2708 datetime,address2708 varchar(50),telephone2708 varchar(20), hireDate2708 datetime,department2708 varchar(30),headShip2708 varchar(10),salary2708 numeric(8,2)asinsert into Employee2708 values(employeeNo2708,employeeName2708,sex2708, birthday2708,address2708,telephone2708,hireDate2708,department2708,headShip2708,salary2708)执行存储过程，添加记录exec emp_new employeeNo2708=E2010236,employeeName2708=点点公主,sex2708=F,birthday2708=19890101,address2708=麦庐园静庐C栋, telephone2708 hireDate2708=20101125,department2708=财务科,headShip2708=出纳,salary2708=3200.00(6) 利用存储过程输出所有客户姓名、客户订购金额及其相应业务员的姓名。一个客户输出完再输出下个，利用一个游标，使用双重循环create procedure proCusInfasbegin create table #myTemp( CustomerName varchar(40), orderSum numeric(9,2), salerName varchar(10) ) insert into #myTemp select CustomerName2708,orderSum2708,employeeName2708 from Employee2708 a,Customer2708 b,OrderMaster2708 c where a.employeeNo2708=c.salerNo2708 and b.CustomerNo2708=c.CustomerNo2708 select * from #myTempend执行存储查询：exec proCusInf (7) 利用存储过程查找某员工的员工编号、订单编号、销售金额。create procedure empNa empname varchar(10)asselect employeeNo2708,orderNo2708,orderSum2708from OrderMaster2708 a,Employee2708 bwhere a.salerNo2708=b.employeeNo2708 and employeeName2708=empname 执行存储过程，例：查找张良exec empNa empname=张良(8) 利用存储过程查找姓“李”并且职称为“职员”的员工的员工编号、订单编号、销售金额。创建存储过程：create procedure emp_li e_xx varchar(10),e_head varchar(10)asselect a.employeeName2708,a.employeeNo2708,b.ssum,b.orderNo2708from employee2708 a,(select salerNo2708,orderNo2708,ssum=sum(orderSum2708)from orderMaster2708group by salerNo2708,orderNo2708) bwhere a.employeeNo2708=b.salerNo2708 and a.employeeName2708 like e_xx and a.headShip2708 like e_head执行存储过程：exec emp_li e_xx=李%,e_head=职员 (9) 请使用游标和循环语句编写一个存储过程proSearchCustomer，根据客户编号，查询该客户的名称、地址以及所有与该客户有关的销售记录，销售记录按商品分组输出。create procedure proSearchCustomer CustomerNo char(9)as declare orderNo char(12),salerNo char(8),orderDate datetime,orderSum numeric(9,2),invoiceNo char(10) select CustomerName2708 客户名称,address2708 客户住址 from Customer2708 where CustomerNo2708=CustomerNo declare cur_Customer cursor for select orderNo2708,salerNo2708,orderDate2708,orderSum2708,invoiceNo2708from OrderMaster2708where OrderMaster2708.CustomerNo2708=CustomerNo open cur_Customer fetch cur_Customer into orderNo,salerNo,orderDate,orderSum,invoiceNo while(f