补充 09101数据库系统原理及应用-自定义函数

1、数据库系统原理及应用数据库系统原理及应用SQL Server 2000编程编程-自定义函数自定义函数2使用函数使用函数函数是函数是T-SQL的语法元素，它接收一系列参数，计算的语法元素，它接收一系列参数，计算后给调用者返回值后给调用者返回值用法：用法：1.可用于选择和赋值中可用于选择和赋值中declare d1 datetimeset d1=getdate()select d1goselect orderid, DateDiff(day,OrderDate,RequiredDate) 天数天数from orders go3使用函数使用函数2.用于过滤规则中用于过滤规则中select * fro

2、m orderswhere RequiredDate0 print 10*sqrt(a);else print 10*sqrt(-a)4使用函数使用函数4.作为检查和默认规则作为检查和默认规则create table stu1(sno char(8) primary key, sname char(8), sen datetime default getdate() -入学日期入学日期)insert into stu1(sno,sname) values (1,111)select * from stu15使用函数使用函数5.替代表替代表SELECT a.*FROM OPENROWSET(SQ

3、LOLEDB,服务器名服务器名; sa;pass, SELECT * FROM pubs.dbo.authors ORDER BY au_lname, au_fname) AS a6实现用户定义函数实现用户定义函数用户定义函数用户定义函数用户定义函数的定义用户定义函数的定义用户定义函数示例用户定义函数示例推荐操作推荐操作7用户定义函数用户定义函数SQL Server 2000 允许用户设计自己的函数，以允许用户设计自己的函数，以补充和扩展系统提供（内置）函数的功能补充和扩展系统提供（内置）函数的功能用户定义函数采用零或多个输入参数并返回标用户定义函数采用零或多个输入参数并返回标量值或表量值或表

4、用户定义函数的输入参数可以是除用户定义函数的输入参数可以是除 timestamp、cursor、table 之外的任何数据类型；之外的任何数据类型；用户定义函数不支持输出参数；用户定义函数不支持输出参数；SQL Server 2000 支持三种用户定义函数：标支持三种用户定义函数：标量函数、多语句表值函数和内嵌表值函数量函数、多语句表值函数和内嵌表值函数8用户定义函数用户定义函数标量函数标量函数标量函数返回一个标量（单值）结果标量函数返回一个标量（单值）结果可在与标量函数返回的数据类型相同的值所能可在与标量函数返回的数据类型相同的值所能使用的任何位置使用该标量函数，包括使用的任何位置使用该标量

5、函数，包括 SELECT 语句中列的列表和语句中列的列表和 WHERE 子句、表子句、表达式、表定义中的约束表达式，甚至作为表中达式、表定义中的约束表达式，甚至作为表中列的数据类型列的数据类型标量函数的返回值不能是非标量类型（如标量函数的返回值不能是非标量类型（如 cursor 或或 table），），不能是不能是 timestamp、text、ntext 或或 image 数据类型，也不能是数据类型，也不能是用户定义类型；用户定义类型；9多语句表值函数多语句表值函数n多语句表值函数返回一个由一条或多条多语句表值函数返回一个由一条或多条 T-SQL 语语句建立的表，类似于存储过程句建立的表，类

6、似于存储过程n与存储过程不同的是，多语句表值函数可以在与存储过程不同的是，多语句表值函数可以在 SELECT 语句的语句的 FROM 子句中被引用，仿佛视图子句中被引用，仿佛视图一样一样内嵌表值函数内嵌表值函数n内嵌表值函数返回一个单条内嵌表值函数返回一个单条 SELECT 语句产生的语句产生的结果的表，类似于视图结果的表，类似于视图n相对于视图，内嵌表值函数可使用参数，提供了相对于视图，内嵌表值函数可使用参数，提供了更强的适应性，扩展了索引视图的功能更强的适应性，扩展了索引视图的功能用户定义函数用户定义函数10用户定义函数的定义用户定义函数的定义创建用户定义函数创建用户定义函数为用户定义函数

7、设置权限为用户定义函数设置权限更改和删除用户定义函数更改和删除用户定义函数11创建用户定义函数创建用户定义函数CREATE FUNCTION function_name (参数名参数名 参数类型参数类型 =默认值默认值 ,.n ) RETURNS 标量函数返回值类型标量函数返回值类型/RETURNS TABLE /RETURNS 返回变量名返回变量名 TABLE WITH , .n AS BEGIN function_body RETURN 标量表达式标量表达式END/RETURN (select语句语句 )/BEGIN function_body RETURNEND12创建用户定义函数创建用

