受立信oss劉長海第8章函數(shù)

1、第8章 函數(shù)授課教師：趙友杰計(jì)算機(jī)與信息學(xué)院 數(shù)據(jù)庫原理及應(yīng)用第8章 函數(shù)8.1 系統(tǒng)內(nèi)置函數(shù)8.2 自定義函數(shù)第8章 函數(shù)8.1 系統(tǒng)內(nèi)置函數(shù)為了讓用戶更方便地對(duì)數(shù)據(jù)庫進(jìn)行操作，SQL Server 在T-SQL 中提供了許多內(nèi)置函數(shù)。函數(shù)其實(shí)就是一段程序代碼，用戶可以通過調(diào)用內(nèi)置函數(shù)并為其提供所需的參數(shù)來執(zhí)行一些特殊的運(yùn)算或完成復(fù)雜的操作。T-SQL 提供的函數(shù)有系統(tǒng)函數(shù)、字符串函數(shù)、日期和時(shí)間函數(shù)、數(shù)學(xué)函數(shù)、轉(zhuǎn)換函數(shù)等。8.1.1 系統(tǒng)函數(shù)系統(tǒng)函數(shù)用于獲取有關(guān)計(jì)算機(jī)系統(tǒng)、用戶、數(shù)據(jù)庫和數(shù)據(jù)庫對(duì)象的信息。可以使用戶在不直接訪問系統(tǒng)表的情況下，獲取SQL Server 系統(tǒng)表中的信息。系統(tǒng)函

2、數(shù)可以讓用戶在得到信息后使用條件語句，根據(jù)返回的信息進(jìn)行不同的操作。與其他函數(shù)一樣，可以在SELECT 語句的SELECT 和WHERE 子句以及表達(dá)式中使用系統(tǒng)函數(shù)。第8章 函數(shù)【例 8.1】 返回學(xué)生信息表中姓名字段的長度和數(shù)據(jù)長度。SELECT COL_LENGTH(StudInfo,StudName) AS Name_Col_Length,DATALENGTH(StudName) AS Name_Data_LengthFROM StudInfo第8章 函數(shù)8.1.2 字符串函數(shù)字符串函數(shù)對(duì)二進(jìn)制數(shù)據(jù)、字符串和表達(dá)式執(zhí)行不同的運(yùn)算。此類函數(shù)作用于CHAR、VARCHAR、BINARY 和

3、VARBINARY 數(shù)據(jù)類型以及可以隱式轉(zhuǎn)換為CHAR 或VARCHAR的數(shù)據(jù)類型。可以在SELECT 語句的SELECT 和WHERE 子句以及表達(dá)式中使用字符串函數(shù)。1. 字符轉(zhuǎn)換函數(shù)（1）ASCII功能：返回字符表達(dá)式最左端字符的ASCII 碼值。語法：ASCII 返回類型：INT示例：SELECT ascii(123),ascii(A)在ASCII 函數(shù)中，純數(shù)字的字符串可不用括起來，但含其他字符的字符串必須用括起來使用，否則會(huì)出錯(cuò)。第8章 函數(shù)（2）CHAR功能：用于將 ASCII 碼轉(zhuǎn)換為字符。語法：CHAR 返回類型：CHAR示例：SELECT char(65),Char(123

4、)如果沒有輸入0 255 之間的ASCII 碼值CHAR()函數(shù)會(huì)返回一個(gè)NULL 值（3）LOWER功能：把字符串全部轉(zhuǎn)換為小寫。語法：LOWER 返回類型：VARCHAR示例：SELECT lower(Abc),lower(A 李C)第8章 函數(shù)（4）UPPER功能：把字符串全部轉(zhuǎn)換為大寫。語法：LOWER 返回類型：VARCHAR示例：SELECT upper(Abc),upper(a 李c)（5）STR功能：把數(shù)值型數(shù)據(jù)轉(zhuǎn)換為字符型數(shù)據(jù)。語法：STR length 返回類型：CHAR示例：SELECT str(123.5),str(123456,5),str(123.456,8,2)自

