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

《第8章存储过程、触发器和函数.ppt》由会员分享，可在线阅读，更多相关《第8章存储过程、触发器和函数.ppt（48页珍藏版）》请在第壹文秘上搜索。

1、第第8章章存储过程、触发器存储过程、触发器本章学习目标本章学习目标 掌握存储过程的基本概念、特点和类型掌握存储过程的基本概念、特点和类型 掌握存储过程的创建和执行方式掌握存储过程的创建和执行方式 掌握存储过程的管理方式掌握存储过程的管理方式 掌握触发器的基本概念、特点和类型掌握触发器的基本概念、特点和类型 掌握触发器的创建和管理方式掌握触发器的创建和管理方式本章本章主要内容主要内容 存储过程概述存储过程概述 创建和执行存储过程创建和执行存储过程 管理存储过程管理存储过程 创建触发器创建触发器 管理触发器管理触发器8.1 存储过程概述存储过程概述1.存储过程的定义2.存储过程的特点3.存储过程的

2、类型1.存储过程的定义存储过程的定义 存储过程是一组为了完成特定任务的SQL语句 集，是 利 用 S Q L S e r v e r 所 提 供 的Transact-SQL语言所编写的程序。存储过程经编译和优化后存储在数据库服务器中。存储过程可以由应用程序通过一个调用来执行，而且允许用户声明变量。存储过程可以接收和输出参数、返回执行存储过程的状态值。2.存储过程的特点存储过程的特点 存储过程允许用户模块化程序设计，大大提高程序的设计效率。存储过程可以强制应用程序的安全性，可以防止SQL嵌入式攻击。由于存储过程在服务器端存储和运行，并且第一次执行后在内存中保留，以后调用时不需要再次从磁盘装载，能

3、够实现更快的执行速度。用户执行存储过程时，只需在客户端通过指明所调用的存储过程的名称来调用存储过程，可大大较少网络通信流量。存储过程具有安全特性和所有权链接，可以执行所有的权限管理。3.存储过程的类型存储过程的类型 用户自定义存储过程 扩展存储过程 系统存储过程。8.2 创建和执行存储过程创建和执行存储过程 1.在图形界面下创建存储过程2.用Transact-SQL语句创建不带参数的存储过程 3.用Transact-SQL语句创建带参数的存储过程 4.在图形界面下执行存储过程5.使用Transact-SQL语句执行存储过程1.在图形界面下创建存储过程在图形界面下创建存储过程2.用用Transa

4、ct-SQL语句创建不带参语句创建不带参数的存储过程数的存储过程 语法：CREATE PROCEDURE procedure_nameASsql_statement【例8-1】CREATE PROCEDURE hotel_proc2ASSELECT 身份证号,姓名,电话 FROM 客户信息3.用用Transact-SQL语句创建带参数语句创建带参数的存储过程的存储过程 语法：CREATE PROCEDURE procedure_name;numberparameter data_type VARYING=defaultOUTPUT,.n WITH RECOMPILE|ENCRYPTION|RE

5、COMPILE,ENCRYPTIONFOR REPLICATION AS sql_statement .n 【例8-2】CREATE PROCEDURE hotel_proc3name varchar(12)ASSELECT*FROM 客户信息WHERE 姓名=name4.在图形界面下执行存储过程在图形界面下执行存储过程5.使用使用Transact-SQL语句执行存储语句执行存储过程过程EXECUTE procedure_name【例8-4】DECLARE temp varchar(12)SET temp=李铭EXECUTE hotel_proc3 temp【例例8-3】创建一名为创建一名为h

6、otel_proc4的存储过程，实现前的存储过程，实现前n个自然数的求个自然数的求和功能，并使得该存储过程在每次执行时都被重新编译，且要求对其原和功能，并使得该存储过程在每次执行时都被重新编译，且要求对其原始始Transact-SQL语句进行加密。语句进行加密。CREATE PROCEDURE hotel_proc4in int,sum int OUTPUTWITH RECOMPILE,ENCRYPTIONASDECLARE i intDECLARE s intSET i=1SET s=0WHILE i=inBEGINSET s=s+iSET i=i+1ENDSET sum=s【例8-5】执行

7、例8-3创建的存储过程hotel_proc4。DECLARE s intEXECUTE hotel_proc4 100,s OUTPUTPRINT 1+2+.+99+100=+str(s)8.3管理存储过程管理存储过程1.使用“对象资源管理器”查看存储过程 2.使用系统存储过程查看存储过程3.使用“对象资源管理器”修改存储过程4.使用Transact-SQL语句修改存储过程5.使用“对象资源管理器”删除存储过程6.使用Transact-SQL语句删除存储过程 1、使用、使用“对象资源管理器对象资源管理器”查看存储查看存储过程过程 2、使用系统存储过程查看存储过程、使用系统存储过程查看存储过程

