第08章Transact-SQL語言編程

第8章T-SQL語言編程8.1批處理、腳本和注釋1、批處理

批處理就是一個或多個T-SQL語句的集合，從應用程序一次性發(fā)送到SQLserver并有SQLserver編譯成一個可執(zhí)行單元，此單元稱為執(zhí)行計劃。執(zhí)行計劃中的語句每次執(zhí)行一條。Go語句是批處理的結(jié)束標記。執(zhí)行錯誤時1）停止執(zhí)行批處理中當前語句及其之后的語句2）僅停止執(zhí)行當前語句，其他語句所有語句，錯誤前的執(zhí)行語句不受影響。3）批處理位于事務中并且錯誤導致事務回滾。（錯誤之前執(zhí)行的未提交的數(shù)據(jù)修改都將回滾）2.腳本腳本是存儲在文件中的一系列SQL語句，即一系列按順序提交的批處理。T-SQL腳本中包含一個或多個批處理。

查詢設(shè)計器是建立、編輯和使用腳本的一個最好平臺。3.注釋1）格式格式一：/*注釋文本*/格式二：--注釋文本2）功能多行的注釋必須用/*和*/指明。用于多行注釋的樣式規(guī)則是，第一行用/*開始，并且用*/結(jié)束注釋。--注釋可插入到單獨行中或嵌套（只限--）在命令行的末端，用--插入的注釋由換行字符分界。注釋沒有最大長度限制。服務器將不運行注釋文本。8.1批處理、腳本和注釋8.2.1局部變量1.聲明局部變量局部變量必須先定義，后使用。語法格式：declare@局部變量數(shù)據(jù)類型[,...n]eg：declare@salarymoneydeclare@namechar(5),@telnvarchar(13)注意：局部變量的數(shù)據(jù)類型不能設(shè)置為text、ntext或image。2.局部變量賦值聲明一個局部變量之后，初始值是null，賦值語法如下：set@局部變量=表達式[,...n]select@局部變量=表達式[,...n](返回多個值時，取最后一個值)8.2變量8.2.1局部變量3.局部變量的作用域從聲明它們的地方開始，一直到聲明它們的批處理或存儲過程的結(jié)尾處Eg：uselibrarygoDeclare@readersumint–-聲明局部變量Select@readersum=count(*)fromreader--查詢結(jié)果存儲到局部變量Print‘讀者信息表中記錄了’+convert(varchar(2),@readersum)+’讀者名’--convert()為轉(zhuǎn)換函數(shù)goPrint‘讀者信息表中讀者數(shù)量為：’+convert(varchar(2),@readersum)go8.2變量1.格式PRINT文本字符串|@字符數(shù)據(jù)類型變量|@@返回字符串結(jié)果的函數(shù)|字符串表達式2.功能將用戶定義的消息返回客戶端。必須是char或varchar，或者能夠隱式轉(zhuǎn)換為這些數(shù)據(jù)類型。若要打印用戶定義的錯誤信息（該消息中包含可由@@ERROR返回的錯誤號），請使用RAISERROR而不要使用PRINT。Print函數(shù)8.2.2全局變量全局變量是SQLserver系統(tǒng)提供并賦值的變量。它是一組特殊的函數(shù)，名稱以“＠＠”開頭，沒有參數(shù)。【例子】Print‘目前使用SQLserver的版本信息如下：’Print@@version--顯示版本信息Print‘目前使用SQLserver服務器名稱為：’+@@servername--顯示服務器名稱Print‘目前所有的服務器為：’+@@servernameGo8.2變量8.3.1BEGIN...END語句1．格式BEGIN{Transact-SQL語句

|語句塊}END2．功能BEGIN...END語句將多個SQL語句組合成一組語句塊，并將些語句塊視為一個單元。BEGIN...END語句塊允許嵌套。8.3流程控制語句8.3.2IF...ELSE語句1．格式IF邏輯表達式

