存储过程

Tagetik 允许通过存储过程进行数据处理。

在以下情况下需要存储过程：

用户所需的功能无法使用标准或多维科目计算逻辑、内置逻辑或 ETL 流程实现；
当存在性能问题，使得使用 ETL 流程效率较低时；
当使用 ETL 无法产生预期结果时，或当参数化过于复杂时。

每当使用存储过程时，必须记住

与 ETL 一样，应特别注意它们的执行顺序（Tagetik 并不从结构上保证该顺序）；
存储过程的执行不使用 Tagetik 技术框架；也就是说，其执行不受任何与用户限制、实体/自定义维度及科目/自定义维度限制、或场景/期间锁定相关的控制（当然，除非该存储过程本身已明确开发以考虑这些方面）。

构建存储过程时应始终格外谨慎，以避免对应用程序的其余部分产生破坏性影响。建议始终寻求 Tagetik 技术人员的建议。

存储过程可以：

被调度，即通过“数据处理 - 存储过程”类型的任务将其插入到作业中；
作为工作流中的任务插入，即通过“存储过程”类型的任务；
在数据录入表单中执行；
由 ETL 执行，即通过存储过程类型的“命令”。

存储过程定义¶

可通过管理员用户 Web 界面，按以下路径访问应用程序数据库上存储过程的定义：

设置与管理（Setup & Admin）> 流程（Processes）磁贴 > 数据库对象（Database objects）。在这种情况下，用户可以访问完整的数据库对象列表

有关存储过程定义窗口的更多详细信息，请参见 数据库对象。

在两种情况下，Tagetik 都会在审计跟踪中记录通过这些功能执行的操作，以便您始终可以完全掌控“谁、何时以及如何”对数据进行了操作

系统对可由 Tagetik 直接运行的存储过程名称设置了条件：它们必须具有以 CPM_SP_ 为前缀的名称（与任何现有架构无关）

参数¶

存储过程可以定义输入、输出或输入/输出参数，具体取决于创建和使用它们的数据库类型。

参数可以是除二进制以外的任何类型。

SQL Server¶

@INPUT_PARAMETER VARCHAR(30)

@OUTPUT_PARAMETER VARCHAR(100) OUTPUT

Oracle¶

INPUT_PARAMETER IN VARCHAR2

OUTPUT_PARAMETER OUT VARCHAR2

INOUT_PARAMETER IN OUT NUMBER

Postgresql¶

IN INPUT_PARAMETER VARCHAR(30)

OUT OUTPUT_PARAMETER VARCHAR(30)

INOUT INOUT_PARAMETER INTEGER

SAP Hana¶

IN INPUT_PARAMETER VARCHAR(30)

OUT OUTPUT_PARAMETER VARCHAR(100)

INOUT INOUT_PARAMETER NUMERIC(2)

在选择存储过程执行前参数时，仅会显示输入类型和输入/输出类型的参数。

如果存储过程已作为任务添加到工作流中，则可以通过使用特定的参数命名，为参数分配从上下文继承的值——例如数据收集流程、实体、源场景、期间和步骤，从而避免需要再次手动设置它们。

对于每个参数，可以从自定义编辑器中选择值；编辑器的类型取决于分配给各个参数的名称（参见下表中所列的规则）。

维度	前缀名称	值继承自工作流的前缀名称
数据收集流程	RACCOLTA	RACCOLTA
期间	DIM_PER	WFDIM_PER
场景（原始和合并）	DIM_SCE
原始场景	DIM_SCE_O	WFDIM_SCE_O
合并场景	DIM_SCE_C
实体	DIM_AZI	WFDIM_AZI
步骤	DIM_FASE	WFDIM_FASE
自定义维度 1	DIM_DEST1
自定义维度 2	DIM_DEST2
自定义维度 3	DIM_DEST3
自定义维度 4	DIM_DEST4
自定义维度 5	DIM_DEST5
类别	DIM_CAT
科目	DIM_CONTO
原因	DIM_CAUSALE
单位	DIM_VAL
表单	DIM_PROSP

对于所有这些与 Tagetik 维度相关的参数，分配的编辑器是元素列表查找。

其他类型	前缀名称	编辑器
会话用户	SESSION_USER	此参数从不由用户手动定义，而是始终由系统自动定义为请求启动存储过程的会话代码。对于 Postgresql，要使用的参数是 SESSION_USR
标志	FLAG_	复选框
Tagetik 枚举值列表	COMBO_<枚举名称>	下拉列表

以下是适用于“来自 Tagetik 枚举的值列表”类型参数的最常见枚举所使用的名称：

NaturaConto；
TipoConto；
TipoCategoria；
TipoScenario。

如果参数没有有效的名称或类型，或者存储过程尚未部署，则它不会出现在用户可执行的存储过程列表中；
可以有多个参数引用同一个 Tagetik 维度。例如 RACCOLTA1 和 RACCOLTA2；
如果参数名称为 DIM_SCE_C1 或 DIM_SCE_O1，系统将分别将其视为合并场景和原始场景；
如果使用了场景和期间参数，则为选择值而提供的列表将不会相互关联；也就是说，无论给定场景是否存在该期间，都可以为期间参数选择一个值；
在为选择参数值而提供的列表中，从不应用用户限制。

