T-SQL程序练习03

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

/* 插入数据 */
insert into Season values('莺啼岸柳弄春晴夜月明')
insert into Season values('香莲碧水动风凉夏日长')
insert into Season values('秋江楚雁宿沙洲浅水流')
insert into Season values('红炉透炭炙寒冬御雪风')

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

/* 存储过程 */
create procedure SeasonLandScape
as
  declare @linetext varchar(50)
  declare @tempStr varchar(50)
  declare line_cursor1 cursor for
  select * from Season
  print '春夏秋冬'
  open line_cursor1
  fetch next from line_cursor1 into @linetext
  while @@FETCH_STATUS=0
    begin
      print @linetext
	  fetch next from line_cursor1 into @linetext
    end
  declare line_cursor2 cursor for
  select * from Season
  print ''
  print '四时山水回文诗'
  open line_cursor2
  fetch next from line_cursor2 into @linetext
  while @@FETCH_STATUS=0
  begin
    set @tempStr=''
    set @tempStr=substring(@linetext,1,7)+','
	set @tempStr=@tempStr+substring(@linetext,4,7)
	print @tempStr
	set @linetext=reverse(@linetext)
	set @tempStr=substring(@linetext,1,7)+','
	set @tempStr=@tempStr+substring(@linetext,4,7)
	print @tempStr
	print ''
	fetch next from line_cursor2 into @linetext
  end
close line_cursor1
deallocate line_cursor1
close line_cursor2
deallocate line_cursor2

/* 测试 */
exec SeasonLandScape

/* 存储过程 */
create procedure MagicNumber
as
  declare @outNum bigint
  declare @inNum bigint
  declare @str varchar(50)
  declare @result bigint
  select @outNum=1,@inNum=1,@str=''
  while(@outNum<=9)
  begin
    while(@inNum<=@outNum)
	begin
	  set @str=@str+cast(@inNum as varchar)
	  set @inNum=@inNum+1
	end
	set @inNum=cast(@str as bigint)
    set @str=@str+' × 8 + '+cast(@outNum as varchar)+' = '
	set @result=@inNum*8+@outNum
	set @str=@str+cast(@result as varchar)
	print @str
	set @str=''
	set @inNum=1
	set @outNum=@outNum+1
  end

/* 测试 */
exec MagicNumber

/*（自己写的取得最大数和最小数）*/
create procedure GetMaxAndMin(
  @a int,
  @b int,
  @c int,
  @d int,
  @Max int output,
  @Min int output
)
as
begin
  declare @tempTable table(n int)
  insert into @tempTable values(@a)
  insert into @tempTable values(@b)
  insert into @tempTable values(@c)
  insert into @tempTable values(@d)
  declare @tempStr int
  declare @str varchar(10)
  declare num_cursor cursor for
  select n from @tempTable order by n desc
  select @str=''
  open num_cursor
  fetch next from num_cursor into @tempStr
  while @@FETCH_STATUS=0
  begin
    set @str=@str+cast(@tempStr as varchar)
	fetch next from num_cursor into @tempStr
  end
  set @Max=cast(@str as int)
  set @Min=cast(reverse(@str) as int)
  close num_cursor
  deallocate num_cursor
end

/*看别人写的获得最大数和最小数（直接用select就可以了，不用游标。。。）*/
create procedure GetMaxAndMin(
  @a int,
  @b int,
  @c int,
  @d int,
  @Max int output,
  @Min int output
)
as
begin
  declare @str varchar(20)
  declare @temptable table(n varchar(20))
  set @str=''
  insert into @temptable
  select @a union all
  select @b union all
  select @c union all
  select @d
  select @str=@str+n from @temptable order by n desc
  set @Max = cast(@str as int)
  set @Min = cast(reverse(@str) as int)
end

/* 主存储过程 */
create procedure MaxMinusMin(
  @a int,
  @b int,
  @c int,
  @d int
)
as
begin
  declare @max int
  declare @min int
  declare @result int
  declare @result1 int
  declare @result2 int
  declare @recordNum int
  select @recordNum=1,@result1=0,@result2=1
  while(@result1!=@result2)
  begin
    exec GetMaxAndMin @a,@b,@c,@d,@max output,@min output
	set @result=@max-@min
	if(@recordNum%2=1)
	  set @result1=@result
	else
	  set @result2=@result
	if(@result1!=@result2)
	  print cast(@max as varchar)+' - '+
	        cast(@min as varchar)+' = '+
		    cast(@result as varchar)
	set @a=@result%10
	set @result=@result/10
	set @b=@result%10
	set @result=@result/10
	set @c=@result%10
	set @result=@result/10
	set @d=@result%10
	set @result=@result/10
	set @recordNum=@recordNum+1
  end
