函數(shù)的創(chuàng)建與管理

1、函數(shù)的創(chuàng)建與管理第1頁(yè)，共27頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一 函數(shù)是接受參數(shù)、執(zhí)行操作（例如復(fù)雜計(jì)算）并將操作結(jié)果以值的形式返回的例程。返回值可以是單個(gè)標(biāo)量值或結(jié)果集。SQL Server 2005中有多種函數(shù)，根據(jù)返回值的類型和是否由系統(tǒng)提供，分為標(biāo)量函數(shù)、表值函數(shù)和內(nèi)置函數(shù)。SQL Server 2005支持3種用戶定義函數(shù)：標(biāo)量函數(shù)、表值函數(shù)和聚合函數(shù)。本章介紹用戶定義函數(shù)的創(chuàng)建、修改及刪除。第2頁(yè)，共27頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一14.1 用戶自定義函數(shù)簡(jiǎn)介 SQL Server不但提供了系統(tǒng)內(nèi)置函數(shù)，而且還允許用戶根據(jù)實(shí)際需要?jiǎng)?chuàng)建用戶自定義函數(shù)。用

2、戶自定義函數(shù)是由一條或多條T-SQL語(yǔ)句組成的子程序，保存在數(shù)據(jù)庫(kù)內(nèi)。它可以具有多個(gè)輸入?yún)?shù)，并返回一個(gè)標(biāo)量值（單個(gè)數(shù)據(jù)值）或一個(gè)表。 第3頁(yè)，共27頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一用戶自定義函數(shù)有以下3種類型：返回單值的標(biāo)量函數(shù)；類似于視圖的可更新內(nèi)嵌表值函數(shù)；使用代碼創(chuàng)建結(jié)果集的多語(yǔ)句表值函數(shù)。第4頁(yè)，共27頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一14.2.1 標(biāo)量函數(shù)14.2.2 內(nèi)嵌表值函數(shù)14.2.3 多語(yǔ)句表值函數(shù)14.2 創(chuàng)建用戶自定義函數(shù) 第5頁(yè)，共27頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一 標(biāo)量函數(shù)類似于系統(tǒng)內(nèi)置函數(shù)。函數(shù)的輸入?yún)?shù)可以是所有標(biāo)量數(shù)

3、據(jù)類型，輸出參數(shù)的類型可以是除了text、nText、image、cursor、timestamp以外的任何數(shù)據(jù)類型，函數(shù)主體在BEGIN-END塊中定義。14.2.1 標(biāo)量函數(shù)第6頁(yè)，共27頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一標(biāo)量函數(shù)標(biāo)量函數(shù)是返回單個(gè)值的函數(shù)。標(biāo)量函數(shù)可以接受多個(gè)參數(shù)進(jìn)行計(jì)算，并且返回單個(gè)值。標(biāo)量函數(shù)一經(jīng)定義后，就可以在SQL Server的表達(dá)式（如表的計(jì)算列）中使用該函數(shù)。創(chuàng)建標(biāo)量函數(shù)的語(yǔ)法：CREATE FUNCTION FunctionName(InputParameters)RETURNS DatatypeASBEGIN Sqlstatement RET

4、URN ExpressionEND第7頁(yè)，共27頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一創(chuàng)建一個(gè)用戶定義標(biāo)量函數(shù)fsum，其功能為求兩個(gè)整數(shù)的和。CREATE FUNCTION dbo.fsum (num1 INT, num2 INT = 6) -參數(shù)num2默認(rèn)值為6RETURNS INT -函數(shù)返回值為整數(shù)類型ASBEGIN RETURN num1 + num2 -返回值表達(dá)式ENDGOSELECT dbo.fsum (2,8)SELECT dbo.fsum (7, default)執(zhí)行結(jié)果如下：-1013第8頁(yè)，共27頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一下面的用戶定義標(biāo)量

5、函數(shù)可以根據(jù)學(xué)生的學(xué)號(hào)查詢學(xué)生的出生日期，由此計(jì)算出并返回學(xué)生的年齡值。-參數(shù)sid為學(xué)生學(xué)號(hào)，year為當(dāng)前年份CREATE FUNCTION dbo.fage(sid char(10),year INT = 2004) RETURNS INT -函數(shù)返回值為整數(shù)類型ASBEGIN DECLARE birdate DATETIME SELECT birdate = stu_birthdate FROM Student WHERE stu_id = sid RETURN year - YEAR(birdate) -返回值表達(dá)式 ENDGOSELECT dbo.fage(0901001,2004

6、)SELECT dbo.fage(0901002, default)執(zhí)行結(jié)果如下： -2624第9頁(yè)，共27頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一查看定義：select * from sys.sql_modules 或select definition from sys.sql_modules where object_id=OBJECT_ID(fsum)第10頁(yè)，共27頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一例14-1use BlueSkyDBgocreate function FunBookSale(bookID int)return intasbegindeclare qu

