存储过程解析.docx

存储过程解析.docx

《存储过程解析.docx》由会员分享，可在线阅读，更多相关《存储过程解析.docx（30页珍藏版）》请在冰豆网上搜索。

存储过程解析

存储过程

在使用Microsoft®SQLServer™2000创建应用程序时，Transact-SQL编程语言是应用程序和SQLServer数据库之间的主要编程接口。

使用Transact-SQL程序时，可用两种方法存储和执行程序。

可以在本地存储程序，并创建向SQLServer发送命令并处理结果的应用程序；也可以将程序在SQLServer中存储为存储过程，并创建执行存储过程并处理结果的应用程序。

SQLServer中的存储过程与其它编程语言中的过程类似，原因是存储过程可以：

*接受输入参数并以输出参数的形式将多个值返回至调用过程或批处理。

*包含执行数据库操作（包括调用其它过程）的编程语句。

*向调用过程或批处理返回状态值，以表明成功或失败（以及失败原因）。

可使用Transact-SQLEXECUTE语句运行存储过程。

存储过程与函数不同，因为存储过程不返回取代其名称的值，也不能直接用在表达式中。

使用SQLServer中的存储过程而不使用存储在客户计算机本地的Transact-SQL程序的优势有：

*允许模块化程序设计。

只需创建过程一次并将其存储在数据库中，以后即可在程序中调用该过程任意次。

存储过程可由在数据库编程方面有专长的人员创建，并可独立于程序源代码而单独修改。

*允许更快执行。

如果某操作需要大量Transact-SQL代码或需重复执行，存储过程将比Transact-SQL批代码的执行要快。

将在创建存储过程时对其进行分析和优化，并可在首次执行该过程后使用该过程的内存中版本。

每次运行Transact-SQL语句时，都要从客户端重复发送，并且在SQLServer每次执行这些语句时，都要对其进行编译和优化。

*减少网络流量。

一个需要数百行Transact-SQL代码的操作由一条执行过程代码的单独语句就可实现，而不需要在网络中发送数百行代码。

*可作为安全机制使用。

即使对于没有直接执行存储过程中语句的权限的用户，也可授予他们执行该存储过程的权限。

SQLServer存储过程是用Transact-SQL语句CREATEPROCEDURE创建的，并可用ALTERPROCEDURE语句进行修改。

存储过程定义包含两个主要组成部分：

过程名称及其参数的说明，以及过程的主体（其中包含执行过程操作的Transact-SQL语句）。

一、扩展存储过程

扩展存储过程使您得以使用象C这样的编程语言创建自己的外部例程。

对用户来说，扩展存储过程与普通存储过程一样，执行方法也相同。

可将参数传递给扩展存储过程，扩展存储过程可返回结果，也可返回状态。

扩展存储过程可用于扩展Microsoft®SQLServer™2000的功能。

扩展存储过程是SQLServer可以动态装载并执行的动态链接库（DLL）。

扩展存储过程直接在SQLServer的地址空间运行，并使用SQLServer开放式数据服务（ODS）API编程。

编写好扩展存储过程后，固定服务器角色sysadmin的成员即可在SQLServer中注册该扩展存储过程，然后授予其他用户执行该过程的权限。

扩展存储过程只能添加到master数据库中。

说明  扩展存储过程可能会产生内存泄漏或其它降低服务器的性能及可靠性的问题。

应考虑将扩展存储过程存储在一个不同于包含被引用数据并使用分布式查询访问数据库的实例的SQLServer实例中。

有关更多信息，请参见分布式查询。

如何添加扩展存储过程（企业管理器）

添加扩展存储过程

1、展开服务器组，然后展开服务器。

2、展开"数据库"文件夹，再展开master数据库。

3、右击"扩展存储过程"，然后单击"新建扩展存储过程"命令。

4、在"名称"框中输入扩展存储过程的名称。

5、在"路径"中，输入包含此扩展存储过程的动态链接库的路径。

