数据库存储过程详解

/* 存储过程可以看作是在数据库中的存储t-sql脚本  为什么使用存储过程 1、增加性能   本地存储发送的内容少、调用快、预编译、高速缓存        一般语句的执行：检查权限、检查语法，建立执行计划处理语句的要求        存储过程：创建时已经检查了语法；第一次执行的时候执行计划被创建，被编译；               再次执行时不需要重检查语法、不需要重编译、根据已经缓存的计划来决定是否需要重创建执行计划 2、增强安全   加密、分离（权限设置，用户只需要有执行存储过程的权限，不需要有访问存储过程所使用的对象的权限）     3、在transact-sql中使用非数据库技术  dll 4、编程模式——使用外部编程语言调用    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_validatelogins 2、本地存储过程   用户创建的解决特定问题的 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.directtemp  4、扩展存储过程  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 sysobjects select * from syscomments  go select * from syscomments  where id = object_id('custorderhist') go select name,text from sysobjects inner join syscomments  on sysobjects.id = syscomments.id where sysobjects.name = 'custorderhist' go sp_helptext sp_helptext go use northwind go exec sp_help custorderhist exec sp_helptext custorderhist exec sp_depends custorderhist exec sp_stored_procedures 'custorderhist'    /* 系统存储过程 以使用为主 */  /* 本地存储过程的创建、修改、删除 1、t-sql语句 create procedure alter procedure drop procedure  create 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_salesdetails as 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 go print '测试'  execute sp_qry_salesdetails  --递归算法 --视图  存储过程  函数 alter view v_qry_salesdetails as 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 print '测试'  select * from v_qry_salesdetails  /* 默认情况下第一次执行时的执行计划被保存，以后执行时都是用这个执行计划，直到服务器重启或存储过程使用的表格变化时 当存储过程变化时，如：参数变化，需要重新编译、制定新的执行计划 当每次调用存储过程时强制重新编译的方法： 1、创建时指定 with recompile  2、sp_recompile  */ create procedure sp1 as  select * from customers  exec sp1  alter procedure sp1 as  select * from customers  alter procedure sp1 with recompile as  select * from customers  sp_recompile sp1  --加密存储过程 with encryption  select objectproperty(object_id('sp_qry_salesdetails'), 'isencrypted')  /* 删除存储过程 drop proc  */ use northwind go create proc dbo.sp_dropproc as select 'northwind.dbo.sp_dropproc' go exec northwind.dbo.sp_dropproc go use master go create proc dbo.sp_dropproc as select 'master.dbo.sp_dropproc' go exec master.dbo.sp_dropproc go use northwind go drop proc sp_dropproc go exec sp_dropproc exec master.dbo.sp_dropproc  /* 提供输入参数 input */ create proc qry_salesdetails @y int,@m int --varchar(10) as 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 convert(varchar(2),month(c.requireddate)) = @m where year(c.requireddate) = @y and month(c.requireddate) = @m go  exec qry_salesdetails 1996,9 exec qry_salesdetails 9,1996 exec qry_salesdetails @m=9,@y=1996 exec qry_salesdetails @y=1996,@m=9 go /* northwind 数据库 orders order details 表格 * 根据指定用户ID显示此用户在1996-07-01到1997-07-01之间的订货记录  要求存储过程文本加密  */   use northwind go --创建存储过程 -- drop proc qry_showorders  create proc qry_showorders @custid nchar(5) with encryption   --加密 as if @custid is  null -- begin --   print '提供了不正确的参数' --   return -- end select *  from orders od inner join [order details] oddt on od.orderid = oddt.orderid where shippeddate >='1996-07-01' and shippeddate <='1997-07-01' and od.customerid = @custid go --调用、检验刚刚创建的存储过程 exec qry_showorders @custid = 'vinet' exec qry_showorders null go --检查是否已经被加密 exec sp_helptext qry_showorders  /* 返回值 output ,一个返回值变量一次只能有一个返回的值 */ create proc testoutput @a varchar(10) output as select @a = 100 go declare @b varchar(10) --exec testoutput @b output exec testoutput @a=@b output select @b --error create proc sum_money @count money, @unitprice money as  select  @count*@unitprice go declare @sum_temp money ,@sum_temp2 money set @sum_temp2 = exec sum_money @count= 1.1,@unitprice = 2.2   create proc sum_money @count money, @unitprice money ,@sum money output as  set @sum = @count*@unitprice go  declare @sum_temp money ,@sum_temp2 money exec sum_money @count= 1.1,@unitprice = 2.2,@sum = @sum_temp output set @sum_temp2= @sum_temp*100 select @sum_temp2   create proc test_output @in  nvarchar(100),@out nvarchar(100) output as print 'i''m @in  ' + @in set @out = @in print 'i''m @out  '+@out  go declare @i nvarchar(100),@o nvarchar(100) set @i = '让我们一起来测试' exec test_output @in = @i,@out = @o output select @o  /* return 语句和错误处理 */ --return 主要用来进行错误处理 create proc testreturn @a int as  if @a<0 begin    return(-1) end  else if @a = 0 begin    return(0) end  else  begin    return(1) end   go declare @rtn int exec @rtn = testreturn @a=-100 select @rtn go  /*   @@error */ select @@error go select 'a'+1 go select @@error   select error, description from master.dbo.sysmessages where error = 245  create proc testerror as  select 'a'+1 go exec testerror go  create proc testerror as declare @e int,@a int ,@b int set @e = 0 set @a = 1 set @b = 0 select @a/@b if @@error<>0 begin    print '有错误'    set @e = @@error end    return @e go declare @er int exec @er = testerror select @er  /*   @@rowcount */  select @@rowcount select * from customers select @@rowcount  /* null 值 */ create proc testreturn @a int as  if @a is null begin    return(100) end else if @a<0 begin    return(-1) end  else if @a = 0 begin    return(0) end  else  begin    return(1) end      /*************************************************************************************************************************** 特殊问题 ***************************************************************************************************************************/ /* 关于sp_的命名 */ use master go create sp_test as select '现在是master数据库' go use northwind go create sp_test as select '现在是northwind数据库' go exec sp_test exec master.dbo.sp_test drop sp_test   create proc sp1_test as  select '这是master' go use northwind go create proc sp1_test as  select '这是northwind'  exec  sp1_test  drop proc sp1_test /* 命名延迟解决方案： 创建存储过程时，应用的对象可以不存在，建议存储过程及引用的对象所有者都设置为dbo */ --按契约编程 use northwind go create proc testdelay as select * from tbldelay go exec testdelay /* 在创建存储过程时可以逻辑上形成组，以便作为同一个管理单元并在一个程序中使用 */ create proc groupedproc;1  as select 'groupedproc;1 ' go create proc groupedproc;2 as select 'groupedproc;2 ' go sp_helptext groupedproc go exec groupedproc;1 go exec groupedproc;2 go exec groupedproc go drop proc groupedproc /* 存储过程嵌套，最多32层 */  create proc a  as select 'a' go create proc b as select 'b' exec a  go exec b  /* 使用默认值 */ -- -- drop proc testdefault create proc testdefault @a int,@b int=2 as select @a,@b go exec testdefault 1 go exec testdefault @a=1 exec testdefault 1,100  /* 在服务器启动时自动运行的存储过程 要求：所有者是dbo，在master数据库中 */ use northwind go create table start ( dt datetime ) go use master go create proc autostart as insert into northwind.dbo.start values(getdate()) go --设置为自动运行 execute sp_procoption @procname = autostart, @optionname = startup, @optionvalue = true go use master --判断是否自动运行 select objectproperty(object_id('autostart'), 'execisstartup') go select * from northwind.dbo.start --停止自动运行 execute sp_procoption @procname = autostart, @optionname = startup, @optionvalue = false  execute sp_configure @configname = 'scan for startup procs', @configvalue = 0 reconfigure go    /* 扩展存储过程 使用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  insert update delete  create 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) go exec 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) output as   --自动产生客户ID create 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 output insert 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 off select 'a' go -- -- --  set nocount on select 'a'  /*  动态语句的使用——动态条件  */   create proc qry_salesdetails @no int = -1,@start char(10),@end char(10) as 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  a.productid= @no and c.requireddate<=@end  and c.requireddate>=@start  go exec 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 @sql exec(@sql) print '''' go  exec qry_salesdetails @end=null,@start=null exec qry_salesdetails @no=35,@end=null,@start=null exec 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..#temp go create table #temp  ( 商品编号 varchar(100), 商品名称  varchar(100), 金额 money, 销售时间 datetime, 排序 int )  insert into #temp select 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 a on b.productid=a.productid join orders as c on b.orderid=c.orderid where year(c.requireddate) = 1996  insert into #temp(商品编号,金额,排序) select '月汇总',sum(金额),month(销售时间) from #temp group by year(销售时间),month(销售时间)    insert into #temp(商品编号,金额,排序) select '年汇总',sum(金额),12 from #temp where 销售时间 is not null  select * from #temp order by 排序 ,商品名称 desc  select * from #temp drop table tempdb..#temp

更多SQL的相关技术文章，请访问SQL教程栏目进行学习！

以上就是数据库存储过程详解的详细内容，更多内容请关注技术你好其它相关文章！