5、變量length 和decimal 必須是非負(fù)值，length 指定返回的字符串的長度。decimal 指定返回的小數(shù)位數(shù)，如果沒有指定長度缺省的length 值為10，decimal 缺省值為0。小數(shù)位數(shù)大于decimal 值時(shí)STR 函數(shù)將其下一位四舍五入。指定長度應(yīng)大于或等于數(shù)字的符號(hào)位數(shù)+小數(shù)點(diǎn)前的位數(shù)+小數(shù)點(diǎn)位數(shù)+小數(shù)點(diǎn)后的位數(shù)。如果小數(shù)點(diǎn)前的位數(shù)超過了指定的長度，則返回指定長度的*。第8章 函數(shù)2. 去空格函數(shù)（1）LTRIM功能：把字符串頭部的空格去掉。語法：LTRIM 返回類型：VARCHAR示例：SELECT Ltrim( A),rtrim( 李 )（2）RTRIM功能：把字

6、符串尾部的空格去掉。語法：RTRIM 返回類型：VARCHAR示例：SELECT Ltrim(A ),rtrim( 李 )在許多情況下,往往需要得到頭部和尾部都沒有空格字符的字符串這時(shí)可將上兩個(gè)函數(shù)嵌套使用。【例 8.2】 去除字符串頭部和尾部空格。SELECT Rtrim(ltrim( 李 )第8章 函數(shù)3. 取子串函數(shù)（1）LEFT功能：返回從字符串左邊開始指定個(gè)數(shù)的字符。語法：LEFT(character_expression,integer_expression)返回類型：VARCHAR示例：SELECT LEFT(ABC,2),LEFT(A 西林C,2)（2）RIGHT功能：返回從字

7、符串右邊開始指定個(gè)數(shù)的字符。語法：RIGHT(character_expression,integer_expression)返回類型：VARCHAR示例：SELECT RIGHT(ABC,2),RIGHT(A 西林C,2)（3）SUBSTRING功能：返回字符串、binary、text 表達(dá)式的一部分。語法：SUBSTRING (expression,start,length)返回類型：VARCHAR，NVARCHAR，VARBINARY示例：SELECT SubString(ABC,2,1), SubString(ABC,2,4), SubString(A 西F,1,2)第8章 函數(shù)4.

8、字符串比較函數(shù)（1）CHARINDEX功能：返回字符串中某個(gè)指定的子串出現(xiàn)的起始位置。語法：CHARINDEX(substring_expression,expression,start_location)返回類型：INT示例：SELECT Charindex(B,ABC),CharIndex(AD,ABCD), CharIndex(B,ABCBDE,3)其中substring _expression 是所要查找的字符表達(dá)式，expression 可為字符串也可為列名表達(dá)式，start_location 表示要查詢的開始位置，省略該參數(shù)默認(rèn)為1。如果沒有發(fā)現(xiàn)子串則返回0 值。此函數(shù)不能用于TE

9、XT 和IMAGE 數(shù)據(jù)類型。（2）REPLACE功能：用第三個(gè)表達(dá)式替換第一個(gè)字符串表達(dá)式中出現(xiàn)的所有第二個(gè)給定字符串表達(dá)式。語法：REPLACE(string_expression1,string_expression2,string_expression3)返回類型：與表達(dá)式類型一致示例：SELECT Replace(ABC,B,123),Replace(ABCD,BD,ERR)第8章 函數(shù)8.1.3 日期函數(shù)日期函數(shù)用來顯示關(guān)于日期和時(shí)間的信息，日期函數(shù)的數(shù)據(jù)類型為datetime 和smalldatetime 值，可以對(duì)這些值執(zhí)行算術(shù)運(yùn)算，最后將返回一個(gè)字符串、數(shù)字值或日期和時(shí)間值。

10、可以在SELECT 語句的SELECT 和WHERE 子句以及表達(dá)式中使用日期函數(shù)。1. DAY功能：返回date_expression 中的日期值。語法：DAY 返回類型：INT示例：SELECT Day(2008-05-12),Day(05/12/2008)2. MONTH功能：返回date_expression 中的月份值。語法：MONTH 返回類型：INT示例：SELECT Month(2008-05-12),Month(05/12/2008)第8章 函數(shù)3. YEAR功能：返回date_expression 中的年份值。語法：YEAR 返回類型：INT示例：SELECT YEAR(2

11、008-05-12),YEAR(05/12/2008)在使用日期函數(shù)時(shí)，其日期值應(yīng)在1753 年到9999 年之間，這是 SQL Server 系統(tǒng)所能識(shí)別的日期范圍，否則會(huì)出現(xiàn)錯(cuò)誤。4. GETDATE功能：按datetime 數(shù)據(jù)類型格式返回當(dāng)前系統(tǒng)日期和時(shí)間。語法：GETDATE()返回類型：datetime示例：SELECT getdate()第8章 函數(shù)5. DATEPART功能：返回代表指定日期的指定日期部分的整數(shù)。語法：DATEPART(datepart,date)返回類型：INT示例：SELECT Datepart(year,getdate(),Datepart(Month,2

12、008-05-12)Datepart 是返回的日期部分可選參數(shù):第8章 函數(shù)6. DATEADD功能：在向指定日期加上一段時(shí)間的基礎(chǔ)上，返回新的datetime 值。語法：DATEADD（Datepart，number，date）返回類型：Datetime示例：SELECT DATEADD（day，20，2008-05-12），DATEADD（Month，10，2008-05-12），7. DATEDIFF功能：返回跨兩個(gè)指定日期的日期和時(shí)間邊界數(shù)。語法：DATEDIFF(datepart,startdate,enddate）返回類型：Int示例：SELECT DATEDIFF(Day,200

13、8-05-12,2008-08-08)第8章 函數(shù)8.1.4 數(shù)學(xué)函數(shù)1. POWER功能：返回給定表達(dá)式乘指定次方的值。語法：POWER(numeric_expression,y)返回類型：與numeric_expression 相同示例：SELECT POWER(2,3),POWER(2,0)2. ROUND功能：返回?cái)?shù)字表達(dá)式并四舍五入為指定的長度或精度。語法：ROUND(numeric_expression,length,function)返回類型：與numeric_expression 相同。示例：SELECT ROUND(24.567,2),ROUND(24.25,0)3. SQR

14、T功能：返回給定表達(dá)式的平方根。語法：SQRT(float_expression)返回類型：float示例：SELECT SQRT(4)ROUND(748.58, -1) 750.00 ROUND(748.58, -2) 700.00 ROUND(748.58, -3) 1000.00 第8章 函數(shù)8.1.5 CASE 函數(shù)1. 簡單 CASE 函數(shù)【例 8.3】 查詢學(xué)生信息，將性別以英文顯示。SELECT StudNo,StudName,學(xué)生性別=Case StudSexWhen 男 then MaleWhen 女 then FemaleElse 性別不詳endFROM StudInfo第

15、8章 函數(shù)2. CASE 搜索函數(shù)【例 8.4】 統(tǒng)計(jì)各學(xué)生平均分，并按等級(jí)顯示。SELECT StudNo,AVG(StudScore) AvgScore,Case When AVG(StudScore)=90 then 優(yōu)秀When AVG(StudScore)=80 AND AVG(StudScore)=70 then 中等When AVG(StudScore)=60 then 及格Else 不及格End AS ScoreLevelFROM StudScoreInfoGROUP BY StudNo第8章 函數(shù)8.1.6 系統(tǒng)內(nèi)置函數(shù)應(yīng)用系統(tǒng)內(nèi)置函數(shù)不僅可以在T-SQL 程序設(shè)計(jì)中使用，也

16、可以在標(biāo)準(zhǔn)SQL 語句使用，下面舉例說明系統(tǒng)內(nèi)置函數(shù)在SQL 語句中的應(yīng)用?！纠?8.5】 查詢姓“李”的學(xué)生信息。SELECT * FROM StudInfoWHERE Left(StudName,1)=李【例 8.6】 查詢姓名中包含“文”的學(xué)生信息。SELECT * FROM StudInfoWHERE Charindex(文,StudName)0【例 8.7】 查詢姓名中第二個(gè)字為“文”的學(xué)生信息。SELECT * FROM StudInfoWHERE substring(StudName,2,1)=文第8章 函數(shù)【例 8.8】 將姓名中的“云”字改為“蕓”。UPDATE StudIn

17、foSet StudName=Replace(StudName,云,蕓)【例 8.9】 統(tǒng)計(jì)學(xué)生信息表中同姓學(xué)生人數(shù)為3 人以上的信息。SELECT Left(StudName,1),COUNT(*)FROM StudInfoGROUP BY Left(StudName,1)HAVING COUNT(*)=3【例 8.11】 使用NEWID()函數(shù)實(shí)現(xiàn)將學(xué)生信息隨機(jī)排序，每次運(yùn)行排序結(jié)果不同。SELECT * FROM StudInfo ORDER BY newid()第8章 函數(shù)【例 8.10】 統(tǒng)計(jì)學(xué)號(hào)為20050319001 的各分?jǐn)?shù)段課程門數(shù)。SELECT Case When Stud

