实验游标和存储过程

实验九游标与存储过程

1 实验目的与要求

(1) 掌握游标的定义和使用方法。

(2) 掌握存储过程的定义、执行和调用方法。

(3) 掌握游标和存储过程的综合应用方法。

2 实验内容

请完成以下实验内容：

(1) 创建游标，逐行显示Customer表的记录，并用WHILE结构来测试@@Fetch_Status

的返回值。输出格式如下：

'客户编号'+'-----'+'客户名称'+'----'+'客户住址'+'-----'+'客户电话

'+'------'+'邮政编码'

(2) 利用游标修改OrderMaster表中orderSum的值。

(3) 创建游标，要求：输出所有女业务员的编号、姓名、性别、所属部门、职务、薪水。

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

(5) 创建存储过程，要求：查找姓“李”的职员的员工编号、订单编号、订单金额。

(6) 创建存储过程，要求：统计每个业务员的总销售业绩，显示业绩最好的前3位业务

员的销售信息。

(7)创建存储过程，要求将大客户(销售数量位于前5名的客户)中热销的前3种商品的

销售信息按如下格式输出：

=======大客户中热销的前3种商品的销售信息================

商品编号商品名称总销售数量

P2******* 120GB硬盘 21.00

P2******* 3.5寸软驱 18.00

P2******* 网卡 16.00

(8) 创建存储过程，要求：输入年度，计算每个业务员的年终奖金。年终奖金＝年销售

总额×提成率。提成率规则如下：年销售总额5000元以下部分，提成率为10％，对于5000

元及超过5000元部分，则提成率为15％。

(9) 创建存储过程，要求将OrderMaster表中每一个订单所对应的明细数据信息按规定

格式输出，格式如图7-1所示。

===================订单及其明细数据信息==================== --------------------------------------------------- 订单编号 200801090001

--------------------------------------------------- 商品编号数量价格

P2******* 5 403.50 P2******* 3 2100.00 P2******* 2 600.00 --------------------------------------------------- 合计订单总金额 3103.50

图7-1 订单及其明细数据信息

(10) 请使用游标和循环语句创建存储过程proSearchCustomer，根据客户编号查找该客户的

名称、住址、总订单金额以及所有与该客户有关的商品销售信息，并按商品分组输出。输出

格式如图7-2所示。

===================客户订单表==================== --------------------------------------------------- 客户名称：统一股份有限公司

客户地址：天津市

总金额： 31121.86

--------------------------------------------------- 商品编号总数量平均价格

P2******* 5 80.70

P2******* 19 521.05

P2******* 5 282.00

P2******* 2 320.00

报表制作人陈辉制作日期 06 8 2012

图7-2 客户订单表

实验脚本：

/*(1) 创建游标，逐行显示Customer表的记录，并用WHILE结构来测试@@Fetch_Status的返

回值。

输出格式如下：

'客户编号'+'-----'+'客户名称'+'----'+'客户电话'+'-----'+'客户住址'+'------'+'邮

政编码'*/

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 for

select*

from Customer62

select@text='================================Customer62表的记录

===================='

print @text

select@text='客户编号'+'------'+'客户名称'+'-----------'+'客户电话

'+'-------'+'客户住址'+'------'+'邮政编码'

print @text

select

@text='======================================================================'

print @text

open cus_cur

fetch cus_cur into @C_no,@C_name,@C_phone,@C_add,@C_zip

