/**
获取给定原子时对应的历法时间
**/
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 is
tableName varchar(5000);--要查询的表名
sqlText varchar(5000);
levelCode varchar(5000);
--rLevelCode varchar(5000);
v_reg varchar(50);
v_reg1 varchar(50);
--sqlerror varchar(5000);
begin
--rLevelCode:='';
v_reg:='''^(/\d+){';
v_reg1:='}$'')';
tableName:='time_term'||calendarNo;
sqlText:='select levelcode from '||tableName||' where '||curTime||'>=starttime and '||curTime||'<endtime and regexp_like(levelcode,'||v_reg||levelNum||v_reg1;
execute immediate sqlText into levelCode;
--根据层级码得到要返回的levelcode
if levelCode is null then
raise_application_error(-22222,'没有找到数据!');
end if;
return levelCode;
end;
/********************************************************************
内部的私有函数
******************************************************************************/
/**
按分隔符切分字符串
**/
function split(
pStr in varchar, --源字符串
pDelimiter in varchar --分割符字符串
) return TArrStr is
i int;
j int;
len int;
len1 int;
str varchar(8000);
arr TArrStr;
begin
i := 1;
j := i;
len := length(pStr);
len1 := length(pDelimiter);
while j <= len loop
j := instr(pStr, pDelimiter, i, 1);
if (j > 0) then
str := substr(pStr, i, j - i);
arr(arr.count + 1) := str;
i := j + len1;
else
str := substr(pStr, i);
if str is not null then
arr(arr.count + 1) := str;
end if;
exit;
end if;
end loop;
return arr;
end;
/**
解析tuple
**/
function parseCubeTuple(
topicCode in varchar, --主题量表Code
dTgt in varchar, --指标层码
dims in varchar --其它维度
) return TArrArrStr is
arr TArrStr;
cubeTuple TArrArrStr;
i number(5);
begin
if topicCode is null then
return cubeTuple;
end if;
-- 解析tuple中的所有维度
arr := split(dims, ';');
-- 添加指标维度
arr(arr.count + 1) := dTgt;
for i in 1 .. arr.count loop
cubeTuple(i)(1):=regexp_substr(arr(i),'\w+'); --维度字段名称
cubeTuple(i)(2):=regexp_substr(arr(i),'=|<>|>=|>|<=|<|#|@|!#|!@'); --运算符
cubeTuple(i)(3):=regexp_replace(arr(i),'^\w+\s*(=|<>|>=|>|<=|<|#|@|!#|!@)\s*',''); --维层码
end loop;
cubeTuple(0)(1) := topicCode;
return cubeTuple;
end;
/**
按给定的列集函数求标量值
**/
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 is pragma autonomous_transaction;
scalar number(38,23):=null;
cTuple TArrArrStr;
pTuple TArrArrStr;
i number(5);
j number(5);
pos number(5);
levelCode varchar(1000);
preStr varchar(1000);
--sqlText varchar(8000);
sqlText2 varchar(8000);
recCount number(18);
isLeaf number(1);
theTuple varchar(1000);
cDTgt varchar(1000);
cDims varchar(8000);
F varchar(8000);
v_dTgt varchar(8000);
v_dPrj varchar(8000);
v_dTime varchar(8000);
begin
sqlText2:='';
cTuple:=parseCubeTuple(topicCode,dTgt,dims);
pTuple:=parseCubeTuple(pTopicCode,pDTgt,pDims);
--如果是当前主题量表
if cTuple(0)(1)='.' then
cTuple(0)(1):=pTuple(0)(1);
end if;
--其它维度绝对化
for i in 1..cTuple.count-1 loop
pos:=-1;
for j in 1..pTuple.count-1 loop
if pTuple(j)(1)=cTuple(i)(1) then
pos:=j;
exit;
end if;
end loop;
cTuple(i)(4):=pos;
preStr:=regexp_substr(cTuple(i)(3),'^\.+');
if preStr is not null then
levelCode:=regexp_replace(pTuple(pos)(3),'(/\w+){'||(length(preStr)-1)||'}$','');
levelCode:=levelCode||regexp_replace(cTuple(i)(3),'\.+','');
cTuple(i)(3):=levelCode;
end if;
-- 处理公式:add(n)
IF regexp_like(cTuple(i)(3),':add\(') THEN
levelCode:=addNum(cTuple(i)(3),pTuple(pos)(3));
cTuple(i)(3):=levelCode;
END IF;
IF regexp_like(cTuple(i)(3),':first\(|:last\(') THEN
-- 包含first或last
cTuple(i)(5):='1';--未知条件
ELSE
cTuple(i)(5):='0';--已知条件
END IF;
end loop;
for i in 1..cTuple.count-1 loop
-- 处理公式:last()或:first()
-- 如果cTuple(i)(5) :='1' 则说明当前维度里面有谓词 first(),last(),进入函数作处理对其绝对化,处理完之后,当前即为已知条件。cTuple(i)(5):='0';
IF cTuple(i)(5) = '1' THEN
levelCode:=processFirstLast(cTuple,i);
cTuple(i)(3):=levelCode;
cTuple(i)(5):='0';
END IF;
end loop;
--拼接tuple
theTuple:=pFun||'('''||cTuple(0)(1)||''','''||cTuple(cTuple.count-1)(1)
||cTuple(cTuple.count-1)(2)||cTuple(cTuple.count-1)(3)||''',''';
for i in 1..cTuple.count-2 loop
theTuple:=theTuple||cTuple(i)(1)
||cTuple(i)(2)||cTuple(i)(3)||';';
end loop;
theTuple:=regexp_replace(theTuple,';$','')||''')';
fetchTmpResult(theTuple,scalar,recCount);
if recCount=1 then
return scalar;
end if;
if pFun='$' then
getTheCubeF(cTuple,recCount,F,isLeaf);
--先判断其自身是否存在
if recCount>0 then
--如果存在,就判断其自身包含不包含非空公式F。
if F is null or regexp_like(F,'^\s*$') then
--公式F为空就再判断其自身是否是叶子节点,是就取指标公式再计算,否则直接返回0
if isLeaf=1 then
F:=fetchFormula(F,cTuple(cTuple.count-1)(3));
if F is null then
scalar:=null;
else
cDTgt:=cTuple(cTuple.count-1)(1)||cTuple(cTuple.count-1)(2)||cTuple(cTuple.count-1)(3);
cDims:='';
for i in 1..cTuple.count-2 loop
cDims:=cDims||cTuple(i)(1)||cTuple(i)(2)||cTuple(i)(3)||';';
end loop;
scalar:=evalTheFormula(F,cTuple(0)(1),cDTgt,cDims);
end if;
else
scalar:=null;
end if;
else
--包含非空公式F就计算F并返回
--current_scalare:=1;
cDTgt:=cTuple(cTuple.count-1)(1)||cTuple(cTuple.count-1)(2)||cTuple(cTuple.count-1)(3);
cDims:='';
for i in 1..cTuple.count-2 loop
cDims:=cDims||cTuple(i)(1)||cTuple(i)(2)||cTuple(i)(3)||';';
end loop;
scalar:=evalTheFormula(F,cTuple(0)(1),cDTgt,cDims);
end if;
else
--如果不存在,就卷积其下子节点
sqlText:=' from '||cTuple(0)(1)||' where 1=1';
for i in 1..cTuple.count-2 loop
if regexp_like(cTuple(i)(3),'^/ba$') then
raise_application_error(-20130,'/ba前面不能一级都没有');
elsif regexp_like(cTuple(i)(3),'^(/\d+){1,50}/ba$') then
--levelCode:=regexp_replace(cTuple(i)(3),'/ba$','',1,1,'i');
--sqlText:=sqlText||' and '||cTuple(i)(1)||' < '''||levelCode||'''';
sqlText:=null;
exit;
elsif regexp_like(cTuple(i)(3),'^(/\d+){0,1}/bi$') then
raise_application_error(-20130,'/bi前面必须要有2级及其以上');
elsif regexp_like(cTuple(i)(3),'^(/\d+){2,50}/bi$') then
levelCode:=regexp_replace(cTuple(i)(3),'(/\d+)/bi$','',1,1,'i');
sqlText:=sqlText||' and '||cTuple(i)(1)||'||''/'' like '''||levelCode||'/%''';
levelCode:=regexp_replace(cTuple(i)(3),'/bi$','',1,1,'i');
sqlText:=sqlText||' and '||cTuple(i)(1)||' < '''||levelCode||'''';
elsif regexp_like(cTuple(i)(3),'^/aa$') then
raise_application_error(-20130,'/aa前面不能一级都没有');
elsif regexp_like(cTuple(i)(3),'^(/\d+){1,50}/aa$') then
levelCode:=regexp_replace(cTuple(i)(3),'/aa$','',1,1,'i');
sqlText:=sqlText||' and '||cTuple(i)(1)||' > '''||levelCode||'''';
elsif regexp_like(cTuple(i)(3),'^(/\d+){0,1}/ai$') then
raise_application_error(-20130,'/ai前面必须要有2级及其以上');
elsif regexp_like(cTuple(i)(3),'^(/\d+){2,50}/ai$') then
levelCode:=regexp_replace(cTuple(i)(3),'(/\d+)/ai$','',1,1,'i');
sqlText:=sqlText||' and '||cTuple(i)(1)||'||''/'' like '''||levelCode||'/%''';
levelCode:=regexp_replace(cTuple(i)(3),'/ai$','',1,1,'i');
sqlText:=sqlText||' and '||cTuple(i)(1)||' > '''||levelCode||'''';
else
sqlText:=sqlText||' and '||cTuple(i)(1)||'||''/'' like '''||cTuple(i)(3)||'/%''';
end if;
end loop;
if sqlText is not null then
sqlText:=sqlText||' and '||cTuple(cTuple.count-1)(1)||' = '''||cTuple(cTuple.count-1)(3)||'''';
sqlText:=sqlText||' and leaf=1';
sqlText2:=''''||cTuple(cTuple.count-1)(1)||'=''||'||cTuple(cTuple.count-1)(1)||',';
for i in 1..cTuple.count-2 loop
sqlText2:=sqlText2||''''||cTuple(i)(1)||'=''||'||cTuple(i)(1)||'||'';''||';
end loop;
sqlText2:=regexp_replace(sqlText2,'.{7}$','');
sqlText:='select sum(dim3.evalTheFormula(dim3.fetchFormula(scalar,'||cTuple(cTuple.count-1)(1)||'),'''||cTuple(0)(1)||''','||sqlText2||'))'||sqlText;
execute immediate sqlText
into scalar;
end if;
end if;
else
sqlText2:=''''||cTuple(cTuple.count-1)(1)||'=''||'||cTuple(cTuple.count-1)(1)||',';
for i in 1..cTuple.count-2 loop
sqlText2:=sqlText2||''''||cTuple(i)(1)||'=''||'||cTuple(i)(1)||'||'';''||';
end loop;
sqlText2:=regexp_replace(sqlText2,'.{7}$','');
sqlText:='select '||regexp_replace(pFun,'^[\$#]','')||'(dim3.evalTheFormula(dim3.fetchFormula(scalar,'||cTuple(cTuple.count-1)(1)||'),'''||cTuple(0)(1)||''','||sqlText2||')) from '||cTuple(0)(1)||' where 1=1';
if pFun='$count' then
sqlText:='select count(*) from '||cTuple(0)(1)||' where 1=1';
end if;
for i in 1..cTuple.count-1 loop
if cTuple(i)(2)='#' then
sqlText:=sqlText||' and '||cTuple(i)(1)||' like '''||cTuple(i)(3)||'''';
elsif cTuple(i)(2)='!#' then
sqlText:=sqlText||' and '||cTuple(i)(1)||' not like '''||cTuple(i)(3)||'''';
elsif cTuple(i)(2)='@' then
sqlText:=sqlText||' and regexp_like('||cTuple(i)(1)||','''||cTuple(i)(3)||''')';
elsif cTuple(i)(2)='!@' then
sqlText:=sqlText||' and not regexp_like('||cTuple(i)(1)||','''||cTuple(i)(3)||''')';
else
sqlText:=sqlText||' and '||cTuple(i)(1)||' '||cTuple(i)(2)||' '''||cTuple(i)(3)||'''';
end if;
end loop;
if regexp_like(pFun,'^\$') then
sqlText:=sqlText||' and leaf=0';
elsif regexp_like(pFun,'^#') then
sqlText:=sqlText||' and 1=1';
else
sqlText:=sqlText||' and leaf=1';
end if;
execute immediate sqlText into scalar;
end if;
IF scalar IS NULL THEN
scalar:=0.0000000001;
END IF;
IF regexp_like(scalar,'^\s*.000000\d+$') THEN
scalar :=0.0000000001 ;
END IF;
--保存临时值
intoTmpResult(theTuple,scalar);
return scalar;
end;
/**
替换给定公式
**/
function evalTheFormula(
F in varchar, --公式F
pTopicCode in varchar,
pDTgt in varchar,
pDims in varchar
) return number is
sqlText varchar(8000);
scalar number(38,23);
arrDims TArrStr;
begin
if F is null or regexp_like(F,'^\s*$') then
return null;
end if;
sqlText := regexp_replace(F,'(\$|[\$#]?(sum|multi|count|max|min|avg))\s*\(', 'dim3.getTheCubeV(''\1'','''||pTopicCode|| ''',''' || pDTgt || ''',''' || pDims || ''',');
sqlText:='select ('||sqlText||') theValue from dual';
execute immediate sqlText into scalar;
return scalar;
end;
/**
获取给定tuple中的公式F,如果存在就返回,不存在就看指标上有没有默认公式,如果有就返回,没有就返回null
**/
procedure getTheCubeF(
tuple TArrArrStr,
recCount out number,
F out varchar,
isLeaf out number
) is
sqlText varchar(8000);
i number(5);
begin
sqlText:=' from '||tuple(0)(1)||' where 1=1';
for i in 1..tuple.count-1 loop
sqlText:=sqlText||' and '||tuple(i)(1)||'='''||tuple(i)(3)||'''';
end loop;
--等于1的情况,就取出来公式
execute immediate 'select scalar,leaf'||sqlText into F,isLeaf;
recCount:=1;
exception
when no_data_found then
recCount:=0;
F:=null;
when too_many_rows then
raise_application_error(-20102,'cube 重复');
end;
/**
如果给定公式F存在就返回它,不存在就返回给定指标的公式
**/
function fetchFormula(
F in varchar, --公式F
dTgtLevelCode in varchar --指标层码
) return varchar is
sqlText varchar(8000);
theF varchar(8000);
recCount number(18);
begin
if F is null or regexp_like(F,'^\s*$') then
--公式为NULL就取指标上的默认公式
sqlText:='select count(t.id) from target t where t.levelcode=:1';
execute immediate sqlText
into recCount
using dTgtLevelCode;
if recCount<1 then
raise_application_error(-20103,'指标'||dTgtLevelCode||'不存在');
end if;
if recCount>1 then
raise_application_error(-20104,'指标'||dTgtLevelCode||'重复');
end if;
sqlText:='select t.Formula from target t where t.levelcode=:1';
execute immediate sqlText
into theF
using dTgtLevelCode;
else
theF:=F;
end if;
if theF is not null then
if regexp_like(theF,'^\s*$') then
theF:=null;
end if;
--current_scalare:=1;
end if;
return theF;
end;
/**
处理维级:add(n)公式
**/
function addNum(
cLevelCode in varchar, --'/2013/:add(3)/04'
pLevelCode in varchar --'/2013/02/04' -- '/2013/05/04'
) return varchar is
v_cLevelCode varchar(8000);
v_pLevelCode varchar(8000);
cLevelCodeArrStr TArrStr;
pLevelCodeArrStr TArrStr;
--定义的变量
v_tablename varchar(8000);
vsqltimeterm varchar(5000);
vlevelcode varchar(5000);--
v_levelcodelike varchar(8000);--用于查询语句
v_level varchar(50);
v_reg varchar(50);
v_reg1 varchar(50);
v_reg2 varchar(50);
i int;
m int;
tlevelcode varchar(5000);
vSqltext varchar(5000);--取出最靠近plevelcode的值
v_levelcodelike_temp varchar(50);
begin
v_tablename:='time_term';
v_levelcodelike:='';
v_reg:='''^(/\d+){';
v_reg1:='}$''))';
v_reg2:='}$'')';
cLevelCodeArrStr:= dim3.split(cLevelCode,'/');
pLevelCodeArrStr:= dim3.split(pLevelCode,'/');
for i in 1..cLevelCodeArrStr.count loop
vSqltext:='select levelcode from '||v_tablename||' where rownum=1 and levelcode >=';
vsqltimeterm:='with ct as (select t.levelcode,row_number() over(order by id) as rnum from '||v_tablename||' t';
if regexp_substr(cLevelCodeArrStr(i),'(\:+)')=':' then
if vlevelcode is null then
v_cLevelCode:=regexp_substr(cLevelCodeArrStr(i),'(\s*-?\d+)'); --v_cLevelCode=3
v_pLevelCode:=pLevelCodeArrStr(i);--i=3 ,则为第2级 v_pLevelCode=02
for m in 2..i loop
v_levelcodelike:=v_levelcodelike||'/'||pLevelCodeArrStr(m);
v_levelcodelike_temp:=v_levelcodelike;
end loop;
v_levelcodelike:=''''||v_levelcodelike||'''';
v_level:=to_char(i-1);
vSqltext:=vSqltext||v_levelcodelike||' and regexp_like(levelcode,'||v_reg|| v_level ||v_reg2;
execute immediate vSqltext into tlevelcode;
tlevelcode:=''''||tlevelcode||'''';
if tlevelcode > v_levelcodelike then
v_cLevelCode:=v_cLevelCode-1;
end if;
vsqltimeterm:=vsqltimeterm||' where regexp_like(t.levelcode,'||v_reg|| v_level ||v_reg1;
vsqltimeterm:=vsqltimeterm||'select levelcode from ct where ct.rnum=(select rnum+'||v_cLevelCode||' from ct where ct.levelcode='||v_levelcodelike||')';
execute immediate vsqltimeterm into vlevelcode;
else
if regexp_substr(cLevelCodeArrStr(i),'(\:+)')=':' then
v_cLevelCode:=regexp_substr(cLevelCodeArrStr(i),'(\s*[+-]?\d+)'); --v_cLevelCode=3
v_pLevelCode:=pLevelCodeArrStr(i);--i=3 ,则为第2级 v_pLevelCode=02
v_levelcodelike:=v_levelcodelike_temp||'/'||pLevelCodeArrStr(i);
v_level:=to_char(i-1);
vlevelcode:=vlevelcode||'/'||pLevelCodeArrStr(i);
vlevelcode:=''''||vlevelcode||'''';
vSqltext:=vSqltext||vlevelcode||' and regexp_like(levelcode,'||v_reg|| v_level ||v_reg2;
execute immediate vSqltext into tlevelcode;
tlevelcode:=''''||tlevelcode||'''';
if tlevelcode > vlevelcode then
if v_cLevelCode > 0 then
v_cLevelCode:=v_cLevelCode-1;
else
if v_cLevelCode<0 then
v_cLevelCode:=v_cLevelCode;
else
v_cLevelCode:=v_cLevelCode;
end if;
end if;
end if;
vsqltimeterm:=vsqltimeterm||' where regexp_like(t.levelcode,'||v_reg|| v_level ||v_reg1;
vsqltimeterm:=vsqltimeterm||'select levelcode from ct where ct.rnum=(select rnum+'||v_cLevelCode||' from ct where ct.levelcode='||tlevelcode||')';
execute immediate vsqltimeterm into vlevelcode;
end if;
end if;
else
if vlevelcode is not null then
vlevelcode:=vlevelcode||'/'||cLevelCodeArrStr(i);
end if;
end if;
end loop;
return vlevelcode;
end;
function getCurrentTime
return varchar2 is
v_currenttime varchar2(500);
begin
execute immediate 'select current_time from time_current' into v_currenttime;
v_currenttime:=to_char(to_date(''||v_currenttime||'','yyyy-MM'),'yyyy-MM');
return v_currenttime;
end;
procedure add_time_current(v_current_time in varchar2)
is pragma autonomous_transaction;
sqlText varchar2(4000);
begin
execute immediate 'delete time_current';
sqlText:=' insert into time_current (id, current_time) values (seq_time_current.nextval,regexp_replace('''||v_current_time||''',''^/(\w+)'',''\1''))';
execute immediate sqlText;
commit;
end;
procedure add_current_tuple(
topic in varchar, --主题量表名称
dTgt in varchar, --指标维
dPrj in varchar , --其它维度
dTime in varchar, --其它维度
scalar varchar
)
is pragma autonomous_transaction;
sqlText varchar2(4000);
begin
sqlText:='update tsceneplan set scalar = '''||scalar||''' where dtgt='''||dTgt||''' and dprj='''||dPrj||''' and dtime='''||dTime||'''';
execute immediate sqlText;
commit;
end;
procedure delete_current_tuple(
topicCode in varchar, --主题量表名称
dTgt in varchar, --指标维
dPrj in varchar , --其它维度
dTime in varchar --其它维度
)
is pragma autonomous_transaction;
sqlText varchar2(4000);
begin
sqlText:='delete from time_current t where t.topic='''||topicCode||''' and t.dtgt='''||dTgt||''' and t.dprj='''||dPrj||''' and t.dtime='''||dTime||'''';
execute immediate sqlText ;
commit;
end;
/**
处理维级中的谓词:first()和:last()
**/
function processFirstLast(
cTuple in TArrArrStr,
cPos in number --当前维度
) return varchar is --'/2013/05/04'
v_dtime varchar(5000);--结果
processcLevelcodeArr TArrStr;
vDtime TArrStr;
whereSql varchar(5000);
v_str varchar(50);
m int;
v_levelcodelike varchar(5000);
begin
--取出所有的已知维度条件
v_levelcodelike:='';
processcLevelcodeArr:=dim3.split(cTuple(cPos)(3),'/');
--当前维度 如 dTime=/:last()/:first 或者dTime=:last()(全级)
--处理 分级 谓词用途的first()或last()
for i in 1..processcLevelcodeArr.count loop
whereSql:='from '||cTuple(0)(1)||' where 1=1 ';
if processcLevelcodeArr.count>0 then
for i in 1..cTuple.count-1 loop
if cTuple(i)(5)='0' then
if cTuple(i)(2)='#' then
whereSql:=whereSql||' and '||cTuple(i)(1)||' like '||''''||cTuple(i)(3)||'''';
elsif cTuple(i)(2)='!#' then
whereSql:=whereSql||' and '||cTuple(i)(1)||' not like '||''''||cTuple(i)(3)||'''';
elsif cTuple(i)(2)='@' then
whereSql:=whereSql||' and regexp_like( '||cTuple(i)(1)||','||''''||cTuple(i)(3)||''')';
elsif cTuple(i)(2)='!@' then
whereSql:=whereSql||' and not regexp_like( '||cTuple(i)(1)||','||''''||cTuple(i)(3)||''')';
else
whereSql:=whereSql||' and '||cTuple(i)(1)||cTuple(i)(2)||''''||cTuple(i)(3)||'''';
end if;
end if;
end loop;
if regexp_substr(processcLevelcodeArr(i),'(\:+)')=':' then
if v_dtime is null then
v_str:=regexp_substr(processcLevelcodeArr(i),'(first|last)');--last or first
if v_str='first' then
v_str:='min';
elsif v_str='last' then
v_str:='max';
end if;
for m in 2..i-1 loop
--以p为主
--以c为主
v_levelcodelike:=v_levelcodelike||'/'||processcLevelcodeArr(m);
end loop;
whereSql:='select '||v_str||'('||cTuple(cPos)(1)||') '||whereSql||' and '||cTuple(cPos)(1) ||' like '''||v_levelcodelike||'/%''';
execute immediate whereSql into v_dtime;
if i < processcLevelcodeArr.count then
vDtime:=dim3.split(v_dtime,'/');
v_dtime:='';
for n in 2..i loop
v_dtime:=v_dtime||'/'||vDtime(n);
end loop;
end if;
else
v_str:=regexp_substr(processcLevelcodeArr(i),'(first|last)');--last or first
if v_str='first' then
v_str:='min';
elsif v_str='last' then
v_str:='max';
end if;
whereSql:='select '||v_str||'('||cTuple(cPos)(1)||') '||whereSql||' and '||cTuple(cPos)(1) ||' like '''||v_dtime||'/%''';
execute immediate whereSql into v_dtime;
if i < processcLevelcodeArr.count then
vDtime:=dim3.split(v_dtime,'/');
v_dtime:='';
for n in 2..i loop
v_dtime:=v_dtime||'/'||vDtime(n);
end loop;
end if;
end if;
else
if v_dtime is not null then
v_dtime:=v_dtime||'/'||processcLevelcodeArr(i);
end if;
end if;
else
--处理 全级 谓词用途的first()或last()--
v_str:=regexp_substr(cTuple(cPos)(3),'(first|last)');
if v_str='first' then
v_str:='min';
elsif v_str='last' then
v_str:='max';
end if;
whereSql:='select '||v_str||'('||cTuple(cPos)(1)||') '||whereSql;
execute immediate whereSql into v_dtime;
end if;
end loop;
return v_dtime;
end;
begin
dbms_output.put_line('dim3 package inited');
end dim3;
/