在数据库中有时候需要把多个步骤的指令当作一个整体来运行,这个整体要么全部成功,要么全部失败,这就需要用到事务。
事务有若干条T-SQL指令组成,并且所有的指令昨晚一个整体提交给数据库系统,执行时,这组指令要么全部执行完成,要么全部取消。因此,事务是一个不可分割的逻辑单元。
事务有4个属性:原子性(Atomicity)、一致性(Consistency)、隔离性(Isolation)以及持久性(Durability),也称作事务的ACID属性。
常用T-SQL事务语句:
--开始事务
begin transaction tran_bank;
declare @tran_error int;
set @tran_error = 0;
begin try
update bank set totalMoney = totalMoney - 10000 where userName = 'jack';
set @tran_error = @tran_error + @@error;
update bank set totalMoney = totalMoney + 10000 where userName = 'jason';
set @tran_error = @tran_error + @@error;
end try
begin catch
print '出现异常,错误编号:' + convert(varchar, error_number()) + ', 错误消息:' + error_message();
set @tran_error = @tran_error + 1;
end catch
if (@tran_error > 0)
begin
--执行出错,回滚事务
rollback tran;
print '转账失败,取消交易';
end
else
begin
--没有异常,提交事务
commit tran;
print '转账成功';
end
go
在程序中,有时候完成一些Transact-SQL会出现错误、异常信息。如果我们想自己处理这些异常信息的话,需要手动捕捉这些信息。那么我们可以利用try catch完成。
TRY…CATCH 构造包括两部分:一个 TRY 块和一个 CATCH 块。如果在 TRY 块中所包含的 Transact-SQL 语句中检测到错误条件,控制将被传递到 CATCH 块(可在此块中处理该错误)。
CATCH 块处理该异常错误后,控制将被传递到 END CATCH 语句后面的第一个 Transact-SQL 语句。如果 END CATCH 语句是存储过程或触发器中的最后一条语句,控制将返回到调用该存储过程或触发器的代码。将不执行 TRY 块中生成错误的语句后面的 Transact-SQL 语句。
如果 TRY 块中没有错误,控制将传递到关联的 END CATCH 语句后紧跟的语句。如果 END CATCH 语句是存储过程或触发器中的最后一条语句,控制将传递到调用该存储过程或触发器的语句。
TRY 块以 BEGIN TRY 语句开头,以 END TRY 语句结尾。在 BEGIN TRY 和 END TRY 语句之间可以指定一个或多个 Transact-SQL 语句。CATCH 块必须紧跟 TRY 块。CATCH 块以 BEGIN CATCH 语句开头,以 END CATCH 语句结尾。在 Transact-SQL 中,每个 TRY 块仅与一个 CATCH 块相关联。
TRY…CATCH 使用错误函数来捕获错误信息。
ERROR_NUMBER() 返回错误号。
ERROR_MESSAGE() 返回错误消息的完整文本。此文本包括为任何可替换参数(如长度、对象名称或时间)提供的值。
ERROR_SEVERITY() 返回错误严重性。
ERROR_STATE() 返回错误状态号。
ERROR_LINE() 返回导致错误的例程中的行号。
ERROR_PROCEDURE() 返回出现错误的存储过程或触发器的名称。
if (object_id('proc_error_info') is not null)
drop procedure proc_error_info
go
create proc proc_error_info
as
select
error_number() '错误编号',
error_message() '错误消息',
error_severity() '严重性',
error_state() '状态好',
error_line() '错误行号',
error_procedure() '错误对象(存储过程或触发器)名称';
go
begin try
select * from st;
end try
begin catch
exec proc_error_info;
end catch
go
if (object_id('proc_select') is not null)
drop procedure proc_select
go
create proc proc_select
as
select * from st;
go
begin try
exec proc_select;
end try
begin catch
exec proc_error_info;
end catch
go
--创建临时用表
if (object_id('temp_tab', 'u') is not null)
drop table temp_tab
go
create table temp_tab(
id int primary key identity(100000, 1),
name varchar(200)
)
go
begin try
begin tran;
--没有createTime字段
alter table temp_tab drop column createTime;
commit tran;
end try
begin catch
exec proc_error_info;--显示异常信息
if (xact_state() = -1)
begin
print '会话具有活动事务,但出现了致使事务被归类为无法提交的事务的错误。'
+ '会话无法提交事务或回滚到保存点;它只能请求完全回滚事务。'
+ '会话在回滚事务之前无法执行任何写操作。会话在回滚事务之前只能执行读操作。'
+ '事务回滚之后,会话便可执行读写操作并可开始新的事务。';
end
else if (xact_state() = 0)
begin
print '会话没有活动事务。';
end
else if (xact_state() = 1)
begin
print '会话具有活动事务。会话可以执行任何操作,包括写入数据和提交事务。';
end
end catch
go
---异常、错误信息表
if (object_id('errorLog', 'U') is not null)
drop table errorLog
go
create table errorLog(
errorLogID int primary key identity(100, 1), --ErrorLog 行的主键。
errorTime datetime default getDate(), --发生错误的日期和时间。
userName sysname default current_user, --执行发生错误的批处理的用户。
errorNumber int, --发生的错误的错误号。
errorSeverity int, --发生的错误的严重性。
errorState int, --发生的错误的状态号。
errorProcedure nvarchar(126), --发生错误的存储过程或触发器的名称。
errorLine int, --发生错误的行号。
errorMessage nvarchar(4000)
)
go
if (object_id('proc_add_exception_log', 'p') is not null)
drop proc proc_add_exception_log
go
create proc proc_add_exception_log(@logId int = 0 output)
as
begin
set nocount on;
set @logId = 0;
begin try
if (error_number() is null)
return;
if (xact_state() = -1)
begin
print '会话具有活动事务,但出现了致使事务被归类为无法提交的事务的错误。'
+ '会话无法提交事务或回滚到保存点;它只能请求完全回滚事务。'
+ '会话在回滚事务之前无法执行任何写操作。会话在回滚事务之前只能执行读操作。'
+ '事务回滚之后,会话便可执行读写操作并可开始新的事务。';
end
else if (xact_state() = 0)
begin
print '会话没有活动事务。';
end
else if (xact_state() = 1)
begin
print '会话具有活动事务。会话可以执行任何操作,包括写入数据和提交事务。';
end
--添加日志信息
insert into errorLog values(getDate(),
current_user, error_number(),
error_severity(), error_state(),
error_procedure(),
error_line(), error_message());
--设置自增值
select @logId = @@identity;
end try
begin catch
print '添加异常日志信息出现错误';
exec proc_error_info;--显示错误信息
return -1;
end catch
end
go
declare @id int;
begin try
begin tran;
--删除带有外键的记录信息
delete classes where id = 1;
commit tran;
end try
begin catch
exec proc_error_info;--显示错误信息
if (xact_state() <> 0)
begin
rollback tran;
end
exec proc_add_exception_log @id output
end catch
select * from errorLog where errorLogID = @id;
go