數(shù)據(jù)庫應(yīng)用技術(shù)：第10章_Transact-SQL程序設(shè)計(jì)

1、第10章 Transact-SQL程序設(shè)計(jì) 本章內(nèi)容10.1 Transact-SQL語言基礎(chǔ) 10.2 表達(dá)式10.3 函數(shù)10.4 流程控制語句10.5 游標(biāo) 10.1 Transact-SQL語言基礎(chǔ) 10.1.1 Transact-SQL語言的編程功能 10.1.2 標(biāo)識符 10.1.3 注釋 10.1.4 語句塊 10.1.1 Transact-SQL語言的編程功能 Transact-SQL語言是在微軟公司的SQL Server中使用的編程語言，它是一個(gè)數(shù)據(jù)定義、操作和控制的語言。(1)基本功能 支持ANSI SQL-92標(biāo)準(zhǔn)：DDL數(shù)據(jù)定義，DML數(shù)據(jù)操縱，DCL數(shù)據(jù)控制，DD數(shù)據(jù)

2、字典。 (2)擴(kuò)展功能加入程序流程控制結(jié)構(gòu)。加入局部變量和系統(tǒng)變量等 10.1.2 標(biāo)識符 數(shù)據(jù)庫對象的名稱即為其標(biāo)識符 。服務(wù)器、數(shù)據(jù)庫和數(shù)據(jù)庫對象（例如表、視圖、列、索引、觸發(fā)器、過程、約束及規(guī)則等）都可以有標(biāo)識符 。1. 標(biāo)識符命名規(guī)則 標(biāo)識符包含的字符數(shù)必須在1到128之間，標(biāo)識符的命名需要滿足以下規(guī)則： (1)標(biāo)識符的第一個(gè)字符必須是：大寫字母、小寫字母、下劃線、和# 。其中以符號開頭的常規(guī)標(biāo)識符表示局部變量或參數(shù)，并且不能用作任何其他類型的對象的名稱。以#開頭的標(biāo)識符表示臨時(shí)表或過程，以#開頭的標(biāo)識符表示全局臨時(shí)對象。 (2)后續(xù)字符必須是符合Unicode 2.0(統(tǒng)一碼)標(biāo)準(zhǔn)的

3、字母，或者是十進(jìn)制數(shù)字，或是特殊字符、#、_、$。 (3)標(biāo)識符不能與任何SQL Server保留字匹配。標(biāo)識符不能包含空格或別的特殊字符。2. 對象命名規(guī)則 所有數(shù)據(jù)庫對象的引用由下面四部分構(gòu)成 ：server_name.database_name.schema_name.object_name| database_name.schema_name.object_name| schema_name.object_name| object_name其中：server_name：指定鏈接的服務(wù)器名稱或遠(yuǎn)程服務(wù)器名稱。 database_name：如果對象駐留在SQL Server的本地實(shí)例中，則

4、指定SQL Server數(shù)據(jù)庫的名稱。如果對象在鏈接服務(wù)器中，則database_name將指定OLE DB目錄 。schema_name：如果對象在SQL Server數(shù)據(jù)庫中，則指定包含對象的架構(gòu)的名稱。如果對象在鏈接服務(wù)器中，則schema_name將指定OLE DB架構(gòu)名稱 。 object_name：對象的名稱 。 數(shù)據(jù)庫架構(gòu)是一個(gè)獨(dú)立于數(shù)據(jù)庫用戶的非重復(fù)命名空間可以將架構(gòu)視為對象的容器。可以在數(shù)據(jù)庫中創(chuàng)建和更改架構(gòu)，并且可以授予用戶訪問架構(gòu)的權(quán)限。任何用戶都可以擁有架構(gòu)，并且架構(gòu)所有權(quán)可以轉(zhuǎn)移 。 在SQL Server 2005中，每個(gè)對象都屬于一個(gè)數(shù)據(jù)庫架構(gòu) 。10.1.3 注

5、釋 注釋是程序代碼中不執(zhí)行的文本字符串，也稱為備注 。注釋通常用于記錄程序名、作者姓名和主要代碼更改的日期，注釋可用于描述復(fù)雜的計(jì)算或解釋編程方法。 SQL Server支持兩種類型的注釋字符： -（雙連字符） ：這些注釋字符可與要執(zhí)行的代碼處在同一行，也可另起一行。對于多行注釋，必須在每個(gè)注釋行的前面使用雙連字符。/* . */（正斜杠-星號字符對）：對于多行注釋，必須使用開始注釋字符“/*”來開始注釋，并使用結(jié)束注釋字符“*/”來結(jié)束注釋。 10.1.4 語句塊 語句塊是由BEGIN 和 END括起來的一系列的Transact-SQL語句，作為一個(gè)邏輯單元執(zhí)行。 語法格式如下： BEGIN

6、 sql_statement | statement_block END其中： sql_statement | statement_block 是使用語句塊定義的任何有效的Transact-SQL語句或語句組 。 BEGIN.END語句塊允許嵌套。10.2 表達(dá)式10.2.1 常量10.2.2 變量10.2.3 運(yùn)算符9.1 數(shù)據(jù)與表達(dá)式10.2.1 常量 在程序運(yùn)行中保持常值的數(shù)據(jù)，即程序本身不能改變其值的數(shù)據(jù)，稱為常量，在程序中經(jīng)常直接使用文字符號表示。 根據(jù)常量的類型不同分為字符串常量、整型常量、日期時(shí)間型常量、實(shí)型常量、貨幣常量、全局唯一標(biāo)識符。1. 字符串常量 字符串常量分為ASCI

