sqlserver------数据库的存储过程（练习）

elect name,a.type,max
  from(select type,max(protein)*(@num/100) max from eatables,heat
  where heat.id = eatables.id
  group by type)p,
  eatables as a,heat as b
  where b.protein*(@num/100)=p.max and a.id=b.id

create procedure Diet(
  @num int
)
as
begin
  declare @name varchar(10),@type varchar(10),@max decimal(8,2)
  declare diet_cursor cursor for
  select name,a.type,max
  from(select type,max(protein)*(@num/100) max from eatables,heat
  where heat.id = eatables.id
  group by type)p,
  eatables as a,heat as b
  where 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

select top 1 type,avg_sugar from
  (select type,sum(sugar)/count(1) as avg_sugar from eatables,heat
  where eatables.id=heat.id
  group by type)p
  order by p.avg_sugar desc

create procedure HeatReport
as
begin
  declare @type varchar(10),@avg_sugar decimal(8,2)
  declare diet_cursor cursor for
  select top 1 type,avg_sugar from
  (select type,sum(sugar)/count(1) as avg_sugar from eatables,heat
  where eatables.id=heat.id
  group by type)p
  order by p.avg_sugar desc
  open diet_cursor
  fetch next from diet_cursor into @type,@avg_sugar
  while @@FETCH_STATUS=0
  begin
  print'在面类，米面类，水产类食品中'
  print @type+'的平均糖含量最高，达到每100克食物含有'+convert(varchar,@avg_sugar)+'克'
  fetch next from diet_cursor into @type,@avg_sugar
  end
close diet_cursor
deallocate diet_cursor
end