7、antity intselect quantity=sum(quantity) from OrdreItems where bookID=bookIDif(quantity is null)set quantity =0return quantity endgo第11頁(yè)，共27頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一use BlueSkyDBgoselect * ,dbo.FunBookSale(bookID) as SaleQuantityfrom Booksgo第12頁(yè)，共27頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一調(diào)用標(biāo)量函數(shù)變量函數(shù)注意事項(xiàng)需要說(shuō)明的是，標(biāo)量函數(shù)必須是確定性

8、的，這意味著，如果使用同樣的輸入?yún)?shù)反復(fù)調(diào)用它，它每次都應(yīng)當(dāng)返回同樣的結(jié)果值。不能在標(biāo)量函數(shù)中使用返回可變數(shù)據(jù)的函數(shù)和全局變量，如connections、getgate()、newid()等。在表達(dá)式中任何可以使用單個(gè)值（數(shù)據(jù)類型相同）的地方都可以使用標(biāo)量函數(shù)。調(diào)用用戶定義標(biāo)量函數(shù)時(shí)，必須始終使用兩個(gè)部分構(gòu)成的名字（owner.functionname）。第13頁(yè)，共27頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一14.2.2 內(nèi)嵌表值函數(shù)第二種用戶定義函數(shù)是內(nèi)嵌表值函數(shù)。內(nèi)嵌表值函數(shù)和視圖類似，都包含有一條存儲(chǔ)的SELECT語(yǔ)句。內(nèi)嵌表值函數(shù)可以使用參數(shù)，也可以不使用參數(shù)。第14頁(yè)，共27

9、頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一創(chuàng)建內(nèi)嵌表值函數(shù)創(chuàng)建用戶定義內(nèi)嵌表值函數(shù)的語(yǔ)法格式：CREATE FUNCTION FunctionName(InputParameters)RETURNS TableASRETURN (select statement)例如：CREATE FUNCTION fstu1()RETURNS TableASRETURN (SELECT student.Stu_id, student.Stu_name, student. Stu_addr,class.class_id, class.class_nameFROM Student INNER JOIN Cl

10、assON Student.stu_classid = Class. class_id )第15頁(yè)，共27頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一調(diào)用內(nèi)嵌表值函數(shù)用戶定義內(nèi)嵌表值函數(shù)創(chuàng)建以后，可以在SELECT語(yǔ)句的FROM子句中調(diào)用它。例如：在SELECT語(yǔ)句的FROM子句中調(diào)用內(nèi)嵌表值函數(shù)。SELECT Stu_id, Stu_name FROM fstu1()執(zhí)行結(jié)果如下：Stu_id Stu_name-0901001 張三0901002 李四第一次調(diào)用內(nèi)嵌表值函數(shù)時(shí)，系統(tǒng)性能會(huì)有明顯的下降，因?yàn)橄到y(tǒng)需要編譯函數(shù)的代碼，并將編譯的結(jié)果存放于內(nèi)存中。一旦編譯完成后，此后對(duì)函數(shù)的調(diào)用執(zhí)

11、行速度都將很快。第16頁(yè)，共27頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一使用參數(shù)與視圖相比，內(nèi)嵌表值函數(shù)的一個(gè)優(yōu)點(diǎn)是可以在預(yù)編譯的SELECT語(yǔ)句中使用參數(shù)，而視圖則不能使用參數(shù)，它只能在調(diào)用視圖的SELECT語(yǔ)句中使用WHERE子句來(lái)實(shí)現(xiàn)。以下舉例說(shuō)明視圖和函數(shù)在運(yùn)行時(shí)限制結(jié)果集的不同方法。例如：CREATE VIEW V_stuASSELECT Stu_id, Stu_name, Stu_classid FROM Student第17頁(yè)，共27頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一要查詢某個(gè)班級(jí)的學(xué)生信息，需要在下面調(diào)用視圖的SELECT語(yǔ)句的WHERE子句的條件中實(shí)現(xiàn)：SE

