目录
一、利用 𝑆𝑒𝑎𝑠𝑜𝑛 表,参照结果,写一个存储过程𝑆𝑒𝑎𝑠𝑜𝑛𝐿𝑎𝑛𝑑𝑆𝑐𝑎𝑝
四、利用𝐶𝑜𝑠𝑚𝑒𝑡𝑖𝑐 表,参照结果,写一个存储过程 𝐶𝑜𝑠𝑚𝑒𝑡𝑖𝑐𝑅𝑒𝑝𝑜𝑟
1)建立 𝑆𝑒𝑎𝑠𝑜𝑛 表并插入数据
2)利用 𝑆𝑒𝑎𝑠𝑜𝑛 表,参照如下转 换规则,写一个存储过程𝑆𝑒𝑎𝑠𝑜𝑛𝐿𝑎𝑛𝑑𝑆𝑐𝑎𝑝𝑒,实现如右边类似的格式输出
/* 建表 */
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
写一个存储过程 𝑀𝑎𝑔𝑖𝑐𝑁𝑢𝑚𝑏𝑒𝑟,实现类似如下图片的运行结果
要求1:等号左边的数按照规律循环产生
要求2:等号右边的数由左边的表达式计算产生
/* 存储过程 */
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