数据库实验十二--游标与存储过程.docVIP

实验九 游标与存储过程 1 实验目的与要求 (1) 掌握游标的定义和使用方法。 (2) 掌握存储过程的定义、执行和调用方法。 (3) 掌握游标和存储过程的综合应用方法。 2 实验内容 请完成以下实验内容： (1) 创建游标，逐行显示Customer表的记录，并用WHILE结构来测试@@Fetch_Status的返回值。输出格式如下： 客户编号+-----+客户名称+----+客户住址+-----+客户电话+------+邮政编码 declare @cno char(9),@cname varchar(20),@tele varchar(20),@addr varchar(12),@zi char(7) declare @text varchar(180) declare cus_cur scroll cursor for select customerNo,customerName,telephone,address,zip from customer order by customerNo set @text================================customer============================================ print @text set @text=客户编号+ +客户名称+ +客户电话+ +客户住址+ +邮政编码 print @text open cus_cur fetch cus_cur into @cno,@cname,@tele,@addr,@zi while(@@fetch_status=0) begin set @text = @cno+ +@cname+ +space(2*(9-len(@cname)))+@tele+ +@addr+ +@zi print @text fetch cus_cur into @cno,@cname,@tele,@addr,@zi end close cus_cur deallocate cus_cur (2) 利用游标修改OrderMaster表中orderSum的值。 declare @no varchar(12),@sumprice varchar(10) declare @text varchar(100) declare cur_ordersum scroll cursor for select orderNo,sum(quantity*price) from orderdetail od group by orderNo open cur_ordersum fetch cur_ordersum into @no,@sumprice while(@@fetch_status=0) begin update ordermaster set ordersum = @sumprice where orderno=@no fetch cur_ordersum into @no,@sumprice end close cur_ordersum deallocate cur_ordersum select* from ordermaster (3) 创建游标，要求：输出所有女业务员的编号、姓名、性别、所属部门、职务、薪水。 declare @no varchar(12),@name varchar(4),@sex varchar(2),@dp varchar(10),@hship varchar(8),@money numeric(7,2) declare @text varchar(50) declare cur_employee scroll cursor for select employeeno,employeename,sex,department,headship,salary from employee where sex=f open cur_employee fetch cur_employee into @no,@name,@sex,@dp,@hship,@money print 员工编号 +姓名 +性别+所属部门 +职务 +薪水 while(@@fetch_status=0) begin set @text = @no+ +@name+space(6-2*len(@name))+@sex+ +@dp+ +@hship+ +convert(char(9),@money) print @text fetch cur_employee into @no