SqlServer数据库的存储过程以及事务处理

SqlServer数据库的存储过程以及事务处理

记录时间：2016年8月11日  上午10点

记录人：Mr.Liu

记录我学习的后的知识总结，希望和读者一起分享学习经验！

一、存储过程

什么是存储过程？

通俗点就是和Java代码一样，可以反复调用

存储过程的格式

create proc 方法名(
    --参数
)
as
begin
    --定义变量
    --sql语句

end

exec 方法名  --访问存储过程

实例练习：

环境：Sql Server 2008数据库；

使用编程软件：Microsoft SQL Server Management Studio

--一、建立学生信息表（除年度测试外不得为空）
use master
create table studentInfo(
	studentId int primary key,
	studentName varchar(20) not null,
	studentAge int not null,
	studentAddress varchar(20) not null,
	studentGrade varchar(20) not null,
	result varchar(20) 
)
select * from studentInfo
go
--二、使用存储过程 插入以下数据至学生信息表
create proc addInfo(
	@studentId int,
	@studentName varchar(20),
	@studentAge int,
	@studentAddress varchar(20),
	@studentGrade varchar(20),
	@result varchar(20)
)
as
begin
	insert into studentInfo values (@studentId,@studentName,@studentAge,@studentAddress,@studentGrade,@result)
end
exec addInfo @studentId=201609001,@studentName='严静涵',@studentAge=19,@studentAddress='新洲',@studentGrade='大一',@result=''
exec addInfo @studentId=201509001,@studentName='邝兰芳',@studentAge=20,@studentAddress='坪山',@studentGrade='大二',@result='合格'
exec addInfo @studentId=201609002,@studentName='万琼英',@studentAge=20,@studentAddress='双龙',@studentGrade='大一',@result=''
exec addInfo @studentId=201509002,@studentName='元永宁',@studentAge=21,@studentAddress='龙华',@studentGrade='大二',@result=''
exec addInfo @studentId=201409001,@studentName='诸葛思怡',@studentAge=22,@studentAddress='光明',@studentGrade='大三',@result='合格'
exec addInfo @studentId=201609003,@studentName='陈冬卉',@studentAge=19,@studentAddress='西丽',@studentGrade='大一',@result=''
exec addInfo @studentId=201609004,@studentName='孟芷天',@studentAge=20,@studentAddress='西乡',@studentGrade='大一',@result=''
exec addInfo @studentId=201409002,@studentName='南宫良骏',@studentAge=21,@studentAddress='西乡',@studentGrade='大三',@result='合格'
go
--三、通过存储过程获取大一学生的所有信息
create proc getStudentGrade(
	@studentGrade varchar(20)
)
as
begin
	select * from studentInfo where studentGrade = @studentGrade 
end
exec getStudentGrade @studentGrade = '大一'
go
--四、通过存储过程 获取年龄为20的学生的所有信息
create proc getStudentAge(
	@studentAge int
)as
begin
	select * from studentInfo where studentAge = @studentAge
end
exec getStudentAge @studentAge = 20
go
--五、通过存储过程获取住址为西乡的学生的所有信息并计数（有几个学生）
create proc getStudentAddress(
	@studentAddress varchar(20),
	@count int output
)as
begin
	select * from studentInfo where studentAddress = @studentAddress 
	select @count = COUNT(*) from studentInfo where studentAddress = @studentAddress 
end
declare @number int
exec getStudentAddress @studentAddress = '西乡',@count = @number output
select @number as '人数'
go
--六、通过存储过程获取大二学生大一阶段合格学生的所有信息并计数
create proc getResult(
	@studentGrade varchar(20),
	@result varchar(20),
	@count int output
)as
begin
	select * from studentInfo where studentGrade = @studentGrade and result = @result
	select @count = COUNT(*) from studentInfo where studentGrade = @studentGrade and result = @result
end
declare @number int
exec getResult @studentGrade = '大二',@result = '合格',@count = @number output
select @number as '人数'
go
--七、通过存储过程获取所有测试合格名单
create proc getResult1(
	@result varchar(20)
)as
begin
	select * from studentInfo where result = @result
end
exec getResult1 @result = '合格'
go
--八、通过存储过程获取所有学生id 为2015开头的学生信息
create proc getStudentId
as
begin
	select * from studentInfo where studentId like '2015%'
end
exec getStudentId

select studentInfo.studentId as '学生id'  from studentInfo

二、事务

什么叫做事务？

一个SQL语句就是一个事务,事务分为很多步骤,只要在开启事务的过程,出现了问题，那么事务就会撤销，也就是说要么一起成功，要么全部撤销

事务4大属性：
    1.原子性(Atomicity):事务是一个完整的操作。
    2.一致性（Consistency）：当事务完成时，数据必须处于一致状态。
    3.隔离性(Isolation):对数据进行修改的所有并发事务是彼此隔离的。
    4.持久性(Durability):事务完成后，它对于系统的影响是永久性的。

事务开启的格式：

begin
	begin tran --步骤一：开启事物
	--步骤二：执行SQL语句操作
	        declare @cuowu int = 0  --定义局部变量
		update student set age = 22 where id = 1  --执行SQL更新语句操作
		set @cuowu = @@ROWCOUNT  --设置判断条件
		update student set age = 30 where id = 5
		set @cuowu = @@ROWCOUNT
		print @cuowu
	--步骤三：判断条件是否符合
	        if(@cuowu = 1)
		begin
			print '提交成功！'
			commit tran  --条件符合，则提交事务
		end
		else
		begin
			print '提交失败！'
			rollback tran  --条件不符合，则回滚事务（即撤销）
		end

end