補充 09101數(shù)據(jù)庫系統(tǒng)原理及應(yīng)用-自定義函數(shù)

1、數(shù)據(jù)庫系統(tǒng)原理及應(yīng)用數(shù)據(jù)庫系統(tǒng)原理及應(yīng)用SQL Server 2000編程編程-自定義函數(shù)自定義函數(shù)2使用函數(shù)使用函數(shù)函數(shù)是函數(shù)是T-SQL的語法元素，它接收一系列參數(shù)，計算的語法元素，它接收一系列參數(shù)，計算后給調(diào)用者返回值后給調(diào)用者返回值用法：用法：1.可用于選擇和賦值中可用于選擇和賦值中declare d1 datetimeset d1=getdate()select d1goselect orderid, DateDiff(day,OrderDate,RequiredDate) 天數(shù)天數(shù)from orders go3使用函數(shù)使用函數(shù)2.用于過濾規(guī)則中用于過濾規(guī)則中select * fro

2、m orderswhere RequiredDate0 print 10*sqrt(a);else print 10*sqrt(-a)4使用函數(shù)使用函數(shù)4.作為檢查和默認規(guī)則作為檢查和默認規(guī)則create table stu1(sno char(8) primary key, sname char(8), sen datetime default getdate() -入學(xué)日期入學(xué)日期)insert into stu1(sno,sname) values (1,111)select * from stu15使用函數(shù)使用函數(shù)5.替代表替代表SELECT a.*FROM OPENROWSET(SQ

3、LOLEDB,服務(wù)器名服務(wù)器名; sa;pass, SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname) AS a6實現(xiàn)用戶定義函數(shù)實現(xiàn)用戶定義函數(shù)用戶定義函數(shù)用戶定義函數(shù)用戶定義函數(shù)的定義用戶定義函數(shù)的定義用戶定義函數(shù)示例用戶定義函數(shù)示例推薦操作推薦操作7用戶定義函數(shù)用戶定義函數(shù)SQL Server 2000 允許用戶設(shè)計自己的函數(shù)，以允許用戶設(shè)計自己的函數(shù)，以補充和擴展系統(tǒng)提供（內(nèi)置）函數(shù)的功能補充和擴展系統(tǒng)提供（內(nèi)置）函數(shù)的功能用戶定義函數(shù)采用零或多個輸入?yún)?shù)并返回標(biāo)用戶定義函數(shù)采用零或多個輸入?yún)?shù)并返回標(biāo)量值或表量值或表

4、用戶定義函數(shù)的輸入?yún)?shù)可以是除用戶定義函數(shù)的輸入?yún)?shù)可以是除 timestamp、cursor、table 之外的任何數(shù)據(jù)類型；之外的任何數(shù)據(jù)類型；用戶定義函數(shù)不支持輸出參數(shù)；用戶定義函數(shù)不支持輸出參數(shù)；SQL Server 2000 支持三種用戶定義函數(shù)：標(biāo)支持三種用戶定義函數(shù)：標(biāo)量函數(shù)、多語句表值函數(shù)和內(nèi)嵌表值函數(shù)量函數(shù)、多語句表值函數(shù)和內(nèi)嵌表值函數(shù)8用戶定義函數(shù)用戶定義函數(shù)標(biāo)量函數(shù)標(biāo)量函數(shù)標(biāo)量函數(shù)返回一個標(biāo)量（單值）結(jié)果標(biāo)量函數(shù)返回一個標(biāo)量（單值）結(jié)果可在與標(biāo)量函數(shù)返回的數(shù)據(jù)類型相同的值所能可在與標(biāo)量函數(shù)返回的數(shù)據(jù)類型相同的值所能使用的任何位置使用該標(biāo)量函數(shù)，包括使用的任何位置使用該標(biāo)量

5、函數(shù)，包括 SELECT 語句中列的列表和語句中列的列表和 WHERE 子句、表子句、表達式、表定義中的約束表達式，甚至作為表中達式、表定義中的約束表達式，甚至作為表中列的數(shù)據(jù)類型列的數(shù)據(jù)類型標(biāo)量函數(shù)的返回值不能是非標(biāo)量類型（如標(biāo)量函數(shù)的返回值不能是非標(biāo)量類型（如 cursor 或或 table），），不能是不能是 timestamp、text、ntext 或或 image 數(shù)據(jù)類型，也不能是數(shù)據(jù)類型，也不能是用戶定義類型；用戶定義類型；9多語句表值函數(shù)多語句表值函數(shù)n多語句表值函數(shù)返回一個由一條或多條多語句表值函數(shù)返回一個由一條或多條 T-SQL 語語句建立的表，類似于存儲過程句建立的表，類

