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;