8、户定义函数n创建用户定义函数创建用户定义函数l每个完全合法的用户定义函数名每个完全合法的用户定义函数名 （数据库名数据库名.拥拥有者名有者名.函数名函数名）必须惟一）必须惟一l语句指定了输入参数及它们的数据类型、处理指语句指定了输入参数及它们的数据类型、处理指令，以及返回的值及其数据类型令，以及返回的值及其数据类型n在在 Transact-SQL 语句中使用用户定义函数语句中使用用户定义函数l标量用户定义函数可以在任何它们返回值的数据标量用户定义函数可以在任何它们返回值的数据类型可以用的地方使用类型可以用的地方使用l表值用户定义函数只能在表值用户定义函数只能在 SELECT 语句的语句的 FR

9、OM 子句中使用子句中使用l若若 SELECT 子句是在子句是在 DECLARE CURSOR 语句语句中，游标必须是中，游标必须是 STATIC 或或 READ_ONLY 的的13创建用户定义函数创建用户定义函数n使用标量用户定义函数使用标量用户定义函数l标量函数以和内置函数相同的形式调用：标量函数以和内置函数相同的形式调用：拥有者名拥有者名.函数名函数名(参数列表参数列表)不可省略拥有者名，也不能用命名参数的形式（不可省略拥有者名，也不能用命名参数的形式（例如例如 参数名参数名=值值），），且参数也不可以省略（且参数也不可以省略（但但可以使用可以使用 DEFAULT 关键字指明使用默认值关

10、键字指明使用默认值）lSQL Server 提供少量内置用户定义函数。与普通提供少量内置用户定义函数。与普通内置函数不同，它们的名字以内置函数不同，它们的名字以“ fn_”开头并且使开头并且使用特定的调用语法：用特定的调用语法：:函数名函数名(参数列表参数列表)其中其中“:”代替了代替了“拥有者名拥有者名”的位置，表明函数的位置，表明函数为内置用户定义函数为内置用户定义函数14创建用户定义函数创建用户定义函数n使用标量用户定义函数（续）使用标量用户定义函数（续）也可以使用也可以使用 EXECUTE 语句调用标量函数：语句调用标量函数：EXECUTE 返回变量返回变量 = 函数名函数名(参数列表

11、参数列表)使用使用 EXECUTE 语句调用时，不必包括语句调用时，不必包括“拥有者名拥有者名”，而且可以使用命名参数：，而且可以使用命名参数：EXECUTE 返回变量返回变量 = 函数名函数名 变量变量=值值 ,变量变量 = 值值当使用命名参数的时候，参数的次序不必按照在函当使用命名参数的时候，参数的次序不必按照在函数中声明的次序，但必须包括所有参数，不能省略任数中声明的次序，但必须包括所有参数，不能省略任何参数何参数n使用表值用户定义函数使用表值用户定义函数l表值用户定义函数必须使用和内置函数相同的表值用户定义函数必须使用和内置函数相同的调用格调用格式：式：函数名函数名 (参数列表参数列表

12、)不需要指定不需要指定“拥有者名拥有者名”，但必须包括所有定义的参，但必须包括所有定义的参数数15创建用户定义函数创建用户定义函数n函数的限制函数的限制l非确定性函数：每次使用相同的输入值集调用的非确定性函数：每次使用相同的输入值集调用的时候，返回不同的结果值的函数时候，返回不同的结果值的函数l用户定义函数体中不允许使用内置非确定性函数用户定义函数体中不允许使用内置非确定性函数，包括，包括ERRORIDENTITYROWCOUNTTRANCOUNTAPP_NAMECURRENT_TIMESTAMPCURRENT_USERDATENAMEFORMATMESSAGEGETANSINULLGETDA

13、TEGETUTCDATEHOST_IDHOST_NAMEIDENT_INCRIDENT_SEEDIDENTITYNEWIDPERMISSIONSSESSION_USERSTATS_DATESYSTEM_USERTEXTPTRTEXTVALIDUSER_NAME16为用户定义函数设置权限为用户定义函数设置权限用户定义函数的权限要求和其他数据库对象的类似用户定义函数的权限要求和其他数据库对象的类似n要创建、更改或删除用户定义函数，必须具有要创建、更改或删除用户定义函数，必须具有 CREATE FUNCTION 权限权限n若函数拥有者之外的用户要在若函数拥有者之外的用户要在 Transact-SQL