6、似于存儲過程n與存儲過程不同的是，多語句表值函數(shù)可以在與存儲過程不同的是，多語句表值函數(shù)可以在 SELECT 語句的語句的 FROM 子句中被引用，仿佛視圖子句中被引用，仿佛視圖一樣一樣內(nèi)嵌表值函數(shù)內(nèi)嵌表值函數(shù)n內(nèi)嵌表值函數(shù)返回一個單條內(nèi)嵌表值函數(shù)返回一個單條 SELECT 語句產(chǎn)生的語句產(chǎn)生的結(jié)果的表，類似于視圖結(jié)果的表，類似于視圖n相對于視圖，內(nèi)嵌表值函數(shù)可使用參數(shù)，提供了相對于視圖，內(nèi)嵌表值函數(shù)可使用參數(shù)，提供了更強的適應(yīng)性，擴展了索引視圖的功能更強的適應(yīng)性，擴展了索引視圖的功能用戶定義函數(shù)用戶定義函數(shù)10用戶定義函數(shù)的定義用戶定義函數(shù)的定義創(chuàng)建用戶定義函數(shù)創(chuàng)建用戶定義函數(shù)為用戶定義函數(shù)

7、設(shè)置權(quán)限為用戶定義函數(shù)設(shè)置權(quán)限更改和刪除用戶定義函數(shù)更改和刪除用戶定義函數(shù)11創(chuàng)建用戶定義函數(shù)創(chuàng)建用戶定義函數(shù)CREATE FUNCTION function_name (參數(shù)名參數(shù)名 參數(shù)類型參數(shù)類型 =默認值默認值 ,.n ) RETURNS 標(biāo)量函數(shù)返回值類型標(biāo)量函數(shù)返回值類型/RETURNS TABLE /RETURNS 返回變量名返回變量名 TABLE WITH , .n AS BEGIN function_body RETURN 標(biāo)量表達式標(biāo)量表達式END/RETURN (select語句語句 )/BEGIN function_body RETURNEND12創(chuàng)建用戶定義函數(shù)創(chuàng)建用

8、戶定義函數(shù)n創(chuàng)建用戶定義函數(shù)創(chuàng)建用戶定義函數(shù)l每個完全合法的用戶定義函數(shù)名每個完全合法的用戶定義函數(shù)名 （數(shù)據(jù)庫名數(shù)據(jù)庫名.擁擁有者名有者名.函數(shù)名函數(shù)名）必須惟一）必須惟一l語句指定了輸入?yún)?shù)及它們的數(shù)據(jù)類型、處理指語句指定了輸入?yún)?shù)及它們的數(shù)據(jù)類型、處理指令，以及返回的值及其數(shù)據(jù)類型令，以及返回的值及其數(shù)據(jù)類型n在在 Transact-SQL 語句中使用用戶定義函數(shù)語句中使用用戶定義函數(shù)l標(biāo)量用戶定義函數(shù)可以在任何它們返回值的數(shù)據(jù)標(biāo)量用戶定義函數(shù)可以在任何它們返回值的數(shù)據(jù)類型可以用的地方使用類型可以用的地方使用l表值用戶定義函數(shù)只能在表值用戶定義函數(shù)只能在 SELECT 語句的語句的 FR

9、OM 子句中使用子句中使用l若若 SELECT 子句是在子句是在 DECLARE CURSOR 語句語句中，游標(biāo)必須是中，游標(biāo)必須是 STATIC 或或 READ_ONLY 的的13創(chuàng)建用戶定義函數(shù)創(chuàng)建用戶定義函數(shù)n使用標(biāo)量用戶定義函數(shù)使用標(biāo)量用戶定義函數(shù)l標(biāo)量函數(shù)以和內(nèi)置函數(shù)相同的形式調(diào)用：標(biāo)量函數(shù)以和內(nèi)置函數(shù)相同的形式調(diào)用：擁有者名擁有者名.函數(shù)名函數(shù)名(參數(shù)列表參數(shù)列表)不可省略擁有者名，也不能用命名參數(shù)的形式（不可省略擁有者名，也不能用命名參數(shù)的形式（例如例如 參數(shù)名參數(shù)名=值值），），且參數(shù)也不可以省略（且參數(shù)也不可以省略（但但可以使用可以使用 DEFAULT 關(guān)鍵字指明使用默認值關(guān)