7、I字符串常量、UNICODE字符串常量。 ASCII字符串常量：用單引號括起來，由ASCII構(gòu)成的字符串。如：abcde。 UNICODE字符串常量：以字符N開頭，如Nabcde。 9.1 數(shù)據(jù)與表達(dá)式 字符串常量必須放在單引號或雙引號中。由字母、數(shù)字、下劃線、特殊字符(!，#)組成。 當(dāng)單引號括住的字符串常量中包含單引號時(shí)，用兩個(gè)單引號表示字符串中的單引號。如：Im ZYT寫作Im ZYT。 UNICODE(統(tǒng)一碼、萬國碼、單一碼)是一種在計(jì)算機(jī)上使用的字符編碼。它為每種語言中的每個(gè)字符設(shè)定了統(tǒng)一并且唯一的二進(jìn)制編碼，以滿足跨語言、跨平臺(tái)進(jìn)行文本轉(zhuǎn)換、處理的要求 。9.1 數(shù)據(jù)與表達(dá)式 2.

8、 整型常量二進(jìn)制整型常量：由0、1組成，如111001。十進(jìn)制整型常量：如1982。十六進(jìn)制整型常量：用0 x開頭，如0 x3e，0 x，只有0 x表示空十六進(jìn)制數(shù)。3. 日期時(shí)間型常量datetime常量使用特定格式的字符日期值表示，用單引號括起來。輸入時(shí)，可以使用“/”、“.”、“-”作日期/時(shí)間常量的分隔符。默認(rèn)情況下，按照mm/dd/yy(月/日/年)的格式來處理。 輸入格式datetime值Smalldatetime值Sep 3, 2008 1:34:34.1222008-09-03 01:34:34.1232008-09-03 01:35:009/3/2008 1PM2008-09

9、-03 13:00:00.0002008-09-03 13:00:009.3.2008 13:002008-09-03 13:00:00.0002008-09-03 13:00:0013:25:191900-01-01 13:25:19.0001900-01-01 13:25:009/3/20082008-09-03 00:00:00.0002008-09-03 00:00:009.1.2 常量與變量4. 實(shí)型常量 實(shí)型常量有純小數(shù)和指數(shù)形式兩種。如165.234，10E23。5. 貨幣常量 用貨幣符號開頭。如$12.5，$54230.25。SQL Server不強(qiáng)制分組，如每隔三個(gè)數(shù)字插一個(gè)

10、逗號等。 6. 全局唯一標(biāo)識符 全局唯一標(biāo)識符(Globally Unique Identification Numbers，GUID)是16字節(jié)長的二進(jìn)制數(shù)據(jù)類型，是SQL Server根據(jù)計(jì)算機(jī)網(wǎng)絡(luò)適配器地址和主機(jī)時(shí)鐘產(chǎn)生的唯一號碼生成的全局唯一標(biāo)識符。9.1.2 常量與變量9.1.2 常量與變量 GUID主要用于在擁有多個(gè)節(jié)點(diǎn)、多臺(tái)計(jì)算機(jī)的網(wǎng)絡(luò)或系統(tǒng)中分配必須具有唯一性的標(biāo)識符 。世界上的任何兩臺(tái)計(jì)算機(jī)都不會(huì)生成重復(fù)的GUID值 。7. 邏輯數(shù)據(jù)常量 邏輯數(shù)據(jù)常量使用數(shù)字0或1表示，并且不使用引號定界。非0的數(shù)字當(dāng)作1處理。 8. 空值 在定義數(shù)據(jù)列時(shí)，需定義該列是否允許空值(NULL)

11、。允許空值意味著用戶在向表中輸入數(shù)據(jù)時(shí)可以忽略該列值?？罩悼梢员硎菊汀?shí)型、字符型數(shù)據(jù)。變量用于臨時(shí)存放數(shù)據(jù)，變量中的數(shù)據(jù)隨著程序的運(yùn)行而變化，變量有名字與數(shù)據(jù)類型兩個(gè)屬性。變量的命名使用常規(guī)標(biāo)識符，即以字母、下劃線(_)、at符號()、數(shù)字符號(#)開頭，后續(xù)字母、數(shù)字、at符號、美元符號($)、下劃線的字符序列。不允許嵌入空格或其他特殊字符。10.2.2 變量9.1.2 常量與變量全局變量由系統(tǒng)定義并維護(hù)，通過在名稱前面加“”符號局部變量的首字母為單個(gè)“”。全局變量和局部變量9.1.2 常量與變量1. 局部變量 局部變量是作用域局限在一定范圍內(nèi)的Transact-SQL對象。作用域：若局

12、部變量在一個(gè)批處理、存儲(chǔ)過程、觸發(fā)器中被聲明或定義，則其作用域就在批處理、存儲(chǔ)過程或觸發(fā)器內(nèi)。(1) 局部變量的聲明或定義DECLARE local_variable AS data_type ,.n其中：local_variable：變量的名稱。變量名必須以開頭。data_type：數(shù)據(jù)類型 變量先聲明或定義，然后就可以在Transact-SQL命令中使用。默認(rèn)初值NULL。(2)局部變量的賦值 用SET或SELECT為局部變量賦值SET local_variable = expression或 SELECT local_variable = expression或 SELECT local

13、_variable =output_value FROM table_name WHERE .各選項(xiàng)含義如下： local_variable：是除cursor，text，ntext，image外的任何類型變量名 。 expression：表達(dá)式是任何有效的SQL Server表達(dá)式。 output_value：用于將單個(gè)值返回到變量中 。 注意：如果output_value為列名，則返回多個(gè)。若SELECT語句返回多個(gè)值，則將返回的最后一個(gè)值賦給變量。若SELECT語句沒有返回值，變量保留當(dāng)前值；若output_value是不返回值的子查詢，則變量為NULL。 9.1.2 常量與變量例如：通過

14、SELECT命令賦值，查詢學(xué)號為S1的學(xué)生姓名。 USE teachingGODECLARE var1 varchar(8) SELECT var1=SNAME FROM student WHERE SNO=S1SELECT var1 AS 學(xué)生姓名執(zhí)行結(jié)果如圖10.1所示。9.1.2 常量與變量例如：使用SELECT命令賦值，結(jié)構(gòu)為多個(gè)返回值時(shí)取最后一個(gè)值。 USE teachingGODECLARE var1 varchar(8)SELECT var1=SNAME FROM studentSELECT var1 AS 讀者姓名執(zhí)行結(jié)果如圖10.2所示。9.1.2 常量與變量2. 全局變量系

15、統(tǒng)全局變量是SQL Server系統(tǒng)提供并賦值的變量。用戶不能建立全局變量，也不能用SET語句改變?nèi)肿兞康闹怠?全局變量記錄SQL Server服務(wù)器活動(dòng)狀態(tài)的一組數(shù)據(jù)。例如：ERROR 表示最后一個(gè)Transact-SQL命令錯(cuò)誤的錯(cuò)誤號；SERVERNAME 表示本地服務(wù)器的名稱。具體見表10.2常用的SQL Server全局變量 全局變量由開始，由系統(tǒng)定義和維護(hù)，用戶只能顯示和讀取，不能修改 。 例如：顯示SQL Server的版本 。SELECT version AS 版本號執(zhí)行結(jié)果如圖10.4所示。 9.1.2 常量與變量例如：使用spid返回當(dāng)前用戶進(jìn)程的ID。SELECT sp

