T-SQL編程與事務(wù)管理

Transact-SQL語(yǔ)言與事務(wù)管理目標(biāo)掌握如何定義變量并賦值掌握如何輸出顯示數(shù)據(jù)掌握IF、WHILE、CASE邏輯控制語(yǔ)句理解SQL中批處理的概念游標(biāo)的使用事務(wù)處理使用變量變量分為：局部變量：局部變量必須以標(biāo)記@作為前綴，如@age局部變量的使用也是先聲明，再賦值

全局變量：全局變量必須以標(biāo)記@@作為前綴，如@@version全局變量由系統(tǒng)定義和維護(hù)，我們只能讀取，不能修改全局變量的值局部變量例如：DECLARE@namevarchar(8)DECLARE@seatint聲明局部變量DECLARE@變量名數(shù)據(jù)類(lèi)型賦值

SET@變量名=值

SELECT@變量名=值例如：SET@name=‘張三’SELECT@name=stuNameFROMstuInfoWHEREstuNo=‘s25302’

必須確保篩選出的記錄只有1條或

局部變量例如問(wèn)題：編寫(xiě)T-SQL查找李文才的左右同桌？學(xué)員信息表分析：第一步，找出“李文才“的座位號(hào)；第二步，李文才的座位號(hào)加1或減1局部變量例如/*--查找李文才的信息--*/DECLARE@namevarchar(8)--學(xué)員姓名SET@name='李文才'--使用SET賦值SELECT*FROMstuInfoWHEREstuName=@name/*--查找李文才的左右同桌--*/DECLARE@seatint--座位號(hào)SELECT@seat=stuSeatFROMstuInfo

--使用SELECT賦值

WHEREstuName=@nameSELECT*FROMstuInfoWHERE(stuSeat=@seat+1)OR(stuSeat=@seat-1)GO演示：使用局部變量參考語(yǔ)句全局變量全局變量都使用兩個(gè)@標(biāo)志作為前綴

變量含義@@ERROR最后一個(gè)T-SQL錯(cuò)誤的錯(cuò)誤號(hào)@@IDENTITY最后一次插入的標(biāo)識(shí)值@@LANGUAGE當(dāng)前使用的語(yǔ)言的名稱(chēng)@@MAX_CONNECTIONS可以創(chuàng)建的同時(shí)連接的最大數(shù)目@@ROWCOUNT 受上一個(gè)SQL語(yǔ)句影響的行數(shù)@@SERVERNAME本地服務(wù)器的名稱(chēng)@@TRANSCOUNT 當(dāng)前連接打開(kāi)的事務(wù)數(shù)@@VERSION

SQLServer的版本信息全局變量例如print'SQLServer的版本'+@@VERSIONprint'效勞器的名稱(chēng):'+@@SERVERNAMEINSERTINTOstuInfo(stuName,stuNo,stuSex,stuAge)VALUES('武松','s25328','男','23')--如果大于0表示上一條語(yǔ)句執(zhí)行有錯(cuò)誤print'當(dāng)前錯(cuò)誤號(hào)'+convert(varchar(5),@@ERROR)print'剛剛報(bào)名的學(xué)員，座位號(hào)為:'+convert(varchar(5),@@IDENTITY)UPDATEstuinfoSETstuAge=85WHEREstuName='李文才'print'當(dāng)前錯(cuò)誤號(hào)'+convert(varchar(5),@@ERROR)GO演示：使用全局變量參考語(yǔ)句SQLServer的版本效勞器名稱(chēng)座位號(hào)〔自動(dòng)編號(hào)〕錯(cuò)誤號(hào)錯(cuò)誤號(hào)邏輯控制語(yǔ)句IF－ELSE語(yǔ)句SQL中的IF-ELSE語(yǔ)句IF〔條件〕BEGIN語(yǔ)句1語(yǔ)句2……ENDELSEBEGIN語(yǔ)句1；語(yǔ)句2；……ENDELSE是可選局部如果有多條語(yǔ)句，才需要BEGIN-END語(yǔ)句塊IF-ELSE例如問(wèn)題：統(tǒng)計(jì)并顯示本班筆試平均分，如果平均分在70以上，顯示“成績(jī)優(yōu)秀“，并顯示前三名學(xué)員的考試信息；如果在70以下，顯示“本班成績(jī)較差“，并顯示后三名學(xué)員的考試信息。學(xué)員成績(jī)表分析：第一步，統(tǒng)計(jì)平均成績(jī)存入臨時(shí)變量；第二步，用IF－ELSE判斷；IF-ELSE例如設(shè)置輸出結(jié)果的格式為了文本消息和輸出結(jié)果顯示在同一窗口，需要設(shè)置輸出結(jié)果的格式邏輯控制語(yǔ)句WHILE循環(huán)語(yǔ)句SQL中的WHILE語(yǔ)句WHILE〔條件〕BEGIN語(yǔ)句1語(yǔ)句2……BREAKENDBREAK表示退出循環(huán)如果有多條語(yǔ)句，才需要BEGIN-END語(yǔ)句塊WHILE例如問(wèn)題：

