“存储过程如下:”CREATE OR REPLACE procedure RQCZ_GETHZXXLIST (p_TYBM in varchar2, p_ZRCBM in varchar2,p_NF in varchar2,p_CURSOR OUT comm_types.comm_cursor) asbegin open p_cursor for SELECT HZBM_ID, XM, CONVERT_XB_TO_CHAR(XB) AS XB, RQCZ_GETHRKS(p_TYBM, TO_CHAR(HZBM_ID)) AS HRKS, RQCZ_GETCZSF(p_TYBM, TO_CHAR(HZBM_ID), p_NF) AS CZSF --主要是---这个函数影响了速度 FROM T_FHDJH WHERE (TYBM_ID = p_TYBM) AND (SUBSTR(TO_CHAR(HZBM_ID), 1, 2) = p_ZRCBM);end RQCZ_GETHZXXLIST;
影响速度的函数如下:CREATE OR REPLACE function RQCZ_GETCZSF (p_TYBM in varchar2,p_HZBM in varchar2,p_NF in varchar2) return varchar2 is Result number;
begin select count(*) into Result from T_RQCZ where p_TYBM = to_char(TYBM_ID) and p_HZBM = to_char(JMBH) and p_NF = to_char(NF);
if Result = 0 then return ('否'); else return ('是'); end if ;end RQCZ_GETCZSF;
select count(*) into Result from T_RQCZ where p_TYBM = to_char(TYBM_ID) and p_HZBM = to_char(JMBH) and p_NF = to_char(NF); 你的T_RQCZ表的字段TYBM_ID,JMBH,NF是什么类型,有无索引。若是字符型,没必要这样写,若是数值型,建议将传入参数改为数值型。 若字段上有索引,where子句中字段前不能有函数,这样不会利用索引
字段类型与参数类型相符,用count(1)就不用全表检索. select count(1) into Result from T_RQCZ where TYBM_ID=p_TYBM and JMBH=p_HZBM and NF= p_NF;
select count(*) into Result from T_RQCZ where p_TYBM = to_char(TYBM_ID) and p_HZBM = to_char(JMBH) and p_NF = to_char(NF); 改为 select NVL(MIN(rownum),0) into Result from T_RQCZ where TYBM_ID=to_number(p_TYBM) and JMBH = to_number(p_HZBM) and NF = to_number(p_NF) and rownum<2;
(p_TYBM in varchar2, p_ZRCBM in varchar2,p_NF in varchar2,p_CURSOR OUT comm_types.comm_cursor)
asbegin
open p_cursor for
SELECT HZBM_ID, XM, CONVERT_XB_TO_CHAR(XB) AS XB,
RQCZ_GETHRKS(p_TYBM, TO_CHAR(HZBM_ID)) AS HRKS,
RQCZ_GETCZSF(p_TYBM, TO_CHAR(HZBM_ID), p_NF) AS CZSF --主要是---这个函数影响了速度
FROM T_FHDJH
WHERE (TYBM_ID = p_TYBM) AND (SUBSTR(TO_CHAR(HZBM_ID), 1, 2) = p_ZRCBM);end RQCZ_GETHZXXLIST;
(p_TYBM in varchar2,p_HZBM in varchar2,p_NF in varchar2) return varchar2 is Result number;
begin
select count(*) into Result
from T_RQCZ
where p_TYBM = to_char(TYBM_ID) and p_HZBM = to_char(JMBH) and p_NF = to_char(NF);
if Result = 0 then
return ('否');
else return ('是');
end if ;end RQCZ_GETCZSF;
from T_RQCZ
where p_TYBM = to_char(TYBM_ID) and p_HZBM = to_char(JMBH) and p_NF = to_char(NF);
你的T_RQCZ表的字段TYBM_ID,JMBH,NF是什么类型,有无索引。若是字符型,没必要这样写,若是数值型,建议将传入参数改为数值型。
若字段上有索引,where子句中字段前不能有函数,这样不会利用索引
select count(1) into Result
from T_RQCZ
where TYBM_ID=p_TYBM and JMBH=p_HZBM and NF= p_NF;
在dba_studio中看索引,或者pl/sql developer也行
cs2002您是不是CS的高手啊?我可知道你是ORACLE的高手
但是现在还是不清楚索引为何物,有什么用?在哪里看?
from T_RQCZ
where p_TYBM = to_char(TYBM_ID) and p_HZBM = to_char(JMBH) and p_NF = to_char(NF);
改为
select NVL(MIN(rownum),0) into Result
from T_RQCZ
where TYBM_ID=to_number(p_TYBM) and JMBH = to_number(p_HZBM) and NF = to_number(p_NF) and rownum<2;
FFS的意思就是使用HINT!
算了,以后单独告诉你吧.空杯可是高手,多学学吧.