单击（"..."）按钮，查找包含此扩展存储过程的DLL。

（可选）

二、创建存储过程

可使用Transact-SQL语句CREATEPROCEDURE创建存储过程。

创建存储过程前，请考虑下列事项：

*不能将CREATEPROCEDURE语句与其它SQL语句组合到单个批处理中。

*创建存储过程的权限默认属于数据库所有者，该所有者可将此权限授予其他用户。

*存储过程是数据库对象，其名称必须遵守标识符规则。

*只能在当前数据库中创建存储过程。

创建存储过程时，应指定：

*所有输入参数和向调用过程或批处理返回的输出参数。

*执行数据库操作（包括调用其它过程）的编程语句。

*返回至调用过程或批处理以表明成功或失败（以及失败原因）的状态值。

系统存储过程

Microsoft®SQLServer™2000中的许多管理活动是通过一种称为系统存储过程的特殊过程执行的。

系统存储过程在master数据库中创建并存储，带有sp_前缀。

可从任何数据库中执行系统存储过程，而无需使用master数据库名称来完全限定该存储过程的名称。

强烈建议您不要创建以sp_为前缀的存储过程。

SQLServer始终按照下列顺序查找以sp_开头的存储过程：

1、在master数据库中查找存储过程。

2、根据所提供的任何限定符（数据库名称或所有者）查找该存储过程。

3、如果未指定所有者，则使用dbo作为所有者查找该存储过程。

因此，虽然当前数据库中可能存在带sp_前缀的用户创建的存储过程，但总会先检查master数据库（即使该存储过程已用数据库名称限定）。

重要  如果用户创建的存储过程与系统存储过程同名，则永远不执行用户创建的存储过程。

分组

如果将一个不同的标识号赋予某过程，则可以用与现有某存储过程相同的名称创建该过程，这样可允许将这些过程进行逻辑分组。

同名的分组过程可以同时删除。

在同一应用程序中使用的过程一般都以该方式分组。

例如，用于my_app应用程序的过程可能被命名为my_proc;1、my_proc;2等。

删除my_proc即删除该整个组。

将过程分组后，就无法删除该组内的单个过程。

临时存储过程

专用和全局临时存储过程与临时表类似，都可以用向该过程名称添加#和##前缀的方法进行创建。

#表示本地临时存储过程，##表示全局临时存储过程。

SQLServer关闭后，这些过程将不再存在。

临时存储过程在连接到SQLServer的早期版本时很有用，这些早期版本不支持再次使用Transact-SQL语句或批处理执行计划。

连接到SQLServer2000的应用程序应使用sp_executesql系统存储过程，而不使用临时存储过程。

有关更多信息，请参见执行计划的高速缓存和重新使用。

只有创建本地临时过程的连接才能执行该过程，当该连接关闭（用户从SQLServer中注销）时，将自动删除该过程。

任何连接都可执行全局临时存储过程。

只有创建该过程的用户所用的连接关闭，并且所有其它连接所用的该过程的当前执行版本运行完毕后，全局临时存储过程才不再存在。

一旦用于创建该过程的连接关闭，将不再允许启动执行该全局临时存储过程。

只允许那些已启动执行该存储过程的连接完成该过程的运行。

如果直接在tempdb数据库中创建没有#或##前缀的存储过程，则由于每次启动SQLServer时tempdb都要重新创建，因此当关闭SQLServer时将自动删除该存储过程。

直接在tempdb中创建的过程即使在创建该过程的连接终止后也会存在。

与任何其它对象一样，可向其他用户授予、拒绝和废除执行该临时存储过程的权限。

如何创建存储过程（企业管理器）

创建存储过程

1、展开服务器组，然后展开服务器。

2、展开"数据库"文件夹，再展开要在其中创建过程的数据库。

3、右击"存储过程"，然后单击"新建存储过程"命令。

4、输入存储过程的文本。

按TAB键可以缩进存储过程的文本。