本次考試成績(jī)較差，假定要提分，確保每人筆試都通過(guò)。提分規(guī)那么很簡(jiǎn)單，先每人都加2分，看是否都通過(guò)，如果沒(méi)有全部通過(guò)，每人再加2分，再看是否都通過(guò)，如此反復(fù)提分，直到所有人都通過(guò)為止。學(xué)員成績(jī)表分析：

第一步，統(tǒng)計(jì)沒(méi)通過(guò)的人數(shù)；

第二步，如果有人沒(méi)通過(guò)，加分；

第三步，循環(huán)判斷。DECLARE@nintWHILE(1=1)--條件永遠(yuǎn)成立

BEGINSELECT@n=COUNT(*)FROMstuMarksWHEREwrittenExam<60--統(tǒng)計(jì)不及格人數(shù)IF(@n>0)UPDATEstuMarks--每人加2分SETwrittenExam=writtenExam+2ELSEBREAK--退出循環(huán)ENDprint'加分后的成績(jī)?nèi)缦拢?SELECT*FROMstuMarksWHILE例如參考語(yǔ)句邏輯控制語(yǔ)句CASE－END多分支語(yǔ)句CASE

WHEN條件1THEN結(jié)果1

WHEN條件2THEN結(jié)果2……

ELSE其他結(jié)果ENDCASE-END例如問(wèn)題：采用美國(guó)的ABCDE五級(jí)打分制來(lái)顯示筆試成績(jī)。A級(jí):90分以上B級(jí)：80－89分C級(jí):70－79分D級(jí)：60－69分E級(jí)：60分以下學(xué)員成績(jī)表print'ABCDE五級(jí)顯示成績(jī)?nèi)缦拢?SELECTstuNo,成績(jī)=CASE

WHENwrittenExam<60THEN'E'

WHENwrittenExamBETWEEN60AND69THEN'D'

WHENwrittenExamBETWEEN70AND79THEN'C'

WHENwrittenExamBETWEEN80AND89THEN'B'

ElSE'A'

ENDFROMstuMarksCASE－END例如參考語(yǔ)句CASE-END課堂練習(xí)課堂練習(xí)：

請(qǐng)根據(jù)平均分和下面的評(píng)分規(guī)那么，編寫(xiě)T-SQL語(yǔ)句查詢(xún)學(xué)員的成績(jī)，如上圖所示。優(yōu)：90分以上良：80－89分中：70－79分差：60－69分不及格：60分以下學(xué)員成績(jī)分析CASE-END練習(xí)答案USEstuDBGOSELECT考號(hào)=ExamNo,學(xué)號(hào)=stuNo,筆試=writtenExam,機(jī)試=labExam,平均分=(writtenExam+labExam)/2,等級(jí)=CASE

WHEN(writtenExam+labExam)/2<60THEN'不及格'

WHEN(writtenExam+labExam)/2BETWEEN60AND69THEN'差'

WHEN(writtenExam+labExam)/2BETWEEN70AND79THEN'中'

WHEN(writtenExam+labExam)/2BETWEEN80AND89THEN'良'

ElSE'優(yōu)'