当存储过程被执行时，所有输入、输出和输入/输出参数都将记录在审计日志中。

示例

Sql Server

CREATE PROCEDURE CPM_SP_TEST (

@RACCOLTA VARCHAR(30),

@OUTPUT1 VARCHAR(100) OUTPUT,

@DIM_SCE_O VARCHAR(15),

@OUTPUT2 VARCHAR(1000) OUTPUT,

@DIM_PER VARCHAR(2),

@NUM1 NUMERIC(2) OUTPUT,

@WFDIM_AZI VARCHAR(30),

@NUM2 NUMERIC(2) OUTPUT,

@COMBO_NATURACONTO VARCHAR(1),

@FLAG_TEST NUMERIC(1),

@SESSION_USER VARCHAR(90)

)

AS

BEGIN

SET @NUM1 = @NUM2 + 1

SET @OUTPUT1 = 'Test output1'

SET @OUTPUT2 = @RACCOLTA + '-' + @DIM_SCE_O + '-' + @DIM_PER + '-' + @WFDIM_AZI + '-' + @SESSION_USER

RETURN (1)

END

Oracle

CREATE OR REPLACE PROCEDURE CPM_SP_TEST (

RACCOLTA IN VARCHAR2,

OUTPUT1 OUT VARCHAR2,

DIM_SCE_O IN VARCHAR2,

OUTPUT2 OUT VARCHAR2,

DIM_PER IN VARCHAR2,

NUM1 IN OUT NUMBER,

WFDIM_AZI IN VARCHAR2,

NUM2 IN OUT NUMBER,

COMBO_NATURACONTO IN VARCHAR2,

FLAG_TEST IN NUMBER,

SESSION_USER IN VARCHAR2

)

IS

BEGIN

NUM1 := NUM2 + 1;

OUTPUT1 := 'Test output1';

OUTPUT2 := RACCOLTA || '-' || DIM_SCE_O || '-' || DIM_PER || '-' || WFDIM_AZI || '-' || SESSION_USER || '-' || NUM1 || '-' || NUM2;

END CPM_SP_TEST;

Postgresql

CREATE OR REPLACE FUNCTION CPM_SP_TEST (

IN RACCOLTA VARCHAR(30),

OUT OUTPUT1 VARCHAR(30),

IN DIM_SCE_O VARCHAR(15),

OUT OUTPUT2 VARCHAR(30),

IN DIM_PER VARCHAR(2),

INOUT NUM1 INTEGER,

IN WFDIM_AZI VARCHAR(30),

INOUT NUM2 INTEGER,

IN COMBO_NATURACONTO VARCHAR(1),

IN FLAG_TEST INTEGER

)

RETURNS void AS

$$

BEGIN

NUM1 := NUM2 + 1;

OUTPUT1 := 'Test output1';

OUTPUT2 := RACCOLTA || '-' || DIM_SCE_O || '-' || DIM_PER || '-' || WFDIM_AZI || '-' || SESSION_USER || '-' || NUM1 || '-' || NUM2;

END;

$$ LANGUAGE plpgsql;

SAP Hana

CREATE PROCEDURE "CPM_SP_TEST" (

IN RACCOLTA VARCHAR(30),

IN DIM_SCE_O VARCHAR(15),

OUT OUTPUT2 VARCHAR(1000),

IN DIM_PER VARCHAR(2),

INOUT NUM1 NUMERIC(2),

IN WFDIM_AZI VARCHAR(30),

INOUT NUM2 NUMERIC(2),

IN COMBO_NATURACONTO VARCHAR(1),

IN FLAG_TEST NUMERIC(1),

IN "SESSION_USER" VARCHAR(255)

)

LANGUAGE SQLSCRIPT SQL SECURITY INVOKER AS

BEGIN

OUTPUT2 = :RACCOLTA || '-' || :DIM_SCE_O || '-' || :DIM_PER || '-' || :WFDIM_AZI || '-' || :SESSION_USER || '-' || :NUM1 || '-' || :NUM2;

END;

示例：以下存储过程从 cogs 表读取数据，为其分配累计值，然后根据它们必须存储到的科目将其加载到总余额表中。

ALTER PROCEDURE [dbo].[CPM_SP_COGS_TO_DSL] as

declare @provenienza varchar(50)

declare @SCE_O VARCHAR(10)

begin

set @SCE_O = '2011MACT'

set @provenienza = 'CPM_SP_COGS_TO_DSL'

select

cod_azienda,

cod_scenario,

cod_periodo,

'G01' Dest1,

ProdCategoryCode Dest2,

ClusterCustomerCode Dest3,

CompanyCurrencyCode CompanyCurrencyCode,

DocCurrencyCode DocCurrencyCode,

-sum( isnull(Qty_L , 0 ) ) QTY_L,

-sum( isnull(Qty_A , 0 ) ) QTY_A,

-sum( isnull(Qty, 0 ) ) AV_P1_M2,

