数据库程序设计SQLServer2000数据库程序设计.PPT

1、数据库程序设计 SQL Server 2000 数据库程序设计,第1章 SQL Server 概述第2章 创建和管理数据库第3章 创建数据类型和表第4章 实现数据完整性第5章 Transact-SQL介绍第6章 使用 Transact-SQL查询工具第7章 检索数据第8章 数据分组与汇总第9章 多表联接第10章 子查询第11章 修改数据,第12章 全文索引查询 第13章 规划索引第14章 创建和维护索引第15章 实现视图第16章 实现存储过程第17章 实现用户定义函数第18章 实现触发器第19章 多服务器编程第20章 优化查询性能第21章 分析查询第22章 管理事务和锁,第17章 实现用户定义

2、函数,用户定义函数用户定义函数的定义用户定义函数示例推荐操作,用户定义函数,SQL Server 2000 允许用户设计自己的函数，以补充和扩展系统提供（内置）函数的功能用户定义函数采用零或多个输入参数并返回标量值或表SQL Server 2000 支持三种用户定义函数：标量函数、多语句表值函数和内嵌表值函数标量函数标量函数返回一个标量（单值）结果可在与标量函数返回的数据类型相同的值所能使用的任何位置使用该标量函数，包括 SELECT 语句中列的列表和 WHERE 子句、表达式、表定义中的约束表达式，甚至作为表中列的数据类型,17.1 用户定义函数,用户定义函数（续）,多语句表值函数多语句表值

3、函数返回一个由一条或多条 Transact-SQL 语句建立的表，类似于存储过程与存储过程不同的是，多语句表值函数可以在 SELECT 语句的 FROM 子句中被引用，仿佛视图一样内嵌表值函数内嵌表值函数返回一个单条 SELECT 语句产生的结果的表，类似于视图相对于视图，内嵌表值函数可使用参数，提供了更强的适应性，扩展了索引视图的功能,17.1 用户定义函数,第17章 实现用户定义函数,用户定义函数用户定义函数的定义用户定义函数示例推荐操作,用户定义函数的定义,创建用户定义函数创建架构绑定函数为用户定义函数设置权限更改和删除用户定义函数,17.2 用户定义函数的定义,创建用户定义函数,创建用

4、户定义函数每个完全合法的用户定义函数名 （数据库名.拥有者名.函数名）必须惟一语句指定了输入参数及它们的数据类型、处理指令，以及返回的值及其数据类型在 Transact-SQL 语句中使用用户定义函数标量用户定义函数可以在任何它们返回值的数据类型可以用的地方使用表值用户定义函数只能在 SELECT 语句的 FROM 子句中使用若 SELECT 子句是在 DECLARE CURSOR 语句中，游标必须是 STATIC 或 READ_ONLY 的,17.2.1 创建用户定义函数,创建用户定义函数（续）,使用标量用户定义函数标量函数以和内置函数相同的形式调用：拥有者名.函数名(参数列表)不可省略拥有

5、者名，也不能用命名参数的形式（例如 参数名=值），且参数也不可以省略（但可以使用 DEFAULT 关键字指明使用默认值）SQL Server 提供少量内置用户定义函数。与普通内置函数不同，它们的名字以“ fn_”开头并且使用特定的调用语法：:函数名(参数列表)其中“:”代替了“拥有者名”的位置，表明函数为内置用户定义函数,17.2.1 创建用户定义函数,创建用户定义函数（续）,使用标量用户定义函数（续）也可以使用 EXECUTE 语句调用标量函数：EXECUTE 返回变量 = 函数名(参数列表)使用 EXECUTE 语句调用时，不必包括“拥有者名”，而且可以使用命名参数：EXECUTE 返回变

6、量 = 函数名 变量=值 ,变量 = 值当使用命名参数的时候，参数的次序不必按照在函数中声明的次序，但必须包括所有参数，不能省略任何参数使用表值用户定义函数表值用户定义函数必须使用和内置函数相同的调用格式：函数名 (参数列表)不需要指定“拥有者名”，但必须包括所有定义的参数,17.2.1 创建用户定义函数,创建用户定义函数（续）,函数的限制非确定性函数：每次使用相同的输入值集调用的时候，返回不同的结果值的函数用户定义函数体中不允许使用内置非确定性函数，包括,17.2.1 创建用户定义函数,创建架构绑定函数,可以使用架构绑定将函数绑定到其引用的数据库对象若函数创建时使用 SCHEMABINDIN