ENDFROMstuMarks批處理語(yǔ)句批處理是包含一個(gè)或多個(gè)SQL語(yǔ)句的組，從應(yīng)用程序一次性地發(fā)送到SQLServer執(zhí)行SQLServer將批處理語(yǔ)句編譯成一個(gè)可執(zhí)行單元，此單元稱(chēng)為執(zhí)行方案。執(zhí)行方案中的語(yǔ)句每次執(zhí)行一條客戶(hù)端應(yīng)用程序SQLServer效勞器批處理語(yǔ)句：語(yǔ)句1語(yǔ)句2……GO批處理語(yǔ)句例如SELECT*FROMstuInfoSELECT*FROMstuMarksUPDATEstuMarksSETwrittenExam=writtenExam+2GOGO是批處理的標(biāo)志，表示SQLServer將這些T-SQL語(yǔ)句編譯為一個(gè)執(zhí)行單元，提高執(zhí)行效率一般是將一些邏輯相關(guān)的業(yè)務(wù)操作語(yǔ)句，放置在同一批中，這完全由業(yè)務(wù)需求和代碼編寫(xiě)者決定批處理語(yǔ)句例如--例如建表語(yǔ)句的末尾必須添加GOCREATETABLEstuInfo(....)GOSQLServer規(guī)定：如果是建庫(kù)、建表語(yǔ)句、以及我們后面學(xué)習(xí)的存儲(chǔ)過(guò)程和視圖等，那么必須在語(yǔ)句末尾添加GO批處理標(biāo)志課堂綜合練習(xí)課堂練習(xí)：那么根據(jù)如下規(guī)那么對(duì)機(jī)試成績(jī)進(jìn)行反復(fù)加分，直到平均分超過(guò)85分為止。請(qǐng)編寫(xiě)T-SQL語(yǔ)句實(shí)現(xiàn)。90分以上：不加分80－89分：加1分70－79分：加2分60－69分：加3分60分以下：加5分加分前加分后課堂綜合練習(xí)答案SELECT*FROMstuMarks--原始成績(jī)DECLARE@labAvgINTWHILE(1=1)

BEGINUPDATEstuMarksSETlabExam=

CASEWHENlabExam<60THENlabExam+5WHENlabExambetween60AND69THENlabExam+3WHENlabExambetween70AND79THENlabExam+2WHENlabExambetween80AND89THENlabExam+1ELSElabExam

ENDSELECT@labAvg=AVG(labExam)FROMstuMarks

IF@labAvg>=85

BREAKENDSELECT*FROMstuMarks--加分后的成績(jī)聲明變量，用戶(hù)臨時(shí)存放平均分循環(huán)加分根據(jù)機(jī)試成績(jī)酌情加分，整個(gè)是一個(gè)UPDATE語(yǔ)句獲取目前的平均分，判斷是否還繼續(xù)加分游標(biāo)2-1游標(biāo)游標(biāo)是一種能從包括多條數(shù)據(jù)記錄的結(jié)果集中每次提取一條記錄的機(jī)制。SQLServer2005中三種游標(biāo)實(shí)現(xiàn)：Transact-SQL游標(biāo)API效勞器游標(biāo)只進(jìn)游標(biāo)靜態(tài)游標(biāo)鍵集驅(qū)動(dòng)游標(biāo)動(dòng)態(tài)游標(biāo)客戶(hù)游標(biāo)游標(biāo)2-2游標(biāo)的特點(diǎn)是：檢索得到的數(shù)據(jù)集更加靈活可有針對(duì)性的對(duì)數(shù)據(jù)進(jìn)行操作擁有對(duì)數(shù)據(jù)進(jìn)行刪除和更新的能力為何使用游標(biāo)：游標(biāo)提供了一種比較好的解決方案，可以將批操作變成行操作。游標(biāo)的使用步驟１．定義游標(biāo)２．翻開(kāi)游標(biāo)３．使用游標(biāo)提取數(shù)據(jù)４．關(guān)閉游標(biāo)５．刪除游標(biāo)定義游標(biāo)DECLARECURSOR語(yǔ)句定義Transact-SQL效勞器游標(biāo)的特性，其語(yǔ)法格式為：DECLAREcursor_name[INSENSITIVE][SCROLL]CURSORFORselect_statement[FOR{READONLY|UPDATE[OFcolumn_name[,...n]]}]翻開(kāi)游標(biāo)翻開(kāi)游標(biāo)的根本格式OPEN{{[GLOBAL]cursor_name}|cursor_variable_name}使用游標(biāo)提取數(shù)據(jù)使用游標(biāo)提取數(shù)據(jù)FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM]{{[GLOBAL]cursor_name}|@cursor_variable_name}[INTO@variable_name[,...n]]關(guān)閉和刪除游標(biāo)關(guān)閉游標(biāo)刪除游標(biāo)CLOSE{{[GLOBAL]cursor_name}|cursor_variable_name}Deallocate[global]游標(biāo)名@@FETCH_STATUS@@FETCH_STATUS：返回被FETCH語(yǔ)句執(zhí)行的最后游標(biāo)的狀態(tài)，而不是任何當(dāng)前被連接翻開(kāi)的游標(biāo)的狀態(tài)。返回值描述0FETCH語(yǔ)句成功。-1FETCH語(yǔ)句失敗或此行不在結(jié)果集中。-2被提取的行不存在。使用游標(biāo)舉例使用只讀游標(biāo)，查詢(xún)并顯示輸出bbsUsers表中有效用戶(hù)的信息，并打印出“uid、uname的值”。

