SQLServer2000特殊类型的存储过程、用户定义函数和触发器详解

### SQL Server 2000特殊类型的存储过程、用户定义函数和触发器详解 #### 1. 特殊类型的存储过程 在SQL Server 2000中，有几种特殊类型的存储过程，包括临时存储过程、全局临时存储过程和远程存储过程。 ##### 1.1 临时存储过程 临时存储过程与存储过程的关系，就如同临时表与表的关系。当你期望在有限时间内重用存储过程的执行计划时，可以使用临时存储过程。尽管标准的用户定义存储过程也能实现相同的功能，但临时存储过程是更好的解决方案，因为你无需担心维护问题（例如删除存储过程）。 临时存储过程驻留在`tempdb`中，并且名称必须以`#`为前缀。创建临时存储过程的方式与创建用户定义存储过程相同，唯一的区别是使用`#`作为名称前缀，这个前缀会告知服务器将该过程创建为临时存储过程。这种存储过程只能在创建它的会话中使用，当会话关闭时，它会自动被删除，因此也常被称为私有临时存储过程。 以下是一个创建临时存储过程的示例： ```sql Create Procedure #GetId @Make varchar(50), @Model varchar(50) as Select EquipmentId from Equipment where Make = @Make and Model = @Model ``` 需要注意的是，有时管理员会将`tempdb`中的用户定义存储过程称为临时存储过程，这是不正确的，因为两者存在重大差异。例如，`tempdb`中的用户定义存储过程可供所有授权用户访问，并且这些存储过程会一直保留在`tempdb`中，直到服务器关闭，此时`tempdb`的所有内容都会被清空。 ##### 1.2 全局临时存储过程 全局临时存储过程与临时存储过程的关系，就如同全局临时表与私有临时表的关系。它们也驻留在`tempdb`中，但使用`##`作为前缀。创建全局临时存储过程的方式与创建临时存储过程相同，唯一的区别是它们可以在所有会话中可见和使用，实际上，不需要权限，并且所有者甚至无法阻止其他用户访问它们。 当创建该过程的会话关闭时，新的会话将无法执行该存储过程。在所有正在运行的存储过程实例完成后，该过程会自动被删除。 以下是一个创建全局临时存储过程的示例： ```sql Create Procedure ##InsertEquipment @Make varchar(50), @Model varchar(50), @EqType varchar(50) as declare @EqTypeId smallint select @EqTypeId = EqTypeId -- This is OK in perfect world, from EqType -- but it is based on Where EqType = @EqType -- unreasonable assumption. Insert Equipment (Make, Model, EqTypeId) Values (@Make, @Model, @EqTypeId) ``` ##### 1.3 远程存储过程 远程存储过程实际上是驻留在远程服务器上的用户定义存储过程。这种类型的存储过程的唯一挑战是，本地服务器必须设置为允许远程使用存储过程。如需更多信息，请使用以下字符串在SQL Server联机丛书中搜索：如何设置远程服务器以允许使用远程存储过程。 需要注意的是，微软实际上将这种机制视为SQL Server旧版本的遗留功能，建议使用异构查询来执行其他服务器上的存储过程或访问表。 #### 2. 用户定义函数 在SQL Server 2000中，设计Transact - SQL函数是一项新功能。在早期版本中，用户只能使用内置函数。 ##### 2.1 用户定义函数的设计 用户定义函数可以使用`Create Function`语句创建，使用`Alter Function`语句修改，使用`Drop Function`语句删除。可以使用`sp_help`和`sp_stored_procedures`获取函数的信息，使用`sp_helptext`获取其源代码。从企业管理器中，管理员可以使用与创建和管理存储过程相同的工具。 函数可以有零个、一个或多个参数，但必须返回单个返回值。返回值可以是标量，也可以是表。输入参数可以是除`timestamp`、`cursor`和`table`之外的任何数据类型。返回值可以是除`timestamp`、`cursor`、`text`、`ntext`和`image`之外的任何数据类型。 `Create Function`语句的语法如下： ```sql Create Function [owner_name.]function_name ( [ {@parameter_name scalar_data_type [= default]} [,...n] ] ) returns scalar_data_type |Table |return_variable Table({column_def|table_constraint}[,…n]) [With {Encryption|Schemabinding}[,…n] ] [As] {Begin function_body End} | Return [(] {value|select-stmt} [)] ``` 以下是一个返回指定日期所在季度的函数示例： ```sql Create Function fnQuarterString -- returns quarter in form of '3Q2000'. ( @dtmDate datetime ) Returns char(6) -- quarter like 3Q2000 As Begin Return (DateName(q, @dtmDate) + 'Q' + DateName(yyyy, @dtmDate)) End ``` 函数可以设计为包含流控制和其他Transact - SQL语句，甚至可以包含多个`Return`语句。在不同条件下，这些`Return`语句可以作为函数的退出点，但函数体的最后一条语句必须是无条件的`Return`语句。 以下是一个返回指定日期三天工作日后的日期的函数示例： ```sql Create Function fnThreeBusDays -- returns date 3 business day after the specified date (@dtmDate datetime) Returns datetime As Begin Declare @inyDayOfWeek tinyint Set @inyDayOfWeek = DatePart(dw, @dtmDate) Set @dtmDate = Convert(datetime, Convert(varchar, @dtmDate, 101)) If @inyDayOfWeek = 1 -- Sunday Return DateAdd(d, 3, @dtmDate ) If @inyDayOfWeek = 7 -- Saturday Return DateAdd(d, 4, @dtmDate ) If @inyDayOfWeek = 6 -- Friday Return DateAdd(d, 5, @dtmDate ) If @inyDayOfWeek = 5 -- Thursday Return DateAdd(d, 5, @dtmDate ) If @inyDayOfWeek = 4 -- Wednesday Return DateAdd(d, 5, @dtmDate ) Return DateAdd(d, 3, @dtmDate ) End ``` ##### 2.2 用户定义函数的副作用 用户定义函数有一个严重的限制，即不能有副作用。函数的副作用是指对函数外部作用域的资源（如非临时表）进行的任何永久性更改。基本上，这意味着函数应该返回一个值，而不更改数据库中的任何内容。 SQL Server通过限制函数内部可以使用的Transact - SQL语句来强制用户不创建副作用，允许使用的语句包括： - 引用函数本地对象（如局部变量和返回值）的赋值语句（`Set`或`Select`） - 流控制语句 - 更新局部表变量的`Update`、`Insert`和`Delete`语句 - 定义局部变量或游标声明语句 - 声明、打开、关闭、提取和释放局部游标的语句（允许的唯一`Fetch`语句是将信息从游标检索到局部变量的语句） ##### 2.3 内置函数的使用限制 用户定义函数不能调用每次调用时返回不同数据的内置函数，例如： - `@@CONNECTIONS` - `@@TIMETICKS` - `@@CPU_BUSY` - `@@TOTAL_ERRORS` - `@@IDLE` - `@@TOTAL_READ` - `@@IO_BUSY` - `@@TOTAL_WRITE` - `@@MAX_CONNECTIONS` - `GETDATE` - `@@PACK_RECEIVED` - `NEWID` - `@@PACK_SENT` - `RAND` - `@@PACKET_ERRORS` - `TEXTPTR` 如果尝试在用户定义函数中使用`GetDate()`，SQL Server将报告错误。 ##### 2.4 加密和架构绑定 与存储过程一样，函数可以进行加密，这样就没有人能看到其源代码。开发人员只需在创建或修改函数时使用`With Encryption`选项即可。 新的`With Schemabinding`选项允许开发人员将用户定义函数与它引用的数据库对象（如表、视图和其他用户定义函数）进行架构绑定。一旦函数进行了架构绑定，就无法对底层对象进行架构更改，所有删除对象的尝试和所有更改底层对象架构的尝试都将失败。 函数进行架构绑定必须满足以下所有条件： - 函数引用的所有用户定义函数和视图必须已经进行了架构绑定。 - 函数引用的所有数据库对象必须与函数位于同一数据库中，对数据库对象的引用不能有服务器或数据库限定符，只允许使用对象所有者限定符和对象标识符。 - 执行`Create`（或`Alter`）`Function`语句的用户对所有引用的数据库对象具有`References`权限。 ##### 2.5 表值用户定义函数 由于SQL Server 2000具有表数据类型，因此可以设计返回表的用户定义函数。表值用户定义函数的主要用途与视图类似，但这些函数更加灵活，并提供了额外的功能。 可以在任何可以使用表（或视图）的地方使用表值用户定义函数。在这方面，它们实现了视图的功能，但函数可以有参数，因此是动态的。视图也仅限于单个`Select`语句，而函数可以包含一个或多个Transact - SQL语句，从而实现更复杂的功能，因此这类函数通常被称为多语句表值用户定义函数。存储过程也可以返回结果集，但这种结果集的使用有一定限制，例如，只有函数返回的结果集可以在`Select`语句的`From`子句中引用。 以下是一个使用表值用户定义函数的示例： ```sql select DD.TermId, DD.DueDate, Inventory.Lease from dbo.fnDueDays('1/1/2000','1/1/2004','monthly') DD, Inventory where InventoryId = 8 and DD.DueDate > GetDate() ``` 结果可能如下： | TermId | DueDate