【SQL Server学习笔记】存储过程、sp_executesql存储过程、try catch错误处理

存储过程就是把一个或多个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