SQL Server 2005实用教程李伟红电子教案 第9章 存储过程的创建和使用.pptVIP

第9章 存储过程的创建和使用 本章学习目标 了解存储过程的概念 了解使用存储过程的优点 了解系统存储过程的特点及用途 掌握创建存储过程的方法 掌握执行存储过程的方法 掌握查看和修改存储过程的方法 掌握删除存储过程的方法 9.1 概述 9.1.1 存储过程的概念 在使用Transact-SQL语言编程的过程中，可以将某些需要多次调用的实现某个特定任务的代码段编写成一个过程，将其保存在数据库中，并由SQL Server服务器通过过程名来调用它们，这些过程就叫做存储过程。 存储过程在创建时就被编译和优化，调用一次以后，相关信息就保存在内存中，下次调用时可以直接执行。 9.1.1 存储过程的概念 存储过程有以下特点： 存储过程中可以包含一条或多条Transact-SQL语句。 存储过程可以接受输入参数并可以返回输出值。 在一个存储过程中可以调用另一个存储过程。 存储过程可以返回执行情况的状态代码给调用它的程序。 9.1.2 存储过程的优点 使用存储过程有很多优点，具体如下： 实现了模块化编程，一个存储过程可以被多个用户共享和重用。 存储过程具有对数据库立即访问的功能。 使用存储过程可以加快程序的运行速度。 使用存储过程可以减少网络流量。。 使用存储过程可以提高数据库的安全性。 9.1.3 存储过程的分类 在SQL Server中的存储过程分为两类：即系统提供的存储过程和用户自定义的存储过程。 系统存储过程：由系统自动创建，系统存储过程出现在每个系统定义数据库和用户定义数据库的sys构架中。在SQL Server 2005中，可将GRANT、DENY和REVOKE权限应用于系统存储过程。 用户自定义存储过程：是指封装了可重用代码的模块或例程，由用户创建，能完成某一特定的功能。可以接受输入参数，返回输出参数。 9.1.3 存储过程的分类 在SQL Server 2005中，用户自定义存储过程有两种类型：Transact-SQL存储过程和CLR存储过程。 Transact-SQL存储过程：是指保存的Transact-SQL语句集合。 CLR存储过程：是指对Microsoft .NET Framework公共语言运行时(CLR)方法的引用，可以接受和返回用户提供的参数。 9.2 创建存储过程 在SQL Server中，可以使用两种方法创建存储过程： 当创建存储过程时，需要确定存储过程的三个组成部分： 所有的输入参数以及传给调用者的输出参数。 被执行的针对数据库的操作语句，包括调用其他存储过程的语句。 返回给调用者的状态值，以指明调用是成功还是失败。 9.2.1 使用SQL Server管理控制台创建存储过程 在SQL Server管理控制台中，选择指定的服务器和数据库，展开数据库中的“可编程性”文件夹，右击其中的“存储过程”，在弹出的快捷菜单中选择“新建存储过程…”选项。 例9-2-1：创建一个名称为“StuInfo”的存储过程，要求完成以下功能：在T_STUDENT表中查询05541班学生的学号、姓名、性别、出生日期和政治面貌五个字段的内容。 9.2.2 使用Transact-SQL语句创建存储过程 可以使用CREATE PROCEDURE命令创建存储过程，考虑下列几个事项： CREATE PROCEDURE语句不能与其他SQL语句在单个批处理中组合使用。 必须具有数据库的CREATE PROCEDURE权限。 只能在当前数据库中创建存储过程。 不要创建任何使用sp_作为前缀的存储过程。 9.2.2 使用Transact-SQL语句创建存储过程 CREATE PROCEDURE的语法形式如下： CREATE { PROC | PROCEDURE } [schema_name.] procedure_name [ { @parameter [ type_schema_name. ] data_type } [ VARYING ] [ = default ] [ OUT | OUTPUT ] ] [ ,...n ] [ WITH ENCRYPTION ] AS { sql_statement [;][ ...n ] }[;] sql_statement ::= { [ BEGIN ] statements [ END ] } 9.2.2 使用Transact-SQL语句创建存储过程 其中，各参数的意义如下： schema_name：过程所属架构的名称。 procedure_name：新存储过程的名称。 @ parameter：过程中的参数。 [ type_schema_name. ] data_type：参数以及所属架构的数据类型。 VARYING：指定作为输出参数支持的结果集。仅适用于cursor参