我可以在select语句中有这样的功能吗?
select
myseq.nextval,
getDoctor(dbNumber,C.code,DOCCODE),
getDoctor(dbNumber,C.code,RFDOCCODE),
...
from charges c
where type='R';
这里的getdoctor指向一个检查两个表并返回值的函数:
create or replace function getDoctor(dbNumber IN NUMBER,code IN VARCHAR2(10),doccode IN VARCHAR2(10), OUT DOCID VARCHAR2(10))
is
begin
DOCID:= nvl((select MDOCID from NEW_PROVIDERS NP where dbNumber=NP.dbNumber and code=NP.code and doccode=NP.doccode),
select MDOCID from ProvidersProxy P where P.originalDB=dbNumber and code=P.originalGroup and doccode=P.originalDocCode);
return DOCID;
end getDoctor;
编写以下内容(不要忘记“ p_”前缀):
create or replace function getDoctor(
p_dbNumber IN NUMBER,
p_code IN VARCHAR2(10),
p_doccode IN VARCHAR2(10)
) return varchar2
is
l_DOCID VARCHAR2(10);
begin
begin
select MDOCID
into l_DOCID
from NEW_PROVIDERS NP
where p_dbNumber=NP.dbNumber
and p_code=NP.code
and p_doccode=NP.doccode
;
exception
when no_data_found then
select MDOCID
into l_DOCID
from ProvidersProxy P
where P.originalDB=p_dbNumber
and p_cpcode=P.originalGroup
and p_doccode=P.originalDocCode
;
end;
return l_DOCID;
end getDoctor;
本文收集自互联网,转载请注明来源。
如有侵权,请联系 [email protected] 删除。
我来说两句