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

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

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

2、YPTION FORREPLICATION ASsql_statement.n 二、存储过程实例讲解 1.使用带有复杂 SELECT 语句的简单过程下面的存储过程从四个表的联接中返回所有作者（提供了*）、出版的书籍以及。该存储过程不使用任何参数。USEpubs IFE*ISTS(SELECTnameFROMsysobjectsWHEREname=au_info_allANDtype=P)DROPPROCEDUREau_info_all GO CREATEPROCEDUREau_info_all AS SELECTau_lname,au_fname,title,pub_nameFROMautho

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

4、s IFE*ISTS(SELECTnameFROMsysobjectsWHEREname=au_infoANDtype=P)DROPPROCEDUREau_info GO USEpubsGO CREATEPROCEDUREau_infolastnamevarchar(40),firstnamevarchar(20)AS SELECTau_lname,au_fname,title,pub_nameFROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_idINNERJOINtitlestONt.title_id=ta.title_idINNERJOIN

5、publisherspONt.pub_id=p.pub_idWHEREau_fname=firstnameANDau_lname=lastname GOau_info 存储过程可以通过以下方法执行：E*ECUTEau_infoDull,Ann-Or-.z.E*ECUTEau_infolastname=Dull,firstname=Ann-Or E*ECUTEau_infofirstname=Ann,lastname=Dull-Or E*ECau_infoDull,Ann-Or E*ECau_infolastname=Dull,firstname=Ann-Or E*ECau_infofirstn

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

7、EDUREau_info2 GO USEpubs GO CREATEPROCEDUREau_info2lastnamevarchar(30)=D*,firstnamevarchar(18)=*AS SELECTau_lname,au_fname,title,pub_name FROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_idINNERJOINtitlestONt.title_id=ta.title_idINNERJOINpublisherspONt.pub_id=p.pub_id WHEREau_fnameLIKEfirstnameANDa

8、u_lnameLIKElastname GO au_info2 存储过程可以用多种组合执行。下面只列出了部分组合：E*ECUTEau_info2-Or E*ECUTEau_info2Wh*-Or E*ECUTEau_info2firstname=A*-Or E*ECUTEau_info2CKarsOEn-OrE*ECUTEau_info2Hunter,Sheryl-Or E*ECUTEau_info2H*,S*4.使用 OUTPUT 参数 OUTPUT 参数允许外部过程、批处理或多条 Transact-SQL 语句访问在过程执行期间设置的*个值。下面的示例创建一个存储过程(titles_sum

9、)，并使用一个可选的输入参数和一个输出参数。首先，创建过程：USEpubs GOIFE*ISTS(SELECTnameFROMsysobjectsWHEREname=titles_sumANDtype=P)DROPPROCEDUREtitles_sum GO-.z.USEpubs GO CREATEPROCEDUREtitles_sumTITLEvarchar(40)=*,SUMmoneyOUTPUT AS SELECTTitleName=title FROMtitles WHEREtitleLIKETITLE SELECTSUM=SUM(price)FROMtitles WHEREtitle

10、LIKETITLE GO 接下来，将该 OUTPUT 参数用于控制流语言。说明 OUTPUT 变量必须在创建表和使用该变量时都进行定义。参数名和变量名不一定要匹配，不过数据类型和参数位置必须匹配（除非使用 SUM=variable 形式）。DECLARETOTALCOSTmoney E*ECUTEtitles_sumThe*,TOTALCOSTOUTPUT IFTOTALCOST200 BEGINPRINTPRINTAllofthesetitlescanbepurchasedforlessthan$200.END ELSESELECTThetotalcostofthesetitlesis$+R