/*定義只讀游標(biāo)*/DECLAREuser_csrCURSORREAD_ONLYFORSELECTUID,UnameFROMbbsUsersWHEREUstate=0ORDERBYUID/*定義變量*/DECLARE@UIDint,@Unamevarchar(15)定義游標(biāo)使用游標(biāo)舉例-續(xù)/*翻開(kāi)游標(biāo)*/OPENuser_csr/*執(zhí)行第一次數(shù)據(jù)讀取操作*/FETCHNEXTFROMuser_csrINTO@UID,@Uname/*循環(huán)游標(biāo)讀取操作*/WHILE@@FETCH_STATUS=0BEGIN PRINT'uid:'+convert(varchar(6),@UID)+'uname:'+@Uname FETCHNEXTFROMuser_csrINTO@UID,@UnameEND/*關(guān)閉游標(biāo)*/CLOSEuser_csr/*釋放游標(biāo)*/DEALLOCATEuser_csr翻開(kāi)游標(biāo)關(guān)閉游標(biāo)刪除游標(biāo)使用游標(biāo)@@FETCH_STATUS事務(wù)管理銀行轉(zhuǎn)帳例如，銀行轉(zhuǎn)帳問(wèn)題：假定資金從帳戶(hù)A轉(zhuǎn)到帳戶(hù)B，至少需要兩步：帳戶(hù)A的資金減少然后帳戶(hù)B的資金相應(yīng)增加帳戶(hù)A帳戶(hù)B假定張三的帳戶(hù)直接轉(zhuǎn)帳1000元到李四的帳戶(hù)事務(wù)管理CREATETABLEbank(customerNameCHAR(10),--顧客姓名currentMoneyMONEY--當(dāng)前余額)GOALTERTABLEbank

ADDCONSTRAINTCK_currentMoneyCHECK(currentMoney>=1)GOINSERTINTObank(customerName,currentMoney)VALUES('張三',1000)INSERTINTObank(customerName,currentMoney)VALUES('李四',1)創(chuàng)立帳戶(hù)表，存放用戶(hù)的帳戶(hù)信息添加約束：根據(jù)銀行規(guī)定，帳戶(hù)余額不能少于1元，否那么視為銷(xiāo)戶(hù)張三開(kāi)戶(hù)，開(kāi)戶(hù)金額為1000元；李四開(kāi)戶(hù)，開(kāi)戶(hù)金額1元事務(wù)管理目前兩個(gè)帳戶(hù)的余額總和為：1000+1=1001元

事務(wù)管理模擬實(shí)現(xiàn)轉(zhuǎn)帳：

從張三的帳戶(hù)轉(zhuǎn)帳1000元到李四的帳戶(hù)/*--轉(zhuǎn)帳測(cè)試：張三轉(zhuǎn)賬1000元給李四--*/--我們可能會(huì)這樣這樣編寫(xiě)語(yǔ)句--張三的帳戶(hù)少1000元，李四的帳戶(hù)多1000元UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName='張三'UPDATEbankSETcurrentMoney=currentMoney+1000WHEREcustomerName='李四'GO--再次查看轉(zhuǎn)帳后的結(jié)果。SELECT*FROMbankGO請(qǐng)問(wèn)：

執(zhí)行轉(zhuǎn)帳語(yǔ)句后，張三、李四的帳戶(hù)余額為多少？張三的帳戶(hù)沒(méi)有減少

但李四的帳戶(hù)卻多了1000元1000＋1001＝2001元

