第09章 存储过程.ppt

第 9 章 存 储 过 程 通过本章的学习，应该掌握以下内容： ? 存储过程的作用; ? 熟练创建、修改、删除存储过程; ? 在实际应用开发时能够灵活运用存储过程以提高开发效率。 第 9 章 存 储 过 程 9.1 存储过程的概念 9.2 建立和执行存储过程 9.3 存储过程中参数的使用 9.4 存储过程的管理与维护 9.5 系统存储过程和扩展存储过程 9.1 存储过程的概念 SQL Server应用操作中,存储过程和触发器扮演相当重要的角色,不仅能提高应用效率,确保一直性,更能提高系统执行速度。同时，使用触发器来完成业务规则，达到简化程序设计的目的。 9.1 存储过程的概念 存储过程包括系统存储过程和用户存储过程。 系统存储过程我们以前接触过： Execute sp_help 表名 ：查看表的结构。 Execute sp_helpindex 表名 ：查看表上的索引信息。 Execute sp_helptext 视图名：查看视图的定义信息 。 9.1 存储过程的概念 存储过程按返回的数据类型，可分为两类：一类类似于SELECT语句，用于查询数据，查询到的数据以结果集的形式给出；另一类存储过程是通过输出参数返回信息，或不返回信息只执行一个动作。存储过程可以嵌套，即一个存储过程的内部可以调用另一个存储过程。 9.1.1 基本概念 存储过程是一组编译在单个执行计划中的Transact-SQL语句，它将一些固定的操作集中起来交给SQL Server数据库服务器完成，以实现某个任务。 存储过程就是预先编译和优化并存储于数据库中的过程。 9.1.2 存储过程的优点 （1）通过本地存储、代码预编译和缓存技术实现高性能的数据操作。 （2）通过通用编程结构和过程重用实现编程框架。 （3）通过隔离和加密的方法提高了数据库的安全性。 使用存储过程的好处 （1）允许模块化的程序设计。 （2）更快的执行速度。 （3）有效降低网络流量。 （4）交好地安全机制。 9.2 建立和执行存储过程 9.2.1 创建和执行简单存储过程 创建存储过程的基本语法如下： CREATE PROCEDURE 存储过程名 [WITH ENCRYPTION]—对存储过程加密 [WITH RECOMPILE]—对存储过程重新编译 AS SQL语句 9.2.2 执行字符串 EXECUTE语句除了可以执行存储过程外，还可以执行存放SQL语句的字符串变量，或直接执行SQL语句字符串。此时EXECUTE语句的语法格式如下。 EXECUTE({@字符串变量|[N]’SQL语句字符串’}[+...n]) 9.3 存储过程中参数的使用 9.3.1 带输入参数的存储过程 1．建立存储过程 一个存储过程可以带一个或多个输入参数，输入参数是指由调用程序向存储过程传递的参数，它们在创建存储过程语句中被定义，在执行存储过程中给出相应的参数值。 声明带输入参数的存储过程的语法格式如下： CREATE PROCEDURE 存储过程名 @参数名 数据类型[=默认值] [,...n] [WITH ENCRYPTION] [WITH RECOMPILE] AS SQL语句 2．执行存储过程 在执行存储过程的语句中，有两种方式来传递参数值，分别是使用参数名传递参数值和按参数位置传递参数值。 使用参数名传递参数值，是通过语句“@参数名=参数值”给参数传递值。当存储过程含有多个输入参数时，参数值可以按任意顺序制定，对于允许空值和具有默认值的输入参数可以不给出参数的传递值。 执行使用参数名传递参数值的存储过程的语法格式如下。 EXECUTE 存储过程名 [@参数名=参数值] [,...n] 9.3.2 带输出参数的存储过程 如果我们需要从存储过程中返回一个或多个值，可以通过在创建存储过程的语句中定义输出参数来实现，为了使用输出参数，需要在CREATE PROCEDURE语句中指定OUTPUT关键字。 声明带输出参数的存储过程的语法格式如下： CREATE PROCEDURE 存储过程名 @参数名 数据类型 [VARYING][=默认值] OUTPUT [,...n] [WITH ENCRYPTION] [WITH RECOMPILE] AS SQL语句 例9-6 创建存储过程disptab3，它是在例9-4的基础上，提供一个游标输出参数，将查询的结果以结果集的方式返回。 create procedure disptab3 @selectkey varchar(6)=客户, @columnkey