8、用 户 可 以 先 用 系 统 存 储 过 程sp_stored_procedures列出当前环境中所有的存储过程，然后使用另一个系统存储过程sp_helptext查看某存储过程的正文信息。3、使用、使用“对象资源管理器对象资源管理器”修改存修改存储过程储过程4、使用、使用Transact-SQL语句修改存语句修改存储过程储过程ALTER PROCEDURE procedure_name;numberparameter data_type VARYING=defaultOUTPUT,.nWITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFOR REPL

9、ICATIONAS sql_statement.n5、使用、使用“对象资源管理器对象资源管理器”删除存删除存储过程储过程6、使用、使用Transact-SQL语句删除存语句删除存储过程储过程DROP PROCEDURE procedure_name,number8.4创建和管理触发器创建和管理触发器 1.触发器定义触发器定义2.触发器的特点触发器的特点3.触发器的分类触发器的分类4.创建创建DML触发器触发器5.创建创建DDL触发器触发器 6.查看触发器查看触发器7.修改修改DML触发器触发器 8.修改修改DDL触发器触发器9.重命名触发器重命名触发器 10.删除删除DML触发器触发器11.删

10、除删除DDL触发器触发器12.禁用启用触发器禁用启用触发器1、触发器定义、触发器定义l触发器是一种特殊的存储过程，它包括了大量的Transact-SQL语句。l但是触发器又与一般的存储过程有着显著的区别，一般的储过程可以由用户直接调用执行，但是触发器不能被直接调用执行，它只能由事件触发而自动执行。2、触发器的特点触发器的特点触发器是自动执行的，当用户对表中数据作了某些操作之后立即被触发。触发器可通过数据库中的相关表实现级联更改，实现多个表之间数据的一致性和完整性。触发器可以强制比用 CHECK 约束定义的约束更为复杂的约束。与 CHECK 约束不同，触发器可以引用其它表中的列。触发器也可以评估

11、数据修改前后的表状态，并根据其差异采取对策。3、触发器的分类触发器的分类lDML触发器lDDL触发器4、创建、创建DML触发器触发器CREATE TRIGGER schema_name.trigger_nameON table|viewWITH ENCRYPTIONFOR|AFTER|INSTEAD OFINSERT,UPDATE,DELETEWITH APPENDNOT FOR REPLICATIONAS sql_statement;,.n|EXTERNAL NAME 【例8-10】创建名为hotel_trig1的INSERT触发器，当用户对数据库hotel的客户信息表进行INSERT操作时

12、被触发，返回受影响的行数信息。（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_trig2ON 客

13、户信息FOR UPDATEASIF update(姓名)BEGINraiserror(不能修改姓名,16,10)ROLLBACK transactionEND（2）可以触发该触发器的UPDATE语句如下：UPDATE 客户信息 SET 姓名=王鹏 WHERE 姓名=王蒙5、创建、创建DDL触发器触发器CREATE TRIGGER trigger_name ON ALL SERVER|DATABASEWITH ENCRYPTIONFOR|AFTER event_type|event_group,.nAS sql_statement;,.n|EXTERNAL NAME ;【例8-12】创建名为ho

14、tel_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：用于查看触发器的正文信

15、息。sp_depends trigger_name|table_name：用于查看触发器所引用的表或表所涉及的触发器。7、修改修改DML触发器触发器 ALTER TRIGGER schema_name.trigger_nameON table|viewWITH ENCRYPTIONFOR|AFTER|INSTEAD OFDELETE,INSERT,UPDATENOT FOR REPLICATIONASsql_statement;.n|EXTERNAL NAME ;8、修改修改DDL触发器触发器ALTER TRIGGER trigger_nameON DATABASE|ALL SERVERWIT

16、H ENCRYPTIONFOR|AFTERevent_type,.n|event_groupASsql_statement;|EXTERNAL NAME ;9、重命名触发器、重命名触发器sp_rename old_name,new_name10、删除删除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|ALLON object_name|DATABASE|ALL SERVER;启用ENABLE TRIGGER schema_name.trigger_name,.n|ALLON object_name|DATABASE|ALL SERVER;【例8-17】禁用数据库hotel中“客户信息”表的触发器hotel_trig4，语句如下：DISABLE TRIGGER hotel_trig4