〈SQL語句1|語句塊1〉[ELSE〈SQL語句2|語句塊2〉]2．功能IF...ELSE語句是雙分支條件判斷語句，根據(jù)某個條件的成立與否，來決定執(zhí)行哪組語句。8.3流程控制語句8.3.2IF...ELSE語句【例子】UselibraryIf(selectAVG(價格)frombookwhere類編號=13)<$20BeginPrint‘以下是理學類別圖書平均價格低于20的圖書’Selectsubstring(圖書名,1,35)FrombookWhere類編號=13EndElseif(selectAVG(價格)frombookwhere類編號=13)<$20BeginPrint‘以下是理學類別圖書平均價格高于20的圖書’Selectsubstring(圖書名,1,35)FrombookWhere類編號=13End

8.3流程控制語句8.3.3CASE語句1．格式格式1：簡單CASE表達式CASEInput_表達式

WHENwhen_表達式1THENresult_表達式1

[WHENwhen_表達式2THENresult_表達式2][...n][ELSEresult_表達式n]END例子：uselibraryselect*case種類編號when1then‘一類讀者’when2then‘二類讀者’endfromreader8.3流程控制語句8.3.3CASE語句格式2：搜索CASE表達式CASEWHEN邏輯表達式1THENresult_表達式1[WHEN邏輯表達式2THENresult_表達式2][...n][ELSEresult_表達式n]END例子：uselibraryselect*casewhen(價格>=40)then‘高檔圖書’when(價格>=10)‘低檔圖書’else‘小圖書’endfromreader8.3流程控制語句8.3.4waitfor語句waitfor語句可以暫停執(zhí)行程序一段時間后再繼續(xù)執(zhí)行，也可以暫停執(zhí)行程序到所指定的時間后再繼續(xù)執(zhí)行。語法格式：waitfor

delay‘時間’/time‘時間’delay：指定一段時間間隔過去后執(zhí)行一個操作time：從某個時刻開始執(zhí)行一個操作例子:selcet‘執(zhí)行waitfor語句之前，秒數(shù)為：’=datepart（second,getdate()）,‘執(zhí)行waitfor語句之前，時間為：’=getdate()GoWaitfordelay’00:00:20’--延時20秒selcet‘執(zhí)行waitfor語句之后，秒數(shù)為：’=datepart（second,getdate()）,‘執(zhí)行waitfor語句之后，時間為：’=getdate()8.3流程控制語句8.3.3while語句1．格式WHILE邏輯表達式{SQL語句|語句塊}[BREAK]{SQL語句|語句塊}[CONTINUE]{SQL語句|語句塊}end2．功能1）當邏輯表達式為真時，重復執(zhí)行SQL語句或語句塊，直到邏輯表達式為假?？梢允褂肂REAK和CONTINUE語句改變WHILE循環(huán)的執(zhí)行。2）END關(guān)鍵字為循環(huán)結(jié)束標記。BREAK語句可以完全退出本層WHILE循環(huán)，執(zhí)行END后面的語句。3）CONTINUE語句回到循環(huán)的第一行命令，重新開始循環(huán)8.3流程控制語句8.3.3while語句Eg：使用while語句，求出1+2+...+100的值