10、鍵字指明使用默認值）lSQL Server 提供少量內(nèi)置用戶定義函數(shù)。與普通提供少量內(nèi)置用戶定義函數(shù)。與普通內(nèi)置函數(shù)不同，它們的名字以內(nèi)置函數(shù)不同，它們的名字以“ fn_”開頭并且使開頭并且使用特定的調(diào)用語法：用特定的調(diào)用語法：:函數(shù)名函數(shù)名(參數(shù)列表參數(shù)列表)其中其中“:”代替了代替了“擁有者名擁有者名”的位置，表明函數(shù)的位置，表明函數(shù)為內(nèi)置用戶定義函數(shù)為內(nèi)置用戶定義函數(shù)14創(chuàng)建用戶定義函數(shù)創(chuàng)建用戶定義函數(shù)n使用標(biāo)量用戶定義函數(shù)（續(xù)）使用標(biāo)量用戶定義函數(shù)（續(xù)）也可以使用也可以使用 EXECUTE 語句調(diào)用標(biāo)量函數(shù)：語句調(diào)用標(biāo)量函數(shù)：EXECUTE 返回變量返回變量 = 函數(shù)名函數(shù)名(參數(shù)列表

11、參數(shù)列表)使用使用 EXECUTE 語句調(diào)用時，不必包括語句調(diào)用時，不必包括“擁有者名擁有者名”，而且可以使用命名參數(shù)：，而且可以使用命名參數(shù)：EXECUTE 返回變量返回變量 = 函數(shù)名函數(shù)名 變量變量=值值 ,變量變量 = 值值當(dāng)使用命名參數(shù)的時候，參數(shù)的次序不必按照在函當(dāng)使用命名參數(shù)的時候，參數(shù)的次序不必按照在函數(shù)中聲明的次序，但必須包括所有參數(shù)，不能省略任數(shù)中聲明的次序，但必須包括所有參數(shù)，不能省略任何參數(shù)何參數(shù)n使用表值用戶定義函數(shù)使用表值用戶定義函數(shù)l表值用戶定義函數(shù)必須使用和內(nèi)置函數(shù)相同的表值用戶定義函數(shù)必須使用和內(nèi)置函數(shù)相同的調(diào)用格調(diào)用格式：式：函數(shù)名函數(shù)名 (參數(shù)列表參數(shù)列表

12、)不需要指定不需要指定“擁有者名擁有者名”，但必須包括所有定義的參，但必須包括所有定義的參數(shù)數(shù)15創(chuàng)建用戶定義函數(shù)創(chuàng)建用戶定義函數(shù)n函數(shù)的限制函數(shù)的限制l非確定性函數(shù)：每次使用相同的輸入值集調(diào)用的非確定性函數(shù)：每次使用相同的輸入值集調(diào)用的時候，返回不同的結(jié)果值的函數(shù)時候，返回不同的結(jié)果值的函數(shù)l用戶定義函數(shù)體中不允許使用內(nèi)置非確定性函數(shù)用戶定義函數(shù)體中不允許使用內(nèi)置非確定性函數(shù)，包括，包括ERRORIDENTITYROWCOUNTTRANCOUNTAPP_NAMECURRENT_TIMESTAMPCURRENT_USERDATENAMEFORMATMESSAGEGETANSINULLGETDA

13、TEGETUTCDATEHOST_IDHOST_NAMEIDENT_INCRIDENT_SEEDIDENTITYNEWIDPERMISSIONSSESSION_USERSTATS_DATESYSTEM_USERTEXTPTRTEXTVALIDUSER_NAME16為用戶定義函數(shù)設(shè)置權(quán)限為用戶定義函數(shù)設(shè)置權(quán)限用戶定義函數(shù)的權(quán)限要求和其他數(shù)據(jù)庫對象的類似用戶定義函數(shù)的權(quán)限要求和其他數(shù)據(jù)庫對象的類似n要創(chuàng)建、更改或刪除用戶定義函數(shù)，必須具有要創(chuàng)建、更改或刪除用戶定義函數(shù)，必須具有 CREATE FUNCTION 權(quán)限權(quán)限n若函數(shù)擁有者之外的用戶要在若函數(shù)擁有者之外的用戶要在 Transact-SQL

