数据库-第14章--实现用户定义函数课件

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,Copyright2008,1,第14章 实现用户定义函数,用户定义函数,用户定义函数的定义与使用,架构绑定函数的创建,推荐操作,第14章 实现用户定义函数用户定义函数,用户定义函数,SQL Server 2000 允许用户设计自己的函数，以补充和扩展系统提供（内置）函数的功能,用户定义函数采用零或多个,输入参数,并返回标量值或表,SQL Server 2000 支持三种用户定义函数：,标量函数、多语句表值函数、内嵌表值函数,标量函数,标量函数返回一个标量（单值）结果,返回值可为：Timestamp、Text、Ntext、Image、Table和Cursor之外的所有数据类型，但不能为用户自定义数据类型。,16.1,用户定义函数,用户定义函数SQL Server 2000 允许用户设计自己,用户定义函数（续）,多语句表值函数,返回一个由一条或多条 Transact-SQL 语句建立的表，类似于存储过程,与存储过程不同的是，多语句表值函数可以在 SELECT 语句的 FROM 子句中被引用，仿佛视图一样,内嵌表值函数,返回一个表，该表是由一个位于RETURN子句中的SELECT命令段从数据库中筛选出来的，类似于视图,相对于视图，内嵌表值函数可使用参数，提供了更强的适应性，扩展了索引视图的功能,16.1,用户定义函数,用户定义函数（续）多语句表值函数16.1 用户定义函数,第14章 实现用户定义函数,用户定义函数,用户定义函数的定义与使用,架构绑定函数的创建,推荐操作,第14章 实现用户定义函数用户定义函数,创建标量用户定义函数,标量函数返回,RETURNS,子句中定义的数据类型的单个数据值,在 BEGIN END 块之间定义了函数体，包含返回值的一系列 Transact-SQL 语句,返回值可以是除了 text、ntext、image、cursor 或 timestamp 之外的任何数据类型,额外的限制,标量函数内的 SQL 语句不能包括任何非确定性系统函数,16.2.1,使用标量用户定义函数,创建标量用户定义函数标量函数返回 RETURNS 子句中定义,标量用户函数定义,CREATE FUNCTION,own_name.function_name,(参数名 AS 数据类型=默认值,.n),RETURNS,返回数据类型,WITH,AS,BEGIN,函数体,RETURN 返回表达式,END,SELECT,own_name,.function_name(参数值,.n),16.2.2,标量用户定义函数示例,必须指出所有者名称,创建函数,调用函数,标量用户函数定义CREATE FUNCTION own_n,标量用户定义函数示例,USE pubs,GO,CREATE FUNCTION,MyFun (type char(12),RETURNS,money,ASBEGIN,DECLARE var money,SELECT,var=,sum(price*ytd_sales)FROM titles,WHERE type=type,RETURN var,END,SELECT,dbo.,MyFun(business),16.2.2,标量用户定义函数示例,创建标量函数MyScarlarFun，计算用户指定类图书的当年销售额。,调用函数,标量用户定义函数示例USE pubsSELECT dbo.M,标量用户定义函数示例（续）,USE pubs,GO,CREATE FUNCTION,MyFun (type char(12)=,business,)RETURNS,money,ASBEGIN,DECLARE var money,SELECT,var=,sum(price*ytd_sales)FROM titles,WHERE type=type,RETURN var,END,SELECT dbo.MyFun(,DEFAULT,),16.2.2,标量用户定义函数示例,不能省略,参数带默认值,创建标量函数MyScarlarFun，计算用户指定类图书的当年销售额（带默认值）,。,调用函数,标量用户定义函数示例（续）USE pubsSELECT db,使用多语句表值函数,多语句表值函数,BEGIN.END 限定了函数体,RETURNS 子句指定 table 作为返回的数据类型,RETURNS 子句定义了返回表的名字和格式。返回变量名的使用域限定于函数局部,16.2.3,使用多语句表值函数,使用多语句表值函数多语句表值函数16.2.3 使用多语句表值,多语句表值函数定义,CREATE FUNCTION,own_name.function_name,(参数名 AS 数据类型=默认值,.n),RETURNS,返回变量,TABLE,WITH,AS,BEGIN,函数体,RETURN,END,SELECT *from,own_name,.function_name(参数值,.n),where ,16.2.3,使用多语句表值函数,可以省略,创建函数,调用函数,多语句表值函数定义CREATE FUNCTION own_,多语句表值函数示例,创建函数，根据要求返回ID及Lastname或Lastname、firstname组合,CREATE FUNCTION,fn_Employees,(length nvarchar(10),RETURNS,fn_Employees,table,(EmployeeID int PRIMARY KEY NOT NULL,EmployeeName nvarchar(20)NOT NULL),AS,BEGIN,IF length=ShortName,INSERT,fn_Employees,SELECT,EmployeeID,LastName FROM Employees,ELSE IF length=LongName,INSERT,fn_Employees,SELECT,EmployeeID,(FirstName+LastName),FROM Employees,RETURN,END,SELECT*FROM dbo.fn_Employees(ShortName),16.2.3,多语句表值函数示例,SELECT*FROM dbo.fn_Employees(LongName),WHERE employeeid 5,调用函数,表定义,多语句表值函数示例创建函数，根据要求返回ID及Lastnam,使用多语句表值函数（续）,函数体内只允许如下语句,赋值语句,流控制语句,用于定义函数局部数据变量和游标的 DECLARE 语句,SELECT 语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量,游标操作，如声明、打开、关闭和释放局部游标。仅允许使用 FETCH 语句通过 INTO 子句给局部变量赋值，不允许使用 FETCH 语句将数据返回到客户端,修改函数中定义的局部表变量的 INSERT、UPDATE 和 DELETE 语句,16.2.3,使用多语句表值函数,使用多语句表值函数（续）函数体内只允许如下语句16.2.3,使用内嵌表值函数,内嵌表值函数返回表，可在 FROM 子句中被引用，就像视图一样,关于使用内嵌表值函数的依据和指导方针,RETURNS 子句在括号中包含单个 SELECT 语句。SELECT 语句的结果集构成函数所返回的表,函数体不由 BEGIN 和 END 分隔,RETURNS 指定 table 作为返回的数据类型,不必定义返回变量的格式，因为它由 RETURN 子句中的 SELECT 语句的结果集的格式设置,16.2.4,使用内嵌表值函数,使用内嵌表值函数内嵌表值函数返回表，可在 FROM 子句中被,内嵌表值函数示例,USE Northwind,GO,CREATE FUNCTION fn_CustomerNamesInRegion,(RegionParameter nvarchar(30),RETURNS,table,AS,RETURN,(,SELECT CustomerID,CompanyName,FROM Northwind.dbo.Customers,WHERE Region=RegionParameter,),SELECT*FROM fn_CustomerNamesInRegion(WA),16.2.4,内嵌表值函数示例,创建函数，根据地区查看其所在的客户姓名。,使用参数调用函数,内嵌表值函数示例USE NorthwindSELECT*,更改和删除用户定义函数,更改函数,语法：ALTER FUNCTION,使用新的函数定义代替原来的函数定义,保留所有已分配的权限,删除函数,语法：DROP FUNCTION,删除一个用户定义函数,16.2.5,更改和删除用户定义函数,更改和删除用户定义函数更改函数16.2.5 更改和删除用户定,第14章 实现用户定义函数,用户定义函数,用户定义函数的定义与使用,架构绑定函数的创建,推荐操作,第14章 实现用户定义函数用户定义函数,创建架构绑定函数,可以使用架构绑定将函数绑定到其引用的数据库对象,若函数创建时使用 SCHEMABINDING 选项，则此函数引用的数据库对象不能被更改（使用 ALTER 语句）或删除（使用 DROP 语句）,必须满足以下条件才能在 CREATE FUNCTION 中指定 SCHEMABINDING 选项,该函数所引用的所有视图和用户定义函数必须是绑定到架构的,函数引用的对象必须以两部分名称的格式引用，即“拥有者名.对象名”,该函数所引用的所有对象必须与函数位于同一数据库中,执行 CREATE FUNCTION 语句的用户必须具有对该函数中引用的所有对象的 REFERENCE 权限,16.3.1,创建架构绑定函数,创建架构绑定函数可以使用架构绑定将函数绑定到其引用的数据库对,创建架构绑定函数（续）,创建架构绑定的函数,16.3.1,创建架构绑定函数,CREATE FUNCTION fn_Employees(employeeid int),RETURNS varchar(100),with SCHEMABINDING,AS,BEGIN,declare emLastName varchar(50),emFirstName varchar(50),select emLastName=lastName,emFirstName=firstname,from,dbo.viwEmployee,where employeeID=employeeid,RETURN (emLastName+emfirstName),END,创建架构绑定的视图,CREATE VIEW dbo.ViwEmployee,with schemabinding,AS,SELECT employeeid,lastname,firstname,city,from,dbo.,employees,必须先构建绑定视图,创建架构绑定函数（续）创建架构绑定的函数16.3.1 创建架,为用户定义函数设置权限,用户定义函数的权限要求和其他数据库对象的类似,要创建、更改或删除用户定义函数，必须具有,CREATE FUNCTION,权限,若函数拥有者之外的用户要在 Transact-SQL 语句中使用函数，则必须先被授予,EXECUTE,权限,若函数是架构绑定的，则创建者必须具有函数所引用的表、视图和函数上的,REFERENCE,权限,若在 CREATE TABLE 或 ALTER TABLE 语句的 CHECK 约束、DEFAULT 子句或计算列中引用了用户定义函数，则表的拥有者必须具有那个函数的 REFERENCE 权限,16.3.2,为用户定义函数设置权限,为用户定义函数设置权限用户定义函数的权限要求和其他数据库对象,第14章 实现用户定义函数,用户定义函数,用户定义函数的定义,架构绑定函数的创建,推荐操作,第14章 实现用户定