16、id as ID,SYSTEM_USER AS Login Name,USER AS User Name運(yùn)行結(jié)果：ID Login Name User Name53 sa dbo9.1.2 常量與變量10.2.3 運(yùn)算符運(yùn)算符用于指定要在一個(gè)或多個(gè)表達(dá)式中執(zhí)行的操作 。將變量、常量和函數(shù)連接起來，構(gòu)成表達(dá)式。 下表列出了SQL Server 2005的運(yùn)算符 優(yōu)先級運(yùn)算符類別所包含運(yùn)算符1一元運(yùn)算符+(正)、-(負(fù))、(取反)2算術(shù)運(yùn)算符*(乖)、/(除)、%(取模)3算術(shù)字符串運(yùn)算符 +(加)、-(減)、+(連接)4比較運(yùn)算符=(等于)、(大于)、=(大于等于)、(小于)、=(小于等于)、(

17、 或!=不等于)、!(不大于) 5按位運(yùn)算符&(位與)、|(位或)、(位異或) 6邏輯運(yùn)算符NOT(非) 7邏輯運(yùn)算符AND(與)8邏輯運(yùn)算符ALL(所有)、ANY(任意一個(gè))、BETWEEN(兩者之間)、EXISTS(存在)、IN(在范圍內(nèi))、LIKE(匹配)、OR(或)、SOME(任意一個(gè)) 9賦值運(yùn)算符=(賦值) SQL Server 2005運(yùn)算符 10.3 函 數(shù)10.3.1 內(nèi)置函數(shù)10.3.2 用戶定義函數(shù)函數(shù)是組編譯好的Transact-SQL語句，它們可以帶一個(gè)或一組數(shù)值做參數(shù)，也可不帶參數(shù)，它返回一個(gè)數(shù)值、數(shù)值集合，或執(zhí)行一些操作。函數(shù)能夠重復(fù)執(zhí)行一些操作，從而避免不斷重寫

18、代碼。SQL Server 2005支持兩種函數(shù)類型：(1) 內(nèi)置函數(shù):是一組預(yù)定義的函數(shù)，是Transact-SQL語言的一部分，按Transact-SQL參考中定義的方式運(yùn)行且不能修改。(2) 用戶定義函數(shù):由用戶定義的Transact-SQL函數(shù)。它將頻繁執(zhí)行的功能語句塊封裝到一個(gè)命名函數(shù)中，該函數(shù)可以由Transact-SQL語句調(diào)用。9.2.1 常用函數(shù)10.3.1 內(nèi)置函數(shù)1字符串函數(shù) 字符串函數(shù)用來實(shí)現(xiàn)對字符型數(shù)據(jù)的轉(zhuǎn)換、查找、分析等操作，對字符串輸入值執(zhí)行操作，返回字符串或數(shù)字值 。 (1) ASCII()函數(shù) 返回字符表達(dá)式最左端字符的ASCII碼值。語法格式如下。 ASCI

19、I ( character_expression ) 其中：character_expression為char或varchar類型的表達(dá)式。例如：以下示例為ASCII()函數(shù)的使用。 DECLARE StringTest CHAR(10) SET StringTest=ASCII(Robin ) SELECT StringTest 執(zhí)行結(jié)果為： 829.2.1 常用函數(shù)(2) CHAR()函數(shù) 將int型的ASCII碼轉(zhuǎn)換為字符的字符串函數(shù)。語法格式如下。 CHAR ( integer_expression ) 其中：integer_expression是介于0和255之間的整數(shù)。如果該整數(shù)表

20、達(dá)式不在此范圍內(nèi)，將返回NULL值。例如：以下示例為CHAR()函數(shù)的使用。 DECLARE StringTest CHAR (10) SET StringTest=ASCII(Robin ) SELECT CHAR(StringTest) 執(zhí)行結(jié)果為： R 9.2.1 常用函數(shù)(3) LEFT()函數(shù) 返回從字符串左邊開始指定個(gè)數(shù)的字符。語法格式如下。 LEFT ( character_expression , integer_expression )其中： character_expression：字符或二進(jìn)制數(shù)據(jù)表達(dá)式?？梢允浅Ａ?、變量或列。可以是任何能夠隱式轉(zhuǎn)換為varchar或nva

21、rchar的數(shù)據(jù)類型，但text或ntext除外。integer_expression：正整數(shù)，指定character_expression將返回的字符數(shù)。如果為負(fù)，則會(huì)返回錯(cuò)誤。integer_expression可以是bigint類型。 9.2.1 常用函數(shù)例如：以下示例為LEFT()函數(shù)的使用。 DECLARE StringTest CHAR (10) SET StringTest=Robin SELECT LEFT(StringTest,3) 執(zhí)行結(jié)果為： Rob9.2.1 常用函數(shù)(4) LOWER()函數(shù) 將大寫字符數(shù)據(jù)轉(zhuǎn)換為小寫字符數(shù)據(jù)后返回字符表達(dá)式。語法格式如下。 LOWER

