SQL Server教程（第6版）(2008版) 課件 第6、7章 T-SQL語言；存儲(chǔ)過程、用戶定義函數(shù)和觸發(fā)器

第6章T-SQL語言——T-SQL語言簡介T-SQL語言簡介T-SQL語言由以下幾部分組成。（1）數(shù)據(jù)定義語言（DataDefinitionLanauage，簡稱DDL）。DDL用于執(zhí)行數(shù)據(jù)庫的任務(wù)，對數(shù)據(jù)庫及數(shù)據(jù)庫中的各種對象進(jìn)行創(chuàng)建、刪除、修改等操作。如前所述，數(shù)據(jù)庫對象主要包括表、默認(rèn)約束、規(guī)則、視圖、觸發(fā)器和存儲(chǔ)過程。DDL包括的主要語句及功能如表。語

句功

能CREATE創(chuàng)建數(shù)據(jù)庫或數(shù)據(jù)庫對象ALTER對數(shù)據(jù)庫或數(shù)據(jù)庫對象進(jìn)行修改DROP刪除數(shù)據(jù)庫或數(shù)據(jù)庫對象（2）數(shù)據(jù)操縱語言（DataManipulationLanguage，簡稱DML）。DML用于操縱數(shù)據(jù)庫中的各種對象，檢索和修改數(shù)據(jù)。DML包括的主要語句及功能如表。語

句功

能SELECT從表或視圖中檢索數(shù)據(jù)INSERT將數(shù)據(jù)插入到表或視圖中UPDATE修改表或視圖中的數(shù)據(jù)DELETE從表或視圖中刪除數(shù)據(jù)T-SQL語言簡介（3）數(shù)據(jù)控制語言（DataManagementLanguage，簡稱DCL）。DCL用于安全管理，確定哪些用戶可以查看或修改數(shù)據(jù)庫中的數(shù)據(jù)。DCL包括的主要語句及功能如表。語

句功

能GRANT授予權(quán)限REVOKE收回權(quán)限D(zhuǎn)ENY收回權(quán)限，并禁止從其他角色繼承許可權(quán)限（4）T-SQL增加的語言元素。這部分不是ANSISQL所包含的內(nèi)容，而是Microsoft公司為了用戶編程的方便而增加的語言元素，包括變量、運(yùn)算符、流程控制語句、函數(shù)等。這些T-SQL語句都可以在查詢分析器窗格中交互執(zhí)行。第6章T-SQL語言——常量、變量與數(shù)據(jù)類型01常

量字符串常量整型常量實(shí)型常量日期時(shí)間常量貨幣常量唯一標(biāo)識(shí)常量常

量1．字符串常量字符串常量分為普通字符串常量和Unicode字符串常量。（1）普通字符串常量。它是用單引號(hào)括起來，由ASCII字符和中文字符構(gòu)成的符號(hào)串。例如：'你好！''It''stimeforclass.'說明：如果單引號(hào)中的字符串包含引號(hào)，可以使用兩個(gè)單引號(hào)來表示嵌入的單引號(hào)。（2）Unicode字符串常量。用一個(gè)N標(biāo)識(shí)符（N代表SQL-92標(biāo)準(zhǔn)中的國際語言）引導(dǎo)。例如：N'你好！'N'It''stimeforclass.'Unicode數(shù)據(jù)中的每個(gè)字符用兩個(gè)字節(jié)存儲(chǔ)，而每個(gè)ASCII字符用一個(gè)字節(jié)存儲(chǔ)。常

量2．整型常量按照整型常量的不同表示方式，又分為二進(jìn)制整型常量、十六進(jìn)制整型常量和十進(jìn)制整型常量。（1）十進(jìn)制整型常量即不帶小數(shù)點(diǎn)的十進(jìn)制數(shù)。例如：18942+145345234-2147483648（2）前輟0x為十六進(jìn)制整型常量。例如：0xEBF0x69048AEFDD010E0x /*空十六進(jìn)制常量*/（3）二進(jìn)制整型常量為數(shù)字0或1。如果使用一個(gè)大于1的數(shù)字，它將被轉(zhuǎn)換為1。常

量3．實(shí)型常量實(shí)型常量有定點(diǎn)和浮點(diǎn)兩種表示方式。（1）包含小數(shù)的數(shù)就是定點(diǎn)表示的實(shí)型常量。例如：1894.12042.0+145345234.2234-2147483648.10（2）采用科學(xué)記數(shù)法表示的數(shù)就是浮點(diǎn)表示的實(shí)型常量。例如：101.5E50.5E-2+123E-3-12E5常

量4．日期時(shí)間常量日期時(shí)間常量：用單引號(hào)將表示日期時(shí)間的字符串括起來構(gòu)成。SQLServer可以識(shí)別如下格式的日期和時(shí)間。字母日期格式：如'April20,2020'；數(shù)字日期格式：如'4/15/2018'，'2018-04-15'未分隔的字符串格式：如'20201207'。（1）時(shí)間常量。例如：'14:30:24''04:24:PM'（2）日期時(shí)間常量。例如：'April20,202014:30:24'常

量5．貨幣常量貨幣常量是以“$”作為前綴的一個(gè)整型或?qū)嵭统Ａ繑?shù)據(jù)。例如：$12$542023-$45.56+$423456.996．唯一標(biāo)識(shí)常量唯一標(biāo)識(shí)常量是用于表示全局唯一標(biāo)識(shí)符（GUID）值的字符串?？梢允褂米址蚴M(jìn)制字符串格式指定。例如：'6F9619FF-8A86-D011-B42D-00004FC964FF'0xff19966f868b11d0b42d00c04fc964ff02變

量變量及其分類局部變量局部游標(biāo)變量表數(shù)據(jù)類型變量變

量1．變量及其分類（1）標(biāo)識(shí)符變量標(biāo)識(shí)符分常規(guī)標(biāo)識(shí)符和分隔標(biāo)識(shí)符。常規(guī)標(biāo)識(shí)符：以ASCII字母、Unicode字母、下劃線（_）、“@”或“#”開頭，后續(xù)可跟一個(gè)或若干個(gè)ASCII字符、Unicode字符、下劃線（_）、美元符號(hào)（$）、“@”或“#”，但不能全為下劃線（_）、“@”或“#”。分隔標(biāo)識(shí)符：包含在雙引號(hào)（"）或者方括號(hào)（[]）內(nèi)的常規(guī)標(biāo)識(shí)符或不符合常規(guī)標(biāo)識(shí)符規(guī)則的標(biāo)識(shí)符。標(biāo)識(shí)符允許的最大長度為128個(gè)字符。符合常規(guī)標(biāo)識(shí)符格式規(guī)則的標(biāo)識(shí)符可以分隔，也可以不分隔。對不符合標(biāo)識(shí)符規(guī)則的標(biāo)識(shí)符必須進(jìn)行分隔。變

量（2）變量的分類根據(jù)作用域變量可以分為全局變量和局部變量。全局變量：由系統(tǒng)提供且預(yù)先聲明，通過在名稱前加兩個(gè)“@”來區(qū)別于局部變量。T-SQL全局變量可作為函數(shù)引用。例如，@@ERROR返回執(zhí)行的上一個(gè)T-SQL語句的錯(cuò)誤號(hào)；@@CONNECTIONS返回自上次啟動(dòng)SQLServer以來連接或試圖連接的次數(shù)。局部變量：用于保存單個(gè)數(shù)據(jù)值。例如，保存運(yùn)算的中間結(jié)果，作為循環(huán)變量等。當(dāng)首字母為“@”時(shí)，表示該標(biāo)識(shí)符為局部變量名；當(dāng)首字母為“#”時(shí)，此標(biāo)識(shí)符為一臨時(shí)數(shù)據(jù)庫對象名，若開頭含一個(gè)“#”，表示局部臨時(shí)數(shù)據(jù)庫對象名；若開頭含兩個(gè)“#”，表示全局臨時(shí)數(shù)據(jù)庫對象名。變

量2．局部變量1）局部變量的定義在批處理或過程中用DECLARE語句聲明局部變量，所有局部變量在聲明后均初始化為NULL。語法格式如下。DECLARE@局部變量

數(shù)據(jù)類型[=值],…說明：（1）@局部變量：局部變量名應(yīng)為常規(guī)標(biāo)識(shí)符。前面的“@”表示是局部變量。（2）數(shù)據(jù)類型：用于定義局部變量的類型，可為系統(tǒng)類型或自定義類型。（3）=值：為變量賦值，值可以是常量或表達(dá)式，但它必須與變量聲明類型匹配。變

