其他数据库对象学习资料(ppt 73页).ppt

04 03 2020 第1页 第10章其他数据库对象 学习目标本章重点本章内容 04 03 2020 第2页 学习目标 从业务数据角度来看 同一种业务数据有可能分散在不同的表中 如何从一个数据库对象中查看这些分散存储的数据呢 从安全角度来看 不同的操作人员或许只能看到表中不同部分的数据 从数据的应用角度来看 一个报表中的数据往往来自于多个不同的表中 如何提高报表的设计效率呢 视图是解决这些问题的一种有效手段 存储过程是一个可重用的代码模块 可以高效率地完成指定的操作 触发器是一种特殊类型的存储过程 可以实现自动化的操作 用户定义函数是由用户根据自己应用程序的需要而定义的可以完成特定操作的函数 本章将全面研究视图 存储过程 触发器 用户定义函数等数据库对象的特点和使用方式 04 03 2020 第3页 本章重点 视图特点和类型管理视图存储过程特点和类型管理存储过程触发器特点和类型管理触发器用户定义函数 04 03 2020 第4页 本章内容 10 1视图10 2存储过程10 3触发器10 4用户定义函数10 5上机练习10 6习题 04 03 2020 第5页 本章内容 10 1视图10 2存储过程10 3触发器10 4用户定义函数10 5上机练习10 6习题 04 03 2020 第6页 10 1视图 本节全面研究与视图有关的内容 首先 分析视图的作用和存在意义 其次 研究了视图的类型和特点 接下来 探讨创建视图技术 然后 讨论如何通过视图修改表中数据 最后 浏览如何通过图形化工具来创建和维护视图 04 03 2020 第7页 概述 数据是存储在表中 对数据的操纵主要是通过表进行的 但是 仅仅通过表操纵数据会带来一系列的性能 安全 效率等问题 下面 对这些问题进行分析 从业务数据角度来看 由于数据库设计时考虑到数据异常等问题 同一种业务数据有可能被分散在不同的表中 但是对这种业务数据的使用经常是同时使用的 前面讲过的连接 子查询 联合等技术就是解决这种问题的一种手段 但是 对于多个表来说这些操作都是比较复杂的 能不能只通过一个数据库对象就可以同时看到这些分散存储的业务数据呢 如果能得话 将大大简化查询语句的复杂程度 从数据安全角度来看 由于工作性质和需求不同 不同的操作人员只是需要查看表中的部分数据 不能查看表中的所有数据 从数据的应用角度来看 在设计报表时 需要明确地指定数据的来源途径和方式 能不能采取有效手段 提高报表的设计效率呢 解决上述问题的一种有效手段就是视图 04 03 2020 第8页 视图的内容 基表的列的子集或行的子集 也就是说视图可以是基表的其中一部分 两个或多个基表的联合 也就是说视图是对多个基表进行联合运算检索的SELECT语句 两个或多个基表的连接 也就是说视图是通过对若干个基表的连接生成的 基表的统计汇总 也就是说视图不仅是基表的投影 还可以是经过对基表的各种复杂运算的结果 另外一个视图的子集 也就是说视图既可以基于表 也可以基于另外一个视图 来自于函数或同义词中的数据 视图和基表的混合 在视图的定义中 视图和基表可以起到同样的作用 04 03 2020 第9页 视图类型 在MicrosoftSQLServer2005系统中 可以把视图分成3种类型 即标准视图 索引视图和分区视图 一般情况下的视图都是标准视图 它是一个虚拟表并不占物理存储空间 如果希望提高聚合多行数据的视图性能 那么可以创建索引视图 索引视图是被物理化的视图 它包含有经过计算的物理数据 通过使用分区视图 可以连接一台或多台服务器中成员表中的分区数据 使得这些数据看起来就像来自一个表中一样 04 03 2020 第10页 创建视图 在MicrosoftSQLServer2005系统中 主要使用CREATEVIEW语句创建视图 只能在当前数据库中创建视图 当创建视图时 MicrosoftSQLServer首先验证视图定义中所引用的对象是否存在 视图的名称应该符合命名规则 是否指定视图的架构则是可选的 视图的外表和表的外表是一样的 因此为了区别表和视图 建议采用一种命名机制 使人容易分辨出视图和表 例如可以在视图名称之前使用vw 作为前缀 04 03 2020 第11页 例10 1 使用CREATEVIEW语句创建简单的视图 04 03 2020 第12页 例10 2 使用sp helptext系统存储过程查看视图信息 04 03 2020 第13页 例10 3 使用WITHENCRYPTION子句加密视图定义文本信息 04 03 2020 第14页 例10 4 使用DROPVIEW语句删除视图 04 03 2020 第15页 通过视图修改数据 无论在什么时候修改视图的数据 实际上都是在修改视图的基表中的数据 在满足一定的限制条件下 可以通过视图自由地插入 删除和更新基表中的数据 在修改视图时 要注意一些条件 04 03 2020 第16页 例10 5 通过视图修改数据 04 03 2020 第17页 例10 5 04 03 2020 第18页 使用SQLServerManagementStudio 除了使用CREATEVIEW语句之外 也可以使用SQLServerManagementStudio图形化工具定义视图 04 03 2020 第19页 例10 6 使用图形化工具定义视图 04 03 2020 第20页 本章内容 10 1视图10 2存储过程10 3触发器10 4用户定义函数10 5上机练习10 6习题 04 03 2020 第21页 10 2存储过程 存储过程可以提高应用程序的设计效率和增强系统的安全性 本节全面介绍存储过程的特点 类型 创建及执行等内容 04 03 2020 第22页 存储过程的特点 存储过程是一个可重用的代码模块 可以高效率地完成指定的操作 在MicrosoftSQLServer2005系统中 既可以使用Transact SQL语言编写存储过程 也可以使用CLR方式编写存储过程 使用CLR编写存储过程是MicrosoftSQLServer2005系统与 NET框架紧密集成的一种表现形式 使用Transact SQL语言编写存储过程而不使用存储在客户端计算机上的Transact SQL语言有许多优点 04 03 2020 第23页 存储过程的类型 在MicrosoftSQLServer2005系统中 提供了3种基本的存储过程类型 即用户定义的存储过程 扩展存储过程和系统存储过程 用户定义的存储过程是主要的存储过程类型 是封装了可重用代码的模块或例程 用户定义的存储过程可以接受输入参数 向客户端返回表格或标量结果和消息 调用数据定义语言 数据操纵语言语句 然后返回参数 在MicrosoftSQLServer2005系统中 用户定义的存储过程又可以分为Transact SQL类型的存储过程和CLR类型的存储过程 04 03 2020 第24页 扩展存储过程 扩展存储过程是指使用某种变成语言如C语言创建的外部例程 是可以在MicrosoftSQLServer实例中动态加载和运行的DLL 但是 微软公司宣布从MicrosoftSQLServer2005版本开始 将逐步删除扩展存储过程类型 这是因为使用CLR存储过程可以可靠和安全地替代扩展存储过程的功能 04 03 2020 第25页 系统存储过程 系统存储过程是指用来完成MicrosoftSQLServer2005中许多管理活动的特殊存储过程 从物理上来看 系统存储过程存储在Resource系统数据库中 并且带有sp 前缀 从逻辑上来看 系统存储过程出现在每个系统数据库和用户数据库的sys架构中 在MicrosoftSQLServer2005系统中 主要的系统存储过程类型和功能如表10 1所示 04 03 2020 第26页 创建存储过程的规则 在设计和创建存储过程中应该满足一定的约束和规则 只有满足了这些约束和规则 才可以创建有效的存储过程 虽然说在CREATEPROCEDURE语句中可以包括任意数量和类型的Transact SQL语句 但是某些特殊的语句是不能包含在存储过程定义中的 04 03 2020 第27页 创建存储过程 在MicrosoftSQLServer2005系统中 可以使用CREATEPROCEDURE语句创建存储过程 需要强调的是 必须具有CREATEPROCEDURE权限才能创建存储过程 存储过程是架构作用域中的对象 只能在本地数据库中创建存储过程 在创建存储过程时 应该指定所有的输入参数 执行数据库操作的编程语句 返回至调用过程或批处理表明成功或失败的状态值 捕捉和处理潜在错误的错误处理语句 04 03 2020 第28页 CREATEPROCEDURE语句 CREATEPROCEDUREprocedure nameparameter namedata type WITHprocedure optionASsql statement 04 03 2020 第29页 例10 7 使用CREATEPROCEDURE语句创建存储过程 04 03 2020 第30页 例10 8 使用OUTPUT关键字创建存储过程 04 03 2020 第31页 执行存储过程 在MicrosoftSQLServer2005系统中 可以使用EXECUTE语句执行存储过程 EXECUTE语句也可以简写为EXEC 如果将要执行的存储过程需要参数 那么应该在存储过程名称后面带上参数值 04 03 2020 第32页 例10 9 使用EXEC语句执行存储过程 04 03 2020 第33页 例10 10 执行带有参数的存储过程 04 03 2020 第34页 例10 11 在执行EXEC语句时引用参数和使用OUTPUT关键字 04 03 2020 第35页 修改和删除存储过程 在MicrosoftSQLServer2005系统中 可以使用ALTERPROCEDURE语句修改已经存在的存储过程 修改存储过程不同于删除和重建存储过程 其目的是保持存储过程的权限不发生变化 例如 如果修改HumanResources GetEmployeeInfo存储过程 那么与该存储过程对象相关的权限将不会发生任何变化 但是 如果删除HumanResources GetEmployeeInfo存储过程并且重新创建同名的存储过程 那么该存储过程对象相关的权限都需要重新定义 如果数据库中某个存储过程不再需要了 可以使用DROPPROCEDURE语句删除该存储过程 这种删除是永久性的 不能恢复 04 03 2020 第36页 存储过程的执行过程 存储过程创建之后 在第一次执行时需要经过语法分析阶段 解析阶段 编译阶段和执行阶段 语法分析阶段是指创建存储过程时 系统检查其创建语句的语法正确性的过程 在存储过程的创建过程中如果碰到语法错误 那么该存储过程创建失败 如果语法检查通过之后 系统将该存储过程的名称存储在当前数据库的sys sql modules目录视图中 解析阶段是指某个存储过程首次执行时 查询处理器从sys sql modules目录视图中读取该存储过程的文本并且检查该过程引用的对象名称是否存在的过程 编译阶段是指分析存储过程和生成存储过程执行计划的过程 执行阶段是指执行驻留在过程高速缓冲存储区中的存储过程的执行计划的过程 04 03 2020 第37页 查看存储过程的信息 在MicrosoftSQLServer2005系统中 可以使用系统存储过程和目录视图查看有关存储过程的信息 如果希望查看存储过程的定义信息 那么可以使用sys sql modules目录视图 OBJECT DEFINITION元数据函数 sp helptext系统存储过程等 04 03 2020 第38页 例10 12 查看存储过程信息 04 03 2020 第39页 本章内容 10 1视图10 2存储过程10 3触发器10 4用户定义函数10 5上机练习10 6习题 04 03 2020 第40页 10 3触发器 MicrosoftSQLServer2005系统提供了两种强制业务逻辑和数据完整性的机制 即约束技术和触发器技术 前面已经讲过了约束技术 本节讲述触发器技术 04 03 2020 第41页 触发器类型 按照触发事件的不同 可以把MicrosoftSQLServer2005系统提供的触发器分成两大类型 即DML触发器和DDL触发器 需要补充的是 DDL触发器是MicrosoftSQLServer2005系统新增的功能 以前的版本只有DML触发器 当数据库中发生数据操纵语言 datamanipulationlanguage DML 事件时将调用DML触发器 DML事件包括在指定表或视图中修改数据的INSERT语句 UPDATE语句或DELETE语句 在DML触发器中 可以执行查询其他表的操作 也可以包含更加复杂的Transact SQL语句 DML触发器将触发器本身和触发事件的语句作为可以在触发器内回滚的单个事务对待 也就是说 当在执行触发器操作过程中 如果检测到错误发生 则整个触发事件语句和触发器操作的事务自动回滚 DDL触发器与DML触发器相同的是 都需要触发事件进行触发 但是 DDL触发器的触发事件是数据定义语言 datadefinitionlanguage DDL 语句 04 03 2020 第42页 DML触发器的基本类型 按照触发器事件类型的不同 可以把MicrosoftSQLServer2005系统DML触发器分成3种类型 即INSERT类型 UPDATE类型和DELETE类型 这也是DML触发器的基本类型 04 03 2020 第43页 创建DML触发器 DML触发器是一种特殊类型的存储过程 所以DML触发器的创建和存储过程的创建方式有很多相似的地方 可以使用CREATETRIGGER语句创建DML触发器 在CREATETRIGGER语句中 指定了定义触发器的基表或视图 触发事件的类型和触发的时间 触发器的所有指令等内容 使用CREATETRIGGER语句创建DML触发器 04 03 2020 第44页 CREATETRIGGER语句 CREATETRIGGERtrigger nameONtable name or view nameWITHENCRYPTION FOR AFTER INSTEADOF DELETE INSERT UPDATE ASsql statement 04 03 2020 第45页 例10 13 查看触发器信息 04 03 2020 第46页 DML触发器的工作原理 前面介绍了DML触发器的基本概念 类型 特点 创建 修改 删除等内容 现在我们来看看触发器是如何工作的 通过了解触发器的工作原理 就可以更好地使用触发器 写出效率更高的触发器 下面主要介绍INSERT DELETE和UPDATE类型触发器的工作原理 04 03 2020 第47页 INSERT触发器的工作原理 当向表中插入数据时 INSERT触发器触发执行 当INSERT触发器触发时 新的记录增加到触发器表中和inserted表中 该inserted表是一个逻辑表 保存了所插入记录的拷贝 允许用户参考INSERT语句中数据 触发器可以检查inserted表 来确定该触发器的操作是否应该执行和如何执行 在inserted表中的那些记录 总是触发器表中一行或多行记录的冗余 04 03 2020 第48页 DELETE触发器的工作原理 当触发一个DELETE触发器时 被删除的记录放在一个特殊的deleted表中 deleted表是一个逻辑表 用来保存已经从表中删除的记录 该deleted表允许参考原来的DELETE语句删除的已经记录在日志中的数据 当使用DELETE语句时 应该考虑下列一些因素 当记录放在deleted表中的时候 该记录就不会存在数据库的表中了 因此 在数据库表和deleted表之间没有共同的记录 逻辑表deleted总是存放在内存中 以提供性能 在DELETE触发器中 不能包括TRUNCATETABLE语句 这是因为该语句是不记日志的操作 04 03 2020 第49页 UPDATE触发器的工作原理 修改一条记录就等于插入一条新记录和删除一条旧记录 同样 UPDATE语句也可以看成是由删除一条记录的DELETE语句和增加一条记录的INSERT语句组成 当在某一个有UPDATE触发器表的上面修改一条记录时 表中原来的记录移动到deleted表中 修改过的记录插入到了inserted表中 触发器可以检查deleted表和inserted表及被修改的表 以便确定是否修改了多个行和应该如何执行触发器的操作 04 03 2020 第50页 一个具体的示例 为了更加全面地掌握开发触发器的步骤和技术 本节通过一个具体的示例 全面讲述使用Transact SQL语言开发和创建触发器的技术 一般地 开发触发器的过程包括用户需求分析 确定触发器的逻辑结构 编写触发器代码和测试触发器 04 03 2020 第51页 例10 14 设计和实现DML触发器 04 03 2020 第52页 创建t accountData insert触发器 04 03 2020 第53页 创建t accountData delete触发 04 03 2020 第54页 一组插入数据的操作 04 03 2020 第55页 审计到的插入数据的操作 04 03 2020 第56页 一组删除数据的操作 04 03 2020 第57页 审计到的删除数据的操作 04 03 2020 第58页 DDL触发器 DDL触发器与DML触发器有许多类似的地方 如可以自动触发完成规定的操作 都可以使用CREATETRIGGER语句创建等 但是也有一些不同的地方 例如 DDL触发器的触发事件主要是CREATE ALTER DROP以及GRANT DENY及REVOKE等语句 并且触发的时间条件只有AFTER 没有INSTEADOF 04 03 2020 第59页 例10 15 使用DDL触发器 04 03 2020 第60页 本章内容 10 1视图10 2存储过程10 3触发器10 4用户定义函数10 5上机练习10 6习题 04 03 2020 第61页 10 4用户定义函数 在MicrosoftSQLServer2005系统中 用户定义函数是接受参数 执行操作并且将运算结果以值的形式返回的例程 这种返回值既可以是单个标量值 也可以是一个结果集 在MicrosoftSQLServer2005系统中 用户定义函数既可以使用Transact SQL语言编写 也可以使用任何 NET编程语言来编写 04 03 2020 第62页 特点 在MicrosoftSQLServer2005系统中 所有的用户定义函数都具有相同的由两部分组成的结构 标题和正文 标题可以定义这些内容 具有可选架构 所有者名称的函数名称 输入参数名称和数据类型 可以用于输入参数的选项 返回参数数据类型和可选名称 可以用于返回参数的选项等 正文定义了函数将要执行的操作 这些操作既可以是一个或多个Transact SQL语句 也可以是 NET程序集的引用 在MicrosoftSQLServer2005系统中 用户定义函数又可以分为两大类 即用户定义标量函数和用户定义表值函数 用户定义标量函数返回在RETURNS子句中定义的类型的单个数据值 对于多语句标量函数 定义在BEGINEND块中的函数体包含一系列返回单个值的Transact SQL语句 返回类型可以是除text ntext image cursor timestamp以外的任何数据类型 用户定义表值函数返回table数据类型 实际上 在表值函数中 表是单个SELECT语句的结果集 04 03 2020 第63页 创建时的考虑 在MicrosoftSQLServer2005系统中 可以分别使用CREATEFUNCTION ALTERFUNCTION DROPFUNCTION语句来实现用户定义函数的创建 修改和删除 在创建用户定义函数时 每个完全限定用户函数的名称 schema name function name 必须唯一 函数的BEGINEND块中的语句不能有任何副作用 函数副作用是指对具有函数外作用域 例如修改数据库表 的资源状态的任何永久性更改 函数中的语句唯一能做的更改是对函数上的局部对象 如局部游标或局部变量 的更改 不能在函数中执行的操作包括对数据库表的修改 对不在函数上的局部游标进行操作 发送电子邮件 尝试修改目录 以及生成返回至用户的结果集 04 03 2020 第64页 使用CREATEFUNCTION语句 在MicrosoftSQLServer2005系统中 使用CREATEFUNCTION语句可以创建标量函数 内联表值函数 多语句表值函数 需要说明的是 如果RETURNS子句指定了一种标量数据类型 则该函数为标量值 如果R