22、 ( character_expression )例如：以下示例為LOWER()函數(shù)的使用 DECLARE StringTest CHAR (10) SET StringTest=Robin SELECT LOWER(LEFT(StringTest,3) 執(zhí)行結(jié)果為： rob9.2.1 常用函數(shù)(5) LTRIM()函數(shù) 刪除起始空格后返回字符表達(dá)式。語法格式如下。LTRIM ( character_expression )例如：以下示例為LTRIM()函數(shù)的使用 DECLARE StringTest CHAR (10) SET StringTest= Robin SELECT Start-+

23、LTRIM(StringTest),Start-+StringTest 執(zhí)行結(jié)果為： Start-Robin Start- Robin9.2.1 常用函數(shù)(6) RIGHT()函數(shù) 返回字符串中從右邊開始指定個(gè)數(shù)的字符。語法格式如下。 RIGHT ( character_expression , integer_expression )例如：以下示例為RIGHT()函數(shù)的使用。 DECLARE StringTest CHAR (10) SET StringTest= Robin SELECT RIGHT(StringTest,3) 執(zhí)行結(jié)果為： in9.2.1 常用函數(shù)(7) RTRIM()函

24、數(shù) 截?cái)嗨形搽S空格后返回一個(gè)字符串。語法格式如下。 RTRIM ( character_expression )例如：以下示例為RTRIM()函數(shù)的使用。 DECLARE StringTest CHAR (10) SET StringTest=Robin SELECT StringTest+-End, RTRIM(StringTest)+-End 執(zhí)行結(jié)果為： Robin -End Robin-End9.2.1 常用函數(shù)(8) STR()函數(shù) 將數(shù)字?jǐn)?shù)據(jù)轉(zhuǎn)換為字符數(shù)據(jù)。語法格式如下。STR ( float_expression , length , decimal )其中： float_ex