按下CTRL+TAB键或单击合适的按钮来退出文本框。

5、若要检查语法，单击"检查语法"命令。

6、若要设置权限，单击"权限"命令。

如何用创建存储过程向导创建存储过程（企业管理器）

用创建存储过程向导创建存储过程

1、展开一个服务器组，再展开要在其中创建视图的服务器。

2、在"工具"菜单上单击"向导"命令。

3、展开"数据库"文件夹。

4、双击"创建存储过程向导"命令。

5、完成向导中的步骤。

2.1、指定参数

存储过程通过其参数与调用程序通讯。

当程序执行存储过程时，可通过存储过程的参数向该存储过程传递值。

这些值可作为Transact-SQL编程语言中的标准变量使用。

存储过程也可通过OUTPUT参数将值返回至调用程序。

一个存储过程可有多达2100个参数，每个参数都有名称、数据类型、方向和默认值。

2.1.1、指定名称

每个存储过程参数都必须用唯一的名称进行定义。

与标准的Transact-SQL变量相同，存储过程名称必须以单个@字符开头，并且必须遵从对象标识符规则。

可在存储过程中使用参数名称以获得参数值并更改它。

既可以通过显式指定参数名称并赋予适当值，也可以通过提供在CREATEPROCEDURE语句中给定的参数值（不指定参数名称）来向存储过程传递值。

例如，如果存储过程my_proc应有三个命名为@first、@second和@third的参数，则可将传递至该存储过程的值赋予参数名称，如：

EXECUTEmy_proc@second=2,@first=1,@third=3或者按照位置传递，而不命名参数名称：

EXECUTEmy_proc1,2,3执行存储过程时指定参数名称，将允许按任何顺序提供参数。

如果未指定参数名称，那么必须按照与定义存储过程参数时相同的顺序（从左至右）提供参数。

另外，必须提供给定参数前面的所有参数，即使这些参数可选且有默认值。

例如，如果my_proc的参数都是可选的，那么执行my_proc时可以仅提供第一个和第二个参数的值，但不能仅提供第二个和第三个参数的值。

这是必需的，否则Microsoft®SQLServer™2000将无法识别正在被指定的参数。

2.1.2、指定数据类型

存储过程中的参数要定义数据类型，这与表中的列几乎一样。

可以用Microsoft®SQLServer™2000的任何一种数据类型（包括text和image类型）定义存储过程参数。

也可以用用户定义的数据类型定义存储过程参数。

说明  对于存储过程来说，数据类型cursor只能用作OUTPUT参数。

有关使用游标变量的更多信息，请参见Transact-SQL游标名称的作用域。

参数的数据类型确定了该参数所接受值的类型和范围。

例如，如果用tinyint数据类型定义参数，则该参数将只接受0至255范围内的数值。

如果用与数据类型不兼容的值执行存储过程，将返回一个错误。

2.1.3、指定参数的方向

当调用存储过程的程序执行该存储过程时，所有过程参数都可接收输入值。

示例

下列存储过程get_sales_for_title使用了输入参数。

该存储过程中的@title参数接收调用程序所指定的书名作为输入值。

SELECT语句使用此@title参数获得正确的ytd_sales值并显示该值。

CREATEPROCEDUREget_sales_for_title

@titlevarchar（80）--Thisistheinputparameter.

AS

--Getthesalesforthespecifiedtitle.

SELECT"YTD_SALES"=ytd_sales

FROMtitles

WHEREtitle=@title

RETURN

GO

如果在存储过程定义中为某参数指定OUTPUT关键字，则存储过程将在其退出时向调用程序返回此参数的当前值。

调用程序也必须使用OUTPUT关键字执行该存储过程，才能将该参数值保存到变量中以便在调用程序中使用。

有关更多信息，请参见使用OUTPUT参数返回数据。

2.1.4、指定默认值

通过为可选参数指定默认值，可创建带有可选参数的存储过程。

执行该存储过程时，如果未指定其它值，则使用默认值。

