SQL存储过程全面实例讲解.doc

《SQL存储过程全面实例讲解.doc》由会员分享，可在线阅读，更多相关《SQL存储过程全面实例讲解.doc（10页珍藏版）》请在淘文阁 - 分享文档赚钱的网站上搜索。

1、*-SQL实例讲解一、 创建存储过程结构CREATEPROCEDURE创建存储过程，存储过程是保存起来的可以接受和返回用户提供的参数的Transact-SQL语句的集合。可以创建一个过程供永久使用，或在一个会话中临时使用（局部临时过程），或在所有会话中临时使用（全局临时过程）。也可以创建在MicrosoftSQLServer启动时自动运行的存储过程。语法CREATEPROCEDUREprocedure_name;numberparameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIO

2、NFORREPLICATIONASsql_statement.n二、 存储过程实例讲解1.使用带有复杂SELECT语句的简单过程下面的存储过程从四个表的联接中返回所有作者（提供了姓名）、出版的书籍以及出版社。该存储过程不使用任何参数。USEpubsIFEXISTS(SELECTnameFROMsysobjectsWHEREname=au_info_allANDtype=P)DROPPROCEDUREau_info_allGOCREATEPROCEDUREau_info_allASSELECTau_lname,au_fname,title,pub_nameFROMauthorsaINNERJOI

