（2.2）DDL增强功能-自定义函数与存储过程

关键词：sql server存储过程，sql server表值函数

目录

1.存储过程

　　SET XACT_ABORT ON

　　基本形式与演示

2.自定义函数/表值函数

3.存储过程的特性

4.函数特性

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

6、事务控制，自定义错误：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）基本形式

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=''
--执行存储过程
execute getArea '', @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