量2）局部變量的賦值當(dāng)聲明局部變量后，可用SET或SELECT語句為其賦值。用SET語句賦值。將DECLARE語句創(chuàng)建的局部變量設(shè)置為指定表達(dá)式的值。語法格式如下。SET@局部變量＝表達(dá)式說明：（1）@局部變量：除cursor、text、ntext、image及table外的任何類型變量名。變量名必須以“@”開頭。（2）表達(dá)式：任何有效的SQLServer表達(dá)式。變

量【例6.1】創(chuàng)建局部變量@var1、@var2并賦值，然后輸出變量的值。語句如下，執(zhí)行結(jié)果如圖。DECLARE@var1char(10),@var2char(30)SET@var1='中國'SET@var2=@var1+'是一個(gè)偉大的國家'SELECT@var1,@var2GO變

量【例6.2】創(chuàng)建一個(gè)名為sex的局部變量，并在SELECT語句中使用該局部變量查找xsb表中所有女同學(xué)的學(xué)號(hào)、姓名。語句如下：DECLARE@sexbitSET@sex=0SELECT學(xué)號(hào),姓名 FROMxsb WHERE性別=@sex【例6.3】使用查詢?yōu)樽兞抠x值。語句如下：DECLARE@studentchar(8)SET@student= (SELECT姓名 FROMxsb WHERE學(xué)號(hào)='221102’ )SELECT@studentAS姓名變

量用SELECT語句賦值。語法格式如下。SELECT@局部變量=表達(dá)式,…說明：（1）“SELECT@局部變量”通常用于將單個(gè)值返回到變量中。（2）如果SELECT語句沒有返回行，變量將保留當(dāng)前值。（3）如果“表達(dá)式”是不返回值的標(biāo)量子查詢，則將變量設(shè)為NULL。（4）一個(gè)SELECT語句可以初始化多個(gè)局部變量?！纠?.4】使用SELECT語句為局部變量賦值。語句如下：DECLARE@var1nvarchar(30)SELECT@var1='劉豐'SELECT@var1AS'NAME'變

量【例6.5】為局部變量賦空值。語句如下：DECLARE@var1nvarchar(30)SELECT@var1='劉豐'USExscjSELECT@var1=( SELECT姓名 FROMxsb WHERE學(xué)號(hào)='221399')SELECT@var1AS'NAME'變

量3．局部游標(biāo)變量1）局部游標(biāo)變量的定義其語法格式如下。DECLARE@游標(biāo)變量名CURSOR,…2）局部游標(biāo)變量的賦值利用SET語句為一個(gè)游標(biāo)變量賦值，有3種情況，語法格式如下。SET @游標(biāo)變量= @游標(biāo)變量

|游標(biāo)名

|CURSOR子句

3）局部游標(biāo)變量的使用步驟如下：定義游標(biāo)變量→給游標(biāo)變量賦值→打開游標(biāo)→利用游標(biāo)讀取行（記錄）→使用結(jié)束后關(guān)閉游標(biāo)→刪除游標(biāo)的引用。變

量【例6.6】使用游標(biāo)變量。語句如下：USExscjDECLARE@CursorVarCURSOR /*定義游標(biāo)變量*/SET@CursorVar=CURSORSCROLLDYNAMIC /*為游標(biāo)變量賦值*/ FOR SELECT學(xué)號(hào),姓名 FROMxsb WHERE姓名LIKE'王%'OPEN@CursorVar /*打開游標(biāo)*/FETCHNEXTFROM@CursorVarFETCHNEXTFROM@CursorVar /*通過游標(biāo)讀行記錄*/CLOSE@CursorVarDEALLOCATE@CursorVar /*刪除對游標(biāo)的引用*/執(zhí)行結(jié)果如圖。變

量4．表數(shù)據(jù)類型變量其語法格式如下。DECLARE@表變量名[AS]TABLE(

列定義 ... [表約束])變

量【例6.7】聲明一個(gè)表數(shù)據(jù)類型變量并向變量中插入數(shù)據(jù)。語句如下：USExscjDECLARE@var_tableASTABLE( numchar(6)NOTNULLPRIMARYKEY, namechar(8)NOTNULL, sexbitNULL) /*聲明表數(shù)據(jù)類型變量*/INSERTINTO@var_table SELECT學(xué)號(hào),姓名,性別FROMxsb /*插入xsb數(shù)據(jù)記錄*/SELECTTOP(4)*FROM@var_table /*查看內(nèi)容*/執(zhí)行結(jié)果如圖。03用戶自定義數(shù)據(jù)類型用戶定義類型用戶定義類型的使用用戶定義類型的刪除SSMS界面方式操作定義類型用戶自定義數(shù)據(jù)類型1．用戶定義類型（1）用戶定義數(shù)據(jù)類型語法格式如下。CREATETYPE類型名 FROM基類型[(精度[,位數(shù)])][NULL|NOTNULL]說明：“類型名”為定義的數(shù)據(jù)類型的名稱?！盎愋汀敝付ǘx數(shù)據(jù)類型所基于的系統(tǒng)數(shù)據(jù)類型。（2）用戶定義表類型用戶定義表類型可作為參數(shù)提供給語句、存儲(chǔ)過程或者函數(shù)。語法格式如下。CREATETYPE類型名 ASTABLE (

列定義

... [表約束] )說明：列定義為對列的描述，包含列名、數(shù)據(jù)類型、為空性、約束等。用戶自定義數(shù)據(jù)類型2．用戶定義類型的使用（1）用戶定義數(shù)據(jù)類型的使用用戶定義數(shù)據(jù)類型后，就可以像系統(tǒng)數(shù)據(jù)類型一樣使用?！纠?.8】先定義char(6)數(shù)據(jù)類型，對xsb1表學(xué)號(hào)列采用自定義數(shù)據(jù)類型。USExscjCREATETYPESTUDENT_num FROMchar(6)NOTNULLGODROPTABLExsb1CREATETABLExsb1(

學(xué)號(hào)

STUDENT_num NOTNULLPRIMARYKEY, /*學(xué)號(hào)為STUDENT_num類型*/

姓名 char(8) NOTNULL,

性別 bit NULLDEFAULT1,

出生日期 date NULL,

專業(yè) varchar(12) NULLDEFAULT'計(jì)算機(jī)',

總學(xué)分 int NULLDEFAULT0,

備注 varchar(500) NULL)用戶自定義數(shù)據(jù)類型（2）用戶定義表類型的使用【例6.9】先創(chuàng)建用戶自定義表類型，表結(jié)構(gòu)與cjb表相同，然后使用該表類型。語句如下：CREATETYPEcjb_type ASTABLE (

學(xué)號(hào) char(6) NOTNULL,

課程號(hào) char(3) NOTNULL,

成績 int NOTNULL, PRIMARYKEY(學(xué)號(hào),課程號(hào)) )GODECLARE@tab1cjb_typeINSERTINTO@tab1VALUES('201301','301',80)SELECT*FROM@tab1執(zhí)行結(jié)果如圖。用戶自定義數(shù)據(jù)類型3．用戶定義類型的刪除刪除用戶定義類型可以使用下列語句。語法格式如下。DROPTYPE類型名例如：USExscjDROPTYPEcjb_typeDROPTYPESTUDENT_num說明：這里cjb_type表類型可以刪除，但STUDENT_num數(shù)據(jù)類型不能被刪除，因?yàn)閤sb1表中引用了它。用戶自定義數(shù)據(jù)類型4.SSMS界面方式操作定義類型（1）創(chuàng)建定義類型指定數(shù)據(jù)庫（例如xscj）→可編程性→類型→用戶定義數(shù)據(jù)類型、用戶定義表類型等，按右鍵，選擇“新建用戶定義數(shù)據(jù)類型”等，在打開的新建用戶定義數(shù)據(jù)類型對話框中定義有關(guān)內(nèi)容，單擊“確定”。在創(chuàng)建自定義類型后，在對應(yīng)的欄下就會(huì)顯示類型名。即在用戶定義數(shù)據(jù)類型下顯示“STUDENT_num”，在用戶定義表類型下顯示cjb_type。（2）刪除定義類型選擇類型名稱，按右鍵，選擇“刪除”，在顯示的對話框中單擊“確定”按鍵。第6章T-SQL語言——運(yùn)算符與表達(dá)式運(yùn)算符與表達(dá)式1．算術(shù)運(yùn)算符算術(shù)運(yùn)算符在兩個(gè)表達(dá)式中執(zhí)行數(shù)學(xué)運(yùn)算，這兩個(gè)表達(dá)式可以是任何數(shù)字?jǐn)?shù)據(jù)類型。算術(shù)運(yùn)算符有“+”（加）、“-”（減）、“*”（乘）、“/”（除）和“%”（求模）五種?！?”（加）和“-”（減）運(yùn)算符還可用于對日期時(shí)間類型的值進(jìn)行算術(shù)運(yùn)算。2．位運(yùn)算符位運(yùn)算符在兩個(gè)表達(dá)式之間執(zhí)行位操作，這兩個(gè)表達(dá)式的類型可為整型或與整型兼容的數(shù)據(jù)類型（如字符型等），但不能為image類型。位運(yùn)算符如表。運(yùn)

