触发器和存储过程

单击此处编辑母版标题样式,单击此处编辑母版文本样式,第二级,第三级,第四级,第五级,*,第 16 讲 索引、触发器和存储过程、,DBMS的基本功能和系统结构,数据库技术,1,一、触发器和存储过程,当对一个表执行特定的操作时，被调用或者被“触发”的SQL语句称为触发器。触发器能够依次调用SQL语句或者存储过程。,触发器是事件驱动的SQL代码，当插入、删除、修改指定的表和列中的数据时，这些代码将自动执行，因此触发器总是与特定的数据库表及特定的数据库事件(如插入(Insert)、删除(Delete)、修改(Update)等)相联系。,2,触发器的优点,触发器不依赖于任何客户端应用程序，也不依赖于访问数据库的语言。触发器有如下优点：,1、标准化。在整个应用上，触发器保证了数据的完整性和一致性，一旦在表上建立了触发器，它就存储在数据库中；这种方法消除了各个客户应用程序的冗余编码，便于规则发生变化时对编码进行修改；,3,2、高效率。触发器初始执行后，作为编译的代码执行。它的运行速度快，与在客户工作站上执行这些代码相比，在服务器上执行这些代码减少了网络通讯量和网络冲突；触发器把数据完整性代码放在服务器平台上比放在客户工作站上更有效；,3、安全性。触发器运行要有表主人的授权，但是，触发器能够被在表中插入、删除、修改记录的任何一个用户触发。任何一个应用程序或交互式子用户都无法避开触发器。,4,触发器的功能,触发器对于强制执行的工作是非常有用的，它主要用于下面两种情况：,1、保证数据的完整性和一致性。当规则太复杂，不能用数据定义语言(DDL)定义它们、这时使用触发器非常有效；,5,2、实现数据之间逻辑联系的业务规则。,例如：在职工表中插入一个新的职工记录时，业务规则要求职工的工作终止日期字段是一个空值。这个规则不能使用数据定义语言中CREATE TABLE语句定义，它需要一个触发器。,对于不能由其它手段实现的规则可考虑使用触发器，以此改善系统的效率。如：参照完整性、表或列的约束等。,6,触发器的类型,触发器是根据所触发事件的类型进行分类的。通常的RDBMS支持三种类型的触发器：,插入触发器 (lnsent trigger)，在表中企图插入记录时触发；,删除触发器 (Delete trigger)，在表中企图删除记录时触发；,修改触发器 (Update trigger)，在表中企图修改记录时触发。,7,存储过程,存储过程是从客户应用或访问数据库的语句中分离出来的。存储过程的设置大大提高了SQL语句的功能、效率和灵活性，具体表现在：,1、改善了性能。,存储过程与原始SQL语句或批处理中的SQL语句的最大区别在于它是预先编译好的，当第一次运行一个过程时，数据库服务器对它进行分析并准备好一个执行计划，最后把这个计划存储在系统表中，以后过程的每次执行都可以依据这个存储的计划，由于大部,8,分查询处理工作已做好，不需再花费时间进行编译，因此存储过程的执行较快。,在客户工作站上，通常调用存储过程比把一系列的SQL语句发送到数据库引擎上更有效。当一份申请首次调用存储过程时，它被编译进数据库引擎的虚拟内存中，并在内存中运行。编译后的代码，被保留在内存中以便于以后的运行，这个工作完全在数据库服务器平台上执行，该平台比客户工作站执行效率更高。,9,2、改善了安全性。,存储过程简化了安全机制，它可以简化对某些操作的授权。例如，假如一个用户不希望别人在他的表上使用SELECT语句，他可以写一个仅可以修改这个表的某些行或列的存储过程，然后将调用该过程的权限授给特定的用户，这样其它用户就可以通过该存储过程访问这个表。,10,3、减少了网络通讯量。,在客户/服务器环境下，存储过程经过编译和优化后，存储在服务器端而不是客户端。 这样，在网络上传输的只是一个远程调用和最终的执行结果，而不是一系列的SQL语句和它们的返回结果，从而大大减少了网络上的传输量，提高了系统性能。,11,4、完成一些难以完成的工作。,如果本地数据库服务器和远程数据库服务器都允许远程登录，那么本地数据库服务器 就可以执行其它数据库服务器上的存储过程。例如，可以在本地服务器上写一个触发器， 当某一事件发生（如删除、更新或插入）时，该触发器执行远程服务器上的存储过程。,12,5、提高了开发者的劳动生产率。,存储过程消除了开发者为一个事务或一个运算法则的多次需求而编写和测试SQL语句的重复劳动。调用一个存储过程比写SQL语句更快、更简单。,6、有利于专门技术的使用。,技术熟练的开发者能够集中精力，写出复杂查询、运算法则和事务的存储过程。其它具有一般SQL经验的开发者能够调用这些存储过程。,13,7、简化了应用的维护，增加了应用的灵活性。,存储过程有助于从应用逻辑中把业务规则分离出来。当业务发生变化，需要修改业务规则时，这个变化只影响存储过程。与改变、测试和调整一个及多个客户的成千上万个用户程序相比，改变和测试存储过程是更有效、更省时间、少犯错误的好方法。,14,8、保证了运算法则的一致性。,存储过程能够确保运算法则每次以同样的精度运行。与此相反，在一个或多个客户应用中多处存放的嵌入式SQL语句，它们写的稍有不同，就会产生不同的结果。,15,存储过程潜在的缺点如下：,1、难以保持负荷平衡。,存储过程提出了一种在服务器上集中处理的模型，用集中处理代替事务的分布处理。这种方法，除非服务器的性能进行了优化，符合存储过程运行的需求，否则它会降低服务器的性能。,2、增加了管理要求。,存储过程是一个共享的资源。管理员必须确保使用这些存储过程，不允许同一个业务规则、事务、运算法则的多个存储过程存在。,16,3、存储过程在不同的RDBMS上语法不同。,存储过程使用了特定厂商扩展的SQL语句。一般来讲，存储过程代码不能方便地在不同的RDBMS之间进行转换。,17,应用访问数据库时。一般要经过5个步骤：,1 查询语句发送到服务器；,2 服务器编译SQL语句；,3 优化查询计划；,4 执行查询；,5 结果返回应用。,存储过程和触发器是在创建时编译的，当通过存储过程发出一个请求时，上述第2、3步就没有了，并且在第一步上也可以提高性能。,18,数据,应用,请求,回答,DBMS,完整性约束,触发器,存储过程,19,二、索引（SQL Server 2000）,数据库索引是以B树结构组织的。,1、索引概念,单一索引,：就是仅在一个表列上定义的索引。,组合索引,：就是在多个表列上定义的索引。,唯一索引,：各索引键值在索引中仅出现一次。当你在表上创建 Primary Key 和 Unique 约束时，SQL Server将创建唯一索引。,20,群集索引,群集索引是一种B树索引，它以分类次序在其叶结点中存储表的实际数据。,A-B,C-D,E-F,A,B,A,数据,B,C,D,C,D,E,F,E,F,数据,数据,数据,数据,数据,21,群集索引的优点：,到达节点，数据即可使用，意味着只需较少的I/O操作。,被检索的数据以索引分类的次序排列，可以减少排序。,群集索引的缺点：,访问表时总要通过索引，导致附加系统开销。,由于实际数据存储在群集索引中，所以在一个表上只能创建一个群集索引,（可以创建 249 个非群集索引）,。,22,非群集索引,A-B,C-D,E-F,A,B,A,R ID,B,C,D,C,D,E,F,E,F,R ID,R ID,R ID,R ID,R ID,数据行,数据行,数据行,数据行,数据行,数据行,23,2、设计有效索引,注 意：,如果一项查询中，需访问表中多于20%的行，则使用表扫描比使用索引效率高,。,使用索引的指导方针：,适量使用索引,不要索引较小的表（比如只有几百行）,尽可能使用较少的索引键列,尽可能使用覆盖查询（即查询内容包含于索引列中，则只有索引被访问，表本身被“绕”过）,24,数据库设计中需要深入考虑的问题,业务需求分析和系统建模,数据库设计,-设计关系表,-设计存取策略（索引、触发器、存储过程）,-设计视图,25,表之间的关系,学号,姓名,性别,生日,专业,班级,T_student,T_course,学号,课程代码,成绩,T_grade,课程代码,课程名称,学分,教师代码,T_teacher,教师代码,教师姓名,职称,部门,针对强制关联关系，建立触发器。,注意引用完整性,26,T_student,T_teacher,T_Course,T_grade,View,数据录入、修改,数据查询、报表,存储过程,触发器,27,三、 DBMS的基本功能和系统结构,1、DBMS的基本功能,数据库定义,对数据库的结构进行描述，包括外模式、模式、内模式的定义；数据库完整性的定义；安全保密定义 (如用户口令、级别、存取权限)；存取路径 (如索引)的定义。这些定义存储在数据字典 (亦称为系统目录)中，是DBMS运行的基本依据。,28,数据存取,提供用户对数据的操作功能，实现对数据库数据的检索、插入、修改和删除。一个好的DBMS应该提供功能强、易学易用的数据操纵语言（DML）、方便的操作方式和较高的数据存取效率。,数据库运行管理,这是指DBMS运行控制和管理功能。包括多用户环境下的事务的管理和自动恢复、并发控制和死锁检测 (或死锁防止)、安全性检查和存取控制、完整性检查和执行、运行日志的组织管理等。这些功能保证了数据库系统的正常运行。,29,数据组织、存储和管理,DBMS要分类组织、存储和管理各种数据，包括数据字典、用户数据、存取路径等。要确定以何种文件结构和存取方式在存储级上组织这些数据，如何实现数据之间的联系。数据组织和存储的基本目标是提高存储空间利用率和方便存取，提供多种存取方法（如索引查找、Hash查找、顺序查找等）提高存取效率。,30,数据库的建立和维护,包括数据库的初始建立、数据的转换、数据库的转储和恢复、数据库的重组织和重构造以及性能监测分析等功能。,其它功能,包括DBMS的网络通信功能，一个DBMS与另一个DBMS或文件系统的数据转换功能；异构数据库之间的互访和互操作能力等。,31,2、DBMS的系统结构,DBMS与操作系统：,从计算机软件系统的构成看，DBMS是建立在操作系统之上的软件系统，是操作系统的用户。操作系统负责计算机系统的进程管理、作业管理、存储器管理、设备管理、文件管理等，因此DBMS对共享数据的组织、管理和存取离不开操作系统的支持。DBMS遇到创建和撤销进程、进程通信、读写磁盘、分配内存等要求时必须请求操作系统的服务。例如，DBMS读取数据库中记录时就要调用操作系统读取磁盘块的操作，操作系统从磁盘取来的是一个物理块，对物理块的解释通常由DBMS来完成。,32,不同操作系统提供了不同的服务方式，例如现代操作系统不仅提供进程管理还进一步发展了线程的概念和技术。操作系统不同的服务方式，导致了DBMS在实现技术和实现方法上的诸多差异。对于同一个操作系统，DBMS也可以有不同的实现选择。,在诸多影响DBMS性能的因素中，DBMS在操作系统上的运行方式尤为突出，例如DBMS进程结构方案的选择是DBMS实现中一个关键问题，它影响DBMS的整体设计思想和DBMS的性能，影响DBMS实现的难易程度。,33,DB,OS,DBMS,DBMS,DBMS,应用1,应用2,应用N,2N方案,每个用户进程均有一个DBMS进程为之服务。在此情况下，数据库系统的各个活跃进程是独立运行的。,34,DB,OS,DBMS,应用1,应用2,应用N,N+1方案,一个DBMS进程为所有用户进程服务。在此情况下，DBMS进程的行为类似一个服务器(Server)。,35,由于要设计自己的多任务处理机制和调度算法，使得N+1方案的DBMS设计在整体上比2N方案要复杂。Server要处理所有用户的申请，如果调度策略不当或不能使数据库进程获得较高的优先权，这种方案将导致瓶颈。,另外，N+1方案中要用到操作系统级的消息机制以实现多进程向单进程以及单进程向多进程的数据传送。许多文献指出消息系统是一种昂贵的设施，大多数操作系统中消息往返一次的开销是几干条指令。,36,N+1方案的一个优点是可以采用多线程 (Multi-Threaded)技术来实现，从而可大大提高系统性能，降低系统资源的开销，简化DBMS许多部分的设计。这是因为线程机制使得N+!方案能用统一的“线程”思想处理所有执行流，其中包括DBMS核心的执行流，这将省去所有后台进程并能适应系统软件设计与开发的微内核 (Micro-Kernel)要求，使DBMS的核心代码简明精巧、易于移植。,37,数据库实例,(Instance)：,一组操作系统进程和内存区域的集合。Oracle和SQL Server 用它来管理数据库访问。常用“数据库服务”作为它的同义词。,数据库实例就是用来访问一个数据库文件集的一个存储结构及后台进程的集合。它使一个单独的数据库可以被多个实例访问（并行服务器方式）。,Windows NT上的每个数据库,实例作为一个包含多个轻便的运行后台线程的单一进程执行。每个后台服务器线程完成一个特点的系统功能。,38,专用服务器体系结构,Oracle,为,每个连接到实例的客户机启动一个专用的前台服务器线程,。该,线程只为其客户机工作。,客户机,实例,专用服务器,数据库,39,多线程服务器体系结构（MTS）,客户机,实例,调度程序,数据库,共享服务器,请求与响应队列,40,DBMS的层次结构,最上层是应用层，位于DBMS核心之外。它处理的对象是各种各样的数据库应用。该层是DBMS与终端用户和应用程序的界面层。,第2层是语言翻译处理层。它处理的对象是数据库语言，如SQL。向上提供的数据接口是关系、视图，即元组的集合。其功能是对数据库语言的各类语句进行语法分析、视图转换、授权检查、完整性检查、查询优化等。通过对下层基本模块的调用，生成可执行代码。这些代码的运行即可完成数据库语句,的功能要求。,第3层是数据存取层。该层处理的对象是单个元组。它把上层的集合操作转换为单记录操作。执行扫描 (如表扫描)、排序、元组的查找、插入、修，改、删除、封锁等基本操作。完成数据记录的存取、存取路径维护、并发控制、事务管理等工作。,第4层是数据存储层。该层处理的对象是数据页和系统缓冲区。执行文件的逻辑打开、关闭、读页、写页、缓冲区读和写、页面淘汰等操作，完成缓冲区管理、内外存交换、外存的数据管理等功能。,数据存储层,操作系统,DB,数据存取层,翻译处理层,应用层,41,操作系统是DBMS的基础。它处理的对象是数据文件的物理块。执行物理文件的读写操作，保证DBMS对数据逻辑上的读写真实地映射到物理文件上。操作系统提供的存取原语和基本的存取方法通常作为和DBMS存储层的接口。,42,DBMS运行过程示意图,应用程序,数据字典,RDBMS,OS,DB,系统,缓冲区,1,2,1 用户发出Select命令,2 DBMS首先对命令进行语法查。语法检查通过后，进行语义检查和存取权限检查。,3 DBMS进行查询优化。,4 DBMS在缓冲区中查找记录。如有，则转到9；否则，转到5。,5 查看存储模式。,6 向OS发出读取记录的命令。,7 OS执行。,8 OS将数据送往缓冲区。,3 5,4,6,7,8,10,9,11,9 导出用户所需数据格式。,10 将数据从缓冲区送往工作区。,11 将执行状态信息返回应用程序。,43,