14、 語句中使語句中使用函數(shù)，則必須先被授予用函數(shù)，則必須先被授予 EXECUTE 權(quán)限權(quán)限n若函數(shù)是架構(gòu)綁定的，則創(chuàng)建者必須具有函數(shù)所引用的表、若函數(shù)是架構(gòu)綁定的，則創(chuàng)建者必須具有函數(shù)所引用的表、視圖和函數(shù)上的視圖和函數(shù)上的 REFERENCE 權(quán)限權(quán)限n若在若在 CREATE TABLE 或或 ALTER TABLE 語句的語句的 CHECK 約束、約束、DEFAULT 子句或計算列中引用了用戶定義函數(shù)，子句或計算列中引用了用戶定義函數(shù)，則表的擁有者必須具有那個函數(shù)的則表的擁有者必須具有那個函數(shù)的 REFERENCE 權(quán)限權(quán)限17更改和刪除用戶定義函數(shù)更改和刪除用戶定義函數(shù)更改函數(shù)更改函數(shù)語法

15、：語法：ALTER FUNCTION n使用新的函數(shù)定義代替原來的函數(shù)定義使用新的函數(shù)定義代替原來的函數(shù)定義n保留所有已分配的權(quán)限保留所有已分配的權(quán)限刪除函數(shù)刪除函數(shù)語法：語法：DROP FUNCTION n刪除一個用戶定義函數(shù)刪除一個用戶定義函數(shù)18用戶定義函數(shù)示例用戶定義函數(shù)示例使用標(biāo)量用戶定義函數(shù)使用標(biāo)量用戶定義函數(shù)標(biāo)量用戶定義函數(shù)示例標(biāo)量用戶定義函數(shù)示例使用多語句表值函數(shù)使用多語句表值函數(shù)多語句表值函數(shù)示例多語句表值函數(shù)示例使用內(nèi)嵌表值函數(shù)使用內(nèi)嵌表值函數(shù)內(nèi)嵌表值函數(shù)示例內(nèi)嵌表值函數(shù)示例19標(biāo)量函數(shù)創(chuàng)建語法標(biāo)量函數(shù)創(chuàng)建語法CREATE FUNCTION function_name (參

16、數(shù)名參數(shù)名 參數(shù)類型參數(shù)類型 =默認值默認值 ,.n ) RETURNS 返回值類型返回值類型 WITH , .n AS BEGIN function_body RETURN 表達式表達式END20使用標(biāo)量用戶定義函數(shù)使用標(biāo)量用戶定義函數(shù)標(biāo)量函數(shù)返回標(biāo)量函數(shù)返回 RETURNS 子句中定義的數(shù)據(jù)類型的單子句中定義的數(shù)據(jù)類型的單個數(shù)據(jù)值個數(shù)據(jù)值n在在 BEGIN END 塊之間定義了函數(shù)體，包含返塊之間定義了函數(shù)體，包含返回值的一系列回值的一系列 Transact-SQL 語句語句n返回值可以是除了返回值可以是除了 text、ntext、image、cursor 和和timestamp 之外的任