算

符運(yùn)算規(guī)則&兩個(gè)位均為1時(shí)，結(jié)果為1，否則為0|只要一個(gè)位為1，則結(jié)果為1，否則為0^兩個(gè)位值不同時(shí)，結(jié)果為1，否則為0運(yùn)算符與表達(dá)式【例6.10】在test1數(shù)據(jù)庫中，建立表bitop，并插入一行，然后將a字段和b字段列的值進(jìn)行按位運(yùn)算。USEtest1CREATETABLEbitop( aintNOTNULL, bintNOTNULL)GOINSERTbitopVALUES(168,73)SELECTa&b,a|b,a^b FROMbitopGO執(zhí)行結(jié)果如圖。運(yùn)算符與表達(dá)式說明：a（168）的二進(jìn)制表示為0000000010101000；b（73）的二進(jìn)制表示為0000000001001001。在這兩個(gè)值之間進(jìn)行的位運(yùn)算如下：運(yùn)算符與表達(dá)式3．比較運(yùn)算符比較運(yùn)算符（又稱關(guān)系運(yùn)算符）如表所示，用于測試兩個(gè)表達(dá)式的值是否相同，其運(yùn)算結(jié)果為邏輯值，可以為TRUE、FALSE及UNKNOWN三者之一。除text、ntext或image類型的數(shù)據(jù)外，比較運(yùn)算符可以用于所有的表達(dá)式。運(yùn)

算

符含

義運(yùn)

算

符含

義=等于<=小于等于>

大于<>、!=不等于<

小于!<不小于>=大于等于!>不大于運(yùn)算符與表達(dá)式4．邏輯運(yùn)算符邏輯運(yùn)算符用于對某個(gè)條件進(jìn)行測試，運(yùn)算結(jié)果為TRUE或FALSE。SQLServer提供的邏輯運(yùn)算符如表6.6所示。運(yùn)

算

符運(yùn)算規(guī)則AND如果兩個(gè)操作數(shù)值都為TRUE，則運(yùn)算結(jié)果為TRUEOR如果兩個(gè)操作數(shù)中有一個(gè)為TRUE，則運(yùn)算結(jié)果為TRUENOT若一個(gè)操作數(shù)值為TRUE，則運(yùn)算結(jié)果為FALSE，否則為TRUEALL如果每個(gè)操作數(shù)值都為TRUE，則運(yùn)算結(jié)果為TRUEANY在一系列操作數(shù)中只要有一個(gè)為TRUE，則運(yùn)算結(jié)果為TRUEBETWEEN如果操作數(shù)在指定的范圍內(nèi)，則運(yùn)算結(jié)果為TRUEEXISTS如果子查詢包含一些行，則運(yùn)算結(jié)果為TRUEIN如果操作數(shù)值等于表達(dá)式列表中的一個(gè)，則運(yùn)算結(jié)果為TRUELIKE如果操作數(shù)與一種模式相匹配，則運(yùn)算結(jié)果為TRUESOME如果在一系列操作數(shù)中，有些值為TRUE，則運(yùn)算結(jié)果為TRUE運(yùn)算符與表達(dá)式（1）ANY、SOME、ALL、IN的使用?？梢詫LL或ANY關(guān)鍵字與比較運(yùn)算符組合進(jìn)行子查詢。SOME的用法與ANY相同。以“>”比較運(yùn)算符為例。>ALL表示大于每一個(gè)值，即大于最大值。例如，>ALL(5,2,3)表示大于5。因此，使用>ALL的子查詢也可用MAX集函數(shù)實(shí)現(xiàn)。>ANY表示至少大于一個(gè)值，即大于最小值。例如，>ANY(7,2,3)表示大于2。因此，使用>ANY的子查詢也可用MIN集函數(shù)實(shí)現(xiàn)。=ANY運(yùn)算符與IN等效。<>ALL與NOTIN等效。運(yùn)算符與表達(dá)式【例6.11】查詢成績高于“林一帆”最高成績的學(xué)生姓名、課程名及成績。語句如下：USExscjSELECT姓名,課程名,成績 FROMxsb,cjb,kcb WHERE成績>ALL ( SELECTb.成績 FROMxsba,cjbb WHEREa.學(xué)號(hào)=b.學(xué)號(hào)ANDa.姓名='林一帆' ) ANDxsb.學(xué)號(hào)=cjb.學(xué)號(hào) ANDkcb.課程號(hào)=cjb.課程號(hào) AND姓名<>'林一帆'查詢結(jié)果如圖。運(yùn)算符與表達(dá)式（2）BETWEEN的使用。語法格式如下。

測試表達(dá)式[NOT]BETWEEN起始表達(dá)式AND結(jié)束表達(dá)式說明：如果“測試表達(dá)式”的值大于或等于“起始表達(dá)式”的值并且小于或等于“結(jié)束表達(dá)式”的值，則運(yùn)算結(jié)果為TRUE，否則為FALSE?！捌鹗急磉_(dá)式”和“結(jié)束表達(dá)式”指定測試范圍，3個(gè)表達(dá)式的類型必須相同。【例6.12】查詢總學(xué)分在40～50的學(xué)生學(xué)號(hào)和姓名。語句如下：SELECT學(xué)號(hào),姓名,總學(xué)分 FROMxsb WHERE總學(xué)分BETWEEN40AND50使用>=和<=代替BETWEEN實(shí)現(xiàn)相同的功能，語句如下。SELECT學(xué)號(hào),姓名,總學(xué)分 FROMxsb WHERE總學(xué)分>=40AND總學(xué)分<=50運(yùn)算符與表達(dá)式【例6.13】查詢總學(xué)分在范圍40～50之外的所有學(xué)生的學(xué)號(hào)和姓名。語句如下：SELECT學(xué)號(hào),姓名,總學(xué)分 FROMxsb WHERE總學(xué)分NOTBETWEEN40AND50（3）LIKE的使用。語法格式如下。表達(dá)式[NOT]LIKE模式[ESCAPE轉(zhuǎn)義符]說明：如果指定的字符串與指定的模式匹配，則運(yùn)算結(jié)果為TRUE，否則為FALSE。模式可以包含普通字符和通配符。【例6.14】查詢課程名以“計(jì)”或C開頭的課程情況。語句如下：SELECT* FROMkcb WHERE課程名LIKE'[計(jì)C]%'運(yùn)算符與表達(dá)式（4）EXISTS與NOTEXISTS的使用。語法格式如下。EXISTS子查詢說明：如果子查詢的結(jié)果不為空，則運(yùn)算結(jié)果為TRUE，否則為FALSE?！白硬樵儭贝硪粋€(gè)受限的SELECT語句（不允許有COMPUTE子句和INTO關(guān)鍵字）。【例6.15】查詢所有選課學(xué)生的姓名。語句如下：SELECTDISTINCT姓名 FROMxsb WHEREEXISTS ( SELECT* FROMcjb WHERExsb.學(xué)號(hào)=cjb.學(xué)號(hào) )運(yùn)算符與表達(dá)式5．字符串連接運(yùn)算符通過運(yùn)算符“+”實(shí)現(xiàn)兩個(gè)字符串的連接運(yùn)算。【例6.16】多個(gè)字符串的連接。語句如下：SELECT(學(xué)號(hào)+','+姓名)AS學(xué)號(hào)及姓名 FROMxsb WHERE學(xué)號(hào)='221101'查詢結(jié)果如圖。6．一元運(yùn)算符一元運(yùn)算符有“+”（正）、“-”（負(fù)）和“～”（按位取反）3個(gè)。對于按位取反運(yùn)算符的舉例如下。設(shè)a的值為12（0000000000001100），計(jì)算～a的值為1111111111110011。7．賦值運(yùn)算符指給局部變量賦值的SET和SELECT語句中使用的“=”。運(yùn)算符與表達(dá)式8．運(yùn)算符的優(yōu)先順序當(dāng)一個(gè)復(fù)雜的表達(dá)式有多個(gè)運(yùn)算符時(shí)，運(yùn)算符優(yōu)先級決定執(zhí)行運(yùn)算的先后次序。執(zhí)行的順序會(huì)影響所得到的運(yùn)算結(jié)果。運(yùn)算符優(yōu)先級如表所示。在一個(gè)表達(dá)式中按先高（優(yōu)先級數(shù)字?。┖蟮停▋?yōu)先級數(shù)字大）的順序進(jìn)行運(yùn)算。運(yùn)

算

符優(yōu)

先

級運(yùn)

算

符優(yōu)

先

