數(shù)據(jù)庫2016課件-第9章transact-sql程序設(shè)計

數(shù)數(shù)據(jù)與表達9.1式

Transact-SQL語SQLServer2008數(shù)據(jù)庫管理系統(tǒng)的編程語言為Tranact-SQ語言，這是一種結(jié)構(gòu)化查詢語言，查詢語言SL的實現(xiàn)和擴展。Transact-SL增強了SQ與標準SQL語言的兼容性。式

用戶定義數(shù)據(jù)類SQLServer有4種基本數(shù)據(jù)類型：字符和二進制數(shù)據(jù)、日期/時間數(shù)據(jù)、邏輯數(shù)據(jù)和數(shù)值數(shù)據(jù)。SQLServer也支持SQLServer允許在系統(tǒng)數(shù)據(jù)類型的基礎(chǔ)上建立用戶CREATEDATABASE和ALTERDATABASE語句中定義數(shù)據(jù)表列，并且可以將默認和規(guī)則關(guān)聯(lián)于用戶定義數(shù)據(jù)類型，為用戶定義數(shù)據(jù)類型的列提供默認值和完整性約束。創(chuàng)建用戶定義數(shù)據(jù)類型時，必須提供3作為新數(shù)據(jù)類型基礎(chǔ)的系統(tǒng)數(shù)據(jù)類型和NULL式

用戶定義數(shù)據(jù)類在SQLServer中，提供了兩種方式來創(chuàng)建用戶定義數(shù)據(jù)類型：系統(tǒng)過程和SQLServer管理平臺sp_addtype[@typename=]type,[@phystype=]system_data_type[，[@nulltype=]'null_type'][，[@owner=]'owner_name']用戶定義數(shù)據(jù)類例如EXECsp_addtypetest_type,’Varchar(10)’,’NOT式

用戶定義數(shù)據(jù)類使用SQLServer管理平臺創(chuàng)建用戶定義數(shù)據(jù)類在SQLServer管理平臺中，為Sales數(shù)據(jù)庫創(chuàng)建一個不允許值的test_add用戶定義數(shù)據(jù)類型，操作步驟如下選擇Sales數(shù)據(jù)庫并展開，然后選擇“可編程性”節(jié)點并展開，選擇“類型”并展開，在“用戶定義數(shù)據(jù)類型”節(jié)點上單擊鼠標右鍵，在出現(xiàn)的快捷菜單中選擇“新建用戶定義數(shù)據(jù)類型”命令。彈出如圖9-1所示的“新建用戶定義數(shù)據(jù)類型”窗口。在“用戶定義的數(shù)據(jù)類型屬性”框中的名稱文本框內(nèi)輸入test_add，在“數(shù)據(jù)類型”下拉列表框中，選擇char，在“長度”文本框中輸入10，選中“允許空值”復(fù)選框。單擊“確定”按鈕完成創(chuàng)建用戶自定義數(shù)據(jù)類型式

用戶定義數(shù)據(jù)類2.使用SQLServer管理平臺創(chuàng)建用戶定義數(shù)據(jù)類9.1式

常量與變不能改變其值的數(shù)據(jù)，稱為常量，在程序中經(jīng)常直可以改變其值的數(shù)據(jù)，稱為變量。9.1式

常量與變常字符（0-9）以及特殊字符（如感嘆號（!）、at符（@）和數(shù)字例如：'Cincinnati'、'O''Brien'、'ProcessXiscomplete.'為字符串常9.1式

常量與變1.常日期/時間常datetime常量使用特定格式的字符日期值表示，用單引號括起。表9-1概括了幾種常見的日期時間格式9.1式

常量與變1.常數(shù)值常uniqueidentifier常量①整型常量由沒有用引號括起來且不含小數(shù)點的一串數(shù)字表示。例如，18942014、-1635、+121②浮點常量主要采用科學(xué)記數(shù)法表示，例如，101.5E5、0.5E-2③精確數(shù)值常量由沒有用引號括起來且包含小數(shù)點的一1894.1204、2.0 .0321、-2013.528為精確數(shù)值常量，也稱為定點常。④貨幣常量是以“$”為前綴的一個整型或?qū)嵭统Ａ浚?12.5、$542023.14⑤uniqueidentifier常量是表示全局唯一標識符GUID值的字符串?？梢允褂?.1式

