oracle存储过程调用1

------------------------------------------------
-- 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;