級+（正）、-（負(fù)）、～（按位NOT）1NOT6*（乘）、/（除）、%（取模）2AND7+（加）、+（串聯(lián)）、-（減）3ALL、ANY、BETWEEN、IN、LIKE、OR、SOME8=、>、<、>=、<=、<>、!=、!>、!<比較運(yùn)算符4=（賦值）9^（位異或）、&（位與）、|（位或）5

運(yùn)算符與表達(dá)式9．表達(dá)式表達(dá)式就是常量、變量、列名、復(fù)雜計(jì)算、運(yùn)算符和函數(shù)的組合。一個(gè)表達(dá)式通常可以得到一個(gè)值。與常量和變量一樣，表達(dá)式的值也具有某種數(shù)據(jù)類型，可能的數(shù)據(jù)類型有字符類型、數(shù)值類型、日期時(shí)間類型。這樣根據(jù)值的類型，表達(dá)式可分為字符型表達(dá)式、數(shù)值型表達(dá)式和日期時(shí)間型表達(dá)式。表達(dá)式還可以根據(jù)值的復(fù)雜性來分類。若表達(dá)式的結(jié)果只是一個(gè)值，如一個(gè)數(shù)值、一個(gè)單詞或一個(gè)日期，則這種表達(dá)式稱為標(biāo)量表達(dá)式，如1+2，'a'>'b'。若表達(dá)式的結(jié)果是由不同類型數(shù)據(jù)組成的一行值，則這種表達(dá)式稱為行表達(dá)式。例如：（'201101'，'王林'，'計(jì)算機(jī)'，15）。若表達(dá)式的結(jié)果為0個(gè)、1個(gè)或多個(gè)行表達(dá)式的集合，那么這個(gè)表達(dá)式就稱為表表達(dá)式。第6章T-SQL語言——流程控制語句流程控制語句在SQLServer中提供了如表所示的流程控制語句。控制語句說

明控制語句說

明BEGIN…END語句塊CONTINUE用于重新開始下一次循環(huán)IF…ELSE條件語句BREAK用于退出最內(nèi)層的循環(huán)CASE分支語句RETURN無條件返回GOTO無條件轉(zhuǎn)移語句WAITFOR為語句的執(zhí)行設(shè)置延遲WHILE循環(huán)語句

【例6.17】查詢總學(xué)分大于42的學(xué)生人數(shù)。語句如下：DECLARE@numintSELECT@num=(SELECTCOUNT(姓名)FROMxsbWHERE總學(xué)分>42)IF@num<>0 SELECT@numAS'總學(xué)分大于42的人數(shù)'01語句塊語句塊當(dāng)要執(zhí)行多條T-SQL語句時(shí)，就需要使用語句塊，作為一組語句來執(zhí)行。語法格式如下。BEGIN SQL語句塊END說明：關(guān)鍵字BEGIN是T-SQL語句塊的起始位置，END標(biāo)識(shí)同一個(gè)T-SQL語句塊的結(jié)尾?！癝QL語句塊”是由一條或者多條T-SQL語句構(gòu)成。02條件語句條件語句在程序中如果要對指定的條件進(jìn)行判定，當(dāng)條件為真或假時(shí)分別執(zhí)行不同的T-SQL語句，可用IF…ELSE語句實(shí)現(xiàn)。語法格式如下。IF條件表達(dá)式 SQL語句|語句塊 /*A*/[ELSE SQL語句|語句塊] /*B*/

IF語句的執(zhí)行流程如圖。條件語句【例6.18】“計(jì)算機(jī)導(dǎo)論”課程的平均成績75分分兩段顯示。語句如下：IF( SELECTAVG(成績) FROMxsb,cjb,kcb WHERExsb.學(xué)號(hào)=cjb.學(xué)號(hào)

ANDcjb.課程號(hào)=kcb.課程號(hào) ANDkcb.課程名='計(jì)算機(jī)導(dǎo)論')<=75 SELECT'計(jì)算機(jī)導(dǎo)論','平均成績小于等于75'ELSE SELECT'計(jì)算機(jī)導(dǎo)論','平均成績大于75'條件語句【例6.18續(xù)】“計(jì)算機(jī)導(dǎo)論”課程的平均成績75分、85分分三段顯示。語句如下：IF( SELECTAVG(成績) FROMxsb,cjb,kcb WHERExsb.學(xué)號(hào)=cjb.學(xué)號(hào)

ANDcjb.課程號(hào)=kcb.課程號(hào) ANDkcb.課程名='計(jì)算機(jī)導(dǎo)論')<=75 SELECT'平均成績小于等于75分'ELSE IF ( SELECTAVG(成績) FROMxsb,cjb,kcb WHERExsb.學(xué)號(hào)=cjb.學(xué)號(hào)

ANDcjb.課程號(hào)=kcb.課程號(hào) ANDkcb.課程名='計(jì)算機(jī)導(dǎo)論' )<=85 SELECT'平均成績大于75分小于等于85分' ELSE SELECT'平均成績大于85分'條件語句【例6.19】刪除數(shù)據(jù)庫快照IFEXISTS(SELECTdbidFROMsys.databasesWHERENAME='sales_snapshot0600')DROPDATABASESalesSnapshot0600;GO--RevertingSalestosales_snapshot1200USEmaster;RESTOREDATABASESalesFROMDATABASE_SNAPSHOT='sales_snapshot1200';GO03分支語句分支語句CASE語句可進(jìn)行流程控制，可根據(jù)多重條件分支。語法格式如下。CASE輸入表達(dá)式 WHEN表達(dá)式THEN結(jié)果表達(dá)式 … [ELSE結(jié)果表達(dá)式]END或者：CASE WHEN布爾表達(dá)式THEN結(jié)果表達(dá)式 … [ELSE結(jié)果表達(dá)式]END分支語句說明：（1）第一種格式中“輸入表達(dá)式”是要判斷的值或表達(dá)式，接下來是一系列的WHEN

THEN塊，每一塊的“WHEN表達(dá)式”參數(shù)指定要與“輸入表達(dá)式”比較的值，如果為真，就執(zhí)行“結(jié)果表達(dá)式”中的T-SQL語句。如果前面的每一個(gè)塊都不匹配，就會(huì)執(zhí)行ELSE塊指定的語句。CASE語句最后以END關(guān)鍵字結(jié)束。（2）第二種格式中CASE關(guān)鍵字后面沒有參數(shù)，在WHEN

THEN塊中，“布爾表達(dá)式”指定了一個(gè)比較表達(dá)式，表達(dá)式為真時(shí)執(zhí)行THEN后面的語句。分支語句【例6.20】使用第一種格式的CASE語句，根據(jù)性別值輸出“男”或“女”。語句如下：SELECT學(xué)號(hào),姓名,專業(yè),SEX= CASE性別 WHEN1THEN'男' WHEN0THEN'女' ELSE'無' END FROMxsb WHERE總學(xué)分>48使用第二種格式的CASE語句則可以使用以下T-SQL語句。SELECT學(xué)號(hào),姓名,專業(yè),SEX= CASE WHEN性別=1THEN'男' WHEN性別=0THEN'女' ELSE'無' END FROMxsb WHERE總學(xué)分>4804無條件轉(zhuǎn)移語句無條件轉(zhuǎn)移語句無條件轉(zhuǎn)移語句將執(zhí)行流程轉(zhuǎn)移到標(biāo)號(hào)指定的位置。語法格式如下。

GOTO標(biāo)號(hào)“標(biāo)號(hào)”是指向的語句標(biāo)號(hào)，必須符合標(biāo)識(shí)符規(guī)則。標(biāo)號(hào)的定義形式如下。