18、Score=90 then 優(yōu)秀When StudScore=80 then 良好When StudScore=70 then 中等When StudScore=60 then 及格Else 不及格End AS ScoreLevel,COUNT(*) CourseCountFROM StudScoreInfoWHERE StudNo=20050319001GROUP BY Case When StudScore=90 then 優(yōu)秀When StudScore=80 then 良好When StudScore=70 then 中等When StudScore=60 then 及格Else 不及

19、格End第8章 函數(shù)8.2 自定義函數(shù)8.2.1 自定義函數(shù)簡介除了使用系統(tǒng)提供的函數(shù)外，用戶還可以根據(jù)需要自定義函數(shù)。用戶自定義函數(shù)不能用于執(zhí)行一系列改變數(shù)據(jù)庫狀態(tài)的操作，但它可以像系統(tǒng)函數(shù)一樣在查詢或存儲(chǔ)過程等的程序段中使用，也可以像存儲(chǔ)過程一樣通過EXECUTE 命令來執(zhí)行。用戶自定義函數(shù)中存儲(chǔ)了一個(gè)Transact-SQL 例程，可以返回一定的值。在 SQL Server 中根據(jù)函數(shù)返回值形式的不同將用戶自定義函數(shù)分為以下類型： 標(biāo)量函數(shù)（Scalar function）：返回單一的數(shù)據(jù)值。 返回?cái)?shù)據(jù)集（RowSet）的用戶定義函數(shù)：返回一個(gè)table 類型的數(shù)據(jù)集，依定義語法不同分為

