---------------存储过程
--创建存储过程
create proc firstproc
as
select *From score --中间放的代码都可以执行出来
go
--执行存储过程
exec firstproc
--定义变量接收存储过程默认的返回值，可判定执行成功或失败
declare @fanhuizhi int
exec @fanhuizhi = firstproc
select @fanhuizhi
----------修改存储过程
alter proc firstproc
as
select score.code,chiese,math,english,name from score,student where score.stucode=student.code
go
exec firstproc
-----------查询多个表
create proc secondproc
as
begin
select *from score
select *from student
select *from teacher
end
go
exec secondproc
----------加语句的存储过程
create proc threeproc
as
begin
declare @count int
select @count=count(*) from score where stucode in (
select code from student where mateacher=(select code from teacher where name='张三丰')
) and math>=80
if @count>3
print '达标'
else
print '不达标'
end
go
exec threeproc
----------------带参数的存储过程
alter proc fourproc
@hello varchar(50),
@ercan varchar(50)
as
begin
print @hello+@ercan
end
go
exec fourproc 'hello world!','这是第二个参数'
--------带语句带参数的存储过程
alter proc fourproc
@name varchar(20)
as
begin
-- declare @jscode int,@kecheng varchar(20)
declare @count int,@kecheng varchar(20)
select COUNT(*) from teacher where name=@name
--select @jscode=code,@kecheng=course from teacher where name=@name
declare @count int
if @kecheng='语文'
begin
select @count=count(*) from score where stucode in (
select code from student where chteacher=(select code from teacher where name=@name)
) and chiese>=80
end
if @kecheng='数学'
begin
select @count=count(*) from score where stucode in (
select code from student where mateacher=(select code from teacher where name=@name)
) and math>=80
end
if @kecheng='英语'
begin
select @count=count(*) from score where stucode in (
select code from student where enteacher=(select code from teacher where name=@name)
) and english>=80
end
if @count>=3
begin
print '达标'
end
else
begin
print '不达标'
end
end
go
exec fourproc '莫言'
---输入学生的学号，判断学生结业与否 三门课优秀证书结业两门课不及格结页一门不结业
alter proc xuesheng
@xuehao int
as
begin
declare @yuwen int,@shuxue int,@yingyu int,@buhui int
select @yuwen=COUNT(*) from score where code=@xuehao and chiese>60
select @shuxue=COUNT(*) from score where code=@xuehao and math>=60
select @yingyu=COUNT(*) from score where code=@xuehao and english>=60
end
set @buhui=@yuwen+@shuxue+ @yingyu
if @buhui=3
print '优秀'
end
go
exec xuesheng
-------------使用return返回值的储存过程
create proc jisuan
@sum int=10
as
begin
set @sum=@sum+10
return @sum
end
go
--定义变量接收执行储存过程返回的值
declare @shu int
exec @shu=jisuan default
print @shu
---------------
--
create proc jiecheng
@al int
as
begin
declare @i int=0, @sum int=0
while @i<=@al
begin
set @sum=@sum+@i
set @i=@i+1
end
return @sum
end
go
declare @sum int
exec @sum=jiecheng 5
print @sum
--------带返回值,返回参数,输入参数的储存过程
select *from score--输入学号返回三门课的成绩
create proc sixproc
@code int,
@yuwen decimal(18,2) output--输出参数
@shuxue decimal(18,2)output
@yingyu decimal(18,2) output
as
begin
declare @count int
select @count=COUNT(*)from student where code=@code
select @yuwen =chiere,@shuxue=math,@yingyu=english from score where stucode=@ocde--select *from score where stucode=@ocde
return @count
end
go
--定义变量接收存储过程带出来的输出参数的值
declare @yuwen decimal(18,2),@yingyu decimal(18,2),@shuxue decimal(18,2)
exec sixproc 1,@yuwen output,@shuxue output,@yingyu output
print @yuwen+@shuxue+@yingyu
print @count