第11章 管理存储过程

1第十一章管理存储过程了解存储过程的基本概念掌握使用企业管理器创建和管理存储过程掌握使用T-SQL语句创建和管理存储过程2在大型数据库系统中，存储过程和触发器具有很重要的作用。无论是存储过程还是触发器，都是SQL语句和流程控制语句的集合。就本质而言，触发器也是一种存储过程。存储过程在运算时生成执行方式，所以以后对其再运行时其执行速度很快。SQLServer2000不仅提供了用户自定义存储过程的功能，而且也提供了许多可作为工具使用的系统存储过程。3存储过程的概念存储过程（StoredProcedure）是一组为了完成特定功能的SQL语句集，经编译后存储在数据库中。用户通过指定存储过程的名字并给出参数（如果该存储过程带有参数）来执行它。存储过程可由应用程序通过一个调用来执行，而且充许用户声明变量。同时，存储过程可以接收和输出参数、返回执行存储过程的状态值，也可以嵌套调用。4存储过程的概念注意：存储过程与函数不同，因为存储过程并不返回取代其名称的值，也不能直接在表达式中使用。存储过程虽然既有参数又有返回值但是它与函数不同：存储过程的返回值只是指明执行是否成功，并且它不能像函数那样被直接调用，也就是在调用存储过程时，在存储过程名字前一定要有EXEC保留字。存储过程属于服务器方软件，可立即访问数据库5存储过程的分类在SQLServer中存储过程分为两类：系统提供的存储过程和用户自定义存储过程。系统过程主要存储在master数据库中，并以sp_为前缀，并且系统存储过程主要是从系统表中获取信息，从而为系统管理员管理SQLServer提供支持。通过系统存储过程，SQLServer中的许多管理性或信息性的活动：如了解数据库对象、数据库信息都可以被顺利有效地完成。尽管这些系统存储过程被放在master数据库中，但是仍可以在其它数据库中对其进行调用，在调用时不必在存储过程名前加上数据库名，而且当创建一个新数据库时，一些系统存储过程会在新数据库中被自动创建。用户自定义存储过程是由用户创建并能完成某一特定功能（如查询用户所需数据信息）的存储过程。6存储过程的运行过程存储过程时存放在SQLServer中的特别快的数据库对象，当首次运行存储过程时，它按以下方式进行：1、该过程被划分成部件片断。2、检查引用数据库中其它对象（表、视图等）的部件，确保引用的对象是存在的，这也被称为分解。3、一旦分解完成，该过程的名字将存放倒sysobjects表中，而创建存储过程的代码存放在syscomments表中4、然后编译，并且，编译过程中将创建如何运行查询的蓝本。该蓝本通称称为常规计划或查询树，查询树存放在sysProcedures表中。5、存储过程首次运行时，读出查询计划并完全编译成过程计划，然后运行。这样，节约了每次运行存成过程的语法检查、分解和编译查询树的时间。7存储过程的优点当利用SQLServer创建一个应用程序时，T-SQL是一种主要的编程语言。若运用T-SQL来进行编程有两种方法：其一是在本地存储T-SQL程序并创建应用程序，向SQLServer发送命令来对结果进行处理。其二是可以把部分用T-SQL编写的程序作为存储过程存储在SQLServer中，并创建应用程序来调用存储过程，对数据结果进行处理。存储过程能够通过接收参数向调用者返回结果集，结果集的格式由调用者确定；返回状态值给调用者，指明调用是成功或是失败；包括针对数据库的操作语句，并且可以在一个存储过程中调用另一存储过程。我们通常更偏爱于使用第二种方法，即在SQLServer中使用存储过程，而不是在客户计算机上调用T-SQL编写的一段程序原因在于存储过程具有以下优点：8存储过程的优点可用存储过程封装事务规则。一旦封装完成，这些规则就可用于多个应用，从而有一个一致的数据接口，因此，若需改变过程的功能，只需在一个地方对其进行修改，而不必对每个应用都进行修改。9存储过程的优点存储过程允许标准组件式编程：存储过程在被创建以后，可以在程序中被多次调用而不必重新编写该存储过程的SQL语句；而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响，因为应用程序源代码只包含存储过程的调用语句，从而极大地提高了程序的可移植性。10存储过程的优点存储过程能够实现较快的执行速度：如果某一操作包含大量的T-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析优化，并给出最终被存在系统表中的执行计划；而批处理的T-SQL语句在每次运行时都要进行编译和优化，因此速度相对要慢一些。11存储过程的优点存储过程能够减少网络流量：对于同一个针对数据数据库对象的操作（如查询修改），如果这一操作所涉及到的T-SQL语句被组织成一存储过程，那么当在客户计算机上调用该存储过程时，网络中传送的只是该调用语句；否则将是多条SQL语句，从而大大增加了网络流量，降低网络负载。12存储过程的优点存储过程可被作为一种安全机制来充分利用：系统管理员通过对执行某一存储过程的权限进行限制，从而能够实现对相应的数据访问权限的限制，避免非授权用户对数据的访问，保证数据的安全。13创建存储过程的规则几乎任何可以写成批处理的T-SQL代码都可用于创建存储过程，但是在设计存储过程时，需要遵循下列规则：名字必须符合SQLServer命名规则。引用的对象必须在创建存储过程前就存在不能在单个存储过程中创建后去掉或再创建同名的对象。存储过程最后能有255个参数。14创建存储过程的规则在自己的存储过程中可以引用临时表，局部临时表再过程结束时将会消失。在存储过程中不能有如下的SQL创建语句：CreateDefault、CreateProcedure、CreateRule、CreateTrigger、CreateView。可在过程中嵌套过程。创建存储过程的文本不能超过64K字节，因为SQL存放再syscomments表中。若在存储过程中使用了Select*，而底层表中加入了新的列，新的列再过程运行时无法显示。15创建存储过程在MSSQLServer2000中创建一个存储过程有两种方法：一种是使用Transaction-SQL命令CreateProcedure，另一种是EnterpriseManager。用Transaction-SQL创建存储过程是一种较为快速的方法，但对于初学者使用EnterpriseManager更易理解，更为简单。当创建存储过程时需要确定存储过程的三个组成部分：所有的输入参数以及传给调用者的输出参数被执行的针对数据库的操作语句包括调用其它存储过程的语句返回给调用者的状态值以指明调用是成功还是失败16使用企业管理器管理存储过程创建存储过程查看和修改存储过程删除存储过程17使用创建存储过程向导创建使用创建存储过程向导创建存储过程的步骤如下：1、打开企业管理器，在树状目录中展开服务器节点。2、在工具栏中选择命令“工具－向导”，打开“选择向导”对话框。3、在“选择向导”对话框中选择节点“数据库－创建存储过程向导”，按“确定”按钮。4、在对话框“欢迎使用创建存储过程向导”中单击“下一步”18使用创建存储过程向导创建5、在对话框“选择数据库”中选择存放存储过程的数据库名，按“下一步”按钮。6、在对话框“选择存储过程”中选择存储过程将针对哪些数据表做哪些操作。单击“下一步”按钮。7、在对话框“正在完成创建存储过程向导”中，可以看到正在创建的存储过程的名称和描述。8、若需要对某个存储过程进行设置，可以在该对话框内选定该存储过程，然后按“编辑”按钮，打开“编辑存储过程属性”对话框，在该对话框内可以完成对该存储过程的设置。9、逐一完成对每个存储过程的设置以后，返回到对话框“正在完成创建存储过程向导”，按“完成按钮”。即可结束。19图1欢迎使用创建存储过程向导对话框20图2选择数据库对话框21图3选择数据库对象对话框22图4完成创建存储过程向导对话框23图5编辑存储过程属性对话框24图6编辑存储过程SQL对话框25使用企业管理器创建存储过程在企业管理器中创建一个存储过程步骤如下：1启动企业管理器，登录到要使用的服务器2选择要创建存储过程的数据库，在左窗格中单击存储过程文件夹，此时在右窗格中显示该数据库的所有存储过程。3右击存储过程文件夹，在弹出菜单中选择“新建存储过程”命令4在“新建存储过程属性”对话框中指定存储过程的名字。在文本框中输入创建存储过程的语句。5单击“检查语法”按钮，检查语法是否正确6单击“确定”保存存储过程，并关闭属性对话框。26图7选择新建存储过程对话框（1）27图8选择新建存储过程对话框（2）28图9新建存储过程对话框29图10设置权限对话框30查看和修改存储过程查看存储过程：存储过程被创建之后，它的名字就存储在系统表sysobjects中，它的源代码存放在系统表syscomments中。可以使用使用企业管理器或系统存储过程来查看用户创建的存储过程。31查看和修改存储过程在企业管理器中查看和修改存储过程及其定义文本，步骤如下：1打开企业管理器，在树状目录中展开存储过程所在的数据库节点。2单击“存储过程”节点，在右窗格中显示该数据库的所有存储过程。选择希望查看或修改的存储过程，并单击鼠标右键，在弹出菜单中选择命令“属性”3在“属性”对话框中可以查看存储过程的名称、所有者、创建日期和存储过程的定义文本。32查看和修改存储过程在“属性”对话框中还可以对该存储过程做如下的修改：修改存储过程定义文本。修改存储过程的权限：单击“权限”按钮，对存储过程的权限进行修改。4、单击“确定”按钮，并关闭属性对话框。33查看存储过程的相关性如果希望查看存储过程的相关性，可以按如下步骤：1、打开企业管理器，在树状目录中展开存储过程所在的数据库节点。2、单击“存储过程”节点，在右边的内容窗口中选择希望查看相关性的存储过程，并单击鼠标右键，在弹出菜单中选择命令“所有任务－显示相关性”34重命名存储过程可以按如下步骤：1、打开企业管理器，在树状目录中展开存储过程所在的数据库节点。2、单击“存储过程”节点，在右边的内容窗口中选择希望重命名的存储过程，并单击鼠标右键，在弹出菜单中选择命令“重命名”3、输入存储过程的新名称，并按回车键。4、确认新名称。35删除存储过程对于不再需要的存储过程，可以使用企业管理器将其删除，步骤如下：1、打开企业管理器，在树状目录中展开存储过程所在的数据库节点。2、单击“存储过程”节点，在右边的内容窗口中选择希望删除的存储过程，并单击鼠标右键，在弹出菜单中选择命令“删除”3、单击“全部除去”按钮，删除选定的存储过程。36使用T-SQL管理存储过程创建存储过程执行存储过程查看存储过程修改存储过程删除存储过程创建一组存储过程在存储过程中使用参数WithRecompile选项37使用T-SQL创建存储过程创建存储过程时，需要确定存储过程的三个组成部分：1、所有的输入参数以及传给调用者的输出参数。2、被执行的针对数据库的操作语句，包括调用其它存储过程的语句。3、返回给调用者的状态值，以指明调用是成功还是失败。38使用T-SQL创建存储过程使用CREATEPROCEDURE创建存储过程语法如下：CREATEPROC[EDURE]procedure_name[;number][{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n][WITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}][FORREPLICATION]ASsql_statement[...n]其中，各参数的含义如下所示：39使用T-SQL创建存储过程procedure_name：用于指定要创建的存储过程的名称。number：该参数是可选的整数，它用来对同名的存储过程分组，以便用一条DROPPROCEDURE语句即可将同组的过程一起除去。@parameter：过程中的参数。在CREATEPROCEDURE语句中可