SQL Server 事務(wù)、異常和游標(biāo)
建議先閱讀存儲(chǔ)過程： SQL Server 存儲(chǔ)過程
? 事務(wù)
在數(shù)據(jù)庫中有時(shí)候需要把多個(gè)步驟的指令當(dāng)作一個(gè)整體來運(yùn)行，這個(gè)整體要么全部成功，要么全部失敗，這就需要用到事務(wù)。
??? 1、 事務(wù)的特點(diǎn)
??????? 事務(wù)有若干條T-SQL指令組成，并且所有的指令昨晚一個(gè)整體提交給數(shù)據(jù)庫系統(tǒng)，執(zhí)行時(shí)，這組指令要么全部執(zhí)行完成，要么全部取消。因此，事務(wù)是一個(gè)不可分割的邏輯單元。
?
??????? 事務(wù)有4個(gè)屬性：原子性（Atomicity）、一致性（Consistency）、隔離性（Isolation）以及持久性（Durability），也稱作事務(wù)的ACID屬性。
??????? ? 原子性 ：事務(wù)內(nèi)的所有工作要么全部完成，要么全部不完成，不存在只有一部分完成的情況。
??????? ? 一致性 ：事務(wù)內(nèi)的然后操作都不能違反數(shù)據(jù)庫的然后約束或規(guī)則，事務(wù)完成時(shí)有內(nèi)部數(shù)據(jù)結(jié)構(gòu)都必須是正確的。
??????? ? 隔離性 ：事務(wù)直接是相互隔離的，如果有兩個(gè)事務(wù)對(duì)同一個(gè)數(shù)據(jù)庫進(jìn)行操作，比如讀取表數(shù)據(jù)。任何一個(gè)事務(wù)看到的所有內(nèi)容要么是其他事務(wù)完成之前的狀態(tài)，要么是其他事務(wù)完成之后的狀態(tài)。一個(gè)事務(wù)不可能遇到另一個(gè)事務(wù)的中間狀態(tài)。
??????? ? 持久性 ：事務(wù)完成之后，它對(duì)數(shù)據(jù)庫系統(tǒng)的影響是持久的，即使是系統(tǒng)錯(cuò)誤，重新啟動(dòng)系統(tǒng)后，該事務(wù)的結(jié)果依然存在。
?
??? 2、 事務(wù)的模式
??????? a、 顯示事務(wù)
??????? 顯示事務(wù)就是用戶使用T-SQL明確的定義事務(wù)的開始（begin transaction）和提交（commit transaction）或回滾事務(wù)（rollback transaction）
??????? b、 自動(dòng)提交事務(wù)
??????? 自動(dòng)提交事務(wù)是一種能夠自動(dòng)執(zhí)行并能自動(dòng)回滾事務(wù)，這種方式是T-SQL的默認(rèn)事務(wù)方式。例如在刪除一個(gè)表記錄的時(shí)候，如果這條記錄有主外鍵關(guān)系的時(shí)候，刪除就會(huì)受主外鍵約束的影響，那么這個(gè)刪除就會(huì)取消。
??????? 可以設(shè)置事務(wù)進(jìn)入隱式方式：set implicit_transaction on;
??????? c、 隱式事務(wù)
??????? 隱式事務(wù)是指當(dāng)事務(wù)提交或回滾后，SQL Server自動(dòng)開始事務(wù)。因此，隱式事務(wù)不需要使用begin transaction顯示開始，只需直接失業(yè)提交事務(wù)或回滾事務(wù)的T-SQL語句即可。
??????? 使用時(shí)，需要設(shè)置set implicit_transaction on語句，將隱式事務(wù)模式打開，下一個(gè)語句會(huì)啟動(dòng)一個(gè)新的事物，再下一個(gè)語句又將啟動(dòng)一個(gè)新事務(wù)。
?
??? 3、 事務(wù)處理
??????? 常用T-SQL事務(wù)語句：
??????? a、 begin transaction語句
??????? 開始事務(wù)，而@@trancount全局變量用來記錄事務(wù)的數(shù)目值加1，可以用@@error全局變量記錄執(zhí)行過程中的錯(cuò)誤信息，如果沒有錯(cuò)誤可以直接提交事務(wù)，有錯(cuò)誤可以回滾。
??????? b、 commit transaction語句
??????? 回滾事務(wù)，表示一個(gè)隱式或顯示的事務(wù)的結(jié)束，對(duì)數(shù)據(jù)庫所做的修改正式生效。并將@@trancount的值減1；
??????? c、 rollback transaction語句
??????? 回滾事務(wù)，執(zhí)行rollback tran語句后，數(shù)據(jù)會(huì)回滾到begin tran的時(shí)候的狀態(tài)
?
??? 4、 事務(wù)的示例
--開始事務(wù)
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
'出現(xiàn)異常，錯(cuò)誤編號(hào)：'
+
convert
(
varchar
, error_number()) +
'， 錯(cuò)誤消息：'
+ error_message();
set
@tran_error = @tran_error + 1;
end
catch
if
(@tran_error > 0)
begin
--執(zhí)行出錯(cuò)，回滾事務(wù)
rollback
tran
;
print
'轉(zhuǎn)賬失敗，取消交易'
;
end
else
begin
--沒有異常，提交事務(wù)
commit
tran
;
print
'轉(zhuǎn)賬成功'
;
end
go
? 異常
???? 在程序中，有時(shí)候完成一些Transact-SQL會(huì)出現(xiàn)錯(cuò)誤、異常信息。如果我們想自己處理這些異常信息的話，需要手動(dòng)捕捉這些信息。那么我們可以利用try catch完成。
TRY…CATCH 構(gòu)造包括兩部分：一個(gè) TRY 塊和一個(gè) CATCH 塊。如果在 TRY 塊中所包含的 Transact-SQL 語句中檢測(cè)到錯(cuò)誤條件，控制將被傳遞到 CATCH 塊（可在此塊中處理該錯(cuò)誤）。
???? CATCH 塊處理該異常錯(cuò)誤后，控制將被傳遞到 END CATCH 語句后面的第一個(gè) Transact-SQL 語句。如果 END CATCH 語句是存儲(chǔ)過程或觸發(fā)器中的最后一條語句，控制將返回到調(diào)用該存儲(chǔ)過程或觸發(fā)器的代碼。將不執(zhí)行 TRY 塊中生成錯(cuò)誤的語句后面的 Transact-SQL 語句。
???? 如果 TRY 塊中沒有錯(cuò)誤，控制將傳遞到關(guān)聯(lián)的 END CATCH 語句后緊跟的語句。如果 END CATCH 語句是存儲(chǔ)過程或觸發(fā)器中的最后一條語句，控制將傳遞到調(diào)用該存儲(chǔ)過程或觸發(fā)器的語句。
???? TRY 塊以 BEGIN TRY 語句開頭，以 END TRY 語句結(jié)尾。在 BEGIN TRY 和 END TRY 語句之間可以指定一個(gè)或多個(gè) Transact-SQL 語句。CATCH 塊必須緊跟 TRY 塊。CATCH 塊以 BEGIN CATCH 語句開頭，以 END CATCH 語句結(jié)尾。在 Transact-SQL 中，每個(gè) TRY 塊僅與一個(gè) CATCH 塊相關(guān)聯(lián)。
???? # 錯(cuò)誤函數(shù)
TRY...CATCH 使用錯(cuò)誤函數(shù)來捕獲錯(cuò)誤信息。
ERROR_NUMBER() 返回錯(cuò)誤號(hào)。
ERROR_MESSAGE() 返回錯(cuò)誤消息的完整文本。此文本包括為任何可替換參數(shù)（如長(zhǎng)度、對(duì)象名稱或時(shí)間）提供的值。
ERROR_SEVERITY() 返回錯(cuò)誤嚴(yán)重性。
ERROR_STATE() 返回錯(cuò)誤狀態(tài)號(hào)。
ERROR_LINE() 返回導(dǎo)致錯(cuò)誤的例程中的行號(hào)。
ERROR_PROCEDURE() 返回出現(xiàn)錯(cuò)誤的存儲(chǔ)過程或觸發(fā)器的名稱。
???? 示例
--錯(cuò)誤消息存儲(chǔ)過程
if
(object_id(
'proc_error_info'
)
is
not
null
)
drop
procedure
proc_error_info
go
create
proc
proc_error_info
as
select
error_number()
'錯(cuò)誤編號(hào)'
,
error_message()
'錯(cuò)誤消息'
,
error_severity()
'嚴(yán)重性'
,
error_state()
'狀態(tài)好'
,
error_line()
'錯(cuò)誤行號(hào)'
,
error_procedure()
'錯(cuò)誤對(duì)象(存儲(chǔ)過程或觸發(fā)器)名稱'
;
go
?
???? # 示例：用異常處理錯(cuò)誤信息
--簡(jiǎn)單try catch示例
begin
try
select
1 / 0;
end
try
begin
catch
exec
proc_error_info; --調(diào)用錯(cuò)誤消息存儲(chǔ)過程
end
catch
go
?
???? # 示例：異常能處理的錯(cuò)誤信息
--
--簡(jiǎn)單try catch示例，無法處理錯(cuò)誤
begin
try
select
* *
from
student;
end
try
begin
catch
exec
proc_error_info;
end
catch
go
--
--簡(jiǎn)單try catch示例，不處理錯(cuò)誤(不存在的表對(duì)象)
begin
try
select
*
from
st;
end
try
begin
catch
exec
proc_error_info;
end
catch
go
--
--異常處理，能處理存儲(chǔ)過程（觸發(fā)器）中（不存在表對(duì)象）的錯(cuò)誤信息
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
???? 異常不能處理編譯期的錯(cuò)誤，如語法錯(cuò)誤。以及重編譯造成部分名稱對(duì)象得不到正確解析的時(shí)候所出現(xiàn)的錯(cuò)誤。
?
???? # 示例：無法提交的事務(wù)
--創(chuàng)建臨時(shí)用表
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
'會(huì)話具有活動(dòng)事務(wù)，但出現(xiàn)了致使事務(wù)被歸類為無法提交的事務(wù)的錯(cuò)誤。'
+
'會(huì)話無法提交事務(wù)或回滾到保存點(diǎn)；它只能請(qǐng)求完全回滾事務(wù)。'
+
'會(huì)話在回滾事務(wù)之前無法執(zhí)行任何寫操作。會(huì)話在回滾事務(wù)之前只能執(zhí)行讀操作。'
+
'事務(wù)回滾之后，會(huì)話便可執(zhí)行讀寫操作并可開始新的事務(wù)。'
;
end
else
if
(xact_state() = 0)
begin
print
'會(huì)話沒有活動(dòng)事務(wù)。'
;
end
else
if
(xact_state() = 1)
begin
print
'會(huì)話具有活動(dòng)事務(wù)。會(huì)話可以執(zhí)行任何操作，包括寫入數(shù)據(jù)和提交事務(wù)。'
;
end
end
catch
go
?
???? # 示例：處理異常日志信息
--
---異常、錯(cuò)誤信息表
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(), --發(fā)生錯(cuò)誤的日期和時(shí)間。
userName sysname
default
current_user
, --執(zhí)行發(fā)生錯(cuò)誤的批處理的用戶。
errorNumber
int
, --發(fā)生的錯(cuò)誤的錯(cuò)誤號(hào)。
errorSeverity
int
, --發(fā)生的錯(cuò)誤的嚴(yán)重性。
errorState
int
, --發(fā)生的錯(cuò)誤的狀態(tài)號(hào)。
errorProcedure nvarchar(126), --發(fā)生錯(cuò)誤的存儲(chǔ)過程或觸發(fā)器的名稱。
errorLine
int
, --發(fā)生錯(cuò)誤的行號(hào)。
errorMessage nvarchar(4000)
)
go
--
--存儲(chǔ)過程：添加異常日志信息
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
'會(huì)話具有活動(dòng)事務(wù)，但出現(xiàn)了致使事務(wù)被歸類為無法提交的事務(wù)的錯(cuò)誤。'
+
'會(huì)話無法提交事務(wù)或回滾到保存點(diǎn)；它只能請(qǐng)求完全回滾事務(wù)。'
+
'會(huì)話在回滾事務(wù)之前無法執(zhí)行任何寫操作。會(huì)話在回滾事務(wù)之前只能執(zhí)行讀操作。'
+
'事務(wù)回滾之后，會(huì)話便可執(zhí)行讀寫操作并可開始新的事務(wù)。'
;
end
else
if
(xact_state() = 0)
begin
print
'會(huì)話沒有活動(dòng)事務(wù)。'
;
end
else
if
(xact_state() = 1)
begin
print
'會(huì)話具有活動(dòng)事務(wù)。會(huì)話可以執(zhí)行任何操作，包括寫入數(shù)據(jù)和提交事務(wù)。'
;
end
--添加日志信息
insert
into
errorLog
values
(getDate(),
current_user
, error_number(),
error_severity(), error_state(),
error_procedure(),
error_line(), error_message());
--設(shè)置自增值
select
@logId = @@
identity
;
end
try
begin
catch
print
'添加異常日志信息出現(xiàn)錯(cuò)誤'
;
exec
proc_error_info;--顯示錯(cuò)誤信息
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;--顯示錯(cuò)誤信息
if
(xact_state() <> 0)
begin
rollback
tran
;
end
exec
proc_add_exception_log @id
output
end
catch
select
*
from
errorLog
where
errorLogID = @id;
go
?
? 游標(biāo)
???? 游標(biāo)可以對(duì)一個(gè)select的結(jié)果集進(jìn)行處理，或是不需要全部處理，就會(huì)返回一個(gè)對(duì)記錄集進(jìn)行處理之后的結(jié)果。
???? 1、游標(biāo)實(shí)際上是一種能從多條數(shù)據(jù)記錄的結(jié)果集中每次提取一條記錄的機(jī)制。游標(biāo)可以完成：
????????? # 允許定位到結(jié)果集中的特定行
????????? # 從結(jié)果集的當(dāng)前位置檢索一行或多行數(shù)據(jù)
????????? # 支持對(duì)結(jié)果集中當(dāng)前位置的進(jìn)行修改
???? 由于游標(biāo)是將記錄集進(jìn)行一條條的操作，所以這樣給服務(wù)器增加負(fù)擔(dān)，一般在操作復(fù)雜的結(jié)果集的情況下，才使用游標(biāo)。SQL Server 2005有三種游標(biāo)：T-SQL游標(biāo)、API游標(biāo)、客戶端游標(biāo)。
?
???? 2、游標(biāo)的基本操作
????????? 游標(biāo)的基本操作有定義游標(biāo)、打開游標(biāo)、循環(huán)讀取游標(biāo)、關(guān)閉游標(biāo)、刪除游標(biāo)。
???? A、 定義游標(biāo)
declare
cursor_name --游標(biāo)名稱
cursor
[
local
|
global
] --全局、局部
[forward
only
|
scroll
] --游標(biāo)滾動(dòng)方式
[read_only | scroll_locks | optimistic] --讀取方式
for
select_statements --查詢語句
[
for
update
|
of
column_name ...] --修改字段
???? 參數(shù)：
???? forward only | scroll：前一個(gè)參數(shù)，游標(biāo)只能向后移動(dòng)；后一個(gè)參數(shù)，游標(biāo)可以隨意移動(dòng)
???? read_only：只讀游標(biāo)
???? scroll_locks：游標(biāo)鎖定，游標(biāo)在讀取時(shí)，數(shù)據(jù)庫會(huì)將該記錄鎖定，以便游標(biāo)完成對(duì)記錄的操作
???? optimistic：該參數(shù)不會(huì)鎖定游標(biāo)；此時(shí)，如果記錄被讀入游標(biāo)后，對(duì)游標(biāo)進(jìn)行更新或刪除不會(huì)超過
?
???? B、 打開游標(biāo)
????????? open cursor_name;
????????? 游標(biāo)打開后，可以使用全局變量@@cursor_rows顯示讀取記錄條數(shù)
?
???? C、 檢索游標(biāo)
????????? fetch cursor_name;
????????? 檢索方式如下：
???????????? fetch first; 讀取第一行
???????????? fetch next; 讀取下一行
???????????? fetch prior; 讀取上一行
???????????? fetch last; 讀取最后一行
???????????? fetch absolute n; 讀取某一行
??????????????? 如果n為正整數(shù)，則讀取第n條記錄
??????????????? 如果n為負(fù)數(shù)，則倒數(shù)提取第n條記錄
??????????????? 如果n為，則不讀取任何記錄
???????????? fetch pelative n
??????????????? 如果n為正整數(shù)，則讀取上次讀取記錄之后第n條記錄
??????????????? 如果n為負(fù)數(shù)，則讀取上次讀取記錄之前第n條記錄
??????????????? 如果n為，則讀取上次讀取的記錄
?
???? D、 關(guān)閉游標(biāo)
????????? close cursor_name;
?
???? E、 刪除游標(biāo)
????????? deallocate cursor_name;
?
???? 3、游標(biāo)操作示例
--創(chuàng)建一個(gè)游標(biāo)
declare
cursor_stu
cursor
scroll
for
select
id, name, age
from
student;
--打開游標(biāo)
open
cursor_stu;
--存儲(chǔ)讀取的值
declare
@id
int
,
@name nvarchar(20),
@age
varchar
(20);
--讀取第一條記錄
fetch
first
from
cursor_stu
into
@id, @name, @age;
--循環(huán)讀取游標(biāo)記錄
print
'讀取的數(shù)據(jù)如下：'
;
--全局變量
while
(@@fetch_status = 0)
begin
print
'編號(hào)：'
+
convert
(
char
(5), @id) +
', 名稱：'
+ @name +
', 類型：'
+ @age;
--繼續(xù)讀取下一條記錄
fetch
next
from
cursor_stu
into
@id, @name, @age;
end
--關(guān)閉游標(biāo)
close
area_cursor;
--刪除游標(biāo)
--
deallocate
area_cursor;
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫作最大的動(dòng)力，如果您喜歡我的文章，感覺我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
