SQL自定義函數(shù)

1、v 函數(shù)綜述函數(shù)綜述v 系統(tǒng)函數(shù)系統(tǒng)函數(shù)v 用戶自定義函數(shù)的基本操作用戶自定義函數(shù)的基本操作v 創(chuàng)建用戶自定義函數(shù)創(chuàng)建用戶自定義函數(shù)v 查看用戶自定義函數(shù)查看用戶自定義函數(shù)v 修改用戶自定義函數(shù)修改用戶自定義函數(shù)v 刪除用戶自定義函數(shù)刪除用戶自定義函數(shù)v 函數(shù)綜述函數(shù)綜述 為了使用戶對(duì)數(shù)據(jù)庫(kù)進(jìn)行查詢和修改時(shí)更加方便，為了使用戶對(duì)數(shù)據(jù)庫(kù)進(jìn)行查詢和修改時(shí)更加方便，SQL server在在T-SQL語(yǔ)言中提供了許多內(nèi)部函數(shù)以供調(diào)用。用戶也可以根據(jù)自己的語(yǔ)言中提供了許多內(nèi)部函數(shù)以供調(diào)用。用戶也可以根據(jù)自己的需要?jiǎng)?chuàng)建函數(shù)。需要?jiǎng)?chuàng)建函數(shù)。 函數(shù)可以由系統(tǒng)提供，也可以由用戶創(chuàng)建。系統(tǒng)提供的函數(shù)稱為函數(shù)可以由

2、系統(tǒng)提供，也可以由用戶創(chuàng)建。系統(tǒng)提供的函數(shù)稱為內(nèi)置函數(shù)，也叫做系統(tǒng)函數(shù)，它為用戶方便快捷地執(zhí)行某些操作提供內(nèi)置函數(shù)，也叫做系統(tǒng)函數(shù)，它為用戶方便快捷地執(zhí)行某些操作提供幫助；用戶創(chuàng)建的函數(shù)稱為用戶自定義函數(shù)，它是用戶根據(jù)自己的特幫助；用戶創(chuàng)建的函數(shù)稱為用戶自定義函數(shù)，它是用戶根據(jù)自己的特殊需求而創(chuàng)建的，用來(lái)補(bǔ)充和擴(kuò)展內(nèi)置函數(shù)。殊需求而創(chuàng)建的，用來(lái)補(bǔ)充和擴(kuò)展內(nèi)置函數(shù)。 v 系統(tǒng)函數(shù)系統(tǒng)函數(shù) 標(biāo)量函數(shù)標(biāo)量函數(shù) 系統(tǒng)函數(shù)系統(tǒng)函數(shù) 標(biāo)量函數(shù)標(biāo)量函數(shù) 聚合函數(shù)聚合函數(shù) 行集函數(shù)。行集函數(shù)。 標(biāo)量函數(shù)標(biāo)量函數(shù) 標(biāo)量函數(shù)對(duì)單一值操作，返回單一值。只要在能夠使用表達(dá)式的標(biāo)量函數(shù)對(duì)單一值操作，返回單一值。只要在能

3、夠使用表達(dá)式的地方，就可以使用標(biāo)量函數(shù)。地方，就可以使用標(biāo)量函數(shù)。 數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù) 日期和時(shí)間函數(shù)日期和時(shí)間函數(shù) 字符串函數(shù)字符串函數(shù) 數(shù)據(jù)類型轉(zhuǎn)換函數(shù)數(shù)據(jù)類型轉(zhuǎn)換函數(shù) 。v 系統(tǒng)函數(shù)系統(tǒng)函數(shù)標(biāo)量函數(shù)標(biāo)量函數(shù) 數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù) 1、abs(數(shù)值型表達(dá)式數(shù)值型表達(dá)式) 功能：返回表達(dá)式功能：返回表達(dá)式 的絕對(duì)值，其值的數(shù)據(jù)類型與參數(shù)一致。的絕對(duì)值，其值的數(shù)據(jù)類型與參數(shù)一致。 例：例：SELECT ABS(-1), ABS(0), ABS(1) 2、ceiling(數(shù)值型表達(dá)式數(shù)值型表達(dá)式) 功能：返回最小的大于或等于給定數(shù)值型表達(dá)式的整數(shù)值，值的功能：返回最小的大于或等于給定數(shù)值型表達(dá)式的整數(shù)

