(2.2)sql server函数,sql server自定义函数/表值函数与存储过程

关键词:sql server存储过程,sql server表值函数

事务控制,自定义错误:https://www.cnblogs.com/gered/p/8746008.html

 

1.存储过程

  精华总结:

通过对比@@ERROR一般和if判断结合使用,@@TRANCOUNT和try catch块结合使用,xact_abort为on可以单独使用
Xact_abort为off时,如果存在事务嵌套可以和保存点结合使用

1.1 SET XACT_ABORT ON是设置事务回滚的!

      当为ON时,如果你存储中的某个地方出了问题,整个事务中的语句都会回滚;且不会再执行该事务内的所有语句了;
      为OFF时,只回滚错误的地方。事务内剩余的语句都会执行;
      举例:一个过程里有10行dml,执行到第5行出错。
        【1】如果为on,则直接回滚整个事务,并且报错
        【2】如果为off,则只会回滚出错的第5行,其余1-4,6-10可以正常执行并且提交保存;
 
 
  在存储过程中使用事务时,如果没有try…catch语句,那么当set xact_abort 为on时,如果有错误发生,在批处理语句结束后,系统会自动回滚所有的sql操作。
  当set xact_abort 为off时,如果有错误发生,在批处理语句结束后,系统会执行所有没有发生错误的语句,发生错误的语句将不会被执行。

    在存储过程中使用事务时,如果存在try…catch语句块,那么当捕获到错误时,需要在catch语句块中手动进行Rollback操作,否则系统会给客户端传递一条错误信息。
  如果在存储过程开始处将set xact_abort on,那么当有错误发生时,系统会将当前事务置为不可提交状态,即会将xact_state()置为-1。
  此时只可以对事务进行Rollback操作,不可进行提交(commit)操作,那么我们在catch语句块中就可以根据xact_state()的值来判断是否有事务处于不可提交状态,如果有则可以进行rollback操作了。
  如果在存储过程开始处将set xact_abort off,那么当有错误发生时,系统不会讲xact_state()置为-1,那么我们在catch块中就不可以根据该函数值来判断是否需要进行 rollback了。
  但是我们可以根据@@Trancount全局变量来判断,如果在catch块中判断出@@Trancount数值大于0,代表还有未提交的事务。
  既然进入catch语句块了,那么还存在未提交的事务,该事务应该是需要rollback的,但是这种方法在某些情况下可能判断的不准确。
  推荐的方法还是将set xact_abort on,然后在catch中判断xact_state()的值来判断是否需要Rollback操作。
 
 

  (1)基本形式

[sql] view plain copy
 
  1. create proc | procedure pro_name  
  2.     [{@参数数据类型} [=默认值] [output],  --加上output就代表是地址传递(即会从调用接收,也会输出给调用)
  3.      {@参数数据类型} [=默认值] [output],  
  4.      ....  
  5.     ]  
  6. as  
  7.     SQL_statements  

  (2)实例演示

--判断过程是否存在
IF
OBJECT_ID('getArea') is not null BEGIN drop procedure getArea END GO --创建存储过程 create procedure getArea @shopId nvarchar(64) , @area nvarchar(32) output as begin print @area if (@shopId is not null and @shopId <> '') begin select @area= 2 end else begin set @area = '' end end GO
--声明变量 declare @a varchar(20); set @a='1'
--执行存储过程
execute getArea '2010315', @a output;

--规范形式,形参实参一一对应

execute getArea @shopId='2010315',
@area=@a output;



--输出验证结果
print @a ; GO

   在存储过程中,使用自定义类型(表类型,2008及以上才支持),作为存储过程的参数的应用

/* 创建表类型.*/
create type Type_test as table(keyId int,info varchar(30));
go
/* 创建一个存储过程以表值参数作为输入 */
create Procedure pr_testTable(@tmp Type_test readonly)
as
select *,getdate() from @tmp;
go

  
/* 声明表值参数变量.*/
declare @tmp as Type_test;
/*对自定义类型进行数据插入*/
insert into @tmp(keyId,info)
values(10,'test'),(20,'maomao365.com'),(30,'猫猫小屋')

/* 存储过程运行表参数*/
exec  pr_testTable @tmp;
go
 
drop proc   pr_testTable
go
drop type Type_test
go
   

 

 

2.自定义函数

【2.1】常规自定义函数

--创建
--(1)直接返回表类型
IF OBJECT_ID('testFunTable') is not null
drop function testFunTable
go
create function testFunTable(@count int)  
returns @temptale table (intcount int ,intcountAdd int)  
as  
begin  
  insert into @temptale values(@count,@count+1)  
return  
end
GO
--调用 
SELECT * FROM dbo.testFunTable(20)
GO


--(2)直接返回数字
IF OBJECT_ID('testFunTable') is not null
drop function testFunTable
go
create function testFunTable(@count int)  
returns  int  
as  
begin  
  return  @count+1
end
GO
--调用 
SELECT  dbo.testFunTable(20)

【2.2】表值函数

   有些情况可能用下表值函数,表值函数主要用于数据计算出来返回结果集,可以带参数(和视图的一个大的区别),如果函数中没有过多的逻辑处理,如变量的定义,判断等,
表值函数返回结果集可以简单向下面这么写:


CREATE FUNCTION Fun_GetReportNews(@type varchar(10))
RETURNS TABLE
AS 
RETURN
(
  SELECT TPR_ID,TPR_Title,TPR_Date FROM TP_ReportNews WHERE TPR_Type = @type
)

调用的时候就 SELECT XX FROM Fun_GetReprotNews('xx')

如果函数中要定义变量,进行判断计算处理什么的,写法有点不一样了,要定义表变量才行,表值函数里是不允许创建临时表的,只能是表变量。
举个简单的写法样式,如下:


CREATE FUNCTION FUN_GetInfoList(@type varchar(10))
RETURNS @Table TABLE(TPR_ID int,TPR_Title nvarchar(100),TPR_PubDate datetime)
AS
BEGIN
  DECLARE @a varchar(10)
  SELECT @a = xx FROM XX WHERE xx = @type
    INSERT @Table SELECT XX,XX,XX FROM TableName WHERE XX = @a --表变量里定义的列数和取值列数要一致
RETURN
END

 

 

 

3.存储过程的特性

  (1)SP中创建临时表,SP结束后临时表被销毁

  (2)SP不能返回表变量,但限制少,可以执行对数据库表的操作,可以返回数据集

  (3)限制少,可以执行对数据库表的操作,可以返回数据集

  (4)可以return一个标量值,也可以省略return

存储过程一般用在实现复杂的功能,数据操纵方面

4.函数特性

  (1)函数中不能用某些DML(select,update,delete)语句

  (2)函数中不能用print与output

  (3)函数中不能使用临时表

  (4)可以返回表变量

  (5)内部操作不影响外部

自定义函数一般用在复用度高,功能简单单一,争对性强的地方。

5、设置存储过程在实例启动时启动

--将该存储过程设置为SQL Server服务启动时自动启动

EXEC sp_procoption
'StartBlackBoxTrace','STARTUP','ON'
print 'ok'
GO
 

 

 

 


 

 

 部分参考自:https://www.cnblogs.com/chaoa/articles/3894311.html

posted @ 2018-05-31 17:36  郭大侠1  阅读(530)  评论(0编辑  收藏  举报