SQL Server数据库基础教程 刘丽 第7章新.ppt

第7章 存储过程 主要内容： 总体要求： 了解存储过程的概念、功能和优点 熟练掌握存储过程的创建方法 熟练掌握存储过程的执行方法 掌握查看和修改存储过程的方法 掌握重命名和删除存储过程的方法 7.1 存储过程简介 在大型的数据库系统中，随着功能的不断完善，系统也变得越来越复杂，大量的时间将耗费在SQL代码和应用程序代码的编写上。在多数情况下，许多代码被重复使用多次，而每次都输入相同的代码既繁琐又会降低系统运行效率。因此， SQL Server提供了一种方法，它可以将一些固定的操作集合起来，由SQL Server数据库服务器来完成，实现某个特定任务，这种方法就是存储过程。 7.1.1 存储过程的概念 存储过程（Stored Procedure）是一组完成特定功能的Transact-SQL语句集，经编译后存储在SQL Server服务器中，并用SQL Server服务器通过过程名调用。 存储过程的功能 包含用于在数据库中执行操作的编程语言。 接受输入参数并以输出参数的格式向调用过程或批处理返回一个或多个值。 向调用过程或批处理返回状态值，以指明成功或失败。 存储过程与Transact-SQL程序相比，有如下好处： 允许模块化程序设计 。 允许更快执行 。 减少网络流量 。 可作为安全机制使用 。 7.1.2 存储过程的优点 7.1.3 存储过程的分类 7.2 创建存储过程 几乎所有可以写成批处理的Transact-SQL代码都可以用来创建存储过程。 创建存储过程时应注意如下事项： 可以在存储过程内引用临时表。 如果执行的存储过程将调用另一个存储过程，则被调用的存储过程可以访问由第一个存储过程创建的所有对象，包括临时表在内。 如果执行对远程SQL Server 2005实例进行更改的远程存储过程，则不能回滚这些更改。远程存储过程不参与事务处理。 存储过程中参数的最大数目为2100个。 存储过程中局部变量的最大数目仅受可用内存的限制。 根据可用内存的不同，存储过程最大可达128MB。 存储过程只能在当前数据库中创建。 7.2.1使用对象资源管理器创建存储过程 【例7-1】 使用对象资源管理器在“Teaching”数据库中创建查询所有课程信息的存储过程up_AllKc。 7.2.2使用Transact-SQL语句创建存储过程 使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。创建存储过程前，应该注意下列几个事项： 不能将CREATE PROCEDURE语句与其他SQL语句组合到单个批处理中。 创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。 存储过程是数据库对象，其名称必须遵守标识符规则。 只能在当前数据库中创建存储过程。 创建存储过程的Transact-SQL语句是CREATE PROCEDURE, 它的语法形式如下： CREATE PROCEDURE procedure_name [;Number] [{@parameter_name data_type} [ = default ] [ OUTPUT ] …， ] [WITH {RECOMPLE|ENCRYTION|REMOMPLE,ENCRYTION}] [FOR REPLICATION] AS SQL_statement … 【例7-2】 使用Transact-SQL语句在“Teaching”数据库中创建返回全部男学生信息的存储过程“up_MXs” 。 USE Teaching GO CREATE PROCEDURE up_MXs AS SELECT * FROM Xs WHERE Xb= 男 GO SQL Server 2005的存储过程可以使用两种类型的参数：输入参数和输出参数。 参数用于在存储过程以及应用程序之间交换数据。 输入参数允许用户将数据值传递到存储过程或函数。 输出参数允许存储过程将数据值或游标变量传递给用户。 每个存储过程向用户返回一个整数代码，如果存储过程没有显示设置返回代码的值，则返回代码为0。 【例7-3】 使用Transact-SQL语句在Teaching数据库中创建一个名为up_getScore的存储过程，作用是通过输入的学号信息显示出该学生的所有科目成绩。 USE Teaching GO CREATE PROCEDURE up_GetScore @StuXh varchar(8) AS SELECT * FROM Cj WHERE xh= @StuXh GO 【例7-4】 使用Transact-SQL语句在Teachi