Declare@iint,@sintset@i=1set@s=0--定義兩個變量i,swhile@i<=100--當i的值大于100時，結(jié)束循環(huán)beginset@s=@s+@iset@i=@i+1continueendprint’1+2+...+100的值是：’+convert（varchar，@s）8.3流程控制語句8.4.1字符函數(shù)１）SUBSTRING函數(shù)格式：SUBSTRING(<字符表達式>,<m>[,<n>])功能：從字符表達式中的第m個字符開始截取n個字符，形成一個新字符串，m,n都是數(shù)值表達式。2）LTRIM函數(shù)格式：LTRIM(<字符表達式>)功能：刪除字符串起始空格函數(shù)，返回varchar類型數(shù)據(jù)3）RTRIM函數(shù)格式：RTRIM(<字符表達式>)功能：刪除字符串尾隨空格函數(shù)，返回varchar類型數(shù)據(jù)。8.4常用系統(tǒng)函數(shù)4）RIGHT函數(shù)格式：RIGHT(<字符表達式>,<數(shù)據(jù)表達式>)功能：返回字符串中從右邊開始指定個數(shù)的字符，返回varchar類型數(shù)據(jù)。5）LEFT函數(shù)格式：LEFT(<字符表達式>,<數(shù)據(jù)表達式>)功能：返回字符串中從左邊開始指定個數(shù)的字符，返回varchar類型數(shù)據(jù)。6）UPPER函數(shù)格式：UPPER(<字符表達式>)功能：將小寫字符數(shù)據(jù)轉(zhuǎn)換為大寫的字符表達式，返回varchar類型數(shù)據(jù)。8.4.1字符函數(shù)7）LOWER函數(shù)格式：LOWER(<字符表達式>)功能：將大寫字符數(shù)據(jù)轉(zhuǎn)換為小寫的字符表達式，返回varchar類型數(shù)據(jù)。8）REVERSE函數(shù)格式：REVERSE(<字符表達式>)功能：返回字符表達式的反轉(zhuǎn)。返回varchar類型數(shù)據(jù)。9）SPACE函數(shù)格式：SPACE(<整數(shù)表達式>)功能：返回由重復的空格組成的字符串。整數(shù)表達式的值表示空格個數(shù)。返回char類型數(shù)據(jù)。8.4.1字符函數(shù)10）STUFF函數(shù)格式：STUFF(字符表達式1,m,n,字符表達式2)功能：刪除指定長度的字符并在指定的起始點插入另一組字符。m,n是整數(shù)，m指定刪除和插入的開始位置,n指定要刪除的字符數(shù),最多刪除到最后一個字符。如果m或n是負數(shù)，則返回空字符串。如果m比字符表達式1長，則返回空字符串。返回char類型數(shù)據(jù)。11）CHARINDEX函數(shù)格式：CHARINDEX(表達式1,表達式2[,m])功能：在表達式2的第m個字符開始查找表達式1起始字符位置。m是整數(shù)表達式，如果m是負數(shù)或缺省，則將從表達式2的起始位置開始搜索。返回int類型數(shù)據(jù)。8.4.1字符函數(shù)12）LEN函數(shù)格式：LEN(字符表達式)

功能：返回給定字符串表達式的字符個數(shù)，不包含尾隨空格。13）ASCII函數(shù)格式：ASCII(字符表達式)

功能：返回給定字符串表達式的最左端字符的ASCII碼值。返回整型值。14）CHAR函數(shù)格式：CHAR(整數(shù)表達式)

功能：用于將ASCII碼轉(zhuǎn)換為字符，整數(shù)表達式的取值范圍為0到255之間的整數(shù)，返回字符型數(shù)據(jù)值。8.4.1字符函數(shù)Eg：求字符串的長度selectlen(‘這是一個例子’)declare@mystr1nchar(20),@mystr2nchar(15)declare@mystr3nchar(20)set@mystr1=‘SQLServer2005’set@mystr2=‘‘set@mystr3=‘程序設(shè)計’selectsubstring(@mystr1,3,8)selectlen(@mystr2)selectlen(@mystr3)8.4.1字符函數(shù)1）ABS函數(shù)格式：ABS(數(shù)字表達式)

功能：返回給定數(shù)字表達式的絕對值。2）EXP函數(shù)格式：EXP(數(shù)字表達式)

功能：返回給定數(shù)字表達式的指數(shù)值。參數(shù)數(shù)字表達式是float類型的表達式。返回類型為float。3）SQRT函數(shù)格式：SQRT(數(shù)字表達式)功能：返回給定數(shù)字表達式的平方根。參數(shù)數(shù)字表達式是float類型的表達式。返回類型為float。8.4.2數(shù)學函數(shù)4）ROUND函數(shù)格式：ROUND(數(shù)字表達式，m)

功能：返回返回數(shù)字表達式并四舍五入為指定的長度或精度。5）RAND函數(shù)格式：RAND([seed])

功能：返回0到1之間的隨機float值。參數(shù)seed為整型表達式。8.4.2數(shù)學函數(shù)1）DATEADD函數(shù)格式：DATEADD(日期參數(shù),數(shù)字,日期)功能：在向指定日期加上一段時間的基礎(chǔ)上，返回新的datetime值。日期參數(shù)規(guī)定了新值的類型。參數(shù)有：Year、Month、Day、Week、Hour2）GETDATE函數(shù)格式：GETDATE()8.4.3日期和時間函數(shù)3）DAY函數(shù)格式：DAY(日期)

