第8章存储过程、触发器和函数课件.ppt
第第8章章存储过程、触发器和函数存储过程、触发器和函数 本章学习目标本章学习目标 掌握存储过程的基本概念、特点和类型掌握存储过程的基本概念、特点和类型 掌握存储过程的创建和执行方式掌握存储过程的创建和执行方式 掌握存储过程的管理方式掌握存储过程的管理方式 掌握触发器的基本概念、特点和类型掌握触发器的基本概念、特点和类型 掌握触发器的创建和管理方式掌握触发器的创建和管理方式 了解常用的聚合函数、日期和时间函数、数了解常用的聚合函数、日期和时间函数、数学函数、字符串函数学函数、字符串函数本章本章主要内容主要内容 存储过程概述存储过程概述 创建和执行存储过程创建和执行存储过程 管理存储过程管理存储过程 创建触发器创建触发器 管理触发器管理触发器 常用函数常用函数存储过程的定义存储过程的特点存储过程的类型8.1 存储过程概述存储过程概述 存储过程是一组为了完成特定任务的SQL语句集，是利用SQL Server所提供的Transact-SQL语言所编写的程序。 存储过程经编译和优化后存储在数据库服务器中。 存储过程可以由应用程序通过一个调用来执行，而且允许用户声明变量。 存储过程可以接收和输出参数、返回执行存储过程的状态值。1. 存储过程的定义 存储过程允许用户模块化程序设计，大大提高程序的设计效率。 存储过程可以强制应用程序的安全性，可以防止SQL嵌入式攻击。 由于存储过程在服务器端存储和运行，并且第一次执行后在内存中保留，以后调用时不需要再次从磁盘装载，能够实现更快的执行速度。 用户执行存储过程时，只需在客户端通过指明所调用的存储过程的名称来调用存储过程，可大大较少网络通信流量。 存储过程具有安全特性和所有权链接，可以执行所有的权限管理。2.存储过程的特点 用户自定义存储过程 扩展存储过程 系统存储过程。 3.存储过程的类型在图形界面下创建存储过程用Transact-SQL语句创建不带参数的存储过程 用Transact-SQL语句创建带参数的存储过程 在图形界面下执行存储过程使用Transact-SQL语句执行存储过程8.2 创建和执行存储过程创建和执行存储过程 1. 在图形界面下创建存储过程2. 用Transact-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 | RECOMPILE , 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使用“对象资源管理器”查看存储过程 使用系统存储过程查看存储过程使用“对象资源管理器”修改存储过程使用Transact-SQL语句修改存储过程使用“对象资源管理器”删除存储过程使用Transact-SQL语句删除存储过程 8.3管理存储过程管理存储过程1、使用“对象资源管理器”查看存储过程 用 户 可 以 先 用 系 统 存 储 过 程sp_stored_procedures列出当前环境中所有的存储过程，然后使用另一个系统存储过程sp_helptext查看某存储过程的正文信息。2、使用系统存储过程查看存储过程3、使用“对象资源管理器”修改存储过程ALTER PROCEDURE procedure_name ;numberparameter data_type VARYING=defaultOUTPUT,.nWITH RECOMPILE | ENCRYPTION | RECOMPILE,ENCRYPTIONFOR REPLICATIONAS sql_statement .n4、使用Transact-SQL语句修改存储过程5、使用“对象资源管理器”删除存储过程DROP PROCEDURE procedure_name ,number6、使用Transact-SQL语句删除存储过程触发器定义触发器定义触发器的特点触发器的特点触发器的分类触发器的分类创建创建DML触发器触发器创建创建DDL触发器触发器 查看触发器查看触发器修改修改DML触发器触发器 修改修改DDL触发器触发器重命名触发器重命名触发器 删除删除DML触发器触发器删除删除DDL触发器触发器禁用启用触发器禁用启用触发器8.4创建和管理触发器创建和管理触发器 l触发器是一种特殊的存储过程，它包括了大量的Transact-SQL语句。l但是触发器又与一般的存储过程有着显著的区别，一般的储过程可以由用户直接调用执行，但是触发器不能被直接调用执行，它只能由事件触发而自动执行。 1、触发器定义触发器是自动执行的，当用户对表中数据作了某些操作之后立即被触发。触发器可通过数据库中的相关表实现级联更改，实现多个表之间数据的一致性和完整性。触发器可以强制比用 CHECK 约束定义的约束更为复杂的约束。与 CHECK 约束不同，触发器可以引用其它表中的列。触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。2、触发器的特点触发器的特点lDML触发器lDDL触发器3、触发器的分类触发器的分类CREATE TRIGGER schema_name.trigger_nameON table | viewWITH ENCRYPTIONFOR | AFTER | INSTEAD OFINSERT , UPDATE , DELETEWITH APPENDNOT FOR REPLICATIONAS sql_statement ; ,.n | EXTERNAL NAME 4、创建、创建DML触发器触发器CREATE TRIGGER trigger_name ON ALL SERVER | DATABASEWITH ENCRYPTIONFOR | AFTER event_type | event_group ,.nAS sql_statement ; ,.n | EXTERNAL NAME ;5、创建、创建DDL触发器触发器sp_help trigger_name：用于查看触发器的名称、属性、类型、创建时间等一般信息。sp_helptext trigger_name：用于查看触发器的正文信息。sp_depends trigger_name | table_name：用于查看触发器所引用的表或表所涉及的触发器。6、查看触发器查看触发器ALTER TRIGGER schema_name.trigger_nameON table | viewWITH ENCRYPTIONFOR | AFTER | INSTEAD OFDELETE , INSERT , UPDATENOT FOR REPLICATIONASsql_statement ; .n | EXTERNAL NAME ;7、修改修改DML触发器触发器 ALTER TRIGGER trigger_nameON DATABASE | ALL SERVERWITH ENCRYPTIONFOR | AFTERevent_type ,.n | event_groupASsql_statement ; | EXTERNAL NAME ; 8、修改修改DDL触发器触发器sp_rename old_name,new_name9、重命名触发器、重命名触发器DROP TRIGGER schema_name.trigger_name ,.n ;10、删除删除DML触发器触发器DROP TRIGGER trigger_name ,.n ON DATABASE | ALL SERVER ;11、触发器定义禁用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 ;12、禁用启用触发器禁用启用触发器聚合函数日期和时间函数数学函数字符串函数8.5 函数函数存储过程的基本概念、类型和特点，以及如何创建、执行、管理存储过程；触发器的概念、类型和特点，以及如何创建、管理触发器；聚合函数、日期和时间函数、数学函数、字符串函数等常用函数的功能。8.6 小结小结
