数据库原理与应用 SQL Server 2005 教学课件 ppt 作者 廖瑞华 2_第6章　存储过程和触发器

数据库原理与应用（SQL Server 2005）,廖瑞华 主编,第5章 关系数据库标准语言SQL 第6章 存储过程和触发器 第7章 安全性管理,第6章 存储过程和触发器,1. 存储过程的创建与维护 2. 触发器的创建与维护 6.1 存储过程 6.2 触发器 6.3 本章小结 6.4 习题,1. 存储过程的创建与维护,2. 触发器的创建与维护,6.1 存储过程,6.1.1 存储过程的基本概念 1) 执行速度快。 2) 模块化的程序设计。 3) 减少网络流量。 4) 安全性。 5)可以将复杂的任务划分为多个简单的商业逻辑。 6) 存储过程对某些需要周期性处理的任务特别有用。 7) 存储过程在客户端和服务器端提供了良好的并发性。 1) 不需要任何客户端信息而在服务器端完成的操作。 2) 涉及大量行的操作、运算。 3) 频繁的、周期性的操作。 4) 那些被多个模块共同执行的操作。,6.1 存储过程,6.1.2 创建存储过程 1. 利用对象资源管理器创建存储过程 1) 启动“SQL Server Management Studio”，在对象资源管理器中展开“数据库”文件夹，展开要在其中创建存储过程的数据库(这里为jwgl)结点，展开“可编程性”结点。 2) 右击“存储过程”项，将弹出如图6-1所示的快捷菜单，选择快捷菜单中的“新建存储过程”项，系统将在查询编辑器中打开存储过程模板，如图6-2所示。,6.1 存储过程,图6-1 在对象资源管理器中创建存储过程,6.1 存储过程,图6-2 系统存储过程模板,3) 在模板中输入存储过程的名称、参数。,6.1 存储过程,4) 单击“确定”按钮，会看到存储过程模板代码会有所变化，如图6-5所示。 5) 修改代码。 6) 在工具栏中单击“执行”按钮，在“存储过程模板代码”下面会显示“命令成功完成”。,6.1 存储过程,图6-3 指定模板参数的值,6.1 存储过程,图6-4 输入参数的值,6.1 存储过程,图6-5 设置参数后的系统存储过程模板,7) 右击左侧窗口中的“存储过程”选项，,6.1 存储过程,在弹出菜单中单击“刷新”选项，会发现该存储过程下会多出一个名为“StudentInfoBystuNo”的结点。 8)单击工具栏的“保存”按钮，保存为SQL文件。 2. 使用Transact-SQL命令创建存储过程 1) procedure_name：要创建的存储过程的名称。 2) number ：用于对同名的过程进行分组的整数，以方便批量删除。 3) parameter：过程中的参数。 4) data_type：参数的数据类型。 5) VARYING：指定作为OUTPUT参数支持的结果集(由存储过程动态构造，内容可以变化)，仅适用于cursor参数。 6) default：参数的默认值。 7) OUTPUT：表明参数是返回参数。,6.1 存储过程,8) RECOMPILE：表明 SQL Server 不会缓存该过程的计划，该过程将在运行时重新编译。 9) ENCRYPTION：表示对存储在表syscomments中的存储过程文本进行加密，以防止其他用户查看或修改。 10) FOR REPLICATION：指定不能在订阅服务器上执行为复制创建的存储过程。 11) AS sql_statement：指定过程要执行的操作。 12) n 是表示此过程可以包含多条 Transact-SQL 语句的占位符。 6.1.3 执行存储过程 1. 手动执行存储过程 1) returm_status：用于保存存储过程返回状态的整型变量。 2) procedure_name_var：局部定义变量名，代表存储过程名称。,6.1 存储过程,3) value：存储过程的参数值。 4) variable：用来保存参数或者返回参数的变量。 5) DEFAULT：表示参数值取默认值。 2. 自动执行存储过程 1) ProcName=procedure：存储过程的名称。 2) OptionName=option：设置存储过程的选项名称。 3) OptionValue=value：表示选项状态。 6.1.4 存储过程的返回值和状态信息 1) 新建查询，在“查询编辑器”里输入如下SQL语句： 2) 执行查询，结果如图6-6所示。,6.1 存储过程,图6-6 存储过程的执行结果及返回值,6.1.5 存储过程的修改和删除,6.1 存储过程,1. 修改存储过程 (1) 使用对象资源管理器修改存储过程 1) 启动“SQL Server Management Studio”，在对象资源管理器中展开“数据库”文件夹，展开要在其中创建存储过程的数据库(这里为jwgl)结点，依次展开“可编程性”结点和“存储过程”结点。 2) 右击要修改的存储过程名“dbo. proc_stu”，在弹出菜单中选择“修改”选项。 3) 将代码修改，重新执行，保存即可。 (2) 使用T-SQL语句修改存储过程 2. 删除存储过程 (1) 使用T-SQL语句删除存储过程 (2) 使用对象资源管理器删除存储过程,6.1 存储过程,1) 启动“SQL Server Management Studio”，在对象资源管理器中展开“数据库”文件夹，展开要在其中创建存储过程的数据库(这里为jwgl)结点，依次展开“可编程性”结点和“存储过程”结点。 2) 右击要删除的存储过程名，在弹出菜单中选择“删除”选项，弹出“删除对象”窗口，单击“确定”按钮。,6.2 触发器,6.2.1 触发器的概念 6.2.2 触发器的功能 1) 强化约束：触发器能够实现比CHECK 语句更复杂的约束。 2) 跟踪执行：触发器可以侦测数据库中内在的操作，从而不允许未经许可的插入、更新和删除操作影响数据库。 3) 级联运行：触发器可以侦测数据库内的操作，并自动级联影响整个数据库中的各项内容。 4) 存储过程的调用：由于触发器是一种特殊的存储过程，存储过程可以嵌套使用，所以触发器可以调用一个或多个存储过程，实现强大的功能。 6.2.3 触发器的分类 (1) DML触发器,6.2 触发器,1) AFTER 触发器只有在执行了某一操作(INSERT、UPDATE、DELETE)之后，触发器才被触发，且只能在表上定义。 2) INSTEAD OF触发器表示并不执行命令所定义的操作(INSERT、UPDATE、DELETE)，而仅是执行触发器中定义的代码。 (2) DDL触发器 6.2.4 Inserted表和deleted表 6.2.5 操作触发器的注意事项 1) 只有表的拥有者才能在表上创建或删除触发器，权限不能转授给其他用户。 2) 在用SQL语句创建触发器时，CREATE TRIGGER必须是批处理中的第一个语句，该语句后面的所有语句都被解释为CREATE TRIGGER语句定义的一部分。,6.2 触发器,3) 触发器的名称必须遵循标识符的命名规则。 4) 可以在触发器中引用视图或临时表，但不能在视图、临时表或系统表上创建触发器。 5) 对于含有用DELETE或UPDATE操作定义的外键的表，不能定义INSTEAD OF DELETE和INSTEAD OF UPDATE触发器。 6) 使用UPDATE语句可以一次对多行数据进行修改，但不管修改了多少数据，触发器都只触发一次。 7) 在执行修改语句过程中，触发器的执行是修改语句事务的一部分。 8) 当使用约束、规则、默认值就可以实现预定的完整性时，应优先考虑使用这3种措施。,6.2 触发器,9) TRUNCATE TABLE虽然在功能上与DELETE操作类似，但是TRUNCATE TABLE不会触发DELETE触发器。 10) WRITETEXT语句不会触发INSERT或UPDATE触发器。 6.2.6 创建触发器 1) 利用SQL Server对象资源管理器创建触发器。 2) 使用Transact-SQL语句中的CREATE TRIGGER命令创建触发器。 1. 利用SQL Server对象资源管理器创建触发器 1) 启动“SQL Server Management Studio”，在对象资源管理器中展开“数据库”文件夹，展开“jwgl”结点，展开“表”结点，单击结点“dbo.CourseInfo”，右击其下的“触发器”，弹出如图6-7所示的快捷菜单。,6.2 触发器,图6-7 通过对象资源管理器创建存储触发器,2) 选择快捷菜单中的“新建触发器”项，,6.2 触发器,系统将在查询编辑器中打开触发器模板，如图6-8所示。,6.2 触发器,图6-8 查询编辑器中打开触发器模板,3) 在模板中修改代码，,6.2 触发器,将从“CREATE”到“GO”结束的代码改为以下代码：,图6-9 创建成功的触发器“course_insert”,6.2 触发器,4) 在工具栏中单击“分析”按钮，检查语法是否有错，如果在图6-8下面的“结果”区域里出现“命令已成功完成”，则表示语法无错。 5) 如果语法没错，在工具栏中单击“执行”按钮，生成触发器。 6) 右击图6-7所示的“触发器”，在弹出菜单中单击“刷新”，会发现该触发器下会多出一个名为“course_insert”的结点，如图6-9所示。 7) 单击工具栏的“保存”按钮，保存为SQL文件。 2. 使用Transact-SQL命令创建触发器 (1) 创建DML触发器的语法格式 (2) 创建DDL触发器的语法格式 1) trigger_name ：触发器的名称。,6.2 触发器,2) schema_name：DML 触发器所属架构的名称。 3) table|view ：对其执行 DML 触发器的表或视图，有时称为触发器表或触发器视图。 4) DATABASE：将 DDL 触发器的作用域应用于当前数据库。 5) ALL SERVER：将 DDL的作用域应用于当前服务器。 6) WITH ENCRYPTION ：对 CREATE TRIGGER 语句的文本进行加密。 7) EXECUTE AS：指定用于执行该触发器的安全上下文。 8) FOR | AFTER ： 指定触发器仅在触发 SQL 语句中指定的所有操作都已成功执行时才被触发。 9) INSTEAD OF ： 指定执行 DML 触发器是“代替”SQL 语句执行的，因此，其优先级高于触发语句的操作。,6.2 触发器,10) INSERT , UPDATE,DELETE：指定数据修改语句，这些语句可在 DML 触发器对此表或视图进行尝试时激活该触发器。 11) event_type：执行之后将导致激发 DDL 触发器的 Transact-SQL 语言事件的名称。 12) event_group：预定义的 Transact-SQL 语言事件分组的名称。 13) WITH APPENG：指定应该再添加一个现有类型的触发器。 14) NOT FOR REPLICATION：指示当复制代理修改涉及触发器的表时，不应执行触发器。 15) sql_statement：触发条件和操作。 1) 新建查询，在“查询编辑器”里输入如下SQL语句： 2) 在工具栏中单击“分析”按钮，检查语法是否有错。,6.2 触发器,3) 如果语法没错，在工具栏中单击“执行”按钮，生成触发器。 1) 启动“SQL Server Management Studio”，在对象资源管理器中展开“数据库”文件夹，选中“jwgl”结点。 2) 单击“新建查询”按钮，在“查询编辑器”里输入以下代码：,图6-10 生成的DDL触发器“DisableDropTable”,6.2 触发器,3) 在工具栏中单击“分析”按钮，检查语法是否有错。 4) 如果语法没错，在工具栏中单击“执行”按钮，生成DDL触发器。 5) 右击“jwgl”数据库结点，在弹出菜单中单击“刷新”，展开“可编程性”结点，展开“数据库触发器”，会发现该结点下多出一