標(biāo)號(hào):語句05循環(huán)語句WHILE循環(huán)語句BREAK語句CONTINUE語句循環(huán)語句1．WHILE循環(huán)語句如果需要重復(fù)執(zhí)行程序中的一部分語句，則可使用WHILE循環(huán)語句實(shí)現(xiàn)。語法格式如下。WHILE條件表達(dá)式 SQL語句|語句塊 /*循環(huán)體*/執(zhí)行流程如圖。循環(huán)語句【例6.21】在一個(gè)臨時(shí)表中生成順序編號(hào)列。語句如下：USExscjDECLARE@iintDECLARE@tab1ASTABLE( numchar(6)NOTNULL, namechar(12))SET@i=1WHILE@i<=10BEGIN INSERTINTO@tab1 (num)VALUES(@i) SET@i=@i+1ENDSELECT*FROM@tab1執(zhí)行結(jié)果如圖。循環(huán)語句【例6.22】計(jì)算所有課程的平均成績。語句如下：DECLARE@kchchar(3)DECLARE@cjfloatUSExscjDECLAREcj_curcursor /*定義游標(biāo)*/FORSELECT課程號(hào),AVG(成績) FROMcjb GROUPBY課程號(hào)OPENcj_cur /*打開游標(biāo)*/FETCHNEXTFROMcj_cur /*讀取第一條記錄放到對應(yīng)變量中*/INTo@kch,@cjWHILE(@@fetch_status=0) BEGINSELECT@kch,@cj /*顯示課程號(hào)和平均成績*/FETCHNEXTFROMcj_cur /*讀取當(dāng)前記錄放到對應(yīng)變量中*/ INTO@kch,@cjENDCLOSEcj_curDEALLOCATEcj_cur執(zhí)行結(jié)果如圖。循環(huán)語句2．BREAK語句其語法格式如下。BREAKBREAK語句一般用在循環(huán)語句中，用于退出本層循環(huán)。當(dāng)程序中有多層循環(huán)嵌套時(shí)，使用BREAK語句只能退出其所在的這一層循環(huán)。3．CONTINUE語句其語法格式如下。CONTINUECONTINUE語句一般用在循環(huán)語句中，用于結(jié)束本次循環(huán)，重新轉(zhuǎn)到下一次循環(huán)條件的判斷。06返回語句返回語句用于從存儲(chǔ)過程、批處理或語句塊中無條件退出，不執(zhí)行位于RETURN之后的語句。其語法格式如下。RETURN[整數(shù)表達(dá)式]如果用在存儲(chǔ)過程中，則可以返回整型的表達(dá)式值。如果不包含選項(xiàng)，則退出程序并返回一個(gè)空值。當(dāng)用于存儲(chǔ)過程時(shí)，RETURN不能返回空值?！纠?.23】判斷xsb1是否存在學(xué)號(hào)為221301的學(xué)生，如果存在則返回，不存在則插入該學(xué)生記錄語句如下：IFEXISTS(SELECT*FROMxsb1WHERE學(xué)號(hào)='221301') RETURNELSE INSERTINTOxsb1VALUES('221301','張可',1,'2004-08-12','電氣工程',0,NULL)SELECT*FROMxsb107等待語句等待語句等待語句指定觸發(fā)語句塊、存儲(chǔ)過程或事務(wù)執(zhí)行的時(shí)刻或需等待的時(shí)間間隔。其語法格式如下。WAITFOR DELAY'等待時(shí)間' |TIME'執(zhí)行時(shí)刻'說明：（1）DELAY'等待時(shí)間'：指定運(yùn)行批處理、存儲(chǔ)過程和事務(wù)必須等待的時(shí)間，最長可達(dá)24小時(shí)。“等待時(shí)間”可以用time數(shù)據(jù)格式指定，用單引號(hào)括起來。（2）TIME'執(zhí)行時(shí)刻'：指定運(yùn)行批處理、存儲(chǔ)過程和事務(wù)的時(shí)刻，“執(zhí)行時(shí)刻”表示W(wǎng)AITFOR語句完成的時(shí)刻，值的指定同上。【例6.24】設(shè)定在早上8點(diǎn)執(zhí)行查詢語句。語句如下：BEGIN WAITFORTIME'8:00' SELECT*FROMxsb1END08錯(cuò)誤處理語句錯(cuò)誤處理語句在SQLServer中，可以使用TRY…CATCH語句進(jìn)行T-SQL語言中的錯(cuò)誤處理。語法格式如下。BEGINTRY SQL語句|語句塊ENDTRYBEGINCATCH [SQL語句|語句塊]ENDCATCH第6章T-SQL語言——系統(tǒng)內(nèi)置函數(shù)01系統(tǒng)內(nèi)置函數(shù)介紹行集函數(shù)聚合函數(shù)標(biāo)量函數(shù)系統(tǒng)內(nèi)置函數(shù)介紹1．行集函數(shù)SQLServer2008主要提供了如下行集函數(shù)。（1）CONTAINSTABLE：對于基于字符類型的列，按照一定的搜索條件進(jìn)行精確或模糊匹配，然后返回一個(gè)表，該表可能為空。（2）FREETEXTTABLE：為基于字符類型的列返回一個(gè)表，其中的值符合指定文本的含義，但不符合確切的表達(dá)方式。（3）OPENDATASOURCE：提供與數(shù)據(jù)源的連接。（4）OPENQUERY：在指定數(shù)據(jù)源上執(zhí)行查詢?？梢栽诓樵兊腇ROM子句中像引用基本表一樣引用OPENQUERY函數(shù)，雖然查詢可能返回多個(gè)記錄，但OPENQUERY只返回第一個(gè)記錄。（5）OPENROWSET：包含訪問OLEDB數(shù)據(jù)源中遠(yuǎn)程數(shù)據(jù)所需的全部連接信息?？稍诓樵兊腇ROM子句中像引用基本表一樣引用OPENROWSET函數(shù)，雖然查詢可能返回多個(gè)記錄，但OPENROWSET只返回第一個(gè)記錄。（6）OPENXML函數(shù)：通過XML文檔提供行集視圖。系統(tǒng)內(nèi)置函數(shù)介紹2．聚合函數(shù)聚合函數(shù)對一組值操作，返回單一的匯總值。聚合函數(shù)在以下情況下，允許作為表達(dá)式使用。（1）SELECT語句的選擇列表（子查詢或外部查詢）。（2）COMPUTE或COMPUTEBY子句。（3）HAVING子句。T-SQL語言提供的常用聚合函數(shù)的應(yīng)用請參考第4章相關(guān)內(nèi)容。3．標(biāo)量函數(shù)標(biāo)量函數(shù)的特點(diǎn)：輸入?yún)?shù)的類型為基本類型，返回值也為基本類型。02常用系統(tǒng)標(biāo)量函數(shù)配置函數(shù)數(shù)學(xué)函數(shù)字符串函數(shù)系統(tǒng)函數(shù)日期時(shí)間函數(shù)游標(biāo)函數(shù)元數(shù)據(jù)函數(shù)常用系統(tǒng)標(biāo)量函數(shù)1．配置函數(shù)配置函數(shù)用于返回當(dāng)前配置選項(xiàng)設(shè)置的信息。2．?dāng)?shù)學(xué)函數(shù)數(shù)學(xué)函數(shù)可對SQLServer提供的數(shù)字?jǐn)?shù)據(jù)（decimal、integer、float、real、money、smallmoney、smallint和tinyint）進(jìn)行數(shù)學(xué)運(yùn)算并返回運(yùn)算結(jié)果。（1）ABS函數(shù)的語法格式如下。ABS(數(shù)字表達(dá)式)返回指定數(shù)字表達(dá)式的絕對值。參數(shù)為數(shù)字型表達(dá)式（bit數(shù)據(jù)類型除外），返回值類型與“數(shù)字表達(dá)式”相同。例如，顯示ABS函數(shù)對3個(gè)不同數(shù)字的效果，語句如下。SELECTABS(

5.0),ABS(0.0),ABS(8.0)（2）RAND函數(shù)的語法格式如下。RAND([種子])返回0～1之間的一個(gè)隨機(jī)值。參數(shù)“種子”是指定種子值的整型表達(dá)式，返回值類型為float。如果未指定“種子”，則隨機(jī)分配種子值。對于指定的種子值，返回的結(jié)果始終相同。例如，如下程序通過RAND函數(shù)返回隨機(jī)值。DECLARE@countintSET@count=5SELECTRAND(@count)常用系統(tǒng)標(biāo)量函數(shù)3．字符串函數(shù)字符串函數(shù)用于對字符串進(jìn)行處理。在此介紹一些常用的字符串函數(shù)，其他的請參考有關(guān)文檔。（1）ASCII函數(shù)的語法格式如下。

ASCII(字符表達(dá)式)返回字符表達(dá)式最左端字符的ASCII值。參數(shù)“字符表達(dá)式”為字符型的表達(dá)式，返回值為整型。例如，查找字符串'sql'最左端字符的ASCII值，語句如下。

SELECTASCII('sql')執(zhí)行結(jié)果為115，它是小寫s的ASCII值。（2）CHAR函數(shù)的語法格式如下。CHAR(整型表達(dá)式)將ASCII碼轉(zhuǎn)換為字符?！罢捅磉_(dá)式”為介于0～255之間的整數(shù)，返回值為字符型。常用系統(tǒng)標(biāo)量函數(shù)（3）LEFT函數(shù)的語法格式如下。

LEFT(字符表達(dá)式,整型表達(dá)式)返回從字符串左邊開始指定個(gè)數(shù)的字符，返回值為varchar型。例如，返回課程名最左邊的4個(gè)字符，語句如下。SELECTLEFT(課程名,4) FROMkcb ORDERBY課程號(hào)又如：SELECT學(xué)號(hào),姓名 FROMxsb WHERELEFT(學(xué)號(hào),2)='19'常用系統(tǒng)標(biāo)量函數(shù)（4）LTRIM函數(shù)的語法格式如下。

