目录
一、写一个存储过程 𝐹𝑖𝑏𝑜𝑛𝑎𝑐𝑐
1. 具体要求
写一个存储过程 𝐹𝑖𝑏𝑜𝑛𝑎𝑐𝑐𝑖 (命名格式“姓名拼音_三位学号_fibonacci”,如LBJ_023_fibonacci,实现如下类似输出。要求如下
1)两个等号之间的数按照规律循环生成
2)最后的和由两个等号之间的表达式计算生成
2. T-SQL程序代码
/* 建立存储过程 */
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
3. 结果显示
二、建立存储过程 𝑆𝑎𝑛𝐺𝑢𝑜𝑅𝑒𝑝𝑜𝑟
1. 具体要求
1)建立 𝐺𝑒𝑛𝑒𝑟𝑎𝑙 表并插入数据
2)建立存储过程 𝑆𝑎𝑛𝐺𝑢𝑜𝑅𝑒𝑝𝑜𝑟𝑡) (命名格式“姓名拼音_三位学号_sanguoreport”,如LBJ_023_sanguoreport),实现如左格式输出,说明如下
⚫ 武将:武力80以上(含80)
⚫ 谋士:智力80以上(含80)
⚫ 美女:美貌60以上(含60)
2. T-SQL程序代码
/* 建表 */
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