《SQL存储过程全面实例讲解》由会员分享，可在线阅读，更多相关《SQL存储过程全面实例讲解（9页珍藏版）》请在金锄头文库上搜索。
1、SQL实例讲解一、 创建存储过程结构CREATEPROCEDURE创建存储过程，存储过程是保存起来的可以接受和返回用户提供的参数的Transact-SQL语句的集合。可以创建一个过程供永久使用，或在一个会话中临时使用（局部临时过程），或在所有会话中临时使用（全局临时过程）。也可以创建在MicrosoftSQLServer启动时自动运行的存储过程。语法CREATEPROCEDUREprocedure_name;numberparameterdata_typeVARYING=defaultOUTPUT,.nWITHRECOMPILE|ENCRYPTION|RECOMPILE,ENCRYPTIONF
2、ORREPLICATIONASsql_statement.n二、 存储过程实例讲解1.使用带有复杂SELECT语句的简单过程下面的存储过程从四个表的联接中返回所有作者（提供了姓名）、出版的书籍以及出版社。该存储过程不使用任何参数。USEpubsIFEXISTS(SELECTnameFROMsysobjectsWHEREname=au_info_allANDtype=P)DROPPROCEDUREau_info_allGOCREATEPROCEDUREau_info_allASSELECTau_lname,au_fname,title,pub_nameFROMauthorsaINNERJOINt
3、itleauthortaONa.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(SEL
4、ECTnameFROMsysobjectsWHEREname=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
5、_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_info2GOUSEpubsGOC
7、REATEPROCEDUREau_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)，并使用一个可选的输入参数和一个输出参数。首先，创建过程：USEpubsGOIFEXIS
9、TS(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-TheBusyExecutivesDatab
11、aseGuideTheGourmetMicrowaveThePsychologyofComputerCooking(3row(s)affected)Warning,nullvalueeliminatedfromaggregate.Allofthesetitlescanbepurchasedforlessthan$200.5.使用OUTPUT游标参数OUTPUT游标参数用来将存储过程的局部游标传递回调用批处理、存储过程或触发器。首先，创建以下过程，在titles表上声明并打开一个游标：USEpubsIFEXISTS(SELECTnameFROMsysobjectsWHEREname=titles
12、_cursorandtype=P)DROPPROCEDUREtitles_cursorGOCREATEPROCEDUREtitles_cursortitles_cursorCURSORVARYINGOUTPUTASSETtitles_cursor=CURSORFORWARD_ONLYSTATICFORSELECT*FROMtitlesOPENtitles_cursorGO接下来，执行一个批处理，声明一个局部游标变量，执行上述过程以将游标赋值给局部变量，然后从该游标提取行。USEpubsGODECLAREMyCursorCURSOREXECtitles_cursortitles_cursor=MyCu