LTRIM(字符表達(dá)式)刪除“字符表達(dá)式”字符串中的前導(dǎo)空格，并返回字符串。例如，使用LTRIM函數(shù)刪除字符變量中的起始空格，語句如下。DECLARE@stringvarchar(40)SET@string='中國，一個(gè)古老而偉大的國家'SELECTLTRIM(@string)SELECT@string（5）REPLACE函數(shù)的語法格式如下。REPLACE('字符串表達(dá)式1','字符串表達(dá)式2','字符串表達(dá)式3')用“字符串表達(dá)式3”替換“字符串表達(dá)式1”中包含的“字符串表達(dá)式2”，并返回替換后的表達(dá)式（返回值為字符型）。常用系統(tǒng)標(biāo)量函數(shù)（6）SUBSTRING函數(shù)的語法格式如下。SUBSTRING(表達(dá)式,起始,長度)返回表達(dá)式中指定的部分?jǐn)?shù)據(jù)。參數(shù)“表達(dá)式”可為字符串、二進(jìn)制串、text、image字段或表達(dá)式；“起始”、“長度”均為整型，前者指定子串的開始位置，后者指定子串的長度（要返回字節(jié)數(shù)）。如果“表達(dá)式”是字符類型和二進(jìn)制類型，則返回值類型與“表達(dá)式”的類型相同。在其他情況下，參考表。給定的表達(dá)式返回值類型給定的表達(dá)式返回值類型給定的表達(dá)式返回值類型textvarcharimagevarbinaryntextnvarchar【例6.25】顯示第1列為xsb表中的姓，在第2列中為學(xué)生名。語句如下，執(zhí)行結(jié)果如圖。SELECTTOP(4)SUBSTRING(姓名,1,1),SUBSTRING(姓名,2,LEN(姓名)-1) FROMxsb ORDERBY姓名常用系統(tǒng)標(biāo)量函數(shù)【例6.26】顯示字符串"China"中每個(gè)字符的ASCII值和字符。語句如下，執(zhí)行結(jié)果如圖。DECLARE@positionint,@stringchar(6)SET@position=1SET@string='China'WHILE@position<=DATALENGTH(@string)BEGIN SELECTASCII(SUBSTRING(@string,@position,1))ASASCII碼, CHAR(ASCII(SUBSTRING(@string,@position,1)))AS字符 SET@position=@position+1END常用系統(tǒng)標(biāo)量函數(shù)4．系統(tǒng)函數(shù)1）數(shù)據(jù)類型的轉(zhuǎn)換函數(shù)CAST、CONVERT這兩個(gè)函數(shù)的功能都是實(shí)現(xiàn)數(shù)據(jù)類型的轉(zhuǎn)換，但CONVERT的功能更強(qiáng)。常用的類型轉(zhuǎn)換有以下幾種情況：日期型→字符型、字符型→日期型、數(shù)值型→字符型。語法格式如下。CAST(表達(dá)式AS數(shù)據(jù)類型[(長度)])CONVERT(數(shù)據(jù)類型[(長度)],表達(dá)式[,類型])常用系統(tǒng)標(biāo)量函數(shù)說明：這兩個(gè)函數(shù)將表達(dá)式的類型轉(zhuǎn)換為“數(shù)據(jù)類型”所指定的類型。參數(shù)“表達(dá)式”可為任何有效的表達(dá)式，“數(shù)據(jù)類型”可為系統(tǒng)提供的基本類型，不能為用戶自定義類型，當(dāng)為nchar、nvarchar、char、varchar、binary或varbinary等數(shù)據(jù)類型時(shí)，可以通過“長度”參數(shù)指定長度。對于不同的表達(dá)式類型轉(zhuǎn)換，參數(shù)“類型”的取值不同?！邦愋汀钡某Ｓ萌≈导捌渥饔萌绫怼２粠兰o(jì)數(shù)位（yy）帶世紀(jì)數(shù)位（yyyy）標(biāo)

準(zhǔn)輸入/輸出

0或100默認(rèn)值monddyyyyhh:miAM(或PM)1101美國mm/dd/yyyy2102ANSIyy.mm.dd

9或109默認(rèn)值+毫秒monddyyyyhh:mi:ss:mmmAM(或PM)10110美國mm-dd-yy12112ISOyymmdd日期型與字符型轉(zhuǎn)換時(shí)“類型”的常用取值及其作用常用系統(tǒng)標(biāo)量函數(shù)style值輸

出0（默認(rèn)值）根據(jù)需要使用科學(xué)記數(shù)法，長度最多為61使用科學(xué)記數(shù)法，長度為82使用科學(xué)記數(shù)法，長度為16值輸

出0（默認(rèn)值）小數(shù)點(diǎn)左側(cè)每3位數(shù)字之間不以逗號(hào)分隔，小數(shù)點(diǎn)右側(cè)取兩位數(shù)，如4235.981小數(shù)點(diǎn)左側(cè)每3位數(shù)字之間以逗號(hào)分隔，小數(shù)點(diǎn)右側(cè)取兩位數(shù)，如3,510.922小數(shù)點(diǎn)左側(cè)每3位數(shù)字之間不以逗號(hào)分隔，小數(shù)點(diǎn)右側(cè)取4位數(shù)，如4235.9819

float或real轉(zhuǎn)換為字符型數(shù)據(jù)時(shí)“類型”的取值從money或smallmoney轉(zhuǎn)換為字符數(shù)據(jù)時(shí)“類型”的取值常用系統(tǒng)標(biāo)量函數(shù)【例6.27】檢索總學(xué)分在50～59分的學(xué)生姓名，并將總學(xué)分轉(zhuǎn)換為char(20)。語句如下：/*使用CAST實(shí)現(xiàn)*/SELECT姓名,總學(xué)分 FROMxsb WHERECAST(總學(xué)分ASchar(20))LIKE'5_'/*使用CONVERT實(shí)現(xiàn)*/SELECT姓名,總學(xué)分 FROMxsb WHERECONVERT(char(20),總學(xué)分)LIKE'5_'常用系統(tǒng)標(biāo)量函數(shù)2）獲得非空表達(dá)式的值語法格式如下。COALESCE(表達(dá)式,…)說明：返回參數(shù)表中第一個(gè)非空表達(dá)式的值，如果所有自變量均為NULL，則COALESCE返回NULL值。參數(shù)“表達(dá)式”可為任何類型的表達(dá)式。COALESCE(表達(dá)式1,…)與如下形式的CASE語句等價(jià)。CASE WHEN(表達(dá)式1ISNOTNULL)THEN表達(dá)式1 … WHEN(表達(dá)式NISNOTNULL)THEN表達(dá)式NELSENULL3）判斷一個(gè)表達(dá)式是否為數(shù)值類型語法格式如下。ISNUMBRIC(表達(dá)式)如果輸入表達(dá)式的計(jì)算值為有效的整數(shù)、浮點(diǎn)數(shù)、money或decimal類型，則ISNUMBRIC返回1；否則返回0。常用系統(tǒng)標(biāo)量函數(shù)5．日期時(shí)間函數(shù)日期時(shí)間函數(shù)可用在SELECT語句的選擇列表或查詢的WHERE子句中。下列介紹最常用的幾個(gè)日期時(shí)間函數(shù)。其他日期時(shí)間函數(shù)請參考有關(guān)文檔。語法格式如下。GETDATE()YEAR(日期)MONTH(日期)DAY(日期)GETDATE()函數(shù)按SQLServer標(biāo)準(zhǔn)內(nèi)部格式返回當(dāng)前系統(tǒng)日期和時(shí)間，返回值類型為datetime。YEAR()、MONTH()、DAY()函數(shù)分別返回指定日期的年、月、日部分，返回值都為整數(shù)。常用系統(tǒng)標(biāo)量函數(shù)6．游標(biāo)函數(shù)（1）游標(biāo)中當(dāng)前存在的行數(shù)語法格式如下。@@CURSOR_ROWS說明：返回最后打開的游標(biāo)中當(dāng)前存在的滿足條件的行數(shù)。返回值為0時(shí)表示游標(biāo)未打開；為1時(shí)表示游標(biāo)為動(dòng)態(tài)游標(biāo)；為-m時(shí)表示游標(biāo)被異步填充，返回值（-m）是鍵集中當(dāng)前的行數(shù)；為n時(shí)表示游標(biāo)已完全填充，返回值（n）是游標(biāo)中的總行數(shù)。【例6.28】聲明一個(gè)游標(biāo)，并用SELECT顯示@@CURSOR_ROWS的值。語句如下：SELECT@@CURSOR_ROWSDECLAREstudent_cursorCURSOR FOR SELECT姓名FROMxsbORDERBY學(xué)號(hào)OPENstudent_cursorFETCHNEXTFROMstudent_cursorSELECT@@CURSOR_ROWSCLOSEstudent_cursorDEALLOCATEstudent_cursor執(zhí)行結(jié)果如圖。常用系統(tǒng)標(biāo)量函數(shù)（2）游標(biāo)的結(jié)果集狀態(tài)函數(shù)語法格式如下。CURSOR_STATUS('本地','游標(biāo)名' /*指明數(shù)據(jù)源為本地游標(biāo)*/|'全局','游標(biāo)名' /*指明數(shù)據(jù)源為全局游標(biāo)*/|'變量','游標(biāo)變量' /*指明數(shù)據(jù)源為游標(biāo)變量*/)說明：常量字符“'本地'”“'全局'”用于指定游標(biāo)的類型，“'本地'”表示為本地游標(biāo)名，“'全局'”表示為全局游標(biāo)名。參數(shù)“'游標(biāo)名'”用于指定游標(biāo)名，常量字符“'變量'”用于說明其后的游標(biāo)變量為一個(gè)本地變量，參數(shù)“'游標(biāo)變量'”為本地游標(biāo)變量名稱，返回值類型為smallint。CURSOR_STATUS()函數(shù)的返回值如表。返

