用户自定义函数与事务.ppt

第10章 用户自定义函数与事务 函数是由一个或多个 Transact-SQL 语句组成的 子程序。SQL Server 2000 支持三种用户定义函数： 标量函数、内嵌表值函数、多语句表值函数。本章介 绍用户定义函数的创建、修改及删除。 事务是作为单个逻辑工作单元执行的一系列操作 ，这些操作要么全部执行，要么都不执行。SQL Server 2000 使用锁确保事务完整性和数据库一致性 ，锁可以防止用户读取正在由其他用户更改的数据， 并可以防止多个用户同时更改相同数据。本章介绍如 何定义事务进行数据处理并详细说明了锁定机制中锁 的粒度、不同类型的锁的特点。 实用教程 10.1用户自定义函数简介 SQL Server 不但提供了系统内置函数，而且还允许 用户根据实际需要创建用户自定义函数。用户自定义函 数是由一条或多条TransactSQL语句组成的子程序， 保存在数据库内。它可以具有多个输入参数，并返回一 个标量值(单个数据值)或一个表。 SQL Server支持三种类型的用户自定义函数：标量 (Scalar)函数、内嵌表值(InLine TableValued)函数 、多语句表值(MultiStatement TableValued)函数。 实用教程 10.2创建用户自定义函数 创建用户自定义函数有两种方法：一种是 使用企业管理器，另一种是使用查询分析器。 实用教程 10.2创建用户自定义函数 10.2.1使用企业管理器创建用户自定义函数 使用企业管理器创建用户自定义函数的步骤如下： (1)展开服务器，展开数据库。 (2)右击“用户定义的函数”，在弹出的菜单中选择“新 建用户定义的函数”命令，屏幕显示如图10.1所示的用 户自定义函数属性窗口。 (3)在“用户自定义函数属性窗口”的“文本”栏内输 入函数内容。 (4)单击“确定”按钮，完成用户自定义函数的创建。 实用教程 10.2创建用户自定义函数 10.2.2使用TransactSQL语言创建用户自定义函 数 在查询分析器中，创建用户自定义函数是使 用CREATE FUNCTION语句来完成的。 1标量函数 标量函数类似于系统内置函数。函数的输 入参数可以是所有标量数据类型，输出参数的 类型可以是除了Text、NText、Image、Cursor 、Timestamp以外的任何数据类型，函数主体 在BEGINEND块中定义。 实用教程 10.2创建用户自定义函数 2内嵌表值函数 内嵌表值函数没有函数体，其返回的表是单个 SELECT语句的结果集。由于视图不支持在WHERE子句 的搜索条件中使用参数，内嵌表值函数可弥补视图的这 一不足之处，即内嵌表值函数可用于实现参数化的视图 功能。 3多语句表值函数 多语句表值函数的函数体在BEGINEND块中定义。 函数体可以包含多条TransactSQL语句，这些语句可 生成行并将行插入将返回的表中。由于视图只能包含单 条SELECT语句，而多语句表值函数可包含多条Transact SQL语句。因此，多语句表值函数的功能比视图更强 大。此外，多语句表值函数还可替换返回单个结果集的 存储过程。 实用教程 10.3修改、删除用户自定义函数 1修改用户自定义函数 使用企业管理器修改用户自定义函数的步骤如下： (1)展开服务器，展开数据库。 (2)单击“用户定义的函数”，在详细列表框中双击需修改 用户自定义函数名称，屏幕显示如如图10.1所示的用户 自定义函数属性窗口。 (3)在“用户自定义函数属性窗口”的“文本”栏内修 改函数内容。 (4) 单击“应用”按钮，再单击“确定”按钮，完成用户自定 义函数的修改。 在查询分析器中，修改用户自定义函数使用ALTER FUNCTION语句。 实用教程 10.3修改、删除用户自定义函数 2删除用户自定义函数 使用企业管理器删除用户自定义函数的步骤如下 ： (1)展开服务器，展开数据库。 (2)单击“用户定义的函数”，在详细列表框中右击 需修改用户自定义函数名称，在弹出的菜单中 选择“删除”命令。 (3)单击“全部移去”按钮，删除用户自定义函数。 在查询分析器中，删除用户自定义函数使用 DROP FUNCTION语句。 实用教程 10.4事务处理 10.4.1事务简介 事务是一个逻辑工作单元，其中包括了一系列的操作，这些操 作要么全部执行，要么都不执行。典型的事务实例是两个银行之间 的转账，账号A转出1000元至账号B，这笔转账业务可分解为：(1) 账号A减去1000元；(2) 账号B增加1000元。当然，要求这两项操 作要么同时成功(转账成功)，要么同时失败(转账失败)。只有其中 一项操作成功则是不可接受的事情。如果确实发生了只有其中一项 操作成功的话，那么应该撤消所做的操作(回滚事务)，就好象什么 操作都没有发生一样。 事务具有4个属性：原子性、一致性、隔离性、持久性。简称为 ACID属性。 原子性(Atomicity)：事务必须作为工作的最小单位，即原子单位。 其所进行的操作要么全部执行，要么都不执行。 一致性(Consistency)：每个事务必须保证数据的一致性。事务完 成后，所有数据必须保持其合法性，即所有数据必须遵守数据库的 约束和规则。 实用教程 10.4事务处理 隔离性(Isolation)：一个事务所做的修改必须与其他事务 所做的修改隔离。一个事务所使用的数据必须是另一个 并发事务完成前或完成后的数据，而不能是另一个事务 执行过程的中间结果。也就是说，两个事务是相互隔离 的，其中间状态的数据是不可见的。 持久性(Durability)：事务完成后对数据库的修改将永久 保持。 在SQL Server 2000中，事务的模式可分为：显式事务 、隐式事务、自动事务。 显式事务：由用户自己使用TransactSQL语言的事 务语句定义的事务，具有明显的开始和结束标志。 实用教程 10.4事务处理 隐式事务：SQL Server为用户而做的事务。例如：在执 行一条INSERT语句时，SQL Server将把它包装到事务 中，如果执行此INSERT语句失败，SQL Server将回滚 或取消这个事务。用户可以通过执行以下命令使SQL Server进入或退出隐式事务状态： SET IMPLICIT TRANSACTI ON：使系统进入隐式事 务模式。 SET IMPLICIT TRANSACTI OFF：使系统退出隐式事 务模式。 自动事务：SQL Server的默认事务管理模式。在自动提 交模式下，每个TransactSQL语句在成功执行完成后 ，都被自动提交；如果遇到错误，则自动回滚该语句。 实用教程 10.4事务处理 10.4.2事务处理 TransactSQL语言的事务语句包括： 1.BEGIN TRANSACTION 语句 格式：BEGIN TRANSACTION 事务名 功能：定义一个事务，标志一个显式事务的起始点。 2.COMMIT TRANSCATION 语句 格式：COMMIT TRANSCATION transaction_name 功能：提交一个事务，标志一个成功的显式事务或隐式事务的结束 。 说明：当在嵌套事务中使用COMMIT TRANSCATION语句时，内部 事务的提交并不释放资源，也没有执行永久修改。只有在提交了外 部事务时，数据修改才具有永久性，资源才会释放。 实用教程 10.4事务处理 3.ROLLBACK TRANSCATION 语句 格式：ROLLBACK TRANSCATION 事务名 其中“事务名”是由前面BEGIN TRANSACTION语句指派 的事务名称。 功能：回滚一个事务，将显式事务或隐式事务回滚到事务 的起点或事务内的某个保存点。 说明： (1) 执行了COMMIT TRANSCATION语句后不能再回滚事 务。 (2) 事务在执行过程中出现的任何错误，SQL Server实例 将回滚事务。 (3) 系统出现死锁时会自动回滚事务。 实用教程 10.4事务处理 (4) 由于其他原因(客户端网络连接中断、应用程序 中止等)引起客户端和SQL Server实例之间通信 的中断，SQL Server实例将回滚事务。 (5)在触发器中发出ROLLBACK TRANSCATION命 令，将回滚对当前事务中所做的数据修改，包括 触发器所做的修改。 (6)对于嵌套事务，ROLLBACK TRANSCATION语 句将所有内层事务回滚到最远的BEGIN TRANSACTION语句，“事务名”也只能是来自最 远的BEGIN TRANSACTION语句的名称。 实用教程 10.4事务处理 4.SAVE TRANSACTION语句 格式：SAVE TRANSACTION 保存点名 功能：建立一个保存点，使用户能将事务回滚到 该保存点的状态，而不是简单回滚整个事务。 在编写事务处理程序中，使用到的全局变量有： error：最近一次执行的语句引发的错误号， 未出错时其值为零。 rowcount：受影响的行数。 实用教程 10.5锁 10.5.1锁的概念 锁作为一种安全机制，用于控制多个用户的并发操 作，防止用户读取正在由其他用户更改的数据或者多个 用户同时修改同一数据，确保事务的完整性和数据的一 致性。 锁定机制的主要属性是锁的粒度和锁的类型。 SQL Server提供了多种粒度的锁，允许一个事务 锁定不同类型的资源。锁的粒度越小，系统允许的并发 用户数目就越多，数据库的利用率就越高，管理锁定所 需要的系统资源越多。反之，则相反。为了减少锁的成 本，应该根据事务所要执行的任务，合理选择锁的粒度 ，将资源锁定在适合任务的级别范围内。 实用教程 10.5锁 按照粒度增加的顺序，不同粒度的锁可以锁定的资 源如表10.1所示。 表10.1不同粒度的锁可以锁定的资源 资源说 明 行行锁定，锁定表中的一行数据。 键键值锁 定，锁定具有索引的行数据。 页页面锁定，锁定8KB的数据页或索引页 。 区域 区域锁定，锁定8个连续的数据页面或索引页面 。 数据表 表锁定，锁定整个数据表，包括所有数据和索引在内。 数据库 数据库锁定，锁定整个数据库。 实用教程 10.5锁 SQL Server使用不同的锁模式锁定资源，这些锁 模式确定了并发事务访问资源的方式。常用的锁模式 有： (1)共享(Shared)锁：用于只读取数据的操作。 (2)更新(Update)锁：用于可更新的资源中，防止多 个会话在读取、锁定及随后可能进行的资源更新时发 生常见形式的死锁。 (3)独占(Exclusive)锁：用于数据修改操作。 实用教程 10.5锁 10.5.2死锁及其排除 锁机制的引入能解决并发用户的数据一致 性问题，但因此可能会引起进程间的死锁问题 。引起死锁的主要原因是，两个进程已各自锁 隹一个页，但又要求访问被对方锁隹的页。更 一般的情况是，一个事务独占了其他事务正在 申请的资源，且若干个这样的事务形成一个等 待圈。例如用户A和用户B按照下表的时间顺序 执行操作，在T3时间点将会产生死锁。 实用教程 10.5锁 表10.3两个用户竞争资源引起死锁的操作说明 T1T2T3 A 查询TAB1表的信息 (使用HOLDLOCK选 项) 修改 TAB2表 的数据 B 查询TAB2表的信息 (使用HOLDLOCK选 项) 修改 TAB1表 的数据 说明：HOLDLOCK选项表示将共享锁保留 到事务完成，而不是在相应的表、行或数据 页不再需要时就立即释放锁。 实用教程 10.5锁 SQLServer能自动发现并解除死锁。当发现 死锁时，它会选择其进程累计的CPU时间最少者 所对应的用户作为“牺牲者”(令其夭折)，以让其 他进程能继续执行。此时SQL Server发送错误 号1205(即error=1205)给牺牲者。 在并发操作中，为了避免死锁，建议采用以下 措施： ( 最大限度地减少保持事务打开的时间长度。 按同一顺序访问对象。 尽量避免事务中的用户交互。 保持事务简短并在一个批处理中。 实用教程 本章小结 SQL Server支持三种类型的用户自定义函数：标 量(Scalar)函数、内嵌表值(InLine TableValued)函数、多语句表值 (MultiStatement TableValued)函数。本章 通过三个实例说明了如何创建和使用标量函数 、内嵌表值函数、多语句表值函数的方法。 SQL Server的事务模式可分为三种：显式事务、 隐式事务、自动事务。本章主要介绍了如何使 用显式事务来实现数据操作的完整性和一致性 ，并对多用户系统使用事务处理