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;
(
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;
数据量达到百万
请问怎么解决
程序一直在运行,LZ可以打印log出来看看,到底是如何运行而没有退出
没有的话会始终判断第一条记录的
fetch cur into T_ERROR_CODE,T_ERROR_QTY;
数量大的话用游标执行效率很低呢;
谢谢commit 和 exit when rcursor%notfound ;
都不是