实验：游标与存储过程.txt.docVIP

/*(1)利用游标查找所有女业务员的基本情况*/ declare cur_emp scroll cursor for select * from employee where sex=F order by employeeno open cur_emp fetch next from cur_emp while(@@fetch_status=0) begin fetch next from cur_emp end close cur_emp deallocate cur_emp /*(2) 创建一游标，逐行显示表Customer的记录，要求按 “客户编号”+‘------’+“客户名称”+‘------’+“客户地址”+ ‘----------------------------------’+“客户电话”+‘-------’+ “客户邮编”+‘------’格式输出，并且用WHILE结构来测试游标的函数 @@Fech_Status的返回值。*/ declare @customerNo char(9),@customerName varchar(10),@telephone varchar(10), @address char(10),@zip char(6) declare @text char(100) declare emp_cur scroll cursor for select * from Customer order by customerNo select @text==========================客户信息表======================= print @text select @text= 客户编号 客户名称 客户电话 客户地址 客户邮编 print @text select @text=---------------------------------------------------------- print @text open emp_cur fetch emp_cur into @customerNo,@customerName,@telephone,@address,@zip while(@@fetch_status=0) begin select @text=@customerNo+ +@customerName+ +@telephone+ +@address+ +@zip print @text fetch emp_cur into @customerNo,@customerName,@telephone,@address,@zip end close emp_cur deallocate emp_cur /*(6) 利用存储过程输出所有客户姓名、客户订单金额及其相应业务员的姓名。*/ declare @customerName char(10),@orderSum numeric(9,2),@employeeName varchar(10) declare emp_cur scroll cursor for select customerName,orderSum,employeeName from Employee a,Customer b,OrderMaster c where employeeNo=salerNo and b.customerNo=c.customerNo open emp_cur fetch emp_cur into @customerName,@orderSum,@employeeName while(@@fetch_status=0) begin fetch next from emp_cur end close emp_cur deallocate emp_cur /*(7) 利用存储过程查找某员工的员工编号、订单编号、销售金额。*/ create procedure emp_sal @em_Name varchar(10) as begin declare @em_No_1 varchar(8),@orderNo char(12),@p_tot int declare @sum int,@count int set @sum=0 set @count=0 declare emp_tot cursor for select employeeNo,orderNo,orderSum from OrderMaster a,Employee b where b.employeeNam