17、何數(shù)據(jù)類型之外的任何數(shù)據(jù)類型額外的限制額外的限制n標(biāo)量函數(shù)只能返回標(biāo)量值；標(biāo)量函數(shù)只能返回標(biāo)量值；n標(biāo)量函數(shù)體內(nèi)不能包括任何非確定性系統(tǒng)函數(shù)；標(biāo)量函數(shù)體內(nèi)不能包括任何非確定性系統(tǒng)函數(shù)；21標(biāo)量用戶定義函數(shù)示例標(biāo)量用戶定義函數(shù)示例USE NorthwindCREATE FUNCTION fn_DateFormat (indate datetime, separator char(1)RETURNS Nchar(20)ASBEGIN RETURN CONVERT(Nvarchar(20), datepart(mm,indate) + separator + CONVERT(Nvarchar(20)

18、, datepart(dd, indate) + separator + CONVERT(Nvarchar(20), datepart(yy, indate)ENDSELECT dbo.fn_DateFormat(GETDATE(), :)n創(chuàng)建函數(shù)創(chuàng)建函數(shù)n調(diào)用函數(shù)調(diào)用函數(shù)22多語句表值函數(shù)多語句表值函數(shù)CREATE FUNCTION function_name (參數(shù)名參數(shù)名 參數(shù)類型參數(shù)類型 =默認值默認值 ,.n ) RETURNS return_variable TABLE WITH , .n AS BEGIN function_body RETURNEND23使用多語句表值函數(shù)使用

19、多語句表值函數(shù)多語句表值函數(shù)多語句表值函數(shù)nBEGIN 和和 END 分隔了函數(shù)體分隔了函數(shù)體nRETURNS 子句指定子句指定 table 作為返回的數(shù)作為返回的數(shù)據(jù)類型據(jù)類型nRETURNS 子句定義了返回表的名字和格子句定義了返回表的名字和格式。返回變量名的使用域限定于函數(shù)局部式。返回變量名的使用域限定于函數(shù)局部24使用多語句表值函數(shù)使用多語句表值函數(shù)函數(shù)體內(nèi)只允許如下語句函數(shù)體內(nèi)只允許如下語句n賦值語句賦值語句n流控制語句流控制語句n用于定義函數(shù)局部數(shù)據(jù)變量和游標(biāo)的用于定義函數(shù)局部數(shù)據(jù)變量和游標(biāo)的 DECLARE 語語句句nSELECT 語句，該語句包含帶有表達式的選擇列表，語句，該語

20、句包含帶有表達式的選擇列表，其中的表達式將值賦予函數(shù)的局部變量其中的表達式將值賦予函數(shù)的局部變量n游標(biāo)操作，該操作引用在函數(shù)中聲明、打開、關(guān)閉游標(biāo)操作，該操作引用在函數(shù)中聲明、打開、關(guān)閉和釋放的局部游標(biāo)。僅允許使用和釋放的局部游標(biāo)。僅允許使用 FETCH 語句通過語句通過 INTO 子句給局部變量賦值，不允許使用子句給局部變量賦值，不允許使用 FETCH 語語句將數(shù)據(jù)返回到客戶端句將數(shù)據(jù)返回到客戶端n修改函數(shù)的局部修改函數(shù)的局部 table 變量的變量的 INSERT、UPDATE 和和 DELETE 語句語句25使用多語句表值函數(shù)使用多語句表值函數(shù)n函數(shù)體內(nèi)不允許出現(xiàn)返回非函數(shù)返回值的語函數(shù)

21、體內(nèi)不允許出現(xiàn)返回非函數(shù)返回值的語句以及產(chǎn)生副作用的語句句以及產(chǎn)生副作用的語句副作用：指的是對函數(shù)外某些永久性狀態(tài)的副作用：指的是對函數(shù)外某些永久性狀態(tài)的改變改變n下列語句是不允許的：對數(shù)據(jù)庫表的更新、下列語句是不允許的：對數(shù)據(jù)庫表的更新、全局游標(biāo)語句、創(chuàng)建對象的語句、事務(wù)控制全局游標(biāo)語句、創(chuàng)建對象的語句、事務(wù)控制語句語句26多語句表值函數(shù)示例多語句表值函數(shù)示例USE NorthwindGOCREATE FUNCTION fn_Employees (length nvarchar(9)RETURNS fn_Employees table (EmployeeID int PRIMARY KEY

22、NOT NULL, EmployeeName nvarchar(61) NOT NULL)ASBEGIN IF length = ShortName INSERT fn_Employees SELECT EmployeeID, LastName FROM Employees ELSE IF length = LongName INSERT fn_Employees SELECT EmployeeID, (FirstName + + LastName) FROM EmployeesRETURNEND SELECT * FROM dbo.fn_Employees(LongName)或或SELECT * FROM dbo.fn_Employees(ShortName) 27內(nèi)嵌表值函數(shù)內(nèi)嵌表值函數(shù)CREATE FUNCTION function_name (參數(shù)名參數(shù)名 參數(shù)類型參數(shù)類型 =默認值默認值 ,.n ) RETURNS TABLE WITH , .n AS RETURN ( select-stmt ) 28使用內(nèi)嵌表值函數(shù)使用內(nèi)嵌表值函數(shù)內(nèi)嵌表值函數(shù)返回表，可在內(nèi)嵌表值函數(shù)返回表，可在 FROM 子句中被引用，子句中被引用，就像視圖一樣就像