一个关于在SQL语句中调用函数慢的问题,急急急
我有个SQL语句:
select HY_FF_UTILITY_PKG.F_GET_CONSIGN_AW_AMOUNT( 'SDGL0704E000070 ', 'USD ', '0 ', 'E ') as receive_usd_amount,
c.fcsg_hbo_no
from fconsign c,
cdepartment cbm,
fexpense fe,
cuser cu,
cdepartment cp,
ccustsuppview csv2
where c.fcsg_canvasser = cu.cusr_user_id(+)
and cu.cusr_dept_id = cp.cdpt_dept_id(+)
and c.fcsg_creator_dept_id = cbm.cdpt_dept_id(+)
and c.fcsg_consign_dept = csv2.accountnumber(+)
and c.fcsg_consign_id = fe.fexp_consign_id(+)
and c.fcsg_consign_type not in ( '01 ', '11 ', '81 ', '82 ')
and c.fcsg_consign_status not in ( '0 ', '2 ', '4 ')
and (c.fcsg_consign_date > = to_date( '2007-04-01 00:00:00 ', 'yyyy-mm-dd hh24:mi:ss ')
and c.fcsg_consign_date < to_date( '2007-04-24 00:00:00 ', 'yyyy-mm-dd hh24:mi:ss ') + 1)
and c.fcsg_org_id = '135 ';
执行很慢,如果把select选项中HY_FF_UTILITY_PKG.F_GET_CONSIGN_AW_AMOUNT( 'SDGL0704E000070 ', 'USD ', '0 ', 'E ') as receive_usd_amount去掉则很快,我的函数是这么写的:
FUNCTION F_GET_CONSIGN_AW_AMOUNT(CONSIGN_ID VARCHAR2, CURRENCY_CODE VARCHAR2, RP_FLAG VARCHAR2 ,COUNT_FLAG VARCHAR2)
RETURN NUMBER is
v_return NUMBER;
BEGIN
v_return := 0;
begin
IF COUNT_FLAG = 'E ' THEN
begin
select nvl(sum(e.fexp_amount_price),0)
into v_return
from fexpense e