4、值，值的 類型和給定的值相同。類型和給定的值相同。 floor(數(shù)值型表達(dá)式數(shù)值型表達(dá)式) 功能：返回最大的小于或等于給定數(shù)值型表達(dá)式的整數(shù)值。功能：返回最大的小于或等于給定數(shù)值型表達(dá)式的整數(shù)值。 例：例：SELECT FLOOR(123.45),CEILING(123.45) SELECT FLOOR(-123.45), CEILING(-123.45)v 系統(tǒng)函數(shù)系統(tǒng)函數(shù)標(biāo)量函數(shù)標(biāo)量函數(shù) 數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù) 3、SQUARE(float表達(dá)式表達(dá)式)函數(shù)函數(shù) 功能：此函數(shù)用于返回給定表達(dá)式的平方值功能：此函數(shù)用于返回給定表達(dá)式的平方值 例如：例如：SQUARE(3)的結(jié)果為的結(jié)果為9.0。

5、POWER（數(shù)值型表達(dá)式（數(shù)值型表達(dá)式1，數(shù)值型表達(dá)式，數(shù)值型表達(dá)式2）函數(shù)）函數(shù) 功能：此函數(shù)用于返回給定表達(dá)式乘指定次方的值。成方運(yùn)算函功能：此函數(shù)用于返回給定表達(dá)式乘指定次方的值。成方運(yùn)算函 數(shù)返回值的數(shù)據(jù)類型與第一個(gè)參數(shù)的數(shù)據(jù)類型相同。數(shù)返回值的數(shù)據(jù)類型與第一個(gè)參數(shù)的數(shù)據(jù)類型相同。 例如：例如：POWER(2,3)表示表示2的的3次方。次方。 SELECT POWER(2,-3),POWER(2.0,-3),POWER(2.000,-3) 4、sign(數(shù)值型表達(dá)式數(shù)值型表達(dá)式) 功能：判斷數(shù)值的正負(fù)屬性，功能：判斷數(shù)值的正負(fù)屬性，+1表示正數(shù)；表示正數(shù)；-1表示負(fù)數(shù)。表示負(fù)數(shù)。v 系

6、統(tǒng)函數(shù)系統(tǒng)函數(shù)標(biāo)量函數(shù)標(biāo)量函數(shù) 數(shù)學(xué)函數(shù)數(shù)學(xué)函數(shù) 5、 rand(整型表達(dá)式整型表達(dá)式) 功能：返回一個(gè)位于功能：返回一個(gè)位于0和和1之間的隨機(jī)數(shù)，在單個(gè)查詢中反復(fù)調(diào)用之間的隨機(jī)數(shù)，在單個(gè)查詢中反復(fù)調(diào)用 rand( )將產(chǎn)生相同的值。將產(chǎn)生相同的值。 例：例：DECLARE counter smallint SET counter = 1 WHILE counter =50 set retrunstr=取得學(xué)分取得學(xué)分 else set retrunstr=未取得學(xué)分未取得學(xué)分 return retrunstr END 如果使用用戶自定義函數(shù)，要在使用的時(shí)候指明函數(shù)的所有者和函數(shù)的名如果使用用

7、戶自定義函數(shù)，要在使用的時(shí)候指明函數(shù)的所有者和函數(shù)的名 稱。在查詢分析器中輸入如下代碼：稱。在查詢分析器中輸入如下代碼： SELECT 學(xué)號(hào)學(xué)號(hào),成績(jī)成績(jī),dbo.xuefen(成績(jī)成績(jī)) AS 學(xué)分情況學(xué)分情況 FROM 課程注冊(cè)課程注冊(cè) WHERE 課程號(hào)課程號(hào)=0003 GOv 用戶自定義函數(shù)用戶自定義函數(shù) 2、使用企業(yè)管理器創(chuàng)建用戶定義函數(shù)、使用企業(yè)管理器創(chuàng)建用戶定義函數(shù) 在企業(yè)管理器中創(chuàng)建用戶自定義函數(shù)的步驟為：在企業(yè)管理器中創(chuàng)建用戶自定義函數(shù)的步驟為： 1）在企業(yè)管理器中，選擇需要建立函數(shù)的數(shù)據(jù)庫(kù)，從中選擇用戶定）在企業(yè)管理器中，選擇需要建立函數(shù)的數(shù)據(jù)庫(kù)，從中選擇用戶定義函數(shù)圖標(biāo)。