回

值游標(biāo)名或游標(biāo)變量返

回

值游標(biāo)名或游標(biāo)變量1游標(biāo)的結(jié)果集至少有一行-2游標(biāo)不可用0游標(biāo)的結(jié)果集為空*-3指定的游標(biāo)不存在-1游標(biāo)被關(guān)閉

常用系統(tǒng)標(biāo)量函數(shù)（3）返回FETCH語句執(zhí)行后游標(biāo)的狀態(tài)@@FETCH_STATUS@@FETCH_STATUS的返回值如表。返

回

值說

明返

回

值說

明0FETCH語句執(zhí)行成功-2被讀取的記錄不存在-1FETCH語句執(zhí)行失敗

常用系統(tǒng)標(biāo)量函數(shù)【例6.29】用@@FETCH_STATUS控制在一個(gè)WHILE循環(huán)中的游標(biāo)活動(dòng)。語句如下：DECLARE@namechar(20),@numchar(6)DECLAREstudent_curCURSOR FOR SELECT姓名,學(xué)號(hào)FROMxscj.dbo.xsbWHERE專業(yè)='通信工程'OPENstudent_curFETCHNEXTFROMstudent_curINTO@name,@numSELECT@name,@numWHILE@@FETCH_STATUS=0BEGIN FETCHNEXTFROMstudent_curENDCLOSEstudent_curDEALLOCATEstudent_cur執(zhí)行結(jié)果如圖。常用系統(tǒng)標(biāo)量函數(shù)7．元數(shù)據(jù)函數(shù)元數(shù)據(jù)是用于描述數(shù)據(jù)庫和數(shù)據(jù)庫對象的。元數(shù)據(jù)函數(shù)用于返回有關(guān)數(shù)據(jù)庫和數(shù)據(jù)庫對象的信息。（1）獲取數(shù)據(jù)庫的標(biāo)識(shí)號(hào)DB_ID(['數(shù)據(jù)庫名'])系統(tǒng)在創(chuàng)建數(shù)據(jù)庫時(shí)，自動(dòng)為其創(chuàng)建一個(gè)標(biāo)識(shí)號(hào)。函數(shù)DB_ID根據(jù)指定的數(shù)據(jù)庫名，返回其數(shù)據(jù)庫標(biāo)識(shí)（ID）號(hào)。如果參數(shù)“'數(shù)據(jù)庫名'”不指定，則返回當(dāng)前數(shù)據(jù)庫ID，返回值類型為smallint。（2）獲取數(shù)據(jù)庫名DB_NAME(數(shù)據(jù)庫ID)根據(jù)參數(shù)“數(shù)據(jù)庫ID”所給的數(shù)據(jù)庫標(biāo)識(shí)號(hào)，返回?cái)?shù)據(jù)庫名。參數(shù)“數(shù)據(jù)庫ID”的類型為smallint，如果沒有指定數(shù)據(jù)庫標(biāo)識(shí)號(hào)，則返回當(dāng)前數(shù)據(jù)庫名。返回值類型為nvarchar(128)。第7章

存儲(chǔ)過程、用戶定義函數(shù)和觸發(fā)器——存儲(chǔ)過程01存儲(chǔ)過程的優(yōu)點(diǎn)和類型存儲(chǔ)過程的優(yōu)點(diǎn)存儲(chǔ)過程的類型存儲(chǔ)過程的優(yōu)點(diǎn)和類型1.存儲(chǔ)過程的優(yōu)點(diǎn)使用存儲(chǔ)過程的優(yōu)點(diǎn)如下。（1）存儲(chǔ)過程在服務(wù)器端運(yùn)行，執(zhí)行速度快。（2）存儲(chǔ)過程執(zhí)行一次后，就駐留在高速緩沖存儲(chǔ)器中，在以后的操作中，只需從高速緩沖存儲(chǔ)器中調(diào)用已編譯好的二進(jìn)制代碼執(zhí)行，從而提高了系統(tǒng)性能。（3）使用存儲(chǔ)過程可以完成所有數(shù)據(jù)庫操作，并可通過編程方式控制對數(shù)據(jù)庫信息訪問的權(quán)限，確保數(shù)據(jù)庫的安全。（4）自動(dòng)完成需要預(yù)先執(zhí)行的任務(wù)。存儲(chǔ)過程可以在SQLServer啟動(dòng)時(shí)自動(dòng)執(zhí)行，而不必在系統(tǒng)啟動(dòng)后再進(jìn)行手工操作，極大方便了用戶的使用，可以自動(dòng)完成一些需要預(yù)先執(zhí)行的任務(wù)。存儲(chǔ)過程的優(yōu)點(diǎn)和類型2.存儲(chǔ)過程的類型在SQLServer中有下列幾種類型的存儲(chǔ)過程。（1）系統(tǒng)存儲(chǔ)過程。是由SQLServer提供的存儲(chǔ)過程，可以作為命令執(zhí)行。系統(tǒng)存儲(chǔ)過程定義在系統(tǒng)數(shù)據(jù)庫master中，其前綴是“sp_”。例如，常用的顯示系統(tǒng)對象信息的sp_help，為檢索系統(tǒng)表的信息提供了方便、快捷的方法。（2）擴(kuò)展存儲(chǔ)過程。擴(kuò)展存儲(chǔ)過程是指在SQLServer環(huán)境之外，使用編程語言（如C++語言）創(chuàng)建的外部例程形成的動(dòng)態(tài)鏈接庫（DLL）。使用時(shí)，先將DLL加載到SQLServer系統(tǒng)中，并且按照使用系統(tǒng)存儲(chǔ)過程的方法執(zhí)行。擴(kuò)展存儲(chǔ)過程在SQLServer實(shí)例地址空間中運(yùn)行。但因?yàn)閿U(kuò)展存儲(chǔ)過程不易撰寫，而且可能會(huì)引發(fā)安全性問題，所以Microsoft公司可能會(huì)在未來的SQLServer中刪除這一功能，本書將不詳細(xì)介紹擴(kuò)展存儲(chǔ)過程。（3）用戶存儲(chǔ)過程。在SQLServer中，用戶存儲(chǔ)過程可以使用T-SQL語言編寫，也可以使用CLR方式編寫。02存儲(chǔ)過程的創(chuàng)建與執(zhí)行創(chuàng)建存儲(chǔ)過程存儲(chǔ)過程的執(zhí)行舉例存儲(chǔ)過程的創(chuàng)建與執(zhí)行1．創(chuàng)建存儲(chǔ)過程創(chuàng)建存儲(chǔ)過程的語句是CREATEPROCEDURE或CREATEPROC，兩者同義。語法格式如下。CREATEPROCEDURE|PROC過程名@參數(shù)數(shù)據(jù)類型[OUTPUT] AS SQL語句塊 /*存儲(chǔ)過程體*/說明：（1）過程名：用于指定存儲(chǔ)過程的名稱，必須符合標(biāo)識(shí)符規(guī)則，且對于數(shù)據(jù)庫及所在架構(gòu)必須唯一。這個(gè)名稱應(yīng)當(dāng)盡量避免與系統(tǒng)內(nèi)置函數(shù)同名，否則會(huì)發(fā)生錯(cuò)誤。另外，也應(yīng)當(dāng)盡量避免使用“sp_”作為前綴，此前綴由SQLServer指定系統(tǒng)存儲(chǔ)過程。（2）@參數(shù)：為存儲(chǔ)過程的形參，@符號(hào)作為第一個(gè)字符來指定參數(shù)名。參數(shù)名必須符合標(biāo)識(shí)符規(guī)則。創(chuàng)建存儲(chǔ)過程時(shí)，可聲明一個(gè)或多個(gè)參數(shù)。（3）數(shù)據(jù)類型：用于指定形參的數(shù)據(jù)類型，形參可為SQLServer支持的任何類型，但游標(biāo)類型只能用于OUTPUT參數(shù)。

