这篇文章给大家分享的是有关数据库存储过程的示例分析的内容。小编觉得挺实用的,因此分享给大家做个参考,一起跟随小编过来看看吧。
/* 存储过程可以看作是在数据库中的存储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
感谢各位的阅读!关于“数据库存储过程的示例分析”这篇文章就分享到这里了,希望以上内容可以对大家有一定的帮助,让大家可以学到更多知识,如果觉得文章不错,可以把它分享出去让更多的人看到吧!
免责声明:本站发布的内容(图片、视频和文字)以原创、转载和分享为主,文章观点不代表本网站立场,如果涉及侵权请联系站长邮箱:is@yisu.com进行举报,并提供相关证据,一经查实,将立刻删除涉嫌侵权内容。