如果在存储过程中没有指定参数的默认值，并且调用程序也没有在执行存储过程时为该参数提供值，那么会返回系统错误，因此指定默认值是必要的。

如果不能为参数指定合适的默认值，则可以指定NULL作为参数的默认值，并在未提供参数值而执行存储过程的情况下，使存储过程返回一条自定义消息。

说明  如果默认值是包含嵌入空格或标点符号的字符串，或者以数字开头（例如,6xxx），那么该默认值必须用直的单引号引起来。

示例

下例显示在未提供@title参数值的情况下执行存储过程时，get_sales_for_title过程将进行特殊处理。

CREATEPROCEDUREget_sales_for_title

@titlevarchar（80）=NULL,--NULLdefaultvalue

@ytd_salesintOUTPUT

AS

--Validatethe@titleparameter.

IF@titleISNULL

BEGIN

PRINT'ERROR:

Youmustspecifyatitlevalue.'

RETURN

END

--Getthesalesforthespecifiedtitleand

--assignittotheoutputparameter.

SELECT@ytd_sales=ytd_sales

FROMtitles

WHEREtitle=@title

RETURN

GO

下例显示三个参数@first、@second和@third均有默认值的过程my_proc，以及在用其它参数值执行该存储过程时所显示的值：

CREATEPROCEDUREmy_proc

@firstint=NULL,--NULLdefaultvalue

@secondint=2,--Defaultvalueof2

@thirdint=3--Defaultvalueof3

AS

--Displayvalues.

SELECT@first,@second,@third

GO

EXECUTEmy_proc--Noparameterssupplied

GO

显示：

NULL23

EXECUTEmy_proc10,20,30--Allparameterssupplied

GO

显示：

102030

EXECUTEmy_proc@second=500--Onlysecondparametersuppliedbyname

GO

显示：

NULL5003

EXECUTEmy_proc40,@third=50--Onlyfirstandthirdparameters

GO--aresupplied.

显示：

40250

2.2、设计存储过程

几乎任何可写成批处理的Transact-SQL代码都可用于创建存储过程。

存储过程的设计规则

存储过程的设计规则包括：

*CREATEPROCEDURE定义本身可包括除下列CREATE语句以外的任何数量和类型的SQL语句，存储过程中的任意地方都不能使用下列语句：

CREATEDEFAULTCREATEPROCEDURECREATERULE

CREATETRIGGERCREATEVIEW

*可在存储过程中创建其它数据库对象。

可以引用在同一存储过程中创建的对象，前提是在创建对象后再引用对象。

*可以在存储过程内引用临时表。

*如果在存储过程内创建本地临时表，则该临时表仅为该存储过程而存在；退出该存储过程后，临时表即会消失。

*如果执行调用其它存储过程的存储过程，那么被调用存储过程可以访问由第一个存储过程创建的、包括临时表在内的所有对象。

*如果执行在远程Microsoft®SQLServer™2000实例上进行更改的远程存储过程，则不能回滚这些更改。

*远程存储过程不参与事务处理。

*存储过程中参数的最大数目为2100。

*存储过程中局部变量的最大数目仅受可用内存的限制。

*根据可用内存的不同，存储过程的最大大小可达128MB。

限定存储过程内的名称

在存储过程内部，如果用于诸如SELECT或INSERT这样的语句的对象名没有限定用户，那么用户将默认为该存储过程的所有者。

在存储过程内部，如果创建存储过程的用户没有限定SELECT、INSERT、UPDATE或DELETE语句中引用的表名，那么通过该存储过程对这些表进行的访问将默认地受到该过程的创建者权限的限制。

如果有其他用户要使用存储过程，则用于语句ALTERTABLE、CREATETABLE、DROPTABLE、TRUNCATETABLE、CREATEINDEX、DROPINDEX、UPDATESTATISTICS和DBCC的对象名必须用该对象所有者的名称限定。

