SQL Server 事务、异常和游标

--开始事务
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

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

--简单try catch示例
begin try
select 1 / 0;
end try
begin catch
exec proc_error_info; --调用错误消息存储过程
end catch
go

--
--简单try catch示例，无法处理错误
begin try
select * * from student;
end try
begin catch
exec proc_error_info;
end catch
go
--
--简单try catch示例，不处理错误(不存在的表对象)
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

declare cursor_name    --游标名称
cursor [local | global]    --全局、局部

[forward only | scroll]    --游标滚动方式

[read_only | scroll_locks | optimistic]    --读取方式
for select_statements                    --查询语句

[for update | of column_name ...]        --修改字段

--创建一个游标
declare cursor_stu cursor scroll for
select id, name, age from student;

--打开游标
open cursor_stu;

--存储读取的值
declare @id int,

@name nvarchar(20),

@age varchar(20);

--读取第一条记录
fetch first from cursor_stu into @id, @name, @age;

--循环读取游标记录
print '读取的数据如下：';

--全局变量
while (@@fetch_status = 0)
begin
print '编号：' + convert(char(5), @id) + ', 名称：' + @name + ', 类型：' + @age;

--继续读取下一条记录
fetch next from cursor_stu into @id, @name, @age;
end

--关闭游标
close area_cursor;


--删除游标

--deallocate area_cursor;