管理存储过程Word格式.docx

管理存储过程Word格式.docx

《管理存储过程Word格式.docx》由会员分享，可在线阅读，更多相关《管理存储过程Word格式.docx（16页珍藏版）》请在冰点文库上搜索。

在本章中所涉及到的存储过程主要是指用户自定义存储过程。

12.1.2存储过程的优点

当利用MSSQLServer创建一个应用程序时，Transaction-SQL是一种主要的编程语言。

若运用Transaction-SQL来进行编程，有两种方法。

其一是，在本地存储Transaction-SQL程序，并创建应用程序向SQLServer发送命令来对结果进行处理。

其二是，可以把部分用Transaction-SQL编写的程序作为存储过程存储在SQLServer中，并创建应用程序来调用存储过程，对数据结果进行处理存储过程能够通过接收参数向调用者返回结果集，结果集的格式由调用者确定；

返回状态值给调用者，指明调用是成功或是失败；

包括针对数据库的操作语句，并且可以在一个存储过程中调用另一存储过程。

我们通常更偏爱于使用第二种方法，即在SQLServer中使用存储过程而不是在客户计算机上调用Transaction-SQL编写的一段程序，原因在于存储过程具有以下优点：

（1）存储过程允许标准组件式编程

存储过程在被创建以后可以在程序中被多次调用，而不必重新编写该存储过程的SQL语句。

而且数据库专业人员可随时对存储过程进行修改，但对应用程序源代码毫无影响（因为应用程序源代码只包含存储过程的调用语句），从而极大地提高了程序的可移植性。

（2）存储过程能够实现较快的执行速度

如果某一操作包含大量的Transaction-SQL代码或分别被多次执行，那么存储过程要比批处理的执行速度快很多。

因为存储过程是预编译的，在首次运行一个存储过程时，查询优化器对其进行分析、优化，并给出最终被存在系统表中的执行计划。

而批处理的Transaction-SQL语句在每次运行时都要进行编译和优化，因此速度相对要慢一些。

（3）存储过程能够减少网络流量

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

（4）存储过程可被作为一种安全机制来充分利用

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

（我们将在14章“SQLServer的用户和安全性管理”中对存储过程的这一应用作更为清晰的介绍）

注意：

存储过程虽然既有参数又有返回值，但是它与函数不同。

存储过程的返回值只是指明执行是否成功，并且它不能像函数那样被直接调用，也就是在调用存储过程时，在存储过程名字前一定要有EXEC保留字（如何执行存储过程见本章下一字）。

12.3.1查看存储过程

存储过程被创建以后，它的名字存储在系统表sysobjects中；

它的源代码存放在系统表syscomments中。

可以通过MSSQLServer提供的系统存储过程来查看关于用户创建的存储过程信息。

1通过EnterpriseManager管理工具同样可以查看存储过程的源代码

其操作如下：

（1）启动EnterpriseManager，登录到要使用的服务器。

（2）选择要创建存储过程的数据库，在左窗格中单击StoredProcedure文件夹，此时在右窗格中显示该数据库的所有存储过程。

（3）在右窗格中，右击要查看源代码的存储过程，在弹出的菜单中选择Properties选项，此时便可看到存储过程的源代码。

如图12-4所示：

2使用sp_helptext存储过程查看存储过程的源代码

其语法格式如下：

sp_helptext存储过程名称

例如要查看数据库pubs是否是存储过程reptq1的源代码，则执行sp_helptextreptq1。

如果在创建存储过程时使用了WITHENCRYPTION选项，那么无论是使用EnterpriseManager还是系统存储过程sp_helptext都无法查看到存储过程的源代码。

12.3.2重新命名存储过程

修改存储过程的名字使用系统存储过程sp_rename。

其命令格式为：

sp_rename原存储过程名,新存储过程名

例12-4：

将存储过程reptq1修改为newproc其语句为：

sp_renamereptq1,newproc

另外，通过EnterpriseManager也可修改存储过程的名字，其操作过程与WINDOWS下修改文件名字的操作类似。

即首先选中需修改名字的存储过程，然后右击鼠标，在弹出菜单中选取rename选项，最后输入新存储过程的名字。

12.3.3删除存储过程

删除存储过程使用drop命令，drop命令可将一个或多个存储过程或者存储过程组从

当前数据库中删除。

其语法规则为：

DROPPROCEDURE{procedure}}[,…n]

例12-5：

如将存储过程reptq1从数据库中删除，则执行：

dropprocedurereptq1

12.3.4执行存储过程

执行已创建的存储过程使用EXECUTE命令，其语法如下：

[EXECUTE]

