雖然學(xué)過(guò)SQLServer，但是在腦海里總是覺(jué)得很亂，并且有很多疑問(wèn)和盲點(diǎn)，如今走入工作崗位，那么這些關(guān)于自己知識(shí)和技術(shù)的漏洞是不應(yīng)該存在了，至少學(xué)一種語(yǔ)言或是技術(shù)，最起碼要知道是什么、做什么、有什么用、什么時(shí)候用、怎么用。如果連這一點(diǎn)都不是很清楚的話那么我不敢相信你做出來(lái)的東西有多好，有多強(qiáng)。以前在學(xué)習(xí)的時(shí)候，總覺(jué)得這些理論知識(shí)，自己知道就行了，不必太在意，會(huì)操作，能使用就行了，可是如今我不這么認(rèn)為了，可能這些很基礎(chǔ)的理論知識(shí)學(xué)起來(lái)很抽象，很枯燥，但是他們真的很有用。作為一名技術(shù)人員，沒(méi)有清晰透徹的理論作為基礎(chǔ)，那么在技術(shù)這條道路上是走不遠(yuǎn)，飛不高的。。。最近我在業(yè)余時(shí)間抽空復(fù)習(xí)自己所學(xué)的東西，找到一些不錯(cuò)的資料，在此分享給正在學(xué)習(xí)中的朋友們，我相信這些資源對(duì)你們的學(xué)習(xí)是很有用的。好了，廢話有點(diǎn)多了，咱們開(kāi)始吧。聲明：一下內(nèi)容是我轉(zhuǎn)載的一些內(nèi)容，稍作些許加工，以下有地址來(lái)源。。。
SQL教程: 事務(wù)
我們知道各種子查詢的用法，包括簡(jiǎn)單子查詢、IN子查詢和EXISTS子查詢。除此之外，我們?cè)趯?shí)際開(kāi)發(fā)中還會(huì)用到一些比較特殊的高級(jí)查詢，包括事務(wù)、索引和視圖。
例如 ，銀行轉(zhuǎn)賬問(wèn)題：假定資金從賬戶A轉(zhuǎn)到賬戶B，至少需要兩步，即賬戶A的資金減少，然后賬戶B的資金相應(yīng)增加。在進(jìn)行資金轉(zhuǎn)賬時(shí)，系統(tǒng)必須保證：這些步驟是一個(gè)整體，如果其間任一步驟失敗，則將撤銷(xiāo)對(duì)這兩個(gè)賬戶數(shù)據(jù)所做的任何修改，這時(shí)就需要使用事務(wù)處理。事務(wù)是指一個(gè)工作單元，該單元可以包含多個(gè)步驟來(lái)完成所需的任務(wù)。一個(gè)事務(wù)作為一個(gè)整體，要么成功，要么失敗。
正如漢語(yǔ)字典中的漢字按頁(yè)存放一樣，SQL Server中的數(shù)據(jù)記錄也是按頁(yè)存放的，每頁(yè)容量一般為4KB。為了加快查找的速度，漢語(yǔ)字（詞）典一般都有按拼音、筆畫(huà)、偏旁部首等排序的目錄（索引），我們可以選擇按拼音或筆畫(huà)查找，快速查找到需要的字（詞）。同理， SQL Server允許用戶在表中創(chuàng)建索引，指定按某列預(yù)先排序，從而大大提高查詢速度。
同一星球，用望遠(yuǎn)鏡從不同的角度或方位觀看，將看到星球的不同位置，從而得到不同的結(jié)果。同一張員工信息表數(shù)據(jù)，因?yàn)楣颈Ｃ艿脑颍赡芤蟛煌瑱?quán)限的人員看到不同的員工信息。例如：財(cái)務(wù)人員只能查看員工的姓名、工資、獎(jiǎng)金等；技術(shù)部經(jīng)理只能查看員工的姓名，職稱(chēng)、技能等；人事部經(jīng)理只能查看員工的姓名、工作經(jīng)歷和發(fā)展方向等；總經(jīng)理當(dāng)然可以全部查看。如何更加安全、直觀地顯示數(shù)據(jù)結(jié)果呢？SQL Server中允許用戶創(chuàng)建視圖，在同一原始數(shù)據(jù)表的基礎(chǔ)上，為不同的用戶選擇不同的列，從而達(dá)到不同用戶的需求。
下面我們將詳細(xì)討論事務(wù)、索引和視圖的具體使用。
事務(wù)(Transaction)是單個(gè)的工作單元。如果某一事務(wù)成功，則在該事務(wù)中進(jìn)行的所有數(shù)據(jù)更改均會(huì)提交，成為數(shù)據(jù)庫(kù)中的永久組成部分。如果事務(wù)遇到錯(cuò)誤且必須取消或回滾，則所有數(shù)據(jù)更改均被清除。
一、為什么需要事務(wù)
一般來(lái)說(shuō)，只要是同一銀行（例如都是農(nóng)行），一般都支持賬戶間直接轉(zhuǎn)賬。我們來(lái)看看上述提及的轉(zhuǎn)賬問(wèn)題，假定張三的賬戶直接轉(zhuǎn)賬1000元到李四的賬戶，就需要?jiǎng)?chuàng)建賬戶表，存放用戶的賬戶信息，T-SQL語(yǔ)句如 示例1 ：
/*
--舉例：為什么需要事務(wù)--
*/
--
同一銀行，如都是農(nóng)行的帳號(hào)，可以直接轉(zhuǎn)賬
/*
－--------------建表-----------------
*/
USE
stuDB
GO
--
創(chuàng)建農(nóng)行帳戶表bank
IF
EXISTS
(
SELECT
*
FROM
sysobjects
WHERE
name
=
'
bank
'
)
DROP
TABLE
bank
GO
CREATE
TABLE
bank
(
customerName
CHAR
(
10
),
--
顧客姓名
currentMoney
MONEY
--
當(dāng)前余額
)
GO
/*
---添加約束：根據(jù)銀行規(guī)定，帳戶余額不能少于1元，除非銷(xiāo)戶----
*/
ALTER
TABLE
bank
ADD
CONSTRAINT
CK_currentMoney
CHECK
(currentMoney
>=
1
)
GO
/*
--插入測(cè)試數(shù)據(jù)：張三開(kāi)戶，開(kāi)戶金額為800 ；李四開(kāi)戶，開(kāi)戶金額1 ---
*/
INSERT
INTO
bank(customerName,currentMoney)
VALUES
(
'
張三
'
,
1000
)
INSERT
INTO
bank(customerName,currentMoney)
VALUES
(
'
李四
'
,
1
)
GO
--
查看結(jié)果
SELECT
*
FROM
bank
GO
/*
--轉(zhuǎn)帳測(cè)試：張三希望通過(guò)轉(zhuǎn)賬，直接匯錢(qián)給李四1000元--
*/
--
我們可能會(huì)這樣這樣寫(xiě)代碼
--
張三的帳戶少1000元，李四的帳戶多1000元
UPDATE
bank
SET
currentMoney
=
currentMoney
-
1000
WHERE
customerName
=
'
張三
'
UPDATE
bank
SET
currentMoney
=
currentMoney
+
1000
WHERE
customerName
=
'
李四
'
GO
--
再次查看結(jié)果，結(jié)果發(fā)現(xiàn)了什么嚴(yán)重的錯(cuò)誤?如何解決呢？
SELECT
*
FROM
bank
GO
?上面代碼的輸出結(jié)果如圖1：
圖1 張三、李四的賬戶信息
注意：目前兩個(gè)賬戶的余額總和為：1000+1=1001元。
現(xiàn)在開(kāi)始模擬實(shí)現(xiàn)轉(zhuǎn)賬：從張三的賬戶直接轉(zhuǎn)賬IOOO元到李四的賬戶?？梢允褂肬PDATE語(yǔ)句修改張三的賬戶和李四的賬戶，張三的賬戶減少1OOO元，李四的賬戶增加1000元。 WANGYEXX.COM
顯然，轉(zhuǎn)賬后的余額總和應(yīng)保持不變，仍為1001元。
T-SQL實(shí)現(xiàn)如示例2所示。
示例2：
/*
--轉(zhuǎn)賬測(cè)試：張三轉(zhuǎn)賬1000元給李四--
*/
--
我們可能會(huì)這樣這樣編寫(xiě)語(yǔ)句
--
張三的賬戶少1000元，李四的賬戶多1000元
UPDATE
bank
SET
currentMoney
=
currentMoney
-
1000
WHERE
customerName
=
'
張三
'
UPDATE
bank
SET
currentMoney
=
currentMoney
+
1000
WHERE
customerName
=
'
李四
'
GO
--
再次查看轉(zhuǎn)賬后的結(jié)果。
SELECT
*
FROM
bank
GO
?
上述語(yǔ)句的輸出結(jié)果如圖2所示。
輸出的結(jié)果是張三的賬戶沒(méi)有減少，還是1000元，但李四的賬戶卻多了1000元，轉(zhuǎn)賬后兩個(gè)賬戶的余額總和變?yōu)?000+1001=2001元，銀行的錢(qián)憑空多出1000元！
?
為什么會(huì)這樣呢？讓我們一起分析出現(xiàn)如此錯(cuò)誤的原因。
?
查看SQL Server給出的錯(cuò)誤提示，顯示UPDATE語(yǔ)句有錯(cuò)，執(zhí)行時(shí)違反了CK_currentMoney約束，即余額不能少于1元。目前有兩條UPDATE語(yǔ)句，哪條語(yǔ)句導(dǎo)致了此錯(cuò)誤呢？顯然是修改張三賬戶的UPDATE語(yǔ)句。因?yàn)閺埲馁~戶原有余額1000元，減少1000元后即為0元，違反了上述約束，所以終止執(zhí)行，余額保持不變，仍為1000元。遺憾的是，后面的語(yǔ)句并沒(méi)有中斷執(zhí)行，修改李四賬戶的UPDATE語(yǔ)句繼續(xù)執(zhí)行，李四的賬戶增加了1000元，變?yōu)?001元。所以兩人賬戶的余額最終出現(xiàn)了圖2所示的結(jié)果。
?
如何解決呢？使用事務(wù)，轉(zhuǎn)賬過(guò)程就是一個(gè)事務(wù)，它需要兩條UPDATE語(yǔ)句來(lái)完成，這兩條語(yǔ)句是一個(gè)整體。如果其中任何一條出現(xiàn)錯(cuò)誤，則整個(gè)轉(zhuǎn)賬業(yè)務(wù)也應(yīng)取消，兩個(gè)賬戶中的余額應(yīng)恢復(fù)到原來(lái)的數(shù)據(jù)，從而確保轉(zhuǎn)賬前和轉(zhuǎn)賬后的余額總和不變，即都是1001元。
二、什么是事務(wù)
事務(wù)是一種機(jī)制、一個(gè)操作序列，它包含了一組數(shù)據(jù)庫(kù)操作命令，并且所有的命令作為一個(gè)整體一起向系統(tǒng)提交或撤銷(xiāo)操作請(qǐng)求，即這一組數(shù)據(jù)庫(kù)命令要么都執(zhí)行，要么都不執(zhí)行，因此事務(wù)是一個(gè)不可分割的工作邏輯單元，在數(shù)據(jù)庫(kù)系統(tǒng)上執(zhí)行并發(fā)操作時(shí)事務(wù)是作為最小的控制單元來(lái)使用的。它特別適用于多用戶同時(shí)操作的數(shù)據(jù)庫(kù)系統(tǒng)。例如，航空公司的訂票系統(tǒng)、銀行、保險(xiǎn)公司以及證券交易系統(tǒng)等。
事務(wù)是作為單個(gè)邏輯工作單元執(zhí)行的一系列操作。一個(gè)邏輯工作單元必須有4個(gè)屬性，即原子性（Atomicity）、一致性(Consistency)、隔離性(Isolation)及持久性（Durability），這些特性通常簡(jiǎn)稱(chēng)為ACID。
(1) 原子性（Atomicity）： 事務(wù)是一個(gè)完整的操作。事務(wù)的各元素是不可分的（原子的）。事務(wù)中的所有元素必須作為一個(gè)整體提交或回滾。如果事務(wù)中的任何元素失敗，則整個(gè)事務(wù)將失敗。
再次以銀行轉(zhuǎn)賬事務(wù)為例，如果該事務(wù)提交了，則這兩個(gè)賬戶的數(shù)據(jù)將會(huì)更新。如果由于某種原因，事務(wù)在成功更新這兩個(gè)賬戶之前終止，則不會(huì)更新這兩個(gè)賬戶余額，并且會(huì)撤銷(xiāo)對(duì)任何賬戶余額的修改。事務(wù)不能部分提交。
(2) 一致性（Consistency）：當(dāng)事務(wù)完成時(shí)，數(shù)據(jù)必須處于一致?tīng)顟B(tài)。也就是說(shuō)，在事務(wù)開(kāi)始之前，數(shù)據(jù)庫(kù)中存儲(chǔ)的數(shù)據(jù)處于一致?tīng)顟B(tài)。在正在進(jìn)行的事務(wù)中，數(shù)據(jù)可能處于不一致的狀態(tài)，例如，數(shù)據(jù)可能有部分修改。然而，當(dāng)事務(wù)成功完成時(shí)，數(shù)據(jù)必須再次回到己知的一致?tīng)顟B(tài)。通過(guò)事務(wù)對(duì)數(shù)據(jù)所做的修改不能損壞數(shù)據(jù)，或者說(shuō)事務(wù)不能使數(shù)據(jù)存儲(chǔ)處于不穩(wěn)定的狀態(tài)。
再次以銀行轉(zhuǎn)賬事務(wù)為例。在事務(wù)開(kāi)始之前，所有賬戶余額的總額處于一致?tīng)顟B(tài)。在事務(wù)進(jìn)行的過(guò)程中，一個(gè)賬戶余額減少，而另一個(gè)賬戶余額尚未修改。因此，所有賬戶余額的總額處于不一致?tīng)顟B(tài)。事務(wù)完成以后，賬戶余額的總額再次恢復(fù)一致?tīng)顟B(tài)。
(3) 隔離性（Isolation）：對(duì)數(shù)據(jù)進(jìn)行修改的所有并發(fā)事務(wù)是彼此隔離的，這表明事務(wù)必須是獨(dú)立的，它不應(yīng)以任何方式依賴于或影響其他事務(wù)。修改數(shù)據(jù)的事務(wù)可以在另一個(gè)使用相同數(shù)據(jù)的事務(wù)開(kāi)始之前訪問(wèn)這些數(shù)據(jù)，或者在另一個(gè)使用相同數(shù)據(jù)的事務(wù)結(jié)束之后訪問(wèn)這些數(shù)據(jù)。另外，當(dāng)事務(wù)修改數(shù)據(jù)時(shí)，如果任何其他進(jìn)程正在同時(shí)使用相同的數(shù)據(jù)，則直到該事務(wù)成功提交之后，對(duì)數(shù)據(jù)的修改才能生效。張三和李四之間的轉(zhuǎn)賬以及王五和趙二之間，永遠(yuǎn)是相互獨(dú)立的。
(4) 持久性（Durability）：事務(wù)完成之后，它對(duì)于系統(tǒng)的影響是永久性的。該修改即使出現(xiàn)系統(tǒng)故障，也將一直保持。
三、如何創(chuàng)建事務(wù)
Transact-SQL使用下列語(yǔ)句來(lái)管理事務(wù)。
● 開(kāi)始事務(wù)：BEGIN TRANSACTION。
● 提交事務(wù)：COIVIIVIIT TRANSACTION。
● 回滾（撤銷(xiāo)）事務(wù)：ROLLBACK TRANSACTION。
事務(wù)的分類(lèi)有以下3種。
● 顯式事務(wù)：用BEGIN TRANSACTION明確指定事務(wù)的開(kāi)始。
● 隱式事務(wù)：通過(guò)設(shè)置SET IMPLICIT TRANSACTIONS ON語(yǔ)句，將隱式事務(wù)模式設(shè)置為打開(kāi)。當(dāng)以隱式事務(wù)操作時(shí)，SQL Server將在提交或回滾事務(wù)后自動(dòng)啟動(dòng)新事務(wù)。無(wú)法描述事務(wù)的開(kāi)始，只需提交或回滾每個(gè)事務(wù)。
● 自動(dòng)提交事務(wù)：這是SQL Server的默認(rèn)模式，它將每條單獨(dú)的T-SQL語(yǔ)句視為一個(gè)事務(wù)。如果成功執(zhí)行，則自動(dòng)提交。如果錯(cuò)誤，則自動(dòng)回滾。
實(shí)際開(kāi)發(fā)中最常用的就是顯式事務(wù)，它明確地指定事務(wù)的開(kāi)始邊界。
判斷T-SQL語(yǔ)句是否有錯(cuò)，將使用到曾講過(guò)的全局變量@@ERROR，它用來(lái)判斷當(dāng)前T-SQL語(yǔ)句執(zhí)行是否有錯(cuò)誤，若有錯(cuò)誤則返回非零值。下面我們應(yīng)用顯式事務(wù)來(lái)解決上述轉(zhuǎn)賬問(wèn)題，T-SQL語(yǔ)句如示例3所示。
示例3：
?
USE
stuDB
GO
--
恢復(fù)原來(lái)的數(shù)據(jù)
--
UPDATE bank SET currentMoney=currentMoney-1000 WHERE customerName='李四'
SET
NOCOUNT
ON
--
不顯示受影響的行數(shù)信息
print
'
查看轉(zhuǎn)帳事務(wù)前的余額
'
SELECT
*
FROM
bank
GO
/*
--開(kāi)始事務(wù)（指定事務(wù)從此處開(kāi)始，后續(xù)的T-SQL語(yǔ)句都是一個(gè)整體--
*/
BEGIN
TRANSACTION
/*
--定義變量，用于累計(jì)事務(wù)執(zhí)行過(guò)程中的錯(cuò)誤--
*/
DECLARE
@errorSum
INT
SET
@errorSum
=
0
--
初始化為0，即無(wú)錯(cuò)誤
/*
--轉(zhuǎn)帳：張三的帳戶少1000元，李四的帳戶多1000元
*/
UPDATE
bank
SET
currentMoney
=
currentMoney
-
800
WHERE
customerName
=
'
張三
'
SET
@errorSum
=
@errorSum
+
@@error
--
累計(jì)是否有錯(cuò)誤
UPDATE
bank
SET
currentMoney
=
currentMoney
+
800
WHERE
customerName
=
'
李四
'
SET
@errorSum
=
@errorSum
+
@@error
--
累計(jì)是否有錯(cuò)誤
print
'
查看轉(zhuǎn)帳事務(wù)過(guò)程中的余額
'
SELECT
*
FROM
bank
/*
--根據(jù)是否有錯(cuò)誤，確定事務(wù)是提交還是撤銷(xiāo)---
*/
IF
@errorSum
<>
0
--
如果有錯(cuò)誤
BEGIN
print
'
交易失敗，回滾事務(wù)
'
ROLLBACK
TRANSACTION
END
ELSE
BEGIN
print
'
交易成功，提交事務(wù)，寫(xiě)入硬盤(pán)，永久的保存
'
COMMIT
TRANSACTION
END
GO
print
'
查看轉(zhuǎn)帳事務(wù)后的余額
'
SELECT
*
FROM
bank
GO
?
圖3 事務(wù)處理：交易失敗的情況
示例3中，我們將轉(zhuǎn)賬金額設(shè)置為1000元，因張三的賬戶余額為0，違反了約束而有錯(cuò)。如果我們修改轉(zhuǎn)賬金額為800元，則結(jié)果如圖4所示。
圖3 事務(wù)處理：交易成功的情況
?
|
說(shuō)明：現(xiàn)實(shí)中銀行的開(kāi)戶、轉(zhuǎn)賬問(wèn)題比上述處理要更加復(fù)雜，如后續(xù)的項(xiàng)目案例所示。 |
| 轉(zhuǎn)載請(qǐng)注明原文地址: http://www.wangyexx.com/db/sqlbasic/1460.html |
?
?
?
?
更多文章、技術(shù)交流、商務(wù)合作、聯(lián)系博主
微信掃碼或搜索：z360901061
微信掃一掃加我為好友
QQ號(hào)聯(lián)系： 360901061
您的支持是博主寫(xiě)作最大的動(dòng)力，如果您喜歡我的文章，感覺(jué)我的文章對(duì)您有幫助，請(qǐng)用微信掃描下面二維碼支持博主2元、5元、10元、20元等您想捐的金額吧，狠狠點(diǎn)擊下面給點(diǎn)支持吧，站長(zhǎng)非常感激您！手機(jī)微信長(zhǎng)按不能支付解決辦法：請(qǐng)將微信支付二維碼保存到相冊(cè)，切換到微信，然后點(diǎn)擊微信右上角掃一掃功能，選擇支付二維碼完成支付。
【本文對(duì)您有幫助就好】元
