首页 > 代码库 > 存储过程、while语句
存储过程、while语句
1、存储过程
（1）新建一个存储过程
create proc ChaXun
as
begin
select *from Score
end
go
（2）执行
exec chaxun
（3）删除
drop proc chaxun
（4）修改
alter proc ChaXun
as
begin
select *from Score
end
go
（5）向存储过程中输入参数
alter proc jiayibai
@shuru int,
@canshu int
as
begin
print @shuru+@canshu
end
go
exec jiayibai 10,120
例：
（1）判断是否闰年
create proc isRunNian
@year int
as
begin
if @year%4=0 and @year%100!=0
begin
print ‘是闰年‘
end
else if @year%100=0 and @year%400=0
begin
print ‘是闰年‘
end
else
begin
print ‘不是闰年‘
end
end
go
declare @jieguo int
exec @jieguo=isRunNian 2100
print @jieguo
（2）判断是不是一个一元二次方程
create proc yiyuanerci
@a int,
@b int,
@c int
as
begin
if @a=0
begin
--print ‘不是一元二次方程‘
return 1
end
else
begin
declare @sqrt decimal(18,2)
set @sqrt = @b*@b-4*@a*@c
if @sqrt>0
begin
--print ‘两个不同的根‘
return 2
end
else if @sqrt=0
begin
--print ‘两个相同的根‘
return 3
end
else
begin
--print ‘无解‘
return 4
end
end
end
go
declare @fanhui int
exec @fanhui = yiyuanerci 0,4,3
if @fanhui=1
print ‘请仔细阅读一元二次方程的构造规则‘
2、out输出参数
alter proc outzhi
@shuru int,
@jiashi int output,
@jiaershi int output
as
begin
set @jiashi=@shuru+10
set @jiaershi=@shuru+20
return 1
end
go
declare @shuchu1 int,@shuchu2 int,@return int
exec @return=outzhi 15,@shuchu1 output,@shuchu2 output
print @shuchu1
print @shuchu2
print @return
例：
（1）闰年
create proc runnian
@y int
as
begin
if (@y%100=0 and @y%400=0) or(@y%100<>0 and @y%4=0)
begin
return 1
end
else
begin
return 2
end
end
go
declare @a int
exec @a=runnian 1990
if @a=1
print ‘是闰年‘
if @a=2
print ‘不是闰年‘
一元二次方程
alter proc yiyuanercifangcheng
@a decimal(18,2),@b decimal(18,2),@c decimal(18,2),
@y1 decimal(18,2) output,
@y2 decimal(18,2) output
as
begin
declare @n decimal(18,2)
if @a=0
begin
return 1
end
else
begin
if @b*@b-4*@a*@c<0
begin
return 2
end
if @b*@b-4*@a*@c=0
begin
set @n=sqrt(@b*@b-4*@a*@c)
set @y1=(-@b+@n)/(2*@a)
return 3
end
if @b*@b-4*@a*@c>0
begin
set @n=sqrt(@b*@b-4*@a*@c)
set @y1=(-@b+@n)/(2*@a)
set @y2=(-@b-@n)/(2*@a)
return 4
end
end
end
go
declare @j1 decimal(18,2),@j2 decimal(18,2),@return int
exec @return=yiyuanercifangcheng 1,22,1,@j1 output,@j2 output
if @return=1
begin
print ‘因为a=0,所以该方程不是一元二次方程‘
end
if @return=2
begin
print ‘因为Δ<0,所以该方程无解‘
end
if @return=3
begin
print ‘因为Δ=0,所以该方程有两个相等的实数根‘
print ‘x1=x2=‘+cast(@j1 as varchar(20))
end
if @return=4
begin
print ‘因为Δ>0,所以该方程有两个不相等的实数根‘
print ‘x1=‘+cast(@j1 as varchar(20))
print ‘x2=‘+cast(@j2 as varchar(20))
End
3、while循环语句
累加求和
alter proc he
@a int,@s int output
as
begin
declare @i int
set @s=0
set @i=1
while @i<=@a
begin
set @s=@s+@i
set @i=@i+1
end
end
go
declare @b int
exec he 10,@b output
print @b
存储过程、while语句