SQL-sever自学教程-存储过程.ppt

SQL-sever自学教程-存储过程.ppt

《SQL-sever自学教程-存储过程.ppt》由会员分享，可在线阅读，更多相关《SQL-sever自学教程-存储过程.ppt（38页珍藏版）》请在冰点文库上搜索。

2023年4月30日星期日,1,第12章存储过程,概述创建存储过程执行存储过程删除存储过程练习,2023年4月30日星期日,2,概述,存储过程（storedprocedure）是一组事先编译好的Transact-SQL代码。

存储过程作为一个独立的数据库对象，可以作为一个单元被用户的应用程序调用。

由于存储过程是已经编译好的代码，所以执行的时候不必再次进行编译，从而提高了程序的运行效率。

2023年4月30日星期日,3,存储过程的特点,SQLServer的存储过程类似于其他编程语言里的过程（procedure），具体体现在：

存储过程可以接收参数，并以输出参数的形式返回多个参数给调用存储过程的过程或批处理。

存储过程也可以容纳存储过程，可以在对数据库进行查询、修改的编程语句中调用其他的存储过程。

可以返回执行存储过程的状态值以反映存储过程的执行情况。

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

2023年4月30日星期日,4,存储过程的好处,使用SQLServer中的存储过程而不使用存储在客户计算机本地的Transact-SQL程序的优势有：

允许模块化程序设计。

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

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

允许更快执行。

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

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

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

减少网络流量。

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

可作为安全机制使用。

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

2023年4月30日星期日,5,存储过程的类型,SQLServer存储过程是用Transact-SQL语句CREATEPROCEDURE创建的，并可用ALTERPROCEDURE语句进行修改。

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

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

在MicrosoftSQLServer2000关系型数据库管理系统中，支持5种类型的存储过程：

系统存储过程、本地存储过程、临时存储过程、远程存储过程和扩展存储过程。

不同类型的存储过程具有不同的作用。

2023年4月30日星期日,6,1）系统存储过程在SQLServer中的许多管理工作是通过执行系统存储过程来完成的。

系统存储过程是由系统提供的存储过程，可以作为命令执行各种操作，还可以作为样本存储过程指导用户如何编写有效的存储过程。

系统存储过程创建和保存在master数据库中，都以sp_为名称的前缀。

允许系统管理员执行修改系统表的数据库管理任务。

系统存储过程是SQLServer系统自带的（分为9类），具有执行系统存储过程权限的用户，可在master数据库之外直接调用（即：

可在任意一个数据库中执行）。

一般情况下，系统存储过程执行成功返回0值，若有错误发生返回非0值。

2023年4月30日星期日,7,2）本地存储过程（用户存储过程）指创建在每个用户自己数据库中的存储过程，即：

由用户根据实际问题的需要所创建的存储过程。

这种存储过程主要在应用程序中使用，虽然存储过程的名称前不能有sp_为名称的前缀，但是在实际应用中应该使用有意义的名称。

在以后的数据库开发过程中，涉及的存储过程都是本地存储过程，可以完成特定的任务。

2023年4月30日星期日,8,3）临时存储过程临时存储过程首先是本地存储过程。

SQLServer支持两种临时过程：

局部临时过程和全局临时过程。

如果存储过程的名称前面有一个符号“#”，那么把该存储过程称为局部临时存储过程，这种存储过程只能在一个用户会话中使用。

局部临时过程只能由创建该过程的连接使用，在当前会话结束时自动除去。

如果存储过程的名称前面有两个符号“#”，那么把该存储过程称为全局临时存储过程，这种存储过程可以在所有用户会话中使用。

全局临时过程则可由所有连接使用，在使用该过程的最后一个会话结束时除去。

通常是在创建该过程的会话结束时。

2023年4月30日星期日,9,4）远程存储过程指从远程服务器上调用的存储过程，或是从连接到另外一个服务器上的客户机上调用的存储过程。

远程存储过程实际上就是非本地服务器上的存储过程。

5）扩展存储过程扩展存储过程是以动态链接库（dll）形式存在的外部程序。

SQLServer自身带了大量的扩展存储过程安装在master数据库中，扩展存储过程与普通存储过程执行方法相同。

2023年4月30日星期日,10,创建存储过程,创建存储过程，存储过程是保存起来的可以接受和返回用户提供的参数的Transact-SQL语句的集合。

用户存储过程可以使用T-SQL语句CREATEPROCEDURE或企业管理器来创建。

在创建一个存储过程之前，应注意以下几个问题：

