SQL Server 事务异常和游标.docx
《SQL Server 事务异常和游标.docx》由会员分享，可在线阅读，更多相关《SQL Server 事务异常和游标.docx（13页珍藏版）》请在冰豆网上搜索。
SQLServer事务异常和游标
SQLServer事务、异常和游标
Ø事务
在数据库中有时候需要把多个步骤的指令当作一个整体来运行，这个整体要么全部成功，要么全部失败，这就需要用到事务。
1、事务的特点
事务有若干条T-SQL指令组成，并且所有的指令昨晚一个整体提交给数据库系统，执行时，这组指令要么全部执行完成，要么全部取消。
因此，事务是一个不可分割的逻辑单元。
事务有4个属性：
原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）以及持久性（Durability），也称作事务的ACID属性。
原子性：
事务内的所有工作要么全部完成，要么全部不完成，不存在只有一部分完成的情况。
一致性：
事务内的然后操作都不能违反数据库的然后约束或规则，事务完成时有内部数据结构都必须是正确的。
隔离性：
事务直接是相互隔离的，如果有两个事务对同一个数据库进行操作，比如读取表数据。
任何一个事务看到的所有内容要么是其他事务完成之前的状态，要么是其他事务完成之后的状态。
一个事务不可能遇到另一个事务的中间状态。
持久性：
事务完成之后，它对数据库系统的影响是持久的，即使是系统错误，重新启动系统后，该事务的结果依然存在。
2、事务的模式
a、显示事务
显示事务就是用户使用T-SQL明确的定义事务的开始（begintransaction）和提交（committransaction）或回滚事务（rollbacktransaction）
b、自动提交事务
自动提交事务是一种能够自动执行并能自动回滚事务，这种方式是T-SQL的默认事务方式。
例如在删除一个表记录的时候，如果这条记录有主外键关系的时候，删除就会受主外键约束的影响，那么这个删除就会取消。
可以设置事务进入隐式方式：
setimplicit_transactionon;
c、隐式事务
隐式事务是指当事务提交或回滚后，SQLServer自动开始事务。
因此，隐式事务不需要使用begintransaction显示开始，只需直接失业提交事务或回滚事务的T-SQL语句即可。
使用时，需要设置setimplicit_transactionon语句，将隐式事务模式打开，下一个语句会启动一个新的事物，再下一个语句又将启动一个新事务。
3、事务处理
常用T-SQL事务语句：
a、begintransaction语句
开始事务，而@@trancount全局变量用来记录事务的数目值加1，可以用@@error全局变量记录执行过程中的错误信息，如果没有错误可以直接提交事务，有错误可以回滚。
b、committransaction语句
回滚事务，表示一个隐式或显示的事务的结束，对数据库所做的修改正式生效。
并将@@trancount的值减1；
c、rollbacktransaction语句
回滚事务，执行rollbacktran语句后，数据会回滚到begintran的时候的状态
4、事务的示例
--开始事务
begintransactiontran_bank;
declare@tran_errorint;
set@tran_error=0;
begintry
updatebanksettotalMoney=totalMoney-10000whereuserName='jack';
set@tran_error=@tran_error+@@error;
updatebanksettotalMoney=totalMoney+10000whereuserName='jason';
set@tran_error=@tran_error+@@error;
endtry
begincatch
print'出现异常，错误编号：
'+convert（varchar,error_number（））+'，错误消息：
'+error_message（）;
set@tran_error=@tran_error+1;
endcatch
if（@tran_error>0）
begin
--执行出错，回滚事务
rollbacktran;
print'转账失败，取消交易';
end
else
begin
--没有异常，提交事务
committran;
print'转账成功';
end
go
Ø异常
在程序中，有时候完成一些Transact-SQL会出现错误、异常信息。
如果我们想自己处理这些异常信息的话，需要手动捕捉这些信息。
那么我们可以利用trycatch完成。
TRY…CATCH构造包括两部分：
一个TRY块和一个CATCH块。
如果在TRY块中所包含的Transact-SQL语句中检测到错误条件，控制将被传递到CATCH块（可在此块中处理该错误）。
CATCH块处理该异常错误后，控制将被传递到ENDCATCH语句后面的第一个Transact-SQL语句。
如果ENDCATCH语句是存储过程或触发器中的最后一条语句，控制将返回到调用该存储过程或触发器的代码。
将不执行TRY块中生成错误的语句后面的Transact-SQL语句。
如果TRY块中没有错误，控制将传递到关联的ENDCATCH语句后紧跟的语句。
如果ENDCATCH语句是存储过程或触发器中的最后一条语句，控制将传递到调用该存储过程或触发器的语句。
TRY块以BEGINTRY语句开头，以ENDTRY语句结尾。
在BEGINTRY和ENDTRY语句之间可以指定一个或多个Transact-SQL语句。
CATCH块必须紧跟TRY块。
CATCH块以BEGINCATCH语句开头，以ENDCATCH语句结尾。
在Transact-SQL中，每个TRY块仅与一个CATCH块相关联。
#错误函数
TRY...CATCH使用错误函数来捕获错误信息。
ERROR_NUMBER（）返回错误号。
ERROR_MESSAGE（）返回错误消息的完整文本。
此文本包括为任何可替换参数（如长度、对象名称或时间）提供的值。
ERROR_SEVERITY（）返回错误严重性。
ERROR_STATE（）返回错误状态号。
ERROR_LINE（）返回导致错误的例程中的行号。
ERROR_PROCEDURE（）返回出现错误的存储过程或触发器的名称。
示例
--错误消息存储过程
if（object_id（'proc_error_info'）isnotnull）
dropprocedureproc_error_info
go
createprocproc_error_info
as
select
error_number（）'错误编号',
error_message（）'错误消息',
error_severity（）'严重性',
error_state（）'状态好',
error_line（）'错误行号',
error_procedure（）'错误对象（存储过程或触发器）名称';
go
#示例：
用异常处理错误信息
--简单trycatch示例
begintry
select1/0;
endtry
begincatch
execproc_error_info;--调用错误消息存储过程
endcatch
go
#示例：
异常能处理的错误信息
--
--简单trycatch示例，无法处理错误
begintry
select**fromstudent;
endtry
begincatch
execproc_error_info;
endcatch
go
--
--简单trycatch示例，不处理错误（不存在的表对象）
begintry
select*fromst;
endtry
begincatch
execproc_error_info;
endcatch
go
--
--异常处理，能处理存储过程（触发器）中（不存在表对象）的错误信息
if（object_id（'proc_select'）isnotnull）
dropprocedureproc_select
go
createprocproc_select
as
select*fromst;
go
begintry
execproc_select;
endtry
begincatch
execproc_error_info;
endcatch
go
异常不能处理编译期的错误，如语法错误。
以及重编译造成部分名称对象得不到正确解析的时候所出现的错误。
#示例：
无法提交的事务
--创建临时用表
if（object_id（'temp_tab','u'）isnotnull）
droptabletemp_tab
go
createtabletemp_tab（
idintprimarykeyidentity（100000,1）,
namevarchar（200）
）
go
begintry
begintran;
--没有createTime字段
altertabletemp_tabdropcolumncreateTime;
committran;
endtry
begincatch
execproc_error_info;--显示异常信息
if（xact_state（）=-1）
begin
print'会话具有活动事务，但出现了致使事务被归类为无法提交的事务的错误。
'
+'会话无法提交事务或回滚到保存点；它只能请求完全回滚事务。
'
+'会话在回滚事务之前无法执行任何写操作。
会话在回滚事务之前只能执行读操作。
'
+'事务回滚之后，会话便可执行读写操作并可开始新的事务。
';
end
elseif（xact_state（）=0）
begin
print'会话没有活动事务。
';
end
elseif（xact_state（）=1）
begin
print'会话具有活动事务。
会话可以执行任何操作，包括写入数据和提交事务。
';
end
endcatch
go
#示例：
处理异常日志信息
--
---异常、错误信息表
if（object_id（'errorLog','U'）isnotnull）
droptableerrorLog
go
createtableerrorLog（
errorLogIDintprimarykeyidentity（100,1）,--ErrorLog行的主键。
errorTimedatetimedefaultgetDate（）,--发生错误的日期和时间。
userNamesysnamedefaultcurrent_user,--执行发生错误的批处理的用户。
errorNumberint,--发生的错误的错误号。
errorSeverityint,--发生的错误的严重性。
errorStateint,--发生的错误的状态号。
errorProcedurenvarchar（126）,--发生错误的存储过程或触发器的名称。
errorLineint,--发生错误的行号。
errorMessagenvarchar（4000）
）
go
--
--存储过程：
添加异常日志信息
if（object_id（'proc_add_exception_log','p'）isnotnull）
dropprocproc_add_exception_log
go
createprocproc_add_exception_log（@logIdint=0output）
as
begin
setnocounton;
set@logId=0;
begintry
if（error_number（）isnull）
return;
if（xact_state（）=-1）
begin
print'会话具有活动事务，但出现了致使事务被归类为无法提交的事务的错误。
'
+'会话无法提交事务或回滚到保存点；它只能请求完全回滚事务。
'
+'会话在回滚事务之前无法执行任何写操作。
会话在回滚事务之前只能执行读操作。
'
+'事务回滚之后，会话便可执行读写操作并可开始新的事务。
';
end
elseif（xact_state（）=0）
begin
print'会话没有活动事务。
';
end
elseif（xact_state（）=1）
begin
print'会话具有活动事务。
会话可以执行任何操作，包括写入数据和提交事务。
';
end
--添加日志信息
insertintoerrorLogvalues（getDate（）,
current_user,error_number（）,
error_severity（）,error_state（）,
error_procedure（）,
error_line（）,error_message（））;
--设置自增值
select@logId=@@identity;
endtry
begincatch
print'添加异常日志信息出现错误';
execproc_error_info;--显示错误信息
return-1;
endcatch
end
go
--
---处理异常信息示例
declare@idint;
begintry
begintran;
--删除带有外键的记录信息
deleteclasseswhereid=1;
committran;
endtry
begincatch
execproc_error_info;--显示错误信息
if（xact_state（）<>0）
begin
rollbacktran;
end
execproc_add_exception_log@idoutput
endcatch
select*fromerrorLogwhereerrorLogID=@id;
go
Ø游标
游标可以对一个select的结果集进行处理，或是不需要全部处理，就会返回一个对记录集进行处理之后的结果。
1、游标实际上是一种能从多条数据记录的结果集中每次提取一条记录的机制。
游标可以完成：
#允许定位到结果集中的特定行
#从结果集的当前位置检索一行或多行数据
#支持对结果集中当前位置的进行修改
由于游标是将记录集进行一条条的操作，所以这样给服务器增加负担，一般在操作复杂的结果集的情况下，才使用游标。
SQLServer2005有三种游标：
T-SQL游标、API游标、客户端游标。
2、游标的基本操作
游标的基本操作有定义游标、打开游标、循环读取游标、关闭游标、删除游标。
A、定义游标
declarecursor_name--游标名称
cursor[local|global]--全局、局部
[forwardonly|scroll]--游标滚动方式
[read_only|scroll_locks|optimistic]--读取方式
forselect_statements--查询语句
[forupdate|ofcolumn_name...]--修改字段
参数：
forwardonly|scroll：
前一个参数，游标只能向后移动；后一个参数，游标可以随意移动
read_only：
只读游标
scroll_locks：
游标锁定，游标在读取时，数据库会将该记录锁定，以便游标完成对记录的操作
optimistic：
该参数不会锁定游标；此时，如果记录被读入游标后，对游标进行更新或删除不会超过
B、打开游标
opencursor_name;
游标打开后，可以使用全局变量@@cursor_rows显示读取记录条数
C、检索游标
fetchcursor_name;
检索方式如下：
fetchfirst;读取第一行
fetchnext;读取下一行
fetchprior;读取上一行
fetchlast;读取最后一行
fetchabsoluten;读取某一行
如果n为正整数，则读取第n条记录
如果n为负数，则倒数提取第n条记录
如果n为，则不读取任何记录
fetchpelativen
如果n为正整数，则读取上次读取记录之后第n条记录
如果n为负数，则读取上次读取记录之前第n条记录
如果n为，则读取上次读取的记录
D、关闭游标
closecursor_name;
E、删除游标
deallocatecursor_name;
3、游标操作示例
--创建一个游标
declarecursor_stucursorscrollfor
selectid,name,agefromstudent;
--打开游标
opencursor_stu;
--存储读取的值
declare@idint,
@namenvarchar（20）,
@agevarchar（20）;
--读取第一条记录
fetchfirstfromcursor_stuinto@id,@name,@age;
--循环读取游标记录
print'读取的数据如下：
';
--全局变量
while（@@fetch_status=0）
begin
print'编号：
'+convert（char（5）,@id）+',名称：
'+@name+',类型：
'+@age;
--继续读取下一条记录
fetchnextfromcursor_stuinto@id,@name,@age;
end
--关闭游标
closearea_cursor;
--删除游标
--deallocatearea_cursor;
升级会员 