11、TRIM(CAST(TOTALCOSTASvarchar(20)下面是结果集：TitleName-TheBusyE*ecutivesDatabaseGuide TheGourmetMicrowave ThePsychologyofputerCooking(3row(s)affected)Warning,nullvalueeliminatedfromaggregate.Allofthesetitlescanbepurchasedforlessthan$200.5.使用 OUTPUT 游标参数 OUTPUT 游标参数用来将存储过程的局部游标传递回调用批处理、存储过程或触发器。首先，创建以下过程，在

12、 titles 表上声明并打开一个游标：USEpubs IFE*ISTS(SELECTnameFROMsysobjectsWHEREname=titles_cursorandtype=P)DROPPROCEDUREtitles_cursor GO CREATEPROCEDUREtitles_cursortitles_cursorCURSORVARYINGOUTPUT AS SETtitles_cursor=CURSOR FORWARD_ONLYSTATICFOR SELECT*FROMtitlesOPENtitles_cursor GO 接下来，执行一个批处理，声明一个局部游标变量，执行上述过

13、程以将游标赋值给局部变量，然后从该游标提取行。USEpubs GODECLAREMyCursorCURSOR E*ECtitles_cursortitles_cursor=MyCursorOUTPUT-.z.WHILE(FETCH_STATUS=0)BEGINFETCHNE*TFROMMyCursor END CLOSEMyCursor DEALLOCATEMyCursor GO6.使用 WITHREPILE 选项如果为过程提供的参数不是典型的参数，并且新的执行计划不应高速缓存或存储在内存中，WITHREPILE 子句会很有帮助。USEpubs IFE*ISTS(SELECTnameFROMs

14、ysobjectsWHEREname=titles_by_authorANDtype=P)DROPPROCEDUREtitles_by_author GO CREATEPROCEDUREtitles_by_authorLNAME_PATTERNvarchar(30)=*WITHREPILE AS SELECTRTRIM(au_fname)+RTRIM(au_lname)ASAuthorsfullname,titleASTitle FROMauthorsaINNERJOINtitleauthortaONa.au_id=ta.au_idINNERJOINtitlestONta.title_id=t

15、.title_id WHEREau_lnameLIKELNAME_PATTERN GO7.使用 WITHENCRYPTION 选项 WITHENCRYPTION 子句对用户隐藏存储过程的文本。下例创建加密过程，使用sp_helpte*t 系统存储过程获取关于加密过程的信息，然后尝试直接从 sysments 表中获取关于该过程的信息。IFE*ISTS(SELECTnameFROMsysobjectsWHEREname=encrypt_thisANDtype=P)DROPPROCEDUREencrypt_this GO USEpubs GO CREATEPROCEDUREencrypt_this

16、WITHENCRYPTION ASSELECT*FROMauthors GO E*ECsp_helpte*tencrypt_this 下面是结果集：Theobjectsmentshavebeenencrypted.接下来，选择加密存储过程内容的标识号和文本。SELECTc.id,c.te*t FROMsysmentscINNERJOINsysobjectsoONc.id=o.id WHEREo.name=encrypt_this下面是结果集：说明 te*t 列的输出显示在单独一行中。执行时，该信息将与 id 列信息出现在同一行中。idte*t-1413580074?e?(1row(s)affe

17、cted)H.创建用户定义的系统存储过程下面的示例创建一个过程，显示表名以 emp 开头的所有表及其对应的索引。如果没有指定参数，该过程将返回表名以 sys开头的所有表（及索引）。-.z.IFE*ISTS(SELECTnameFROMsysobjectsWHEREname=sp_showinde*esANDtype=P)DROPPROCEDUREsp_showinde*es GO USEmaster GO CREATEPROCEDUREsp_showinde*esTABLEvarchar(30)=sys*ASSELECTo.nameASTABLE_NAME,i.nameASINDE*_NAME

18、,indidASINDE*_ID FROMsysinde*esiINNERJOINsysobjectsoONo.id=i.id WHEREo.nameLIKETABLE GO USEpubs E*ECsp_showinde*esemp*GO 下面是结果集：TABLE_NAMEINDE*_NAMEINDE*_ID-employeeemployee_ind1 employeePK_emp_id2(2row(s)affected)8.使用延迟名称解析下面的示例显示四个过程以及延迟名称解析的各种可能使用方式。尽管引用的表或列在编译时不存在，但每个存储过程都可创建。IFE*ISTS(SELECTname

19、FROMsysobjectsWHEREname=proc1ANDtype=P)DROPPROCEDUREproc1 GO-Creatingaprocedureonanone*istenttable.USEpubs GO CREATEPROCEDUREproc1 ASSELECT*FROMdoes_not_e*ist GO-Hereisthestatementtoactuallyseethete*toftheprocedure.SELECTo.id,c.te*t FROMsysobjectsoINNERJOINsysmentscONo.id=c.id WHEREo.type=PANDo.name

20、=proc1 GO USEmaster GOIFE*ISTS(SELECTnameFROMsysobjectsWHEREname=proc2ANDtype=P)DROPPROCEDUREproc2 GO-Creatingaprocedurethatattemptstoretrieveinformationfroma-none*istentcolumninane*istingtable.USEpubs GO CREATEPROCEDUREproc2 ASDECLAREmiddle_initchar(1)SETmiddle_init=NULLSELECTau_id,middle_initial=middle_initFROMauthors-.z.GO-Hereisthestatementtoactuallyseethete*toftheprocedure.SELECTo.id,c.te*t FROMsysobjectsoINNERJOINsysmentscONo.id=c.id WHEREo.type=Pando.name=proc2