end

/* 测试 */
exec MaxMinusMin 8,7,3,2
exec MaxMinusMin 5,8,4,4

/* 建表 */
create table Cosmetic(
  大类 varchar(20),
  小类 varchar(20),
  品名 varchar(20)
)

/* 插入数据 */
insert into Cosmetic values('护肤类','清洁皮肤','清洁霜')
insert into Cosmetic values('护肤类','保护皮肤','雪花霜')
insert into Cosmetic values('护肤类','保护皮肤','冷霜')
insert into Cosmetic values('护肤类','保护皮肤','防晒霜')
insert into Cosmetic values('护肤类','营养皮肤','人参霜')
insert into Cosmetic values('护肤类','营养皮肤','蜂王霜')
insert into Cosmetic values('护肤类','营养皮肤','防皱霜')
insert into Cosmetic values('护肤类','美化皮肤','珍珠霜')
insert into Cosmetic values('护肤类','美化皮肤','雀斑霜')
insert into Cosmetic values('护肤类','美化皮肤','粉刺霜')
insert into Cosmetic values('发用类','美发','电烫液')
insert into Cosmetic values('发用类','美发','冷烫液')
insert into Cosmetic values('发用类','美发','染发剂')
insert into Cosmetic values('发用类','护发','发油')
insert into Cosmetic values('发用类','护发','发蜡')
insert into Cosmetic values('发用类','护发','发乳')
insert into Cosmetic values('发用类','护发','护发素')
insert into Cosmetic values('发用类','洗发','珠光香波')
insert into Cosmetic values('发用类','洗发','膏状香波')
insert into Cosmetic values('美容类','身体','香粉')
insert into Cosmetic values('美容类','脸','胭脂')
insert into Cosmetic values('美容类','脸','腮红')
insert into Cosmetic values('美容类','眉毛','眉笔')
insert into Cosmetic values('美容类','嘴唇','唇膏')
insert into Cosmetic values('美容类','指甲','指甲油')
insert into Cosmetic values('美容类','眼睛','睫毛笔')
insert into Cosmetic values('美容类','眼睛','眼线')
insert into Cosmetic values('香水类','香水','花香系列')
insert into Cosmetic values('香水类','香水','草绿香系列')
insert into Cosmetic values('香水类','古龙水','古龙水')
insert into Cosmetic values('香水类','花露水','花露水')
insert into Cosmetic values('香水类','化妆水','化妆水')

/* 建立存储过程 */
create procedure CosmeticReport
as
begin
  declare @bigClass varchar(20)
  declare @smallClass varchar(20)
  declare @brandName varchar(20)
  declare big_cursor cursor for
  select 大类 from Cosmetic group by 大类 order by 大类
  print '化妆品报告'
  print '-----------'
  open big_cursor
  fetch next from big_cursor into @bigClass
  while @@FETCH_STATUS=0
  begin
    print ''
	print '  '+@bigClass+'化妆品'
	declare @str varchar(100)
	declare small_cursor cursor for
	select distinct 小类 from Cosmetic where 大类=@bigClass
	set @str=''
	open small_cursor
	fetch next from small_cursor into @smallClass
	while @@FETCH_STATUS=0
	begin
	  set @str='    '+@smallClass+'类：'
	  declare brand_cursor cursor for
	  select 品名 from Cosmetic where 小类=@smallClass
	  open brand_cursor
	  fetch next from brand_cursor into @brandName
	  while @@FETCH_STATUS=0
	  begin
		set @str=@str+@brandName+' '
		fetch next from brand_cursor into @brandName
	  end
	  close brand_cursor
	  deallocate brand_cursor
	  print @str
	  set @str=''
	  fetch next from small_cursor into @smallClass
    end
	close small_cursor
	deallocate small_cursor
	fetch next from big_cursor into @bigClass
  end
  close big_cursor
  deallocate big_cursor
end

/* 测试 */
exec CosmeticReport