T-SQL編程與事務管理

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

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

SET@變量名=值

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

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

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

--使用SELECT賦值

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

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

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

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

第一步，統(tǒng)計沒通過的人數(shù)；

第二步，如果有人沒通過，加分；

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

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

WHEN條件1THEN結果1

WHEN條件2THEN結果2……

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

WHENwrittenExam<60THEN'E'

WHENwrittenExamBETWEEN60AND69THEN'D'

WHENwrittenExamBETWEEN70AND79THEN'C'

WHENwrittenExamBETWEEN80AND89THEN'B'

ElSE'A'

ENDFROMstuMarksCASE－END例如參考語句CASE-END課堂練習課堂練習：

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

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

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

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

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

ElSE'優(yōu)'

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

BEGINUPDATEstuMarksSETlabExam=

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

ENDSELECT@labAvg=AVG(labExam)FROMstuMarks

IF@labAvg>=85

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

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

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

事務管理模擬實現(xiàn)轉帳：

從張三的帳戶轉帳1000元到李四的帳戶/*--轉帳測試：張三轉賬1000元給李四--*/--我們可能會這樣這樣編寫語句--張三的帳戶少1000元，李四的帳戶多1000元UPDATEbankSETcurrentMoney=currentMoney-1000WHEREcustomerName='張三'UPDATEbankSETcurrentMoney=currentMoney+1000WHEREcustomerName='李四'GO--再次查看轉帳后的結果。SELECT*FROMbankGO請問：

執(zhí)行轉帳語句后，張三、李四的帳戶余額為多少？張三的帳戶沒有減少

但李四的帳戶卻多了1000元1000＋1001＝2001元

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

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