第10章存储过程和触发器.pptVIP

第10章 存储过程和触发器 10.1 存储过程的概念 10.2 建立和执行存储过程 10.3 存储过程的管理与维护 10.4 触发器的概念 10.5 创建和应用DML触发器 10.6 触发器的管理与维护 10.7 DDL触发器 10.8 事务 本章重点 存储过程的概念、分类 存储过程的创建、执行 触发器的概念、分类 触发器的创建、触发 DELETED与INSERTED表 本章难点 有参存储过程的创建、执行 触发器的触发时机 DELETED与INSERTED表 10.1 存储过程的概念 10．1．1 基本概念 存储过程是一组编译在单个执行计划中的Transact-SQL语句，将一些固定的操作集中起来交给SQL Server数据库服务器完成，以实现某个任务。 10．1．2 存储过程的优点 （1）与其他应用程序共享应用程序逻辑，因而确保了数据访问和修改的一致性。 （2）防止数据库中表的细节暴露给用户。 （3）提供安全机制。 （4）改进性能。 （5）减少网络流量。 10．1．3 存储过程的类型 存储过程分为三类： 系统提供的存储过程 系统提供的存储过程系统存储过程的名字都以“sp_”为前缀 例：sp_bindefault ，sp_bindrule ，sp_help ， sp_helpdb, sp_helpindex 等 用户定义的存储过程 用户定义的存储过程是由用户为完成某一特定功能而编写的存储过程。存储在创建时的数据库中。 扩展存储过程： 扩展存储过程是用来调用操作系统提供的功能。 10.2 建立和执行存储过程 简单存储过程类似于将一组SQL语句起个名字，然后就可以在需要时反复调用。复杂一些的则要有输入和输出参数。 10．2．1 创建和执行简单存储过程 创建存储过程的基本语法如下： CREATE PROC 存储过程名 [WITH ENCRYPTION] [WITH RECOMPILE] AS SQL 语句 其中各参数如下： [WITH ENCRYPTION]：对存储过程进行加密。 [WITH RECOMPILE]：对存储过程重新编译。 【例10.1】创建一个无参数的存储过程，在SALES数据库中,创建存储过程xs,查询销售编号,商品名称,数量。 10．2．2 存储过程的执行 执行存储过程的基本语法如下： EXEC[UTE] 存储过程名 同时EXECUTE命令除了可以执行存储过程外还可以执行存放Transact-SQL语句的字符串变量，或直接执行Transact-SQL语句字符串。此时EXECUTE语句的语法格式如下。 EXECUTE({@字符串变量| [N]SQL语句字符串}[+...n] 其中“@字符串变量”是局部字符串变量名，最大值为服务器的可用内存。[N] SQL语句字符串的语句字符串是一个由SQL语句构成的字符串常量。如果包含N，则该字符串将解释为nvarchar数据类型。 【例10.2】建立一个批处理，查询相应表中的信息。 10．2．3 带输入参数的存储过程 1．建立存储过程 一个存储过程可以带一个或多个参数，输入参数是指由调用程序向存储过程传递的参数，它们在创建存储过程语句中被定义，在执行存储过程中给出相应的参数值。 声明带输入参数的存储过程的语法格式如下： CREATE PROCEDURE 存储过程名 @参数名 数据类型[=默认值] [，…n] [WITH ENCRYPTION] [WITH RECOMPILE] AS SQL 语句 其中“@参数名”和定义局部变量一样，必须以符号@为前缀，要指定数据类型，多个参数定义要用“，”隔开。在执行存储过程时该参数将由指定的参数值来代替，如果执行时未提供该参数的参数值，则使用时须定义默认值（默认值可以是常量或空值null），否则将产生错误。 【例10.3】创建一个带输入参数的存储过程PROC_GOODS,查询指定员工所进商品信息 2．执行存储过程 在执行存储过程的语句中，有两种方式传递参数值，分别是使用参数名传递参数值和按参数位置传递参数值。 使用参数名传递参数值，是通过语句“@参数名=参数值”给参数传递值。当存储过程含有多个输入参数时，对数值可以按任意顺序给出，对于允许空值和具有默认值的输入参数可以不给参数值，其语法格式为： EXECUTE 存储过程名 [@参数名=参数值] [，…n] 按参数位置传递参数值，不显式地给出“@参数名”，而是按照参数定义的顺序给出参数值。按位置传递参数时，也可以忽略允许为空值和有默认值的参数，但不能因此破坏输入参数的指定顺序。必要时使用关