sqlserver存储过程学习(通俗易懂)

---------------------------------------------------------------------------------------------------------存储过程Study----------------------------------------------------------------------------------------------------------删除表drop table studentgo------------------------------------------------------------------------------------------------------------------------------------------------------创建用户信息数据表create table student(id int identity(1,1),--递增1,stuNo varchar(50) primary key, --唯一键stuName varchar(50),stuAge datetime,stuSex varchar(5))--创建用户分数数据表create table course(id int identity(1,1),--递增1,stuNo varchar(50),courseName varchar(50),courseScore decimal)------------------------------------------------------------------------------------------------------------------------------------------------------插入数据--插入单条数据insert into student(stuNo,stuName,stuAge,stuSex) values('01','王男','1996-08-27 09:00:00.000','男')--多数量插入数据--SQL Server2008特有的插入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') insert into course values('03','思想政治','60'), ('03','数学','65'), ('03','语文','84'), ('03','物理','70'), ('03','化学','76'), ('03','英语','54')   --使用UNION ALL来进行插入insert into student select '04','wangan','1895-5-27 14:30:28','女'union all select '05','zhangnan','1990-1-20 19:0:0','女'go------------------------------------------------------------------------------------------------------------------------------------------------------修改数据update student set stuSex='男',stuAge='2016-5-9 8:0:0' where stuName='王男' go------------------------------------------------------------------------------------------------------------------------------------------------------删除数据delete from student where stuNo=01go------------------------------------------------------------------------------------------------------------------------------------------------------存储过程查询所有数据--begin...end 类似编程语言中的{}create proc stu1asbeginselect * from student;endgoexec stu1go------------------------------------------------------------------------------------------------------------------------------------------------------存储过程根据条件用户名查询用户信息create proc stu2@sname varchar(50)   --声明全局变量asbeginselect * from student s where s.stuName=@sname;endgoexec stu2 '王男'go------------------------------------------------------------------------------------------------------------------------------------------------------存储过程内部设定用户名查询用户信息create proc stu3@sname varchar(50)='王男'as beginselect * from student s where s.stuName=@sname; endgoexec stu3 go------------------------------------------------------------------------------------------------------------------------------------------------------存储过程根据用户名查询是否存在这个用户信息create proc stu4@sname varchar(50),@result varchar(8) output --输出参数asbegin if (select COUNT(1) from student s where s.stuName=@sname)>0 --if exists (select COUNT(1) from student s where s.stuName=@sname) set @result='存在!' else set @result='不存在!'endgodeclare @result varchar(8)exec stu4 '王男1',@result outputprint @resultgo------------------------------------------------------------------------------------------------------------------------------------------------------存储过程内部设定局部变量用户名来查询用户信息create proc stu5asdeclare @sname varchar(50)  --局部变量声明set @sname='杨幂'beginselect * from student s where s.stuName=@snameendgoexec stu5go------------------------------------------------------------------------------------------------------------------------------------------------------存储过程根据条件用户学号查询用户名create proc stu6@stuNo varchar(50)asdeclare @sname varchar(50)set @sname=(select s.stuName from student s where s.stuNo=@stuNo)select @snamegoexec stu6 '01'go------------------------------------------------------------------------------------------------------------------------------------------------------存储过程插入用户信息create proc stu7@stuNo varchar(50),@stuName varchar(50),@stuAge datetime,@stuSex varchar(5)as begininsert into student (stuNo,stuName,stuAge,stuSex)values (@stuNo,@stuName,@stuAge,@stuSex) endgoexec stu7 '07','王莽','2000-9-9 9:9:9','女'go------------------------------------------------------------------------------------------------------------------------------------------------------存储过程根据用户名来删除对应的用户信息--@@rowcount返回操作条数--return返回信息，终止下面的操作create proc stu8@stuName varchar(50)asbegindelete from student where stuName=@stuNamereturn @@rowcountendgodeclare @result varchar(50)exec @result=stu8 '王莽'select @result as '删除条数'--print @resultgo------------------------------------------------------------------------------------------------------------------------------------------------------存储过程根据用户学号来查询他的平均分create proc stu9@stuNo varchar(50),@avg int outputas beginset @avg=(select AVG(courseScore) from course where stuNo=@stuNo)--等同--select @avg=AVG(courseScore) from course where stuNo=@stuNoendgodeclare @avg intexec stu9 '02',@avg outputprint @avggo------------------------------------------------------------------------------------------------------------------------------------------------------存储过程根据用户学号来联合查询用户信息和课程信息create proc stu10@stuNo varchar(50)asselect 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=@stuNogoexec stu10 '02'go------------------------------------------------------------------------------------------------------------------------------------------------------存储过程判断学号是否存在，不存在，插入用户信息，返回消息；存在，返回信息create proc stu11@stuNo varchar(50),@stuName varchar(50),@stuAge datetime,@stuSex varchar(5),@result varchar(50) outputasif exists (select * from student where stuNo=@stuNo)beginset @result='对不起，学号已存在!'endelsebegininsert into student (stuNo,stuName,stuAge,stuSex)values (@stuNo,@stuName,@stuAge,@stuSex)set @result='恭喜你，用户信息插入成功!' endgodeclare @result varchar(50)exec stu11 '06','王忠磊','1980-8-8 8:9:0','男',@result outputprint @result------------------------------------------------------------------------------------------------------------------------------------------------------存储过程查询当前用户的平均成绩与总的平均成绩之间的关系create proc stu12@stuNo varchar(50)asdeclare @curAvg decimal(18,2)declare @totalAvg decimal(18,2)if exists(select * from course where stuNo=@stuNo)beginset @totalAvg=(select AVG(courseScore) from course)select @curAvg=AVG(courseScore) from course where stuNo=@stuNoprint ('总的平均分:'+convert(varchar(18),@totalAvg))print ('该生的平均分:'+convert(varchar(18),@curAvg))if @curAvg>@totalAvgprint '高于平均水平!'else print '低于平均水平!'endelseprint '该生对应的分数信息不存在,请重新查询!'goexec stu12 '03'go