oracle调用函数语句,一个关于在SQL语句中调用函数慢的有关问题，

当前位置:我的异常网» Oracle开发 » 一个关于在SQL语句中调用函数慢的有关问题，

一个关于在SQL语句中调用函数慢的有关问题，

www.myexceptions.net  网友分享于：2013-03-28  浏览：96次

一个关于在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

文章评论