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

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