7、G 选项，则此函数引用的数据库对象不能被更改（使用 ALTER 语句）或删除（使用 DROP 语句）必须满足以下条件才能在 CREATE FUNCTION 中指定 SCHEMABINDING 选项该函数所引用的所有视图和用户定义函数必须是绑定到架构的函数引用的对象必须以两部分名称的格式引用，即“拥有者名.对象名”该函数所引用的所有对象必须与函数位于同一数据库中执行 CREATE FUNCTION 语句的用户必须具有对该函数中引用的所有对象的 REFERENCE 权限,17.2.2 创建架构绑定函数,为用户定义函数设置权限,用户定义函数的权限要求和其他数据库对象的类似要创建、更改或删除用户定义函

8、数，必须具有 CREATE FUNCTION 权限若函数拥有者之外的用户要在 Transact-SQL 语句中使用函数，则必须先被授予 EXECUTE 权限若函数是架构绑定的，则创建者必须具有函数所引用的表、视图和函数上的 REFERENCE 权限若在 CREATE TABLE 或 ALTER TABLE 语句的 CHECK 约束、DEFAULT 子句或计算列中引用了用户定义函数，则表的拥有者必须具有那个函数的 REFERENCE 权限,17.2.3 为用户定义函数设置权限,更改和删除用户定义函数,更改函数语法：ALTER FUNCTION 使用新的函数定义代替原来的函数定义保留所有已分配的权

9、限删除函数语法：DROP FUNCTION 删除一个用户定义函数,17.2.4 更改和删除用户定义函数,第17章 实现用户定义函数,用户定义函数用户定义函数的定义用户定义函数示例推荐操作,用户定义函数示例,使用标量用户定义函数标量用户定义函数示例使用多语句表值函数多语句表值函数示例使用内嵌表值函数内嵌表值函数示例,17.3 用户定义函数示例,使用标量用户定义函数,标量函数返回 RETURNS 子句中定义的数据类型的单个数据值在 BEGIN END 块之间定义了函数体，包含返回值的一系列 Transact-SQL 语句返回值可以是除了 text、ntext、image、cursor 或 time

10、stamp 之外的任何数据类型额外的限制标量函数内的 SQL 语句不能包括任何非确定性系统函数,17.3.1 使用标量用户定义函数,标量用户定义函数示例,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), datepart(dd, indate) + separator +