常量與變常邏輯數(shù)據(jù)常邏輯數(shù)據(jù)常量使用數(shù)字0或1表示，并且不使用引號。非0數(shù)字當作1處理空在數(shù)據(jù)列定義之后，還需確定該列是否允許空值。允許空值意味著用戶在向表中數(shù)據(jù)時可以忽略該列值?？罩悼梢员硎菊?、實型、字符型數(shù)據(jù)。9.1式

9.1.2常量與變變變量用于臨時存放數(shù)據(jù)，變量中的數(shù)據(jù)隨著程型與常量相同，但變量名不允許與函數(shù)名或命令名相同。變量名使用常規(guī)標識符，即以字母、下線（_）at符號（@）、數(shù)字符號（#）開頭，后續(xù)字母、數(shù)字at符號、符號$）、下劃線的字符序列。不允許嵌入空格或其他特殊字符。9.1式

9.1.2常量與變變SQLServer2008將變量分為全局變量和局部變量兩類，其中全局變量由系統(tǒng)定義并，通過在名稱前面加“@@”局部變局部變量是指作用域局限在一定范圍內(nèi)的變量，相對于局變量，局部變量需要 后使用式

常量與變2.變創(chuàng)建局部變量使用DECLARE語句，局部變量的定義僅存在于它的批處理、過程或觸發(fā)器中，處理結(jié)束后，在局部變DECLARE語句的語法格式如下DECLARE{@local_variabledata_type3個字符。一條DCLAE語句可以定義多個變量，各變量之間使用逗號隔開。DECLARE@namevarchar(30),@type數(shù)數(shù)據(jù)與表達式

常量與變局部變量的賦值：可以通過SELT、PTE和SE語句進行。數(shù)數(shù)據(jù)與表達

常量與變在一條語句中可以同時對幾個變量進行賦 例如DECLARE@LastName char(8),@BirthDate SELECT 式

常量與變【例9-1】使用SELECT語句從customer表中檢索出顧客DECLARE@customervarchar(40),@curdateFROMcustomerWHERE式

常量與變用UPDATE為局部變量賦92】將ll_od表中的anspo_id列值為“T001”、goodd列值為“00003的od_nu列的值賦給局部變量d_nu。DECLARE@order_numfloatUPDATEsell_orderSET@order_num=order_num*2WHEREtransporter_id='T001'AND9.1數(shù)據(jù)與表達常量與變式數(shù)數(shù)據(jù)與表達式

常量與變變?nèi)肿儠捚陂g的信息，不能顯式地被賦值或。式

常量與變2.變（2）全局變量表9-2列出了SQLServer中常用的全局變量9.1式

運算符與表達運算符用來執(zhí)行數(shù)據(jù)列之間的數(shù)算或比較操作。Transact-SQL運算符可以分為算術(shù)運算式可以是一個常量、變量、列、運算符或函數(shù)，復(fù)9.1式

運算符與表達算術(shù)運算符與表達算術(shù)運算符用于數(shù)值型列或變量間的算術(shù)運算。算術(shù)運算符包括加（+）、減（-）、乘（*）、除（/）和取模（%）運算等。如表93所示列出了所有算術(shù)運算符及其可操作的數(shù)據(jù)類型。加（+）和減（-）運算符也可用于對datetime及Smalldatetime值執(zhí)行算術(shù)運算。9.1式

運算符與表達位運算符與表達位運算符用以對數(shù)據(jù)進行按位與（＆）、或（|）、異或（^）、求反~）等運算。在Transact-SQL語句中，進行整型數(shù)據(jù)的位運算時，SQLServer先將它們轉(zhuǎn)換為二進制數(shù)，然后再進行計算。其中與、或、異或運算符需要兩個操作數(shù)，求反運算符僅需要一個操作數(shù)。如表9-4所示列出了位運算符及其可操作的數(shù)據(jù)類型。9.1式