12、LECT * FROM V_stuWHERE Stu_classid = 09計(jì)一執(zhí)行結(jié)果如下：Stu_id Stu_name Stu_classid-0901001 張三 09計(jì)一 第18頁(yè)，共27頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一與視圖相比，在函數(shù)中可以通過(guò)為預(yù)編譯的SQL SELECT語(yǔ)句傳遞補(bǔ)貼的參數(shù)對(duì)返回的結(jié)果集進(jìn)行限制，如下:創(chuàng)建一個(gè)自定義函數(shù)。CREATE FUNCTION dbo.fstu2(classid char(10) = null)RETURNS TableASRETURN (SELECT Stu_id, Stu_name, Stu_classid FROM

13、 Student WHERE stu_classid = classid OR classid IS NULL)如果使用default關(guān)鍵字來(lái)調(diào)用該函數(shù)，就會(huì)返回對(duì)應(yīng)班級(jí)的學(xué)生信息：SELECT * FROM dbo.fstu2(DEFAULT)執(zhí)行結(jié)果如下：Stu_id Stu_name Stu_classid-0901001 張三 09計(jì)一 0901002 李四 09計(jì)二第19頁(yè)，共27頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一如果通過(guò)輸入?yún)?shù)傳遞了班級(jí)代碼，函數(shù)內(nèi)預(yù)編譯的SELECT語(yǔ)句就會(huì)返回對(duì)應(yīng)班級(jí)的學(xué)生信息。SELECT * FROM dbo.fstu2(09計(jì)一)執(zhí)行結(jié)果如下

14、：Stu_id Stu_name Stu_classid-0901001 張三 09計(jì)一 第20頁(yè)，共27頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一例14.4use BlueSkyDBgocreate function funbookselect(catecoryname nvarchar(50)returns tablewith encryptionas return(select b.* from Books b join Categories c on b.categoryCode=c.categoryCodewhere c.categoryName=b.categoryName)g

15、o第21頁(yè)，共27頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一 多語(yǔ)句表值函數(shù) 多語(yǔ)句表值用戶定義函數(shù)既可以像標(biāo)量函數(shù)那樣包含復(fù)雜的代碼，也可以像內(nèi)嵌表值函數(shù)那樣返回一個(gè)結(jié)果集。多語(yǔ)句表值函數(shù)會(huì)創(chuàng)建一個(gè)表變量，并使用代碼對(duì)它進(jìn)行填充，然后返回這個(gè)表變量，以便在SELECT語(yǔ)句中使用它。第22頁(yè)，共27頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一創(chuàng)建多語(yǔ)句表值函數(shù)創(chuàng)建多語(yǔ)句表值函數(shù)的語(yǔ)法格式：CREATE FUNCTION FunctionName(InputParameters)RETURNS TableName Table(Columns)ASBEGINInsert sqlstateme

16、ntRETURN END第23頁(yè)，共27頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一創(chuàng)建一個(gè)多語(yǔ)句表值用戶定義函數(shù)，其作用為將屬于特定系部的學(xué)生信息放入表變量中作為函數(shù)的輸出。CREATE FUNCTION fstu3(deptid char(10)RETURNS stu Table(stu_id char(7),stu_name varchar(10),dept_id char(10)ASBEGININSERT stu SELECT Student.stu_id, Student.stu_name,Dept.id FROM Student,Class,DeptWHERE Student.s

17、tu_classid = Class.id AND Class.deptid = Dept.id AND Dept.id = deptidRETURN END第24頁(yè)，共27頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一調(diào)用函數(shù)要執(zhí)行多語(yǔ)句表值函數(shù)，可以在SELECT語(yǔ)句的FROM子句中使用它。例如：SELECT * FROM dbo.fstu3(計(jì)算機(jī)系)執(zhí)行結(jié)果如下：stu_id stu_name dept_id-0301001 張三 計(jì)算機(jī)系 0301002 李四 計(jì)算機(jī)系 第25頁(yè)，共27頁(yè)，2022年，5月20日，9點(diǎn)32分，星期一例14.7use BlueSkyDBgocreate

18、 function funorderofcustomer(customerID int)returns orderofcustomer table(customerName nvarchar(20) not null,orderID int not null,orderDate datetime not null,title nvarchar(50) not null,unitPrice pricedecimal not null,quantity int not null)asbegininsert into orderofcustomerselect c.customerName,o.orderID,o.orderdate,b.title,b.unitPrice,i