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

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

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

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

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

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

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

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

8、 整型常量二進制整型常量：由0、1組成，如111001。十進制整型常量：如1982。十六進制整型常量：用0 x開頭，如0 x3e，0 x，只有0 x表示空十六進制數(shù)。3. 日期時間型常量datetime常量使用特定格式的字符日期值表示，用單引號括起來。輸入時，可以使用“/”、“.”、“-”作日期/時間常量的分隔符。默認情況下，按照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ù)形式兩種。如165.234，10E23。5. 貨幣常量 用貨幣符號開頭。如$12.5，$54230.25。SQL Server不強制分組，如每隔三個數(shù)字插一個

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

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

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

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

14、SELECT命令賦值，查詢學號為S1的學生姓名。 USE teachingGODECLARE var1 varchar(8) SELECT var1=SNAME FROM student WHERE SNO=S1SELECT var1 AS 學生姓名執(zhí)行結(jié)果如圖10.1所示。9.1.2 常量與變量例如：使用SELECT命令賦值，結(jié)構為多個返回值時取最后一個值。 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服務器活動狀態(tài)的一組數(shù)據(jù)。例如：ERROR 表示最后一個Transact-SQL命令錯誤的錯誤號；SERVERNAME 表示本地服務器的名稱。具體見表10.2常用的SQL Server全局變量 全局變量由開始，由系統(tǒng)定義和維護，用戶只能顯示和讀取，不能修改 。 例如：顯示SQL Server的版本 。SELECT version AS 版本號執(zhí)行結(jié)果如圖10.4所示。 9.1.2 常量與變量例如：使用spid返回當前用戶進程的ID。SELECT sp

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

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

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

19、I ( character_expression ) 其中：character_expression為char或varchar類型的表達式。例如：以下示例為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、達式不在此范圍內(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ù) 返回從字符串左邊開始指定個數(shù)的字符。語法格式如下。 LEFT ( character_expression , integer_expression )其中： character_expression：字符或二進制數(shù)據(jù)表達式?？梢允浅Ａ俊⒆兞炕蛄?。可以是任何能夠隱式轉(zhuǎn)換為varchar或nva

21、rchar的數(shù)據(jù)類型，但text或ntext除外。integer_expression：正整數(shù)，指定character_expression將返回的字符數(shù)。如果為負，則會返回錯誤。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ù)后返回字符表達式。語法格式如下。 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ù) 刪除起始空格后返回字符表達式。語法格式如下。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ù) 返回字符串中從右邊開始指定個數(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ù) 截斷所有尾隨空格后返回一個字符串。語法格式如下。 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ù)字數(shù)據(jù)轉(zhuǎn)換為字符數(shù)據(jù)。語法格式如下。STR ( float_expression , length , decimal )其中： float_ex

25、pression：帶小數(shù)點的近似數(shù)字(float)數(shù)據(jù)類型的表達式。length：總長度。它包括小數(shù)點、符號、數(shù)字以及空格。默認值為10。decimal：小數(shù)點后的位數(shù)。decimal必須小于或等于16。如果decimal大于16，則會截斷結(jié)果，使其保持為小數(shù)點后具有十六位。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：是字符串、二進制字符串、文本、圖像、列或包含列的表達式。不能使用包含聚合函數(shù)的表達式。start：指定子字符串開始位置的整數(shù)，start可以為bigint類型。length：正整數(shù)，指定要返回的expression的字符數(shù)或字節(jié)數(shù)。如果length為負，則會返回錯誤。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)換為大寫字符的表達式。語法格式如下。 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ù)對日期和時間輸入值執(zhí)行操作，并返回一個字符串、數(shù)字值或日期和時間值。 (1) DATEADD()函數(shù) 在指定日期加上一段時間的基礎上，返回新的DATETIM

28、E類型值。語法格式如下。 DATEADD ( datepart , number, date )其中： datepart：指定要返回新值的日期的組成部分。 number：用來增加datepart的值。如果指定一個不是整數(shù)的值，則將廢棄此值的小數(shù)部分。 date：是返回DATETIME或SMALLDATETIME類型值或日期格式字符串的表達式。 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ù)和時間邊界數(shù)。語法格式如下。 DATEDIFF ( datepart , startdate , endda

30、te ) 其中： datepart：指定應在日期的哪一部分計算差額的參數(shù)。startdate：計算的開始日期。startdate是返回DATETIME或SMALLDATETIME類型值或日期格式字符串的表達式。enddate：計算的結(jié)束日期。enddate是返回DATETIME或SMALLDATETIME類型值或日期格式字符串的表達式。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：表達式，用于返回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ù)函數(shù)返回值形式的不同將用戶定義函數(shù)分為3種類型。(1) 標量函數(shù)標量函數(shù)返回一個確定類型的標量值，其函數(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ù)值為一個表。內(nèi)嵌表值函數(shù)的函數(shù)體不

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