運算符與表達比較運算符與表達比較運算符也稱為關(guān)系運算符，用來比較兩個表達式的值之間的系，可用于字符、數(shù)字或日期數(shù)據(jù)。SQLServer中的比較運算符（>）、小于（<）、大于等于（>=）、小于等于（<=）和不等于等，比較運算返回布爾值，通常出現(xiàn)在條件表達式中比較運算的結(jié)果為布爾數(shù)據(jù)類型，根據(jù)表達式的輸出結(jié)果，返TRUE、FALSE及UNKNOWN值例如，表達式2=3的運算結(jié)果為FALSE一般情況下，帶有一個或兩個NULL表達式的運算符返回。當SETANSI_NULLS為OFF且兩個表達式都為NULL，那么“=”運算符回TRUE式

運算符與表達邏輯運算符與表達邏輯運算符與（AND）、或（OR）、非（NOT）等，用于對某個條件進行測試，以獲得其真實情況。邏輯運算符和比較運算符一樣，返回TRUE或FALSE的布爾數(shù)據(jù)值。如下表所示列出邏輯運算符及其運算情況。例如，NOTTRUE為假；TRUEANDFALSE為假；TRUEORFALSE為真邏輯運算符通常和比較運算一起構(gòu)成更為復(fù)雜的表達式。與比較運算不同的是，邏輯運算符的操作數(shù)都只能是布爾型數(shù)據(jù)式

運算符與表達邏輯運算符與表達LIKE運算符：確定給定的字符串是否與指定的模式匹，通常只限于字符數(shù)據(jù)類型。模式可以使用通配符字符如表9-6所示，它們使LIKE式

運算符與表達連接運算符與表達常也稱為字符串運算符。在SQLServer中，對字符式

運算符與表達運算符的優(yōu)先級不同運算符具有不同的運算優(yōu)先級，在一個表達式，運算符的優(yōu)先級決定了運算的順序。SQLServer中各運算符的優(yōu)先順序如下~^|*→AND→排面的運算符的優(yōu)先級高于其后的運算符。在一個表達式中，先計算優(yōu)先級較高的運算，后計算優(yōu)先級低的運算，相同優(yōu)先級的運算按自左向右的順序依次進行。函函1

函數(shù)是一組編譯好的Transact-SQL語句，它們可以帶一個或一組數(shù)值做參數(shù)，也可不帶參數(shù)，它返回一個數(shù)值、數(shù)值集合，或執(zhí)行一些操作。函數(shù)能夠重復(fù)執(zhí)行一些操作，從而避免不斷重寫代碼。SQLServer2008支持兩種函數(shù)類型：內(nèi)置函數(shù)和用戶，該實體可以由Transact-SQL常用函

在SQLServer中，函數(shù)主要用來獲得系統(tǒng)的的轉(zhuǎn)換等。SQLServer2008提供的函數(shù)包括字

常用函字符串函用來實現(xiàn)對字符型數(shù)據(jù)的轉(zhuǎn)換、查找、分析等操作，通常用作字符串表達式一部分。如表9-7所示列出了SQLServer的常用字符串函

常用函數(shù)字函數(shù)學(xué)函數(shù)用來實現(xiàn)各種數(shù)算，如指數(shù)運算、對數(shù)運算、三角運算等，其操作數(shù)為數(shù)值型數(shù)據(jù)，如infloatalmoe等。如表98所示列出了常用的20多個數(shù)學(xué)函數(shù)。

常用函日期函日期和時間函數(shù)用來操作dtetie和maldattime類型的數(shù)據(jù)，執(zhí)行算術(shù)運算。與其他函數(shù)一樣，可以在SELET語句和WHE子句以及表達式中使用日期和時間函數(shù)。如表9-9所示列出了系統(tǒng)提供的個日期和時間函數(shù)。

常用函系統(tǒng)函系統(tǒng)函數(shù)用于獲取有關(guān)計算機系統(tǒng)、用戶、數(shù)據(jù)庫和數(shù)據(jù)庫對象的信息。與他函數(shù)一樣，可以在SELECT和WHERE子句以及表達式中使用系統(tǒng)函數(shù)。表9-11所示列出了SQLServer2008常見的系統(tǒng)函數(shù)

