原文出自:http://blog.csdn.net/sqlserverdiscovery/article/details/7851881
存储过程就是把一个或多个T-SQL语句组合到一个逻辑单元中,在SQL Server数据库中保存为一个对象。在创建完成后,定义会保存在sys.sql_modules系统目录视图中。
存储过程的有点:
1、存储过程是在数据层汇集的T-SQL代码,可以促进代码的重复使用,同时有助于调试、寻找bug。也就是说这些代码就存放在数据库里,而不是嵌入在应用程序中的SQL代码,再由这些代码连接起来,最后发送到服务器上来执行的。
2、存储过程相对于大的即席查询,能够减少网络流量。
3、存储过程则更安全,因为内嵌的即席查询容易进行SQL注入。
4、相对于函数、视图,存储过程能使用流控制、临时表、表变量等。
5、可以淡化数据获取的方法。一旦底层的表有变化,不用去修改嵌入到应用程序中的sql代码,只要修改存储过程就可以了。
6、存储过程的查询响应时间比较稳定。存储过程提供了更加可靠的查询计划缓存,因此可以重用,而即席查询完全依赖于执行计划被缓存的环境(语句中的参数)。
另外,需要注意的:'CREATE/ALTER PROCEDURE'不允许将数据库名称指定为对象名前缀
- --1.1创建无参数存储过程
- --这个存储过程的定义很像视图定义
- create procedure dbo.usp_wct
- as
- select wcId,
- wcV,
- wcDate
- from wcT
- go
- --1.2执行存储过程
- exec dbo.usp_wct
- --2.1创建带参数的存储过程
- create procedure dbo.usp_param_wct
- (@wcId bigint =1, --默认值为1
- @wcDate datetime =null) --默认值为null
- as
- select wcV
- from dbo.wcT
- where wcId = @wcId
- and isnull(@wcDate,1)=case when @wcdate is null
- then 1
- else wcDate
- end
- --返回状态值,一般不用于返回计算结果,而是用来返回表明存储过程执行的状态
- return 1
- go
- --2.2.1调用存储过程
- exec dbo.usp_param_wct '1','2012-07-01'
- --2.2.2调用存储过程,带参数名
- exec dbo.usp_param_wct @wcid='1',
- @wcdate='2012-07-01'
- --2.2.3调用存储过程,带参数名,只输入一个参数值,另一个会使用默认值
- exec dbo.usp_param_wct @wcid='1'
- --2.2.4调用存储过程,带参数名,不输入参数,2个参数都会使用默认值
- exec dbo.usp_param_wct
- --3.1创建带output参数的存储过程
- create procedure dbo.usp_output_param_wct
- (@wcId bigint =1, --默认值为1
- @wcDate datetime =null, --默认值为null
- @wcV varchar(50) output) --输出参数,用来返回计算结果
- as
- select @wcV = wcV
- from dbo.wcT
- where wcId = @wcId
- and isnull(@wcDate,1)=case when @wcdate is null
- then 1
- else wcDate
- end
- --返回状态值,一般不用于返回计算结果,而是用来返回表明存储过程执行的状态
- return 1
- go
- --3.2调用存储过程
- declare @output varchar(50)
- exec dbo.usp_output_param_wct @wcId =1, --输入参数
- --@wcDate = null,
- @wcv = @output output --输出参数,存放返回值
- print @output
- --4.1修改存储过程
- create procedure dbo.usp_output_param_wct
- (@wcId bigint =1, --默认值为1
- @wcDate datetime =null, --默认值为null
- @wcV varchar(50) output) --输出参数,用来返回计算结果
- with encryption --加密存储过程
- as
- select @wcV = wcV
- from dbo.wcT
- where wcId = @wcId
- and isnull(@wcDate,1)=case when @wcdate is null
- then 1
- else wcDate
- end
- --返回状态值,一般不用于返回计算结果,而是用来返回表明存储过程执行的状态
- return 1
- go
- --4.2查看定义
- select object_name(object_id),
- definition --显示为NULL
- from sys.sql_modules
- where object_id = object_id('dbo.usp_output_param_wct')
- --5.在SQL Server启动时自动执行存储过程
- create procedure dbo.create_table
- as
- select * into wc.dbo.wcX
- from wc.dbo.wcT
- go
- --5.1设置为true,在SQL Server启动时自动执行存储过程
- exec sp_procoption @procname = 'dbo.create_table',
- @optionname = 'startup',
- @optionvalue= 'true'
- --5.2设置为false来禁止
- --只有dbo拥有的master数据库中的对象才能更改启动设置。
- use master
- go
- exec sp_procoption @procname = 'dbo.create_table',
- @optionname = 'startup',
- @optionvalue= 'false'
- --6.1每次存储过程执行时都重新编译
- --需要注意的是:当表、索引数据大量更新时,会进行语句级别的重新编译
- create procedure dbo.usp_backupset_info
- (@startDate datetime,
- @finishDate datetime)
- with recompile --不保存执行计划,每次都是重新编译
- as
- select SUM(backup_size)/1024/1024/1024 as 'GB'
- from msdb.dbo.backupset
- where backup_start_date >= @startDate
- and backup_finish_date <= @finishDate
- and type = 'D' --数据库备份,不是日志备份或差异备份
- go
- --6.2清空过程缓存
- select *
- from sys.dm_exec_cached_plans
- dbcc freeproccache --清空过程缓存
- --再次查询发现都已经清空
- select *
- from sys.dm_exec_cached_plans
- --7.刷新存储过程,查看元数据
- exec sp_refreshsqlmodule @name ='dbo.usp_output_param_wct'
- select p.name, --存储过程名称
- p.object_id,
- pp.parameter_id,
- pp.name, --参数名称
- pp.default_value, --好像不起作用
- t.name --参数列的类型
- from sys.procedures p
- inner join sys.parameters pp
- on pp.object_id = p.object_id
- inner join sys.types t
- on t.system_type_id = pp.system_type_id
- where p.object_id =965578478
- --8.删除存储过程
- DROP PROCEDURE dbo.usp_output_param_wct
存储过程的Execute as选项
- --1.创建表
- create table dbo.wcE
- (vid int not null primary key clustered,
- v int not null
- )
- insert into dbo.wcE
- values(1,100),
- (2,200),
- (3,300)
- --2.创建存储过程,存储过程的架构与存储过程中引用对象的架构一样
- create procedure dbo.usp_delete_wcE
- as
- delete from dbo.wcE
- go
- --3.1创建登录名
- create login wclogin with password = 'wclogin123'
- --3.2创建数据库用户
- create user wclogin
- --4.授予用户执行存储过程的权限
- grant exec on dbo.usp_delete_wcE to wclogin
- grant connect to wclogin
- /*====================================================
- 5.1打开另一个客户端,以wclogin登录名登进去,然后执行存储过程,
- 虽然这个用户没有访问存储过程中所引用表的权限,
- 但是由于存储过程的架构与存储过程中引用对象的架构一样,
- 同时由于存储过程中只能是insert,update,delete,select,
- 而不能是trancate等操作,这就是所有权链,所以没有报错.
- 特别需要注意的是:以上的所有权链对于动态的SQL是无效的.
- ======================================================*/
- exec dbo.usp_delete_wcE
- --5.2这里改为truncate后,会报错
- alter procedure dbo.usp_delete_wcE
- as
- truncate table dbo.wcE
- go
- --6.1使用execute as owner指定存储过程的任何调用,
- --都是在存储过程架构的拥有者的安全上下文运行的
- --这时再次以wclogin登录就可以执行存储过程而不会报错
- alter procedure dbo.usp_delete_wcE
- with execute as owner
- as
- truncate table dbo.wcE
- go
- --6.2以执行用户的权限来执行存储过程
- --在以wclogin登录执行时会报错
- alter procedure dbo.usp_delete_wcE
- with execute as caller
- as
- truncate table dbo.wcE
- go
- --6.3以创建或最近一次更新存储过程的用户的权限来执行存储过程,
- --在以wclogin登录执行时会报错
- alter procedure dbo.usp_delete_wcE
- with execute as self
- as
- truncate table dbo.wcE
- go
- --6.4以指定的用户的权限来执行存储过程,
- --在以wclogin登录执行时显然不会报错
- alter procedure dbo.usp_delete_wcE
- with execute as 'dbo'
- as
- truncate table dbo.wcE
- go
- --6.5这个存储过程用了动态的SQL语句
- create procedure dbo.w
- as
- exec('select * from dbo.wcE');
- go
- grant exec on dbo.w to wclogin
- --在客户端以wclogin登录,执行时会报错
- --拒绝了对对象'wcE'(数据库'wc',架构'dbo')的SELECT权限。
- exec dbo.w
- --1.函数
- if exists(select * from sys.objects where name = 'f_splitSTR' and type = 'tf')
- drop function dbo.f_splitSTR
- go
- create function dbo.f_splitSTR
- (
- @s varchar(8000), --要分拆的字符串
- @split varchar(10) --分隔字符
- )
- returns @re table( --要返回的临时表
- col varchar(1000) --临时表中的列
- )
- as
- begin
- declare @len int
- set @len = LEN(@split) --分隔符不一定就是一个字符,可能是2个字符
- while CHARINDEX(@split,@s) >0
- begin
- insert into @re
- values(left(@s,charindex(@split,@s) - 1))
- set @s = STUFF(@s,1,charindex(@split,@s) - 1 + @len ,'') --覆盖:字符串以及分隔符
- end
- insert into @re values(@s)
- return --返回临时表
- end
- go
- select * from dbo.f_splitSTR('123,456',',')
- --2.存储过程
- --创建类型
- create type var_orgid as table
- (
- org_id numeric(10,0)
- )
- --创建存储过程
- create proc usp_orgid
- @orgid_table var_orgid readonly
- as
- select * from @orgid_table
- go
- --定义表变量
- declare @orgid_table as var_orgid
- insert into @orgid_table
- values(123)
- exec usp_orgid @orgid_table
sp_executesql存储过程,可以使动态语句重用,提高效率:
- DECLARE @sumcount INT
- DECLARE @tmpSQL NVARCHAR(1000)
- DECLARE @TABLENAME VARCHAR(50)
- SELECT @TABLENAME = ' sys.objects ' --表名变量赋值
- SELECT @tmpSQL = 'select @sum = convert(varchar(50),count(object_id)) from ' + @TABLENAME
- /*
- 参数1: 动态语句
- 参数2:定义的参数,这个参数就是在动态语句中引用到的变量@sum
- 参数3:指明了执行结果@sum的值,放到@sumcount参数里面,
- 这里需要特别注意的是,动态语句中的变量,和外面定义的变量,是不同的,
- 下面的语句,实际相当于:
- declare @sum int
- select @sum = convert(varchar(50),count(object_id))
- from + 表名
- 所以,通过sp_executesql,可以把语句产生的结果输出到外面的变量,
- 这样我们才能知道输结果
- */
- exec sp_executesql @tmpsql,
- N'@sum int output',
- @sumcount out
- select @sumcount
try catch 错误处理
- create proc proc_t(@i int)
- as
- select 5 * 1.0 / @i
- go
- --1.不会报错
- begin try
- begin tran
- exec proc_t 1
- select '执行成功'
- commit tran
- end try
- begin catch
- select '执行失败'
- if @@trancount >0
- rollback
- end catch
- --2.这次会报错,因为分母为0
- begin try
- begin tran
- exec proc_t 0
- select '执行成功'
- commit tran
- end try
- begin catch
- select '执行失败'
- if @@trancount >0
- rollback
- end catch