（4）OUTPUT：指示參數(shù)為輸出參數(shù)，輸出參數(shù)可以從存儲(chǔ)過程返回信息。（5）SQL語句：代表過程體包含的T-SQL語句，存儲(chǔ)過程體中可以包含一條或多條T-SQL語句，除了DCL、DML與DDL命令外，還能包含過程式語句，如變量的定義與賦值、流程控制語句等。存儲(chǔ)過程的創(chuàng)建與執(zhí)行2．存儲(chǔ)過程的執(zhí)行語法格式如下。EXECUTE|EXEC 過程名|@過程名變量 [參數(shù)值,…|@參數(shù)名=值,…]說明：（1）過程名：要調(diào)用的存儲(chǔ)過程或用戶定義標(biāo)量函數(shù)的完全或者不完全限定名稱。“組號(hào)”用于調(diào)用已定義的一組存儲(chǔ)過程中的某一個(gè)。（2）@過程名變量：局部定義的變量名，保存存儲(chǔ)過程或用戶定義函數(shù)的名稱。（3）@參數(shù)名：為CREATEPROCEDURE或CREATEFUNCTION語句中定義的參數(shù)名，“值”為實(shí)參。如果省略“@參數(shù)名”，則后面的實(shí)參順序要與定義時(shí)參數(shù)的順序一致。存儲(chǔ)過程的創(chuàng)建與執(zhí)行3．舉例1）設(shè)計(jì)簡單的存儲(chǔ)過程【例7.1】從xscj數(shù)據(jù)庫的3個(gè)表中查詢所有學(xué)生課程的成績和學(xué)分。（1）創(chuàng)建存儲(chǔ)過程語句如下：USExscjGOCREATEPROCEDUREstudent_info AS SELECTa.學(xué)號(hào),姓名,課程名,成績,t.學(xué)分 FROMxsbaINNERJOINcjbb ONa.學(xué)號(hào)=b.學(xué)號(hào)INNERJOINkcbt ONb.課程號(hào)=t.課程號(hào)

GO說明：該存儲(chǔ)過程不使用任何參數(shù)。（2）執(zhí)行存儲(chǔ)過程語句如下。EXECUTEstudent_info如果該存儲(chǔ)過程是批處理中的第一條語句，直接寫出過程名即可。student_info存儲(chǔ)過程的創(chuàng)建與執(zhí)行2）使用帶參數(shù)的存儲(chǔ)過程【例7.2】從xscj數(shù)據(jù)庫的3個(gè)表中查詢某學(xué)生指定課程的成績和學(xué)分。該存儲(chǔ)過程接收與傳遞參數(shù)精確匹配的值。語句如下：CREATEPROCEDUREstudent_info1@namechar(8),@cnamechar(16) AS SELECTa.學(xué)號(hào),姓名,課程名,成績,t.學(xué)分 FROMxsbaINNERJOINcjbb ONa.學(xué)號(hào)=b.學(xué)號(hào)INNERJOINkcbt ONb.課程號(hào)=t.課程號(hào)

WHEREa.姓名=@nameANDt.課程名=@cnameGO存儲(chǔ)過程的創(chuàng)建與執(zhí)行執(zhí)行存儲(chǔ)過程student_info1，語句如下。

EXECUTEstudent_info1'王林','計(jì)算機(jī)導(dǎo)論'執(zhí)行結(jié)果如圖。以下命令的執(zhí)行結(jié)果與上面的相同。EXECUTEstudent_info1@name='王林',@cname='計(jì)算機(jī)導(dǎo)論'或者：DECLARE@procchar(20)SET@proc='student_info1'EXECUTE@proc@name='王林',@cname='計(jì)算機(jī)導(dǎo)論'存儲(chǔ)過程的創(chuàng)建與執(zhí)行3）使用帶OUPUT參數(shù)的存儲(chǔ)過程【例7.3】創(chuàng)建一個(gè)存儲(chǔ)過程kc_insert，作用是向kcb表中插入一行數(shù)據(jù)。插入成功，則輸出1，否則輸出0。CREATEPROCEDUREkc_insert@kchchar(3),@kcmchar(16),@xfint,@yesbitOUTPUT AS BEGIN SET@yes=0 INSERTINTOkcb(課程號(hào),課程名,學(xué)分)VALUES(@kch,@kcm,@xf) SET@yes=1 END接下來執(zhí)行存儲(chǔ)過程kc_insert來查看結(jié)果。語句如下。DECLARE@myyesbitEXECdbo.kc_insert'401','就業(yè)指導(dǎo)',2,@myyesOUTPUTSELECT@myyes執(zhí)行結(jié)果顯示“修改成功”。存儲(chǔ)過程的創(chuàng)建與執(zhí)行4）使用帶有通配符參數(shù)的存儲(chǔ)過程【例7.4】從3個(gè)表的連接中返回指定學(xué)生的學(xué)號(hào)、姓名、所選課程名及該課程的成績。該存儲(chǔ)過程在參數(shù)中使用了模式匹配，如果沒有提供參數(shù)，則使用預(yù)設(shè)的默認(rèn)值。語句如下。CREATEPROCEDUREst_info@namevarchar(30)='李%' AS SELECTa.學(xué)號(hào),a.姓名,c.課程名,b.成績 FROMxsbaINNERJOINcjbb ONa.學(xué)號(hào)=b.學(xué)號(hào)INNERJOINkcbc ONc.課程號(hào)=b.課程號(hào)

WHERE姓名LIKE@nameGO執(zhí)行存儲(chǔ)過程，代碼如下。EXECUTEst_info /*參數(shù)使用默認(rèn)值*/或者：EXECUTEst_info'王%' /*傳遞給@name的實(shí)參為'王%'*/存儲(chǔ)過程的創(chuàng)建與執(zhí)行5）使用OUTPUT游標(biāo)參數(shù)的存儲(chǔ)過程OUTPUT游標(biāo)參數(shù)用于返回存儲(chǔ)過程的局部游標(biāo)?！纠?.5】在xscj數(shù)據(jù)庫中的xsb表上聲明并打開一個(gè)游標(biāo)。語句如下。CREATEPROCEDUREst_cursor@st_cursorCURSORVARYINGOUTPUT AS SET@st_cursor=CURSOR FORWARD_ONLYSTATIC FOR SELECT* FROMxsb OPEN@st_cursor說明：VARYING指定作為輸出參數(shù)支持的結(jié)果集。該參數(shù)由存儲(chǔ)過程動(dòng)態(tài)構(gòu)造，其內(nèi)容可能發(fā)生改變，僅適用于游標(biāo)參數(shù)。如果指定參數(shù)的數(shù)據(jù)類型為CURSOR，則必須同時(shí)指定VARYING和OUTPUT關(guān)鍵字。存儲(chǔ)過程的創(chuàng)建與執(zhí)行在如下的批處理中，聲明一個(gè)局部游標(biāo)變量，執(zhí)行上述存儲(chǔ)過程，并將游標(biāo)賦值給局部游標(biāo)變量，然后通過該游標(biāo)變量讀取記錄。語句如下。DECLARE@MyCursorCURSOREXECst_cursor@st_cursor=@MyCursorOUTPUT /*執(zhí)行存儲(chǔ)過程*/FETCHNEXTFROM@MyCursorWHILE(@@FETCH_STATUS=0)BEGIN FETCHNEXTFROM@MyCursorENDCLOSE@MyCursorDEALLOCATE@MyCursor存儲(chǔ)過程的創(chuàng)建與執(zhí)行6）使用WITHENCRYPTION選項(xiàng)WITHENCRYPTION選項(xiàng)用于對用戶隱藏存儲(chǔ)過程的文本?！纠?.6】創(chuàng)建加密過程，使用sp_helptext系統(tǒng)存儲(chǔ)過程獲取加密過程的信息，然后嘗試直接從syscomments表中獲取該過程的信息。語句如下：CREATEPROCEDUREencrypt_thisWITHENCRYPTION AS SELECT*FROMxsb通過系統(tǒng)存儲(chǔ)過程sp_helptext可顯示規(guī)則、默認(rèn)值、未加密的存儲(chǔ)過程、用戶定義函數(shù)、觸發(fā)器或視圖的文本。執(zhí)行如下語句。EXECsp_helptextencrypt_this結(jié)果集為提示信息“對象'encrypt_this'的文本已加密”。03存儲(chǔ)過程的修改存儲(chǔ)過程的修改使用ALTERPROCEDURE命令可修改已存在的存儲(chǔ)過程并保留以前賦予的許可。語法格式如下。ALTER P