create or replace procedure P_REPORT_LCD_LINE_TOTALERROR
(
T_MONTH VARCHAR2
)
AS
type refCur is ref cursor;--定义动态游标类型
cur refCur ; --声明动态游标
T_STATIME DATE;
sqlstr varchar2(1000);
BEGIN
open cur for 'select duty_type,count(duty_type) errorqty
from
(
select
substr(duty_type,1,1)duty_type
from t_re_repair
where trunc(test_time)>='||chr(39)||T_MONTHS_DAY||chr(39)||
'and trunc(test_time)<='||chr(39)||T_MONTHE_DAY||chr(39)||
'and duty_type is not null
)
where duty_type='||chr(39)||'A'||chr(39)|| ' or duty_type=' ||chr(39)||'B'||chr(39)||' or duty_type='||chr(39)||'C'||chr(39)||' or duty_type='||chr(39)||'D'||chr(39)||' or duty_type='||chr(39)||'E'||chr(39) ||
' group by duty_type' ;
loop
fetch cur into T_ERROR_CODE,T_ERROR_QTY;
exit when cur %notfound;
insert into EQHAdmin.T_LCD_LINE_TOTALERRORANALY(SEQUENCE_NUMBER,ERROR_PROJECT,ERROR_CODE,ERROR_QTY,ERROR_RATE,YEARMONTH,STATIME)
values(I,T_ERROR_PROJECT,T_ERROR_CODE,T_ERROR_QTY,T_ERROR_RATE||'%',T_MONTH,sysdate);
end loop;
close cur;
end;