存储过程学习

--创建student、course表 create table ();
create  table student (
id int identity(1,1), --id 递增
stuNo  varchar(50) primary key , --主键约束
stuName varchar(50),
stuAge datetime,
stuSex varchar(5)
)

create table  course(
id int identity(1,1),
stuNo varchar(50),
courseName varchar(50),
courseScore decimal
)
--插入数据insert into、 union all
--insert into table_name () values ()
insert into student (stuNo,stuName,stuAge,stuSex )values('01','王男','1996-08-27 09:00:00.000','男')
select * from student

insert into student values ('02','杨幂','1995-4-20 6:0:0','女'),
						  ('03','程峰','1988-9-17 15:30:0','男')

insert into course values ('02','思想政治','85.5'),
						 ('02','数学','70'),
						 ('02','语文','80'),
						 ('02','物理','90'),
						 ('02','化学','65'),
						 ('02','英语','96'),
						 ('03','思想政治','60'),
						 ('03','数学','65'),
						 ('03','语文','84'),
						 ('03','物理','70'),
						 ('03','化学','76'),
						 ('03','英语','54')	
insert into student
select '06','张三','1895-5-27 14:30:28','女'
union all
select '07','李四','1990-1-20 19:0:0','女'
go
--修改数据
--update table_name set colunm='values'  where colunm=values
update student set stuSex='男',stuAge='2016-5-9 8:0:0' where stuName='王男'


--删除数据
--delete from table_name where colunm=values
delete from student where stuNo=01

------------------------------------------------------------------------------
------------------------------------------------------------------------------
--创建存储过程
create  proc stu
as
begin
	select * from student;
end

create proc cou1
as
begin
	select * from course
end

--执行存储过程
exec stu1
exec cou1


--存储过程根据条件用户名查询用户信息
--创建一个带局部变量的存储过程
create proc stu2
@sname varchar(50) --局部变量
as
begin
	select * from student s where s.stuName=@sname;
end

exec stu2 '张三'


create proc stu
@sex varchar(2)
as
begin
 select * from student s where s.stuSex=@sex
end
 

exec stu '男'
 -----
 /*
 *创建存储过程  create proce   proc_name    @value（局部变量）
 *          as  begin  (sql语句 )   end    
 * @var      --局部变量
 * declare @var  ---全局变量
 */

 --存储过程根据用户名查询是否存在这个用户信息
 create  proc stu3
 @stuName varchar(10), 
 @result  varchar(10) output
 as begin
	if(select count(1) from student s where @stuName=s.stuName) > 0
	set
			@result='存在!'
	else
	set
			@result='不存在!'
end	 
drop proc stu3
declare @result varchar(6)   
exec  stu3 'lisi' ,@result output
print @result


 
--存储过程内部设定局部变量用户名来查询用户信息
create proc stu5
as
declare @sname varchar(50)  --局部变量声明
set @sname='杨幂'
begin
	select * from student s where s.stuName=@sname
end
go
 
exec stu5
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程根据条件用户学号查询用户名
create proc stu6
@stuNo varchar(50)
as
declare @sname varchar(50)
set @sname=(select s.stuName from student s where s.stuNo=@stuNo)
select @sname
go
 
exec stu6 '01'
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程插入用户信息
create proc stu7
@stuNo varchar(50),
@stuName varchar(50),
@stuAge datetime,
@stuSex varchar(5)
as 
begin
	insert into student 
		(stuNo,stuName,stuAge,stuSex)
	values 
		(@stuNo,@stuName,@stuAge,@stuSex) 
end
go
 
exec stu7 '07','王莽','2000-9-9 9:9:9','女'
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程根据用户名来删除对应的用户信息
--@@rowcount返回操作条数
--return返回信息，终止下面的操作
create proc stu8
@stuName varchar(50)
as
begin
	delete from student where stuName=@stuName
	return @@rowcount
end
go
 
declare @result varchar(50)
exec @result=stu8 '王莽'
select @result as '删除条数'
--print @result
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程根据用户学号来查询他的平均分
create proc stu9
@stuNo varchar(50),
@avg int output
as 
begin
	set @avg=(select AVG(courseScore) from course where stuNo=@stuNo)
	--等同
	--select @avg=AVG(courseScore) from course where stuNo=@stuNo
end
go
 
declare @avg int
exec stu9 '02',@avg output
print @avg
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程根据用户学号来联合查询用户信息和课程信息
create proc stu10
@stuNo varchar(50)
as
	select c.stuNo,s.stuName,s.stuAge,s.stuSex,c.courseName,c.courseScore from student s join course c on s.stuNo=c.stuNo where s.stuNo=@stuNo
go
 
exec stu10 '02'
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程判断学号是否存在，不存在，插入用户信息，返回消息；存在，返回信息
create proc stu11
@stuNo varchar(50),
@stuName varchar(50),
@stuAge datetime,
@stuSex varchar(5),
@result varchar(50) output
as
	if exists (select * from student where stuNo=@stuNo)
		begin
			set @result='对不起，学号已存在!'
		end		
	else
		begin
			insert into student 
				(stuNo,stuName,stuAge,stuSex)
			values 
				(@stuNo,@stuName,@stuAge,@stuSex)
			set @result='恭喜你，用户信息插入成功!'	 
		end
go
 
declare @result varchar(50)
exec stu11 '06','王忠磊','1980-8-8 8:9:0','男',@result output
print @result
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程查询当前用户的平均成绩与总的平均成绩之间的关系
create proc stu12
@stuNo varchar(50)
as
declare @curAvg decimal(18,2)
declare @totalAvg decimal(18,2)
if exists(select * from course where stuNo=@stuNo)
	begin
		set @totalAvg=(select AVG(courseScore) from course)
		select @curAvg=AVG(courseScore) from course where stuNo=@stuNo
		print ('总的平均分:'+convert(varchar(18),@totalAvg))
		print ('该生的平均分:'+convert(varchar(18),@curAvg))
		if @curAvg>@totalAvg
			print '高于平均水平!'
		else 
			print '低于平均水平!'	
	end
else
	print '该生对应的分数信息不存在,请重新查询!'
go
 
exec stu12 '03'
go