对于数据库的存储过程之前的专题有讲过

这里具体讲述存储过程的编写方法：

例题：有heat表和eatables两张表，分别为：

eatables

heat：protein（蛋白质），fat（脂肪），sugar（含糖量），kj（热量）,以100克为单位

1.写一个存储过程Diet，以食物重量(克)为参数，输出该重量的不同类产品中，蛋白质成分最高的食物及其蛋白质含量。输出类似如下:

exec Diet 200

由于检索内容较为复杂：

所以先写检索内容：

select name,a.type,maxfrom(select type,max(protein)*(@num/100) max from eatables,heatwhere heat.id = eatables.idgroup by type)p,eatables as a,heat as bwhere b.protein*(@num/100)=p.max and a.id=b.id

接着加上存储过程：

create procedure Diet(@num int
)
as
begindeclare @name varchar(10),@type varchar(10),@max decimal(8,2)declare diet_cursor cursor forselect name,a.type,maxfrom(select type,max(protein)*(@num/100) max from eatables,heatwhere heat.id = eatables.idgroup by type)p,eatables as a,heat as bwhere b.protein*(@num/100)=p.max and a.id=b.id
open diet_cursor
fetch next from diet_cursor into @name,@type,@max
while @@FETCH_STATUS=0
begin
print '在' +@type+'食品中'
print '每'+convert(varchar,@num)+'克'+@name+'含有的蛋白质最高，达到'+convert(varchar,@max)+'克'
fetch next from diet_cursor into @name,@type,@max
end
close diet_cursor
deallocate diet_cursor
end

输入

exec Diet 200

得

2.写一个存储过程HeatReport，找出平均糖含量最高的食物及其糖含量。输出类似如下:
exec HeatReport；

先写检索过程：

select top 1 type,avg_sugar from(select type,sum(sugar)/count(1) as avg_sugar from eatables,heatwhere eatables.id=heat.idgroup by type)porder by p.avg_sugar desc

再写存储过程

create procedure HeatReport
as
begindeclare @type varchar(10),@avg_sugar decimal(8,2)declare diet_cursor cursor forselect top 1 type,avg_sugar from(select type,sum(sugar)/count(1) as avg_sugar from eatables,heatwhere eatables.id=heat.idgroup by type)porder by p.avg_sugar descopen diet_cursorfetch next from diet_cursor into @type,@avg_sugarwhile @@FETCH_STATUS=0beginprint'在面类，米面类，水产类食品中'print @type+'的平均糖含量最高，达到每100克食物含有'+convert(varchar,@avg_sugar)+'克'fetch next from diet_cursor into @type,@avg_sugarend
close diet_cursor
deallocate diet_cursor
end

输入
exec HeatReport

得：