首页 > 代码库 > 14、SQL基础整理（存储过程）
14、SQL基础整理（存储过程）
存储过程procedure（proc）
数据库—可编程性—存储过程
新建存储过程：
create proc firstproc
as
select *from fenshu
go
执行存储过程：
存储过程—右键—执行存储过程
declare @fanhuizhi int
exec @fanhuizhi = firstproc
select ‘Return Value‘ = @fanhuizhi
return value执行成功/失败的返回值（0为成功）
execute/exec firstproc
修改存储过程
alter proc firstproc
as
select yuwen,shuxue,yingyu,name from student,fenshu where student.code = fenshu.code
go
execute firstproc
查询多个表
create proc secondproc
as
begin
select*from student
select*from teacher
select*from fenshu
end
带子查询的查询过程
create proc threeproc
as
begin
declare @count int
select @count=count(*) from (select *from student where code = any(select code from fenshu where code in
(select code from student where MT = ( select code from teacher where name = ‘数一‘))and shuxue>80)
)as new
if @count> 3
print‘达标‘
else
print‘不达标‘
end
exec threeproc
带参数的查询过程
create proc fourthproc
@hello varchar(20),
@ercan varchar(20)—参数部分
as
begin
print @hello+@ercan
end
go
exec fourthproc ‘helloworld‘,‘,你好世界！‘
复杂参数
create proc fifthproc
@name varchar(20)
as
begin
declare @countjs int,@lesson varchar(20)
select @countjs = COUNT(*)from teacher where name = @name
if @countjs = 0
begin
print ‘没找到这个老师‘
end
else
begin
select @lesson = course from teacher where name = @name
declare @count int
if @lesson = ‘语文‘
begin
select @count= count(*)from student where code = any(select code from fenshu where code in
(select code from student where CT = ( select code from teacher where name = @name))and yuwen>80)
end
else
if @lesson = ‘数学‘
begin
select @count= count(*)from student where code = any(select code from fenshu where code in
(select code from student where MT = ( select code from teacher where name = @name))and shuxue>80)
end
else
if @lesson = ‘英语‘
begin
select @count= count(*)from student where code = any(select code from fenshu where code in
(select code from student where ET = ( select code from teacher where name = @name))and yingyu>80)
end
if @count>=3
print ‘达标‘
else
print ‘不达标‘
end
end
go
exec fifthproc (@name=可忽略)‘数一‘
exec fifthproc ‘数‘
删除存储过程
drop proc 存储过程名
练习
------------输入学生的学号，看是否结业（三门课都超过分发优秀证书，有两门课或以上不及格不结业，有一门课不及格结业）------------
alter proc jieye
@xuehao int
as
begin
declare @shuxue int
declare @yuwen int
declare @yingyu int
declare @zongfen int
select @shuxue = count(*) from fenshu where code=@xuehao and shuxue>80
select @yuwen = count(*) from fenshu where code=@xuehao and yuwen>80
select @yingyu = count(*) from fenshu where code=@xuehao and yingyu>80
set @zongfen = @shuxue+@yuwen+@yingyu
if @zongfen = 3
print ‘优秀‘
if @zongfen = 2
print ‘结业‘
if @zongfen < 2
print ‘不结业‘
end
go
返回值
-----------输入一个数,使其+10返回------定义变量接收执行存储过程返回的值
create proc jisuan
@sum int（可以设默认值@sum int=10）
as
begin
set @sum = @sum +10
return @sum
end
declare @shu int
exec @shu = jisuan 2（把2改为default即可按照默认值的输出结果打印）
print @shu
---------返回总分的个数并打印出来-----------
create proc jieye2
@xuehao int
as
begin
declare @shuxue int
declare @yuwen int
declare @yingyu int
declare @zongfen int
select @shuxue = count(*) from fenshu where code=@xuehao and shuxue>80
select @yuwen = count(*) from fenshu where code=@xuehao and yuwen>80
select @yingyu = count(*) from fenshu where code=@xuehao and yingyu>80
set @zongfen = @shuxue+@yuwen+@yingyu
return @zongfen
end
declare @count int
exec @count = jieye2 1
print @count
return后面的存储过程将不再运行（放在if等句子里的例外）
练习
--输入一个数n，求n+n-1+……+1的和---
alter proc qiuhe
@n int
as
begin
declare @sum int =0
while @n>=1
begin
set @sum = @n+@sum
set @n = @n-1
end
return @sum
end
go
declare @n1 int
exec @n1 = qiuhe 5
print @n1
--------带返回值,返回参数,输入参数的存储过程--------
---输入学号，返回三门课的成绩
create proc sixproc
@yuwen decimal(18,2) output,
@shuxue decimal(18,2) output,
@yingyu decimal(18,2) output,
@code int
as
begin
declare @count int
select @yuwen = yuwen,@shuxue = shuxue,@yingyu = yingyu from fenshu where code = @code
end
go
---定义变量接受存储过程带出来的输出参数的值
declare @yuwen decimal(18,2),@yingyu decimal(18,2),@shuxue decimal(18,2)
exec sixproc @yuwen output,@shuxue output, @yingyu output,1
print @yuwen+@yingyu+@shuxue
----------------------------------------------
alter proc sixproc
@yuwen decimal(18,2) output,
@shuxue decimal(18,2) output,
@yingyu decimal(18,2) output,
@code int
as
begin
declare @count int
select @count = COUNT(*)from student where code = @code
select @yuwen = yuwen,@shuxue = shuxue,@yingyu = yingyu from fenshu where code = @code
return @count
end
go
declare @yuwen decimal(18,2),@yingyu decimal(18,2),@shuxue decimal(18,2),@count int
exec @count = sixproc @yuwen output,@shuxue output, @yingyu output,15
print @yuwen+@yingyu+@shuxue
print @count
14、SQL基础整理（存储过程）