l在存储过程中不能包含CREATEVIEW、CREATEDEFAULT、CREATERULE、CREATEPROCEDURE和CREATETRIGGER这几个创建数据库对象的语句。

l创建存储过程的权限默认属于数据库所有者，该权限可以授予其他人。

l存储过程是数据库对象，它的命名应符合标识符的命名规则。

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

2023年4月30日星期日,11,当创建好存储过程之后，系统检查其中语句的正确性。

语法检查之后，系统将存储过程的名称存储在当前数据库的系统表sysobjects中，将存储过程的文本储存在当前数据库的系统表syscomments中；如果碰到语法错误，那么存储过程创建失败。

权限：

CREATEPROCEDURE的权限默认授予sysadmin固定服务器角色成员和db_owner和db_ddladmin固定数据库角色成员。

sysadmin固定服务器角色成员和db_owner固定数据库角色成员可以将CREATEPROCEDURE权限转让给其他用户。

执行存储过程的权限授予过程的所有者，该所有者可以为其它数据库用户设置执行权限。

创建存储过程的方法：

1、使用T-SQL语句创建存储过程2、使用企业管理器创建存储过程3、使用向导创建存储过程,2023年4月30日星期日,12,1使用T-SQL语句创建存储过程创建存储过程使用CREATEPROCEDURE语句。

【语法格式】CREATEPROCEDUREprocedure_name;numberparameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONFORREPLICATIONASsql_statement.n,2023年4月30日星期日,13,其中：

procedure_name为存储过程名。

parameter为存储过程中的输入、输出参数。

data_type为参数的数据类型。

default为参数指定的默认值。

WITH子名为指定一些选项。

RECOMPILE表明SQLServer不会缓存该过程的计划，该过程将在每次运行时重新编译。

ENCRYPTION表示SQLServer加密syscomments表中包含CREATEPROCEDURE语句文本的条目。

n表示此过程可以包含多条T-SQL语句。

sql_statement在存储过程中要执行的T-SQL语句。

2023年4月30日星期日,14,一个简单存储过程的创建和执行【示例】使用带有复杂SELECT语句的简单过程。

（该存储过程不使用任何参数。

）CREATEPROCEDUREau_info_allASSELECTau_lname,au_fname,title,pub_nameFROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_idINNERJOINtitlestONt.title_id=ta.title_idINNERJOINpublisherspONt.pub_id=p.pub_idGOau_info_all存储过程可以通过以下方法执行：

EXECUTEau_info_all（或）EXECau_info_all如果该过程是批处理中的第一条语句，则可使用：

au_info_all,2023年4月30日星期日,15,说明：

建议使用下面的方式建立存储过程。

首先检验在sysobjects表中是否存在名字相同的存储过程的记录，如果有，则先删除原有的存储过程，再创建与之同名的存储过程。

USEpubsIFEXISTS（SELECTnameFROMsysobjectsWHEREname=au_info_allANDtype=P）DROPPROCEDUREau_info_allGOCREATEPROCEDUREau_info_allASSELECTau_lname,au_fname,title,pub_nameFROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_idINNERJOINtitlestONt.title_id=ta.title_idINNERJOINpublisherspONt.pub_id=p.pub_idGO,2023年4月30日星期日,16,创建带参数的存储过程在创建和使用存储过程时，其参数是非常重要的。

存储过程通过参数来与调用它的程序通信。

在程序中调用存储过程时，可以通过输入参数将数据传给存储过程，存储过程可以通过输出参数和返回值将数据返回给调用它的程序。

一个存储过程中最多可以有1024个参数。

存储过程的参数在创建时应在CREATEPROCEDURE和AS关键字之间定义，每个参数都要指定参数名和数据类型。

参数名必须以符号为前缀。

用户可以为参数指定默认值，如果调用存储过程时，不为输入参数传入数据，系统就会将默认值传给输入参数。

如果是输出参数应用OUTPUT关键字描述，各个参数定义之间用逗号隔开。

【语法】parameter_namedata_type=defaultOUTPUT,2023年4月30日星期日,17,【示例】使用带有参数的简单过程下面的存储过程从四个表的联接中只返回指定的作者（提供了姓名）、出版的书籍以及出版社。

该存储过程接受与传递的参数精确匹配的值。

USEpubsIFEXISTS（SELECTnameFROMsysobjectsWHEREname=au_infoANDtype=P）DROPPROCEDUREau_infoGOUSEpubsGOCREATEPROCEDUREau_infolastnamevarchar（40）,firstnamevarchar（20）ASSELECTau_lname,au_fname,title,pub_nameFROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_idINNERJOINtitlestONt.title_id=ta.title_idINNERJOINpublisherspONt.pub_id=p.pub_idWHEREau_fname=firstnameANDau_lname=lastnameGO,2023年4月30日星期日,18,在程序中调用存储过程时，SQLServer提供了两种传递参数的方法。