用戶定義函用戶定義函數(shù)是用戶定義的Transact-SQL函數(shù)，它可以擁有零、一個或多個輸入?yún)?shù)，必須返回單一的返回值。返回值可以是單個數(shù)值，也可以是一個表。但是，自定義函數(shù)不支持輸出參數(shù)。在SQLServer2008中，根據(jù)函數(shù)返回值形式的不同將用戶定義函數(shù)分為3種類型。值類型為SQLServer2008的系統(tǒng)數(shù)據(jù)類型（除tex 、timestamp、table類型外）。函數(shù)體語句定義在BEGIN…END語句內(nèi)內(nèi)嵌表值自定義函數(shù)。內(nèi)嵌表值函數(shù)返回的函數(shù)值為一個表。內(nèi)嵌表值函數(shù)的函數(shù)體不使用BEGIN…END語句，其返回的表是RETURN子句中的SELECT命令查詢的結(jié)果集，其功能相當于一個提供參數(shù)化的視圖。多語句表值自定義函數(shù)。多語句表值函數(shù)可以看作標量函數(shù)和內(nèi)嵌表BEGIN…END定義，返回值的表中的數(shù)據(jù)由函數(shù)體中的語句。因此，多語句表值函數(shù)可以進行多次查詢，彌補了內(nèi)嵌表值自定義函數(shù)的不足。9.2