功能：返回代表指定日期的“日”部分的整數(shù)。返回類型為int。4）YEAR函數(shù)格式：YEAR(日期)功能：返回表示指定日期中的年份的整數(shù)。返回類型為int。

5）MONTH函數(shù)格式：MONTH(日期)功能：返回表示指定日期中的月份的整數(shù)。返回類型為int。8.4.3日期和時間函數(shù)【例子】Print‘當前日期為：’Selectgetdate()Print‘當前年份為：’Selectyear(getdate())Print‘日期2010-10-01加上20天后所得日期為：’Selectdateadd(dd,20,’2010-10-01’)Print‘某人生日為1982-10-20，則他的年齡為：’Selectdatediff(yy,’1982-10-20’,getdate())8.4.3日期和時間函數(shù)1）CAST函數(shù)格式：CAST(表達式AS數(shù)據(jù)類型)功能：將指定的表達式轉(zhuǎn)換成對應的數(shù)據(jù)類型。2）CONVERT函數(shù)格式：CONVERT(數(shù)據(jù)類型[(長度)],表達式[,樣式])功能：樣式是指日期格式樣式。5．系統(tǒng)函數(shù)函數(shù)DB_NAME()的功能是返回數(shù)據(jù)庫的名稱。函數(shù)HOST_NAME()的功能是返回服務器端計算機的名稱。函數(shù)HOST_ID()的功能是返回服務器端計算機的ID號。函數(shù)USER_NAME()的功能是返回用戶的數(shù)據(jù)庫用戶名。8.4.3數(shù)據(jù)轉(zhuǎn)換函數(shù)1．格式CREATEFUNCTION[擁有者.]函數(shù)名([{@形參名1[AS]數(shù)據(jù)類型1[=默認值]}[,…n]])RETURNS返回值的類型[AS]BEGIN函數(shù)體RETURN標量表達式END8.5.1標量函數(shù)8.5用戶自定義函數(shù)Eg:uselibraryGoCreatefunctionfn_dateformat(@indatedatetime,@separatorchar(1))Returnsnchar(20)AsBeginReturnConvert(nvarchar(20),datepart(mm,@indate))+@separator+Convert(nvarchar(20),datepart(dd,@indate))+@separator8.5.2標量函數(shù)例子8.5用戶自定義函數(shù)+Convert(nvarchar(20),datepart(yy,@indate))EndGoSelectdbo.fn_dateformat(getdate(),‘：’)go1．格式

DROPFUNCTION{[擁有者.]函數(shù)名}[,…n]2．功能刪除指定用戶定義的函數(shù)名稱。3.【例】刪除自定義函數(shù)course_grade。DROPFUNCTIONcourse_grade8.5.3刪除用戶定義函數(shù)8.5用戶自定義函數(shù)1．格式DECLARE游標名[insensitive][SCROLL]CURSORFORselect_statement[FOR{readonly|UPDATE[OF列名[,...n]]}]2．說明1）insensitive：定義游標時將在tempdb數(shù)據(jù)庫中創(chuàng)建一個臨時表，用于存儲該游標使用的數(shù)據(jù)，且該游標不可修改。如果省略insensitive關(guān)鍵字，任何用戶對基表提交的刪除和更新都反映在后面的提取中。2）SCROLL：使用時所有的提取選項都可使用；未指定SCROLL關(guān)鍵字，則fetchnext是唯一支持的提取選項。3）select_statement：用來定義游標的結(jié)果集，其中不允許使用compute、computeby和into關(guān)鍵字。4）readonly：使用時不能通過該游標更新數(shù)據(jù)。5）update：用于定義游標內(nèi)可更新的字段，如果未指定更新的列，則可以更新所有列。8.6游標的使用8.6.1聲明游標Eg：USELibraryDECLAREbooks_vursorCURSORFORSELECT*FROMBooks--聲明游標OPENbooks_vursor--打開游標FETCHNEXTFROMbooks_vursor--從游標中提取一行記錄CLOSEbooks_vursor--關(guān)閉游標DEALLOCATEbooks_vursor--刪除游標8.6游標的使用8.6.1聲明游標8.6.2

