我有个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了,是不是有问题,请大家多指教
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了,是不是有问题,请大家多指教
select sum(nvl(e.fexp_amount_price,0))
把nvl写里面试试
你写死下执行sql试下select HY_FF_UTILITY_PKG.F_GET_CONSIGN_AW_AMOUNT(1, 2, 3, 4) as receive_usd_amount,
fcsg_hbo_no
from fconsign
把1,2,3,4处换成唯一值,直接写进去,看下这个要多长时间,应该是1秒一下
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';
写什么函数啊
select HY_FF_UTILITY_PKG.F_GET_CONSIGN_AW_AMOUNT(c.fcsg_consign_id, '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';意思就是我调用函数传入的第一个值c.fcsg_consign_id是动态的。这能把函数移出来吗
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';
这一段中的 and fe.fexp_consign_id = 'SDGL0704E000070' 改为 and fe.fexp_consign_id = c.fcsg_consign_id
不就行了?