8、義函數(shù)圖標(biāo)。 2）選擇用戶定義函數(shù)圖標(biāo)后，擊右健，從彈出的快捷菜單中選擇）選擇用戶定義函數(shù)圖標(biāo)后，擊右健，從彈出的快捷菜單中選擇“新建用戶定義函數(shù)新建用戶定義函數(shù)”命令，打開命令，打開“用戶定義函數(shù)屬性用戶定義函數(shù)屬性”對(duì)話框，該屬對(duì)話框，該屬性窗口在性窗口在“文本文本”框中列出了建立函數(shù)的框架，如圖框中列出了建立函數(shù)的框架，如圖10.2所示。所示。 3）在）在“文本文本”框中輸入函數(shù)的所有者、函數(shù)名稱、參數(shù)列表、返回框中輸入函數(shù)的所有者、函數(shù)名稱、參數(shù)列表、返回類型和函數(shù)體等函數(shù)的各個(gè)組成部分。類型和函數(shù)體等函數(shù)的各個(gè)組成部分。 4）單擊）單擊“檢查語(yǔ)法檢查語(yǔ)法”按鈕，檢查輸入的建立函數(shù)語(yǔ)句

9、是否有語(yǔ)法錯(cuò)按鈕，檢查輸入的建立函數(shù)語(yǔ)句是否有語(yǔ)法錯(cuò)誤。如果沒(méi)有語(yǔ)法錯(cuò)誤，單擊誤。如果沒(méi)有語(yǔ)法錯(cuò)誤，單擊“確定確定”按鈕，將用戶定義的函數(shù)保存按鈕，將用戶定義的函數(shù)保存到數(shù)據(jù)庫(kù)中供用戶以后使用。到數(shù)據(jù)庫(kù)中供用戶以后使用。 v 用戶自定義函數(shù)的分類用戶自定義函數(shù)的分類-標(biāo)量函數(shù)標(biāo)量函數(shù)例：在例：在STUDENT庫(kù)中創(chuàng)建一個(gè)用戶自定義函數(shù)庫(kù)中創(chuàng)建一個(gè)用戶自定義函數(shù)XUEFENJI，該函數(shù)通過(guò)輸，該函數(shù)通過(guò)輸入成績(jī)來(lái)計(jì)算學(xué)生的學(xué)分績(jī)。其代碼如下：入成績(jī)來(lái)計(jì)算學(xué)生的學(xué)分績(jī)。其代碼如下：CREATE FUNCTION xuefenji(inputzz int) RETURNS nvarchar(10)

10、BEGIN declare retrunstr nvarchar(10) if inputzz =50 AND inputzz =60 AND inputzz =70 AND inputzz =80 AND inputzz =100 set retrunstr=學(xué)分績(jī)?yōu)閷W(xué)分績(jī)?yōu)?.5 else set retrunstr=無(wú)學(xué)分績(jī)無(wú)學(xué)分績(jī) return retrunstr ENDv 用戶自定義函數(shù)的分類用戶自定義函數(shù)的分類標(biāo)量函數(shù)標(biāo)量函數(shù)例：例： USE STUDENT GO SELECT 課程號(hào)課程號(hào),成績(jī)成績(jī),dbo.xuefenji(成績(jī)成績(jī)) AS 學(xué)分績(jī)學(xué)分績(jī) FROM 課程注冊(cè)課程