14、 语句中使语句中使用函数，则必须先被授予用函数，则必须先被授予 EXECUTE 权限权限n若函数是架构绑定的，则创建者必须具有函数所引用的表、若函数是架构绑定的，则创建者必须具有函数所引用的表、视图和函数上的视图和函数上的 REFERENCE 权限权限n若在若在 CREATE TABLE 或或 ALTER TABLE 语句的语句的 CHECK 约束、约束、DEFAULT 子句或计算列中引用了用户定义函数，子句或计算列中引用了用户定义函数，则表的拥有者必须具有那个函数的则表的拥有者必须具有那个函数的 REFERENCE 权限权限17更改和删除用户定义函数更改和删除用户定义函数更改函数更改函数语法

15、：语法：ALTER FUNCTION n使用新的函数定义代替原来的函数定义使用新的函数定义代替原来的函数定义n保留所有已分配的权限保留所有已分配的权限删除函数删除函数语法：语法：DROP FUNCTION n删除一个用户定义函数删除一个用户定义函数18用户定义函数示例用户定义函数示例使用标量用户定义函数使用标量用户定义函数标量用户定义函数示例标量用户定义函数示例使用多语句表值函数使用多语句表值函数多语句表值函数示例多语句表值函数示例使用内嵌表值函数使用内嵌表值函数内嵌表值函数示例内嵌表值函数示例19标量函数创建语法标量函数创建语法CREATE FUNCTION function_name (参

16、数名参数名 参数类型参数类型 =默认值默认值 ,.n ) RETURNS 返回值类型返回值类型 WITH , .n AS BEGIN function_body RETURN 表达式表达式END20使用标量用户定义函数使用标量用户定义函数标量函数返回标量函数返回 RETURNS 子句中定义的数据类型的单子句中定义的数据类型的单个数据值个数据值n在在 BEGIN END 块之间定义了函数体，包含返块之间定义了函数体，包含返回值的一系列回值的一系列 Transact-SQL 语句语句n返回值可以是除了返回值可以是除了 text、ntext、image、cursor 和和timestamp 之外的任

17、何数据类型之外的任何数据类型额外的限制额外的限制n标量函数只能返回标量值；标量函数只能返回标量值；n标量函数体内不能包括任何非确定性系统函数；标量函数体内不能包括任何非确定性系统函数；21标量用户定义函数示例标量用户定义函数示例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创建函数创建函数n调用函数调用函数22多语句表值函数多语句表值函数CREATE FUNCTION function_name (参数名参数名 参数类型参数类型 =默认值默认值 ,.n ) RETURNS return_variable TABLE WITH , .n AS BEGIN function_body RETURNEND23使用多语句表值函数使用

19、多语句表值函数多语句表值函数多语句表值函数nBEGIN 和和 END 分隔了函数体分隔了函数体nRETURNS 子句指定子句指定 table 作为返回的数作为返回的数据类型据类型nRETURNS 子句定义了返回表的名字和格子句定义了返回表的名字和格式。返回变量名的使用域限定于函数局部式。返回变量名的使用域限定于函数局部24使用多语句表值函数使用多语句表值函数函数体内只允许如下语句函数体内只允许如下语句n赋值语句赋值语句n流控制语句流控制语句n用于定义函数局部数据变量和游标的用于定义函数局部数据变量和游标的 DECLARE 语语句句nSELECT 语句，该语句包含带有表达式的选择列表，语句，该语

20、句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量其中的表达式将值赋予函数的局部变量n游标操作，该操作引用在函数中声明、打开、关闭游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。仅允许使用和释放的局部游标。仅允许使用 FETCH 语句通过语句通过 INTO 子句给局部变量赋值，不允许使用子句给局部变量赋值，不允许使用 FETCH 语语句将数据返回到客户端句将数据返回到客户端n修改函数的局部修改函数的局部 table 变量的变量的 INSERT、UPDATE 和和 DELETE 语句语句25使用多语句表值函数使用多语句表值函数n函数体内不允许出现返回非函数返回值的语函数

21、体内不允许出现返回非函数返回值的语句以及产生副作用的语句句以及产生副作用的语句副作用：指的是对函数外某些永久性状态的副作用：指的是对函数外某些永久性状态的改变改变n下列语句是不允许的：对数据库表的更新、下列语句是不允许的：对数据库表的更新、全局游标语句、创建对象的语句、事务控制全局游标语句、创建对象的语句、事务控制语句语句26多语句表值函数示例多语句表值函数示例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内嵌表值函数内嵌表值函数CREATE FUNCTION function_name (参数名参数名 参数类型参数类型 =默认值默认值 ,.n ) RETURNS TABLE WITH , .n AS RETURN ( select-stmt ) 28使用内嵌表值函数使用内嵌表值函数内嵌表值函数返回表，可在内嵌表值函数返回表，可在 FROM 子句中被引用，子句中被引用，就像视图一样就像