一个关于在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
where e.fexp_rp_flag = RP_FLAG
and e.fexp_consign_id = CONSIGN_ID
and e.fexp_currency_code = CURRENCY_CODE
and e.fexp_cancel_flag = 'N '
and e.fexp_forward_flag = 'N ';
exception
when no_data_found then
v_return := 0;
end;
END IF;
end;
return v_return;
END F_GET_CONSIGN_AW_AMOUNT;
可是我执行了函数里的SQL语句，很快，在我DEBUG时，执行到select nvl(sum(e.fexp_amount_price),0)这行时下步就跳到end了，是不是有问题，请大家多指教
[解决办法]
那个查询返回的结果集有多少行？如果很多（如几万行以上），那么就不奇怪了，因为需要做那么多次的函数调用，而且函数中还需要执行查询。再就是fexpense表有多大，函数中那个查询的执行计划是什么
[解决办法]
改成如下:
select /*SUM*/
sum(fe.fexp_amount_price) over (partition by fe.fexp_rp_flag,fe.fexp_consign_id, fe.fexp_currency_code)
as receive_usd_amount,
/*SUM*/
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 '
/*加上去*/
and fe.fexp_rp_flag = 0
and fe.fexp_consign_id = 'SDGL0704E000070 '
and fe.fexp_currency_code = 'USD '
and fe.fexp_cancel_flag = 'N '
and fe.fexp_forward_flag = 'N ';
写什么函数啊