打開游標1．格式OPEN{{[GLOBAL]游標名}|游標變量名}2．功能打開指定游標。聲明游標后，要從游標中取出數(shù)據(jù)，必須先打游標GLOBAL：說明打開的是全局游標。否則說明是局部游標。DECLAREbooks_cursorCURSORKeysetFORSELECT*FROMBooks--聲明游標OPENbooks_cursor--打開游標IF@@ERROR=0BEGINPRINT'游標打開成功'IF@@CURSOR_ROWS>0PRINT'游標結(jié)果集內(nèi)記錄數(shù)為：'+CONVERT(varchar(3),@@CURSOR_ROWS)ENDCLOSEbooks_cursor--關(guān)閉游標DEALLOCATEbooks_cursor--釋放游標1．格式FETCH[[NEXT|PRIOR|FIRST|LAST|ABSOLUTE{n|@nvar}|RELATIVE{n|@nvar}]FROM[GLOBAL]游標名[INTO@變量名[,...n]]2．功能NEXT：返回當前行的下一行PRIOR：返回當前行的前一行FIRST：返回游標中的第一行并將其作為當前行LAST：返回游標中的最后一行并將其作為當前行。ABSOLUTE{n|@nvar}：如果為正數(shù)，返回從游標頭開始的第n行記錄；負數(shù)則返回游標尾之前的第N行記錄；如果為0，沒有行返回。N必須是整數(shù)，@nvar必須為smallint、tinyint或int數(shù)據(jù)類型。RELATIVE{n|@nvar}：如果為正數(shù)，返回當前行之后的第n行記錄；負數(shù)則返回當前行之前的第N行記錄；如果為0，返回當前行記錄。Into：將提取操作的字段數(shù)據(jù)放到局部變量中。8.6.3從游標中獲取數(shù)據(jù)Eg：使用游標查看數(shù)據(jù)庫library中的訂單信息表readers中的記錄。USELibraryGODECLAREreaders_cursorCURSORFORSELECT*FROMReaders--聲明游標OPENreaders_cursorFETCHNEXTFROMreaders_cursor--執(zhí)行第一次提取，得到結(jié)果集中的首條記錄WHILE@@FETCH_STATUS=0--檢測全局變量@@FETCH_STATUS，如果仍有記錄，則繼續(xù)循環(huán)BEGINFETCHNEXTFROMreaders_cursorENDCLOSEreaders_cursorDEALLOCATEreaders_cursorGO8.6.3從游標中獲取數(shù)據(jù)1）格式CLOSE{{[GLOBAL]游標名}|@游標變量}2）功能關(guān)閉游標。CLOSE使得數(shù)據(jù)結(jié)構(gòu)可以重新打開，但不允許提取和定位更新，直到游標重新打開為止。2.釋放游標格式：DEALLOCATE{{[GLOBAL]游標名}|@游標變量}【例】關(guān)閉和釋放游標stud_cursor。CLOSEstud_cursorDEALLOCATEstud_cursor1.關(guān)閉游標8.6.4關(guān)閉和釋放游標

事務是指一個單元工作，該事物可能包括一條語句，也可能包括一百條語句，而這些語句的所有操作，要么都完成，要么都取消。事務必須具備4個ACID屬性：1.原子性(atomicity)：事務必須執(zhí)行一個完整的工作2.一致性(consistency)：當事務完成時，必須使所有數(shù)據(jù)都

具有一致的狀態(tài)。3.獨立性(isolation)：并行事務的修改必須與其他并行事務

的修改相互獨立。4.持久性(durability)：當一個事務完成之后，其影響永久性

地存在于系統(tǒng)中，也就是這種修改寫到數(shù)據(jù)庫中。8.7.1事務的概念8.7事務與鎖

事務在工作過程中不出現(xiàn)異常，那么將執(zhí)行提交；反之，則執(zhí)行回滾。1.事務處理語句

1.1定義事務的開始1)格式BEGINTRANSACTION[事務的名稱@變量][WITHMARK[‘描述標記的字符串’]]]2)功能定義顯式事務的開始，使全局變量@@trancount的值加1。[WITHMARK[‘描述標記的字符串’]]:在日志中標記事務。執(zhí)行每個事務,根據(jù)當前事務隔離級別的設(shè)置情況，鎖定資源，直到事務結(jié)束