搜索
第8章存储过程、触发器和函数本章学习目标•掌握存储过程的基本概念、特点和类型•掌握存储过程的创建和执行方式•掌握存储过程的管理方式•掌握触发器的基本概念、特点和类型•掌握触发器的创建和管理方式•了解常用的聚合函数、日期和时间函数、数学函数、字符串函数本章主要内容•存储过程概述•创建和执行存储过程•管理存储过程•创建触发器•管理触发器•常用函数1.存储过程的定义2.存储过程的特点3.存储过程的类型8.1存储过程概述•存储过程是一组为了完成特定任务的SQL语句集，是利用SQLServer所提供的Transact-SQL语言所编写的程序。•存储过程经编译和优化后存储在数据库服务器中。•存储过程可以由应用程序通过一个调用来执行，而且允许用户声明变量。•存储过程可以接收和输出参数、返回执行存储过程的状态值。1.存储过程的定义•存储过程允许用户模块化程序设计，大大提高程序的设计效率。•存储过程可以强制应用程序的安全性，可以防止SQL嵌入式攻击。•由于存储过程在服务器端存储和运行，并且第一次执行后在内存中保留，以后调用时不需要再次从磁盘装载，能够实现更快的执行速度。•用户执行存储过程时，只需在客户端通过指明所调用的存储过程的名称来调用存储过程，可大大较少网络通信流量。•存储过程具有安全特性和所有权链接，可以执行所有的权限管理。2.存储过程的特点•用户自定义存储过程•扩展存储过程•系统存储过程。3.存储过程的类型1.在图形界面下创建存储过程2.用Transact-SQL语句创建不带参数的存储过程3.用Transact-SQL语句创建带参数的存储过程4.在图形界面下执行存储过程5.使用Transact-SQL语句执行存储过程8.2创建和执行存储过程1.在图形界面下创建存储过程2.用Transact-SQL语句创建不带参数的存储过程语法：CREATEPROC[EDURE]procedure_nameASsql_statement【例8-1】CREATEPROCEDUREhotel_proc2ASSELECT身份证号,姓名,电话FROM客户信息3.用Transact-SQL语句创建带参数的存储过程语法：CREATEPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]【例8-2】CREATEPROCEDUREhotel_proc3@namevarchar(12)ASSELECT*FROM客户信息WHERE姓名=@name4.在图形界面下执行存储过程5.使用Transact-SQL语句执行存储过程EXEC[UTE]procedure_name【例8-4】DECLARE@tempvarchar(12)SET@temp='李铭'EXECUTEhotel_proc3@temp1.使用“对象资源管理器”查看存储过程2.使用系统存储过程查看存储过程3.使用“对象资源管理器”修改存储过程4.使用Transact-SQL语句修改存储过程5.使用“对象资源管理器”删除存储过程6.使用Transact-SQL语句删除存储过程8.3管理存储过程1、使用“对象资源管理器”查看存储过程①用户可以先用系统存储过程sp_stored_procedures列出当前环境中所有的存储过程，②然后使用另一个系统存储过程sp_helptext查看某存储过程的正文信息。2、使用系统存储过程查看存储过程3、使用“对象资源管理器”修改存储过程ALTERPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITH{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]4、使用Transact-SQL语句修改存储过程5、使用“对象资源管理器”删除存储过程DROPPROC[EDURE]procedure_name[,number]6、使用Transact-SQL语句删除存储过程1.触发器定义2.触发器的特点3.触发器的分类4.创建DML触发器5.创建DDL触发器6.查看触发器7.修改DML触发器8.修改DDL触发器9.重命名触发器10.删除DML触发器11.删除DDL触发器12.禁用启用触发器8.4创建和管理触发器触发器是一种特殊的存储过程，它包括了大量的Transact-SQL语句。但是触发器又与一般的存储过程有着显著的区别，一般的储过程可以由用户直接调用执行，但是触发器不能被直接调用执行，它只能由事件触发而自动执行。1、触发器定义•触发器是自动执行的，当用户对表中数据作了某些操作之后立即被触发。•触发器可通过数据库中的相关表实现级联更改，实现多个表之间数据的一致性和完整性。•触发器可以强制比用CHECK约束定义的约束更为复杂的约束。与CHECK约束不同，触发器可以引用其它表中的列。•触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。2、触发器的特点DML触发器DDL触发器3、触发器的分类CREATETRIGGER[schema_name.]trigger_nameON{table|view}[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[INSERT][,][UPDATE][,][DELETE]}[WITHAPPEND][NOTFORREPLICATION]AS{sql_statement[;][,...n]|EXTERNALNAMEmethodspecifier[;]}4、创建DML触发器CREATETRIGGERtrigger_nameON{ALLSERVER|DATABASE}[WITHENCRYPTION]{FOR|AFTER}{event_type|event_group}[,...n]AS{sql_statement[;][,...n]|EXTERNALNAMEmethodspecifier[;]}5、创建DDL触发器•sp_helptrigger_name：用于查看触发器的名称、属性、类型、创建时间等一般信息。•sp_helptexttrigger_name：用于查看触发器的正文信息。•sp_dependstrigger_name|table_name：用于查看触发器所引用的表或表所涉及的触发器。6、查看触发器ALTERTRIGGERschema_name.trigger_nameON{table|view}[WITHENCRYPTION]{FOR|AFTER|INSTEADOF}{[DELETE][,][INSERT][,][UPDATE]}[NOTFORREPLICATION]AS{sql_statement[;][...n]|EXTERNALNAMEmethodspecifier[;]}7、修改DML触发器ALTERTRIGGERtrigger_nameON{DATABASE|ALLSERVER}[WITHENCRYPTION]{FOR|AFTER}{event_type[,...n]|event_group}AS{sql_statement[;]|EXTERNALNAMEmethodspecifier[;]}8、修改DDL触发器sp_renameold_name,new_name9、重命名触发器DROPTRIGGER[schema_name.]trigger_name[,...n][;]10、删除DML触发器DROPTRIGGERtrigger_name[,...n]ON{DATABASE|ALLSERVER}[;]11、触发器定义禁用DISABLETRIGGER{[schema_name.]trigger_name[,...n]|ALL}ON{object_name|DATABASE|ALLSERVER}[;]启用ENABLETRIGGER{[schema_name.]trigger_name[,...n]|ALL}ON{object_name|DATABASE|ALLSERVER}[;]12、禁用启用触发器1.聚合函数2.日期和时间函数3.数学函数4.字符串函数8.5函数•存储过程的基本概念、类型和特点，以及如何创建、执行、管理存储过程；•触发器的概念、类型和特点，以及如何创建、管理触发器；•聚合函数、日期和时间函数、数学函数、字符串函数等常用函数的功能。8.6小结