（1）按位置传递参数。

即：

使传递的参数和定义时的参数顺序一致。

【例】EXECUTEau_infoDull,Ann

（2）通过参数名传递。

即：

采用“参数=值”的形式，此时各个参数的顺序可以任意排列。

注意：

通过参数名接收输出参数值时，输出参数名一定要位于等号的左侧。

EXECUTEau_infolastname=Dull,firstname=Ann-OrEXECUTEau_infofirstname=Ann,lastname=Dull,2023年4月30日星期日,19,故：

上例au_info存储过程可以通过以下方法执行：

EXECUTEau_infoDull,Ann-OrEXECUTEau_infolastname=Dull,firstname=Ann-OrEXECUTEau_infofirstname=Ann,lastname=Dull-OrEXECau_infoDull,Ann-OrEXECau_infolastname=Dull,firstname=Ann-OrEXECau_infofirstname=Ann,lastname=Dull如果该过程是批处理中的第一条语句，则可使用：

au_infoDull,Ann-Orau_infolastname=Dull,firstname=Ann-Orau_infofirstname=Ann,lastname=Dull,2023年4月30日星期日,20,【示例】创建使用参数的存储过程下例创建一个在pubs数据库中很有用的存储过程。

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

CREATEPROCau_infolastnamevarchar（40）,firstnamevarchar（20）ASSELECTau_lname,au_fname,title,pub_nameFROMauthorsINNERJOINtitleauthorONauthors.au_id=titleauthor.au_idJOINtitlesONtitleauthor.title_id=titles.title_idJOINpublishersONtitles.pub_id=publishers.pub_idWHEREau_fname=firstnameANDau_lname=lastnameGO将出现一条说明该命令未返回任何数据也未返回任何行的消息，这表示已创建该存储过程。

现在执行au_info存储过程：

EXECUTEau_infoRinger,AnneGO,2023年4月30日星期日,21,【示例】创建使用参数默认值的存储过程（默认值必须为常量或NULL）下例创建一个存储过程pub_info2，该存储过程显示作为参数给出的出版商所出版的某本书的作者姓名。

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

CREATEPROCpub_info2pubnamevarchar（40）=AlGOdataInfosystemsASSELECTau_lname,au_fname,pub_nameFROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_idJOINtitlestONta.title_id=t.title_idJOINpublisherspONt.pub_id=p.pub_idWHEREpubname=p.pub_name执行未指定参数的pub_info2：

EXECUTEpub_info2GO可以看到，调用存储过程时没有指定参数值，系统自动使用相应的默认值。

2023年4月30日星期日,22,【示例】使用带有通配符参数的简单过程下面的存储过程从四个表的联接中只返回指定的作者（提供了姓名）、出版的书籍以及出版社。

该存储过程对传递的参数进行模式匹配，如果没有提供参数，则使用预设的默认值。

USEpubsIFEXISTS（SELECTnameFROMsysobjectsWHEREname=au_info2ANDtype=P）DROPPROCEDUREau_info2GOUSEpubsGOCREATEPROCEDUREau_info2lastnamevarchar（30）=D%,firstnamevarchar（18）=%ASSELECTau_lname,au_fname,title,pub_nameFROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_idINNERJOINtitlestONt.title_id=ta.title_idINNERJOINpublisherspONt.pub_id=p.pub_idWHEREau_fnameLIKEfirstnameANDau_lnameLIKElastnameGO,2023年4月30日星期日,23,au_info2存储过程可以用多种组合执行。

下面只列出了部分组合：

EXECUTEau_info2-OrEXECUTEau_info2Wh%-OrEXECUTEau_info2firstname=A%-OrEXECUTEau_info2CKarsOEn-OrEXECUTEau_info2Hunter,Sheryl-OrEXECUTEau_info2H%,S%,2023年4月30日星期日,24,【示例】使用WITHENCRYPTION选项WITHENCRYPTION子句对用户隐藏存储过程的文本。

下例创建加密过程，使用sp_helptext系统存储过程获取关于加密过程的信息，然后尝试直接从syscomments表中获取关于该过程的信息。

IFEXISTS（SELECTnameFROMsysobjectsWHEREname=encrypt_thisANDtype=P）DROPPROCEDUREencrypt_thisGOUSEpubsGOCREATEPROCEDUREencrypt_thisWITHENCRYPTIONASSELECT*FROMauthorsGOEXECsp_helptextencrypt_this,2023年4月30日星期日,25,选择加密存储过程内容的标识号和文本。