-sum( - isnull(SalesmanCommision, 0)

isnull(DistributorCommision, 0)) P25100_M2,

-sum( -isnull(Cost , 0 ) ) P15110_M2,

-sum( -isnull(CostCorporate_S,0 )) CPR_S,

-sum( -isnull(YearEndBonus,0)) B00001,

-sum( isnull(Value_F_A ,0 )) VAL_F_A,

sum( isnull(Value_F_S ,0 )) VAL_F_S, -sum( isnull(Value_F_A ,0 )+ isnull(Value_F_S ,0 )) VAL_F,

-sum( isnull(Value_L_S ,0 )) VAL_L_S,

-sum( -isnull(Royalties , 0 )) P25200_M2

/* 开始：临时表 */

into #COGS_TO_DSL

from cogs

where cod_scenario = @SCE_O

and cod_azienda in (select cod_azienda from azienda )

group by

cod_azienda,

cod_scenario,

cod_periodo,

ProdCategoryCode,

ClusterCustomerCode,

DocCurrencyCode,

CompanyCurrencyCode

/* 结束临时表 */

select

cod_azienda,

cod_scenario,

p.cod_periodo,

Dest1,

Dest2,

Dest3,

CompanyCurrencyCode CompanyCurrencyCode,

DocCurrencyCode DocCurrencyCode,

sum( QTY_L ) QTY_L,

sum( QTY_A ) QTY_A,

sum( AV_P1_M2) AV_P1_M2,

sum( P25100_M2 ) P25100_M2,

sum( P15110_M2 ) P15110_M2,

sum( CPR_S ) CPR_S,

sum( B00001 ) B00001,

sum( VAL_F_A ) VAL_F_A,

sum( VAL_F_S ) VAL_F_S,

sum( VAL_F ) VAL_F,

SUM( VAL_L_S) VAL_L_S,

sum( P25200_M2 ) P25200_M2

into #COGS_TO_DSL_YTD

from #COGS_TO_DSL t, periodo p

where t.cod_periodo <= p.cod_periodo

and cod_scenario = @SCE_O

group by

cod_azienda,

cod_scenario,

p.cod_periodo,

dest1,

dest2,

dest3,

DocCurrencyCode,

CompanyCurrencyCode

--/* 累计化结束 */

delete from dati_saldi_lordi

where cod_scenario = @SCE_O

and provenienza = @provenienza

/* 开始：插入到 dati_Saldi_lordi */

--50225 销售人员成本

insert into dati_saldi_lordi(OID_DATI_SALDI_LORDI, COD_SCENARIO, COD_PERIODO, COD_AZIENDA, COD_CONTO, COD_DEST1, COD_DEST2, COD_DEST3, COD_CATEGORIA, IMPORTO, COD_VALUTA, IMPORTO_VALUTA_ORIGINARIA, COD_VALUTA_ORIGINARIA, PROVENIENZA, USERUPD, DATEUPD, NOTE)

select

newid() OID_DATI_SALDI_LORDI,

t.cod_scenario COD_SCENARIO,

t.cod_periodo COD_PERIODO,

cod_azienda COD_AZIENDA,

'50225' COD_CONTO,

Dest1,

Dest2,

Dest3,

'S' COD_CATEGORIA,

P25100_M2 IMPORTO,

COMPANYCURRENCYCODE COD_VALUTA,

P25100_M2 IMPORTO_VALUTA_ORIGINARIA,

COMPANYCURRENCYCODE COD_VALUTA_ORIGINARIA,

@PROVENIENZA PROVENIENZA,

'Young' USERUPD,

getdate() DATEUPD,

null NOTE

from #COGS_TO_DSL_YTD t

where P25100_M2 <> 0

and cod_scenario = @SCE_o

and Dest2 <> 'POP'

-- 50220 - 广告与促销

insert into dati_saldi_lordi(OID_DATI_SALDI_LORDI, COD_SCENARIO, COD_PERIODO, COD_AZIENDA, COD_CONTO, COD_DEST1, COD_DEST2, COD_DEST3, COD_CATEGORIA, IMPORTO, COD_VALUTA, IMPORTO_VALUTA_ORIGINARIA, COD_VALUTA_ORIGINARIA, PROVENIENZA, USERUPD, DATEUPD, NOTE)

select

newid() OID_DATI_SALDI_LORDI,

t.cod_scenario COD_SCENARIO,

t.cod_periodo COD_PERIODO,

cod_azienda COD_AZIENDA,

'50220' COD_CONTO,

Dest1,

Dest2,

Dest3,

'S' COD_CATEGORIA,

P15110_M2 IMPORTO,

COMPANYCURRENCYCODE COD_VALUTA,

P15110_M2 IMPORTO_VALUTA_ORIGINARIA,

COMPANYCURRENCYCODE COD_VALUTA_ORIGINARIA,

@PROVENIENZA PROVENIENZA,

'Young' USERUPD,

getdate() DATEUPD,

null NOTE

from #COGS_TO_DSL_YTD t

where P15110_M2 <> 0

AND Dest2 <> 'POP'

AND T.COD_SCENARIO = @SCE_O

end