25、pression：帶小數(shù)點(diǎn)的近似數(shù)字(float)數(shù)據(jù)類型的表達(dá)式。length：總長度。它包括小數(shù)點(diǎn)、符號、數(shù)字以及空格。默認(rèn)值為10。decimal：小數(shù)點(diǎn)后的位數(shù)。decimal必須小于或等于16。如果decimal大于16，則會(huì)截?cái)嘟Y(jié)果，使其保持為小數(shù)點(diǎn)后具有十六位。9.2.1 常用函數(shù)例如： SELECT A+STR(82) 執(zhí)行結(jié)果為： A 82 SELECT A+LTRIM(STR(82) 執(zhí)行結(jié)果為： A829.2.1 常用函數(shù)(9) SUBSTRING()函數(shù) 求子串函數(shù)。語法格式如下。 SUBSTRING ( expression ,start , length )其中：e

26、xpression：是字符串、二進(jìn)制字符串、文本、圖像、列或包含列的表達(dá)式。不能使用包含聚合函數(shù)的表達(dá)式。start：指定子字符串開始位置的整數(shù)，start可以為bigint類型。length：正整數(shù)，指定要返回的expression的字符數(shù)或字節(jié)數(shù)。如果length為負(fù)，則會(huì)返回錯(cuò)誤。length可以是bigint類型。9.2.1 常用函數(shù)例如：以下示例為SUBSTRING()函數(shù)的使用。 DECLARE StringTest char(10) SET StringTest=Robin SELECT SUBSTRING(StringTest,3,LEN(StringTest) 執(zhí)行結(jié)果為：

27、bin9.2.1 常用函數(shù)(10) UPPER()函數(shù) 返回將小寫字符數(shù)據(jù)轉(zhuǎn)換為大寫字符的表達(dá)式。語法格式如下。 UPPER ( character_expression ) 例如：以下示例為UPPER ()函數(shù)的使用 DECLARE StringTest CHAR(10) SET StringTest=Robin SELECT UPPER(StringTest) 執(zhí)行結(jié)果為： ROBIN 9.2.1 常用函數(shù)2.日期時(shí)間函數(shù) 日期時(shí)間函數(shù)對日期和時(shí)間輸入值執(zhí)行操作，并返回一個(gè)字符串、數(shù)字值或日期和時(shí)間值。 (1) DATEADD()函數(shù) 在指定日期加上一段時(shí)間的基礎(chǔ)上，返回新的DATETIM

28、E類型值。語法格式如下。 DATEADD ( datepart , number, date )其中： datepart：指定要返回新值的日期的組成部分。 number：用來增加datepart的值。如果指定一個(gè)不是整數(shù)的值，則將廢棄此值的小數(shù)部分。 date：是返回DATETIME或SMALLDATETIME類型值或日期格式字符串的表達(dá)式。 9.2.1 常用函數(shù)表 10.4 SQL Server的日期部分日期部分寫 法取值范圍Yearyy17539999Quarterqq14Monthmm112Dayofyeardy1366Daydd131Weekwk154Weekdaydw17(MonSu

29、n)Hourhh023Minutemi059Secondss059Millisecondms09999.2.1 常用函數(shù)例如：以下示例為DATEADD()函數(shù)的使用。 DECLARE OLDTime DATETIME SET OLDTime=12-02-2004 06:30pm SELECT DATEADD(hh,4,OldTime)執(zhí)行結(jié)果為： 2004-12-02 22:30:00.0009.2.1 常用函數(shù)(2) DATEDIFF()函數(shù) 兩時(shí)間之差，返回跨兩個(gè)指定日期的日期邊界數(shù)和時(shí)間邊界數(shù)。語法格式如下。 DATEDIFF ( datepart , startdate , endda

30、te ) 其中： datepart：指定應(yīng)在日期的哪一部分計(jì)算差額的參數(shù)。startdate：計(jì)算的開始日期。startdate是返回DATETIME或SMALLDATETIME類型值或日期格式字符串的表達(dá)式。enddate：計(jì)算的結(jié)束日期。enddate是返回DATETIME或SMALLDATETIME類型值或日期格式字符串的表達(dá)式。9.2.1 常用函數(shù)例如：以下示例為DATEDIFF()函數(shù)的使用。 DECLARE FirstTime DATETIME, SecondTime DATETIME SET FirstTime=03-24-2006 6:30pm SET SecondTime=0

31、3-24-2006 6:33pm SELECT DATEDIFF(ms, FirstTime, SecondTime)執(zhí)行結(jié)果為：1800009.2.1 常用函數(shù)(3) DATENAME() 返回表示指定日期的指定日期部分的字符串。語法格式如下。 DATENAME ( datepart , date )其中：datepart：是指定要返回的日期部分的參數(shù)。date：表達(dá)式，用于返回DATETIME或SMALLDATETIME類型值，或日期格式的字符串。例如：DECLARE StatementDate DATETIME SET StatementDate=2006-3-14 3:00 PM SE

32、LECT DATENAME(dw,StatementDate) 執(zhí)行結(jié)果為： 星期二9.2 函 數(shù)10.3.2 用戶定義函數(shù) 用戶定義函數(shù)(User-Defined Function，UDF)是執(zhí)行計(jì)算并返回一個(gè)值(標(biāo)量值或表)的一段程序。 根據(jù)函數(shù)返回值形式的不同將用戶定義函數(shù)分為3種類型。(1) 標(biāo)量函數(shù)標(biāo)量函數(shù)返回一個(gè)確定類型的標(biāo)量值，其函數(shù)值類型為SQL Server的系統(tǒng)數(shù)據(jù)類型（除text、ntext、image、cursor、timestamp、table類型外）。函數(shù)體語句定義在BEGINEND語句內(nèi)。(2) 內(nèi)嵌表值函數(shù)內(nèi)嵌表值函數(shù)返回的函數(shù)值為一個(gè)表。內(nèi)嵌表值函數(shù)的函數(shù)體不

33、使用BEGINEND語句，其返回的表是RETURN子句中的SELECT命令查詢的結(jié)果集，其功能相當(dāng)于一個(gè)參數(shù)化的視圖。(3) 多語句表值函數(shù)多語句表值函數(shù)可以看作標(biāo)量函數(shù)和內(nèi)嵌表值函數(shù)的結(jié)合體。其函數(shù)值也是一個(gè)表，但函數(shù)體也用BEGINEND語句定義，返回值的表中的數(shù)據(jù)由函數(shù)體中的語句插入。9.2 函 數(shù)用戶定義函數(shù)需要注意以下幾點(diǎn) ： UDF可以嵌入到查詢、約束和計(jì)算列中。定義UDF的代碼不能影響函數(shù)范圍之外的數(shù)據(jù)庫狀態(tài)，也就是說，UDF代碼不能修改表中的數(shù)據(jù)或調(diào)用會(huì)產(chǎn)生副作用的函數(shù)(例如，RAND) 。 UDF的代碼只能創(chuàng)建表變量，不能創(chuàng)建或訪問臨時(shí)表，也不允許使用動(dòng)態(tài)執(zhí)行。 在Micro

34、soft SQL Server 2005系統(tǒng)中，可以分別使用CREATE FUNCTION、ALTER FUNCTION、DROP FUNCTION語句來實(shí)現(xiàn)用戶定義函數(shù)的創(chuàng)建、修改和刪除。在創(chuàng)建用戶定義函數(shù)時(shí)，每個(gè)完全限定用戶函數(shù)名稱必須唯一。 用戶定義函數(shù)不能用于執(zhí)行一系列可以改變數(shù)據(jù)庫狀態(tài)的操作 。9.2.2 用戶定義函數(shù)1. 創(chuàng)建用戶定義函數(shù)(1) 使用CREATE FUNCTION創(chuàng)建用戶定義函數(shù) 標(biāo)量函數(shù)的語法格式：CREATE FUNCTION owner_name. function_name( parameter_name AS scalar_parameter_data_t

35、ype=default ,.n ) RETURNS scalar_return_data_type WITH ,.n AS BEGIN function_body RETURN scalar_expressionEND:= ENCRYPTION | SCHEMABINDING 其中： owner_name：指定用戶定義函數(shù)的所有者。 function_name：用戶定義函數(shù)的名稱。函數(shù)名稱必須符合有關(guān)標(biāo)識符的規(guī)則，并且在數(shù)據(jù)庫中以及對其架構(gòu)來說是唯一的。 parameter_name：用戶定義函數(shù)中的參數(shù)。一個(gè)函數(shù)最多可以有1 024個(gè)參數(shù)。執(zhí)行函數(shù)時(shí)，如果未定義參數(shù)的默認(rèn)值，則用戶必須提供每

36、個(gè)已聲明參數(shù)的值。通過將符號用作第一個(gè)字符來指定參數(shù)名稱。參數(shù)名稱必須符合有關(guān)標(biāo)識符的規(guī)則。參數(shù)是對應(yīng)于函數(shù)的局部參數(shù)；其他函數(shù)中可使用相同的參數(shù)名稱。參數(shù)只能代替常量，而不能用于代替表名、列名或其他數(shù)據(jù)庫對象的名稱。 scalar_parameter_data_type：指定標(biāo)量參數(shù)的數(shù)據(jù)類型，除了text、ntext、image、cursor、timestamp和table類型外的其他數(shù)據(jù)類型。 = default ：參數(shù)的默認(rèn)值。如果定義了default值，則無需指定此參數(shù)的值即可執(zhí)行函數(shù) 。scalar_return_data_type：指定標(biāo)量返回值的數(shù)據(jù)類型，除了text、ntex

37、t、image、cursor、timestamp和table類型外的其它數(shù)據(jù)類型 。 scalar_expression：指定標(biāo)量型用戶自定義函數(shù)返回的標(biāo)量值表達(dá)式 。 function_body：指定一系列的Transact_SQL語句，它們決定了函數(shù)的返回值。 encryption：加密選項(xiàng)，讓SQL Server對系統(tǒng)表中有關(guān)CREATE FUNCTION的聲明加密，以防止用戶定義函數(shù)作為SQL Server復(fù)制的一部分被發(fā)布 。 schemabinding：計(jì)劃綁定選項(xiàng)。將用戶定義函數(shù)綁定到它所引用的數(shù)據(jù)庫對象，則函數(shù)所涉及的數(shù)據(jù)庫對象從此將不能被刪除或修改，除非函數(shù)被刪除或去掉此選項(xiàng)

38、。應(yīng)注意的是要綁定的數(shù)據(jù)庫對象必須與函數(shù)在同一數(shù)據(jù)庫中。9.2.2 用戶定義函數(shù)例如：根據(jù)輸入的某人的年齡，返回其出生年份。 USE teaching GO CREATE FUNCTION birth_year(age int) RETURNS int AS BEGIN RETURN(YEAR(GETDATE()-age) END9.2.2 用戶定義函數(shù)內(nèi)嵌表值函數(shù)的語法格式：CREATE FUNCTION owner_name. function_name( parameter_name AS scalar_parameter_data_type =default ,.n ) RETURNS

39、 TABLE WITH ,.n AS RETURN ( select_stmt ) 其中：TABLE：指定表值函數(shù)的返回值為表。只有常量和local_variables可以傳遞到表值函數(shù)。在內(nèi)聯(lián)表值函數(shù)中，TABLE返回值是通過單個(gè)SELECT語句定義的。 select_stmt：定義內(nèi)聯(lián)表值函數(shù)返回值的單個(gè)SELECT語句，確定返回的表的數(shù)據(jù) 。9.2.2 用戶定義函數(shù)例如：在teaching數(shù)據(jù)庫中創(chuàng)建內(nèi)聯(lián)表值函數(shù)，根據(jù)輸入的學(xué)生學(xué)號，返回student表中對應(yīng)的姓名和年齡。 USE teaching GO CREATE FUNCTION student_info(no char(4) R

40、ETURNS TABLE AS RETURN(SELECT SNAME, AGE FROM student WHERE SNO=no)9.2.2 用戶定義函數(shù)多語句表值函數(shù)的語法格式：CREATE FUNCTION owner_name. function_name( parameter_name AS scalar_parameter_data_type = default ,.n ) RETURNS return_variable TABLE WITH ,.n AS BEGIN function_body RETURNEND: = ( | ,.n )9.2.2 用戶定義函數(shù)其中： retu

41、rn_variable：一個(gè)table類型的變量，用于存儲(chǔ)和累積返回的表中的數(shù)據(jù)行。 ：定義Transact-SQL函數(shù)的表數(shù)據(jù)類型。表聲明包含列定義和列約束(或表約束)。表始終放在主文件組中。9.2.2 用戶定義函數(shù)例如：根據(jù)輸入的學(xué)生學(xué)號，返回該學(xué)生的選課情況。 USE teaching GO CREATE FUNCTION choice_course( no char(4) ) RETURNS choiceinfo TABLE (學(xué)號 char(4), 姓名 char(8), 課程號 char(4), 成績 smallint) AS BEGIN INSERT choiceinfo SEL

42、ECT student.SNO, SNAME, CNO, GRADE FROM student, s_c WHERE student.SNO=s_c.SNO AND student.SNO=no RETURN END9.2.2 用戶定義函數(shù) 具體步驟詳見教材（2）使用圖形工具創(chuàng)建用戶定義函數(shù) 2.調(diào)用用戶定義函數(shù) 調(diào)用用戶定義函數(shù)和調(diào)用內(nèi)置函數(shù)方式基本相同 當(dāng)調(diào)用標(biāo)量值函數(shù)時(shí)，必須加上“所有者”，通常是dbo。例如：調(diào)用例10.17創(chuàng)建的標(biāo)量函數(shù)birth_year如下。 SELECT dbo.birth_year(30) 執(zhí)行結(jié)果為： 1983 當(dāng)調(diào)用表值函數(shù)時(shí)，可以只使用函數(shù)名稱 因?yàn)楸碇?/p>

43、函數(shù)返回的是一個(gè)表，因此調(diào)用時(shí)，要把該函數(shù)作為表來使用。 例如：調(diào)用例10.18創(chuàng)建的內(nèi)聯(lián)表值函數(shù)student_info如下。 SELECT * FROM student_info(S1)執(zhí)行結(jié)果如下圖所示。9.2.2 用戶定義函數(shù)3. 修改用戶定義函數(shù)可以使用Transact-SQL命令A(yù)LTER FUNCTION命令修改用戶定義函數(shù)，語法格式與CREATE FUNCTION相同，相當(dāng)于重建。也可以使用圖形工具修改用戶定義函數(shù)，具體步驟詳見教材9.2.2 用戶定義函數(shù)3. 刪除用戶定義函數(shù)(1)使用Transact-SQL命令刪除 使用DROP FUNCTION命令刪除用戶定義函數(shù)，其語法

44、如下：DROP FUNCTION owner_name . function_name ,.n 其中，function_name是要?jiǎng)h除的用戶定義的函數(shù)名稱。例如：刪除teaching數(shù)據(jù)庫中的用戶定義函數(shù)birth_year。 USE teaching GO DROP FUNCTION birth_year GO 9.2.2 用戶定義函數(shù)(2)使用圖形工具刪除用戶定義函數(shù)具體步驟詳見教材10.4 流程控制語句10.4.1 批處理10.4.2 選擇語句10.4.3 循環(huán)語句9.3 程序控制流語句Transact-SQL語言提供了稱為控制流語言的特殊關(guān)鍵字，這些關(guān)鍵字用于控制Transact-S

45、QL語句、語句塊、用戶定義函數(shù)以及存儲(chǔ)過程的執(zhí)行流。控制流語言支持基本的流控制邏輯，它允許按照給定的某種條件執(zhí)行程序流和分支?？刂屏髡Z句不能跨多個(gè)批處理、用戶定義函數(shù)或存儲(chǔ)過程。Transact-SQL提供的控制流有：IFELSE分支、CASE多重分支、WHILE循環(huán)結(jié)構(gòu)、GOTO語句、WAITFOR語句和RETURN語句。9.3 程序控制流語句10.4.1 批處理 批處理是包含條或多條Transact-SQL語句的集合，被一次性執(zhí)行。 SQL Server將批處理編譯成一個(gè)可執(zhí)行單元，稱為執(zhí)行計(jì)劃。批中如果某處發(fā)生編譯錯(cuò)誤，整個(gè)執(zhí)行計(jì)劃都無法執(zhí)行。 寫批處理時(shí)，GO語句作為批處理命令的結(jié)束標(biāo)

46、志，當(dāng)編譯器讀取到GO語句時(shí)，會(huì)把GO語句前的所有語句當(dāng)作一個(gè)批處理，并將這些語句打包發(fā)送給服務(wù)器。GO語句本身不是Transact-SQL語句的組成部分，只是一個(gè)表示批處理結(jié)束的前端指令。 1. 批處理使用規(guī)則 (1) CREATE DEFAULT、CREATE FUNCTION、CREATE PROCEDURE、CREATE RULE、CREATE SCHEMA、CREATE TRIGGER和CREATE VIEW語句不能在批處理中與其他語句組合使用，在同一個(gè)批處理中只能提交一個(gè) 。 (2)不能在刪除一個(gè)對象之后，在同一批處理中再次引用這個(gè)對象。 (3)不能把規(guī)則和默認(rèn)值綁定到表字段或者自

47、定義字段上后，立即在同一批處理中使用它們。 (4)不能定義一個(gè)CHECK約束后，立即在同一個(gè)批處理中使用這個(gè)約束。 (5)不能修改表中一個(gè)字段名后，立即在同一個(gè)批處理中引用這個(gè)新字段。 (6)使用SET語句設(shè)置的某些SET選項(xiàng)不能應(yīng)用于同一個(gè)批處理中的查詢。 (7)若批處理中第一個(gè)語句是執(zhí)行某個(gè)存儲(chǔ)過程的EXECUTE語句，則EXECUTE關(guān)鍵字可以省略。若該語句不是第一個(gè)語句，則必須寫上。 9.3 程序控制流語句 SQL Server有以下幾種指定批處理的方法。(1) 應(yīng)用程序作為一個(gè)執(zhí)行單元發(fā)出的所有SQL語句構(gòu)成一個(gè)批處理，并生成單個(gè)執(zhí)行計(jì)劃。(2) 存儲(chǔ)過程或觸發(fā)器內(nèi)的所有語句構(gòu)成一個(gè)

48、批處理。每個(gè)存儲(chǔ)過程或觸發(fā)器都編譯為一個(gè)執(zhí)行計(jì)劃。(3) 由EXECUTE語句執(zhí)行的字符串是一個(gè)批處理，并編譯為一個(gè)執(zhí)行計(jì)劃。例如，EXEC (SELECT * FROM employee)(4) 由sp_executesql系統(tǒng)存儲(chǔ)過程執(zhí)行的字符串是一個(gè)批處理，并編譯為一個(gè)執(zhí)行計(jì)劃。例如，execute sp_executesql NSELECT * from Sales.dbo.employee 注意：應(yīng)用程序發(fā)出的批處理過程中含有EXECUTE語句，已執(zhí)行字符串或存儲(chǔ)過程的執(zhí)行計(jì)劃，將和包含EXECUTE語句的執(zhí)行計(jì)劃分開執(zhí)行。 若sp_executesql存儲(chǔ)過程所執(zhí)行的字符串生成的執(zhí)

49、行計(jì)劃也與包含sp_executesql調(diào)用的批處理執(zhí)行計(jì)劃分開執(zhí)行。若批處理中的語句激發(fā)了觸發(fā)器，則觸發(fā)器執(zhí)行將和原始的批處理執(zhí)行分開進(jìn)行 。2. 指定批處理的方法9.3 程序控制流語句(1)批處理結(jié)束語句：GO 作為批處理的結(jié)束標(biāo)志，也就是說當(dāng)編譯器執(zhí)行到GO時(shí)會(huì)把GO之前的所有語句當(dāng)作一個(gè)批處理來執(zhí)行。 注意： GO命令和Transact-SQL語句不可在同一行，在批處理中的第一條語句后執(zhí)行任何存儲(chǔ)過程必須包含EXECUTE關(guān)鍵字。 局部變量的作用域限制在一個(gè)批處理中，不可在GO命令后引用。 EXECUTE命令執(zhí)行標(biāo)量值的用戶定義函數(shù)、系統(tǒng)過程、用戶定義存儲(chǔ)過程或擴(kuò)展存儲(chǔ)過程。同時(shí)支持T

50、ransact-SQL批處理內(nèi)的字符串的執(zhí)行。 3. 批處理的結(jié)束與退出9.3 程序控制流語句(2)批處理退出語句：RETURN 整型表達(dá)式 無條件中止查詢、存儲(chǔ)過程或批處理的執(zhí)行。注意：存儲(chǔ)過程或批處理不執(zhí)行位于RETURN之后的語句。 當(dāng)存儲(chǔ)過程使用該語句，則可用該語句指定返回給調(diào)用應(yīng)用程序、批處理或過程的整數(shù)值。 若RETURN語句未指定值，則存儲(chǔ)過程的返回值是0。 當(dāng)用于存儲(chǔ)過程時(shí)，RETURN不能返回空值 。4. 腳本 腳本是存儲(chǔ)在文件中的一系列Transact-SQL語句?？砂粋€(gè)或多個(gè)批處理，GO作為批處理結(jié)束語句，如果腳本中無GO語句，則作為單個(gè)批處理。腳本文件擴(kuò)展名為.sq

51、l。9.3 程序控制流語句10.4.2 選擇語句1. 條件執(zhí)行語句IFELSE必須IF.ELSE結(jié)構(gòu)根據(jù)條件表達(dá)式的值，以決定執(zhí)行哪些語句。 IF.ELSE的語法格式為：IF Boolean_expression sql_statement | statement_block -條件表達(dá)式為真時(shí)執(zhí)行 ELSE sql_statement | statement_block -條件表達(dá)式為假時(shí)執(zhí)行其中： Boolean_expression：返回TRUE或FALSE的表達(dá)式。如果布爾表達(dá)式中含有SELECT語句，則必須用括號將SELECT語句括起來。 sql_statement | statem

52、ent_block ：任何Transact-SQL語句或用語句塊定義的語句分組。 注意： IF.ELSE構(gòu)造可用于批處理、存儲(chǔ)過程和即席查詢。當(dāng)此構(gòu)造用于存儲(chǔ)過程時(shí)，通常用于測試某個(gè)參數(shù)是否存在?？梢栽谄渌鸌F之后或在ELSE下面，嵌套另一個(gè)IF測試，嵌套級數(shù)的限制取決于可用內(nèi)存 。例如：查詢課程中是否有OS課程，如果有，統(tǒng)計(jì)選課人數(shù)。 USE teaching GO DECLARE num smallint IF EXIST(SELECT * FROM course WHERE CNAME LIKE OS%) BEGIN SELECT num=COUNT(*) FROM s_c WHERE

53、CNO IN (SELECT CNO FROM course WHERE CNAME LIKE OS%) PRINT 選此課程人數(shù)為：+str(num) END ELSE PRINT 數(shù)據(jù)庫中沒有此課程 執(zhí)行結(jié)果為： 選此課程人數(shù)為： 39.3.2 選擇控制2. CASE函數(shù) CASE表達(dá)式用來計(jì)算條件列表并返回多個(gè)可能結(jié)果表達(dá)式之一。 (1) 簡單CASE函數(shù)：將某個(gè)表達(dá)式與一組簡單表達(dá)式進(jìn)行比較以確定結(jié)果。CASE input_expression WHEN when_expression THEN result_expression .n ELSE else_result_express

54、ion END 其中： input_expression：計(jì)算的表達(dá)式，可以是任意有效的表達(dá)式。 WHEN when_expression：要與input_expression進(jìn)行比較的簡單表達(dá)式，是任意有效的表達(dá)式。input_expression及每個(gè)when_expression 的數(shù)據(jù)類型必須相同或必須是隱式轉(zhuǎn)換的數(shù)據(jù)類型。 THEN result_expression：當(dāng)input_expression = when_expression計(jì)算結(jié)果為TRUE時(shí)返回的表達(dá)式。 ELSE else_result_expression：比較運(yùn)算計(jì)算結(jié)果不為TRUE時(shí)返回的表達(dá)式。 9.3.2

55、 選擇控制例如：顯示每個(gè)學(xué)生選課的數(shù)量。 USE teaching GO SELECT SNO, 課程數(shù)量= CASE COUNT(*) WHEN 1 THEN 選修了一門課 WHEN 2 THEN 選修了兩門課 WHEN 3 THEN 選修了三門課 ELSE 選修了三門課以上 END FROM s_c GROUP BY SNO 執(zhí)行結(jié)果如下圖：9.3.2 選擇控制 (2) CASE搜索函數(shù)，CASE計(jì)算一組邏輯表達(dá)式以確定結(jié)果。CASE WHEN Boolean_expression THEN result_expression . n ELSE else_result_expression

56、 END其中：WHEN Boolean_expression是計(jì)算的布爾表達(dá)式，是任意有效的布爾表達(dá)式。例如：使用CASE搜索表達(dá)式顯示每個(gè)學(xué)生選課的數(shù)量 USE teaching GO SELECT SNO, 課程數(shù)量= CASE WHEN COUNT (*)=1 THEN 選修了一門課 WHEN COUNT (*)=2 THEN 選修了兩門課 WHEN COUNT (*)=3 THEN 選修了三門課 END FROM s_c GROUP BY SNO執(zhí)行結(jié)果如下圖：9.3.2 選擇控制3. WAITFOR語句 WAITFOR語句，稱為延遲語句。就是暫停批處理、存儲(chǔ)過程或事務(wù)的執(zhí)行，轉(zhuǎn)去執(zhí)行

57、一個(gè)指定的時(shí)間間隔或者到一個(gè)指定的時(shí)間。在達(dá)到指定時(shí)間或時(shí)間間隔之前，或者指定語句至少修改或返回一行之前，阻止執(zhí)行批處理、存儲(chǔ)過程或事務(wù)。 語法格式如下： WAITFOR DELAY time_to_pass /* 設(shè)定等待時(shí)間 */ | TIME time_to_execute /* 設(shè)定等待到某一時(shí)刻 */ 9.3.2 選擇控制其中： DELAY：可以繼續(xù)執(zhí)行批處理、存儲(chǔ)過程或事務(wù)之前必須經(jīng)過的指定時(shí)段，最長可為24小時(shí)。 time_to_pass：等待的時(shí)段?？梢允褂胐atetime數(shù)據(jù)可接受的格式之一指定time_to_pass，也可以將其指定為局部變量。不能指定日期，因此，不允許指定

58、datetime值的日期部分，只能指定時(shí)間。 TIME：指定的運(yùn)行批處理、存儲(chǔ)過程或事務(wù)的時(shí)間。 time_to_execute：WAITFOR語句完成的時(shí)間?？梢允褂胐atetime數(shù)據(jù)可接受的格式之一指定time_to_execute，也可以將其指定為局部變量，不能指定日期，因此，不允許指定datetime值的日期部分。注意：執(zhí)行WAITFOR語句時(shí)，事務(wù)正在運(yùn)行，并且其他請求不能在同一事務(wù)下運(yùn)行。 WAITFOR不更改查詢的語義。 如果查詢不能返回任何行，WAITFOR將一直等待，或等到滿足TIMEOUT條件(如果已指定)。 9.3.2 選擇控制例如：延遲30秒執(zhí)行查詢 USE teac

59、hing GO WAITFOR DELAY 00:00:30 SELECT * FROM student例如：在時(shí)刻21:20:00執(zhí)行查詢。 USE teaching GO WAITFOR TIME 21:20:00 SELECT * FROM student9.3.2 選擇控制4. 跳轉(zhuǎn)語句GOTO GOTO語句將執(zhí)行語句無條件跳轉(zhuǎn)到標(biāo)簽處，并從標(biāo)簽位置繼續(xù)處理。GOTO語句和標(biāo)簽可在過程、批處理或語句塊中的任何位置使用。語法格式如下 ： GOTO label其中，label為GOTO語句處理的起點(diǎn)。label必須符合標(biāo)識符規(guī)則。9.3.2 選擇控制5. RETURN語句 從查詢或過程中無

60、條件退出。RETURN的執(zhí)行是即時(shí)且完全的，可在任何時(shí)候用于從過程、批處理或語句塊中退出。RETURN之后的語句是不執(zhí)行的。語法格式如下 ： RETURN integer_expression 其中：integer_expression是返回的整數(shù)值。存儲(chǔ)過程可向執(zhí)行調(diào)用的過程或應(yīng)用程序返回一個(gè)整數(shù)值。注意：除非另外說明，否則所有系統(tǒng)存儲(chǔ)過程都將返回一個(gè)0值。此值表示成功，非0值表示失敗。如果用于存儲(chǔ)過程，RETURN不能返回NULL值。9.3 程序控制流語句10.4.3 循環(huán)語句 WHILE語句根據(jù)條件表達(dá)式設(shè)置Transact-SQL語句或語句塊重復(fù)執(zhí)行的次數(shù)。如果所設(shè)置的條件為真（TRU