第十章存储过程和触发器清华大学.pptVIP

第10章 存储过程和触发器 10.1 存储过程的概念 10.2 建立和执行存储过程 10.3 存储过程的管理与维护 10.4 触发器的概念 10.5 创建和应用DML触发器 10.6 触发器的管理与维护 10.7 DDL触发器 10.8 事务 第10章 存储过程和触发器 教学要求：通过本章学习，读者应掌握以下内容： 存储过程和触发器的作用 存储过程的创建、修改和删除 存储过程的灵活运用 触发器的创建、修改和删除 10.1 存储过程的概念 10．1．1 基本概念 存储过程是一组编译在单个执行计划中的Transact-SQL语句，将一些固定的操作集中起来交给SQL Server数据库服务器完成，以实现某个任务。 10．1．2 存储过程的优点 （1）与其他应用程序共享应用程序逻辑，因而确保了数据访问和修改的一致性。 （2）防止数据库中表的细节暴露给用户。 （3）提供安全机制。 （4）改进性能。 （5）减少网络流量。 10．1．3 存储过程的类型 （1）系统存储过程。 （2）本地存储过程。 （3）临时存储过程。 （4）远程存储过程。 （5）扩展存储过程。 10.2 建立和执行存储过程 简单存储过程类似于将一组SQL语句起个名字，然后就可以在需要时反复调用。复杂一些的则要有输入和输出参数。 10．2．1 创建和执行简单存储过程 创建存储过程的基本语法如下： CREATE PROCEDURE 存储过程名 [WITH ENCRYPTION] [WITH RECOMPILE] AS SQL 语句 其中各参数如下： [WITH ENCRYPTION]：对存储过程进行加密。 [WITH RECOMPILE]：对存储过程重新编译。 【例10.1】从xsgl数据库的三个表中查询，返回学生学号、姓名、课程名、成绩。该存储过程实际上只返回一个查询信息。 --建立存储过程 USE xsgl GO CREATE PROCEDURE stu_cj AS SELECT xs.学号,姓名,课程名,成绩 FROM xs INNER JOIN cj ON xs.学号=cj.学号 INNER JOIN kc ON cj.课程号=kc.课程号 GO 10．2．2 存储过程的执行 执行存储过程的基本语法如下： EXEC[UTE] 存储过程名 同时EXECUTE命令除了可以执行存储过程外还可以执行存放Transact-SQL语句的字符串变量，或直接执行Transact-SQL语句字符串。此时EXECUTE语句的语法格式如下。 EXECUTE({@字符串变量| [N]SQL语句字符串}[+...n] 其中“@字符串变量”是局部字符串变量名，最大值为服务器的可用内存。[N] SQL语句字符串的语句字符串是一个由SQL语句构成的字符串常量。如果包含N，则该字符串将解释为nvarchar数据类型。 【例10.2】建立一个批处理，查询相应表中的信息。 DECLARE @tab_name varchar(20) SET @tab_name=xs EXECUTE(SELECT * FROM +@tab_name) GO 10．2．3 带输入参数的存储过程 1．建立存储过程 一个存储过程可以带一个或多个参数，输入参数是指由调用程序向存储过程传递的参数，它们在创建存储过程语句中被定义，在执行存储过程中给出相应的参数值。 声明带输入参数的存储过程的语法格式如下： CREATE PROCEDURE 存储过程名 @参数名 数据类型[=默认值] [，…n] [WITH ENCRYPTION] [WITH RECOMPILE] AS SQL 语句 其中“@参数名”和定义局部变量一样，必须以符号@为前缀，要指定数据类型，多个参数定义要用“，”隔开。在执行存储过程时该参数将由指定的参数值来代替，如果执行时未提供该参数的参数值，则使用时须定义默认值（默认值可以是常量或空值null），否则将产生错误。 【例10.3】从xsgl数据库的三个表中查询某人指定课程的成绩。 USE xsgl GO IF EXISTS (SELECT name FROM SYSOBJECTS WHERE name=stu_cj1 AND type=P) DROP PROCEDURE stu_cj1 GO CREATE PROCEDURE stu_cj1 @name char(10),@cname char(16) AS SELECT xs.学号,姓名,课程名,成绩 FROM xs INNER JOIN cj ON xs.学号=cj.学号INNER JOIN kc ON cj.课程号=kc.课程号