11、注冊(cè) WHERE 學(xué)號(hào)學(xué)號(hào)=010101001001 GO 需要查看需要查看“010101001001”號(hào)學(xué)生的課程學(xué)分績(jī)號(hào)學(xué)生的課程學(xué)分績(jī) v 用戶自定義函數(shù)的分類用戶自定義函數(shù)的分類表值函數(shù)表值函數(shù)表值函數(shù)遵循的原則：表值函數(shù)遵循的原則： 1、RETURNS子句僅包含關(guān)鍵字子句僅包含關(guān)鍵字table。不必定義返回變量的格式，因?yàn)樗?。不必定義返回變量的格式，因?yàn)樗蒖ETURN 子句中的子句中的 SELECT 語(yǔ)句的結(jié)果集的格式設(shè)置。語(yǔ)句的結(jié)果集的格式設(shè)置。 2、function_body 不由不由BEGIN和和END分隔。分隔。 3、RETURN子句在括號(hào)中包含單個(gè)子句在括號(hào)中包含單個(gè)S

12、ELECT語(yǔ)句。語(yǔ)句。SELECT語(yǔ)句的結(jié)果集構(gòu)成語(yǔ)句的結(jié)果集構(gòu)成函數(shù)所返回的表。內(nèi)嵌表值函數(shù)中使用的函數(shù)所返回的表。內(nèi)嵌表值函數(shù)中使用的SELECT語(yǔ)句受到與視圖中使用的語(yǔ)句受到與視圖中使用的SELECT語(yǔ)句相同的限制。語(yǔ)句相同的限制。例：在例：在STUDENT庫(kù)中創(chuàng)建一個(gè)內(nèi)嵌表值函數(shù)庫(kù)中創(chuàng)建一個(gè)內(nèi)嵌表值函數(shù)XUESHENG，該函數(shù)可以根據(jù)輸入，該函數(shù)可以根據(jù)輸入的系部代碼返回該系學(xué)生的基本信息。其代碼如下：的系部代碼返回該系學(xué)生的基本信息。其代碼如下：CREATE FUNCTION XUESHENG(inputxbdm nvarchar(4) RETURNS table AS RETURN

13、 ( SELECT 學(xué)號(hào)學(xué)號(hào), 姓名姓名, 入學(xué)時(shí)間入學(xué)時(shí)間 FROM 學(xué)生學(xué)生 WHERE 系部代碼系部代碼=inputxbdm)GO建立好該內(nèi)嵌表值函數(shù)后，就可以象使用表或視圖一樣來(lái)使用它：建立好該內(nèi)嵌表值函數(shù)后，就可以象使用表或視圖一樣來(lái)使用它：SELECT * FROM DBO.XUESHENG(01)GO v 用戶自定義函數(shù)的分類用戶自定義函數(shù)的分類多表值函數(shù)多表值函數(shù)例：在例：在STUDENT庫(kù)中創(chuàng)建一個(gè)多語(yǔ)句表值函數(shù)庫(kù)中創(chuàng)建一個(gè)多語(yǔ)句表值函數(shù)CHENGJI，該函數(shù)可以根據(jù)輸入，該函數(shù)可以根據(jù)輸入的課程名稱返回選修該課程的學(xué)生姓名和成績(jī)。其代碼如下：的課程名稱返回選修該課程的學(xué)生姓

14、名和成績(jī)。其代碼如下： CREATE FUNCTION CHENGJI( inputkc as char(20) ) RETURNS chji TABLE ( 課程名課程名 char(20), 姓名姓名 char(8), 成績(jī)成績(jī) tinyint ) AS BEGIN INSERT chji SELECT c.課程名課程名,s.姓名姓名 ,k.成績(jī)成績(jī) FROM 學(xué)生學(xué)生 as s INNER JOIN 課程注冊(cè)課程注冊(cè) as k ON s.學(xué)號(hào)學(xué)號(hào) =k.學(xué)號(hào)學(xué)號(hào) inner join 課程課程 as c on c.課程號(hào)課程號(hào)=k.課程號(hào)課程號(hào) WHERE c.課程名課程名=inputkc

15、 RETURN END GO在查詢分析器中輸入以下查詢命令：在查詢分析器中輸入以下查詢命令： SELECT * FROM DBO.CHENGJIi(大學(xué)語(yǔ)文大學(xué)語(yǔ)文)v 用戶自定義函數(shù)的分類用戶自定義函數(shù)的分類多表值函數(shù)多表值函數(shù)總結(jié)：總結(jié)：多語(yǔ)句函數(shù)的主體中允許使用以下語(yǔ)句。多語(yǔ)句函數(shù)的主體中允許使用以下語(yǔ)句。 1、賦值語(yǔ)句。、賦值語(yǔ)句。 2、控制流語(yǔ)句。、控制流語(yǔ)句。 3、DECLARE 語(yǔ)句，該語(yǔ)句定義函數(shù)局部的數(shù)據(jù)變量和游標(biāo)。語(yǔ)句，該語(yǔ)句定義函數(shù)局部的數(shù)據(jù)變量和游標(biāo)。 4、SELECT 語(yǔ)句，該語(yǔ)句包含帶有表達(dá)式的選擇列表，其中的表達(dá)語(yǔ)句，該語(yǔ)句包含帶有表達(dá)式的選擇列表，其中的表達(dá)式將

16、值賦予函數(shù)的局部變量。式將值賦予函數(shù)的局部變量。 5、游標(biāo)操作，該操作引用在函數(shù)中聲明、打開、關(guān)閉和釋放的局部、游標(biāo)操作，該操作引用在函數(shù)中聲明、打開、關(guān)閉和釋放的局部游標(biāo)。只允許使用以游標(biāo)。只允許使用以INTO子句向局部變量賦值的子句向局部變量賦值的FETCH語(yǔ)句；不允語(yǔ)句；不允許使用將數(shù)據(jù)返回到客戶端的許使用將數(shù)據(jù)返回到客戶端的FETCH語(yǔ)句。語(yǔ)句。 6、INSERT、UPDATE和和DELETE語(yǔ)句，這些語(yǔ)句修改函數(shù)的局部語(yǔ)句，這些語(yǔ)句修改函數(shù)的局部table變量。變量。 7、EXECUTE語(yǔ)句調(diào)用擴(kuò)展存儲(chǔ)過(guò)程。語(yǔ)句調(diào)用擴(kuò)展存儲(chǔ)過(guò)程。 v 查看用戶自定義函數(shù)的屬性查看用戶自定義函數(shù)的屬性

17、查詢分析器查詢分析器在在SQL Server中，根據(jù)不同需要，可以使用中，根據(jù)不同需要，可以使用sp_helptext、sp_help等系等系統(tǒng)存儲(chǔ)過(guò)程來(lái)查看用戶自定義函數(shù)的不同信息。每個(gè)系統(tǒng)存儲(chǔ)過(guò)程的統(tǒng)存儲(chǔ)過(guò)程來(lái)查看用戶自定義函數(shù)的不同信息。每個(gè)系統(tǒng)存儲(chǔ)過(guò)程的具體作用和語(yǔ)法如下：具體作用和語(yǔ)法如下：使用使用sp_helptext查看用戶定義函數(shù)的文本信息，其語(yǔ)法格式為：查看用戶定義函數(shù)的文本信息，其語(yǔ)法格式為： sp_helptext 用戶自定義函數(shù)名用戶自定義函數(shù)名使用使用sp_help查看用戶自定義函數(shù)的一般信息，其語(yǔ)法格式為：查看用戶自定義函數(shù)的一般信息，其語(yǔ)法格式為： sp_help

18、 用戶自定義函數(shù)名用戶自定義函數(shù)名例：使用有關(guān)系統(tǒng)過(guò)程查看例：使用有關(guān)系統(tǒng)過(guò)程查看STUDENT數(shù)據(jù)庫(kù)中名為數(shù)據(jù)庫(kù)中名為XUEFEN的用戶的用戶自定義函數(shù)的文本信息。其程序代碼如下：自定義函數(shù)的文本信息。其程序代碼如下： USE STUDENT GO SP_HELPTEXT XUEFEN GOv 查看用戶自定義函數(shù)的屬性查看用戶自定義函數(shù)的屬性企業(yè)管理器企業(yè)管理器在企業(yè)管理器中，用戶可以很方便地查看用戶定義函數(shù)的信息，其步在企業(yè)管理器中，用戶可以很方便地查看用戶定義函數(shù)的信息，其步驟為：驟為： 1）在企業(yè)管理器中，選擇需要查看的函數(shù)所在的數(shù)據(jù)庫(kù)。）在企業(yè)管理器中，選擇需要查看的函數(shù)所在的數(shù)據(jù)庫(kù)

19、。 2）在詳細(xì)信息窗格中雙擊戶定義函數(shù)圖標(biāo)，在詳細(xì)窗格中顯示出）在詳細(xì)信息窗格中雙擊戶定義函數(shù)圖標(biāo)，在詳細(xì)窗格中顯示出用戶建立的函數(shù)名稱。用戶建立的函數(shù)名稱。 3）右擊需要查看的用戶定義函數(shù)，從彈出的快捷菜單中選擇）右擊需要查看的用戶定義函數(shù)，從彈出的快捷菜單中選擇“屬屬性性”命令。命令。 4）當(dāng)出現(xiàn)）當(dāng)出現(xiàn)“用戶定義函數(shù)數(shù)性用戶定義函數(shù)數(shù)性”對(duì)話框時(shí)，在對(duì)話框時(shí)，在“文本文本”框中可以框中可以瀏覽用戶定義函數(shù)的文本信息。單擊瀏覽用戶定義函數(shù)的文本信息。單擊“權(quán)限權(quán)限”按鈕查看用戶定義函數(shù)按鈕查看用戶定義函數(shù)的權(quán)限。的權(quán)限。v 修改用戶自定義函數(shù)的屬性修改用戶自定義函數(shù)的屬性企業(yè)管理器企業(yè)管理

20、器1、使用企業(yè)管理器修改用戶定義函數(shù)，在企業(yè)管理器中修改用戶自、使用企業(yè)管理器修改用戶定義函數(shù)，在企業(yè)管理器中修改用戶自定義函數(shù)的步驟為：定義函數(shù)的步驟為： 1）在企業(yè)管理器中，選擇需要修改的函數(shù)所在的數(shù)據(jù)庫(kù)。）在企業(yè)管理器中，選擇需要修改的函數(shù)所在的數(shù)據(jù)庫(kù)。 2）在詳細(xì)信息窗格中雙擊戶定義函數(shù)圖標(biāo)，顯示出用戶建立的函）在詳細(xì)信息窗格中雙擊戶定義函數(shù)圖標(biāo)，顯示出用戶建立的函數(shù)名稱。數(shù)名稱。 3）右擊需要修改的用戶定義函數(shù)，從彈出的快捷菜單中選擇）右擊需要修改的用戶定義函數(shù)，從彈出的快捷菜單中選擇“屬屬性性”命令。命令。 4）當(dāng)出現(xiàn)）當(dāng)出現(xiàn)“用戶定義函數(shù)數(shù)性用戶定義函數(shù)數(shù)性”對(duì)話框時(shí)，在對(duì)話框時(shí)

21、，在“文本文本”框中修改框中修改用戶定義函數(shù)的文本。用戶定義函數(shù)的文本。 5）修改完后，單擊）修改完后，單擊“檢查語(yǔ)法檢查語(yǔ)法”按鈕，語(yǔ)法檢查通過(guò)后，單擊按鈕，語(yǔ)法檢查通過(guò)后，單擊“確定確定”按鈕，完成函數(shù)修改。按鈕，完成函數(shù)修改。v 修改用戶自定義函數(shù)的屬性修改用戶自定義函數(shù)的屬性查詢分析器查詢分析器2、使用、使用SQL命令修改用戶自定義函數(shù)，使用命令修改用戶自定義函數(shù)，使用ALTER FUNCTION 命命令可以修改用戶自定義函數(shù)。修改由令可以修改用戶自定義函數(shù)。修改由CREATE FUNCTION 語(yǔ)句創(chuàng)建語(yǔ)句創(chuàng)建的現(xiàn)有用戶定義函數(shù)，不會(huì)更改權(quán)限，也不影響相關(guān)的函數(shù)、存儲(chǔ)過(guò)的現(xiàn)有用戶定義函數(shù)，不會(huì)更改權(quán)限，也不影響相關(guān)的函數(shù)、存儲(chǔ)過(guò)程或觸發(fā)器。其語(yǔ)法格式如下：程或觸發(fā)器。其語(yǔ)法格式如下：ALTER FUNCTION owner_n