SQL Server 2005数据库实用教程 高职应用技术专业 屈武江 第7章 使用存储过程操纵数据库.ppt

尚辅网 第7章 使用存储过程操纵数据库 1、存储过程概述 2、存储过程的类型 3、创建存储过程的规则 4、存储过程管理 5、存储过程的错误处理 第7章 使用存储过程操纵数据库 7.1 存储过程概述 7.2 创建存储过程 7.3 管理存储过程 7.1.1 存储过程的基本概念 存储过程(Stored Procedure) 是一组为了完成特定功能的T-SQL语句集，经编译后存储在SQL Server服务器端数据库中。 应用程序与SQL Server数据库交互执行某些操作有两种方法： （1）将程序存储在本地，应用程序向SQL Server发送命令，并对返回的数据进行处理； （2）将程序以存储过程的形式存储在SQL Server服务器中，并创建执行存储过程并处理结果的应用程序。 SQL Server推荐使用第二种方法，即使用调用存储过程的方法而不是在客户计算机上调用SQL编写的一段程序，这是因为存储过程具备了如下的优点： （1）存储过程允许模块化程序设计，存储过程一旦创建，以后即可在程序中调用任意多次，这可以改进应用程序的可维护性，并允许应用程序统一访问数据库。 （2）存储过程已在服务器上注册，这样可以提高T-SQL语句的执行效率。 （3）存储过程具有安全特性和所有权链接，可以执行所有的权限管理。用户可以被授予执行存储过程的权限而不必拥有直接对存储过程所引用对象的执行权限。 （4）存储过程可以提高应用程序的安全性，可以防止SQL嵌入式攻击。 （5）存储过程是一组命名代码，允许延迟绑定，可以在存储过程中引用当前不存在的对象，但是，这些对象在存储过程执行时应该存在。 （6）存储过程可以减少网络通信流量。一个需要数百行T-SQL代码的操作可以通过一条执行过程代码的语句来执行，而不需要在网络中发送数百行代码，这样就大大的降低了网络的负担。 7.1.2 存储过程的类型 在SQL Server 2005系统中提供了3种基本类型的存储过程，包括：用户自定义存储过程、系统存储过程和扩展存储过程。 1．用户自定义存储过程 用户自定义存储过程是主要的存储过程类型，是由用户创建并能完成某一特定功能的存储过程。用户自定义的存储过程可以接受输入参数；向客户端返回标量结果、集合或消息；可以调用数据定义语言（DDL）和数据操纵语言（DML）,以及返回输出参数。 在Microsoft SQL Server 2005系统中，用户自定义的存储过程有两种类型：T-SQL存储过程和CLR存储过程。 （1）T-SQL存储过程是指保存T-SQL语句的集合，可以接受和返回用户提供的参数。 （2）CLR存储过程是指对.NET Framework公共语言运行时（CLR）方法的应用，可以接受和返回用户提供的参数。 7.2 创建存储过程 7.2.1 创建存储过程的规则 在设计和创建存储过程时，应该满足一定的约束和规则，只有满足了这些约束和规则，才可以创建有效的存储过程。存储过程的设计规则包括以下内容： （1）在创建存储过程语句CREATE PROCEDURE中可以包括任意数量和类型的SQL语句，但某些特殊的语句是不能包含在存储过程定义中的，其中包括： CREATE AGGREGATE CREATE DEFAULT CREATE FUNCTION CREATE PROCEDURE CREATE RULE CREATE SCHEMA CREATE TRIGGER CREATE VIEW SET PARSEONLY SET SHOWPLAN_TEXT SET SHOWPLAN_ALL SET SHOWPLAN_XML USE database_name 除了上面列出的语句外，其他的数据库对象都可以在存储过程中创建，只要在引用前该对象已经被创建即可。 （2）可以在存储过程中引用临时表。如果在存储过程内创建本地临时表，则临时表仅为该存储过程而存在，退出该存储过程后，临时表将消失。 （3）如果执行的存储过程将调用另一个存储过程，则被调用的存储过程可以访问由第一个存储过程创建的所有对象，包括临时表在内。 （4）存储过程中的参数的最大数目为2100，存储过程中的局部变量的最大数目仅受可用内存的限制，根据可用内存的不同，存储过程最大可达128MB。 以上是创建存储过程必须遵守的规则，除此之外，在创建存储过程时，还需要确定存储过程的以下3个组成部分： （1）所有的输入参数以及传给调用者的输出参数。 （2）数据库的操作语句，包括调用其他存储过程的语句。 （3）返回给调用者的状态值，以指明调用是否成功。 在Microsoft SQL Server 2005系统中，创建存储过程有两种方法：一是使用SQL Server Management Studio工具；二是使用