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

实验九游标与存储过程

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,@name,@sex,@dp,@hship,@money

end

close cur_employee

deallocate cur_employee

(4) 创建存储过程，要求：按表定义中的CHECK约束自动产生员工编号。

create procedure pro_employeeno @name varchar,@sex varchar,@birthday varchar,@address varchar,@telephone varchar,@hiredate

varchar,@department varchar,@headship varchar,@salary varchar

as

declare @y varchar,@countt int,@no varchar

declare cur_employeeno scroll cursor for