{[@return_statur=]

{procedure_name[;

number]|@procedure_name_var}

[[@parameter=]{value|@variable[OUTPUT]|[DEFAULT][,…n]

[WITHRECOMPILE]

各参数的含义如下：

@return_status

是可选的整型变量，用来存储存储过程向调用者返回的值。

@procedure_name_var

是一变量名用来代表存储过程的名字。

其它参数据和保留字的含义与CREATEPROCEDURE中介绍的一样。

例12-6：

该存储过程被用来将两个字符串连接成一个字符串，并将结果返回。

创建存储过程：

12.3.5修改存储过程

修改以前用CREATEPROCEDURE命令创建的存储过程，并且不改变权限的授予情况以及不影响任何其它的独立的存储过程或触发器常使用ALTERPROCEDURE命令。

其语法规则是：

ALTERPROC[EDURE]procedure_name[;

number]

[{@parameterdata_type}[VARYING][=default][OUTPUT]][,...n]

[WITH

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]

AS

sql_statement[...n]

其中各参数和保留字的具体含义请参看CREATEPROCEDURE命令。

下面将举一个例子使读者对该命令有更为深刻的理解。

将该段代码输入到SQLServerQueryAnalyzer窗口中运行后结果为：

由于在ALERTPROCEDURE中使用了WITHENCTYPTION保留字，所以在查看修改后的存储过程源代码时看到是一些代码。

创建存储过程

在MSSQLServer2000中，创建一个存储过程有两种方法：

一种是使用Transaction-SQL命令CreateProcedure，另一种是使用图形化管理工具EnterpriseManager。

用Transaction-SQL创建存储过程是一种较为快速的方法，但对于初学者，使用EnterpriseManager更易理解，更为简单。

当创建存储过程时，需要确定存储过程的三个组成部分；

∙所有的输入参数以及传给调用者的输出参数。

∙被执行的针对数据库的操作语句，包括调用其它存储过程的语句；

∙返回给调用者的状态值，以指明调用是晒故鞘О堋?

/li>

12.2.1使用EnterpriseManager创建存储过程

按照下述步骤用EnterpriseManager创建一个存储过程：

∙启动EnterpriseManager，登录到要使用的服务器。

∙选择要创建存储过程的数据库，在左窗格中单击StoredProcedure文件夹，此时在右窗格中显示该数据库的所有存储过程，如图12-1所示。

∙右击StoredProcedure文件夹，在弹出菜单中选择NewStoredProcedure,此时打开创建存储过程对话框，如图12-2所示。

∙输入存储过程正文。

∙单击CheckSyntax，检查语法是否正确。

∙单击OK，保存。

∙在右窗格中，右击该存储过程，在弹出菜单中选择Alltask,选择ManagePermissions，设置权限，如图12-3所示。

12.2.2用CREATEPROCEDURE命令创建存储过程

通过运用CreateProcedure命令能够创建存储过程，在创建存储过程之前，应该考虑到以下几个方面：

∙在一个批处理中，CreateProcedure语句不能与其它SQL语句合并在一起；

∙数据库所有者具有默认的创建存储过程的权限，它可把该权限传递给其它的用户；

∙存储过程作为数据库对象其命名必须符合命名规则；

∙只能在当前数据库中创建属于当前数据库的存储过程。

用CreateProcedure创建存储过程的语法规则如下：

CREATEPROC[EDURE]procedure_name[;

number]

[{@parameterdata_type}

[VARYING][=default][OUTPUT]

][,...n]

[WITH

{RECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTION}]

[FORREPLICATION]

ASsql_statement[...n]

∙procedure_name

是要创建的存储过程的名字，它后面跟一个可选项number，它是一个整数，用来区别一组同名的存储过程。

存储过程的命名必须符合命名规则，在一个数据库中或对其所有者而言，存储过程的名字必须惟一。

∙@parameter

是存储过程的参数。

在CreateProcedure语句中，可以声明一个或多个参数。

当调用该存储过程时，用户必须给出所有的参数值，除非定义了参数的缺省值。

若参数的形式以@parameter=value出现，则参数的次序可以不同，否则用户给出的参数值必须与参数列表中参数的顺序保持一致。

若某一参数以@parameter=value形式给出，那么其它参数也必须以该形式给出。

一个存储过程至多有1024个参数。

∙Data_type

是参数的数据类型。

在存储过程中，所有的数据类型包括text和image都可被用作参数。

但是，游标cursor数据类型只能被用作OUTPUT参数。

当定义游标数据类型时，也必须对VARING和OUTPUT关键字进行定义。

对可能是游标型数据类型的OUTPUT参数而言，参数的最大数目没有限制。

∙VARYING

指定由OUTPUT参数支持的结果集，仅应用于游标型参数。

∙Default

是指参数的缺省值。

如果定义了缺省值，那么即使不给出参数值，则该存储过程仍能被调用。

缺省值必须是常数，或者是空值。

∙OUTPUT

表明该参数是一个返回参数。

用OUTPUT参数可以向调用者返回信息。

Text类型参数不能用作OUTPUT参数。

∙RECOMPILE

指明SQLServer并不保存该存储过程的执行计划，该存储过程每执行一次都又要重新编译。

∙ENCRYPTION

表明SQLServer加密了syscomments表，该表的text字段是包含有Createprocedure语句的存储过程文本，使用该关键字无法通过查看syscomments表来查看存储过程内容。

∙FORREPLICATION

选项指明了为复制创建的存储过程不能在订购服务器上执行，只有在创建过滤存储过程时（仅当进行数据复制时过滤存储过程才被执行），才使用该选项。

FORREPLICATION与WITHRECOMPILE选项是互不兼容的。

∙AS

指明该存储过程将要执行的动作。

∙Sql_statement

是任何数量和类型的包含在存储过程中的SQL语句。

另外应该指出，一个存储过程的最大尺寸为128M，用户定义的存储过程必须创建在当前数据库中。

下面将给出几个例子，用来详细介绍如何创建包含有各种保留字的存储过程。

系统存储过程

系统存储过程就是系统创建的存储过程，目的在于能够方便地从系统表中查询信息或完成与更新数据库表相关的管理任务或其它的系统管理任务。

系统过程以“sp_"

_为开头,在Master数据库中创建并保存在该数据库中，为数据库管理者所有。

一些系统过程只能由系统管理员使用，而有些系统过程通过授权可以被其它用户所使用。

系统存储过程主要包括以下几类：

（这里主要给出每类系统过程中经常使用的系统过程）