《数据库原理与应用》09.存储过程的创建和使用.pptx

孙发勤扬州大学新闻与传媒学院Y《数据库原理与应用》

第九讲存储过程的创建和使用

本讲主要内容创建存储过程执行存储过程查看和修改存储过程重命名和删除存储过程

存储过程的概念SQLServer提供了一种方法，它可以将一些固定的操作集中起来由SQLServer数据库服务器来完成，以实现某个任务，这种方法就是存储过程。类似于DOS一下的批处理。存储过程是SQL语句和可选控制流程语句的预编译集合。是一种封装重复任务操作的方法，以一个名称存储，作为一个单元处理。存储过程属于服务器方软件，可立即访问数据库01在SQLServer中存储过程分为两类：即系统提供的存储过程和用户自定义的存储过程。02

存储过程存储在数据库内，可由应用程序通过一个调用来执行，而且充许用户声明变量。同时，存储过程可以接收和输出参数、返回执行存储过程的状态值，也可以嵌套调用。注意:存储过程与函数不同，因为存储过程并不返回取代其名称的值，也不能直接在表达式中使用。

在SQLServer中存储过程分为两类:系统提供的存储过程和用户存储过程。系统过程主要存储在Master数据库中，并以SP_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQLServer提供支持。通过系统存储过程，SQLServer中的许多管理性或信息性的活动:如了解数据库对象、数据库信息都可以被顺利有效地完成。尽管这些系统存储过程被放在master数据库中，但是仍可以在其它数据库中对其进行调用，在调用时不必在存储过程名前加上数据库名，而且当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。用户自定义存储过程是由用户创建并能完成某一特定功能(如查询用户所需数据信息)的存储过程。

存储过程时存放在SQLServer中的特别快的数据库对象，当首次运行存储过程时，它按以下方式进行:1、该过程被划分成部件片断。2、检查引用数据库中其它对蒙(表、视图等)的部件，确保引用的对象是存在的，这也被称为分解。3、一旦分解完成，该过程的名字将存放倒sysobjects表中，而创建存储过程的代码存放在syscomments表中4、然后编译，并且，编译过程中将创建如何运行查询的蓝本。该蓝本通称称为常规计划或查询树，查询树存放在sysProcetlures表中。5、存储过程首次运行时，读出查询计划并完全编译成过程计划，然后运行。这样，节约了每次运行存成过程的语法检查、分解和编译查询树的时间。

当利用SQLServer创建一个应用程序时，T-SQL是一种主要的编程语言。若运用T一SQL来进行编程有两种方法:其一是在本地存储T-SQL程序并创建应用程序，向SQL-Server发送命令米对结果进行T-SQL编写的程序作为存储过程其二是可以把部分用在SQLServer中，并创建应用程序来调用存储过程，对数据结果进行处理。存储过程能够通过接收参数向调用者返回结果集，结果集的格式由调用者确定;返回状态值给调用者，指明调用是成功或是失败:包括针对数据库的操作语句，并且可以在一个存储过程中调用另一存储过程。我们通常更偏爱于使用第二种方法，即在SQLServer中使用存储过程，而不是在客户计算机上调用T一QL编写的一段程序原因在于存储过程具有以下优点:

可用存储过程封装事务规则。一旦封装完成，这些规则就可用于多个应用，从而有一个一致的数据接口，因此，只需改变过程的功能，只需在一个地方对其进行修改，而不必对每个应用都进行修改。

存储过程允许标准组件式编程:存储过程在被创建以后，可以在程序中被多次调用而不必重新编写该存储过程的SQL语句;而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响，因为应用程序源代码只包含存储过程的调用语句，从而极大地提高了程序的可移植性。

存储过程能够实现较快的执行速度:如果某一操作包含大量的T－SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析优化，并给出最终被存在系统表中的执行计划;而批处理的T-SQL语句在每次运行时都要进行编译和优化，因此速度相对要慢一些。

存储过程能够减少网络流量:对于同一个针对数据数据库对象的操作(如查询修改)，如果这一操作所涉及到的T-SQL语句被组织成一存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句;否则将是多条SQL语句，从而大大增加了网络流量，降低网络负载。

存储过程可被作为一种安全机制来充分利用:系统管理员通过对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。

创建存储过程的规则几乎任何可以写成批处理的T一SQL代码