用戶定義函創(chuàng)建用戶定義函創(chuàng)建用戶定義函數(shù)可以使用SQLServer管理平臺，也可以使Transact-SQL語句CREATEFUNCTION實現(xiàn)（1）使用CREATEFUNCTION創(chuàng)建用戶定義函SQLServer2008根據(jù)用戶定義函數(shù)類型的不同提供了不同創(chuàng)建函數(shù)的格式標量函數(shù)的語法格式如下CREATEFUNCTION[owner_name.]([{@parameter_name[AS]scalar_parameter_data_type[=default]}，…n]]RETURNS[WITH<function_option>[[,]…n][ASRETURN用戶定義函1函

（1）使用CREATEFUNCTION創(chuàng)建用戶定義函內(nèi)嵌表值函數(shù)的語法格式如下CREATEFUNCTION[owner_name.]([{@parameter_name[AS]scalar_parameter_data_type[=default]}[，…n]])RETURNS[WITH<function_option>[[,]…n][ASRETURN[(]select_stmt[)用戶定義函1義函

使用CREATEFUNCTION創(chuàng)建用戶定義函多語句表值函數(shù)的語法格式如下CREATEFUNCTION[owner_name.]([{@parameter_name[AS]scalar_parameter_data_type[default]}[，…n]]RETURNS@return_variableTABLE[WITH<function_option>[[,]…n][AS]9.2

用戶定義函1義函【例-】創(chuàng)建一個用戶定義函數(shù)toQutr，將輸入的日期數(shù)據(jù)轉(zhuǎn)換為該日期對應(yīng)的季度值。如輸入'20--'，返回'3Q，表示年季度。

9.29.2.2用戶定義函創(chuàng)建用戶定義函【例9-19】創(chuàng)建用戶定義函數(shù)goodsq，返回輸入商品的商品名函函

9.2.2用戶定義函執(zhí)行用戶定義函在創(chuàng)建了新函數(shù)后，就可以使用了。使用函數(shù)的方法幾乎與SQLSeer2008內(nèi)置函數(shù)完全一樣，除了需要函數(shù)所有者外，即為函數(shù)加上所有者權(quán)限作為前綴。其語法格式如下([argument_expr][，…])例如調(diào)用例9-18創(chuàng)建的用戶定義函數(shù)DatetoQuarter，使用以下語：SELECTdbo.DatetoQuarter('2011-8-運行結(jié)果為9.2.2用戶定義函修改和刪除用戶定義函

用QLv管理平臺中修改用戶定義函數(shù)，選擇要修改函數(shù)，單擊右鍵，從快捷菜單中選擇“修改”選項，打開圖-所示的“用戶定義函數(shù)屬性”編輯框。在該框中可以修改用戶定義函數(shù)的函數(shù)體、參數(shù)等。從快捷菜單中選擇“刪除”選項，則可刪除用戶定義函數(shù)。用TRFUNCTION命令也可以修改用戶定義函數(shù)。此命令的語法與RTFNTION相同，也分為標量函數(shù)、內(nèi)嵌表值函數(shù)、多語句表值函數(shù)定義格式，因此使用TRFNTION命令其實相當于重建一個同名的函數(shù)。

9.2.2用戶定義函3用戶定義函使用DROPFUNCTION命令刪除用戶定義函數(shù)的語法為DROPFUNCTION{[owner_name.]function_name}，…n其中，funone是要刪除的用戶定義的函數(shù)名稱。可以選擇是否指定所有者名稱，但不能指定服務(wù)器名稱和數(shù)據(jù)庫名稱。例如，刪除例9-18創(chuàng)建的用戶定義函數(shù)，使用以下語句DROPFUNCTON刪除用戶定義函數(shù)時，可以不加所有者前綴程程序控制流語

環(huán)、過程、觸發(fā)器等程序設(shè)計，編寫結(jié)構(gòu)化

語句塊和注1句塊BEGIN…END用來設(shè)定一個語句塊，將在BEGIN…END{sql_statement|statement_block塊Transact-SQL語句的結(jié)尾。{sql_statement|statement_block}是任何有從語法格式上講，控制流語句IF、WHILE句。但在實際程序設(shè)計時，一條語句往往不能滿足復(fù)雜的程序設(shè)計要求，這，就需要使用BEGIN…END語句將一條以上SQL語句封閉起來，構(gòu)成一個句塊，在處理時，整個語句塊被視為一條語句

語句塊和注1句塊【例9-21】顯示Sales數(shù)據(jù)庫中customer表的編號為C0001的聯(lián)系人。USEDECLARE@linkman_nameSELECT@linkman_name=(SELECTlinkman_nameFROMWHEREcustomer_idLIKE'C0001')SELECT@linkman_name

語句塊和注注在源代碼中加入注釋可以幫助讀者理解程序員的意圖，也可以標識出SQ源代碼中開發(fā)者出于某些原因不想執(zhí)行的那部分。有兩種方法來注釋：多行注釋選擇控

SQLServer提供了多個根據(jù)檢查條件值并改用的控制流語句，CASE函數(shù)可以判斷多個條件，GOTO語句無條件地改變流程，RTURN語句會將當前正在執(zhí)行的批處理、過程等中斷。9.3

選擇控條件執(zhí)行語句通常計算機按順序執(zhí)行程序中的語句。但在許多情況下，語句執(zhí)行的順序以及是否執(zhí)行依賴于程序運行的中間結(jié)果IF…ELSE的語法格IF{sql_statement|statement_block--條件表達式為真時執(zhí)[{sql_statement|statement_block--條件表達式為假時執(zhí)選擇控1

【例9-23】判斷表goods中r_id為S001的IF(SELECTavg(unit_price)FROMWHERESELECT'r_id為S001的商品的平均單價比9799大SELECT'r_id為S001的商品的平均單價比9799小r_id為S001的商品的平均單價比9799

9.3.2選擇控1.條件執(zhí)行語句【例9-24】用EXISTS確定表department中是否存在“”DECLARE@lnamevarchar(40),@msgvarchar(255)SELECT@lname=''IFEXISTS(SELECT*FROMdepartmentSELECT@msg='有人名為SELECTSELECT@msg='沒有人名為SELECT9.3.2選擇控1【例9-25】嵌套IF…ELSE

IF(SELECTSUM(order_num)FROMPRINT他們是最佳的客戶'IF(SELECTSUM(order_numFROMsell_order)>30PRINT'必須與他們保持聯(lián)絡(luò)'PRINT再想想辦法吧9.3

9.3.2選擇控CASE如果有多個條件要判斷，可以使用到多個嵌套的IF…ELSE語句，但這樣會造成日后及除錯的，此時使CASE函數(shù)來取代多個嵌套的IF…ELSE語句較為合適。CASE函數(shù)計算多個條件并為每個條件返回單個值。CASE具有如下兩種格式。(1）簡單CASE函數(shù)：將某個表達式與一組簡單表達式進行比較以確定結(jié)果。CASEWHENwhen_expressionTHEN[…n[ELSEelse_result_expression9.3

9.3.2選擇控2CASECASE搜索函數(shù)，CASE計算一組邏輯表達式以確定結(jié)果。WHENBoolean_expressionTHEN[…n[ELSEelse_result_expression]9.3

9.3.2選擇控2CASE【例9-26】使用簡單CASE函數(shù)將goods表中的CASEWHENF00001THEN激光打印復(fù)印機WHENF00002THEN投影儀'WHENF00003THEN掃描儀'WHENF00004THEN路由器ELSE'沒有這種品牌ENDASgoods_nameAS'GoodsName',unit_priceASPriceFROMgoodsWHEREunit_priceISNOT9.3

9.3.2選擇控2CASE【例9-26】使用簡單CASE函數(shù)將goods表中的運行結(jié)果如圖9-5所示9.3

9.3.2選擇控2CASE【例9-27根據(jù)goods表中庫存貨物數(shù)量與訂貨量SELECTgoods_nameAS商品名WHENstock_ty-order_ty<=3THEN'緊急進貨WHENstock_ty-order_andstock_ty-order_ty<=10THEN'暫緩進貨WHENstock_ty-order_ty>10THEN'貨物充足'ENDAS進貨判斷FROM9.3

9.3.2選擇控CASE。【例9-27】根據(jù)goods表中庫存貨物數(shù)量與訂貨量。9.3

9.3.2選擇控跳轉(zhuǎn)語句GOTO語句將允許程序的執(zhí)行轉(zhuǎn)移到處，尾隨在GOTO語句之后的Transact-SQL語句被忽略，而從繼續(xù)GOTO語句破壞了程序結(jié)構(gòu)化的特點，使程序結(jié)構(gòu)變得復(fù)雜而且難以測試。事實上，使用GOTO語句的程序可以用其他語句來所以盡量少使用GOTOGOTOlabel其中，label為GOTO語句處理的起點。label必須符合標識符規(guī)則9.3

9.3.2選擇控跳轉(zhuǎn)語句DECLARE@xSELECTSELECTSELECTWHILEGOTO程序通過GOTOlabel_1語句實現(xiàn)循環(huán)，當@x<6時，執(zhí)行9.3

9.3.2選擇控RETURN語GOTORETURN語句可使程序從批處理、過程或觸發(fā)器無條件退出，不再執(zhí)行本語句之后的任何語句RETURNinteger_expression其中，integer_expression是返回的整型值。過程可以給調(diào)用過程或應(yīng)用程序返回整型值，參見10章過程相關(guān)內(nèi)容。

選擇控4RETURN語DECLARE@xint,@ySELECTIF@x>@y如果沒有指定返回值，SQLServer系統(tǒng)會根據(jù)程序9.3.2選擇控RETURN語9.39.3

9.3.2選擇控調(diào)度執(zhí)行語句在SQLServer中有兩種方法可以調(diào)度執(zhí)行批處理或者存儲過程?！N方法是基于SQLServerAgent的使用；另一WAITFORWAITFOR{DELAY'time'|TIME這個語句中有兩個變量。DELAY'time'指定執(zhí)行繼續(xù)進，指定的時間間隔必須小于24小時9.3

循環(huán)控在WHILE語句根據(jù)條件表達式設(shè)置Transact-SQL語句或語句塊重復(fù)執(zhí)行的次數(shù)。如果所設(shè)置的條件為真TRUE）時，在WHILE循環(huán)體內(nèi)的Transact-SQL語句會一直重復(fù)執(zhí)行，直到條件為假（FALSE）為止。在WHILE循環(huán)內(nèi)Transact-SQL語句的執(zhí)行可以使用BREAK與CONTINUE關(guān)鍵詞來控制。WHILEboolean_expression{}[BREAK[][CONTINUE循環(huán)控【例9-32計算s=1!+2!+…+10!DECLARE@sint,@nint,@tint,@c9.3

SETSET@n