我在ORACLE中写了如下一个FUNCTION
create or replace function TradeEvaluation(v_sendcount in number, v_countdays in number, v_cycode1 in varchar2, v_amount1 in number, v_cycode2 in varchar2, v_amount2 in number)
return number is
v_count number(5);
v_spotrate1 number(12, 8);
v_totaldays1 number(4);
v_spotrate2 number(12, 8);
v_totaldays2 number(4);
v_termid1 number(4);
v_termid2 number(4);
a_amount1 number(18, 3);
a_amount2 number(18, 3);
begin select count(1) into v_count from dbos_term where totaldays = v_countdays;
--------------------------如果是非标准期限-----------------------
if v_count = 0 then
-------------------------计算AMOUNT1---------------------------
select min(termid) into v_termid1 from dbos_term
where totaldays > v_countdays;
----------------------------------------------------------------
select spotrate, totaldays
into v_spotrate1, v_totaldays1
from dbos_ratesource a, dbos_term b
where a.termid = b.termid and cycode = v_cycode1 and
sendcount = v_sendcount and a.termid = v_termid1;
----------------------------------------------------------------
select max(termid) into v_termid2 from dbos_term
where totaldays <= v_countdays;
----------------------------------------------------------------
select spotrate, totaldays
into v_spotrate2, v_totaldays2
from dbos_ratesource a, dbos_term b
where a.termid = b.termid and cycode = v_cycode1 and
sendcount = v_sendcount and a.termid = v_termid2;
----------------------------------------------------------------
a_amount1 := v_amount1 * ((v_spotrate1 - v_spotrate2) / (v_totaldays1 - v_totaldays2) * (v_countdays - v_totaldays2) + v_spotrate2);
---------------------计算AMOUNT2-------------------------------
select min(termid) into v_termid1 from dbos_term
where totaldays > v_countdays;
----------------------------------------------------------------
select spotrate, totaldays
into v_spotrate1, v_totaldays1
from dbos_ratesource a, dbos_term b
where a.termid = b.termid and cycode = v_cycode2 and
sendcount = v_sendcount and a.termid = v_termid1;
----------------------------------------------------------------
select max(termid) into v_termid2 from dbos_term
where totaldays <= v_countdays;
----------------------------------------------------------------
select spotrate, totaldays
into v_spotrate2, v_totaldays2
from dbos_ratesource a, dbos_term b
where a.termid = b.termid and cycode = v_cycode2 and
sendcount = v_sendcount and a.termid = v_termid2;
----------------------------------------------------------------
a_amount2 := v_amount2 * ((v_spotrate1 - v_spotrate2) / (v_totaldays1 - v_totaldays2) * (v_countdays - v_totaldays2) + v_spotrate2);
else
select termid into v_termid1 from dbos_term
where totaldays = v_countdays;
----------------------------------------------------------------
select spotrate into v_spotrate1
from dbos_ratesource a, dbos_term b
where a.termid = b.termid and cycode = v_cycode1 and
sendcount = v_sendcount and a.termid = v_termid1;
a_amount1 := v_amount1 * v_spotrate1;
----------------------------------------------------------------
select spotrate into v_spotrate2
from dbos_ratesource a, dbos_term b
where a.termid = b.termid and cycode = v_cycode2 and
sendcount = v_sendcount and a.termid = v_termid1;
a_amount2 := v_amount2 * v_spotrate2;
end if;
return (a_amount1 + a_amount2);
end TradeEvaluation;当我传入的参数是(2,8,'014',-128.123,'013',1000)通过该FUNCTION返回给我的是个空值,我很是诧异,不知道错在哪了.我将入上的参数单独代入FUNCTION中去执行的时候可以计算出正确的a_amount1和a.amount2的值.请大家指点下我,很急~~~
create or replace function TradeEvaluation(v_sendcount in number, v_countdays in number, v_cycode1 in varchar2, v_amount1 in number, v_cycode2 in varchar2, v_amount2 in number)
return number is
v_count number(5);
v_spotrate1 number(12, 8);
v_totaldays1 number(4);
v_spotrate2 number(12, 8);
v_totaldays2 number(4);
v_termid1 number(4);
v_termid2 number(4);
a_amount1 number(18, 3);
a_amount2 number(18, 3);
begin select count(1) into v_count from dbos_term where totaldays = v_countdays;
--------------------------如果是非标准期限-----------------------
if v_count = 0 then
-------------------------计算AMOUNT1---------------------------
select min(termid) into v_termid1 from dbos_term
where totaldays > v_countdays;
----------------------------------------------------------------
select spotrate, totaldays
into v_spotrate1, v_totaldays1
from dbos_ratesource a, dbos_term b
where a.termid = b.termid and cycode = v_cycode1 and
sendcount = v_sendcount and a.termid = v_termid1;
----------------------------------------------------------------
select max(termid) into v_termid2 from dbos_term
where totaldays <= v_countdays;
----------------------------------------------------------------
select spotrate, totaldays
into v_spotrate2, v_totaldays2
from dbos_ratesource a, dbos_term b
where a.termid = b.termid and cycode = v_cycode1 and
sendcount = v_sendcount and a.termid = v_termid2;
----------------------------------------------------------------
a_amount1 := v_amount1 * ((v_spotrate1 - v_spotrate2) / (v_totaldays1 - v_totaldays2) * (v_countdays - v_totaldays2) + v_spotrate2);
---------------------计算AMOUNT2-------------------------------
select min(termid) into v_termid1 from dbos_term
where totaldays > v_countdays;
----------------------------------------------------------------
select spotrate, totaldays
into v_spotrate1, v_totaldays1
from dbos_ratesource a, dbos_term b
where a.termid = b.termid and cycode = v_cycode2 and
sendcount = v_sendcount and a.termid = v_termid1;
----------------------------------------------------------------
select max(termid) into v_termid2 from dbos_term
where totaldays <= v_countdays;
----------------------------------------------------------------
select spotrate, totaldays
into v_spotrate2, v_totaldays2
from dbos_ratesource a, dbos_term b
where a.termid = b.termid and cycode = v_cycode2 and
sendcount = v_sendcount and a.termid = v_termid2;
----------------------------------------------------------------
a_amount2 := v_amount2 * ((v_spotrate1 - v_spotrate2) / (v_totaldays1 - v_totaldays2) * (v_countdays - v_totaldays2) + v_spotrate2);
else
select termid into v_termid1 from dbos_term
where totaldays = v_countdays;
----------------------------------------------------------------
select spotrate into v_spotrate1
from dbos_ratesource a, dbos_term b
where a.termid = b.termid and cycode = v_cycode1 and
sendcount = v_sendcount and a.termid = v_termid1;
a_amount1 := v_amount1 * v_spotrate1;
----------------------------------------------------------------
select spotrate into v_spotrate2
from dbos_ratesource a, dbos_term b
where a.termid = b.termid and cycode = v_cycode2 and
sendcount = v_sendcount and a.termid = v_termid1;
a_amount2 := v_amount2 * v_spotrate2;
end if;
return (a_amount1 + a_amount2);
end TradeEvaluation;当我传入的参数是(2,8,'014',-128.123,'013',1000)通过该FUNCTION返回给我的是个空值,我很是诧异,不知道错在哪了.我将入上的参数单独代入FUNCTION中去执行的时候可以计算出正确的a_amount1和a.amount2的值.请大家指点下我,很急~~~
楼主是不是不用在最后返回(a_amount1 + a_amount2);
直接返回a_amount1或者返回a_amount2看看是否都存在值,类似的逐步往上推测,看看到底哪个变量出现null值
楼主,要不这样,全部初始化。
v_count number(5) := 0;
v_spotrate1 number(12, 8) := 0;select nvl(spotrate,0) into v_spotrate1全部改了再运行看看.