数据库应用技术——SQLServer篇课件作者徐守祥第09章节.pptVIP

第 9 章 存 储 过 程 9.1 存储过程的概念 9.2 建立和执行存储过程 9.3 存储过程中参数的使用 9.4 存储过程的管理与维护 9.5 系统存储过程和扩展存储过程 通过本章的学习，读者应该掌握以下内容： ? 存储过程的作用 ? 熟练创建、修改、删除存储过程 ? 在实际应用开发时能够灵活运用存储过程以提高开发效率 9.1 存储过程的概念 存储过程按返回的数据类型，可分为两类：一类类似于SELECT语句，用于查询数据，查询到的数据以结果集的形式给出；另一类存储过程是通过输出参数返回信息，或不返回信息只执行一个动作。存储过程可以嵌套，即一个存储过程的内部可以调用另一个存储过程。 9.1.1 基本概念 存储过程是一组编译在单个执行计划中的Transact-SQL语句，它将一些固定的操作集中起来交给SQL Server数据库服务器完成，以实现某个任务。 9.1.2 存储过程的优点 （1）通过本地存储、代码预编译和缓存技术实现高性能的数据操作。 （2）通过通用编程结构和过程重用实现编程框架。 （3）通过隔离和加密的方法提高了数据库的安全性。 9.2 建立和执行存储过程 9.2.1 创建和执行简单存储过程 创建存储过程的基本语法如下。 CREATE PROCEDURE 存储过程名 [WITH ENCRYPTION] [WITH RECOMPILE] AS SQL语句 执行存储过程的基本语法如下。 EXEC[UTE] 存储过程名 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.3.3 通过RETURN返回参数 用户可以通过RETUEN语句返回状态值，RETURN语句只能返回整数，在存储过程中RETURN不能返回空值，默认返回值是0。也可以利用它返回整数输出参数值。 9.4 存储过程的管理与维护 9.4.1 查看存储过程的定义信息 在企业管理器下，可以在“存储过程属性”窗口中，对存储过程进行维护，参见9.2节中启动“存储过程属性”窗口的方法。在这个窗口中，可以查看存储过程的定义信息、修改存储过程的定义，给用户指定使用存储过程的权限。 在查询分析器下，可以通过系统存储过程sp_helptext查看存储过程的定义；通过sp_help查看存储过程的参数；通过sp_depends查看存储过程的相关性。 9.4.2 存储过程的重编译 1．在创建存储过程时设定 2．在执行存储过程时设定 3．通过系统存储过程设定 9.4.3 重新命名存储过程 其语法格式如下。 sp_rename 存储过程原名 存储过程新名 9.4.4 修改和删除存储过程 1．修改存储过程 存储过程的修改是由ALTER语句来完成的，基本语法如下。 ALTER PROCEDURE 存储过程名 [WITH ENCRY