3、NtitleauthortaONa.au_id=ta.au_idINNERJOINtitlestONt.title_id=ta.title_idINNERJOINpublisherspONt.pub_id=p.pub_idGOau_info_all存储过程可以通过以下方法执行：EXECUTEau_info_all-OrEXECau_info_all如果该过程是批处理中的第一条语句，则可使用：au_info_all2.使用带有参数的简单过程下面的存储过程从四个表的联接中只返回指定的作者（提供了姓名）、出版的书籍以及出版社。该存储过程接受与传递的参数精确匹配的值。USEpubsIFEXISTS(S

4、ELECTnameFROMsysobjectsWHEREname=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.p

5、ub_id=p.pub_idWHEREau_fname=firstnameANDau_lname=lastnameGOau_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如果该过程

6、是批处理中的第一条语句，则可使用：au_infoDull,Ann-Orau_infolastname=Dull,firstname=Ann-Orau_infofirstname=Ann,lastname=Dull3.使用带有通配符参数的简单过程下面的存储过程从四个表的联接中只返回指定的作者（提供了姓名）、出版的书籍以及出版社。该存储过程对传递的参数进行模式匹配，如果没有提供参数，则使用预设的默认值。USEpubsIFEXISTS(SELECTnameFROMsysobjectsWHEREname=au_info2ANDtype=P)DROPPROCEDUREau_info2GOUSEpubsG

7、OCREATEPROCEDUREau_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_lnameLIKElastnameGOau_info2存

8、储过程可以用多种组合执行。下面只列出了部分组合：EXECUTEau_info2-OrEXECUTEau_info2Wh*-OrEXECUTEau_info2firstname=A*-OrEXECUTEau_info2CKarsOEn-OrEXECUTEau_info2Hunter,Sheryl-OrEXECUTEau_info2H*,S*4.使用OUTPUT参数OUTPUT参数允许外部过程、批处理或多条Transact-SQL语句访问在过程执行期间设置的某个值。下面的示例创建一个存储过程(titles_sum)，并使用一个可选的输入参数和一个输出参数。首先，创建过程：USEpubsGOIFEX

9、ISTS(SELECTnameFROMsysobjectsWHEREname=titles_sumANDtype=P)DROPPROCEDUREtitles_sumGOUSEpubsGOCREATEPROCEDUREtitles_sumTITLEvarchar(40)=*,SUMmoneyOUTPUTASSELECTTitleName=titleFROMtitlesWHEREtitleLIKETITLESELECTSUM=SUM(price)FROMtitlesWHEREtitleLIKETITLEGO接下来，将该OUTPUT参数用于控制流语言。说明OUTPUT变量必须在创建表和使用该变量时都

10、进行定义。参数名和变量名不一定要匹配，不过数据类型和参数位置必须匹配（除非使用SUM=variable形式）。DECLARETOTALCOSTmoneyEXECUTEtitles_sumThe*,TOTALCOSTOUTPUTIFTOTALCOST200BEGINPRINTPRINTAllofthesetitlescanbepurchasedforlessthan$200.ENDELSESELECTThetotalcostofthesetitlesis$+RTRIM(CAST(TOTALCOSTASvarchar(20)下面是结果集：TitleName-TheBusyExecutivesDat

11、abaseGuideTheGourmetMicrowaveThePsychologyofComputerCooking(3row(s)affected)Warning,nullvalueeliminatedfromaggregate.Allofthesetitlescanbepurchasedforlessthan$200.5.使用OUTPUT游标参数OUTPUT游标参数用来将存储过程的局部游标传递回调用批处理、存储过程或触发器。首先，创建以下过程，在titles表上声明并打开一个游标：USEpubsIFEXISTS(SELECTnameFROMsysobjectsWHEREname=titl

12、es_cursorandtype=P)DROPPROCEDUREtitles_cursorGOCREATEPROCEDUREtitles_cursortitles_cursorCURSORVARYINGOUTPUTASSETtitles_cursor=CURSORFORWARD_ONLYSTATICFORSELECT*FROMtitlesOPENtitles_cursorGO接下来，执行一个批处理，声明一个局部游标变量，执行上述过程以将游标赋值给局部变量，然后从该游标提取行。USEpubsGODECLAREMyCursorCURSOREXECtitles_cursortitles_cursor

13、=MyCursorOUTPUTWHILE(FETCH_STATUS=0)BEGINFETCHNEXTFROMMyCursorENDCLOSEMyCursorDEALLOCATEMyCursorGO6.使用WITHRECOMPILE选项如果为过程提供的参数不是典型的参数，并且新的执行计划不应高速缓存或存储在内存中，WITHRECOMPILE子句会很有帮助。USEpubsIFEXISTS(SELECTnameFROMsysobjectsWHEREname=titles_by_authorANDtype=P)DROPPROCEDUREtitles_by_authorGOCREATEPROCEDURE

14、titles_by_authorLNAME_PATTERNvarchar(30)=*WITHRECOMPILEASSELECTRTRIM(au_fname)+RTRIM(au_lname)ASAuthorsfullname,titleASTitleFROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_idINNERJOINtitlestONta.title_id=t.title_idWHEREau_lnameLIKELNAME_PATTERNGO7.使用WITHENCRYPTION选项WITHENCRYPTION子句对用户隐藏存储过程的文本。下例创

15、建加密过程，使用sp_helptext系统存储过程获取关于加密过程的信息，然后尝试直接从syscomments表中获取关于该过程的信息。IFEXISTS(SELECTnameFROMsysobjectsWHEREname=encrypt_thisANDtype=P)DROPPROCEDUREencrypt_thisGOUSEpubsGOCREATEPROCEDUREencrypt_thisWITHENCRYPTIONASSELECT*FROMauthorsGOEXECsp_helptextencrypt_this下面是结果集：Theobjectscommentshavebeenencrypte

16、d.接下来，选择加密存储过程内容的标识号和文本。SELECTc.id,c.textFROMsyscommentscINNERJOINsysobjectsoONc.id=o.idWHEREo.name=encrypt_this下面是结果集：说明text列的输出显示在单独一行中。执行时，该信息将与id列信息出现在同一行中。idtext-1413580074?e?(1row(s)affected)H.创建用户定义的系统存储过程下面的示例创建一个过程，显示表名以emp开头的所有表及其对应的索引。如果没有指定参数，该过程将返回表名以sys开头的所有表（及索引）。IFEXISTS(SELECTnameFR

17、OMsysobjectsWHEREname=sp_showindexesANDtype=P)DROPPROCEDUREsp_showindexesGOUSEmasterGOCREATEPROCEDUREsp_showindexesTABLEvarchar(30)=sys*ASSELECTo.nameASTABLE_NAME,i.nameASINDEX_NAME,indidASINDEX_IDFROMsysindexesiINNERJOINsysobjectsoONo.id=i.idWHEREo.nameLIKETABLEGOUSEpubsEXECsp_showindexesemp*GO下面是结

18、果集：TABLE_NAMEINDEX_NAMEINDEX_ID-employeeemployee_ind1employeePK_emp_id2(2row(s)affected)8.使用延迟名称解析下面的示例显示四个过程以及延迟名称解析的各种可能使用方式。尽管引用的表或列在编译时不存在，但每个存储过程都可创建。IFEXISTS(SELECTnameFROMsysobjectsWHEREname=proc1ANDtype=P)DROPPROCEDUREproc1GO-Creatingaprocedureonanonexistenttable.USEpubsGOCREATEPROCEDUREproc

19、1ASSELECT*FROMdoes_not_existGO-Hereisthestatementtoactuallyseethetextoftheprocedure.SELECTo.id,c.textFROMsysobjectsoINNERJOINsyscommentscONo.id=c.idWHEREo.type=PANDo.name=proc1GOUSEmasterGOIFEXISTS(SELECTnameFROMsysobjectsWHEREname=proc2ANDtype=P)DROPPROCEDUREproc2GO-Creatingaprocedurethatattemptsto

20、retrieveinformationfroma-nonexistentcolumninanexistingtable.USEpubsGOCREATEPROCEDUREproc2ASDECLAREmiddle_initchar(1)SETmiddle_init=NULLSELECTau_id,middle_initial=middle_initFROMauthorsGO-Hereisthestatementtoactuallyseethetextoftheprocedure.SELECTo.id,c.textFROMsysobjectsoINNERJOINsyscommentscONo.id=c.idWHEREo.type=Pando.name=proc2