摘要:
下文通过举例的方式，分享存储过程的基础例子(简单通俗易懂)，供大家学习，如下所示:
实验环境: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