12第12章存储过程和用户存储过程设计.pptVIP

12.1 存储过程概述 12.1.1 存储过程的概念和分类 SQL Server提供了一种方法，它可以将一些固定的操作集中起来由SQL Server数据库服务器来完成，以实现某个任务，这种方法就是存储过程。 存储过程（Stored Procedure）是一组为了完成特定功能的SQL 语句集，经编译后存储在数据库中。 用户或应用程序通过指定存储过程的名字并给出参数来执行它，而且允许用户声明变量、有条件执行以及其它强大的编程功能。 在SQL Server中存储过程分为两类：即系统提供的存储过程和用户自定义的存储过程。 12.1.2 存储过程的优点 （1）存储过程允许标准组件式编程 存储过程在被创建以后可以在程序中被多次调用，而不必重新编写该存储过程的SQL 语句。而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响（因为应用程序源代码只包含存储过程的调用语句），从而极大地提高了程序的可移植性。 （2）存储过程能够实现较快的执行速度 如果某一操作包含大量的Transaction-SQL 代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。 12.1.2 存储过程的优点 （3）存储过程能够减少网络流量 对于同一个针对数据数据库对象的操作（如查询、修改），当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句，从而大大增加了网络流量，降低网络负载。 （4）存储过程可被作为一种安全机制来充分利用系统管理员通过对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。 （5）自动完成需要预先执行的任务.存储过程可以在系统启动时自动执行,而不必在系统启动后再进行手工操作,大大方便了用户的使用,可以自动完成一些需要预先执行的任务。 12.2 系统存储过程 12.2.1 系统存储过程分类 系统存储过程就是系统创建的存储过程，目的在于能够方便地从系统表中查询信息或完成与更新数据库表相关的管理任务或其它的系统管理任务。 系统过程以“sp_开头,在Master数据库中创建并保存在该数据库中，为数据库管理者所有。一些系统过程只能由系统管理员使用，而有些系统过程通过授权可以被其它用户所使用。 12.2.2 一些常用的系统存储过程 12.2 系统存储过程 例如，sp_helpdb系统存储过程，其功能是：报告有关指定数据库或所有数据库的信息。 语法： sp_helpdb [ [ @dbname= ] ‘数据库名’ ] 【例】返回有关所有数据库的信息 exec sp_helpdb 12.3.1 创建用户存储过程 用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。在SQL Server中，可以使用两种方法创建存储过程： ① 利用SQL Server对象资源管理器创建存储过程。 ② 使用Transact-SQL语句中的CREATE PROCEDURE命令创建存储过程。 创建存储过程时，需要确定存储过程的三个组成部分： ① 所有的输入参数以及传给调用者的输出参数。 ② 被执行的针对数据库的操作语句，包括调用其它存储过程的语句。 ③ 返回给调用者的状态值，以指明调用是成功还是失败。 1．使用SQL Server对象资源管理器创建存储过程 在SQL Server对象资源管理器中，选择指定的服务器和数据库，在可编程性里选择存储过程，用右键单击，在弹出的快捷菜单中选择“新建存储过程”选项，如图12-1所示。 2．使用Transact-SQL语句命令创建存储过程 创建存储过程前，应该考虑下列几个事项： ① 在一个批处理中，Create Procedure 语句不能与其它SQL语句合并在一起； ② 创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。 ③ 存储过程是数据库对象，其名称必须遵守标识符规则。 ④ 只能在当前数据库中创建当前数据库的存储过程。 ⑤ 一个存储过程的最大尺寸为128M。 语法格式： CREATE PROCEDURE 存储过程名 [ ({ @参数名 数据类型 } [VARYING] [=default] [OUTPUT] [，…])] [WITH RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION] [FOR REPLICATION] AS SQL语句 生疏参数说明： VARY