------------------------------------------------
-- Export file for user PLANBI --
-- Created by lWX179495 on 2013/9/25, 8:50:05 --
------------------------------------------------
set define off;
create or replace package dim3 is
/********************************************************************
自定义类型
******************************************************************************/
type TArrStr is table of varchar(8000) index by binary_integer;
type TArrArrStr is table of TArrStr index by binary_integer;
jobNo number(20);
/********************************************************************
对外供j2ee调用的公共函数
******************************************************************************/
/**
批量获取给定cube中的值
**/
procedure getTheseCubeV(
rtnCursor out sys_refcursor,
tuples0 in varchar, --$('tbase','','')&&$('tbase','','')&&$('tbase','','')
tuples1 in varchar, --$('tbase','','')&&$('tbase','','')&&$('tbase','','')
tuples2 in varchar, --$('tbase','','')&&$('tbase','','')&&$('tbase','','')
tuples3 in varchar, --$('tbase','','')&&$('tbase','','')&&$('tbase','','')
tuples4 in varchar, --$('tbase','','')&&$('tbase','','')&&$('tbase','','')
tuples5 in varchar, --$('tbase','','')&&$('tbase','','')&&$('tbase','','')
tuples6 in varchar, --$('tbase','','')&&$('tbase','','')&&$('tbase','','')
tuples7 in varchar, --$('tbase','','')&&$('tbase','','')&&$('tbase','','')
tuples8 in varchar, --$('tbase','','')&&$('tbase','','')&&$('tbase','','')
tuples9 in varchar --$('tbase','','')&&$('tbase','','')&&$('tbase','','')
);
/**
批量设置给定cube中的公式
**/
procedure saveTheseCubeF(
tuples0 in varchar, --$('tbase','','')&&35&&$('tbase','','')&&45&&$('tbase','','')&&67
tuples1 in varchar, --$('tbase','','')&&35&&$('tbase','','')&&45&&$('tbase','','')&&67
tuples2 in varchar, --$('tbase','','')&&35&&$('tbase','','')&&45&&$('tbase','','')&&67
tuples3 in varchar, --$('tbase','','')&&35&&$('tbase','','')&&45&&$('tbase','','')&&67
tuples4 in varchar, --$('tbase','','')&&35&&$('tbase','','')&&45&&$('tbase','','')&&67
tuples5 in varchar, --$('tbase','','')&&35&&$('tbase','','')&&45&&$('tbase','','')&&67
tuples6 in varchar, --$('tbase','','')&&35&&$('tbase','','')&&45&&$('tbase','','')&&67
tuples7 in varchar, --$('tbase','','')&&35&&$('tbase','','')&&45&&$('tbase','','')&&67
tuples8 in varchar, --$('tbase','','')&&35&&$('tbase','','')&&45&&$('tbase','','')&&67
tuples9 in varchar --$('tbase','','')&&35&&$('tbase','','')&&45&&$('tbase','','')&&67
);
/**
获取给定坐标cube中的标量值
**/
function getCubeV(
topicCode in varchar, --主题量表名称
dTgt in varchar, --指标维
dims in varchar --其它维度
) return number;
/**
52周做特殊处理
**/
procedure initCubeWeek(
topicCode in varchar, --主题量表名称
dTgt in varchar, --指标维
dims in varchar --其它维度
);
procedure conversionF(
F in varchar --公式F
);
procedure conversionTheF(
F in varchar --公式F
);
function conversionTuple(
pFun in varchar, --列集函数
pTopicCode in varchar, --父cube所在量表名称
pDTgt in varchar, --父cube的指标维度
pDims in varchar, --父cube的其它维度
topic in varchar,
dtgt in varchar,
dims in varchar
) return varchar;
/**
获取给定坐标cube中的公式
**/
function getCubeF(
topicCode in varchar, --主题量表名称
dTgt in varchar, --指标维
dims in varchar --其它维度
) return varchar;
/**
获取给定坐标cube中的公式
**/
function getCubeF2(
tuple in varchar --cube坐标
) return varchar;
/**
向给定坐标cube中设置公式F
**/
procedure setCubeF(
topicCode in varchar, --主题量表名称
dTgt in varchar, --指标维
dims in varchar, --其它维度
F in varchar --公式F
);
/**
向给定坐标cube中设置公式F
**/
procedure setCubeF2(
tuple in varchar, --cube坐标
F in varchar --公式F
);
/**
计算给定的公式F
**/
function evalFormula(
F in varchar --公式F
) return number;
/**
获取给定原子时对应的历法时间
**/
function getCalendarDate(
calendarNo in varchar, --历法编号(必须是数字),比如是零宽字符串就对应历法time_term,'1'对应历法time_term1
curTime in number, --当前原子时(距离1979年1月1号0时0分0秒的毫秒数)
levelNum in number --要返回的历法时间的精确度,1就返回一级,2就返回2级,小于1或大于历法中的级别就分别报错
) return varchar;
/********************************************************************
内部的私有函数
******************************************************************************/
/**
按分隔符切分字符串
**/
function split(
pStr in varchar, --源字符串
pDelimiter in varchar --分割符字符串
) return TArrStr;
/**
解析tuple
**/
function parseCubeTuple(
topicCode in varchar, --主题量表Code
dTgt in varchar, --指标层码
dims in varchar --其它维度
) return TArrArrStr;
/**
按给定的列集函数求标量值
**/
function getTheCubeV(
pFun in varchar, --列集函数
pTopicCode in varchar, --父cube所在量表名称
pDTgt in varchar, --父cube的指标维度
pDims in varchar, --父cube的其它维度
topicCode in varchar, --主题量表名称
dTgt in varchar, --指标维
dims in varchar --其它维度
) return number;
/**
替换给定公式
**/
function evalTheFormula(
F in varchar, --公式F
pTopicCode in varchar,
pDTgt in varchar,
pDims in varchar
) return number;
/**
获取给定tuple中的公式F,如果存在就返回,不存在就看指标上有没有默认公式,如果有就返回,没有就返回null
**/
procedure getTheCubeF(
tuple TArrArrStr,
recCount out number,
F out varchar,
isLeaf out number
);
/**
如果给定公式F存在就返回它,不存在就返回给定指标的公式
**/
function fetchFormula(
F in varchar, --公式F
dTgtLevelCode in varchar --指标层码
) return varchar;
/**
处理维级:add(n)公式
**/
function addNum(
cLevelCode in varchar, --'/2013/:add(3)/04'
pLevelCode in varchar --'/2013/02/04'
) return varchar; --'/2013/05/04'
/**
处理维级中的谓词:min()和:max()
**/
function processFirstLast(
cTuple in TArrArrStr,
cPos in number
) return varchar; --'/2013/05/04'
function getCurrentTime
return varchar2;
procedure add_time_current(v_current_time in varchar2);
procedure add_current_tuple(
topic in varchar, --主题量表名称
dTgt in varchar, --指标维
dPrj in varchar , --其它维度
dTime in varchar, --其它维度
scalar varchar
);
procedure delete_current_tuple(
topicCode in varchar, --主题量表名称
dTgt in varchar, --指标维
dPrj in varchar , --其它维度
dTime in varchar --其它维度
);
procedure clearTmpResult;
end dim3;
/
create or replace package init is
procedure sure_PrjBaseInit( --tBase
pProjectLevelCode in varchar, --/p0234
pCalenderLevelCode in varchar, --/2013
pTargetLevelCode in varchar --/base/prj
);
procedure sure_SceneBaseInit( --tBase
pProjectLevelCode in varchar, --/p0234
pCalenderLevelCode in varchar, --/2013
pTargetLevelCode in varchar --/plan
);
procedure sure_ReportInit(
pProjectLevelCode in varchar,
pCalenderLevelCode in varchar,
pTargetLevelCode in varchar
);
procedure sure_PrjPlanInit( --tPrjPlan
pProjectLevelCode in varchar, --/p0234
pCalenderLevelCode in varchar, --/2013
pTargetLevelCode in varchar --/plan
);
procedure sure_ScenePlanInit( --tScenePlan
pProjectLevelCode in varchar, --/p0234
pCalenderLevelCode in varchar, --/2013
pTargetLevelCode in varchar --/plan
);
procedure sure_EquItemPrjPlanInit( --tEquItemPlan
pProjectLevelCode in varchar, --/p0234
pCalenderLevelCode in varchar, --/2013
pTargetLevelCode in varchar --/001/tEquItemPlan/prj
);
procedure sure_EquItemScenePlanInit(
pProjectLevelCode in varchar,
pCalenderLevelCode in varchar,
pTargetLevelCode in varchar
);
procedure sure_SvrItemPrjPlanInit(
pProjectLevelCode in varchar,
pCalenderLevelCode in varchar,
pTargetLevelCode in varchar
);
procedure sure_SvrItemScenePlanInit(
pProjectLevelCode in varchar,
pCalenderLevelCode in varchar,
pTargetLevelCode in varchar
);
end init;
/
create or replace package body dim3 is
type TTmpResultRecord is record(
tuple varchar2(1000),
scalar number(20,10),
lastTime timestamp
);
type TTmpResultTable is table of TTmpResultRecord index by binary_integer;
tempResult TTmpResultTable;
--current_scalare number:=0;
--current_count number:=0;
sqlText varchar(8000);
/********************************************************************
对外供j2ee调用的公共函数
******************************************************************************/
procedure fetchTmpResult(
tuple in varchar,
scalar out number,
recCount out number
) is
i number(5);
begin
for i in 1..tempResult.count loop
if tempResult(i).tuple=tuple then
scalar:=tempResult(i).scalar;
recCount:=1;
end if;
end loop;
if recCount is null then
recCount:=0;
scalar:=null;
end if;
end;
procedure intoTmpResult(
tuple in varchar,
scalar in number
) is
i number(5);
founded number(1);
begin
for i in 1..tempResult.count loop
if tempResult(i).tuple=tuple then
tempResult(i).scalar:=scalar;
tempResult(i).lastTime:=current_timestamp;
founded:=1;
end if;
end loop;
if founded is null then
tempResult(tempResult.count+1).tuple:=tuple;
tempResult(tempResult.count).scalar:=scalar;
tempResult(tempResult.count).lastTime:=current_timestamp;
end if;
end;
procedure clearTmpResult
is --pragma autonomous_transaction;
begin
tempResult.delete;
--commit;
end;
procedure fetchCubeByTuple(
F in varchar,
scalar out number,
status out varchar
) is
begin
status:=null;
scalar:=evalFormula(F);
EXCEPTION
WHEN OTHERS THEN
status:='#error';
end;
/**
批量获取给定cube中的值
**/
procedure getTheseCubeV(
rtnCursor out sys_refcursor,
tuples0 in varchar, --$('tbase','','')&&$('tbase','','')&&$('tbase','','')
tuples1 in varchar, --$('tbase','','')&&$('tbase','','')&&$('tbase','','')
tuples2 in varchar, --$('tbase','','')&&$('tbase','','')&&$('tbase','','')
tuples3 in varchar, --$('tbase','','')&&$('tbase','','')&&$('tbase','','')
tuples4 in varchar, --$('tbase','','')&&$('tbase','','')&&$('tbase','','')
tuples5 in varchar, --$('tbase','','')&&$('tbase','','')&&$('tbase','','')
tuples6 in varchar, --$('tbase','','')&&$('tbase','','')&&$('tbase','','')
tuples7 in varchar, --$('tbase','','')&&$('tbase','','')&&$('tbase','','')
tuples8 in varchar, --$('tbase','','')&&$('tbase','','')&&$('tbase','','')
tuples9 in varchar --$('tbase','','')&&$('tbase','','')&&$('tbase','','')
) is
--变量定义
i int;
vScalar number(38,23);
temp TArrStr;
tuples TArrStr;
status varchar(100);
theValue varchar(8000);
begin
if tuples0 is not null and not regexp_like(tuples0,'^\s*$') then
temp:=split(tuples0,'&&');
for i in 1..temp.count loop
tuples(tuples.count+1):=temp(i);
end loop;
end if;
if tuples1 is not null and not regexp_like(tuples1,'^\s*$') then
temp:=split(tuples1,'&&');
for i in 1..temp.count loop
tuples(tuples.count+1):=temp(i);
end loop;
end if;
if tuples2 is not null and not regexp_like(tuples2,'^\s*$') then
temp:=split(tuples2,'&&');
for i in 1..temp.count loop
tuples(tuples.count+1):=temp(i);
end loop;
end if;
if tuples3 is not null and not regexp_like(tuples3,'^\s*$') then
temp:=split(tuples3,'&&');
for i in 1..temp.count loop
tuples(tuples.count+1):=temp(i);
end loop;
end if;
if tuples4 is not null and not regexp_like(tuples4,'^\s*$') then
temp:=split(tuples4,'&&');
for i in 1..temp.count loop
tuples(tuples.count+1):=temp(i);
end loop;
end if;
if tuples5 is not null and not regexp_like(tuples5,'^\s*$') then
temp:=split(tuples5,'&&');
for i in 1..temp.count loop
tuples(tuples.count+1):=temp(i);
end loop;
end if;
if tuples6 is not null and not regexp_like(tuples6,'^\s*$') then
temp:=split(tuples6,'&&');
for i in 1..temp.count loop
tuples(tuples.count+1):=temp(i);
end loop;
end if;
if tuples7 is not null and not regexp_like(tuples7,'^\s*$') then
temp:=split(tuples7,'&&');
for i in 1..temp.count loop
tuples(tuples.count+1):=temp(i);
end loop;
end if;
if tuples8 is not null and not regexp_like(tuples8,'^\s*$') then
temp:=split(tuples8,'&&');
for i in 1..temp.count loop
tuples(tuples.count+1):=temp(i);
end loop;
end if;
if tuples9 is not null and not regexp_like(tuples9,'^\s*$') then
temp:=split(tuples9,'&&');
for i in 1..temp.count loop
tuples(tuples.count+1):=temp(i);
end loop;
end if;
for i in 1..tuples.count loop
IF tuples(i) IS NOT NULL THEN
select regexp_substr(tuples(i),'^\$\(''tsceneplan') into theValue from dual ;
IF theValue='$(''tsceneplan' THEN
conversionF(tuples(i));
END IF;
END IF;
fetchCubeByTuple(tuples(i),vScalar,status);
if status is null then
status:=to_char(vScalar);
end if;
insert into tmp_getthesecubev(cubTuple,scalar) values (tuples(i),status);
end loop;
open rtnCursor for select * from tmp_getthesecubev;
delete from tmp_getthesecubev;
dim3.clearTmpResult;
--execute immediate 'delete time_current';--删除临时表
end;
/**
批量设置给定cube中的公式
**/
procedure saveTheseCubeF(
tuples0 in varchar, --$('tbase','','')&&35&&$('tbase','','')&&45&&$('tbase','','')&&67
tuples1 in varchar, --$('tbase','','')&&35&&$('tbase','','')&&45&&$('tbase','','')&&67
tuples2 in varchar, --$('tbase','','')&&35&&$('tbase','','')&&45&&$('tbase','','')&&67
tuples3 in varchar, --$('tbase','','')&&35&&$('tbase','','')&&45&&$('tbase','','')&&67
tuples4 in varchar, --$('tbase','','')&&35&&$('tbase','','')&&45&&$('tbase','','')&&67
tuples5 in varchar, --$('tbase','','')&&35&&$('tbase','','')&&45&&$('tbase','','')&&67
tuples6 in varchar, --$('tbase','','')&&35&&$('tbase','','')&&45&&$('tbase','','')&&67
tuples7 in varchar, --$('tbase','','')&&35&&$('tbase','','')&&45&&$('tbase','','')&&67
tuples8 in varchar, --$('tbase','','')&&35&&$('tbase','','')&&45&&$('tbase','','')&&67
tuples9 in varchar --$('tbase','','')&&35&&$('tbase','','')&&45&&$('tbase','','')&&67
) is
i int;
sqlText varchar(8000);
temp TArrStr;
tuples TArrStr;
begin
if tuples0 is not null and not regexp_like(tuples0,'^\s*$') then
temp:=split(tuples0,'&&');
for i in 1..temp.count loop
tuples(tuples.count+1):=temp(i);
end loop;
end if;
if tuples1 is not null and not regexp_like(tuples1,'^\s*$') then
temp:=split(tuples1,'&&');
for i in 1..temp.count loop
tuples(tuples.count+1):=temp(i);
end loop;
end if;
if tuples2 is not null and not regexp_like(tuples2,'^\s*$') then
temp:=split(tuples2,'&&');
for i in 1..temp.count loop
tuples(tuples.count+1):=temp(i);
end loop;
end if;
if tuples3 is not null and not regexp_like(tuples3,'^\s*$') then
temp:=split(tuples3,'&&');
for i in 1..temp.count loop
tuples(tuples.count+1):=temp(i);
end loop;
end if;
if tuples4 is not null and not regexp_like(tuples4,'^\s*$') then
temp:=split(tuples4,'&&');
for i in 1..temp.count loop
tuples(tuples.count+1):=temp(i);
end loop;
end if;
if tuples5 is not null and not regexp_like(tuples5,'^\s*$') then
temp:=split(tuples5,'&&');
for i in 1..temp.count loop
tuples(tuples.count+1):=temp(i);
end loop;
end if;
if tuples6 is not null and not regexp_like(tuples6,'^\s*$') then
temp:=split(tuples6,'&&');
for i in 1..temp.count loop
tuples(tuples.count+1):=temp(i);
end loop;
end if;
if tuples7 is not null and not regexp_like(tuples7,'^\s*$') then
temp:=split(tuples7,'&&');
for i in 1..temp.count loop
tuples(tuples.count+1):=temp(i);
end loop;
end if;
if tuples8 is not null and not regexp_like(tuples8,'^\s*$') then
temp:=split(tuples8,'&&');
for i in 1..temp.count loop
tuples(tuples.count+1):=temp(i);
end loop;
end if;
if tuples9 is not null and not regexp_like(tuples9,'^\s*$') then
temp:=split(tuples9,'&&');
for i in 1..temp.count loop
tuples(tuples.count+1):=temp(i);
end loop;
end if;
for i in 1..tuples.count/2 loop
sqlText:='call dim3.setCubeF2(:1,:2)';
execute immediate sqlText using tuples(i*2-1),tuples(i*2);
end loop;
end;
/**
获取给定坐标cube中的标量值
**/
function getCubeV(
topicCode in varchar, --主题量表名称
dTgt in varchar, --指标维
dims in varchar --其它维度
) return number is
scalar number(38,23);
begin
scalar:=getTheCubeV('$',null,null,null,topicCode,dTgt,dims);
return scalar;
end;
procedure conversionF(
F in varchar --公式F
)
is
begin
conversionTheF(F);
end;
procedure conversionTheF(
F in varchar --公式F
)
is
sqlText varchar(8000);
scalar varchar2(4000);
begin
sqlText := regexp_replace(F,'(\$|[\$#]?(sum|multi|count|max|min|avg))\s*\(', 'dim3.conversionTuple(''\1'','''||null|| ''',''' || null || ''',''' || null || ''',');
sqlText:='select ('||sqlText||') from dual';
execute immediate sqlText into scalar;
end;
function conversionTuple(
pFun in varchar, --列集函数
pTopicCode in varchar, --父cube所在量表名称
pDTgt in varchar, --父cube的指标维度
pDims in varchar, --父cube的其它维度
topic in varchar,
dtgt in varchar,
dims in varchar
)
return varchar is
F varchar2(4000);
sqlText varchar2(4000);
begin
initCubeWeek(topic,dtgt,dims);
dbms_output.put_line('topic:'||topic||';dtgt:'||dtgt||';dims:'||dims);
return null;
end;
/**
降龙秘籍之呼风唤雨
*/
procedure initCubeWeek(
topicCode in varchar, --主题量表名称
dTgt in varchar, --指标维
dims in varchar --其它维度
) is
scalar number(38,23);
cTuple TArrArrStr;
v_dTgt varchar2(4000);
v_prj varchar2(4000);
v_time varchar2(4000);
flag int:=0;
begin
cTuple:=parseCubeTuple(topicCode,dTgt,dims);
scalar:=getTheCubeV('$',null,null,null,topicCode,dTgt,dims);
flag:=0;
FOR i IN 1..cTuple.COUNT-1 LOOP
IF cTuple(i)(1)='dTime' AND regexp_like(cTuple(i)(3),'/\d+/12/49') THEN
v_time:=cTuple(i)(3);
flag:=flag+1;
END IF;
IF cTuple(i)(1)='dPrj' THEN
v_prj:=cTuple(i)(3);
END IF ;
IF cTuple(i)(1)='dTgt' THEN
IF cTuple(i)(3)='/plan/box/Inventory_In_transit' THEN
v_dTgt:=cTuple(i)(3);
flag:=flag+1;
ELSIF cTuple(i)(3)='/plan/box/Inventory_Delivered_Not_Installe' THEN
v_dTgt:=cTuple(i)(3);
flag:=flag+1;
ELSIF cTuple(i)(3)='/plan/box/Inventory_Installed_Not_Revenue' THEN
v_dTgt:=cTuple(i)(3);
flag:=flag+1;
END IF;
END IF;
END LOOP;
IF flag=2 THEN
v_time:=v_time||'/c';
dbms_output.put_line('v_dTgt:'||v_dTgt||';v_prj:'||v_prj||';v_time:'||v_time||';scalar:'||scalar||';v_time:'||v_time);
add_current_tuple(topicCode,v_dTgt,v_prj,v_time,scalar);
END IF;
end;
/**
获取给定坐标cube中的公式
**/
function getCubeF(
topicCode in varchar, --主题量表名称
dTgt in varchar, --指标维
dims in varchar --其它维度
) return varchar is
tuple TArrArrStr;
F varchar(8000);
recCount number(18);
isLeaf number(1);
begin
tuple:=parseCubeTuple(topicCode,dTgt,dims);
getTheCubeF(tuple,recCount,F,isLeaf);
return F;
end;
/**
获取给定坐标cube中的公式
**/
function getCubeF2(
tuple in varchar --cube坐标
) return varchar is
sqlText varchar(8000);
F varchar(8000);
begin
sqlText:=regexp_replace(tuple,'^\$\((.*)\)$','select dim3.getCubeF(\1) theF from dual');
execute immediate sqlText into F;
return F;
end;
/**
向给定坐标cube中设置公式F
**/
procedure setCubeF(
topicCode in varchar, --主题量表名称
dTgt in varchar, --指标维
dims in varchar, --其它维度
F in varchar --公式F
) is
cTuple TArrArrStr;
recCount number(18);
sqlText varchar(8000);
sqlText2 varchar(8000);
sqlText3 varchar(8000);
theF varchar(8000);
--theCursor sys_refcursor;
--tgtLevelCode varchar(500);
leaf number(1);
v_dTgt varchar(8000);
v_dPrj varchar(8000);
v_dTime varchar(8000);
begin
cTuple:=parseCubeTuple(topicCode,dTgt,dims);
--先删除此cube
sqlText:=' from '||cTuple(0)(1)||' where 1=1';
for i in 1..cTuple.count-1 loop
sqlText:=sqlText||' and '||cTuple(i)(1)||' = '''||cTuple(i)(3)||'''';
end loop;
execute immediate 'delete '||sqlText;
if F is null or regexp_like(F,'^\s*$') then
theF:=fetchFormula(F,cTuple(cTuple.count-1)(3));
if theF is null then
return;
else
theF:='';
end if;
else
theF:=F;
end if;
/**
if F is null or regexp_like(F,'^\s*$') then
theF:='';
else
theF:=F;
end if ;
*/
--计算有没有下级cube,如果有此cube就是非叶子cube,否则为叶子cube
sqlText:=' from '||cTuple(0)(1)||' where 1=1';
for i in 1..cTuple.count-2 loop
if regexp_like(cTuple(i)(3),'/(ba|bi|aa|ai|forecast)$') then
sqlText:=null;
exit;
end if;
sqlText:=sqlText||' and '||cTuple(i)(1)||'||''/'' like '''||cTuple(i)(3)||'/%''';
end loop;
if sqlText is null then
leaf:=0;
else
sqlText:=sqlText||' and '||cTuple(cTuple.count-1)(1)||' = '''||cTuple(cTuple.count-1)(3)||'''';
execute immediate 'select count(id)'||sqlText into recCount;
if recCount>0 then
leaf:=0;
else
leaf:=1;
end if;
end if;
--再插入此
sqlText2:='';
sqlText3:='';
for i in 1..cTuple.count-1 loop
sqlText:=sqlText||' and '||cTuple(i)(1)||' = '''||cTuple(i)(3)||'''';
sqlText2:=sqlText2||cTuple(i)(1)||',';
sqlText3:=sqlText3||''''||cTuple(i)(3)||''',';
end loop;
sqlText:='insert into '||cTuple(0)(1)||'(id,'||sqlText2||'scalar,leaf) values(seq_'||cTuple(0)(1)||'.nextval,'||sqlText3||':1,:2)';
execute immediate sqlText using theF,leaf;
--然后把此cube的所有上级cube设为非叶子节点
sqlText:='update '||cTuple(0)(1)||' set leaf=0 where 1=1';
for i in 1..cTuple.count-1 loop
sqlText:=sqlText||' and '||cTuple(i)(1)||' = ''%'||cTuple(i)(3)||'''';
end loop;
execute immediate sqlText;
for i in 1..cTuple.count-1 loop
IF regexp_like(cTuple(i)(1),'dTime') THEN
v_dTime:=cTuple(i)(3);
ELSIF regexp_like(cTuple(i)(1),'dTgt') THEN
v_dTgt:=cTuple(i)(3);
ELSIF regexp_like(cTuple(i)(1),'dPrj') THEN
v_dPrj:=cTuple(i)(3);
END IF;
end loop;
--sqlText:='select count(t.id) from time_current t where t.topic='''||cTuple(0)(1)||''' and t.dtgt='''||v_dTgt||''' and t.dprj='''||v_dPrj||''' and t.dtime='''||v_dTime||'''';
--execute immediate sqlText into recCount;
--IF recCount>0 THEN
-- delete_current_tuple(cTuple(0)(1),v_dTgt,v_dPrj,v_dTime);
--END IF;
--如果当前cube是叶子cube,并且同在(其它维度相同)的其它公式指标cube如果不存在就插入为零宽字符串叶子cube
/*if leaf=1 then
sqlText:='';
sqlText2:='';
sqlText3:='';
for i in 1..cTuple.count-2 loop
sqlText:=sqlText||' and '||cTuple(i)(1)||'='''||cTuple(i)(3)||'''';
sqlText2:=sqlText2||cTuple(i)(1)||',';
sqlText3:=sqlText3||''''||cTuple(i)(3)||''',';
end loop;
tgtLevelCode:=cTuple(cTuple.count-1)(3);
tgtLevelCode:=regexp_replace(tgtLevelCode,'/\w+$','');
open theCursor for 'select t.levelCode
from target t
where t.formula is not null and not regexp_like(t.formula,''^\s*$'')
and t.levelcode||''/'' like '''||tgtLevelCode||'/%''
and not exists(select s.id
from '||cTuple(0)(1)||' s
where s.'||cTuple(cTuple.count-1)(1)||'=t.levelcode
'||sqlText||')';
loop
fetch theCursor into tgtLevelCode;
exit when theCursor%notfound;
sqlText:='insert into '||cTuple(0)(1)||'(id,'||sqlText2||cTuple(cTuple.count-1)(1)||',scalar,leaf) values(seq_'||cTuple(0)(1)||'.nextval,'||sqlText3||':1,:2,:3)';
execute immediate sqlText using tgtLevelCode,'',1;
end loop;
close theCursor;
end if;*/
end;
/**
向给定坐标cube中设置公式F
**/
procedure setCubeF2(
tuple in varchar, --cube坐标
F in varchar --公式F
) is
sqlText varchar(8000);
begin
--intoTmpResult(tuple,F);
sqlText:=regexp_replace(tuple,'^\$\((.*)\)$','call dim3.setCubeF(\1,:1)');
execute immediate sqlText using F;
end;
/**
计算给定的公式F
**/
function evalFormula(
F in varchar --公式F
) return number is
scalar number(38,23);
begin
scalar:=evalTheFormula(F,null,null,null);
return scalar;
end;