这篇文章给大家分享的是有关数据库存储过程的示例分析的内容。小编觉得挺实用的，因此分享给大家做个参考，一起跟随小编过来看看吧。

/*存储过程可以看作是在数据库中的存储t-sql脚本为什么使用存储过程1、增加性能本地存储发送的内容少、调用快、预编译、高速缓存一般语句的执行：检查权限、检查语法，建立执行计划处理语句的要求存储过程：创建时已经检查了语法；第一次执行的时候执行计划被创建，被编译；再次执行时不需要重检查语法、不需要重编译、根据已经缓存的计划来决定是否需要重创建执行计划2、增强安全加密、分离（权限设置，用户只需要有执行存储过程的权限，不需要有访问存储过程所使用的对象的权限）3、在transact-sql中使用非数据库技术dll4、编程模式——使用外部编程语言调用1）input2）output3）feedback状态代码或描述性的文本4）模块化、可重用、可调用其他存储过程5）隐藏程序逻辑，便于编程6）可以调用动态连接库（外接的程序）基本原则：越简单越好单一任务*//*分类1、系统存储过程存在于master数据库，一般以sp_开头提供对系统表格数据调用、数据库管理功能、安全管理功能的支持--表格授权usepubsgoexecutesp_table_privilegesstores--显示kylin\administrator的所有进程executesp_who@loginame='W2K3SERVER\Administrator'--报告有关孤立的microsoftwindowsnt用户和组的信息，这些用户和组已不在windowsnt环境中，但仍在microsoftsqlserver系统表中拥有项。executesp_validatelogins2、本地存储过程用户创建的解决特定问题的3、临时存储过程存储于tempdb创建、调用时的数据库使用范围生存周期#local不限数据库创建时的连接有效从创建时开始，当创建的连接中断时消失##global不限数据库所有连接从创建时开始，当创建的连接中断时消失直接创建在tempdb的存储过程tempdb所有连接从创建时开始，当数据库服务器服务停止时消失createproc#localasselect'#local'goexec#localgocreateproc##globalasselect'##global'goexec##globalgousetempdbgocreateproceduredirecttempasselect*from[pubs].[dbo].[authors]gousenorthwindgoexectempdb.dbo.directtemp4、扩展存储过程c++xpxp_sendmail既是系统存储过程，也是扩展存储过程使用objectproperty来判断是否是扩展存储过程usemaster--扩展存储过程selectobjectproperty(object_id('sp_prepare'),'isextendedproc')--非扩展存储过程selectobjectproperty(object_id('xp_logininfo'),'isextendedproc')5、远程存储过程目前版本中只是为了向后兼容，已被分布式查询替代*//*存储过程在数据库中如何存储名字sysobjects文本syscomments*//*练习1：通过查询分析器中的对象查看器查看存储过程*//*练习2：查看存储过程的内容图形语句*/select*fromsysobjectsselect*fromsyscommentsgoselect*fromsyscommentswhereid=object_id('custorderhist')goselectname,textfromsysobjectsinnerjoinsyscommentsonsysobjects.id=syscomments.idwheresysobjects.name='custorderhist'gosp_helptextsp_helptextgousenorthwindgoexecsp_helpcustorderhistexecsp_helptextcustorderhistexecsp_dependscustorderhistexecsp_stored_procedures'custorderhist'/*系统存储过程以使用为主*//*本地存储过程的创建、修改、删除1、t-sql语句createprocedurealterproceduredropprocedurecreateprocedure存储过程名字as存储过程文本goalterprocedure存储过程名字as存储过程文本godropprocedure存储过程名字2、企业管理器右键向导*//*简单*/------selecttop1*fromproducts------selecttop1*fromorders------selecttop1*from[orderdetails]/*1、和视图比较*/alterprocsp_qry_salesdetailsasselecta.productidas商品编号,a.productnameas商品名称,b.unitpriceas数量,b.quantityas价格,b.unitprice*b.quantityas金额,c.requireddateas销售时间from[orderdetails]asbjoinproductsasaonb.productid=a.productidjoinordersasconb.orderid=c.orderidgoprint'测试'executesp_qry_salesdetails--递归算法--视图存储过程函数alterviewv_qry_salesdetailsasselecta.productidas商品编号,a.productnameas商品名称,b.unitpriceas数量,b.quantityas价格,b.unitprice*b.quantityas金额,c.requireddateas销售时间from[orderdetails]asbjoinproductsasaonb.productid=a.productidjoinordersasconb.orderid=c.orderidprint'测试'select*fromv_qry_salesdetails/*默认情况下第一次执行时的执行计划被保存，以后执行时都是用这个执行计划，直到服务器重启或存储过程使用的表格变化时当存储过程变化时，如：参数变化，需要重新编译、制定新的执行计划当每次调用存储过程时强制重新编译的方法：1、创建时指定withrecompile2、sp_recompile*/createproceduresp1asselect*fromcustomersexecsp1alterproceduresp1asselect*fromcustomersalterproceduresp1withrecompileasselect*fromcustomerssp_recompilesp1--加密存储过程withencryptionselectobjectproperty(object_id('sp_qry_salesdetails'),'isencrypted')/*删除存储过程dropproc*/usenorthwindgocreateprocdbo.sp_dropprocasselect'northwind.dbo.sp_dropproc'goexecnorthwind.dbo.sp_dropprocgousemastergocreateprocdbo.sp_dropprocasselect'master.dbo.sp_dropproc'goexecmaster.dbo.sp_dropprocgousenorthwindgodropprocsp_dropprocgoexecsp_dropprocexecmaster.dbo.sp_dropproc/*提供输入参数input*/createprocqry_salesdetails@yint,@mint--varchar(10)asselecta.productidas商品编号,a.productnameas商品名称,b.unitpriceas数量,b.quantityas价格,b.unitprice*b.quantityas金额,c.requireddateas销售时间from[orderdetails]asbjoinproductsasaonb.productid=a.productidjoinordersasconb.orderid=c.orderid--whereconvert(varchar(2),month(c.requireddate))=@mwhereyear(c.requireddate)=@yandmonth(c.requireddate)=@mgoexecqry_salesdetails1996,9execqry_salesdetails9,1996execqry_salesdetails@m=9,@y=1996execqry_salesdetails@y=1996,@m=9go/*northwind数据库ordersorderdetails表格*根据指定用户ID显示此用户在1996-07-01到1997-07-01之间的订货记录要求存储过程文本加密*/usenorthwindgo--创建存储过程--dropprocqry_showorderscreateprocqry_showorders@custidnchar(5)withencryption--加密asif@custidisnull--begin--print'提供了不正确的参数'--return--endselect*fromordersodinnerjoin[orderdetails]oddtonod.orderid=oddt.orderidwhereshippeddate>='1996-07-01'andshippeddate<='1997-07-01'andod.customerid=@custidgo--调用、检验刚刚创建的存储过程execqry_showorders@custid='vinet'execqry_showordersnullgo--检查是否已经被加密execsp_helptextqry_showorders/*返回值output,一个返回值变量一次只能有一个返回的值*/createproctestoutput@avarchar(10)outputasselect@a=100godeclare@bvarchar(10)--exectestoutput@boutputexectestoutput@a=@boutputselect@b--errorcreateprocsum_money@countmoney,@unitpricemoneyasselect@count*@unitpricegodeclare@sum_tempmoney,@sum_temp2moneyset@sum_temp2=execsum_money@count=1.1,@unitprice=2.2createprocsum_money@countmoney,@unitpricemoney,@summoneyoutputasset@sum=@count*@unitpricegodeclare@sum_tempmoney,@sum_temp2moneyexecsum_money@count=1.1,@unitprice=2.2,@sum=@sum_tempoutputset@sum_temp2=@sum_temp*100select@sum_temp2createproctest_output@innvarchar(100),@outnvarchar(100)outputasprint'i''m@in'+@inset@out=@inprint'i''m@out'+@outgodeclare@invarchar(100),@onvarchar(100)set@i='让我们一起来测试'exectest_output@in=@i,@out=@ooutputselect@o/*return语句和错误处理*/--return主要用来进行错误处理createproctestreturn@aintasif@a<0beginreturn(-1)endelseif@a=0beginreturn(0)endelsebeginreturn(1)endgodeclare@rtnintexec@rtn=testreturn@a=-100select@rtngo/*@@error*/select@@errorgoselect'a'+1goselect@@errorselecterror,descriptionfrommaster.dbo.sysmessageswhereerror=245createproctesterrorasselect'a'+1goexectesterrorgocreateproctesterrorasdeclare@eint,@aint,@bintset@e=0set@a=1set@b=0select@a/@bif@@error<>0beginprint'有错误'set@e=@@errorendreturn@egodeclare@erintexec@er=testerrorselect@er/*@@rowcount*/select@@rowcountselect*fromcustomersselect@@rowcount/*null值*/createproctestreturn@aintasif@aisnullbeginreturn(100)endelseif@a<0beginreturn(-1)endelseif@a=0beginreturn(0)endelsebeginreturn(1)end/***************************************************************************************************************************特殊问题***************************************************************************************************************************//*关于sp_的命名*/usemastergocreatesp_testasselect'现在是master数据库'gousenorthwindgocreatesp_testasselect'现在是northwind数据库'goexecsp_testexecmaster.dbo.sp_testdropsp_testcreateprocsp1_testasselect'这是master'gousenorthwindgocreateprocsp1_testasselect'这是northwind'execsp1_testdropprocsp1_test/*命名延迟解决方案：创建存储过程时，应用的对象可以不存在，建议存储过程及引用的对象所有者都设置为dbo*/--按契约编程usenorthwindgocreateproctestdelayasselect*fromtbldelaygoexectestdelay/*在创建存储过程时可以逻辑上形成组，以便作为同一个管理单元并在一个程序中使用*/createprocgroupedproc;1asselect'groupedproc;1'gocreateprocgroupedproc;2asselect'groupedproc;2'gosp_helptextgroupedprocgoexecgroupedproc;1goexecgroupedproc;2goexecgroupedprocgodropprocgroupedproc/*存储过程嵌套，最多32层*/createprocaasselect'a'gocreateprocbasselect'b'execagoexecb/*使用默认值*/----dropproctestdefaultcreateproctestdefault@aint,@bint=2asselect@a,@bgoexectestdefault1goexectestdefault@a=1exectestdefault1,100/*在服务器启动时自动运行的存储过程要求：所有者是dbo，在master数据库中*/usenorthwindgocreatetablestart(dtdatetime)gousemastergocreateprocautostartasinsertintonorthwind.dbo.startvalues(getdate())go--设置为自动运行executesp_procoption@procname=autostart,@optionname=startup,@optionvalue=truegousemaster--判断是否自动运行selectobjectproperty(object_id('autostart'),'execisstartup')goselect*fromnorthwind.dbo.start--停止自动运行executesp_procoption@procname=autostart,@optionname=startup,@optionvalue=falseexecutesp_configure@configname='scanforstartupprocs',@configvalue=0reconfigurego/*扩展存储过程使用sp_addextendedproc注册或使用企业管理器在master扩展存储过程*/--------execxp_dirtree"D:\"------------------msg15281,level16,state1,procedurexp_cmdshell,line1------------sqlserverblockedaccesstoprocedure'sys.xp_cmdshell'ofcomponent'xp_cmdshell'becausethiscomponentisturnedoffaspartofthesecurityconfigurationforthisserver.asystemadministratorcanenabletheuseof'xp_cmdshell'byusingsp_configure.formoreinformationaboutenabling'xp_cmdshell',see"surfaceareaconfiguration"insqlserverbooksonline.---execxp_cmdshell"dir*.exe"------------execxp_cmdshelltree------/*练习：向northwind数据库中的customers表格插入记录的存储过程名字insertcust*/selectinsertupdatedeletecreateprocinsertcust@custidnchar(5),@cmpnmnvarchar(40),@cntnmnvarchar(30),@cntttlnvarchar(30),@addrnvarchar(60),@citynvarchar(15),@rgnvarchar(15),@pscdnvarchar(10),@cntrynvarchar(15),@phonenvarchar(24),@faxnvarchar(24)as--业务逻辑insertintocustomers(customerid,companyname,contactname,contacttitle,address,city,region,postalcode,country,phone,fax)values(@custid,@cmpnm,@cntnm,@cntttl,@addr,@city,@rg,@pscd,@cntry,@phone,@fax)goexecinsertcust@custid='abcd',@cmpnm='abccompany',@cntnm='anyone',@cntttl='mr.',@addr='anywhere',@city='shanghai',@rg='huangpu',@pscd='200000',@cntry='chian',@phone='021-88888888',@fax='021-66666666'go--简单实现createproccreatecustid@idnchar(5)outputas--自动产生客户IDcreateprocinsertcust@cmpnmnvarchar(40),@cntnmnvarchar(30),@cntttlnvarchar(30),@addrnvarchar(60),@citynvarchar(15),@rgnvarchar(15),@pscdnvarchar(10),@cntrynvarchar(15),@phonenvarchar(24),@faxnvarchar(24)asdeclare@idnchar(t5)execcreatecustid@idoutputinsertintocustomers(customerid,companyname,contactname,contacttitle,address,city,region,postalcode,country,phone,fax)values(@id,@cmpnm,@cntnm,@cntttl,@addr,@city,@rg,@pscd,@cntry,@phone,@fax)go/*其他要考虑的因素：customerid自动生成如果重复怎么处理？生成新id？电话号码格式不正确如何处理？return*/------------------------------------------------------------------------------------------------------------------------setnocountoffselect'a'go------setnocountonselect'a'/*动态语句的使用——动态条件*/createprocqry_salesdetails@noint=-1,@startchar(10),@endchar(10)asselecta.productidas商品编号,a.productnameas商品名称,b.unitpriceas数量,b.quantityas价格,b.unitprice*b.quantityas金额,c.requireddateas销售时间from[orderdetails]asbjoinproductsasaonb.productid=a.productidjoinordersasconb.orderid=c.orderidwherea.productid=@noandc.requireddate<=@endandc.requireddate>=@startgoexecqry_salesdetails6,'1996-01-01','1997-01-01'alterprocqry_salesdetails@noint=-1,@startchar(10),@endchar(10)asdeclare@sqlvarchar(4000)set@sql='selecta.productidas商品编号,a.productnameas商品名称,b.unitpriceas数量,b.quantityas价格,b.unitprice*b.quantityas金额,c.requireddateas销售时间from[orderdetails]asbjoinproductsasaonb.productid=a.productidjoinordersasconb.orderid=c.orderidwhere1=1'if@noisnotnullset@sql=@sql+'anda.productid='+convert(varchar(10),@no)if@startisnotnulland@endisnotnullset@sql=@sql+'andc.requireddate>='''+@start+''''+'andc.requireddate<='''+@end+''''--print@sqlexec(@sql)print''''goexecqry_salesdetails@end=null,@start=nullexecqry_salesdetails@no=35,@end=null,@start=nullexecqry_salesdetails@no=null,@end='1997-07-01',@start='1996-07-01'execqry_salesdetails@no=38,@end='1997-07-01',@start='1996-07-01'sp_stored_proceduresqry_salesdetails/*临时表的使用年度销售汇总表月汇总年汇总*/droptabletempdb..#tempgocreatetable#temp(商品编号varchar(100),商品名称varchar(100),金额money,销售时间datetime,排序int)insertinto#tempselecta.productidas商品编号,a.productnameas商品名称,b.unitprice*b.quantityas金额,c.requireddateas销售时间,month(c.requireddate)from[orderdetails]asbjoinproductsasaonb.productid=a.productidjoinordersasconb.orderid=c.orderidwhereyear(c.requireddate)=1996insertinto#temp(商品编号,金额,排序)select'月汇总',sum(金额),month(销售时间)from#tempgroupbyyear(销售时间),month(销售时间)insertinto#temp(商品编号,金额,排序)select'年汇总',sum(金额),12from#tempwhere销售时间isnotnullselect*from#temporderby排序,商品名称descselect*from#tempdroptabletempdb..#temp

感谢各位的阅读！关于“数据库存储过程的示例分析”这篇文章就分享到这里了，希望以上内容可以对大家有一定的帮助，让大家可以学到更多知识，如果觉得文章不错，可以把它分享出去让更多的人看到吧！