数据库第14章实现用户定义函数.ppt

第14章 实现用户定义函数,用户定义函数 用户定义函数的定义与使用 架构绑定函数的创建 推荐操作,用户定义函数,SQL Server 2000 允许用户设计自己的函数，以补充和扩展系统提供（内置）函数的功能 用户定义函数采用零或多个输入参数并返回标量值或表 SQL Server 2000 支持三种用户定义函数： 标量函数、多语句表值函数、内嵌表值函数 标量函数 标量函数返回一个标量（单值）结果 返回值可为：Timestamp、Text、Ntext、Image、Table和Cursor之外的所有数据类型，但不能为用户自定义数据类型。,16.1 用户定义函数,用户定义函数（续）,多语句表值函数 返回一个由一条或多条 Transact-SQL 语句建立的表，类似于存储过程 与存储过程不同的是，多语句表值函数可以在 SELECT 语句的 FROM 子句中被引用，仿佛视图一样 内嵌表值函数 返回一个表，该表是由一个位于RETURN子句中的SELECT命令段从数据库中筛选出来的，类似于视图 相对于视图，内嵌表值函数可使用参数，提供了更强的适应性，扩展了索引视图的功能,16.1 用户定义函数,第14章 实现用户定义函数,用户定义函数 用户定义函数的定义与使用 架构绑定函数的创建 推荐操作,创建标量用户定义函数,标量函数返回 RETURNS 子句中定义的数据类型的单个数据值 在 BEGIN END 块之间定义了函数体，包含返回值的一系列 Transact-SQL 语句 返回值可以是除了 text、ntext、image、cursor 或 timestamp 之外的任何数据类型 额外的限制 标量函数内的 SQL 语句不能包括任何非确定性系统函数,16.2.1 使用标量用户定义函数,标量用户函数定义,CREATE FUNCTION own_name. function_name ( 参数名 AS 数据类型 = 默认值 ,.n ) RETURNS 返回数据类型 WITH AS BEGIN 函数体 RETURN 返回表达式 END,SELECT own_name.function_name (参数值,.n),16.2.2 标量用户定义函数示例,必须指出所有者名称,创建函数,调用函数,标量用户定义函数示例,USE pubs GO CREATE FUNCTION MyFun (type char(12) RETURNS money AS BEGIN 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 pubs GO CREATE FUNCTION MyFun (type char(12) = business) RETURNS money AS BEGIN 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，计算用户指定类图书的当年销售额（带默认值） 。,调用函数,使用多语句表值函数,多语句表值函数 BEGIN . END 限定了函数体 RETURNS 子句指定 table 作为返回的数据类型 RETURNS 子句定义了返回表的名字和格式。返回变量名的使用域限定于函数局部,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 使用多语句表值函数,可以省略,创建函数,调用函数,多语句表值函数示例,创建函数，根据要求返回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,调用函数,表定义,使用多语句表值函数（续）,函数体内只允许如下语句 赋值语句 流控制语句 用于定义函数局部数据变量和游标的 DECLARE 语句 SELECT 语句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量 游标操作，如声明、打开、关闭和释放局部游标。仅允许使用 FETCH 语句通过 INTO 子句给局部变量赋值，不允许使用 FETCH 语句将数据返回到客户端 修改函数中定义的局部表变量的 INSERT、UPDATE 和 DELETE 语句,16.2.3 使用多语句表值函数,使用内嵌表值函数,内嵌表值函数返回表，可在 FROM 子句中被引用，就像视图一样 关于使用内嵌表值函数的依据和指导方针 RETURNS 子句在括号中包含单个 SELECT 语句。SELECT 语句的结果集构成函数所返回的表 函数体不由 BEGIN 和 END 分隔 RETURNS 指定 table 作为返回的数据类型 不必定义返回变量的格式，因为它由 RETURN 子句中的 SELECT 语句的结果集的格式设置,16.2.4 使用内嵌表值函数,内嵌表值函数示例,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 内嵌表值函数示例,创建函数，根据地区查看其所在的客户姓名。,使用参数调用函数,更改和删除用户定义函数,更改函数 语法：ALTER FUNCTION 使用新的函数定义代替原来的函数定义 保留所有已分配的权限 删除函数 语法：DROP FUNCTION 删除一个用户定义函数,16.2.5 更改和删除用户定义函数,第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,必须先构建绑定视图,为用户定义函数设置权限,用户定义函数的权限要求和其他数据库对象的类似 要创建、更改或删除用户定义函数，必须具有 CREATE FUNCTION 权限 若函数拥有者之外的用户要在 Transact-SQL 语句中使用函数，则必须先被授予 EXECUTE 权限 若函数是架构绑定的，则创建者必须具有函数所引用的表、视图和函数上的 REFERENCE 权限 若在 CREATE TABLE 或 ALTER TABLE 语句的 CHECK 约束、DEFAULT 子句或计算列中引用了用户定义函数，则表的拥有者必须具有那个函数的 REFERENCE 权限,16.3.2 为用户定义函数设置权限,第14章 实现用户定义函数,用户定义函数 用户定义函数的定义 架构绑定函数的创建 推荐操作,推荐操作,16.4 推荐操作,随堂练习1,表AppUsers中存放了用户的姓名与密码，要求密码经 过加密后，以二进制的方式存放，请提供加密与解密 函数来实现该功能。,create function dbo.fn_EncryptPassword(Password varchar(20) returns binary(20) begin declare EPassword binary(20) set EPassword = cast(Password as binary(20) return EPassword end,create function dbo.fn_DecryptPassword(EPassword binary(20) returns varchar(20) begin declare Password varchar(20) set Password = cast(EPassword as varchar(20) return Password end,随堂练习1（续）,在向表AppUsers插入数据时，使用加密函数对密码进 行加密后再插入，在从表中读取数据时，用解密函数 对密码进行解密,insert into AppUsers (UserName, Password) values(Tom,dbo.fn_EncryptPassword(00001),select username,dbo.fn_DecryptPassword(password) from AppUsers,随堂练习2,在设计某个销售公司的数据库系统时，客户要求设计 一张季度销售情况的视图。这张视图连接多张表同时 使用聚集函数计算所需的统计信息。客户要求提供一 个带参数的查询方法，输出的结果要作为其他的 SELECT语句的列值使用。请问应当如何设计该视图？,A、使用ALTER VIEW语句在视图定义中添加参数 B、创建带参数的存储过程，将结果集作为一个行集合返回 C、创建一个接收参数输入的标量用户自定义函数 D、创建一个接收参数输入的内联用户自定义函数,在设计某个公司的数据库系统时，要求设计一张数据 表。这张数据表使用聚集函数计算所需的统计信息并 使用索引提高查询速度。要求将该数据表查询输出的 结果要放在其他SELECT语句的FRO