總額多出了1000元！事務(wù)管理--張三的帳戶(hù)減少1000元，李四的帳戶(hù)增加1000元UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName='張三'UPDATEbankSETcurrentMoney=currentMoney+1000WHEREcustomerName='李四'GO錯(cuò)誤原因分析：UPDATE語(yǔ)句違反約束：

余額>=1元執(zhí)行失敗，所以張三還是1000元繼續(xù)往下執(zhí)行：執(zhí)行成功，所以李四變?yōu)?001元如何解決呢？使用事務(wù)什么是事務(wù)事務(wù)(TRANSACTION)是作為單個(gè)邏輯工作單元執(zhí)行的一系列操作這些操作作為一個(gè)整體一起向系統(tǒng)提交，要么都執(zhí)行、要么都不執(zhí)行事務(wù)是一個(gè)不可分割的工作邏輯單元轉(zhuǎn)帳過(guò)程就是一個(gè)事務(wù)。它需要兩條UPDATE語(yǔ)句來(lái)完成，這兩條語(yǔ)句是一個(gè)整體，如果其中任一條出現(xiàn)錯(cuò)誤，那么整個(gè)轉(zhuǎn)帳業(yè)務(wù)也應(yīng)取消，兩個(gè)帳戶(hù)中的余額應(yīng)恢復(fù)到原來(lái)的數(shù)據(jù)，從而確保轉(zhuǎn)帳前和轉(zhuǎn)帳后的余額不變，即都是1001元。事務(wù)的特性事務(wù)必須具備以下四個(gè)屬性，簡(jiǎn)稱(chēng)ACID屬性：原子性〔Atomicity〕：事務(wù)是一個(gè)完整的操作。事務(wù)的各步操作是不可分的〔原子的〕；要么都執(zhí)行，要么都不執(zhí)行一致性〔Consistency〕：當(dāng)事務(wù)完成時(shí)，數(shù)據(jù)必須處于一致?tīng)顟B(tài)隔離性〔Isolation〕：對(duì)數(shù)據(jù)進(jìn)行修改的所有并發(fā)事務(wù)是彼此隔離的，這說(shuō)明事務(wù)必須是獨(dú)立的，它不應(yīng)以任何方式依賴(lài)于或影響其他事務(wù)永久性〔Durability〕：事務(wù)完成后，它對(duì)數(shù)據(jù)庫(kù)的修改被永久保持，事務(wù)日志能夠保持事務(wù)的永久性如何創(chuàng)立事務(wù)T-SQL使用以下語(yǔ)句來(lái)管理事務(wù)：開(kāi)始事務(wù)：BEGINTRANSACTION提交事務(wù)：COMMITTRANSACTION回滾〔撤銷(xiāo)〕事務(wù)：ROLLBACKTRANSACTION保存回滾點(diǎn)SAVETRANSACTION一旦事務(wù)提交或回滾，那么事務(wù)結(jié)束。判斷某條語(yǔ)句執(zhí)行是否出錯(cuò)：使用全局變量@@ERROR；@@ERROR只能判斷當(dāng)前一條T-SQL語(yǔ)句執(zhí)行是否有錯(cuò)，為了判斷事務(wù)中所有T-SQL語(yǔ)句是否有錯(cuò)，我們需要對(duì)錯(cuò)誤進(jìn)行累計(jì)；如：SET@errorSum=@errorSum+@@error如何創(chuàng)立事務(wù)了解事務(wù)的分類(lèi)：顯示事務(wù)：用BEGINTRANSACTION明確指定事務(wù)的開(kāi)始，這是最常用的事務(wù)類(lèi)型隱性事務(wù)：通過(guò)設(shè)置SETIMPLICIT_TRANSACTIONSON語(yǔ)句，將隱性事務(wù)模式設(shè)置為翻開(kāi)，下一個(gè)語(yǔ)句自動(dòng)啟動(dòng)一個(gè)新事務(wù)。當(dāng)該事務(wù)完成時(shí)，再下一個(gè)T-SQL語(yǔ)句又將啟動(dòng)一個(gè)新事務(wù)自動(dòng)提交事務(wù)：這是SQLServer的默認(rèn)模式，它將每條單獨(dú)的T-SQL語(yǔ)句視為一個(gè)事務(wù)，如果成功執(zhí)行，那么自動(dòng)提交；如果錯(cuò)誤，那么自動(dòng)回滾……關(guān)鍵語(yǔ)句