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

2、戶自定義函數是由一條或多條T-SQL語句組成的子程序，保存在數據庫內。它可以具有多個輸入參數，并返回一個標量值（單個數據值）或一個表。 第3頁，共27頁，2022年，5月20日，9點32分，星期一用戶自定義函數有以下3種類型：返回單值的標量函數；類似于視圖的可更新內嵌表值函數；使用代碼創建結果集的多語句表值函數。第4頁，共27頁，2022年，5月20日，9點32分，星期一14.2.1 標量函數14.2.2 內嵌表值函數14.2.3 多語句表值函數14.2 創建用戶自定義函數 第5頁，共27頁，2022年，5月20日，9點32分，星期一 標量函數類似于系統內置函數。函數的輸入參數可以是所有標量數

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

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

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

6、)SELECT dbo.fage(0901002, default)執行結果如下： -2624第9頁，共27頁，2022年，5月20日，9點32分，星期一查看定義：select * from sys.sql_modules 或select definition from sys.sql_modules where object_id=OBJECT_ID(fsum)第10頁，共27頁，2022年，5月20日，9點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頁，共27頁，2022年，5月20日，9點32分，星期一use BlueSkyDBgoselect * ,dbo.FunBookSale(bookID) as SaleQuantityfrom Booksgo第12頁，共27頁，2022年，5月20日，9點32分，星期一調用標量函數變量函數注意事項需要說明的是，標量函數必須是確定性

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

9、頁，2022年，5月20日，9點32分，星期一創建內嵌表值函數創建用戶定義內嵌表值函數的語法格式：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頁，共27頁，2022年，5月20日，9點32分，星期一調用內嵌表值函數用戶定義內嵌表值函數創建以后，可以在SELECT語句的FROM子句中調用它。例如：在SELECT語句的FROM子句中調用內嵌表值函數。SELECT Stu_id, Stu_name FROM fstu1()執行結果如下：Stu_id Stu_name-0901001 張三0901002 李四第一次調用內嵌表值函數時，系統性能會有明顯的下降，因為系統需要編譯函數的代碼，并將編譯的結果存放于內存中。一旦編譯完成后，此后對函數的調用執

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

12、LECT * FROM V_stuWHERE Stu_classid = 09計一執行結果如下：Stu_id Stu_name Stu_classid-0901001 張三 09計一 第18頁，共27頁，2022年，5月20日，9點32分，星期一與視圖相比，在函數中可以通過為預編譯的SQL SELECT語句傳遞補貼的參數對返回的結果集進行限制，如下:創建一個自定義函數。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關鍵字來調用該函數，就會返回對應班級的學生信息：SELECT * FROM dbo.fstu2(DEFAULT)執行結果如下：Stu_id Stu_name Stu_classid-0901001 張三 09計一 0901002 李四 09計二第19頁，共27頁，2022年，5月20日，9點32分，星期一如果通過輸入參數傳遞了班級代碼，函數內預編譯的SELECT語句就會返回對應班級的學生信息。SELECT * FROM dbo.fstu2(09計一)執行結果如下

14、：Stu_id Stu_name Stu_classid-0901001 張三 09計一 第20頁，共27頁，2022年，5月20日，9點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頁，共27頁，2022年，5月20日，9點32分，星期一 多語句表值函數 多語句表值用戶定義函數既可以像標量函數那樣包含復雜的代碼，也可以像內嵌表值函數那樣返回一個結果集。多語句表值函數會創建一個表變量，并使用代碼對它進行填充，然后返回這個表變量，以便在SELECT語句中使用它。第22頁，共27頁，2022年，5月20日，9點32分，星期一創建多語句表值函數創建多語句表值函數的語法格式：CREATE FUNCTION FunctionName(InputParameters)RETURNS TableName Table(Columns)ASBEGINInsert sqlstateme

16、ntRETURN END第23頁，共27頁，2022年，5月20日，9點32分，星期一創建一個多語句表值用戶定義函數，其作用為將屬于特定系部的學生信息放入表變量中作為函數的輸出。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頁，共27頁，2022年，5月20日，9點32分，星期一調用函數要執行多語句表值函數，可以在SELECT語句的FROM子句中使用它。例如：SELECT * FROM dbo.fstu3(計算機系)執行結果如下：stu_id stu_name dept_id-0301001 張三 計算機系 0301002 李四 計算機系 第25頁，共27頁，2022年，5月20日，9點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