T-SQL程序练习04

/* 建立存储过程 */
create procedure LYL_116_fibonacci
as
begin
  declare @begin int
  declare @end int
  declare @num1 int
  declare @num2 int
  declare @num3 int
  declare @sum int
  declare @str varchar(255)
  select @begin=1,@end=9,@str=''
  print 'Fibonacci数列'
  print ''
  while(@begin<=@end)
  begin
    select @num1=0,@num2=1,@sum=0,@str=''
    set @str='f('+cast(@begin as varchar)+')='
	  +cast(@num1 as varchar)+'+'
	  +cast(@num2 as varchar)
	declare @temp int
	set @temp=@begin
	while(@temp>=1)
	begin
	  set @num3=@num2+@num1
	  set @sum=@sum+@num3-@num1
	  if(@temp>1)
	  set @str=@str+'+'+cast(@num3 as varchar)
	  set @num1=@num2
	  set @num2=@num3
	  set @temp=@temp-1
    end
	set @str=@str+'='+cast(@sum as varchar)
	print @str
	set @begin=@begin+1
  end
end

/* 测试 */
exec LYL_116_fibonacci

/* 建表 */
create table General(
  姓名 varchar(10),
  性别 varchar(2),
  国籍 varchar(5),
  武力 int,
  智力 int,
  美貌 int
)

/* 插入数据 */
insert into General values('关羽','男','蜀',99,85,0)
insert into General values('张飞','男','蜀',99,51,0)
insert into General values('赵云','男','蜀',98,88,0)
insert into General values('诸葛亮','男','蜀',30,100,0)
insert into General values('蔡文姬','女','汉',0,80,80)
insert into General values('貂蝉','女','汉',0,79,100)
insert into General values('周瑜','男','吴',80,93,0)
insert into General values('张辽','男','魏',95,90,0)
insert into General values('孙尚香','女','吴',65,70,90)
insert into General values('程普','男','吴',88,85,0)
insert into General values('黄盖','男','吴',89,80,0)
insert into General values('夏侯渊','男','魏',93,73,0)
insert into General values('黄月英','女','蜀',0,90,50)
insert into General values('小乔','女','吴',0,60,95)
insert into General values('曹洪','男','魏',94,78,0)

/* 建立存储过程 */
create procedure LYL_116_SanGuoReport
as
begin
  declare @国籍 varchar(5)
  declare @str1 varchar(50)
  declare @str2 varchar(50)
  declare @str3 varchar(50)
  declare con_cursor cursor for
    select 国籍 from General group by 国籍
  print '三国人物报告'
  print '-----------'
  print ''
  open con_cursor
  fetch next from con_cursor into @国籍
  while @@FETCH_STATUS=0
  begin
    select @str1='',@str2='',@str3=''
    print @国籍+'国人物表'
	select @str1=@str1+姓名+' ' from General 
	where 国籍=@国籍 and 武力>=80 order by 姓名
	select @str2=@str2+姓名+' ' from General 
	where 国籍=@国籍 and 智力>=80 order by 姓名
	select @str3=@str3+姓名+' ' from General 
	where 国籍=@国籍 and 美貌>=60 order by 姓名
	print '武将有：'+@str1
	print '谋士有：'+@str2
	print '美女有：'+@str3
	print ''
	fetch next from con_cursor into @国籍
  end
  close con_cursor
  deallocate con_cursor
end

/* 测试 */
exec LYL_116_SanGuoReport