while(@@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

end

close cus_cur

deallocate cus_cur

/*(2) 利用游标修改OrderMaster表中orderSum的值*/

declare @orderNo varchar(20),@total numeric(9,2)

declare om_cur cursor for

select orderNo,sum(quantity*price)

from OrderDetail62

group by orderNo

open om_cur

fetch om_cur into @orderNo,@total

while(@@fetch_status=0)

begin

update OrderMaster62

set orderSum=@total

where orderNo=@orderNo

fetch om_cur into @orderNo,@total

end

close om_cur

deallocate 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'

select @text='====================================================='

print @text

select @text='编号姓名性别所属部门职务薪水'

print @text

select @text='====================================================='

print @text

open em_cur

fetch em_cur into @emNo,@emNa,@emse,@emde,@emhe,@emsa

while(@@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,@emsa

end

close em_cur

deallocate em_cur

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

create table Rnum(

number char(8)null,

ename char(10)null

)--先创建一张新表用来存储已经产生的员工编号

create procedure no_tot(@name nvarchar(50))

as

begin

declare @i int,@text char(100)

set @i=1

while(@i<1000)

begin

if exists(select number

from Rnum

where

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'+convert(varchar(3),@i),3 ))+' '+@name

--这里的两个数字'3' 就是我们要设置的id长度

print @text

break

end

end

end

/*执行过程*/

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.salerNo and a.employeeName like'@emNo'

/*执行过程*/

exec emli_tot '李%'

/*(6) 创建存储过程，要求：统计每个业务员的总销售业绩，显示业绩最好的前3位业务员的销售信息*/

create procedure saler_tot

as

select top 3 salerNo 业务员编号,sum(orderSum)总销售业绩

from OrderMaster62

group by salerNo

order by sum(orderSum)desc

/*执行过程*/

exec saler_tot

/* (7) 创建存储过程，要求将大客户(销售数量位于前5名的客户)中热销的前3种商品的销售信息按如下格式输出：

=======大客户中热销的前种商品的销售信息================

商品编号商品名称总销售数量

P2******* 120GB硬盘 21.00

P2******* 3.5寸软驱 18.00

P2******* 网卡 16.00*/

create procedure product_tot

as

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,sum(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.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,@total

end

close sale_cur

deallocate sale_cur

/*执行过程*/

exec product_tot

/*(8) 创建存储过程，要求：输入年度，计算每个业务员的年终奖金。年终奖金＝年销售总额×提成率。提成率规则如下：年销售总额元以下部分，提成率为％，对于元及超过元部分，则提成率为％*/

create procedure pride_tot @date int

as

declare @saleNo char(15),@total numeric(9,2)

declare @text char(100),@money numeric(8,2)

declare pride_cur scroll 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(@total<5000)

select @money=@total*0.1

else

select @money=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

---------------------------------------------------

商品编号数量价格

P2******* 5 403.50

P2******* 3 2100.00

P2******* 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)

from 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

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 orderNo=@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，根据客户编号查找该客户

的名称、住址、总订单金额以及所有与该客户有关的商品销售信息，并按商品分组输出。输

出格式如图-2所示。

===================客户订单表====================

---------------------------------------------------

客户名称：统一股份有限公司

客户地址：天津市

总金额： 31121.86

---------------------------------------------------

商品编号总数量平均价格

P2******* 5 80.70

P2******* 19 521.05

P2******* 5 282.00

P2******* 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 @text='-------------------------------------------------'

print @text

open sear_cur

fetch sear_cur into @cusname,@address,@total

while(@@fetch_status=0)

begin

select @text='客户名称:'+' '+@cusname

print @text

select @text='客户地址:'+' '+@address

print @text

select @text='总金额: '+' '+convert(char(12),@total)

print @text

fetch sear_cur into @cusname,@address,@total

end

select @text='-------------------------------------------------'

close sear_cur

deallocate sear_cur

declare @productno char(10),@num int,@avg numeric(9,2)

declare searpro_cur scroll cursor for

select productNo,sum(quantity),avg(price)

from OrderMaster62 a,OrderDetail62 b

where a.orderNo=b.orderNo and a.customerNo='C20050001'

group by productNo

select @text='商品编号总数量平均价格'

print @text

open searpro_cur

fetch searpro_cur into @productno,@num,@avg

while(@@fetch_status=0)

begin

select @text=@productno+' '+convert(char(8),@num)+' '

+convert(char(12),@avg)

print @text

fetch searpro_cur into @productno,@num,@avg end

select @text='报表制作人杨学森制作日期 2012-12-1' print @text

close searpro_cur

deallocate searpro_cur

/*执行过程*/

exec proSearchCustomer C20050001