如题,2万条得30分钟左右,烦请高手指点。如何能优化,提高效率。谢谢
PROCEDURE DOWNLOADDOC(v_moveNo IN VARCHAR2,
v_flag IN VARCHAR2,
p_returnFlag IN VARCHAR2,
v_errFlag OUT VARCHAR2,
v_docText OUT CLOB)
AS
CURSOR cur_doc(v_moveNo VARCHAR2, v_flag VARCHAR2) IS
SELECT CONTENT, EXAMTIME
FROM SPEECH_RECORD
WHERE MOVENO = v_moveNo
AND USERTYPE = v_flag
ORDER BY EXAMTIME ASC;
v_loopNum number(10) := 0;
v_returnFlag varchar2(10);
BEGIN
v_errFlag := '0';
v_docText := '';
v_returnFlag :='';
IF p_returnFlag = 1 --如果为1则写入文件,为2则为页面显示
THEN v_returnFlag := CHR(13);
ELSE
v_returnFlag := '<BR>';
END IF;
FOR rec_doc IN cur_doc(v_moveNo, v_flag) LOOP
IF v_loopNum = 0 THEN
v_docText := rec_doc.CONTENT || '&'||'nbsp;'||'&'||'nbsp;' || to_char(TO_DATE(rec_doc.EXAMTIME,'YYYYMMDDhh24miss'),'yyyy-mm-dd hh24:mi:ss');
ELSE
v_docText := v_docText || v_returnFlag || rec_doc.CONTENT || '&'||'nbsp;'||'&'||'nbsp;' || to_char(TO_DATE(rec_doc.EXAMTIME,'YYYYMMDDhh24miss'),'yyyy-mm-dd hh24:mi:ss');
END IF;
v_loopNum := v_loopNum + 1;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
v_errFlag := '-1'; --操作失败
v_docText := '';
DBMS_OUTPUT.put_line(SQLERRM); --打印错误信息
--关闭游标
IF cur_doc%ISOPEN THEN
CLOSE cur_doc;
END IF;
END DOWNLOADDOC;
PROCEDURE DOWNLOADDOC(v_moveNo IN VARCHAR2,
v_flag IN VARCHAR2,
p_returnFlag IN VARCHAR2,
v_errFlag OUT VARCHAR2,
v_docText OUT CLOB)
AS
CURSOR cur_doc(v_moveNo VARCHAR2, v_flag VARCHAR2) IS
SELECT CONTENT, EXAMTIME
FROM SPEECH_RECORD
WHERE MOVENO = v_moveNo
AND USERTYPE = v_flag
ORDER BY EXAMTIME ASC;
v_loopNum number(10) := 0;
v_returnFlag varchar2(10);
BEGIN
v_errFlag := '0';
v_docText := '';
v_returnFlag :='';
IF p_returnFlag = 1 --如果为1则写入文件,为2则为页面显示
THEN v_returnFlag := CHR(13);
ELSE
v_returnFlag := '<BR>';
END IF;
FOR rec_doc IN cur_doc(v_moveNo, v_flag) LOOP
IF v_loopNum = 0 THEN
v_docText := rec_doc.CONTENT || '&'||'nbsp;'||'&'||'nbsp;' || to_char(TO_DATE(rec_doc.EXAMTIME,'YYYYMMDDhh24miss'),'yyyy-mm-dd hh24:mi:ss');
ELSE
v_docText := v_docText || v_returnFlag || rec_doc.CONTENT || '&'||'nbsp;'||'&'||'nbsp;' || to_char(TO_DATE(rec_doc.EXAMTIME,'YYYYMMDDhh24miss'),'yyyy-mm-dd hh24:mi:ss');
END IF;
v_loopNum := v_loopNum + 1;
END LOOP;
EXCEPTION
WHEN OTHERS THEN
v_errFlag := '-1'; --操作失败
v_docText := '';
DBMS_OUTPUT.put_line(SQLERRM); --打印错误信息
--关闭游标
IF cur_doc%ISOPEN THEN
CLOSE cur_doc;
END IF;
END DOWNLOADDOC;
SELECT content, examtime
FROM speech_record
WHERE moveno = v_moveno AND usertype = v_flag
ORDER BY examtime ASC;其他地方看不出有什么代码会造成瓶颈。