11、CONVERT(Nvarchar(20), datepart(yy, indate)END,SELECT dbo.fn_DateFormat(GETDATE(), :),创建函数调用函数,17.3.2 标量用户定义函数示例,使用多语句表值函数,多语句表值函数BEGIN 和 END 分隔了函数体RETURNS 子句指定 table 作为返回的数据类型RETURNS 子句定义了返回表的名字和格式。返回变量名的使用域限定于函数局部,17.3.3 使用多语句表值函数,使用多语句表值函数（续）,函数体内只允许如下语句赋值语句流控制语句用于定义函数局部数据变量和游标的 DECLARE 语句SELECT 语

12、句，该语句包含带有表达式的选择列表，其中的表达式将值赋予函数的局部变量游标操作，该操作引用在函数中声明、打开、关闭和释放的局部游标。仅允许使用 FETCH 语句通过 INTO 子句给局部变量赋值，不允许使用 FETCH 语句将数据返回到客户端修改函数的局部 table 变量的 INSERT、UPDATE 和 DELETE 语句,17.3.3 使用多语句表值函数,使用多语句表值函数（续）,副作用：指的是对函数外某些永久性状态的改变函数体内不允许出现返回非函数返回值的语句以及产生副作用的语句下列语句是不允许的：对数据库表的更新、全局游标语句、创建对象的语句、事务控制语句,17.3.3 使用多语句表

13、值函数,多语句表值函数示例,创建函数调用函数,USE NorthwindGOCREATE FUNCTION fn_Employees (length nvarchar(9)RETURNS fn_Employees table (EmployeeID int PRIMARY KEY NOT NULL, Employee Name nvarchar(61) NOT NULL)ASBEGIN IF length = ShortName INSERT fn_Employees SELECT EmployeeID, LastName FROM Employees ELSE IF length = Lon

14、gName INSERT fn_Employees SELECT EmployeeID, (FirstName + + LastName) FROM EmployeesRETURNEND,SELECT * FROM dbo.fn_Employees(LongName)或SELECT * FROM dbo.fn_Employees(ShortName),17.3.4 多语句表值函数示例,使用内嵌表值函数,内嵌表值函数返回表，可在 FROM 子句中被引用，就像视图一样关于使用内嵌表值函数的依据和指导方针RETURN 子句在括号中包含单个 SELECT 语句。SELECT 语句的结果集构成函数所返回

15、的表函数体不由 BEGIN 和 END 分隔RETURN 指定 table 作为返回的数据类型不必定义返回变量的格式，因为它由 RETURN 子句中的 SELECT 语句的结果集的格式设置,17.3.5 使用内嵌表值函数,内嵌表值函数示例,创建函数使用参数调用函数,USE NorthwindGOCREATE FUNCTION fn_CustomerNamesInRegion ( RegionParameter nvarchar(30) )RETURNS tableASRETURN ( SELECT CustomerID, CompanyName FROM Northwind.dbo.Custo

16、mers WHERE Region = RegionParameter ),SELECT * FROM fn_CustomerNamesInRegion(NWA),17.3.6 内嵌表值函数示例,第17章 实现用户定义函数,用户定义函数用户定义函数的定义用户定义函数示例推荐操作,推荐操作,17.4 推荐操作,目标创建标量用户定义函数创建多语句表值用户定义函数创建内嵌表值用户定义函数练习 1 创建标量用户定义函数练习 2 创建多语句表值用户定义函数练习 3 创建内嵌表值用户定义函数,实验 创建用户定义函数,回顾,学习完本章后，将能够：描述用户定义函数的三种类型创建和更改用户定义函数创建每种类型的

17、用户定义函数,随堂练习1,在某个使用SQL Server 2000创建数据库中删除一个不再使用的视图时，操作不成功。该视图上有一个clustered索引；视图使用了WITH SCHEMABINDING选项；在视图上定义了一个INSTEAD OF触发器。还有一个绑定架构的内联函数引用该视图，应当如何解决该问题？删除clustered索引除去视图上的WITH SCHEMABINDING选项除去内联函数上的WITH SCHEMABINDING选项禁用视图的INSTEAD OF触发器,随堂练习2,在某个数据库中使用数据表ProfitRate和ProductPrice来分别记录产品的利润率和单价。利润率

18、和单价经常发生波动。为了计算产品的销售利润需要将两个表中的数据进行计算，而销售利润常常用在SELECT语句中用于特定的查询，请问哪种方法可以使用户在查询时不用关心计算的细节问题？创建一个用户定义的函数用于计算某种产品的销售利润使用存储过程用于计算某种产品的销售利润使用一张数据表记录销售利润，在原来的表中添加触发器更新利润表中的数据在ProductPrice中添加一列专门用于计算销售利润,随堂练习3,在设计某个销售公司的数据库系统时，客户要求设计一张季度销售情况的视图。这张视图连接多张表同时使用聚集函数计算所需的统计信息。客户要求提供一个带参数的查询方法，输出的结果要作为其他的SELECT语句的

19、列表使用。请问应当如何设计该视图？使用ALTER VIEW语句在视图定义中添加参数创建带参数的存储过程，将结果集作为一个行集合返回创建一个接收参数输入的标量用户自定义函数创建一个接收参数输入的内联用户自定义函数,随堂练习4,在设计某个公司的数据库系统时，要求设计一张数据表。这张数据表使用聚集函数计算所需的统计信息并使用索引提高查询速度。要求将该数据表查询输出的结果要放在其他SELECT语句的FROM子句使用，下列做法哪个是正确的？ 创建一个接收参数输入标量用户自定义函数创建一个接收参数输入并将结果集作为一个行集合返回的标量用户自定义函数创建一个接收参数输入内联用户自定义函数创建一个接收参数输入并将结果集作为一个行集合返回的内联用户自定义函数,随堂练习5,在某个SQL Server 2000数据库系统中，由于业务需要，经常需要连接三张数据表查询访问数据库，请问应如何提高访问效率？创建带索引的视图创建存储过程访问数据库返回数据创建返回数据的标量用户自定义函数创建返回数据的内联用户自定义函数,