CREATE OR REPLACE FUNCTION "ETL_GET_111HS_CSTM_NO"(acode VARCHAR2)
RETURN VARCHAR2 is
pCM01CUSNO VARCHAR2(10);
BEGIN
case
when exists (select 1
from sop_mir_cifm04
where cm04cusno is not null
and cm04acct = acode) then
select cm04cusno
into pCM01CUSNO
from sop_mir_cifm04
where cm04acct = acode
and cm04cusno is not null;
else
SELECT CM01CUSNO into pCM01CUSNO FROM CIFM01 where CM01ACCNO = acode;
end;
RETURN pCM01CUSNO;
END;
怎么if else啊...求大家帮助啊...意思就是第一个表中 数据不为空的时候 拿第一个表中的数据项
如果第一个表的数据项为空
就return第二个表的数据项...快帮助我哇!!!!!!!!!!!!!!!!!!!!!
RETURN VARCHAR2 is
pCM01CUSNO VARCHAR2(10);
a1 varchar2(10);
a2 varchar2(10);
BEGIN
select cm04cusno into a1 from sop_mir_cifm04
where cm04cusno is not null
and cm04acct = acode ;
SELECT CM01CUSNO into a2 FROM CIFM01 where CM01ACCNO = acode;
return nvl(a1,a2);
END;
RETURN VARCHAR2 is
pCM01CUSNO VARCHAR2(10);
BEGIN
select cm04cusno
into pCM01CUSNO
from sop_mir_cifm04
where cm04acct = acode
if pcm01cusno is not null then
retuen pcm01cusno;
else
SELECT CM01CUSNO into pCM01CUSNO FROM CIFM01 where CM01ACCNO = acode;
RETURN pCM01CUSNO;
END if;
end;
RETURN VARCHAR2 is
pCM01CUSNO VARCHAR2(10);
BEGIN
select max(cm04cusno)
into pCM01CUSNO
from sop_mir_cifm04
where cm04cusno is not null
and cm04acct = acod;
if pCM01CUSNO is null then
SELECT CM01CUSNO into pCM01CUSNO FROM CIFM01 where CM01ACCNO = acode;
end;
RETURN pCM01CUSNO;
END;
CREATE OR REPLACE FUNCTION "ETL_GET_111HS_CSTM_NO"(acode VARCHAR2)
RETURN VARCHAR2 is
pCM01CUSNO VARCHAR2(10);
BEGIN
select cm04cusno
into pCM01CUSNO
from sop_mir_cifm04 where cm04acct = acode
and cm04cusno is not null;
return pCM01CUSNO;
exception
when no_data_found then
SELECT CM01CUSNO into pCM01CUSNO FROM CIFM01 where CM01ACCNO = acode;
RETURN pCM01CUSNO;
END;