mssql sqlserver 存储过程基础学习(入门)篇


摘要:
下文通过举例的方式,分享存储过程的基础例子(简单通俗易懂),供大家学习,如下所示:
实验环境:sql server 2008 R2


例:
下文主要是用学生和学生成绩表,作为基础表,编写一系列的存储过程,供大家存储过程入门

 ---创建测试信息表:
--创建用户数据表,表名为:maomao_stu
create table maomao_stu
(
	keyId int identity(1,1),--标识列,自动增加1
	stu_code varchar(30) primary key, ---主键,学号
	stu_Name  nvarchar(30),              ---姓名
	stu_Birthday datetime,                 ---生日
	stu_XingBie  nchar(1)     		  ---性别
)
 
--创建学生成绩表
create table mamao_chengJi
(
	keyId int identity(1,1),--标识列,自动增加1
	stu_code varchar(30) ,
	keCheng_name nvarchar(50),
	chengJi decimal(18,1)  ---分数最多采用一位小数的方式保存	
)
 
---生成基础数据
insert into maomao_stu(stu_code,
	   stu_Name,stu_Birthday,
	    stu_XingBie) values('08','猫猫','1985-11-10','女')
          ,('09','刘恺威','1983-10-2,'女')
          ,('10','阮一峰','1986-6-5','男')
						  
insert into mamao_chengJi values('08','科学','88'),
						 ('08','数学','78'),
						 ('08','语文','92'),
						 ('08','音乐','98'),
						 ('08','钢琴','56'),
						 ('08','英语','82')
						 
insert into mamao_chengJi values('09','科学','80'),
						 ('09','数学','63'),
						 ('09','语文','78'),
						 ('09','音乐','67'),
						 ('09','钢琴','73'),
						 ('09','英语','36')						 
						  
 ----存储过程功能:1:编写存储过程,查询所有的学生信息
create proc  pr_getAllUserInfo
 as
begin
	select * from maomao_stu;
end
go
 
exec pr_getAllUserInfo ---运行存储过程
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程功能:根据条件用户名查询用户信息
create proc pr_getUserInfoByName
@stu_name nvarchar(30)   --定义存储过程所使用的变量
as
begin
	select * from maomao_stu a where a.stu_Name=@ stu_name;
end
go
 
exec pr_getUserInfoByName '刘恺威'   ---测试存储过程
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程功能:设定默认值的查询方法
create proc pr_getUserInfoByName2
@stu_name nvarchar(30) ='刘恺威'   --定义存储过程所使用的变量,并赋默认值
as
begin
	select * from maomao_stu a where a.stu_Name=@ stu_name;
end
go
 
exec pr_getUserInfoByName2   ---测试存储过程
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程功能:根据用户名查询是否存在这个用户信息

create proc pr_checkUserNameExist
@stu_name nvarchar(30),     --定义存储过程所使用的变量,并赋默认值
@stu_output nvarchar(20)   output ---输出信息
as
begin 
        if exists (select * from maomao_stu a where a.stu_Name=@ stu_name)
	   begin
            set  @stu_output='存在此学生'
           end
	 else
            begin
	      set   @stu_output='不存在此学生'
           end
end
go
 
declare @tmp  nvarchar(20)
exec pr_checkUserNameExist   N'sql博客',@tmp output
print @ tmp
go
  
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程功能:插入用户信息
create proc pr_userInfoInsert
 @stu_code varchar(30) primary key, ---主键,学号
 @stu_Name  nvarchar(30),              ---姓名
 @stu_Birthday datetime,                 ---生日
  @stu_XingBie  nchar(1)   --性别
as 
begin  
    insert into maomao_stu 
	 (stu_code, ---主键,学号
          stu_Name ,              ---姓名
          stu_Birthday,                 ---生日
          stu_XingBie)
	values 
		(@stu_code, ---主键,学号
                @stu_Name ,              ---姓名
                @stu_Birthday,                 ---生日
                @stu_XingBie) 
end
go
 
exec pr_userInfoInsert '12','王菲','1975-12-5','女'
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程功能:编写一个存储过程,删除指定用户名,并返回删除的行数
create proc pr_delUserInfoByUserName
@stu_Name nvarchar(30)
as
begin
	delete from maomao_stu where stu_Name=@stu_Name
	return @@rowcount
end
go
  
declare @tmp int 
exec @tmp =  pr_delUserInfoByUserName '猫猫'
select @tmp  as '删除用户的行数' 
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程功能:根据用户编号获取用户的平均分
create proc pr_getUserChengJiByCOde 
@stu_code varchar(30),
@chengJi_Avg decimal(18,1) output
as 
begin
	set @avg=(select AVG(chengJie) from mamao_chengJi where stu_Code=@stu_code)
end
go
 
declare @chengJiAvg decimal(18,1)
exec pr_getUserChengJiByCOde '09',@chengJiAvg output
print @chengJiAvg
go
 
--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程功能:根据编号,返回用户信息和成绩信息
create proc pr_getUserInfoAndChengJiByUserCode
@stu_Code varchar(30)
as 
	select
         a.stu_code,
	a.stu_Name,
	a.stu_Birthday,
	a.stu_XingBie,
        b.keCheng_name,
	b.chengJi 
     from
    maomao_stu a join mamao_chengJi b on a.stu_code = b.stu_code  where a.stu_code=@stu_code
go
 
exec pr_getUserInfoAndChengJiByUserCode '09'
go
 

--------------------------------------------------------------------------
--------------------------------------------------------------------------
--存储过程功能:获取指定学生的平均成绩和平均成绩,并进行相应的对比
create proc pr_chengJiDuiBiByUserCode
@stu_Code varchar(30)
as
declare @userPingJunChengJi decimal(18,2)
declare @zongPingJunChengJi decimal(18,2)
if exists(select * from mamao_chengJi where stu_code=@stu_code)
	begin
		set @zongPingJunChengJi =(select AVG(chengJI) from mamao_chengJi)
		select @userPingJunChengJi =AVG(chengJi) from mamao_chengJi where  stu_code=@stu_code
		print ('全部同学的总平均成绩:'+convert(varchar(18),@zongPingJunChengJi))
		print ('当前学生的平均成绩:'+convert(varchar(18),@curAvg))
		if @userPingJunChengJi >@zongPingJunChengJi 
			print '高于总平均水平!'
		else 
			print '低于或等于总平均水平!'	
	end
else
	print '无该同学的成绩,请检查学生编号'
go
 
exec pr_chengJiDuiBiByUserCode '88'
go