20、以下兩類： 行內(nèi)數(shù)據(jù)集函數(shù)（或稱為“內(nèi)嵌數(shù)據(jù)表值函數(shù)”） 多語句數(shù)據(jù)集函數(shù)（或稱為 “多語句式數(shù)據(jù)表值函數(shù)”）第8章 函數(shù)8.2.2 創(chuàng)建自定義函數(shù)標(biāo)量函數(shù)（Scalar function）：這類函數(shù)會(huì)返回單一的數(shù)據(jù)值，而數(shù)據(jù)值的類型可以是除了text、ntext、image、cursor 及rowversion(timestamp)之外的所有類型。創(chuàng)建標(biāo)量函數(shù)語法如下：CREATE FUNCTION owner_name. function_name( parameter_name AS scalar_parameter_data_type = default ,.n )RETURNS sc

21、alar_return_data_type WITH , .n AS BEGINfunction_bodyRETURN scalar_expressionEND:=ENCRYPTION | SCHEMABINDING函數(shù)的參數(shù)，可有0 個(gè)或多個(gè)（最多可有1024 個(gè)參數(shù)），而參數(shù)的名稱前要加上“”。參數(shù)行必須用小括號(hào)括起來，即使沒有參數(shù)，小括號(hào)也不可省略。可以用來為參數(shù)指定默認(rèn)值。例如：CREATE FUNCTION myFunct(achar(10),b int=500)。聲明返回值的類型設(shè)置函數(shù)的選項(xiàng)指定ENCRYPTION 時(shí)表示函數(shù)的內(nèi)容加密，函數(shù)建立之后即無法查看其程序內(nèi)容。若指定SCHEMABINDING（結(jié)構(gòu)綁定）選項(xiàng)，則可限制在函數(shù)中所使用到的各數(shù)