数据库技术与应用——SQL Server 2005 作者 张建伟 第9章 存储过程.pptVIP

SQL Server 2005 第9章 存储过程 存储过程概述 在大型数据库系统中，存储过程具有很重要的作用。存储过程是SQL 语句和流程控制语句的集合。存储过程在运算时生成执行方式，所以，以后对其再运行时其执行速度很快。SQL Server 2005不仅提供了用户自定义存储过程的功能，而且也提供了许多可作为工具使用的系统存储过程。 。 存储过程的基本概念 存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中，用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。 存储过程分类 系统存储过程 SQL Server 2005 中的许多管理活动都是通过一种特殊的存储过程执行的，这种存储过程被称为系统存储过程。例如，sys.sp_changedbowner 就是一个系统存储过程。从物理意义上讲，系统存储过程存储在源数据库中，并且带有 sp_ 前缀。从逻辑意义上讲，系统存储过程出现在每个系统定义数据库和用户定义数据库的 sys 构架中。在 SQL Server 2005 中，可将 GRANT、DENY 和 REVOKE 权限应用于系统存储过程。 存储过程分类 用户定义的存储过程 存储过程是指封装了可重用代码的模块或例程。存储过程可以接受输入参数、向客户端返回表格或标量结果和消息、调用数据定义语言 (DDL) 和数据操作语言 (DML) 语句，然后返回输出参数。在 SQL Server 2005 中，存储过程有两种类型：Transact-SQL 或 CLR 存储过程分类 Transact-SQL Transact-SQL 存储过程是指保存的 Transact-SQL 语句集合，可以接受和返回用户提供的参数。例如，存储过程中可能包含根据客户端应用程序提供的信息在一个或多个表中插入新行所需的语句。存储过程也可能从数据库向客户端应用程序返回数据。例如，电子商务 Web 应用程序可能使用存储过程根据联机用户指定的搜索条件返回有关特定产品的信息。 CLR CLR 存储过程是指对 Microsoft .NET Framework 公共语言运行时 (CLR) 方法的引用，可以接受和返回用户提供的参数。它们在 .NET Framework 程序集中是作为类的公共静态方法实现的。 存储过程分类 扩展存储过程 扩展存储过程允许您使用编程语言（例如 C）创建自己的外部例程。扩展存储过程是指 Microsoft SQL Server 的实例可以动态加载和运行的 DLL。扩展存储过程直接在 SQL Server 的实例的地址空间中运行，可以使用 SQL Server 扩展存储过程 API 完成编程。 存储过程的优点 与其他应用程序共享应用程序逻辑，因而确保了数据访问和修改的一致性。存储过程可以封装业务功能，在存储过程中可以在同一位置改变封装的业务规则和策略，所有的客户端可以使用相同的存储过程来确保数据访问和修改的一致性 防止把数据库中表的细节暴露给用户。如果一组存储过程支持用户需要执行的所有业务功能，用户就不必直接访问表 提供了安全机制。即使是没有访问存储过程引用的表或视图的权限的用户，也可以被授权执行该存储过程 存储过程的优点 改进性能。如果某一操作包含大量的Transaction-SQL 代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的执行计划。而批处理的Transaction- SQL 语句在每次运行时都要进行编译和优化，因此速度相对要慢一些。 减少网络流量。用户可以通过发送一个单独的语句实现一个复杂的操作，而不需要在网络上发送几百个Transact-SQL代码，这样减少了在服务器和客户机之间传递的请求的数量 创建存储过程 在图形界面下创建存储过程 打开Microsoft SQL Server Manager Studio，并连接数据库。 在对象资源管理器中，依次展开数据库|Northwind|可编程性，选中存储过程点击鼠标右键，选择新建存储过程 系统将在查询编辑器中打开存储过程模版。在模版中输入存储过程的名称，设置相应的参数。也可以通过菜单“查询”|“指定模版参数的值”进行设置 指定模版参数的值窗口的前三行分别是创建人、创建时间、描述，是对存储过程进行注释。从第四行开始，分别指定存储过程名称、参数名称、数据类型、参数的缺省值。 删除掉参数@p2,并编写相应的SQL语句。SQL语句如下： BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfer