Oracle函数返回数组

 CREATE OR REPLACE TYPE defect_number3 AS OBJECT(
    ID varchar2(100),
    urgent NUMBER(10,2),
    major NUMBER(10,2),
    norm NUMBER(10,2),
    total NUMBER(10,2)
    )
create or replace type defect_number_array as TABLE OF defect_number3 

 

以前项目中写过的一个函数：

create or replace function f_get_defect_total_onerate(timeRangeStart  in varchar2,

                                                 timeRangeEnd    in varchar2,

                                                 deviceClassify  in varchar2,

                                                 voltageLevel    in varchar2,

                                                 defectStatus    in varchar2,

                                                 company         in varchar2,

                                                 vyear           in varchar2,

                                                 devicePart      in varchar2,

                                                 deviceFactory   in varchar2,

                                                 defectClass     in varchar2,

                                                 devicePlantYear in varchar2,

                                                 deviceType in varchar2,

                                                 defectLevel in varchar2)

  return defect_number_array is

  --在年份不确定的条件下，该函数的最大年份为2011

  --timeRangeStart  开始时间

  --timeRangeEnd    结束时间

  --voltageLevel    电压等级  该项可以传入多个值，每个值之间使用英文逗号隔开“,”

  --company         供电局ID

  --deviceClassify    设备类别

  --defectStatus      缺陷等级

  ---使用该函数的时候，需要判断返回结果是否为空，可参考以下

  ----SELECT  * from TABLE(f_pub_get_defect_rate('20120101','20120131','20024','13, 12, 11, 10','','1909','22'));

  var_out      defect_number3 := defect_number3(-1, -1, -1, -1, -1);

  var_outt      defect_number3 := defect_number3(-1, -1, -1, -1, -1);

  resultdefect defect_number_array := defect_number_array();

 

  minYear          number;

  maxYear          number;

  vtimeRangeStart  varchar2(512);

  vtimeRangeEnd    varchar2(512);

  v2timeRangeStart varchar2(512);

  v2timeRangeEnd   varchar2(512);

  urgent number(10,2);

  major number(10,2);

  norm number(10,2);

  total number(10,2);

   years number(10);

begin

  if ('empty' != NVL(vyear, 'empty')) then

    --电压等级

    minYear := SUBSTR(timeRangeStart, 0, 4);

    select f_get_defect_rate(timeRangeStart,

                             timeRangeEnd,

                             deviceClassify,

                             voltageLevel,

                             defectStatus,

                             company,

                             minYear,

                             devicePart,

                             deviceFactory,

                             defectClass,devicePlantYear,deviceType,defectLevel)

      into var_out

      from dual;

    resultdefect.extend();

    resultdefect(resultdefect.last) := var_out;

 

  else

    vtimeRangeStart := timeRangeStart;

    vtimeRangeEnd   := timeRangeEnd;

    if ('empty' = NVL(timeRangeStart, 'empty')) then

      vtimeRangeStart := '0101';

      vtimeRangeEnd   := '1231';

    end if;

    if('empty' != NVL(company, 'empty')) then

               select min(record_defect_year) into minYear from dim_operation_unit where id = company;

    else

               select min(record_defect_year) into minYear from dim_operation_unit;

    end if;

    SELECT to_char(sysdate-365, 'yyyy') into maxYear FROM DUAL;

      years:=maxYear - minYear + 1;

      urgent:=0;major:=0;norm:=0;total:=0;

    for i in 1 .. (maxYear - minYear + 1) loop

      v2timeRangeStart := minYear || vtimeRangeStart;

      v2timeRangeEnd   := minYear || vtimeRangeEnd;

      select f_get_defect_rate(v2timeRangeStart,

                               v2timeRangeEnd,

                               deviceClassify,

                               voltageLevel,

                               defectStatus,

                               company,

                               minYear,

                               devicePart,

                               deviceFactory,

                               defectClass,devicePlantYear,deviceType,defectLevel)

        into var_out

        from dual;

       urgent:=urgent+ var_out.urgent;

      major:=major+var_out.major;

      norm:=norm+var_out.norm;

      total:=total+var_out.total;

 

      minYear := minYear + 1;

    end loop;

    var_outt.urgent:=urgent/years;

    var_outt.major:=major/years;

    var_outt.norm:=norm/years;

    var_outt.total:=total/years;

    resultdefect.extend();

    resultdefect(resultdefect.last) := var_outt;

  end if;

  return resultdefect;

end f_get_defect_total_onerate;