数据库存储过程的示例分析

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

/*存储过程可以看作是在数据库中的存储t-sql脚本为什么使用存储过程1、增加性能   本地存储发送的内容少、调用快、预编译、高速缓存       一般语句的执行：检查权限、检查语法，建立执行计划处理语句的要求       存储过程：创建时已经检查了语法；第一次执行的时候执行计划被创建，被编译；              再次执行时不需要重检查语法、不需要重编译、根据已经缓存的计划来决定是否需要重创建执行计划2、增强安全   加密、分离（权限设置，用户只需要有执行存储过程的权限，不需要有访问存储过程所使用的对象的权限）   3、在transact-sql中使用非数据库技术  dll4、编程模式--使用外部编程语言调用   1）input   2）output   3）feedback 状态代码或描述性的文本   4）模块化、可重用、可调用其他存储过程   5）隐藏程序逻辑，便于编程   6）可以调用动态连接库（外接的程序）基本原则：越简单越好 单一任务*//*分类1、系统存储过程    存在于master数据库，一般以sp_开头   提供对系统表格数据调用、数据库管理功能、安全管理功能的支持  --表格授权  use pubs  go  execute sp_table_privileges stores  --显示kylin\administrator的所有进程  execute sp_who @loginame='W2K3SERVER\Administrator'  --报告有关孤立的 microsoft windows nt 用户和组的信息，这些用户和组已不在 windows nt 环境中，但仍在 microsoft sql server系统表中拥有项。  execute sp_validatelogins2、本地存储过程   用户创建的解决特定问题的3、临时存储过程   存储于tempdb                    创建、调用时的数据库    使用范围           生存周期    #local                     不限数据库        创建时的连接有效    从创建时开始，当创建的连接中断时消失   ##global                   不限数据库        所有连接            从创建时开始，当创建的连接中断时消失   直接创建在tempdb的存储过程  tempdb            所有连接            从创建时开始，当数据库服务器服务停止时消失   create proc #local   as   select '#local'   go   exec #local   go   create proc ##global   as   select '##global'   go   exec ##global   go   use tempdb    go    create procedure directtemp    as    select * from [pubs].[dbo].[authors]    go   use northwind   go   exec tempdb.dbo.directtemp4、扩展存储过程  c++ xp   xp_sendmail既是系统存储过程，也是扩展存储过程   使用objectproperty来判断是否是扩展存储过程    use master    --扩展存储过程    select objectproperty(object_id('sp_prepare'), 'isextendedproc')    --非扩展存储过程    select objectproperty(object_id('xp_logininfo'), 'isextendedproc')5、远程存储过程   目前版本中只是为了向后兼容，已被分布式查询替代*//*存储过程在数据库中如何存储名字 sysobjects文本 syscomments *//*练习1：通过查询分析器中的对象查看器查看存储过程*//*练习2：查看存储过程的内容       图形       语句*/select * from sysobjectsselect * from syscomments goselect * from syscomments where id = object_id('custorderhist')goselect name,textfrom sysobjects inner join syscomments on sysobjects.id = syscomments.idwhere sysobjects.name = 'custorderhist'gosp_helptext sp_helptextgouse northwindgoexec sp_help custorderhistexec sp_helptext custorderhistexec sp_depends custorderhistexec sp_stored_procedures 'custorderhist' /*系统存储过程以使用为主*//*本地存储过程的创建、修改、删除1、t-sql语句create procedurealter proceduredrop procedurecreate procedure 存储过程名字as存储过程文本go alter procedure 存储过程名字as存储过程文本go  drop procedure 存储过程名字2、企业管理器  右键               向导*//*简单 */-- -- -- select top 1 * from products-- -- -- select top 1 * from orders-- -- -- select top 1 * from [order details]/*1、和视图比较*/alter  proc sp_qry_salesdetailsasselect a.productid as 商品编号,a.productname as 商品名称,b.unitprice as 数量,b.quantity as 价格,b.unitprice*b.quantity as 金额,c.requireddate as 销售时间from [order details] as b join products as aon b.productid=a.productidjoin orders as con b.orderid=c.orderidgoprint '测试'execute sp_qry_salesdetails--递归算法--视图  存储过程  函数alter view v_qry_salesdetailsasselect a.productid as 商品编号,a.productname as 商品名称,b.unitprice as 数量,b.quantity as 价格,b.unitprice*b.quantity as 金额,c.requireddate as 销售时间from [order details] as b join products as aon b.productid=a.productidjoin orders as con b.orderid=c.orderidprint '测试'select * from v_qry_salesdetails /*默认情况下第一次执行时的执行计划被保存，以后执行时都是用这个执行计划，直到服务器重启或存储过程使用的表格变化时当存储过程变化时，如：参数变化，需要重新编译、制定新的执行计划当每次调用存储过程时强制重新编译的方法：1、创建时指定 with recompile 2、sp_recompile */create procedure sp1as select * from customersexec sp1alter procedure sp1as select * from customersalter procedure sp1with recompileas select * from customerssp_recompile sp1--加密存储过程 with encryption select objectproperty(object_id('sp_qry_salesdetails'), 'isencrypted')/*删除存储过程drop proc */use northwindgocreate proc dbo.sp_dropprocasselect 'northwind.dbo.sp_dropproc'goexec northwind.dbo.sp_dropprocgouse mastergocreate proc dbo.sp_dropprocasselect 'master.dbo.sp_dropproc'goexec master.dbo.sp_dropprocgouse northwindgodrop proc sp_dropprocgoexec sp_dropprocexec master.dbo.sp_dropproc/*提供输入参数 input*/create proc qry_salesdetails @y int,@m int --varchar(10)asselect a.productid as 商品编号,a.productname as 商品名称,b.unitprice as 数量,b.quantity as 价格,b.unitprice*b.quantity as 金额,c.requireddate as 销售时间from [order details] as b join products as aon b.productid=a.productidjoin orders as con b.orderid=c.orderid--where convert(varchar(2),month(c.requireddate)) = @mwhere year(c.requireddate) = @y and month(c.requireddate) = @mgo exec qry_salesdetails 1996,9exec qry_salesdetails 9,1996exec qry_salesdetails @m=9,@y=1996exec qry_salesdetails @y=1996,@m=9go/*northwind 数据库orders order details 表格 *根据指定用户ID显示此用户在1996-07-01到1997-07-01之间的订货记录 要求存储过程文本加密 */use northwindgo--创建存储过程-- drop proc qry_showorders create proc qry_showorders @custid nchar(5)with encryption   --加密asif @custid is  null-- begin--   print '提供了不正确的参数'--   return-- endselect * from orders od inner join [order details] oddton od.orderid = oddt.orderidwhere shippeddate >='1996-07-01' and shippeddate <='1997-07-01'and od.customerid = @custidgo--调用、检验刚刚创建的存储过程exec qry_showorders @custid = 'vinet'exec qry_showorders nullgo--检查是否已经被加密exec sp_helptext qry_showorders/*返回值 output ,一个返回值变量一次只能有一个返回的值*/create proc testoutput @a varchar(10) outputasselect @a = 100godeclare @b varchar(10)--exec testoutput @b outputexec testoutput @a=@b outputselect @b--errorcreate proc sum_money @count money, @unitprice moneyas select  @count*@unitpricegodeclare @sum_temp money ,@sum_temp2 moneyset @sum_temp2 = exec sum_money @count= 1.1,@unitprice = 2.2 create proc sum_money @count money, @unitprice money ,@sum money outputas set @sum = @count*@unitpricegodeclare @sum_temp money ,@sum_temp2 moneyexec sum_money @count= 1.1,@unitprice = 2.2,@sum = @sum_temp outputset @sum_temp2= @sum_temp*100select @sum_temp2create proc test_output @in  nvarchar(100),@out nvarchar(100) outputasprint 'i''m @in  ' + @inset @out = @inprint 'i''m @out  '+@outgodeclare @i nvarchar(100),@o nvarchar(100)set @i = '让我们一起来测试'exec test_output @in = @i,@out = @o outputselect @o/*return 语句和错误处理*/--return 主要用来进行错误处理create proc testreturn @a intas if @a<0begin   return(-1)end else if @a = 0begin   return(0)end else begin   return(1)end godeclare @rtn intexec @rtn = testreturn @a=-100select @rtngo/*  @@error*/select @@errorgoselect 'a'+1goselect @@errorselect error, description from master.dbo.sysmessageswhere error = 245create proc testerroras select 'a'+1goexec testerrorgocreate proc testerrorasdeclare @e int,@a int ,@b intset @e = 0set @a = 1set @b = 0select @a/@bif @@error<>0begin   print '有错误'   set @e = @@errorend   return @egodeclare @er intexec @er = testerrorselect @er/*  @@rowcount*/select @@rowcountselect * from customersselect @@rowcount/*null 值*/create proc testreturn @a intas if @a is nullbegin   return(100)endelse if @a<0begin   return(-1)end else if @a = 0begin   return(0)end else begin   return(1)end /***************************************************************************************************************************特殊问题***************************************************************************************************************************//*关于sp_的命名*/use mastergocreate sp_testasselect '现在是master数据库'gouse northwindgocreate sp_testasselect '现在是northwind数据库'goexec sp_testexec master.dbo.sp_testdrop sp_test create proc sp1_testas select '这是master'gouse northwindgocreate proc sp1_testas select '这是northwind'exec  sp1_testdrop proc sp1_test/*命名延迟解决方案：创建存储过程时，应用的对象可以不存在，建议存储过程及引用的对象所有者都设置为dbo*/--按契约编程use northwindgocreate proc testdelayasselect * from tbldelaygoexec testdelay/*在创建存储过程时可以逻辑上形成组，以便作为同一个管理单元并在一个程序中使用*/create proc groupedproc;1 asselect 'groupedproc;1 'gocreate proc groupedproc;2asselect 'groupedproc;2 'gosp_helptext groupedprocgoexec groupedproc;1goexec groupedproc;2goexec groupedprocgodrop proc groupedproc/*存储过程嵌套，最多32层*/ create proc a asselect 'a'gocreate proc basselect 'b'exec a goexec b/*使用默认值*/-- -- drop proc testdefaultcreate proc testdefault @a int,@b int=2asselect @a,@bgoexec testdefault 1goexec testdefault @a=1exec testdefault 1,100/*在服务器启动时自动运行的存储过程要求：所有者是dbo，在master数据库中*/use northwindgocreate table start(dt datetime)gouse mastergocreate proc autostartasinsert into northwind.dbo.startvalues(getdate())go--设置为自动运行execute sp_procoption@procname = autostart,@optionname = startup,@optionvalue = truegouse master--判断是否自动运行select objectproperty(object_id('autostart'), 'execisstartup')goselect * from northwind.dbo.start--停止自动运行execute sp_procoption@procname = autostart,@optionname = startup,@optionvalue = falseexecute sp_configure@configname = 'scan for startup procs', @configvalue = 0reconfigurego/*扩展存储过程使用sp_addextendedproc 注册或使用企业管理器 在master 扩展存储过程*/-- -- -- -- exec xp_dirtree "D:\"-- -- -- -- -- -- ------msg 15281, level 16, state 1, procedure xp_cmdshell, line 1-- -- -- ------sql server blocked access to procedure 'sys.xp_cmdshell' of component 'xp_cmdshell' because this component is turned off as part of the security configuration for this server. a system administrator can enable the use of 'xp_cmdshell' by using sp_configure. for more information about enabling 'xp_cmdshell', see "surface area configuration" in sql server books online. ---exec  xp_cmdshell "dir *.exe"-- -- -- -- -- -- exec  xp_cmdshell tree-- -- -- /*  练习：向northwind数据库中的customers 表格插入记录的存储过程  名字insertcust*/select insertupdatedeletecreate proc insertcust @custid nchar(5),                       @cmpnm nvarchar(40),                       @cntnm nvarchar(30),                       @cntttl nvarchar(30),                       @addr nvarchar(60),                        @city nvarchar(15),                       @rg nvarchar(15),                       @pscd nvarchar(10),                       @cntry nvarchar(15),                       @phone nvarchar(24),                       @fax nvarchar(24)as --业务逻辑insert into customers(customerid,companyname,contactname,contacttitle,address,city,region,postalcode,country,phone,fax)values(@custid,@cmpnm,@cntnm,@cntttl,@addr,@city,@rg,@pscd,@cntry,@phone,@fax)goexec insertcust @custid='abcd',@cmpnm='abc company',@cntnm='anyone',@cntttl='mr.',@addr='anywhere',                @city='shanghai',@rg='huangpu',@pscd='200000',@cntry='chian',@phone='021-88888888',@fax='021-66666666'go--简单实现create proc createcustid  @id nchar(5) outputas  --自动产生客户IDcreate proc insertcust                        @cmpnm nvarchar(40),                       @cntnm nvarchar(30),                       @cntttl nvarchar(30),                       @addr nvarchar(60),                        @city nvarchar(15),                       @rg nvarchar(15),                       @pscd nvarchar(10),                       @cntry nvarchar(15),                       @phone nvarchar(24),                       @fax nvarchar(24)as declare @id nchar(t5)exec createcustid  @id outputinsert into customers(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*/------------------------------------------------------------------------------------------------------------------------set nocount offselect 'a'go-- -- -- set nocount onselect 'a'/*动态语句的使用--动态条件*/create proc qry_salesdetails @no int = -1,@start char(10),@end char(10)asselect a.productid as 商品编号,a.productname as 商品名称,b.unitprice as 数量,b.quantity as 价格,b.unitprice*b.quantity as 金额,c.requireddate as 销售时间         from [order details] as b join products as a    on b.productid=a.productid    join orders as c    on b.orderid=c.orderidwhere  a.productid= @no and c.requireddate<=@end and c.requireddate>=@startgoexec qry_salesdetails 6,'1996-01-01','1997-01-01'alter proc qry_salesdetails @no int = -1,@start char(10),@end char(10)as declare @sql varchar(4000)set @sql = 'select a.productid as 商品编号,a.productname as 商品名称,b.unitprice as 数量,b.quantity as 价格,b.unitprice*b.quantity as 金额,c.requireddate as 销售时间         from [order details] as b join products as a    on b.productid=a.productid    join orders as c    on b.orderid=c.orderid  where 1=1  'if @no is not null     set @sql = @sql + ' and  a.productid = '+convert(varchar(10),@no)if @start is not null  and  @end is not null     set @sql = @sql    + ' and c.requireddate >=  '''+ @start+''''                        + ' and c.requireddate <= '''+ @end+''''--print @sqlexec(@sql)print ''''go exec qry_salesdetails @end=null,@start=nullexec qry_salesdetails @no=35,@end=null,@start=nullexec qry_salesdetails @no=null,@end='1997-07-01',@start='1996-07-01'exec qry_salesdetails @no=38,@end='1997-07-01',@start='1996-07-01'sp_stored_procedures qry_salesdetails/*临时表的使用年度销售汇总表月汇总年汇总*/drop table tempdb..#tempgocreate table #temp (商品编号 varchar(100),商品名称  varchar(100),金额 money,销售时间 datetime,排序 int)insert into #tempselect a.productid as 商品编号,a.productname as 商品名称,       b.unitprice*b.quantity as 金额,c.requireddate as 销售时间,       month(c.requireddate)from [order details] as b join products as aon b.productid=a.productidjoin orders as con b.orderid=c.orderidwhere year(c.requireddate) = 1996insert into #temp(商品编号,金额,排序)select '月汇总',sum(金额),month(销售时间)from #tempgroup by year(销售时间),month(销售时间) insert into #temp(商品编号,金额,排序)select '年汇总',sum(金额),12from #tempwhere 销售时间 is not nullselect * from #temporder by 排序 ,商品名称 descselect * from #tempdrop table tempdb..#temp

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