SQLServer实验指导NEW实验存储过程.ppt

* 实验8 存储过程 存储过程是使用SQL Server所提供的Transact-SQL 语言所编写的程序。SQL Server 2000不仅提供了用户自定义的存储过程的功能，而且也提供了许多可作为工具使用的系统存储过程。通过本实验来学习、掌握用户自定义存储过程的创建、修改、删除、执行的方法。 【知识要点】 1．存储过程的类型 SQL Server中的存储过程划分为四类： 系统存储过程 扩展存储过程 用户自定义存储过程 临时性存储过程 常用的存储过程为两类：系统提供的存储过程和用户自定义存储过程。系统提供的存储过程是系统自动创建的，主要存储在master数据库中并以sp_为前缀。功能主要是从系统表中获取信息，从而为系统管理员管理SQL Server提供支持。用户自定义存储过程是由用户创建并完成某一特定功能的存储过程，存储在所属的数据库中。 实验8 存储过程 2．存储过程的特点 使用 SQL Server 中的存储过程而不使用存储在客户计算机本地的 T-SQL 程序的原因主要是存储过程具有以下特点： （1）允许模块化程序设计 存储过程只需创建一次便可作为数据库中的对象之一存储在数据库中，以后各用户即可在程序中调用该过程任意次。 （2）执行速度更快 存储过程只在第一次执行时需要编译且被存储在存储器内，其它次执行就可以不必由数据引擎逐一再翻译，从而提高了执行速度。 （3）减少网络流量 一个需要数百行 Transact-SQL 代码的操作由一条执行过程代码的单独语句就可实现， 而不需要在网络中发送数百行代码。 （4）可作为安全机制使用 对于没有直接执行存储过程中某个（些）语句权限的用户，也可授予他们执行该存储过程的权限。 （5）减轻操作人员和程序设计者的劳动强度 用户可以通过执行现有的存储过程，并提供存储过程所需的参数就可以得到他要的结果而不用接触SQL命令。 实验8 存储过程 3．SQL Server 2000 应用程序 在使用 SQL Server 2000 创建应用程序时，Transact-SQL 编程语言是应用程序和 SQL Server 数据库之间的主要编程接口。使用 Transact-SQL 程序时，可用两种方法存储和执行程序： （1）在本地（客户端）创建并存储程序，把此程序发送给 SQL Server 执行； （2）在SQL Server 中创建存储过程，并将其存储在SQL Server 中；然后SQL SERVER 或客户端调用执行此存储过程。 4．存储过程的功能 SQL Server 中的存储过程与其它编程语言中的过程类似： （1）可以以输入参数的形式引用存储过程以外的参数 （2）可以以输出参数的形式将多个值返回给调用它的过程或批处理。 （3）存储过程中包含有执行数据库操作的编程语句，也可调用其它存储过程。 （4）用RETURN向调用过程或批处理返回状态值，以表明成功或失败，以及失败原因。 实验8 存储过程 5．创建存储过程的SQL语句 CREATE PROCEDURE [拥有者.][存储过程名][；程序编号] [{ @参数名 数据类型 }?[ VARYING ][ = 默认值][ OUTPUT ]] [，… n ] [WITH {RECOMPILE | ENCRYPTION | RECOMPILE，ENCRYPTION} ] AS 程序行 6．执行存储过程的SQL语句 [ [ EXEC[UTE ] ][ @返回值 = ]{ 程序名[；程序编号] | @存储程序名的变量 }[ [ @参数名= ]{ 参数值 } | @变量[OUTPUT ] | [DEFAULT ] ] [,…n ] [ WITH RECOMPILE ] 7．存储过程的权限 （1）执行Create Procedure语句，必须是系统管理员、数据库拥有者或数据定义语言管理员角色中的一个成员，或被授予Create Procedure 权限。 （2）创建过程者一定拥有过程中所引用的所有对象的权限：UPDATE、INSERT、DELETE、SELECT （3）若过程拥有者把执行此过程的权限授予某用户，那么此用户就拥有此过程的执行权限。 实验8 存储过程 【实验目的】 通过实验掌握存储过程的概念、功能； 掌握用户自定义存储过程的创建、修改、删除和执行。 实验8.1 创建并执行存储过程 【实验目的】 掌握用