SELECTc.id,c.textFROMsyscommentscINNERJOINsysobjectsoONc.id=o.idWHEREo.name=encrypt_this,2023年4月30日星期日,26,【示例】使用OUTPUT参数OUTPUT参数允许外部过程、批处理或多条Transact-SQL语句访问在过程执行期间设置的某个值。

下面的示例创建一个存储过程（titles_sum），并使用一个可选的输入参数和一个输出参数。

-创建存储过程USEpubsGOIFEXISTS（SELECTnameFROMsysobjectsWHEREname=titles_sumANDtype=P）DROPPROCEDUREtitles_sumGOUSEpubsGOCREATEPROCEDUREtitles_sumTITLEvarchar（40）=%,SUMmoneyOUTPUTASSELECTTitleName=titleFROMtitlesWHEREtitleLIKETITLESELECTSUM=SUM（price）FROMtitlesWHEREtitleLIKETITLEGO,2023年4月30日星期日,27,接下来，将该OUTPUT参数用于控制流语言。

说明：

OUTPUT变量必须在创建表和使用该变量时都进行定义。

参数名和变量名不一定要匹配，不过数据类型和参数位置必须匹配（除非使用SUM=variable形式）。

DECLARETOTALCOSTmoneyEXECUTEtitles_sumThe%,TOTALCOSTOUTPUTIFTOTALCOST200BEGINPRINTPRINTAllofthesetitlescanbepurchasedforlessthan$200.ENDELSESELECTThetotalcostofthesetitlesis$+RTRIM（CAST（TOTALCOSTASvarchar（20）,2023年4月30日星期日,28,2使用企业管理器创建存储过程3、使用向导创建存储过程,2023年4月30日星期日,29,查看存储过程,1使用T-SQL语句查看存储过程

（1）可以使用sp_helptext命令查看创建存储过程的文本信息。

【例】sp_helptexttitles_sum说明：

如果在创建存储过程时，使用了WITHENCRYPTION参数，则使用sp_helptext将无法看到有关存储过程的信息（看见的是不可识别的乱码）。

（2）可以使用sp_help查看存储过程的一般信息。

【例】sp_helptitles_sum（3）可以使用sp_depends语句查看。

某个表被存储过程引用的情况。

【例】sp_dependstitles_sum,2023年4月30日星期日,30,【示例】sp_helptitles_sumgosp_helptexttitles_sumgosp_dependstitles_sumgosp_stored_procedurestitles_sumgo,2023年4月30日星期日,31,2使用企业管理器查看存储过程,2023年4月30日星期日,32,修改存储过程,SQLServer提供了在不改变存储过程使用许可和名字的情况下，对存储过程进行修改的语句。

语法格式为：

ALTERPROCEDURE存储过程名WITHRECOMPILEENCRYPTIONRECOMPILE,ENCRYPTIONASSQL语句,2023年4月30日星期日,33,执行存储过程,成功执行CREATEPROCEDURE语句后，过程名称将存储在sysobjects系统表中，而CREATEPROCEDURE语句的文本将存储在syscomments中。

第一次执行时，将编译该过程以确定检索数据的最佳访问计划。

只有具有存储过程的执行权限EXECUTE，才可以执行存储过程。

执行存储过程有两种方法。

一种方法是直接执行存储过程，另外一种方法是在INSERT语句中执行存储过程。

直接执行存储过程就是调用EXECUTE语句来执行存储过程。

在执行存储过程的时候，需要提供存储过程的名称和存储过程所需要的参数。

2023年4月30日星期日,34,说明：

如果对存储过程的调用是批处理的第一条语句，则可以直接使用存储过程的名字调用该存储过程。

【例】USEpubsGOtitles_sumGO在批处理中对存储过程的调用若不是第一个语句应该使用EXECUTE或EXEC关键字。

否则将会返回错误的信息。

【例】USEpubsEXECtitles_sumGO,2023年4月30日星期日,35,确定存储过程的执行状态SQLServer允许设置存储过程的返回值，从而确定存储过程执行的状态。

通常返回的值，用户可以知道存储过程是正确执行了还是失败了以及为什么失败。

SQLServer提供了预定返回状态值的集合，但用户也可以自己定义返回的状态值。

说明：

SQLServer预定义0表示成功返回，预留-1-99为不同的失败原因，从-1-14通常用于指示错的不同原因，如：

-2表示数据类型不