SqlServer创建函数（CreateFunction）注意事项表值函数
1. 定义
- 表值函数就是返回值为⼀个表的函数，调⽤⽅式如下⽰例
SELECT * FROM TABLESFunctionName(Parameter1，parameter2)
//example One:
select * from [cnooc].[Gas_MutiPrice_GetModelDiffDataByMutiDealFun]('1111')
- 表值函数可以在视图中使⽤
- 表值函数在sqlsrver中显⽰在Table-valued Functions中，如下图
2. 创建
------------------------------------------------------------------
//在标志函数中使⽤创建临时表语句，如下
declare@TEMPDiffData table(
ID int IDENTITY(1,1)not null, --创建列ID，并且每次新增⼀条记录就会加1
DIFFFlag varchar(1),
primary key(ID) --定义ID为临时表#Tmp的主键
);
//可以看出与存储过程创建语句相似，只是#变成了@，create变成了declare
//这个地⽅要注意，如果在标志函数中需要重复使⽤@TEMPDiffData（这⾥的重复指的是双重循环，且这个临时表建在最内层），//sql引擎对于设置的IDENTITY (1,1) ⾃增是会重置的，使⽤dbcc checkident (''table_name'', reseed, 0)是不起作⽤的，
//因为@TEMPDiffData不是真是存在的数据库表；只能在创建表时把⾃增去掉，⾃⼰⾃定义设置主键ID值。
//在⽂章后⾯附上案例。
//标志函数⾃定义的临时表不能drop等操作，只能DELETE；
//如DELETE TEMPDiffData
//同时函数创建语句中是不能存在动态执⾏语句的，也就是 insert、update 这样的语句
------------------------------------------------------------------
------------------------------创建表值函数⽅式1
CREATE FUNCTION[cnooc].[Gas_MutiPrice_GetModelDiffDataByMutiDealFun]
(@FILTER nvarchar(max))
returns@TEMPFinalDiffDataAllDeal table(
DealModelValueUID int not null, --创建列ID，并且每次新增⼀条记录就会加1
DealUID int not null,
DealSchedLineUID INT,
Price float,
NETValue float,
Volume float,diff函数
DiffFlag nvarchar(1)
)
AS
BEGIN
insert into@TEMPFinalDiffDataAllDeal
select*from Deals
RETURN
END
GO
------------------------------------创建表值函数⽅式2
CREATE FUNCTION functionName
(@FILTER nvarchar(max))
returns Table
AS
RETURN
select*from Deals
GO
-
-----------------------------------------------------------------
//其他语法和存储过程类似，此处不做赘述。
- 双重循环表值函数⽰例
CREATE FUNCTION[cnooc].[Gas_MutiPrice_GetModelDiffDataByMutiDealFun]
(@FILTER nvarchar(max))
returns@TEMPFinalDiffDataAllDeal table(
DealModelValueUID int not null, --创建列ID，并且每次新增⼀条记录就会加1
DealUID int not null,
DealSchedLineUID INT,
Price float,
NETValue float,
Volume float,
DiffFlag nvarchar(1)
)
AS
BEGIN
DECLARE@DealUID INT
-----------------------------------------------------------
-------必须是选择多⾏且有diff的公式适⽤这个存储过程------------
-------必须是选择多⾏且有diff的公式适⽤这个存储过程------------
-----------------------------------------------------------
----------------------------------------------------------------------------------------
-
-DECLARE @FILTER NVARCHAR(MAX)='1259862,1259872'
DECLARE@NUM INT=cnooc.TotalNumberOfChar(@FILTER,',')+1
DECLARE@DEALUIDStr nvarchar(255)
DECLARE@TEMPDealUIDsData TABLE(
ID int IDENTITY(1,1)not null, --创建列ID，并且每次新增⼀条记录就会加1
DealUID int,
primary key(ID) --定义ID为临时表#Tmp的主键
);
WHILE@NUM>0
BEGIN
IF(SELECT CASE WHEN@FILTER LIKE'%,%'THEN1ELSE0END)=1
BEGIN
SET@DEALUIDStr=SUBSTRING(@FILTER,0,DBO.IndexOfChar(@FILTER,',',1))
SET@FILTER=SUBSTRING(@FILTER,DBO.IndexOfChar(@FILTER,',',1)+1,LEN(@FILTER)-DBO.IndexOfChar(@FILTER,',',1))
INSERT INTO@TEMPDealUIDsData(DealUID)VALUES(@DEALUIDStr)
END
ELSE
BEGIN
INSERT INTO@TEMPDealUIDsData(DealUID)VALUES(@FILTER)
END
SET@NUM=@NUM-1
END
----------------------------------------------------------------------------------------
--CREATE TABLE #TEMPFinalDiffDataAllDeal(
-- DealModelValueUID int not null, --创建列ID，并且每次新增⼀条记录就会加1
-- DealUID int not null,
-- DealSchedLineUID INT,
-- Price float,
-- NETValue float ,
-- DiffFlag nvarchar(1)
-- );
DECLARE@ROWNBUM INT
SELECT@ROWNBUM=COUNT(*)FROM@TEMPDealUIDsData
while@ROWNBUM>0
BEGIN
SELECT@DealUID=DealUID FROM@TEMPDealUIDsData where ID=@ROWNBUM
-----------------------------------------------------------------------------------------------
BEGIN
DECLARE@TEM TABLE(
DealUID int, --创建列ID，并且每次新增⼀条记录就会加1
cc NVARCHAR(255),
PricingFormulaDescription NVARCHAR(255),
UsePriceTotalNumber INT,
PriceTotalNumber INT,
PricingFormula NVARCHAR(255)
);
--DECLARE @DealUID INT =1259862 --1259872
DECLARE@UsePriceTotalNumber INT=0
DECLARE@PricingFormula NVARCHAR(255)
INSERT INTO@TEM
SELECT d.DealUID
,L3_C20045.PricingFormula as cc
,L3_C20045.PricingFormulaDescription
,cnooc.TotalNumberOfChar(L3_C20045.PricingFormulaDescription,'+')+1AS UsePriceTotalNumber--⽤户填了⼏个价格
,
(cnooc.TotalNumberOfChar(L3_C20045.PricingFormula,',')+1)/3AS PriceTotalNumber--公式总共能填⼏个价格
,substring(L3_C20045.PricingFormula,DBO.IndexOfChar(L3_C20045.PricingFormula,',',14)+1,LEN(L3_C20045.PricingFormula)-DBO.IndexOfChar(L3_C2 0045.PricingFormula,',',14)-1)AS PricingFormula
FROM Deals D
LEFT OUTER JOIN DealLegs L1_C2 ON d.DealUID = L1_C2.DealUID AND L1_C2.LineSEQ =0AND d.VersionSEQ = L1_C2.VersionSEQ AND L1_C2.Ri skSEQ =0AND L1_C2.LegSEQ =3
LEFT OUTER JOIN DealModelLegs L3_C20045 ON L1_C2.DealLegUID = L3_C20045.DealLegUID
WHERE D.InactiveFlag='N'AND D.DealTypeUID=20110AND D.DealUID=@DealUID
SELECT
@UsePriceTotalNumber=UsePriceTotalNumber,
@PricingFormula=replace(PricingFormula,'"','')
from@TEM
SET@PricingFormula=SUBSTRING(@PricingFormula,0,DBO.IndexOfChar(@PricingFormula,',',@UsePriceTotalNumber))
DECLARE@TEMPDiffData TABLE(
ID int,--IDENTITY (1,1) not null, --创建列ID，并且每次新增⼀条记录就会加1
DIFFFlag varchar(1),
primary key(ID) --定义ID为临时表#Tmp的主键
);
DECLARE@Str nvarchar(255)
DECLARE@ID INT=1
WHILE@UsePriceTotalNumber>0
BEGIN
IF(SELECT CASE WHEN@PricingFormula LIKE'%,%'THEN1ELSE0END)=1
BEGIN
SET@Str=SUBSTRING(@PricingFormula,0,DBO.IndexOfChar(@PricingFormula,',',1))
SET@PricingFormula=SUBSTRING(@PricingFormula,DBO.IndexOfChar(@PricingFormula,',',1)+1,LEN(@PricingFormula)-DBO.IndexOfChar(@PricingF ormula,',',1))
INSERT INTO@TEMPDiffData(ID,DIFFFlag)values(@ID,@Str)
END
ELSE
BEGIN
INSERT INTO@TEMPDiffData(ID,DIFFFlag)values(@ID,@PricingFormula)
END
SET@UsePriceTotalNumber=@UsePriceTotalNumber-1
SET@ID=@ID+1
END
DECLARE@TEMP1TABLE(
RowNum INT,
DealModelValueUID INT,
DealUID INT,
DealSchedLineUID INT,
PRICE FLOAT,
NETValue FLOAT,
Volume float,
DIFF VARCHAR(1)
)
INSERT INTO@TEMP1
SELECT
ROW_NUMBER()over(order by DMV.DealModelValueUID asc)as RowNum
,DMV.DealModelValueUID
,DMHV.DealUID
,DMHV.DealSchedLineUID
,DMV.AllInPriceInPriceUnit AS PRICE
,DMV.NetValueInSettleCurrency AS NETValue
,dmv.VolumeInVolumeUnit AS Volume
,'N'AS DIFF
FROM DealModelValues DMV
INNER JOIN DealModelHeaderValues DMHV ON DMV.DealModelHeaderValueUID=DMHV.DealModelHeaderValueUID AND AllInPriceInPriceUnit<>0 WHERE DealUID=@DealUID--default asc
DECLARE@ROW INT
SELECT@ROW=count(*)from@TEMP1
WHILE@ROW>0
BEGIN
IF(SELECT DIFFFlag FROM@TEMPDiffData WHERE ID=@ROW)='Y'
BEGIN
UPDATE@TEMP1SET DIFF='Y'WHERE RowNum=@ROW
END
SET@ROW=@ROW-1
END
INSERT INTO@TEMPFinalDiffDataAllDeal
SELECT DealModelValueUID, DealUID,DealSchedLineUID,PRICE,NETValue,Volume,DIFF FROM@TEMP1
DELETE@TEM--IF DON'T CLEAR ,WILL DISPLAY ERROR
DELETE@TEMP1
DELETE@TEMPDiffData
END
-----------------------------------------------------------------------------------------------
SET@ROWNBUM=@ROWNBUM-1
END
RETURN
END
GO
版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。
发表评论