T-SQL程序练习02

create function GetMax(
 @num1 int,
 @num2 int,
 @num3 int,
 @num4 int
)
returns int
as
begin
  declare @str varchar(20);
  declare @temp table(n varchar(20));
  set @str='';
  insert into @temp
  select @num1 union all
  select @num2 union all
  select @num3 union all
  select @num4
  select @str = @str+n from @temp order by n desc;
  return cast(@str as int)
end

create procedure AreYouFat(
  @weight float,
  @height float,
  @mess varchar(100) output,
  @bmi float output
)
as
begin
  set @mess=''
  set @bmi = @weight/(@height*@height)
  if(@bmi<18.5)
  set @mess='你的体型过轻，请加强营养'
  else if(@bmi>=18.5 and @bmi<=23.9)
  set @mess='你的体型正常，请注意保持'
  else if(@bmi>=24 and @bmi<=27.9)
  set @mess='你的体型偏胖，请注意饮食'
  else
  set @mess='你的体型超重，请注意节食'
end

/* 测试 */
go
declare @mess varchar(50)
declare @bmi float
exec dbo.AreYouFat 60.0,1.78, @mess output,@bmi output
print '身高体重指数是: '+cast(@bmi as varchar(20))
print '我给你的建议是: '+@mess
go

create function ranksign(
  @sign varchar(10)
)
returns @rang table(标志 varchar(10))
as
begin
if(@sign like'_将')
insert into @rang values('一花')
else if(@sign like'_尉')
insert into @rang values('一杠')
else if(@sign like'_校')
insert into @rang values('二杠')
if(@sign like'上_')
insert into @rang values('三星')
else if(@sign like'中_')
insert into @rang values('二星')
else if(@sign like'少_')
insert into @rang values('一星')
else
delete @rang
if(@sign='大校')
begin
insert into @rang values('二杠')
insert into @rang values('四星')
end
return
end

/* 测试 */
select * from dbo.ranksign('中校')

/* 建表 */
create table MissWife(
  linetext varchar(50)
)

/* 插入数据 */
insert into MissWife values('枯眼望遥山隔水')
insert into MissWife values('往来曾见几心知')
insert into MissWife values('壶空怕酌一杯酒')
insert into MissWife values('笔下难成和韵诗')
insert into MissWife values('途路阻人离别久')
insert into MissWife values('讯音无雁寄回迟')
insert into MissWife values('孤灯夜守长寥寂')
insert into MissWife values('夫忆妻兮父忆儿')

/* 查询表是否成功插入数据 */
select * from MissWife

/* 存储过程 */
create procedure ReversePoem
as
declare @linetext1 varchar(50)
declare @str varchar(200)
declare @start1 int
declare line_cursor cursor for
select linetext from MissWife
begin
  open line_cursor
  set @str=''
  print '思妻诗'
  fetch next from line_cursor into @linetext1
  while @@FETCH_STATUS=0
    begin
	print @linetext1
	set @str=@str+@linetext1
	fetch next from line_cursor into @linetext1
	end
  print ''
  set @start1 = 1
  set @str = reverse(@str)
  print '思夫诗'
  while(@start1<len(@str))
    begin
	print substring(@str,@start1,7)
	set @start1 = @start1+7;
	end
	close line_cursor
  deallocate line_cursor
end

/* 测试 */
exec ReversePoem