34、soft SQL Server 2005系統(tǒng)中，可以分別使用CREATE FUNCTION、ALTER FUNCTION、DROP FUNCTION語句來實現(xiàn)用戶定義函數(shù)的創(chuàng)建、修改和刪除。在創(chuàng)建用戶定義函數(shù)時，每個完全限定用戶函數(shù)名稱必須唯一。 用戶定義函數(shù)不能用于執(zhí)行一系列可以改變數(shù)據(jù)庫狀態(tài)的操作 。9.2.2 用戶定義函數(shù)1. 創(chuàng)建用戶定義函數(shù)(1) 使用CREATE FUNCTION創(chuàng)建用戶定義函數(shù) 標量函數(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ī)則，并且在數(shù)據(jù)庫中以及對其架構來說是唯一的。 parameter_name：用戶定義函數(shù)中的參數(shù)。一個函數(shù)最多可以有1 024個參數(shù)。執(zhí)行函數(shù)時，如果未定義參數(shù)的默認值，則用戶必須提供每

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

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

38、。應注意的是要綁定的數(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返回值是通過單個SELECT語句定義的。 select_stmt：定義內(nèi)聯(lián)表值函數(shù)返回值的單個SELECT語句，確定返回的表的數(shù)據(jù) 。9.2.2 用戶定義函數(shù)例如：在teaching數(shù)據(jù)庫中創(chuàng)建內(nèi)聯(lián)表值函數(shù)，根據(jù)輸入的學生學號，返回student表中對應的姓名和年齡。 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：一個table類型的變量，用于存儲和累積返回的表中的數(shù)據(jù)行。 ：定義Transact-SQL函數(shù)的表數(shù)據(jù)類型。表聲明包含列定義和列約束(或表約束)。表始終放在主文件組中。9.2.2 用戶定義函數(shù)例如：根據(jù)輸入的學生學號，返回該學生的選課情況。 USE teaching GO CREATE FUNCTION choice_course( no char(4) ) RETURNS choiceinfo TABLE (學號 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ù)方式基本相同 當調(diào)用標量值函數(shù)時，必須加上“所有者”，通常是dbo。例如：調(diào)用例10.17創(chuàng)建的標量函數(shù)birth_year如下。 SELECT dbo.birth_year(30) 執(zhí)行結(jié)果為： 1983 當調(diào)用表值函數(shù)時，可以只使用函數(shù)名稱 因為表值

43、函數(shù)返回的是一個表，因此調(diào)用時，要把該函數(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命令ALTER FUNCTION命令修改用戶定義函數(shù)，語法格式與CREATE FUNCTION相同，相當于重建。也可以使用圖形工具修改用戶定義函數(shù)，具體步驟詳見教材9.2.2 用戶定義函數(shù)3. 刪除用戶定義函數(shù)(1)使用Transact-SQL命令刪除 使用DROP FUNCTION命令刪除用戶定義函數(shù)，其語法

44、如下：DROP FUNCTION owner_name . function_name ,.n 其中，function_name是要刪除的用戶定義的函數(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語言提供了稱為控制流語言的特殊關鍵字，這些關鍵字用于控制Transact-S

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

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

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

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

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

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

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

52、ent_block ：任何Transact-SQL語句或用語句塊定義的語句分組。 注意： IF.ELSE構造可用于批處理、存儲過程和即席查詢。當此構造用于存儲過程時，通常用于測試某個參數(shù)是否存在?？梢栽谄渌鸌F之后或在ELSE下面，嵌套另一個IF測試，嵌套級數(shù)的限制取決于可用內(nèi)存 。例如：查詢課程中是否有OS課程，如果有，統(tǒng)計選課人數(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表達式用來計算條件列表并返回多個可能結(jié)果表達式之一。 (1) 簡單CASE函數(shù)：將某個表達式與一組簡單表達式進行比較以確定結(jié)果。CASE input_expression WHEN when_expression THEN result_expression .n ELSE else_result_express

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

55、 選擇控制例如：顯示每個學生選課的數(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計算一組邏輯表達式以確定結(jié)果。CASE WHEN Boolean_expression THEN result_expression . n ELSE else_result_expression

56、 END其中：WHEN Boolean_expression是計算的布爾表達式，是任意有效的布爾表達式。例如：使用CASE搜索表達式顯示每個學生選課的數(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語句，稱為延遲語句。就是暫停批處理、存儲過程或事務的執(zhí)行，轉(zhuǎn)去執(zhí)行

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

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

59、hing GO WAITFOR DELAY 00:00:30 SELECT * FROM student例如：在時刻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)到標簽處，并從標簽位置繼續(xù)處理。GOTO語句和標簽可在過程、批處理或語句塊中的任何位置使用。語法格式如下 ： GOTO label其中，label為GOTO語句處理的起點。label必須符合標識符規(guī)則。9.3.2 選擇控制5. RETURN語句 從查詢或過程中無

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