第8章存储过程、触发器和函数.ppt

第8章 存储过程、触发器,本章学习目标,掌握存储过程的基本概念、特点和类型 掌握存储过程的创建和执行方式 掌握存储过程的管理方式 掌握触发器的基本概念、特点和类型 掌握触发器的创建和管理方式,本章主要内容,存储过程概述 创建和执行存储过程 管理存储过程 创建触发器 管理触发器,8.1 存储过程概述,存储过程的定义 存储过程的特点 存储过程的类型,1. 存储过程的定义,存储过程是一组为了完成特定任务的SQL语句集，是利用SQL Server所提供的Transact-SQL语言所编写的程序。 存储过程经编译和优化后存储在数据库服务器中。 存储过程可以由应用程序通过一个调用来执行，而且允许用户声明变量。 存储过程可以接收和输出参数、返回执行存储过程的状态值。,2.存储过程的特点,存储过程允许用户模块化程序设计，大大提高程序的设计效率。 存储过程可以强制应用程序的安全性，可以防止SQL嵌入式攻击。 由于存储过程在服务器端存储和运行，并且第一次执行后在内存中保留，以后调用时不需要再次从磁盘装载，能够实现更快的执行速度。 用户执行存储过程时，只需在客户端通过指明所调用的存储过程的名称来调用存储过程，可大大较少网络通信流量。 存储过程具有安全特性和所有权链接，可以执行所有的权限管理。,3.存储过程的类型,用户自定义存储过程 扩展存储过程 系统存储过程。,8.2 创建和执行存储过程,在图形界面下创建存储过程 用Transact-SQL语句创建不带参数的存储过程 用Transact-SQL语句创建带参数的存储过程 在图形界面下执行存储过程 使用Transact-SQL语句执行存储过程,1. 在图形界面下创建存储过程,2. 用Transact-SQL语句创建不带参数的存储过程,语法： CREATE PROCEDURE procedure_name AS sql_statement 【例8-1】 CREATE PROCEDURE hotel_proc2 AS SELECT 身份证号,姓名,电话 FROM 客户信息,3. 用Transact-SQL语句创建带参数的存储过程,语法： CREATE PROCEDURE procedure_name ;number parameter data_type VARYING=defaultOUTPUT,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE , ENCRYPTION FOR REPLICATION AS sql_statement .n 【例8-2】 CREATE PROCEDURE hotel_proc3 name varchar(12) AS SELECT * FROM 客户信息 WHERE 姓名=name,4. 在图形界面下执行存储过程,5.使用Transact-SQL语句执行存储过程,EXECUTE procedure_name 【例8-4】 DECLARE temp varchar(12) SET temp='李铭' EXECUTE hotel_proc3 temp,【例8-3】创建一名为hotel_proc4的存储过程，实现前n个自然数的求和功能，并使得该存储过程在每次执行时都被重新编译，且要求对其原始Transact-SQL语句进行加密。,CREATE PROCEDURE hotel_proc4 in int,sum int OUTPUT WITH RECOMPILE,ENCRYPTION AS DECLARE i int DECLARE s int SET i=1 SET s=0 WHILE i=in BEGIN SET s=s+i SET i=i+1 END SET sum=s,【例8-5】执行例8-3创建的存储过程hotel_proc4。 DECLARE s int EXECUTE hotel_proc4 100,s OUTPUT PRINT '1+2+.+99+100='+str(s),8.3管理存储过程,使用“对象资源管理器”查看存储过程 使用系统存储过程查看存储过程 使用“对象资源管理器”修改存储过程 使用Transact-SQL语句修改存储过程 使用“对象资源管理器”删除存储过程 使用Transact-SQL语句删除存储过程,1、使用“对象资源管理器”查看存储过程,2、使用系统存储过程查看存储过程,用户可以先用系统存储过程sp_stored_procedures列出当前环境中所有的存储过程， 然后使用另一个系统存储过程sp_helptext查看某存储过程的正文信息。,3、使用“对象资源管理器”修改存储过程,4、使用Transact-SQL语句修改存储过程,ALTER PROCEDURE procedure_name ;number parameter data_type VARYING=defaultOUTPUT,.n WITH RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTION FOR REPLICATION AS sql_statement .n,5、使用“对象资源管理器”删除存储过程,6、使用Transact-SQL语句删除存储过程,DROP PROCEDURE procedure_name ,number,8.4创建和管理触发器,触发器定义 触发器的特点 触发器的分类 创建DML触发器 创建DDL触发器 查看触发器 修改DML触发器 修改DDL触发器 重命名触发器 删除DML触发器 删除DDL触发器 禁用启用触发器,1、触发器定义,触发器是一种特殊的存储过程，它包括了大量的Transact-SQL语句。 但是触发器又与一般的存储过程有着显著的区别，一般的储过程可以由用户直接调用执行，但是触发器不能被直接调用执行，它只能由事件触发而自动执行。,2、触发器的特点,触发器是自动执行的，当用户对表中数据作了某些操作之后立即被触发。 触发器可通过数据库中的相关表实现级联更改，实现多个表之间数据的一致性和完整性。 触发器可以强制比用 CHECK 约束定义的约束更为复杂的约束。与 CHECK 约束不同，触发器可以引用其它表中的列。 触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。,3、触发器的分类,DML触发器 DDL触发器,4、创建DML触发器,CREATE TRIGGER schema_name.trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF INSERT , UPDATE , DELETE WITH APPEND NOT FOR REPLICATION AS sql_statement ; ,.n | EXTERNAL NAME ,【例8-10】创建名为hotel_trig1的INSERT触发器，当用户对数据库hotel的客户信息表进行INSERT操作时被触发，返回受影响的行数信息。 （1）编写创建触发器hotel_trig1的语句代码如下： CREATE TRIGGER hotel_trig1 ON 客户信息 FOR INSERT AS PRINT '插入了一条客户记录' （2）可以触发该触发器的INSERT语句如下： INSERT INTO 客户信息 VALUES('410111198810114411','王蒙','13598820173'),【例8-11】创建名为hotel_trig2的UPDATE触发器，防止用户修改客户信息表中的“姓名”列。 （1）编写创建触发器hotel_trig2的语句代码如下： CREATE TRIGGER hotel_trig2 ON 客户信息 FOR UPDATE AS IF update (姓名) BEGIN raiserror('不能修改姓名',16,10) ROLLBACK transaction END （2）可以触发该触发器的UPDATE语句如下： UPDATE 客户信息 SET 姓名='王鹏' WHERE 姓名='王蒙',5、创建DDL触发器,CREATE TRIGGER trigger_name ON ALL SERVER | DATABASE WITH ENCRYPTION FOR | AFTER event_type | event_group ,.n AS sql_statement ; ,.n | EXTERNAL NAME ;,【例8-12】创建名为hotel_trig3的DDL触发器，阻止删除数据库中的任何表。 （1）编写创建触发器hotel_trig3的语句代码如下： CREATE TRIGGER hotel_trig3 ON DATABASE FOR DROP_TABLE AS PRINT '该触发器禁止删除数据库中的表，如果确实要删除表，请先禁用该触发器！' ROLLBACK; （2）触发该触发器的DROP TABLE语句如下： DROP TABLE 业务记录,6、查看触发器,sp_help trigger_name：用于查看触发器的名称、属性、类型、创建时间等一般信息。 sp_helptext trigger_name：用于查看触发器的正文信息。 sp_depends trigger_name | table_name：用于查看触发器所引用的表或表所涉及的触发器。,7、修改DML触发器,ALTER TRIGGER schema_name.trigger_name ON table | view WITH ENCRYPTION FOR | AFTER | INSTEAD OF DELETE , INSERT , UPDATE NOT FOR REPLICATION AS sql_statement ; .n | EXTERNAL NAME ;,8、修改DDL触发器,ALTER TRIGGER trigger_name ON DATABASE | ALL SERVER WITH ENCRYPTION FOR | AFTER event_type ,.n | event_group AS sql_statement ; | EXTERNAL NAME ;,9、重命名触发器,sp_rename old_name,new_name,10、删除DML触发器,DROP TRIGGER schema_name.trigger_name ,.n ;,11、触发器定义,DROP TRIGGER trigger_name ,.n ON DATABASE | ALL SERVER ;,12、禁用启用触发器,禁用 DISABLE TRIGGER schema_name.trigger_name ,.n | ALL ON object_name | DATABASE | ALL SERVER ; 启用 ENABLE TRIGGER schema_name.trigger_name ,.n | ALL ON object_name | DATABASE | ALL SERVER ;,【例8-17】禁用数据库hotel中“客户信息”表的触发器hotel_trig4，语句如下： DISABLE TRIGGER hotel_trig4 ON 客户信息 【例8-18】启用数据库hotel中“客户信息”表的触发器hotel_trig4，语句如下： ENABLE TRIGGER hotel_trig4 ON 客户信息,8.6 小结,存储过程的基本概念、类型和特点，以及如何创建、执行、管理存储过程； 触发器的概念、类型和特点，以及如何创建、管理触发器；,