例如，Mary拥有表marytab，如果她希望其他用户能够执行使用该表的存储过程，必须在该表用于上述某一条语句时对其表名进行限定。

此规则是必需的，因为运行存储过程时将解析对象的名称。

如果未限定marytab，而John试图执行该过程，SQLServer将查找John所拥有的名为marytab的表。

加密过程定义

如果要创建存储过程，并且希望确保其他用户无法查看该过程的定义，那么可以使用WITHENCRYPTION子句。

这样，过程定义将以不可读的形式存储。

存储过程一旦加密其定义即无法解密，任何人（包括存储过程的所有者或系统管理员）都将无法查看存储过程定义。

SET语句选项

当ODBC应用程序与SQLServer连接时，服务器将自动设置会话的下列选项：

*SETQUOTED_IDENTIFIERON

*SETTEXTSIZE2147483647

*SETANSI_DEFAULTSON

*SETCURSOR_CLOSE_ON_COMMITOFF

*SETIMPLICIT_TRANSACTIONSOFF

这些设置将提高ODBC应用程序的可移植性。

由于基于DB-Library的应用程序通常不设置这些选项，所以应在上述所列SET选项打开和关闭的情况下都对存储过程进行测试。

这样可确保存储过程始终能正确工作，而不管特定的连接在唤醒调用该存储过程时可能设置的选项。

需要特别设置其中一个选项的存储过程，应在开始该存储过程时发出一条SET语句。

此SET语句将只对该存储过程的执行保持有效，当该存储过程结束时，将恢复原设置。

示例

A.创建使用参数的存储过程

下例创建一个在pubs数据库中很有用的存储过程。

给出一个作者的姓和名，该存储过程将显示该作者的每本书的标题和出版商。

CREATEPROCau_info@lastnamevarchar（40）,@firstnamevarchar（20）

AS

SELECTau_lname,au_fname,title,pub_name

FROMauthorsINNERJOINtitleauthorONauthors.au_id=titleauthor.au_id

JOINtitlesONtitleauthor.title_id=titles.title_id

JOINpublishersONtitles.pub_id=publishers.pub_id

WHEREau_fname=@firstname

ANDau_lname=@lastname

GO

将出现一条说明该命令未返回任何数据也未返回任何行的消息，这表示已创建该存储过程。

现在执行au_info存储过程：

EXECUTEau_infoRinger,Anne

GO

下面是结果集：

au_lname

au_fname

title

pub_name

---------

---------

---------------------

----------------

Ringer

Anne

TheGourmetMicrowave

Binnet&Hardley

Ringer

Anne

IsAngertheEnemy?

NewMoonBooks

（2row（s）affected）

B.创建使用参数默认值的存储过程

下例创建一个存储过程pub_info2，该存储过程显示作为参数给出的出版商所出版的某本书的作者姓名。

如果未提供出版商的名称，该存储过程将显示由AlgodataInfosystems出版的书籍的作者。

CREATEPROCpub_info2@pubnamevarchar（40）='AlgodataInfosystems'

AS

SELECTau_lname,au_fname,pub_name

FROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_id

JOINtitlestONta.title_id=t.title_id

JOINpublisherspONt.pub_id=p.pub_id

WHERE@pubname=p.pub_name

执行未指定参数的pub_info2：

EXECUTEpub_info2

GO

下面是结果集：

au_lname

au_fname

pub_name

----------------

----------------

--------------------

Green

Marjorie

AlgodataInfosystems

Bennet

Abraham

AlgodataInfosystems

O'Leary

Michael

AlgodataInfosystems

MacFeather

Stearns

AlgodataInfosystems

Straight

Dean

AlgodataInfosystems

Carson

Cheryl

AlgodataInfosystems

Dull

Ann

AlgodataInfosystems

Hunter

Sheryl

AlgodataInfosystems

Locksley

Charlene

AlgodataInfosystems

（9row（s）affected）

C.执行用显式值替代参数默认值的存储过程

在下例中，存储过程showind2